Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-23291

SUM column from a derived table returns invalid values

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.1.45, 10.2.32, 10.3.23, 10.4.13, 10.5.4, 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5
    • 10.2.38, 10.3.29, 10.4.19, 10.5.10
    • None
    • Platforms:
       - Windows 10 Pro - version 2004
       - CloudLinux 7.8

    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

      Attachments

        Issue Links

          Activity

            alice Alice Sherepa added a comment -

            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
            

            alice Alice Sherepa added a comment - 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

            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;
            

            varun Varun Gupta (Inactive) added a comment - 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;
            varun Varun Gupta (Inactive) added a comment - - edited

            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.

            varun Varun Gupta (Inactive) added a comment - - edited 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.

            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,

            sanja Oleksandr Byelkin added a comment - 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,

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

            sanja Oleksandr Byelkin added a comment - OK, it looks like we supported the name resolution in the subquery so have to keep it.

            OK to push

            sanja Oleksandr Byelkin added a comment - OK to push

            People

              varun Varun Gupta (Inactive)
              socramg Marcos Alejandro Gallardo
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.