Booking search returns a "DB error: syntax error" (CiviCRM 5.19.2)

[shohidgmcvo]

Hi,

I've just upgraded a site from 5.13.4 to 5.19.2 with this extension previously working on it. When I perform a booking search now with a start date range, it throws an error "DB error: syntax error"

The report log states "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax".

Can someone look into the cause of this please?

Thank you.

[Barijohn]

Have updated the system to 5.20 and the only thing broken now is the date range. Error message below:

Array
(
[callback] => Array
(
[0] => CRM_Core_Error
[1] => handle
)

[code] => -2
[message] => DB Error: syntax error
[mode] => 16
[debug_info] => SELECT count( DISTINCT  civicrm_booking.id ) as rowCount  FROM civicrm_contact contact_a     LEFT JOIN civicrm_booking ON civicrm_booking.primary_contact_id = contact_a.id AND civicrm_booking.is_deleted = 0  WHERE  ( 

( .start_date >= '20191202000000' ) AND
( .start_date <= '20191208235959' )
) AND (contact_a.is_deleted = 0) [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '>= '20191202000000' ) AND
( .start_date <= '20191208235959' )
) AND (contact_a' at line 2]
[type] => DB_Error
[user_info] => SELECT count( DISTINCT civicrm_booking.id ) as rowCount FROM civicrm_contact contact_a LEFT JOIN civicrm_booking ON civicrm_booking.primary_contact_id = contact_a.id AND civicrm_booking.is_deleted = 0 WHERE (
( .start_date >= '20191202000000' ) AND
( .start_date <= '20191208235959' )
) AND (contact_a.is_deleted = 0) [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '>= '20191202000000' ) AND
( .start_date <= '20191208235959' )
) AND (contact_a' at line 2]
[to_string] => [db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::handle prefix="" info="SELECT count( DISTINCT civicrm_booking.id ) as rowCount FROM civicrm_contact contact_a LEFT JOIN civicrm_booking ON civicrm_booking.primary_contact_id = contact_a.id AND civicrm_booking.is_deleted = 0 WHERE (
( .start_date >= '20191202000000' ) AND
( .start_date <= '20191208235959' )
) AND (contact_a.is_deleted = 0) [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '>= '20191202000000' ) AND
( .start_date <= '20191208235959' )
) AND (contact_a' at line 2]"]
)

[samuelsov]

I'm not able to retrace what has caused this problem in core but it seems that core is now adding the where clause before getting to CRM_Booking_BAO_Query::where

Core function CRM_Contact_BAO_Query::restWhere expect the table_name to be defined in the DAO fields definition. We should probably rebuild the DAOs to fix this properly but for now, the following patch fix the bug for me:

diff --git a/CRM/Booking/DAO/Booking.php b/CRM/Booking/DAO/Booking.php
index 28aa140..3266313 100644
--- a/CRM/Booking/DAO/Booking.php
+++ b/CRM/Booking/DAO/Booking.php
@@ -299,6 +303,7 @@ class CRM_Booking_DAO_Booking extends CRM_Core_DAO
           'where' => 'civicrm_booking.booking_date',
           'headerPattern' => '',
           'dataPattern' => '',
+          'table_name' => 'civicrm_booking',
         ) ,
         'booking_start_date' => array(
           'name' => 'start_date',
@@ -309,6 +314,7 @@ class CRM_Booking_DAO_Booking extends CRM_Core_DAO
           'where' => 'civicrm_booking.start_date',
           'headerPattern' => '',
           'dataPattern' => '',
+          'table_name' => 'civicrm_booking',
         ) ,
         'booking_end_date' => array(
           'name' => 'end_date',
@@ -319,6 +325,7 @@ class CRM_Booking_DAO_Booking extends CRM_Core_DAO
           'where' => 'civicrm_booking.end_date',
           'headerPattern' => '',
           'dataPattern' => '',
+          'table_name' => 'civicrm_booking',
         ) ,
         'booking_po_number' => array(
           'name' => 'po_number',

That means we could also probably remove some legacy code in CRM_Booking_BAO_Query and maybe elsewhere.