SQL error in 20.38.3

[ghost]

I updated to 20.38.3, as 20.35.0 had an issue with the table join in the sales_order_grid.

However, it is still present in 20.38.3.

[2020-08-28 15:40:25] report.CRITICAL: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'delivery_date' in 'where clause', query was: SELECT COUNT(*) FROM `sales_order_grid` AS `main_table`
 LEFT JOIN `wiserobot_channeladvisorapi_order` AS `caOrders` ON main_table.ca_order_id = caOrders.ca_order_id
 LEFT JOIN `braintree_transaction_details` ON braintree_transaction_details.order_id = main_table.entity_id WHERE (`delivery_date` >= '2020-08-01 00:00:00') AND (`delivery_date` <= '2020-08-28 23:59:59') {"exception":"[object] (Zend_Db_Statement_Exception(code: 42): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'delivery_date' in 'where clause', query was: SELECT COUNT(*) FROM `sales_order_grid` AS `main_table`
 LEFT JOIN `wiserobot_channeladvisorapi_order` AS `caOrders` ON main_table.ca_order_id = caOrders.ca_order_id
 LEFT JOIN `braintree_transaction_details` ON braintree_transaction_details.order_id = main_table.entity_id WHERE (`delivery_date` >= '2020-08-01 00:00:00') AND (`delivery_date` <= '2020-08-28 23:59:59') at /var/www/magento2/vendor/magento/framework/DB/Statement/Pdo/Mysql.php:110, PDOException(code: 42S22): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'delivery_date' in 'where clause' at /var/www/magento2/vendor/magento/framework/DB/Statement/Pdo/Mysql.php:91)"} []

delivery_date is not a field in the sales_order_grid table and I am currently unable to figure out why the count() query is being called (doesn't seem to be called for purchase date for example)

[wsajason]

Hi @paul-gene From the SQL query provided this is attempting to join the sales_order_grid to wiserobot_channeladvisorapi_order and braintree_transaction_details. ShipperHQ joins the sales order grid to the shipperhq_order_detail_grid table.

[ghost]

Ignore the wiserobot and braintree joins, I can remove those modules and still get this error.

[wsajason]

Can you confirm that the shipperhq_order_detail_grid table is present and the delivery_date column is present there? If the column/table is missing you will need to ensure the SQL has run, as detailed here If the column & table are present the most likely cause is that something is conflicting or overriding the behaviour of the module. If this is the case and there are any modules that affect the order grid I would recommend disabling for testing purposes. Please also confirm the Magento version for reference.

[ghost]

@wsajason I can confirm the table shipperhd_order_detail_grid is present, and the column delivery_date is also present

Disabled Wiserobot and Braintree. Still getting SQL error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'shipperhq_order_delivery_date' in 'where clause', query was: SELECT COUNT(*) FROM sales_order_grid AS main_table WHERE (shipperhq_order_delivery_date >= '2020-08-31 23:00:00') AND (shipperhq_order_delivery_date <= '2020-09-07 22:59:59')

This is happening when trying to filter by "Delivery date" on the sales order grid.

Not sure exactly where this "count" query is being ran, but you can clearly see from the printed query, there is no join to the shipperhq_order_detail_grid table.

[wsajosh]

It looks like the GitHub user account that reported this issue has been deleted. We've had no one else report this issue and none of the ShipperHQ team have been able to replicate this issue. We believe it was an issue specific to the users Magento install so we're closing this issue for now.