Tag Aggregation Data index process unknown error

Question

I seem to be having an issues indexing on Magento. All available indexes index fine except for one:

Tag Aggregation Data

When indexing through the following error, but I can't quite see what the issue is.

Tag Aggregation Data index process unknown error:
exception 'PDOException' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'COUNT(tr.customer_id) + MIN(IF(tp.base_popularity IS NOT NULL, tp.base_popularity, 0))' in 'field list'' in /mywebsite-domain/httpd.www/lib/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 /mywebsite-domain/httpd.www/lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /mywebsite-domain/httpd.www/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#2 /mywebsite-domain/httpd.www/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#3 /mywebsite-domain/httpd.www/lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#4 /mywebsite-domain/httpd.www/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO `ma...', Array)
#5 /mywebsite-domain/httpd.www/lib/Varien/Db/Adapter/Pdo/Mysql.php(504): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `ma...', Array)
#6 /mywebsite-domain/httpd.www/app/code/core/Mage/Tag/Model/Resource/Indexer/Summary.php(233): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO `ma...')
#7 /mywebsite-domain/httpd.www/app/code/core/Mage/Tag/Model/Resource/Indexer/Summary.php(135): Mage_Tag_Model_Resource_Indexer_Summary->aggregate()
#8 /mywebsite-domain/httpd.www/app/code/core/Mage/Index/Model/Indexer/Abstract.php(143): Mage_Tag_Model_Resource_Indexer_Summary->reindexAll()
#9 /mywebsite-domain/httpd.www/app/code/core/Mage/Index/Model/Process.php(210): Mage_Index_Model_Indexer_Abstract->reindexAll()
#10 /mywebsite-domain/httpd.www/app/code/core/Mage/Index/Model/Process.php(258): Mage_Index_Model_Process->reindexAll()
#11 /mywebsite-domain/httpd.www/shell/indexer.php(168): Mage_Index_Model_Process->reindexEverything()
#12 /mywebsite-domain/httpd.www/shell/indexer.php(216): Mage_Shell_Compiler->run()
#13 {main}

Next exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'COUNT(tr.customer_id) + MIN(IF(tp.base_popularity IS NOT NULL, tp.base_popularity, 0))' in 'field list', query was: INSERT INTO `magentotag_summary` (`tag_id`, `store_id`, `customers`, `products`, `popularity`, `uses`, `historical_uses`, `base_popularity`) SELECT `tr`.`tag_id`, `tr`.`store_id`, COUNT(DISTINCT tr.customer_id) AS `customers`, COUNT(DISTINCT tr.product_id) AS `products`, `COUNT(tr.customer_id) + MIN(IF(tp.base_popularity IS NOT NULL, tp`.`base_popularity, 0))` AS `popularity`, 0 AS `uses`, 0 AS `historical_uses`, 0 AS `base_popularity` FROM `magentotag_relation` AS `tr`
 INNER JOIN `magentocore_store` AS `cs` ON cs.store_id = tr.store_id
 INNER JOIN `magentocatalog_product_website` AS `pw` ON cs.website_id = pw.website_id AND tr.product_id = pw.product_id
 INNER JOIN `magentocatalog_product_entity` AS `e` ON tr.product_id = e.entity_id
 LEFT JOIN `magentotag_properties` AS `tp` ON tp.tag_id = tr.tag_id AND tp.store_id = tr.store_id
 INNER JOIN `magentocatalog_product_entity_int` AS `tad_status` ON tad_status.entity_id = e.entity_id AND tad_status.attribute_id = 273 AND tad_status.store_id = 0
 LEFT JOIN `magentocatalog_product_entity_int` AS `tas_status` ON tas_status.entity_id = e.entity_id AND tas_status.attribute_id = 273 AND tas_status.store_id = cs.store_id
 INNER JOIN `magentocatalog_product_entity_int` AS `tad_visibility` ON tad_visibility.entity_id = e.entity_id AND tad_visibility.attribute_id = 526 AND tad_visibility.store_id = 0
 LEFT JOIN `magentocatalog_product_entity_int` AS `tas_visibility` ON tas_visibility.entity_id = e.entity_id AND tas_visibility.attribute_id = 526 AND tas_visibility.store_id = cs.store_id
 INNER JOIN `magentocataloginventory_stock_status` AS `ciss` ON ciss.product_id = e.entity_id AND ciss.website_id = cs.website_id WHERE (tr.active = 1) AND (IF(IFNULL(tas_status.value_id, -1) > 0, tas_status.value, tad_status.value)=1) AND (IF(IFNULL(tas_visibility.value_id, -1) > 0, tas_visibility.value, tad_visibility.value)!=1) AND (ciss.stock_status = 1) GROUP BY `tr`.`tag_id`,
        `tr`.`store_id` ON DUPLICATE KEY UPDATE `tag_id` = VALUES(`tag_id`), `store_id` = VALUES(`store_id`), `customers` = VALUES(`customers`), `products` = VALUES(`products`), `popularity` = VALUES(`popularity`), `uses` = VALUES(`uses`), `historical_uses` = VALUES(`historical_uses`), `base_popularity` = VALUES(`base_popularity`)' in /mywebsite-domain/httpd.www/lib/Zend/Db/Statement/Pdo.php:235
Stack trace:
#0 /mywebsite-domain/httpd.www/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 /mywebsite-domain/httpd.www/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 /mywebsite-domain/httpd.www/lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 /mywebsite-domain/httpd.www/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO `ma...', Array)
#4 /mywebsite-domain/httpd.www/lib/Varien/Db/Adapter/Pdo/Mysql.php(504): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `ma...', Array)
#5 /mywebsite-domain/httpd.www/app/code/core/Mage/Tag/Model/Resource/Indexer/Summary.php(233): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO `ma...')
#6 /mywebsite-domain/httpd.www/app/code/core/Mage/Tag/Model/Resource/Indexer/Summary.php(135): Mage_Tag_Model_Resource_Indexer_Summary->aggregate()
#7 /mywebsite-domain/httpd.www/app/code/core/Mage/Index/Model/Indexer/Abstract.php(143): Mage_Tag_Model_Resource_Indexer_Summary->reindexAll()
#8 /mywebsite-domain/httpd.www/app/code/core/Mage/Index/Model/Process.php(210): Mage_Index_Model_Indexer_Abstract->reindexAll()
#9 /mywebsite-domain/httpd.www/app/code/core/Mage/Index/Model/Process.php(258): Mage_Index_Model_Process->reindexAll()
#10 /mywebsite-domain/httpd.www/shell/indexer.php(168): Mage_Index_Model_Process->reindexEverything()
#11 /mywebsite-domain/httpd.www/shell/indexer.php(216): Mage_Shell_Compiler->run()
#12 {main}

Any clues?

Thanks.

Answer 1

The faulty backticks are inserted because Zend_Db_Select->_tableCols() fails to detect COUNT(tr.customer_id) + MIN(IF(tp.base_popularity IS NOT NULL, tp.base_popularity, 0)) as some SQL function, which should pass it to Zend_Db_Expr() then. Instead, it is detected as [currentCorrelationName].[col] and then handled as ordinary string.

A fix for this issue is to loosen the regular expression for detecting SQL functions:

Open lib/Zend/Db/Select.php, find the following line and remove the string end symbol ($):

const REGEX_COLUMN_EXPR = '/^([\w]*\(([^\(\)]|(?1))*\))$/';

... so it looks like this one:

const REGEX_COLUMN_EXPR = '/^([\w]*\(([^\(\)]|(?1))*\))/';

Now, Zend_Db_Select->_tableCols() should pass the whole expression to Zend_Db_Expr(), and the SQL error should disappear.