Opening orders view in backend throws SQL error

[wahidnory]

Hello,

After updating to version 1.23.2, our magento 2.3.4 installation started throwing an SQL error in the backend on the orders screen.

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'O18' in 'where clause', query was: select method, group_concat(distinct(buckaroo_magento2_group_transaction.servicecode) SEPARATOR "-") as giftcard_codes, increment_id from sales_order_payment inner join sales_order on sales_order.entity_id = sales_order_payment.parent_id inner join buckaroo_magento2_group_transaction on buckaroo_magento2_group_transaction.order_id=sales_order.increment_id where sales_order.increment_id in (O18-0053366, ... omitted ) group by sales_order.increment_id

After investigating some more I found out that there was an update to this file.
view/adminhtml/ui_component/sales_order_grid.xml
(removing the file loads the grid correctly).

And this file
Ui/Component/Listing/Column/Method/Filter.php
does something with:

/**
     * Get options
     *
     * @return array
     */
    public function toOptionArray()
    {
        parent::toOptionArray();

        $db = $this->resourceConnection->getConnection();

        $result = $db->query('
            select 
            method, 
            group_concat(distinct(buckaroo_magento2_giftcard.servicecode) SEPARATOR "-") as giftcard_codes,
            group_concat(distinct(buckaroo_magento2_giftcard.label) SEPARATOR "-") as giftcard_titles 
            from sales_order_payment 
            inner join sales_order on sales_order.entity_id = sales_order_payment.parent_id 
            inner join buckaroo_magento2_group_transaction on buckaroo_magento2_group_transaction.order_id=sales_order.increment_id 
            inner join buckaroo_magento2_giftcard on buckaroo_magento2_giftcard.servicecode=buckaroo_magento2_group_transaction.servicecode 
            group by buckaroo_magento2_group_transaction.order_id
        ');
...

Which kinda looks as to where the SQL error is coming from.

Hope this is enough information to help you debug/reproduce the issue.

Thank you in advance.

[vladislav-padalka-hys]

Hi @wahidnory !

We've verified - PHP file you've provided is not related to the issue (at least because there is no WHERE statement)
We are unable to reproduce the issue you reported at our stagings.
But we suppose it's because your installation is non standard and using custom prefixes for order increments and this makes them non numeric.
Thanks for reporting and we'll adjust our code for your case soon.

[vladislav-padalka-hys]

@wahidnory we've found that SQL and made where statement more generic
It has been committed into the master branch (ed96349)
Fix will be included into next release or, in case of urgency, you can use updated file from master branch.
Thank you !

[wahidnory]

Thank you for your fast fix, this indeed fixes the issue.