0

I am having an issue migrating from MySQL to MariaDB 10.11.

My query gets an order by an id and calculates the balance for the order by summing the transactions/taxes and discounts via a select subquery. It works in the production environment in MySQL 5.7+

This query with SUM subquery in the select statement, works perfectly fine in MySQL 5.7+, but fails in MariaDB 10.11

select `orders`.`id`,
       `orders`.*,
       DATE_FORMAT(orders.created_at, "%Y%m") as partition_period,
       SUM(IF(orders.status = '9', 0,
              CONVERT((SELECT ROUND(SUM(b), 0)
                       FROM (SELECT ROUND(SUM((item.price + item.tax_amount + item.discount) * item.quantity), 0) as b
                             FROM order_items item
                             where item.order_id = orders.id
                               AND item.status = 1
                               AND item.deleted_at IS NULL
                             UNION ALL
                             SELECT -ROUND((SUM(IF(pt.amount IS NULL, 0, pt.amount))), 0) as b
                             FROM tags as mt_transactions
                                      JOIN payment_transactions as pt ON pt.id = mt_transactions.source_id
                             WHERE mt_transactions.source_type = '46'
                               AND mt_transactions.target_type = '7'
                               AND mt_transactions.thru_type = '0'
                               AND mt_transactions.thru_id = '0'
                               AND mt_transactions.status = 1
                               AND mt_transactions.target_id = orders.id
                               AND pt.status_id IN ('1')
                               AND pt.deleted_at IS NULL) as a), SIGNED INTEGER))) as balance
from `orders`
where `orders`.`id` = 124965
  and `orders`.`deleted_at` is null
limit 1

The error that I get in MariaDB is

 Unknown column 'orders.id' in 'where clause'

Which points to the where clauses in the SUM() subqueries, not in the outer where.

The question is, why MariaDB is unable to access the outer query columns while MySQL can do it without a problem, is there a workaround for MariaDB to behave as MySQL?

Thank you

Edit here is the SHOW CREATE TABLE for tables involved: Orders:

CREATE TABLE `orders` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `invoice_number` varchar(255) DEFAULT NULL,
  `details` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`details`)),
  `status` tinyint(4) NOT NULL DEFAULT 0 COMMENT '''draft''=>0, ''hold''=>1, ''in_progress_incomplete''=>2, ''in_progress''=>3, ''cart''=>4, ''test''=>5, ''completed''=>7, ''deleted''=>9',
  `balance_due_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `submitted_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`,`status`),
  KEY `submitted_at` (`submitted_at`),
  KEY `updated_at` (`updated_at`)
) ENGINE=InnoDB AUTO_INCREMENT=125127 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
 PARTITION BY KEY (`status`)
(PARTITION `p0` ENGINE = InnoDB,
 PARTITION `p1` ENGINE = InnoDB,
 PARTITION `p2` ENGINE = InnoDB,
 PARTITION `p3` ENGINE = InnoDB,
 PARTITION `p4` ENGINE = InnoDB,
 PARTITION `p5` ENGINE = InnoDB,
 PARTITION `p6` ENGINE = InnoDB,
 PARTITION `p7` ENGINE = InnoDB,
 PARTITION `p8` ENGINE = InnoDB,
 PARTITION `p9` ENGINE = InnoDB)

Payment transactions

CREATE TABLE `payment_transactions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `source_id` int(11) NOT NULL,
  `parent_id` bigint(20) DEFAULT NULL,
  `account_id` int(11) NOT NULL,
  `amount` bigint(20) NOT NULL,
  `currency_id` int(11) NOT NULL,
  `status_id` int(11) NOT NULL COMMENT '0 = pending, 1 = complete, 2 = authorized, 3 = our_pending, 8 = canceled, 9 = failed',
  `type` tinyint(1) NOT NULL COMMENT '1 = charge, 2 = refund, 3 = transfer incoming, 4 = transfer outgoing',
  `details` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`details`)),
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id` (`id`,`status_id`,`currency_id`,`amount`,`deleted_at`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=8655 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Order items:

CREATE TABLE `order_items` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` bigint(20) unsigned NOT NULL,
  `currency_id` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  `price` decimal(19,4) NOT NULL DEFAULT 0.0000 COMMENT 'Price per unit in cents',
  `discount` decimal(19,4) NOT NULL DEFAULT 0.0000 COMMENT 'discount per unit in cents',
  `tax_amount` decimal(19,4) NOT NULL DEFAULT 0.0000 COMMENT 'per unit in cents',
  `details` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`details`)),
  `status` tinyint(1) NOT NULL DEFAULT 1 COMMENT '0 or 1, 1=active, 0 = inactive',
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `fulfill_at` timestamp NULL DEFAULT NULL,
  `partition_period` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`,`partition_period`),
  KEY `order_id` (`order_id`,`status`,`deleted_at`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=146521 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
 PARTITION BY KEY (`partition_period`)
(PARTITION `p0` ENGINE = InnoDB,
 PARTITION `p1` ENGINE = InnoDB,
 PARTITION `p2` ENGINE = InnoDB
)
4
  • Is id the PRIMARY KEY of orders? If so, there are several simplifications to perform. Please provide SHOW CREATE TABLE for the tables.
    – Rick James
    Commented Apr 25, 2023 at 18:20
  • A side note: The nesting of ROUNDs and SUMs may produce a different result than if you left out the inner ROUNDs. (or the outer). The "CONVERT" may be unnecessary.
    – Rick James
    Commented Apr 25, 2023 at 18:28
  • @RickJames the primary key for the orders table is (id,status), see my edits to see the result of SHOW CREATE TABLE output for all tables involved in the query. Commented Apr 25, 2023 at 23:45
  • @RickJames And, yes I am aware I can get rid of the extra convert and sum functions, but that is not the root of the problem right now, it's the inability of MariaDB to access the outer select column inside the subquery. Any solution for this? Commented Apr 25, 2023 at 23:46

1 Answer 1

0

I think there was an issue with the SQL Standard. MySQL and MariaDB diverged -- one went with the Standard; the other did something different.

I do not understand why they would not allow orders.id at the "same level" as the definition of it, but it is simply a restriction.

It may be possible to turn the subquery into a JOIN, which is very likely to have no problems with "Unknown column".

id is declared AUTO_INCREMENT, but, because of Partitioning, it is possible that multiple rows contain the same id. Is this the case in your dataset.

Meanwhile, I suspect that Partitioning is not providing any performance benefit.

JOIN

Maybe something like

SELECT ..., ROUND(SUM(s1) + x.s2)
    FROM orders
    JOIN order items AS item  ON ...
    JOIN ( SELECT ..., SUM(...) AS s2
             FROM tags as mt_transactions
             JOIN payment_transactions as pt ON ...
             WHERE order.id = 124965
             ...
         ) AS x  ON ...
    ...
2
  • thank you, that's what I thought about the divergence, any ideas how to rewrite it as a join? I will try to do it tonight. Commented Apr 27, 2023 at 15:01
  • @KalkiTruth - I could not unravel your query, but I made a stab at it.
    – Rick James
    Commented Apr 27, 2023 at 15:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.