Unknown column 'at_special_from_date_default.value' in 'on clause'

[luciaebizmarts]

Will update when found more information

Preconditions

1. Mailchimp for Magento 1.1.16
2. Mgento any version

Ecommerca cronjob fail with the next message:

a:5:{i:0;s:2246:"SELECT e., IF(at_special_price.value_id > 0, at_special_price.value, at_special_price_default.value) AS special_price, at_special_from_date.value AS special_from_date, at_special_to_date.value AS special_to_date, m4m.related_id, m4m.type, m4m.mailchimp_store_id, m4m.mailchimp_sync_delta, m4m.mailchimp_sync_modified FROM catalog_product_entity AS e
INNER JOIN catalog_product_website AS product_website ON product_website.product_id = e.entity_id AND product_website.website_id = '1'
LEFT JOIN catalog_product_entity_decimal AS at_special_price_default ON (at_special_price_default.entity_id = e.entity_id) AND (at_special_price_default.attribute_id = '76') AND at_special_price_default.store_id = 0
LEFT JOIN catalog_product_entity_decimal AS at_special_price ON (at_special_price.entity_id = e.entity_id) AND (at_special_price.attribute_id = '76') AND (at_special_price.store_id = 1)
LEFT JOIN catalog_product_entity_datetime AS at_special_from_date ON (at_special_from_date.entity_id = e.entity_id) AND (at_special_from_date.attribute_id = '77') AND (at_special_from_date.store_id = 0)
LEFT JOIN catalog_product_entity_datetime AS at_special_to_date ON (at_special_to_date.entity_id = e.entity_id) AND (at_special_to_date.attribute_id = '78') AND (at_special_to_date.store_id = 0)
LEFT JOIN mailchimp_ecommerce_sync_data AS m4m ON m4m.related_id = e.entity_id AND m4m.type = 'PRO' AND m4m.mailchimp_store_id = '3a7a579d3c8624f23c21166e35a65dab'AND m4m.mailchimp_sync_modified = 0 WHERE (IF(at_special_price.value_id > 0, at_special_price.value, at_special_price_default.value) IS NOT NULL) AND (at_special_from_date.value IS NOT NULL) AND (((IF(at_special_from_date.value_id > 0, at_special_from_date.value, at_special_from_date_default.value) 0, at_special_from_date.value, at_special_from_date_default.value)) OR (IF(at_special_to_date.value_id > 0, at_special_to_date.value, at_special_to_date_default.value) < '2019-05-14 00:00:00' AND m4m.mailchimp_sync_delta < IF(at_special_to_date.value_id > 0, at_special_to_date.value, at_special_to_date_default.value)) AND mailchimp_sync_delta IS NOT NULL))
";i:1;s:3520:"#0
/www/httpdocs/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1/www/httpdocs/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 /www/httpdocs/lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 /www/httpdocs/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT e., I...', Array)
#4 /www/httpdocs/lib/Varien/Db/Adapter/Pdo/Mysql.php(504): Zend_Db_Adapter_Pdo_Abstract->query('SELECT e., I...', Array)
#5 /www/httpdocs/lib/Zend/Db/Adapter/Abstract.php(737): Varien_Db_Adapter_Pdo_Mysql->query('SELECT e., I...', Array)
#6 /www/httpdocs/lib/Varien/Data/Collection/Db.php(740): Zend_Db_Adapter_Abstract->fetchAll('SELECT e., I...', Array)
#7 /www/httpdocs/app/code/core/Mage/Eav/Model/Entity/Collection/Abstract.php(1046): Varien_Data_Collection_Db->_fetchAll('SELECT e., I...')
#8 /www/httpdocs/app/code/core/Mage/Eav/Model/Entity/Collection/Abstract.php(871): Mage_Eav_Model_Entity_Collection_Abstract->_loadEntities(false, false)
#9 /www/httpdocs/lib/Varien/Data/Collection.php(741): Mage_Eav_Model_Entity_Collection_Abstract->load()
#10 /www/httpdocs/app/code/community/Ebizmarts/MailChimp/Model/Api/Products.php(992): Varien_Data_Collection->getIterator()
#11 /www/httpdocs/app/code/community/Ebizmarts/MailChimp/Model/Api/Products.php(54): Ebizmarts_MailChimp_Model_Api_Products->_markSpecialPrices('3a7a579d3c8624f...', '1')
#12 /www/httpdocs/app/code/community/Ebizmarts/MailChimp/Model/Api/Batches.php(316): Ebizmarts_MailChimp_Model_Api_Products->createBatchJson('3a7a579d3c8624f...', '1')
#13 /www/httpdocs/app/code/community/Ebizmarts/MailChimp/Model/Api/Batches.php(203): Ebizmarts_MailChimp_Model_Api_Batches->_sendEcommerceBatch('1')
#14 /www/httpdocs/app/code/community/Ebizmarts/MailChimp/Model/Cron.php(28): Ebizmarts_MailChimp_Model_Api_Batches->handleEcommerceBatches()
#15 [internal function]: Ebizmarts_MailChimp_Model_Cron->syncEcommerceBatchData(Object(Aoe_Scheduler_Model_Schedule))
#16 /www/httpdocs/app/code/community/Aoe/Scheduler/Model/Schedule.php(213): call_user_func_array(Array, Array)
#17 /www/httpdocs/app/code/community/Aoe/Scheduler/controllers/Adminhtml/JobController.php(105): Aoe_Scheduler_Model_Schedule->runNow(false)
#18 /www/httpdocs/app/code/core/Mage/Core/Controller/Varien/Action.php(418): Aoe_Scheduler_Adminhtml_JobController->runNowAction()
#19 /www/httpdocs/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(254): Mage_Core_Controller_Varien_Action->dispatch('runNow')
#20 /www/httpdocs/app/code/core/Mage/Core/Controller/Varien/Front.php(172): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#21 /www/httpdocs/app/code/core/Mage/Core/Model/App.php(365): Mage_Core_Controller_Varien_Front->dispatch()
#22 /www/httpdocs/app/Mage.php(689): Mage_Core_Model_App->run(Array)
#23 /www/httpdocs/index.php(83): Mage::run('', 'store')
#24 {main}";s:3:"url";s:66:"/index.php/_5taff/job/runNow/key/0fb3cbc07e37b075d346bb769980c015/";s:11:"script_name";s:10:"/index.php";s:4:"skin";s:7:"ukstore";}

[TroyPatteson]

This occurs because the function joinMailchimpSyncData() in app/code/community/Ebizmarts/MailChimp/Model/Api/Products.php expects there to be a default value for the special_from_date and special_to_date attributes which only occurs if they are not using the global scope. This can be seen in the core magento function _joinAttributeToSelect() in app/code/core/Mage/Catalog/Model/Resource/Collection/Abstract.php:

        if ($store_id != $this->getDefaultStoreId() && !$attribute->isScopeGlobal()) {
            /**
             * Add joining default value for not default store
             * if value for store is null - we use default value
             */

On my development system the special_from_date attribute scope is not global and therefore the default value is available in the query as at_special_from_date_default.value but the special_to_date attribute scope is global and therefore the default value at_special_to_date_default.value is not available.

On my system I fixed this by first checking whether the default values are available before referencing them. To do this I changed joinMailchimpSyncData() in app/code/community/Ebizmarts/MailChimp/Model/Api/Products.php from:

    public function joinMailchimpSyncData($collection, $mailchimpStoreId, $isForSpecialPrice = false)
    {
        $whereCreateBatchJson = "m4m.mailchimp_sync_delta IS null OR m4m.mailchimp_sync_modified = 1";
        $this->joinMailchimpSyncDataWithoutWhere($collection, $mailchimpStoreId, $isForSpecialPrice);
        if ($isForSpecialPrice) {
            $whereMarkSpecialPrice = new Zend_Db_Expr("((IF(at_special_from_date.value_id > 0, at_special_from_date.value, at_special_from_date_default.value) <= '" . date('Y-m-d', time()) . " 23:59:59' AND m4m.mailchimp_sync_delta <  IF(at_special_from_date.value_id > 0, at_special_from_date.value, at_special_from_date_default.value)) OR (IF(at_special_to_date.value_id > 0, at_special_to_date.value, at_special_to_date_default.value) < '" . date('Y-m-d', time()) . "  00:00:00' AND m4m.mailchimp_sync_delta <  IF(at_special_to_date.value_id > 0, at_special_to_date.value, at_special_to_date_default.value)) AND mailchimp_sync_delta IS NOT NULL)");
            $collection->getSelect()->where($whereMarkSpecialPrice);
        } else {
            $collection->getSelect()->where($whereCreateBatchJson);
        }
    }

to

    public function joinMailchimpSyncData($collection, $mailchimpStoreId, $isForSpecialPrice = false)
    {
        $whereCreateBatchJson = "m4m.mailchimp_sync_delta IS null OR m4m.mailchimp_sync_modified = 1";
        $this->joinMailchimpSyncDataWithoutWhere($collection, $mailchimpStoreId, $isForSpecialPrice);
        if ($isForSpecialPrice) {
            $sql = (string)$collection->getSelect();
            $specialFromSql = preg_match('/at_special_from_date_default/', $sql) ? 'IF(at_special_from_date.value_id > 0, at_special_from_date.value, at_special_from_date_default.value)' : 'at_special_from_date.value_id';
            $specialToSql = preg_match('/at_special_to_date_default/', $sql) ? 'IF(at_special_to_date.value_id > 0, at_special_to_date.value, at_special_to_date_default.value)' : 'at_special_to_date.value';
            $whereMarkSpecialPrice = new Zend_Db_Expr("(($specialFromSql <= '" . date('Y-m-d', time()) . " 23:59:59' AND m4m.mailchimp_sync_delta < $specialFromSql) OR ($specialToSql < '" . date('Y-m-d', time()) . " 00:00:00' AND m4m.mailchimp_sync_delta < $specialToSql) AND mailchimp_sync_delta IS NOT NULL)");
            $collection->getSelect()->where($whereMarkSpecialPrice);
        } else {
            $collection->getSelect()->where($whereCreateBatchJson);
        }
    }