[MDEV-29585] Incorrect output of SELECT with CTE Created: 2022-09-21  Updated: 2023-11-28

Status: Confirmed
Project: MariaDB Server
Component/s: Optimizer - Window functions
Affects Version/s: 10.10.1, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11, 11.0
Fix Version/s: 10.4, 10.5, 10.6, 10.11, 11.0

Type: Bug Priority: Major
Reporter: Zuming Jiang Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Ubuntu 20.04

Issue Links:
Relates
relates to MDEV-23364 Wrong results or crash in Field::is_n... Closed

 Description   

I used my fuzzing tool to test MariaDB and found a logic bug that make the server produce incorrect results.

MariaDB installation
1) cd mariadb-10.10.1
2) mkdir build; cd build
3) cmake .. -DCMAKE_BUILD_TYPE=Debug -DWITH_ASAN=ON
4) make -j12 && sudo make install

Setup the environment
1) /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql &
2) /usr/local/mysql/bin/mysql -uroot
3) mysql> create database testdb;

Trigger the bug
/usr/local/mysql/bin/mysql --force -uroot -Dtestdb

CREATE TABLE `t_rry5a` (
 
  `wkey` int(11) DEFAULT NULL,
 
  `pkey` int(11) NOT NULL,
 
  `c_t4jlkc` int(11) DEFAULT NULL,
 
  `c_a047t` text DEFAULT NULL,
 
  `c_bhsf6d` double DEFAULT NULL,
 
  `c_t9_mu` int(11) DEFAULT NULL,
 
  PRIMARY KEY (`pkey`)
 
);
 
 
 
insert into t_rry5a (wkey, pkey, c_t4jlkc, c_bhsf6d) values
 
(1052, 5800000, 1, 100);
 
 
 
WITH
 
cte_0 AS (select
 
    ref_0.wkey as c0,
 
    ref_0.pkey as c1,
 
    ref_0.c_t4jlkc as c2,
 
    ref_0.c_a047t as c3,
 
    ref_0.c_bhsf6d as c4,
 
    ref_0.c_t9_mu as c5
 
  from
 
    t_rry5a as ref_0)
 
select
 
    ref_2.c0 as c0,
 
    ref_2.c1 as c1,
 
    ref_2.c2 as c2,
 
    ref_2.c3 as c3,
 
    ref_2.c4 as c4,
 
    ref_2.c5 as c5
 
  from
 
    cte_0 as ref_2
 
  where exists (
 
      select
 
            FIRST_VALUE(ref_2.c4) over (partition by ref_2.c1) as c0
 
      );
 
 
 
WITH
 
cte_0 AS (select
 
    ref_0.wkey as c0,
 
    ref_0.pkey as c1,
 
    ref_0.c_t4jlkc as c2,
 
    ref_0.c_a047t as c3,
 
    ref_0.c_bhsf6d as c4,
 
    ref_0.c_t9_mu as c5
 
  from
 
    t_rry5a as ref_0)
 
select
 
    ref_2.c0 as c0,
 
    ref_2.c1 as c1,
 
    ref_2.c2 as c2,
 
    ref_2.c3 as c3,
 
    ref_2.c4 as c4,
 
    ref_2.c5 as c5
 
  from
 
    cte_0 as ref_2
 
  where exists (
 
      select
 
            FIRST_VALUE(ref_2.c4) over (partition by ref_2.c1) as c0
 
      ) or 17 <> 0;

Analysis
The first SELECT outputs

+------+---------+------+------+------+------+
 
| c0   | c1      | c2   | c3   | c4   | c5   |
 
+------+---------+------+------+------+------+
 
| 1052 | 5800000 |    1 | NULL |  100 | NULL |
 
+------+---------+------+------+------+------+
 
1 row in set (0.002 sec)

The second SELECT outputs

+------+---------+------+------+------+------+
 
| c0   | c1      | c2   | c3   | c4   | c5   |
 
+------+---------+------+------+------+------+
 
| 1052 | 5800000 |    1 | NULL | NULL | NULL |
 
+------+---------+------+------+------+------+
 
1 row in set (0.002 sec)

The first SELECT outputs a row whose c4 column is 100. The second SELECT only adds a "or 17 <> 0" at the end of the WHERE clause of the first SELECT. Such change should not affect the results of the SELECT, so the second SELECT should output the same results as the first SELECT. However, it outputs a row whose c4 column is NULL.

Based on the analysis, I think it is a logic bug that makes SELECT output incorrect results.

 Comments   
Comment by Alice Sherepa [ 2022-09-22 ]

Thank you!
I repeated on 10.3-10.10. 

CREATE TABLE t1 (a int,b int);
 
INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
 
 
SELECT * FROM (select * from t1) dt  WHERE EXISTS ( SELECT sum(dt.a) over (order by b)) OR 1;
 
SELECT * FROM (select * from t1) dt  WHERE EXISTS ( SELECT sum(dt.a) over (order by b));
 
 
 
SELECT * FROM (select * from t1) dt  WHERE EXISTS ( SELECT sum(dt.a) over (order by a)); #ASAN

10.3 bbf81b51f26eedb472ea892e4799

 
MariaDB [test]> SELECT * FROM (select * from t1) dt  WHERE EXISTS ( SELECT sum(dt.a) over (order by b)) OR 1;
 
+------+------+
 
| a    | b    |
 
+------+------+
 
| NULL |    1 |
 
| NULL |    2 |
 
| NULL |    3 |
 
+------+------+
 
3 rows in set (0,002 sec)
 
 
 
MariaDB [test]> SELECT * FROM (select * from t1) dt  WHERE EXISTS ( SELECT sum(dt.a) over (order by b));
 
+------+------+
 
| a    | b    |
 
+------+------+
 
|    1 |    1 |
 
|    2 |    2 |
 
|    3 |    3 |
 
+------+------+
 
3 rows in set (0,003 sec)
 
 
 
MariaDB [test]> explain extended  SELECT * FROM (select * from t1) dt  WHERE EXISTS ( SELECT sum(dt.a) over (order by b)) OR 1;
 
+------+--------------------+-------+------+---------------+------+---------+------+------+----------+----------------+
 
| id   | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
 
+------+--------------------+-------+------+---------------+------+---------+------+------+----------+----------------+
 
|    1 | PRIMARY            | t1    | ALL  | NULL          | NULL | NULL    | NULL | 3    |   100.00 |                |
 
|    3 | DEPENDENT SUBQUERY | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
 
+------+--------------------+-------+------+---------------+------+---------+------+------+----------+----------------+
 
2 rows in set, 3 warnings (0,003 sec)
 
 
 
Note (Code 1276): Field or reference 'dt.a' of SELECT #3 was resolved in SELECT #1
 
Note (Code 1276): Field or reference 'b' of SELECT #3 was resolved in SELECT #1
 
Note (Code 1003): /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where 1

10.3 bbf81b51f26eedb472ea892e4799

 
Version: '10.3.37-MariaDB-debug-log'  
=================================================================
 
==550194==ERROR: AddressSanitizer: heap-use-after-free on address 0x6190000f0128 at pc 0x5573515bf92d bp 0x7ff18e4a34f0 sp 0x7ff18e4a34e0
 
READ of size 8 at 0x6190000f0128 thread T27
 
    #0 0x5573515bf92c in Field::is_null(long long) const /10.3/src/sql/field.h:1183
 
    #1 0x5573515b90cc in Protocol_text::store(Field*) /10.3/src/sql/protocol.cc:1245
 
    #2 0x557352057084 in Item_field::send(Protocol*, st_value*) /10.3/src/sql/item.cc:7584
 
    #3 0x55735205e566 in Item_ref::send(Protocol*, st_value*) /10.3/src/sql/item.cc:8389
 
    #4 0x557352067a1a in Item_direct_view_ref::send(Protocol*, st_value*) /10.3/src/sql/item.cc:9115
 
    #5 0x5573515b5bf0 in Protocol::send_result_set_row(List<Item>*) /10.3/src/sql/protocol.cc:1000
 
    #6 0x55735175206e in select_send::send_data(List<Item>&) /10.3/src/sql/sql_class.cc:3049
 
    #7 0x55735199910b in end_send /10.3/src/sql/sql_select.cc:21118
 
    #8 0x5573519915aa in evaluate_join_record /10.3/src/sql/sql_select.cc:20159
 
    #9 0x55735198fec2 in sub_select(JOIN*, st_join_table*, bool) /10.3/src/sql/sql_select.cc:19932
 
    #10 0x55735198de81 in do_select /10.3/src/sql/sql_select.cc:19470
 
    #11 0x557351920904 in JOIN::exec_inner() /10.3/src/sql/sql_select.cc:4171
 
    #12 0x55735191e281 in JOIN::exec() /10.3/src/sql/sql_select.cc:3965
 
    #13 0x557351921d7f in mysql_select(THD*, 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*) /10.3/src/sql/sql_select.cc:4374
 
    #14 0x5573518f7ed2 in handle_select(THD*, LEX*, select_result*, unsigned long) /10.3/src/sql/sql_select.cc:372
 
    #15 0x557351868be2 in execute_sqlcom_select /10.3/src/sql/sql_parse.cc:6340
 
    #16 0x557351856c1d in mysql_execute_command(THD*) /10.3/src/sql/sql_parse.cc:3871
 
    #17 0x55735187293f in mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool) /10.3/src/sql/sql_parse.cc:7871
 
    #18 0x55735184981c in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool) /10.3/src/sql/sql_parse.cc:1852
 
    #19 0x55735184635f in do_command(THD*) /10.3/src/sql/sql_parse.cc:1398
 
    #20 0x557351c1a0d8 in do_handle_one_connection(CONNECT*) /10.3/src/sql/sql_connect.cc:1403
 
    #21 0x557351c19992 in handle_one_connection /10.3/src/sql/sql_connect.cc:1308
 
    #22 0x55735324b3d2 in pfs_spawn_thread /10.3/src/storage/perfschema/pfs.cc:1869
 
    #23 0x7ff1a4c60608 in start_thread /build/glibc-SzIz7B/glibc-2.31/nptl/pthread_create.c:477
 
    #24 0x7ff1a4b85132 in __clone (/lib/x86_64-linux-gnu/libc.so.6+0x11f132)
 
 
 
0x6190000f0128 is located 168 bytes inside of 1100-byte region [0x6190000f0080,0x6190000f04cc)
 
freed by thread T27 here:
 
    #0 0x7ff1a55b240f in __interceptor_free ../../../../src/libsanitizer/asan/asan_malloc_linux.cc:122
 
    #1 0x55735339944c in free_memory /10.3/src/mysys/safemalloc.c:279
 
    #2 0x557353398a08 in sf_free /10.3/src/mysys/safemalloc.c:197
 
    #3 0x557353366d33 in my_free /10.3/src/mysys/my_malloc.c:223
 
    #4 0x5573533434a3 in free_root /10.3/src/mysys/my_alloc.c:430
 
    #5 0x55735198c587 in free_tmp_table(THD*, TABLE*) /10.3/src/sql/sql_select.cc:19236
 
    #6 0x557351962040 in JOIN::cleanup(bool) /10.3/src/sql/sql_select.cc:13238
 
    #7 0x557351961410 in JOIN::join_free() /10.3/src/sql/sql_select.cc:13129
 
    #8 0x55735198e4ad in do_select /10.3/src/sql/sql_select.cc:19517
 
    #9 0x557351920904 in JOIN::exec_inner() /10.3/src/sql/sql_select.cc:4171
 
    #10 0x55735191e281 in JOIN::exec() /10.3/src/sql/sql_select.cc:3965
 
    #11 0x55735220ea40 in subselect_single_select_engine::exec() /10.3/src/sql/item_subselect.cc:4024
 
    #12 0x5573521e9dd3 in Item_subselect::exec() /10.3/src/sql/item_subselect.cc:792
 
    #13 0x5573521f4338 in Item_exists_subselect::val_int() /10.3/src/sql/item_subselect.cc:1730
 
    #14 0x55735209d35c in Item_in_optimizer::val_int() /10.3/src/sql/item_cmpfunc.cc:1556
 
    #15 0x557351990ba5 in evaluate_join_record /10.3/src/sql/sql_select.cc:20027
 
    #16 0x55735198fec2 in sub_select(JOIN*, st_join_table*, bool) /10.3/src/sql/sql_select.cc:19932
 
    #17 0x55735198de81 in do_select /10.3/src/sql/sql_select.cc:19470
 
    #18 0x557351920904 in JOIN::exec_inner() /10.3/src/sql/sql_select.cc:4171
 
    #19 0x55735191e281 in JOIN::exec() /10.3/src/sql/sql_select.cc:3965
 
    #20 0x557351921d7f in mysql_select(THD*, 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*) /10.3/src/sql/sql_select.cc:4374
 
    #21 0x5573518f7ed2 in handle_select(THD*, LEX*, select_result*, unsigned long) /10.3/src/sql/sql_select.cc:372
 
    #22 0x557351868be2 in execute_sqlcom_select /10.3/src/sql/sql_parse.cc:6340
 
    #23 0x557351856c1d in mysql_execute_command(THD*) /10.3/src/sql/sql_parse.cc:3871
 
    #24 0x55735187293f in mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool) /10.3/src/sql/sql_parse.cc:7871
 
    #25 0x55735184981c in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool) /10.3/src/sql/sql_parse.cc:1852
 
    #26 0x55735184635f in do_command(THD*) /10.3/src/sql/sql_parse.cc:1398
 
    #27 0x557351c1a0d8 in do_handle_one_connection(CONNECT*) /10.3/src/sql/sql_connect.cc:1403
 
    #28 0x557351c19992 in handle_one_connection /10.3/src/sql/sql_connect.cc:1308
 
    #29 0x55735324b3d2 in pfs_spawn_thread /10.3/src/storage/perfschema/pfs.cc:1869
 
 
 
previously allocated by thread T27 here:
 
    #0 0x7ff1a55b2808 in __interceptor_malloc ../../../../src/libsanitizer/asan/asan_malloc_linux.cc:144
 
    #1 0x5573533983bc in sf_malloc /10.3/src/mysys/safemalloc.c:118
 
    #2 0x55735336623c in my_malloc /10.3/src/mysys/my_malloc.c:101
 
    #3 0x5573533422a9 in alloc_root /10.3/src/mysys/my_alloc.c:251
 
    #4 0x557353343b08 in memdup_root /10.3/src/mysys/my_alloc.c:494
 
    #5 0x557351f2b879 in Field::make_new_field(st_mem_root*, TABLE*, bool) /10.3/src/sql/field.cc:2347
 
    #6 0x557351979273 in create_tmp_field_from_field(THD*, Field*, st_mysql_const_lex_string*, TABLE*, Item_field*) /10.3/src/sql/sql_select.cc:17177
 
    #7 0x55735197b1d2 in create_tmp_field(THD*, TABLE*, Item*, Item::Type, Item***, Field**, Field**, bool, bool, bool, bool) /10.3/src/sql/sql_select.cc:17464
 
    #8 0x55735197ebc8 in create_tmp_table(THD*, TMP_TABLE_PARAM*, List<Item>&, st_order*, bool, bool, unsigned long long, unsigned long long, st_mysql_const_lex_string const*, bool, bool) /10.3/src/sql/sql_select.cc:17922
 
    #9 0x5573519191ad in JOIN::create_postjoin_aggr_table(st_join_table*, List<Item>*, st_order*, bool, bool, bool) /10.3/src/sql/sql_select.cc:3478
 
    #10 0x557351914adc in JOIN::make_aggr_tables_info() /10.3/src/sql/sql_select.cc:3077
 
    #11 0x5573519109e2 in JOIN::optimize_stage2() /10.3/src/sql/sql_select.cc:2751
 
    #12 0x557351909281 in JOIN::optimize_inner() /10.3/src/sql/sql_select.cc:2018
 
    #13 0x5573519044b4 in JOIN::optimize() /10.3/src/sql/sql_select.cc:1534
 
    #14 0x5573517f6ecb in st_select_lex::optimize_unflattened_subqueries(bool) /10.3/src/sql/sql_lex.cc:4123
 
    #15 0x557351d06d0a in JOIN::optimize_unflattened_subqueries() /10.3/src/sql/opt_subselect.cc:5411
 
    #16 0x55735190ed5f in JOIN::optimize_stage2() /10.3/src/sql/sql_select.cc:2546
 
    #17 0x557351909281 in JOIN::optimize_inner() /10.3/src/sql/sql_select.cc:2018
 
    #18 0x5573519044b4 in JOIN::optimize() /10.3/src/sql/sql_select.cc:1534
 
    #19 0x557351921b8e in mysql_select(THD*, 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*) /10.3/src/sql/sql_select.cc:4360
 
    #20 0x5573518f7ed2 in handle_select(THD*, LEX*, select_result*, unsigned long) /10.3/src/sql/sql_select.cc:372
 
    #21 0x557351868be2 in execute_sqlcom_select /10.3/src/sql/sql_parse.cc:6340
 
    #22 0x557351856c1d in mysql_execute_command(THD*) /10.3/src/sql/sql_parse.cc:3871
 
    #23 0x55735187293f in mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool) /10.3/src/sql/sql_parse.cc:7871
 
    #24 0x55735184981c in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool) /10.3/src/sql/sql_parse.cc:1852
 
    #25 0x55735184635f in do_command(THD*) /10.3/src/sql/sql_parse.cc:1398
 
    #26 0x557351c1a0d8 in do_handle_one_connection(CONNECT*) /10.3/src/sql/sql_connect.cc:1403
 
    #27 0x557351c19992 in handle_one_connection /10.3/src/sql/sql_connect.cc:1308
 
    #28 0x55735324b3d2 in pfs_spawn_thread /10.3/src/storage/perfschema/pfs.cc:1869
 
    #29 0x7ff1a4c60608 in start_thread /build/glibc-SzIz7B/glibc-2.31/nptl/pthread_create.c:477
 
 
 
Thread T27 created by T0 here:
 
    #0 0x7ff1a54df815 in __interceptor_pthread_create ../../../../src/libsanitizer/asan/asan_interceptors.cc:208
 
    #1 0x55735324b7c3 in spawn_thread_v1 /10.3/src/storage/perfschema/pfs.cc:1919
 
    #2 0x55735156b39e in inline_mysql_thread_create /10.3/src/include/mysql/psi/mysql_thread.h:1275
 
    #3 0x557351584158 in create_thread_to_handle_connection(CONNECT*) /10.3/src/sql/mysqld.cc:6668
 
    #4 0x5573515848f3 in create_new_thread /10.3/src/sql/mysqld.cc:6738
 
    #5 0x557351585a85 in handle_connections_sockets() /10.3/src/sql/mysqld.cc:6996
 
    #6 0x557351583449 in mysqld_main(int, char**) /10.3/src/sql/mysqld.cc:6290
 
    #7 0x557351569b9c in main /10.3/src/sql/main.cc:25
 
    #8 0x7ff1a4a8a082 in __libc_start_main ../csu/libc-start.c:308
 
 
 
SUMMARY: AddressSanitizer: heap-use-after-free /10.3/src/sql/field.h:1183 in Field::is_null(long long) const
 
Shadow bytes around the buggy address:
 
  0x0c3280015fd0: fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa
 
  0x0c3280015fe0: fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa
 
  0x0c3280015ff0: fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa
 
  0x0c3280016000: fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa
 
  0x0c3280016010: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
 
=>0x0c3280016020: fd fd fd fd fd[fd]fd fd fd fd fd fd fd fd fd fd
 
  0x0c3280016030: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
 
  0x0c3280016040: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
 
  0x0c3280016050: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
 
  0x0c3280016060: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
 
  0x0c3280016070: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
 
Shadow byte legend (one shadow byte represents 8 application bytes):
 
  Addressable:           00
 
  Partially addressable: 01 02 03 04 05 06 07 
  Heap left redzone:       fa
 
  Freed heap region:       fd
 
  Stack left redzone:      f1
 
  Stack mid redzone:       f2
 
  Stack right redzone:     f3
 
  Stack after return:      f5
 
  Stack use after scope:   f8
 
  Global redzone:          f9
 
  Global init order:       f6
 
  Poisoned by user:        f7
 
  Container overflow:      fc
 
  Array cookie:            ac
 
  Intra object redzone:    bb
 
  ASan internal:           fe
 
  Left alloca redzone:     ca
 
  Right alloca redzone:    cb
 
  Shadow gap:              cc
 
==550194==ABORTING
 
----------SERVER LOG END-------------
 
 
 
 

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