"Syntax error or access violation: 1064" when placing an order with 19.5.0-rc3

[andyt]

Preconditions (*)

1. OpenMage 19.5.0-rc3
2. PHP 8.2.5

Steps to reproduce (*)

1. Place an order

Expected result (*)

1. No exceptions

Actual result (*)

1. Order appears tp be placed, but there are exceptions in the exception.log file

> 2023-04-24T10:17:51+00:00 ERR (3): 
> PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.`tax_class_id WHEN 2 THEN       0`.`2000  ELSE 0 END))*CASE e`.`tax_class_id WH' at line 2 in /var/www/vendor/shardj/zf1-future/library/Zend/Db/Statement/Pdo.php:228
> Stack trace:
> #0 /var/www/vendor/shardj/zf1-future/library/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
> #1 /var/www/htdocs/lib/Varien/Db/Statement/Pdo/Mysql.php(98): Zend_Db_Statement_Pdo->_execute(Array)
> #2 /var/www/vendor/shardj/zf1-future/library/Zend/Db/Statement.php(303): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
> #3 /var/www/vendor/shardj/zf1-future/library/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
> #4 /var/www/vendor/shardj/zf1-future/library/Zend/Db/Adapter/Pdo/Abstract.php(244): Zend_Db_Adapter_Abstract->query('SELECT ROUND(((...', Array)
> #5 /var/www/htdocs/lib/Varien/Db/Adapter/Pdo/Mysql.php(486): Zend_Db_Adapter_Pdo_Abstract->query('SELECT ROUND(((...', Array)
> #6 /var/www/vendor/shardj/zf1-future/library/Zend/Db/Adapter/Abstract.php(797): Varien_Db_Adapter_Pdo_Mysql->query('SELECT ROUND(((...', Array)
> #7 /var/www/htdocs/app/code/core/Mage/Catalog/Model/Resource/Layer/Filter/Price.php(315): Zend_Db_Adapter_Abstract->fetchCol(Object(Varien_Db_Select))
> #8 /var/www/htdocs/app/code/core/Mage/Catalog/Model/Layer/Filter/Price.php(542): Mage_Catalog_Model_Resource_Layer_Filter_Price->loadPrices(Object(Mage_Catalog_Model_Layer_Filter_Price), 22.0, 14.0, 0, NULL)
> #9 /var/www/htdocs/app/code/core/Mage/Catalog/Model/Layer/Filter/Price/Algorithm.php(347): Mage_Catalog_Model_Layer_Filter_Price->loadPrices(22.0, 14.0, 0, NULL)
> #10 /var/www/htdocs/app/code/core/Mage/Catalog/Model/Layer/Filter/Price/Algorithm.php(534): Mage_Catalog_Model_Layer_Filter_Price_Algorithm->_findPriceSeparator(1)
> #11 /var/www/htdocs/app/code/core/Mage/Catalog/Model/Layer/Filter/Price.php(277): Mage_Catalog_Model_Layer_Filter_Price_Algorithm->calculateSeparators()
> #12 /var/www/htdocs/app/code/core/Mage/Catalog/Model/Layer/Filter/Price.php(297): Mage_Catalog_Model_Layer_Filter_Price->_getCalculatedItemsData()
> #13 /var/www/htdocs/app/code/core/Mage/Catalog/Model/Layer/Filter/Abstract.php(142): Mage_Catalog_Model_Layer_Filter_Price->_getItemsData()
> #14 /var/www/htdocs/app/code/core/Mage/Catalog/Model/Layer/Filter/Abstract.php(111): Mage_Catalog_Model_Layer_Filter_Abstract->_initItems()
> #15 /var/www/htdocs/app/code/core/Mage/Catalog/Model/Layer/Filter/Abstract.php(100): Mage_Catalog_Model_Layer_Filter_Abstract->getItems()
> #16 /var/www/htdocs/app/code/core/Mage/Catalog/Block/Layer/Filter/Abstract.php(123): Mage_Catalog_Model_Layer_Filter_Abstract->getItemsCount()
> #17 /var/www/htdocs/app/code/core/Mage/Catalog/Block/Layer/View.php(209): Mage_Catalog_Block_Layer_Filter_Abstract->getItemsCount()
> #18 /var/www/htdocs/app/code/core/Mage/Catalog/Block/Layer/View.php(224): Mage_Catalog_Block_Layer_View->canShowOptions()
> #19 /var/www/htdocs/app/design/frontend/ultimo/default/template/catalog/layer/view.phtml(34): Mage_Catalog_Block_Layer_View->canShowBlock()
> #20 /var/www/htdocs/app/code/core/Mage/Core/Block/Template.php(251): include('/var/www/htdocs...')
> #21 /var/www/htdocs/app/code/core/Mage/Core/Block/Template.php(288): Mage_Core_Block_Template->fetchView('frontend/ultimo...')
> #22 /var/www/htdocs/app/code/core/Mage/Core/Block/Template.php(301): Mage_Core_Block_Template->renderView()
> #23 /var/www/htdocs/app/code/core/Mage/Core/Block/Abstract.php(927): Mage_Core_Block_Template->_toHtml()
> #24 /var/www/htdocs/app/code/core/Mage/Core/Block/Text/List.php(36): Mage_Core_Block_Abstract->toHtml()
> #25 /var/www/htdocs/app/code/core/Mage/Core/Block/Abstract.php(927): Mage_Core_Block_Text_List->_toHtml()
> #26 /var/www/htdocs/app/code/core/Mage/Core/Block/Abstract.php(644): Mage_Core_Block_Abstract->toHtml()
> #27 /var/www/htdocs/app/code/core/Mage/Core/Block/Abstract.php(588): Mage_Core_Block_Abstract->_getChildHtml('left', true)
> #28 /var/www/htdocs/app/design/frontend/ultimo/default/template/page/2columns-left.phtml(36): Mage_Core_Block_Abstract->getChildHtml('left')
> #29 /var/www/htdocs/app/code/core/Mage/Core/Block/Template.php(251): include('/var/www/htdocs...')
> #30 /var/www/htdocs/app/code/core/Mage/Core/Block/Template.php(288): Mage_Core_Block_Template->fetchView('frontend/ultimo...')
> #31 /var/www/htdocs/app/code/core/Mage/Core/Block/Template.php(301): Mage_Core_Block_Template->renderView()
> #32 /var/www/htdocs/app/code/core/Mage/Core/Block/Abstract.php(927): Mage_Core_Block_Template->_toHtml()
> #33 /var/www/htdocs/app/code/core/Mage/Core/Model/Layout.php(574): Mage_Core_Block_Abstract->toHtml()
> #34 /var/www/htdocs/app/code/core/Mage/Core/Controller/Varien/Action.php(391): Mage_Core_Model_Layout->getOutput()
> #35 /var/www/htdocs/app/code/core/Mage/Catalog/controllers/CategoryController.php(167): Mage_Core_Controller_Varien_Action->renderLayout()
> #36 /var/www/htdocs/app/code/core/Mage/Core/Controller/Varien/Action.php(422): Mage_Catalog_CategoryController->viewAction()
> #37 /var/www/htdocs/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(256): Mage_Core_Controller_Varien_Action->dispatch('view')
> #38 /var/www/htdocs/app/code/core/Mage/Core/Controller/Varien/Front.php(182): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
> #39 /var/www/htdocs/app/code/core/Mage/Core/Model/App.php(365): Mage_Core_Controller_Varien_Front->dispatch()
> #40 /var/www/htdocs/app/Mage.php(739): Mage_Core_Model_App->run(Array)
> #41 /var/www/htdocs/index.php(55): Mage::run('freeborn', 'store')
> #42 {main}
> 
> Next Zend_Db_Statement_Exception: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.`tax_class_id WHEN 2 THEN       0`.`2000  ELSE 0 END))*CASE e`.`tax_class_id WH' at line 2, query was: SELECT ROUND(((e.min_price -(e.min_price/(1+(CASE e.tax_class_id WHEN 2 THEN       0.2000  ELSE 0 END))*CASE e.tax_class_id WHEN 2 THEN       0.2000  ELSE 0 END)+((e.min_price-(e.min_price/(1+(CASE e.tax_class_id WHEN 2 THEN       0.2000  ELSE 0 END))*CASE e.tax_class_id WHEN 2 THEN       0.2000  ELSE 0 END))*CASE e.tax_class_id WHEN 2 THEN       0.2000  ELSE 0 END))) * 1, 2) AS `min_price_expr` FROM `catalog_product_index_price` AS `e`
>  INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.visibility IN(2, 4) AND cat_index.category_id = 4 WHERE 1=1 AND ( e.website_id = 1 ) AND ( e.customer_group_id = 1) AND ((e.min_price -(e.min_price/(1+(CASE e.tax_class_id WHEN 2 THEN       0.2000  ELSE 0 END))*CASE e.tax_class_id WHEN 2 THEN       0.2000  ELSE 0 END)+((e.min_price-(e.min_price/(1+(CASE e.tax_class_id WHEN 2 THEN       0.2000  ELSE 0 END))*CASE e.tax_class_id WHEN 2 THEN       0.2000  ELSE 0 END))*CASE e.tax_class_id WHEN 2 THEN       0.2000  ELSE 0 END)) IS NOT NULL) AND ((e.min_price -(e.min_price/(1+(CASE e.tax_class_id WHEN 2 THEN       0.2000  ELSE 0 END))*CASE e.tax_class_id WHEN 2 THEN       0.2000  ELSE 0 END)+((e.min_price-(e.min_price/(1+(CASE e.tax_class_id WHEN 2 THEN       0.2000  ELSE 0 END))*CASE e.tax_class_id WHEN 2 THEN       0.2000  ELSE 0 END))*CASE e.tax_class_id WHEN 2 THEN       0.2000  ELSE 0 END)) >= -0.005000) ORDER BY `(e`.`min_price -(e`.`min_price/(1+(CASE e`.`tax_class_id WHEN 2 THEN       0`.`2000  ELSE 0 END))*CASE e`.`tax_class_id WHEN 2 THEN       0`.`2000  ELSE 0 END)+((e`.`min_price-(e`.`min_price/(1+(CASE e`.`tax_class_id WHEN 2 THEN       0`.`2000  ELSE 0 END))*CASE e`.`tax_class_id WHEN 2 THEN       0`.`2000  ELSE 0 END))*CASE e`.`tax_class_id WHEN 2 THEN       0`.`2000  ELSE 0 END))` ASC LIMIT 22 OFFSET 14 in /var/www/vendor/shardj/zf1-future/library/Zend/Db/Statement/Pdo.php:235
> Stack trace:
> #0 /var/www/htdocs/lib/Varien/Db/Statement/Pdo/Mysql.php(98): Zend_Db_Statement_Pdo->_execute(Array)
> #1 /var/www/vendor/shardj/zf1-future/library/Zend/Db/Statement.php(303): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
> #2 /var/www/vendor/shardj/zf1-future/library/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
> #3 /var/www/vendor/shardj/zf1-future/library/Zend/Db/Adapter/Pdo/Abstract.php(244): Zend_Db_Adapter_Abstract->query('SELECT ROUND(((...', Array)
> #4 /var/www/htdocs/lib/Varien/Db/Adapter/Pdo/Mysql.php(486): Zend_Db_Adapter_Pdo_Abstract->query('SELECT ROUND(((...', Array)
> #5 /var/www/vendor/shardj/zf1-future/library/Zend/Db/Adapter/Abstract.php(797): Varien_Db_Adapter_Pdo_Mysql->query('SELECT ROUND(((...', Array)
> #6 /var/www/htdocs/app/code/core/Mage/Catalog/Model/Resource/Layer/Filter/Price.php(315): Zend_Db_Adapter_Abstract->fetchCol(Object(Varien_Db_Select))
> #7 /var/www/htdocs/app/code/core/Mage/Catalog/Model/Layer/Filter/Price.php(542): Mage_Catalog_Model_Resource_Layer_Filter_Price->loadPrices(Object(Mage_Catalog_Model_Layer_Filter_Price), 22.0, 14.0, 0, NULL)
> #8 /var/www/htdocs/app/code/core/Mage/Catalog/Model/Layer/Filter/Price/Algorithm.php(347): Mage_Catalog_Model_Layer_Filter_Price->loadPrices(22.0, 14.0, 0, NULL)
> #9 /var/www/htdocs/app/code/core/Mage/Catalog/Model/Layer/Filter/Price/Algorithm.php(534): Mage_Catalog_Model_Layer_Filter_Price_Algorithm->_findPriceSeparator(1)
> #10 /var/www/htdocs/app/code/core/Mage/Catalog/Model/Layer/Filter/Price.php(277): Mage_Catalog_Model_Layer_Filter_Price_Algorithm->calculateSeparators()
> #11 /var/www/htdocs/app/code/core/Mage/Catalog/Model/Layer/Filter/Price.php(297): Mage_Catalog_Model_Layer_Filter_Price->_getCalculatedItemsData()
> #12 /var/www/htdocs/app/code/core/Mage/Catalog/Model/Layer/Filter/Abstract.php(142): Mage_Catalog_Model_Layer_Filter_Price->_getItemsData()
> #13 /var/www/htdocs/app/code/core/Mage/Catalog/Model/Layer/Filter/Abstract.php(111): Mage_Catalog_Model_Layer_Filter_Abstract->_initItems()
> #14 /var/www/htdocs/app/code/core/Mage/Catalog/Model/Layer/Filter/Abstract.php(100): Mage_Catalog_Model_Layer_Filter_Abstract->getItems()
> #15 /var/www/htdocs/app/code/core/Mage/Catalog/Block/Layer/Filter/Abstract.php(123): Mage_Catalog_Model_Layer_Filter_Abstract->getItemsCount()
> #16 /var/www/htdocs/app/code/core/Mage/Catalog/Block/Layer/View.php(209): Mage_Catalog_Block_Layer_Filter_Abstract->getItemsCount()
> #17 /var/www/htdocs/app/code/core/Mage/Catalog/Block/Layer/View.php(224): Mage_Catalog_Block_Layer_View->canShowOptions()
> #18 /var/www/htdocs/app/design/frontend/ultimo/default/template/catalog/layer/view.phtml(34): Mage_Catalog_Block_Layer_View->canShowBlock()
> #19 /var/www/htdocs/app/code/core/Mage/Core/Block/Template.php(251): include('/var/www/htdocs...')
> #20 /var/www/htdocs/app/code/core/Mage/Core/Block/Template.php(288): Mage_Core_Block_Template->fetchView('frontend/ultimo...')
> #21 /var/www/htdocs/app/code/core/Mage/Core/Block/Template.php(301): Mage_Core_Block_Template->renderView()
> #22 /var/www/htdocs/app/code/core/Mage/Core/Block/Abstract.php(927): Mage_Core_Block_Template->_toHtml()
> #23 /var/www/htdocs/app/code/core/Mage/Core/Block/Text/List.php(36): Mage_Core_Block_Abstract->toHtml()
> #24 /var/www/htdocs/app/code/core/Mage/Core/Block/Abstract.php(927): Mage_Core_Block_Text_List->_toHtml()
> #25 /var/www/htdocs/app/code/core/Mage/Core/Block/Abstract.php(644): Mage_Core_Block_Abstract->toHtml()
> #26 /var/www/htdocs/app/code/core/Mage/Core/Block/Abstract.php(588): Mage_Core_Block_Abstract->_getChildHtml('left', true)
> #27 /var/www/htdocs/app/design/frontend/ultimo/default/template/page/2columns-left.phtml(36): Mage_Core_Block_Abstract->getChildHtml('left')
> #28 /var/www/htdocs/app/code/core/Mage/Core/Block/Template.php(251): include('/var/www/htdocs...')
> #29 /var/www/htdocs/app/code/core/Mage/Core/Block/Template.php(288): Mage_Core_Block_Template->fetchView('frontend/ultimo...')
> #30 /var/www/htdocs/app/code/core/Mage/Core/Block/Template.php(301): Mage_Core_Block_Template->renderView()
> #31 /var/www/htdocs/app/code/core/Mage/Core/Block/Abstract.php(927): Mage_Core_Block_Template->_toHtml()
> #32 /var/www/htdocs/app/code/core/Mage/Core/Model/Layout.php(574): Mage_Core_Block_Abstract->toHtml()
> #33 /var/www/htdocs/app/code/core/Mage/Core/Controller/Varien/Action.php(391): Mage_Core_Model_Layout->getOutput()
> #34 /var/www/htdocs/app/code/core/Mage/Catalog/controllers/CategoryController.php(167): Mage_Core_Controller_Varien_Action->renderLayout()
> #35 /var/www/htdocs/app/code/core/Mage/Core/Controller/Varien/Action.php(422): Mage_Catalog_CategoryController->viewAction()
> #36 /var/www/htdocs/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(256): Mage_Core_Controller_Varien_Action->dispatch('view')
> #37 /var/www/htdocs/app/code/core/Mage/Core/Controller/Varien/Front.php(182): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
> #38 /var/www/htdocs/app/code/core/Mage/Core/Model/App.php(365): Mage_Core_Controller_Varien_Front->dispatch()
> #39 /var/www/htdocs/app/Mage.php(739): Mage_Core_Model_App->run(Array)
> #40 /var/www/htdocs/index.php(55): Mage::run('freeborn', 'store')
> #41 {main}
> 

[addison74]

I used the latest version of OpenMage (main branch) with Magento Sample Pack. I placed the order and looked in the logs. Apart from those reported here #3184, I did not find any of those reported by you.

We cannot investigate particular cases that appear in custom installations, with extensions. It is known that major changes have been made in OpenMage to make it compatible with the latest version of PHP and certain extensions can no longer work. As you can see from you report, there are most likely errors in the extensions (custom SQL queries., Try to disable all of them except those related to OpenMage and analyze if the errors still occur.

If no one else confirms this report we will close it within a week from now.

[fballiano]

mmmm the only thing I see is a tax_class_id = 2, maybe we should check with real tax amounts just to be sure :-\

[andyt]

Thanks @addison74. The stack trace doesn't appear to be running through any extensions, and I'm not aware of any on this installation relating to taxation. I've checked the only other mention of third party code, which is the theme, and that doesn't appear to be a culprit.

However, I'll try disabling some extensions and see what happens.

Thanks for investigating.

I used the latest version of OpenMage (main branch) with Magento Sample Pack. I placed the order and looked in the logs. Apart from those reported here #3184, I did not find any of those reported by you.

We cannot investigate particular cases that appear in custom installations, with extensions. It is known that major changes have been made in OpenMage to make it compatible with the latest version of PHP and certain extensions can no longer work. As you can see from you report, there are most likely errors in the extensions (custom SQL queries., Try to disable all of them except those related to OpenMage and analyze if the errors still occur.

If no one else confirms this report we will close it within a week from now.

[fballiano]

It could be an observer, it wouldnt be visible in the stack in some cases

[luigifab]

I think this is that.
Try to search ELSE 0 END in all of your files, if you are lucky, you will find the culprit.

[elidrissidev]

Most likely originating from the line below. Try wrapping the sql code in a Zend_Db_Expr and see if it will solve your issue.

magento-lts/app/code/core/Mage/Tax/Helper/Data.php

Line 841 in 98464c6

$$rateVariable = "CASE {$taxClassField} {$$rateVariable} ELSE 0 END";

[andyt]

Thanks @elidrissidev. This fixed the issue.

Most likely originating from the line below. Try wrapping the sql code in a Zend_Db_Expr and see if it will solve your issue.

magento-lts/app/code/core/Mage/Tax/Helper/Data.php

Line 841 in 98464c6

$$rateVariable = "CASE {$taxClassField} {$$rateVariable} ELSE 0 END";

[elidrissidev]

@andyt Did you test with my PR #3227?

[andyt]

@elidrissidev No, I simply wrapped the line you suggested in Data.php with a new Zend_DB_Expr. Your PR doesn't make any changes to that file.

[elidrissidev]

Could you try that fix instead? If it solves the issue it will make it to a new RC release soon.