PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'e.sum(qty_ordered)-sum(qty_refunded)-sum(qty_canceled)'

[Jattura]

Preconditions (*)

1.19.5.0-rc1
2.

Steps to reproduce (*)

1.First of all, I'm not an expert, just an average user. Please help to solve the following problem. Thanks for your help.

Expected result (*)

1. I used Amasty”
   Extended Product Grid with Editor" on Sale > Orders panel. In version 19.4.23 , it can use index management to re-index everything. However, Index Management > Qty Sold cannot reindex due to the change to 19.5.0-rc1 version.

2. This link is for your reference to know about qty_ordered)-sum(qty_refunded)-sum(qty_canceled). Please click https://itecnotes.com/magento/magento-how-to-get-collection-of-orders-which-can-be-shipped/

Actual result (*)

1. In the exception log, it found the following code.

2023-03-09T05:01:39+00:00 ERR (3): 
PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'e.sum(qty_ordered)-sum(qty_refunded)-sum(qty_canceled)' in 'field list' in C:\xampp\htdocs\z\vendor\shardj\zf1-future\library\Zend\Db\Statement\Pdo.php:228
Stack trace:
#0 C:\xampp\htdocs\z\vendor\shardj\zf1-future\library\Zend\Db\Statement\Pdo.php(228): PDOStatement->execute(Array)
#1 C:\xampp\htdocs\z\lib\Varien\Db\Statement\Pdo\Mysql.php(104): Zend_Db_Statement_Pdo->_execute(Array)
#2 C:\xampp\htdocs\z\vendor\shardj\zf1-future\library\Zend\Db\Statement.php(303): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#3 C:\xampp\htdocs\z\vendor\shardj\zf1-future\library\Zend\Db\Adapter\Abstract.php(480): Zend_Db_Statement->execute(Array)
#4 C:\xampp\htdocs\z\vendor\shardj\zf1-future\library\Zend\Db\Adapter\Pdo\Abstract.php(244): Zend_Db_Adapter_Abstract->query('SELECT `e`.`pro...', Array)
#5 C:\xampp\htdocs\z\lib\Varien\Db\Adapter\Pdo\Mysql.php(492): Zend_Db_Adapter_Pdo_Abstract->query('SELECT `e`.`pro...', Array)
#6 C:\xampp\htdocs\z\vendor\shardj\zf1-future\library\Zend\Db\Adapter\Abstract.php(739): Varien_Db_Adapter_Pdo_Mysql->query('SELECT `e`.`pro...', Array)
#7 C:\xampp\htdocs\z\app\code\local\Amasty\Pgrid\Model\Resource\Indexer\Qty.php(112): Zend_Db_Adapter_Abstract->fetchAll(Object(Varien_Db_Select))
#8 C:\xampp\htdocs\z\app\code\local\Amasty\Pgrid\Model\Resource\Indexer\Qty.php(37): Amasty_Pgrid_Model_Resource_Indexer_Qty->_prepareIndexTable()
#9 C:\xampp\htdocs\z\app\code\core\Mage\Index\Model\Indexer\Abstract.php(145): Amasty_Pgrid_Model_Resource_Indexer_Qty->reindexAll()
#10 C:\xampp\htdocs\z\app\code\core\Mage\Index\Model\Process.php(207): Mage_Index_Model_Indexer_Abstract->reindexAll()
#11 C:\xampp\htdocs\z\app\code\core\Mage\Index\Model\Process.php(255): Mage_Index_Model_Process->reindexAll()
#12 C:\xampp\htdocs\z\app\code\core\Mage\Index\controllers\Adminhtml\ProcessController.php(135): Mage_Index_Model_Process->reindexEverything()
#13 C:\xampp\htdocs\z\app\code\core\Mage\Core\Controller\Varien\Action.php(428): Mage_Index_Adminhtml_ProcessController->reindexProcessAction()
#14 C:\xampp\htdocs\z\app\code\core\Mage\Core\Controller\Varien\Router\Standard.php(262): Mage_Core_Controller_Varien_Action->dispatch('reindexProcess')
#15 C:\xampp\htdocs\z\app\code\core\Mage\Core\Controller\Varien\Front.php(188): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#16 C:\xampp\htdocs\z\app\code\core\Mage\Core\Model\App.php(371): Mage_Core_Controller_Varien_Front->dispatch()
#17 C:\xampp\htdocs\z\app\Mage.php(748): Mage_Core_Model_App->run(Array)
#18 C:\xampp\htdocs\z\index.php(61): Mage::run('', 'store')
#19 {main}

Next Zend_Db_Statement_Exception: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'e.sum(qty_ordered)-sum(qty_refunded)-sum(qty_canceled)' in 'field list', query was: SELECT `e`.`product_id`, `e`.`sum(qty_ordered)-sum(qty_refunded)-sum(qty_canceled)` AS `qty_sold` FROM `sales_flat_order_item` AS `e` GROUP BY `product_id` in C:\xampp\htdocs\z\vendor\shardj\zf1-future\library\Zend\Db\Statement\Pdo.php:235
Stack trace:
#0 C:\xampp\htdocs\z\lib\Varien\Db\Statement\Pdo\Mysql.php(104): Zend_Db_Statement_Pdo->_execute(Array)
#1 C:\xampp\htdocs\z\vendor\shardj\zf1-future\library\Zend\Db\Statement.php(303): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 C:\xampp\htdocs\z\vendor\shardj\zf1-future\library\Zend\Db\Adapter\Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 C:\xampp\htdocs\z\vendor\shardj\zf1-future\library\Zend\Db\Adapter\Pdo\Abstract.php(244): Zend_Db_Adapter_Abstract->query('SELECT `e`.`pro...', Array)
#4 C:\xampp\htdocs\z\lib\Varien\Db\Adapter\Pdo\Mysql.php(492): Zend_Db_Adapter_Pdo_Abstract->query('SELECT `e`.`pro...', Array)
#5 C:\xampp\htdocs\z\vendor\shardj\zf1-future\library\Zend\Db\Adapter\Abstract.php(739): Varien_Db_Adapter_Pdo_Mysql->query('SELECT `e`.`pro...', Array)
#6 C:\xampp\htdocs\z\app\code\local\Amasty\Pgrid\Model\Resource\Indexer\Qty.php(112): Zend_Db_Adapter_Abstract->fetchAll(Object(Varien_Db_Select))
#7 C:\xampp\htdocs\z\app\code\local\Amasty\Pgrid\Model\Resource\Indexer\Qty.php(37): Amasty_Pgrid_Model_Resource_Indexer_Qty->_prepareIndexTable()
#8 C:\xampp\htdocs\z\app\code\core\Mage\Index\Model\Indexer\Abstract.php(145): Amasty_Pgrid_Model_Resource_Indexer_Qty->reindexAll()
#9 C:\xampp\htdocs\z\app\code\core\Mage\Index\Model\Process.php(207): Mage_Index_Model_Indexer_Abstract->reindexAll()
#10 C:\xampp\htdocs\z\app\code\core\Mage\Index\Model\Process.php(255): Mage_Index_Model_Process->reindexAll()
#11 C:\xampp\htdocs\z\app\code\core\Mage\Index\controllers\Adminhtml\ProcessController.php(135): Mage_Index_Model_Process->reindexEverything()
#12 C:\xampp\htdocs\z\app\code\core\Mage\Core\Controller\Varien\Action.php(428): Mage_Index_Adminhtml_ProcessController->reindexProcessAction()
#13 C:\xampp\htdocs\z\app\code\core\Mage\Core\Controller\Varien\Router\Standard.php(262): Mage_Core_Controller_Varien_Action->dispatch('reindexProcess')
#14 C:\xampp\htdocs\z\app\code\core\Mage\Core\Controller\Varien\Front.php(188): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#15 C:\xampp\htdocs\z\app\code\core\Mage\Core\Model\App.php(371): Mage_Core_Controller_Varien_Front->dispatch()
#16 C:\xampp\htdocs\z\app\Mage.php(748): Mage_Core_Model_App->run(Array)
#17 C:\xampp\htdocs\z\index.php(61): Mage::run('', 'store')
#18 {main}

[elidrissidev]

This issue is due to the latest release using zf1-future, which requires wrapping expressions like sum(qty_ordered)-sum(qty_refunded)-sum(qty_canceled) in a Zend_Db_Expr.

This could be fixed directly in the extension, but since it's not open-source I can't help with much. You can apply a fix similar to the one from #2864:

         $this->getSelect()
             ->columns([
-                'quantity' => 'COUNT(main_table.entity_id)',
+                'quantity' => new Zend_Db_Expr('COUNT(main_table.entity_id)'),
                 'range' => $tzRangeOffsetExpression,
             ])

[Jattura]

This issue is due to the latest release using zf1-future, which requires wrapping expressions like sum(qty_ordered)-sum(qty_refunded)-sum(qty_canceled) in a Zend_Db_Expr.

This could be fixed directly in the extension, but since it's not open-source I can't help with much. You can apply a fix similar to the one from #2864:

         $this->getSelect()
             ->columns([
-                'quantity' => 'COUNT(main_table.entity_id)',
+                'quantity' => new Zend_Db_Expr('COUNT(main_table.entity_id)'),
                 'range' => $tzRangeOffsetExpression,
             ])

Thank you for your reply. After I dig and search into the Qty.php, I guess this script appear problem

$select  = $adapter->select()
    ->from(array('e' => $this->getTable('sales/order_item')), array(
        'product_id',
        'sum(qty_ordered)-sum(qty_refunded)-sum(qty_canceled) as qty_sold'
    ))
    ->group('product_id');

I tried adding this script following your format and finding other information. It doesn't work. Would you mind helping me re-add the new Zend_Db_Expr function in this little script? Million thanks

[elidrissidev]

This patch should fix it:

 $select  = $adapter->select()
     ->from(array('e' => $this->getTable('sales/order_item')), array(
         'product_id',
-        'sum(qty_ordered)-sum(qty_refunded)-sum(qty_canceled) as qty_sold'
+        'qty_sold' => new Zend_Db_Expr('sum(qty_ordered)-sum(qty_refunded)-sum(qty_canceled)')
     ))
     ->group('product_id');

[Jattura]

This patch should fix it:

 $select  = $adapter->select()
     ->from(array('e' => $this->getTable('sales/order_item')), array(
         'product_id',
-        'sum(qty_ordered)-sum(qty_refunded)-sum(qty_canceled) as qty_sold'
+        'qty_sold' => new Zend_Db_Expr('sum(qty_ordered)-sum(qty_refunded)-sum(qty_canceled)')
     ))
     ->group('product_id');

it's OK now. Expert Mr. Elidrissidev. Thank you so so much! :)