在prestashop中使用SQL CTE函数时出现问题

问：

我在 prestashop 1.7.6.9 中使用 WITH 函数 （CTE） 在统计信息中制作附加功能时遇到了问题。

我的原始PHP代码（工作正常）：

$this->query = 'SELECT SQL_CALC_FOUND_ROWS p.reference, p.id_product, pl.name,
            ROUND(AVG(od.product_price / o.conversion_rate), 2) as avgPriceSold,
            LEFT(MAX(o.date_add), 10) AS date,
            IFNULL(stock.quantity, 0) as quantity,
            IFNULL(SUM(od.product_quantity), 0) AS totalQuantitySold,
            ROUND(IFNULL(SUM((od.product_price * od.product_quantity) / o.conversion_rate), 0), 2) AS totalPriceSold,
            1 + LEAST( TO_DAYS('.$array_date_between[1].'), TO_DAYS(NOW())) - GREATEST(TO_DAYS( '.$array_date_between[0].'), TO_DAYS(product_shop.date_add)) - IFNULL((
                SELECT COUNT(sh.quantity) FROM `ps1769_xcustom_stock_history` sh WHERE sh.quantity <= 0 AND sh.id = p.id_product AND sh.date BETWEEN '.$date_between.'
                GROUP BY sh.id
            ),0) AS AvailableDays,
            p.wholesale_price AS purchasePrice,
            product_shop.active
            FROM '._DB_PREFIX_.'product p
            '.Shop::addSqlAssociation('product', 'p').'
            LEFT JOIN '._DB_PREFIX_.'product_lang pl ON (p.id_product = pl.id_product AND pl.id_lang = '.(int)$this->getLang().' '.Shop::addSqlRestrictionOnLang('pl').')
            LEFT JOIN '._DB_PREFIX_.'order_detail od ON od.product_id = p.id_product
            LEFT JOIN '._DB_PREFIX_.'orders o ON od.id_order = o.id_order
            '.Shop::addSqlRestriction(Shop::SHARE_ORDER, 'o').'
            '.Product::sqlStock('p', 0).'
            WHERE o.valid = 1
            AND o.invoice_date BETWEEN '.$date_between.'
            GROUP BY od.product_id';

if (Validate::IsName($this->_sort)) {
        $this->query .= ' ORDER BY `'.bqSQL($this->_sort).'`';
        if (isset($this->_direction) && Validate::isSortDirection($this->_direction)) {
            $this->query .= ' '.$this->_direction;
        }
}

if (($this->_start === 0 || Validate::IsUnsignedInt($this->_start)) && Validate::IsUnsignedInt($this->_limit)) {
        $this->query .= ' LIMIT '.(int)$this->_start.', '.(int)$this->_limit;
}

我只是使用 WITH 函数添加一个表，而不使用它（仅用于测试），如下所示：

$this->query = 'WITH LastDates30 AS (
                   SELECT id, date, row_num
                   FROM
                        (
                            SELECT id, date,
                                ROW_NUMBER() OVER (PARTITION BY id ORDER BY date DESC) AS row_num
                            FROM
                                '._DB_PREFIX_.'xcustom_stock_history sh
                            WHERE
                                quantity > 0
                        ) AS subquery
                    WHERE row_num <= 30
                )
                SELECT SQL_CALC_FOUND_ROWS p.reference, p.id_product, pl.name,
                ROUND(AVG(od.product_price / o.conversion_rate), 2) as avgPriceSold,
                LEFT(MAX(o.date_add), 10) AS date,
                IFNULL(stock.quantity, 0) as quantity,
                IFNULL(SUM(od.product_quantity), 0) AS totalQuantitySold,
                ROUND(IFNULL(SUM((od.product_price * od.product_quantity) / o.conversion_rate), 0), 2) AS totalPriceSold,
                1 + LEAST( TO_DAYS('.$array_date_between[1].'), TO_DAYS(NOW())) - GREATEST(TO_DAYS( '.$array_date_between[0].'), TO_DAYS(product_shop.date_add)) - IFNULL((
                    SELECT COUNT(sh.quantity) FROM `ps1769_xcustom_stock_history` sh WHERE sh.quantity <= 0 AND sh.id = p.id_product AND sh.date BETWEEN '.$date_between.'
                    GROUP BY sh.id
                ),0) AS AvailableDays,
                p.wholesale_price AS purchasePrice,
                product_shop.active
                FROM '._DB_PREFIX_.'product p
                '.Shop::addSqlAssociation('product', 'p').'
                LEFT JOIN '._DB_PREFIX_.'product_lang pl ON (p.id_product = pl.id_product AND pl.id_lang = '.(int)$this->getLang().' '.Shop::addSqlRestrictionOnLang('pl').')
                LEFT JOIN '._DB_PREFIX_.'order_detail od ON od.product_id = p.id_product
                LEFT JOIN '._DB_PREFIX_.'orders o ON od.id_order = o.id_order
                '.Shop::addSqlRestriction(Shop::SHARE_ORDER, 'o').'
                '.Product::sqlStock('p', 0).'
                WHERE o.valid = 1
                AND o.invoice_date BETWEEN '.$date_between.'
                GROUP BY od.product_id';

    if (Validate::IsName($this->_sort)) {
        $this->query .= ' ORDER BY `'.bqSQL($this->_sort).'`';
        if (isset($this->_direction) && Validate::isSortDirection($this->_direction)) {
            $this->query .= ' '.$this->_direction;
        }
    }

    if (($this->_start === 0 || Validate::IsUnsignedInt($this->_start)) && Validate::IsUnsignedInt($this->_limit)) {
        $this->query .= ' LIMIT '.(int)$this->_start.', '.(int)$this->_limit;
    }

即使我不使用这个附加表，它也会返回空表:(

我在prestashop脚本之外测试了这个SQL查询，它工作正常。 我认为问题出在PHP中的CTE上，但我不知道为什么？