[MDEV-23291] SUM column from a derived table returns invalid values Created: 2020-07-25  Updated: 2021-06-10  Resolved: 2021-02-16

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Subquery
Affects Version/s: 10.0, 10.1, 10.1.45, 10.2.32, 10.3.23, 10.4.13, 10.5.4, 10.2, 10.3, 10.4, 10.5
Fix Version/s: 10.2.38, 10.3.29, 10.4.19, 10.5.10

Type: Bug Priority: Major
Reporter: Marcos Alejandro Gallardo Assignee: Varun Gupta (Inactive)
Resolution: Fixed Votes: 0
Labels: None
Environment:

Platforms:

  • Windows 10 Pro - version 2004
  • CloudLinux 7.8

Issue Links:
Relates
relates to MDEV-19680 Assertion `!table || (!table->read_se... Stalled
relates to MDEV-19984 Assertion `!table || (!table->read_se... Confirmed

 Description   

Use this schema as example:

CREATE TABLE `products` (
 
  `product_id` INT,
 
  `code` VARCHAR(24),
 
  `price` DECIMAL(10,2),
 
  `avgOrderQuantity` INT
 
);
 
 
 
CREATE TABLE `orders` (
 
  `order_id` INT,
 
  `date` DATETIME DEFAULT CURRENT_TIMESTAMP,
 
  `state` ENUM('pending','confirmed','cancelled','completed')
 
);
 
 
 
CREATE TABLE `order_products` (
 
  `order_id` INT,
 
  `product_id` INT,
 
  `quantity` INT,
 
  `price` DECIMAL(10,2)
 
);
 
 
 
CREATE TABLE `invoices` (
 
  `invoice_id` INT,
 
  `order_id` INT,
 
  `date` DATETIME DEFAULT CURRENT_TIMESTAMP
 
);
 
 
 
CREATE TABLE `invoice_products` (
 
  `invoice_id` INT,
 
  `product_id` INT,
 
  `quantity` INT,
 
  `price` DECIMAL(10,2)
 
);
 
 
 
--
 
 
 
INSERT INTO `products` (`product_id`, `code`, `price`, `avgOrderQuantity`) VALUES
 
(1, 'COD1', 12, 3), (2, 'COD2', 11, 1);
 
 
 
INSERT INTO `orders` (`order_id`, `state`) VALUES
 
(1, 'confirmed'),
 
(2, 'confirmed'),
 
(3, 'pending');
 
 
 
INSERT INTO `order_products` (`order_id`, `product_id`, `quantity`, `price`) VALUES
 
(1, 1, 30, 10.2),
 
(2, 1, 10, 12), (2, 2, 2, 11),
 
(3, 1, 10, 12);
 
 
 
INSERT INTO `invoices` (`invoice_id`, `order_id`) VALUES
 
(1, 1),
 
(2, 2),
 
(3, 1);
 
 
 
INSERT INTO `invoice_products` (`invoice_id`, `product_id`, `quantity`, `price`) VALUES
 
(1, 1, 10, 10.2),
 
(2, 1, 20, 12), (2, 2, 2, 11),
 
(3, 1, 1, 10.2);

If we run this query:

  SELECT
 
    P.code,
 
    O.state,
 
    OP.quantity AS ordered,
 
    IFNULL((
 
      SELECT SUM(IP.quantity)
 
      FROM invoices AS I
 
        LEFT JOIN invoice_products AS IP
 
          ON IP.invoice_id = I.invoice_id
 
      WHERE I.order_id = O.order_id
 
        AND IP.product_id = OP.product_id
 
    ), 0) AS invoiced,
 
    IF(O.state = 'confirmed', (SELECT ordered), (SELECT invoiced)) AS sold,
 
    P.avgOrderQuantity AS avgOrdQty,
 
    IF((SELECT sold) > (SELECT avgOrdQty), (SELECT avgOrdQty), (SELECT sold)) AS avgSold
 
  FROM order_products AS OP
 
    INNER JOIN orders AS O
 
      ON O.order_id = OP.order_id
 
    INNER JOIN products AS P
 
      ON P.product_id = OP.product_id
 
  WHERE O.state != 'pending';

It returns:

code state ordered invoiced sold avgOrdQty avgSold
COD1 confirmed 30 11 30 3 3
COD1 confirmed 10 20 10 3 3
COD2 confirmed 2 2 2 1 1

Now If move this into a derived table to get the sumarized `avgSold` grouped by `code` like this

SELECT T.code, SUM(T.avgSold)
 
FROM (
 
  SELECT
 
    P.code,
 
    O.state,
 
    OP.quantity AS ordered,
 
    IFNULL((
 
      SELECT SUM(IP.quantity)
 
      FROM invoices AS I
 
        LEFT JOIN invoice_products AS IP
 
          ON IP.invoice_id = I.invoice_id
 
      WHERE I.order_id = O.order_id
 
        AND IP.product_id = OP.product_id
 
    ), 0) AS invoiced,
 
    IF(O.state = 'confirmed', (SELECT ordered), (SELECT invoiced)) AS sold,
 
    P.avgOrderQuantity AS avgOrdQty,
 
    IF((SELECT sold) > (SELECT avgOrdQty), (SELECT avgOrdQty), (SELECT sold)) AS avgSold
 
  FROM order_products AS OP
 
    INNER JOIN orders AS O
 
      ON O.order_id = OP.order_id
 
    INNER JOIN products AS P
 
      ON P.product_id = OP.product_id
 
  WHERE O.state != 'pending'
 
) AS T
 
GROUP BY T.code

It returns:

code SUM(T.avgSold)
COD1 null
COD2 null

This is NOT the expected result.

If I add a numeric column (YES it MUST be a numeric column) like this:

SELECT T.code, T.sold, SUM(T.avgSold)
 
FROM (
 
  SELECT
 
    P.code,
 
    O.state,
 
    OP.quantity AS ordered,
 
    IFNULL((
 
      SELECT SUM(IP.quantity)
 
      FROM invoices AS I
 
        LEFT JOIN invoice_products AS IP
 
          ON IP.invoice_id = I.invoice_id
 
      WHERE I.order_id = O.order_id
 
        AND IP.product_id = OP.product_id
 
    ), 0) AS invoiced,
 
    IF(O.state = 'confirmed', (SELECT ordered), (SELECT invoiced)) AS sold,
 
    P.avgOrderQuantity AS avgOrdQty,
 
    IF((SELECT sold) > (SELECT avgOrdQty), (SELECT avgOrdQty), (SELECT sold)) AS avgSold
 
  FROM order_products AS OP
 
    INNER JOIN orders AS O
 
      ON O.order_id = OP.order_id
 
    INNER JOIN products AS P
 
      ON P.product_id = OP.product_id
 
  WHERE O.state != 'pending'
 
) AS T
 
GROUP BY T.code

It returns:

code sold SUM(T.avgSold)
COD1 30 6
COD2 2 1

NOW this is the expected result

Here you can find a working demo

 Comments   
Comment by Alice Sherepa [ 2020-07-27 ]

Thanks a lot!
I repeated as described. On debug versions on 10.0-10.5 test fails with the assertion 

`!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))'
 
`!table || (!table->read_set || bitmap_is_set(table->read_set, field_index) || (!(ptr >= table->record[0] && ptr < table->record[0] + table->s->reclength)))' 
`marked_for_read()'

5.5 returns correct results.

10.0 c2a7dffc573f311029ed4696f

 
Version: '10.0.40-4-MariaDB-debug' 
mysqld: /10.0/sql/field.cc:3798: virtual longlong Field_long::val_int(): Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed.
 
200727  9:21:02 [ERROR] mysqld got signal 6 ;
 
 
 
Server version: 10.0.40-4-MariaDB-debug
 
 
 
linux/raise.c:51(__GI_raise)[0x7f54491a67bb]
 
stdlib/abort.c:81(__GI_abort)[0x7f5449191535]
 
intl/loadmsgcat.c:1177(_nl_load_domain)[0x7f544919140f]
 
/lib/x86_64-linux-gnu/libc.so.6(+0x30102)[0x7f544919f102]
 
sql/field.cc:3801(Field_long::val_int())[0x556f7e2a44ec]
 
sql/item.cc:2607(Item_field::val_int())[0x556f7e2df94e]
 
sql/item.cc:7446(Item_direct_ref::val_int())[0x556f7e2ec313]
 
sql/item.cc:7222(Item_ref::val_int_result())[0x556f7e2eb6e0]
 
sql/item.cc:8844(Item_cache_int::cache_value())[0x556f7e2f00bf]
 
sql/item_subselect.cc:1125(Item_singlerow_subselect::store(unsigned int, Item*))[0x556f7e362628]
 
sql/sql_class.cc:3069(select_singlerow_subselect::send_data(List<Item>&))[0x556f7e0a03a5]
 
sql/sql_select.cc:2496(JOIN::exec_inner())[0x556f7e113426]
 
sql/sql_select.cc:2413(JOIN::exec())[0x556f7e112e40]
 
sql/item_subselect.cc:3774(subselect_single_select_engine::exec())[0x556f7e36a990]
 
sql/item_subselect.cc:678(Item_subselect::exec())[0x556f7e36142a]
 
sql/item_subselect.cc:1298(Item_singlerow_subselect::val_decimal(my_decimal*))[0x556f7e362eb5]
 
sql/item_cmpfunc.cc:2875(Item_func_if::decimal_op(my_decimal*))[0x556f7e3022a0]
 
sql/item_func.h:453(Item_func_hybrid_result_type::decimal_op_with_null_check(my_decimal*))[0x556f7e33cf2a]
 
sql/item_func.cc:1040(Item_func_hybrid_result_type::val_decimal(my_decimal*))[0x556f7e32926a]
 
sql/item.cc:7462(Item_direct_ref::val_decimal(my_decimal*))[0x556f7e2ec3e2]
 
sql/item.cc:7247(Item_ref::val_decimal_result(my_decimal*))[0x556f7e2eb856]
 
sql/item.cc:9081(Item_cache_decimal::cache_value())[0x556f7e2f0ccc]
 
sql/item_subselect.cc:1125(Item_singlerow_subselect::store(unsigned int, Item*))[0x556f7e362628]
 
sql/sql_class.cc:3069(select_singlerow_subselect::send_data(List<Item>&))[0x556f7e0a03a5]
 
sql/sql_select.cc:2496(JOIN::exec_inner())[0x556f7e113426]
 
sql/sql_select.cc:2413(JOIN::exec())[0x556f7e112e40]
 
sql/item_subselect.cc:3774(subselect_single_select_engine::exec())[0x556f7e36a990]
 
sql/item_subselect.cc:678(Item_subselect::exec())[0x556f7e36142a]
 
sql/item_subselect.cc:1298(Item_singlerow_subselect::val_decimal(my_decimal*))[0x556f7e362eb5]
 
sql/item_cmpfunc.cc:1125(Arg_comparator::compare_decimal())[0x556f7e2fc0d5]
 
sql/item_cmpfunc.h:80(Arg_comparator::compare())[0x556f7e30d7e8]
 
sql/item_cmpfunc.cc:2052(Item_func_gt::val_int())[0x556f7e2fedc7]
 
sql/item.cc:213(Item::val_bool())[0x556f7e2d93c0]
 
sql/item_cmpfunc.cc:2874(Item_func_if::decimal_op(my_decimal*))[0x556f7e302258]
 
sql/item_func.h:453(Item_func_hybrid_result_type::decimal_op_with_null_check(my_decimal*))[0x556f7e33cf2a]
 
sql/item_func.cc:1040(Item_func_hybrid_result_type::val_decimal(my_decimal*))[0x556f7e32926a]
 
sql/item.cc:7462(Item_direct_ref::val_decimal(my_decimal*))[0x556f7e2ec3e2]
 
sql/item.h:3849(Item_direct_view_ref::val_decimal(my_decimal*))[0x556f7e2f6210]
 
sql/item_sum.cc:2336(Item_sum_sum::reset_field())[0x556f7e378aa4]
 
sql/sql_select.cc:22858(init_tmptable_sum_functions(Item_sum**))[0x556f7e145958]
 
sql/sql_select.cc:19507(end_update(JOIN*, st_join_table*, bool))[0x556f7e13d82a]
 
sql/sql_join_cache.cc:2409(JOIN_CACHE::generate_full_extensions(unsigned char*))[0x556f7e22d76c]
 
sql/sql_join_cache.cc:2301(JOIN_CACHE::join_matching_records(bool))[0x556f7e22d4fe]
 
sql/sql_join_cache.cc:2097(JOIN_CACHE::join_records(bool))[0x556f7e22ceb9]
 
sql/sql_join_cache.cc:2143(JOIN_CACHE::join_records(bool))[0x556f7e22d059]
 
sql/sql_select.cc:17850(sub_select_cache(JOIN*, st_join_table*, bool))[0x556f7e139944]
 
sql/sql_select.cc:18020(sub_select(JOIN*, st_join_table*, bool))[0x556f7e139b50]
 
sql/sql_select.cc:17731(do_select(JOIN*, List<Item>*, TABLE*, Procedure*))[0x556f7e139639]
 
sql/sql_select.cc:2665(JOIN::exec_inner())[0x556f7e113d73]
 
sql/sql_select.cc:2413(JOIN::exec())[0x556f7e112e40]
 
sql/sql_select.cc:3352(mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x556f7e1161bd]
 
sql/sql_select.cc:366(handle_select(THD*, LEX*, select_result*, unsigned long))[0x556f7e10c2ac]
 
sql/sql_parse.cc:5312(execute_sqlcom_select(THD*, TABLE_LIST*))[0x556f7e0e0038]
 
sql/sql_parse.cc:2554(mysql_execute_command(THD*))[0x556f7e0d838d]
 
sql/sql_parse.cc:6648(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x556f7e0e2e6b]
 
sql/sql_parse.cc:1303(dispatch_command(enum_server_command, THD*, char*, unsigned int))[0x556f7e0d5635]
 
sql/sql_parse.cc:1003(do_command(THD*))[0x556f7e0d495f]
 
sql/sql_connect.cc:1377(do_handle_one_connection(THD*))[0x556f7e1f7358]
 
sql/sql_connect.cc:1293(handle_one_connection)[0x556f7e1f70b6]
 
perfschema/pfs.cc:1863(pfs_spawn_thread)[0x556f7e5c826f]
 
nptl/pthread_create.c:487(start_thread)[0x7f5449b7bfa3]
 
x86_64/clone.S:97(clone)[0x7f54492684cf]
 
 
 
Query (0x7f54409f0088): SELECT T.code, SUM(T.avgSold) FROM ( SELECT P.code, O.state, OP.quantity AS ordered, IFNULL(( SELECT SUM(IP.quantity) FROM invoices AS I LEFT JOIN invoice_products AS IP ON IP.invoice_id = I.invoice_id WHERE I.order_id = O.order_id AND IP.product_id = OP.product_id ), 0) AS invoiced, IF(O.state = 'confirmed', (SELECT ordered), (SELECT invoiced)) AS sold, P.avgOrderQuantity AS avgOrdQty, IF((SELECT sold) > (SELECT avgOrdQty), (SELECT avgOrdQty), (SELECT sold)) AS avgSold FROM order_products AS OP INNER JOIN orders AS O ON O.order_id = OP.order_id INNER JOIN products AS P ON P.product_id = OP.product_id WHERE O.state != 'pending' ) AS T GROUP BY T.code

Comment by Varun Gupta (Inactive) [ 2020-08-05 ]

A simpler test case that fails on debug builds

CREATE TABLE t1(a INT, b INT);
 
INSERT INTO t1 VALUES (1,1), (2,2);
 
 
 
CREATE view v1 AS SELECT a as x, (select x) as y, (select y) as z FROM t1;
 
SELECT sum(z) FROM v1;

Comment by Varun Gupta (Inactive) [ 2020-08-05 ]

So for the query:
CREATE view v1 AS SELECT a as x, (select x) as y, (select y) as z FROM t1;

SELECT sum(z) FROM v1;

we we walk over the arguments of the SUM function.

(lldb) p dbug_print_item(this)
 
(const char *) $0 = 0x00000001022bdc40 "(subquery#4)"
 
(lldb) p this
 
(Item_direct_view_ref *) $1 = 0x000062900006f070

The item z is a reference so we walk to the item it is referring to

(lldb) p *ref
 
(Item_singlerow_subselect *) $4 = 0x000062b000011fd0
 
(lldb) p dbug_print_item(*ref)
 
(const char *) $5 = 0x00000001022bdc40 "(subquery#4)"

item z is referring to SELECT#4

Then we walk the upper references in the SELECT #4, that would be item y

(lldb) p dbug_print_item(this)
 
(const char *) $6 = 0x00000001022bdc40 "y"
 
(lldb) p this
 
(Item_direct_ref *) $7 = 0x000062b000014050

item y is again a reference to SELECT#3

(lldb) p dbug_print_item(*ref)
 
(const char *) $9 = 0x00000001022bdc40 "(subquery#3)"
 
(lldb) p *ref
 
(Item_singlerow_subselect *) $10 = 0x000062b000011208

So here we end up with (select x) as y termed as SELECT#3
For SELECT #3 we should again walk over the references from outer select that is used inside it
that is we need to check in subquery#3 if there is outer reference then we need to set the bitmaps
by walking to the original item wrapped by the reference.
When we reach the original item just update the fields inside the original item.
In this case the original item is a and then we just update the table bitmaps for column a.

Comment by Oleksandr Byelkin [ 2020-08-06 ]

If we take such simplified example:

CREATE TABLE t1(a INT, b INT);
 
INSERT INTO t1 VALUES (1,1), (2,2);
 
SELECT a as x, x as y, y as z FROM t1;
 
main.test                                [ fail ]
 
        Test ended at 2020-08-06 10:57:49
 
 
 
CURRENT_TEST: main.test
 
mysqltest: At line 3: query 'SELECT a as x, x as y, y as z FROM t1' failed: 1054: Unknown column 'x' in 'field list'

we found that it is not allowed to reference select list from other select list element. So even in user example we shoud have "ordered" as unknown.

So one bug is in not returning an error in case of select list name reference.

Also try use table reference in user examle and if the bug will be present it shoud also be fixed (second part) but I think it will not,

Comment by Oleksandr Byelkin [ 2020-12-30 ]

OK, it looks like we supported the name resolution in the subquery so have to keep it.

Comment by Oleksandr Byelkin [ 2021-02-10 ]

OK to push

Generated at Thu Feb 08 09:21:18 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.