Wrong result/valgrind warning in Item_sum_hybrid::any_value()

[Philip Stoev]

Executing a particular prepared statement twice caused the following valgrind warning:

==21705== Thread 9:
==21705== Invalid read of size 1
==21705== at 0x82120E2: Item_sum_hybrid::any_value() (item_sum.h:889)
==21705== by 0x8202D5D: Item_func_not_all::empty_underlying_subquery() (item_cmpfunc.cc:333)
==21705== by 0x8202E5C: Item_func_nop_all::val_int() (item_cmpfunc.cc:363)
==21705== by 0x81C37D3: Item::val_bool() (item.cc:187)
==21705== by 0x820EA75: Item_cond_and::val_int() (item_cmpfunc.cc:4701)
==21705== by 0x832CACC: evaluate_join_record(JOIN*, st_join_table*, int) (sql_select.cc:14162)
==21705== by 0x832C737: sub_select(JOIN*, st_join_table*, bool) (sql_select.cc:14067)
==21705== by 0x832BB09: do_select(JOIN*, List<Item>*, st_table*, Procedure*) (sql_select.cc:13602)
==21705== by 0x8310749: JOIN::exec() (sql_select.cc:2114)
==21705== by 0x83128A1: 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*) (sql_select.cc:2772)
==21705== by 0x8458F0F: mysql_derived_filling(THD*, st_lex*, TABLE_LIST*) (sql_derived.cc:296)
==21705== by 0x845881A: mysql_handle_derived(st_lex*, bool (*)(THD*, st_lex*, TABLE_LIST*)) (sql_derived.cc:56)
==21705== by 0x82F34F1: open_and_lock_tables_derived(THD*, TABLE_LIST*, bool) (sql_base.cc:5125)
==21705== by 0x82AEA18: open_and_lock_tables(THD*, TABLE_LIST*) (mysql_priv.h:1670)
==21705== by 0x82A7D1F: execute_sqlcom_select(THD*, TABLE_LIST*) (sql_parse.cc:5060)
==21705== by 0x829ED7D: mysql_execute_command(THD*) (sql_parse.cc:2239)

crash backtrace:

# 2011-05-13T20:06:54 #3 0x0828c410 in handle_segfault (sig=11) at mysqld.cc:2778
# 2011-05-13T20:06:54 #4 <signal handler called>
# 2011-05-13T20:06:54 #5 0x082120e2 in Item_sum_hybrid::any_value (this=0xa8e4aa8) at item_sum.h:889
# 2011-05-13T20:06:54 #6 0x08202d5e in Item_func_not_all::empty_underlying_subquery (this=0xa831dc8) at item_cmpfunc.cc:333
# 2011-05-13T20:06:54 #7 0x08202e5d in Item_func_nop_all::val_int (this=0xa831dc8) at item_cmpfunc.cc:363
# 2011-05-13T20:06:54 #8 0x081c37d4 in Item::val_bool (this=0xa831dc8) at item.cc:187
# 2011-05-13T20:06:54 #9 0x0820ea76 in Item_cond_and::val_int (this=0xa859718) at item_cmpfunc.cc:4701
# 2011-05-13T20:06:54 #10 0x0832cacd in evaluate_join_record (join=0xa84d9a8, join_tab=0xa858db8, error=0) at sql_select.cc:14162
# 2011-05-13T20:06:54 #11 0x0832c738 in sub_select (join=0xa84d9a8, join_tab=0xa858db8, end_of_records=false) at sql_select.cc:14067
# 2011-05-13T20:06:54 #12 0x0832cda5 in evaluate_join_record (join=0xa84d9a8, join_tab=0xa858bd4, error=0) at sql_select.cc:14262
# 2011-05-13T20:06:54 #13 0x0832c880 in sub_select (join=0xa84d9a8, join_tab=0xa858bd4, end_of_records=false) at sql_select.cc:14107
# 2011-05-13T20:06:54 #14 0x0832bb0a in do_select (join=0xa84d9a8, fields=0x0, table=0xa77f530, procedure=0x0) at sql_select.cc:13602
# 2011-05-13T20:06:54 #15 0x0831074a in JOIN::exec (this=0xa84d9a8) at sql_select.cc:2114
# 2011-05-13T20:06:54 #16 0x083128a2 in mysql_select (thd=0xa78aa10, rref_pointer_array=0xa84d094, tables=0xa77d7e0, wild_num=0, fields=..., conds=0xa7967e0, og_num=0, order=0x0,
# 2011-05-13T20:06:54 group=0x0, having=0x0, proc_param=0x0, select_options=2416200201, result=0xa796f40, unit=0xa84d130, select_lex=0xa84cf90) at sql_select.cc:2772
# 2011-05-13T20:06:54 #17 0x08458f10 in mysql_derived_filling (thd=0xa78aa10, lex=0xa7fa2b0, orig_table_list=0xa834b68) at sql_derived.cc:296
# 2011-05-13T20:06:54 #18 0x0845881b in mysql_handle_derived (lex=0xa7fa2b0, processor=0x8458d2d <mysql_derived_filling(THD*, LEX*, TABLE_LIST*)>) at sql_derived.cc:56
# 2011-05-13T20:06:54 #19 0x082f34f2 in open_and_lock_tables_derived (thd=0xa78aa10, tables=0xa84c440, derived=true) at sql_base.cc:5125
# 2011-05-13T20:06:54 #20 0x082aea19 in open_and_lock_tables (thd=0xa78aa10, tables=0xa84c440) at mysql_priv.h:1670
# 2011-05-13T20:06:54 #21 0x082a7d20 in execute_sqlcom_select (thd=0xa78aa10, all_tables=0xa84c440) at sql_parse.cc:5060
# 2011-05-13T20:06:54 #22 0x0829ed7e in mysql_execute_command (thd=0xa78aa10) at sql_parse.cc:2239
# 2011-05-13T20:06:54 #23 0x0835258c in Prepared_statement::execute (this=0xa72cd50, expanded_query=0x9159178c, open_cursor=false) at sql_prepare.cc:3677
# 2011-05-13T20:06:54 #24 0x08351a74 in Prepared_statement::execute_loop (this=0xa72cd50, expanded_query=0x9159178c, open_cursor=false, packet=0x0, packet_end=0x0)
# 2011-05-13T20:06:54 at sql_prepare.cc:3352
# 2011-05-13T20:06:54 #25 0x08350390 in mysql_sql_stmt_execute (thd=0xa78aa10) at sql_prepare.cc:2613
# 2011-05-13T20:06:54 #26 0x0829eda7 in mysql_execute_command (thd=0xa78aa10) at sql_parse.cc:2248
# 2011-05-13T20:06:54 #27 0x082aa4d7 in mysql_parse (thd=0xa78aa10, rawbuf=0xa796130 "EXECUTE prep_stmt_21349 /* TRANSFORM_OUTCOME_UNORDERED_MATCH */", length=63,
# 2011-05-13T20:06:54 found_semicolon=0x91592228) at sql_parse.cc:6094
# 2011-05-13T20:06:54 #28 0x0829ca07 in dispatch_command (command=COM_QUERY, thd=0xa78aa10, packet=0xa78cc11 "EXECUTE prep_stmt_21349 /* TRANSFORM_OUTCOME_UNORDERED_MATCH */",
# 2011-05-13T20:06:54 packet_length=63) at sql_parse.cc:1215
# 2011-05-13T20:06:54 #29 0x0829be65 in do_command (thd=0xa78aa10) at sql_parse.cc:904
# 2011-05-13T20:06:54 #30 0x08298f18 in handle_one_connection (arg=0xa78aa10) at sql_connect.cc:1154
# 2011-05-13T20:06:54 #31 0x00821919 in start_thread () from /lib/libpthread.so.0
# 2011-05-13T20:06:54 #32 0x0076acce in clone () from /lib/libc.so.6

A test case will be attached shortly.

[Timour Katchaounov (timour)]

I could no longer reproduce the crash, however the second execution produces
a different (wrong) result compared to the first execution. Thus renaming the bug.
It doesn't matter which subquery strategy was used during prepare/execution.

MariaDB [test]> execute st1;
+--------+--------+--------+
| field1 | field2 | field3 |
+--------+--------+--------+
| 0 | NULL | m |
+--------+--------+--------+
1 row in set (0.00 sec)

MariaDB [test]> execute st1;
+--------+--------+--------+
| field1 | field2 | field3 |
+--------+--------+--------+
| 0 | NULL | NULL |
+--------+--------+--------+

[Timour Katchaounov (timour)]

The valgrind error is still the same.

[Timour Katchaounov (timour)]

Slightly reduced query:

prepare st1 from "
SELECT COUNT( alias1 .col_varchar_key) AS field1,
       alias1.col_int_key AS field2,
       alias2.col_varchar_key AS field3
FROM ((SELECT SQ1_alias1 .*
       FROM C AS SQ1_alias1
       WHERE EXISTS (SELECT C_SQ1_alias1.col_varchar_key AS C_SQ1_field1
                     FROM A AS C_SQ1_alias1)) AS alias1
      RIGHT JOIN
     (C AS alias2
      INNER JOIN
      (SELECT DISTINCT SQ2_alias1 .*
       FROM (BB AS SQ2_alias1
             INNER JOIN
            (C AS SQ2_alias2
             INNER JOIN
             C AS SQ2_alias3 ON (SQ2_alias3.col_int_key = SQ2_alias2.pk
                                 AND
                                 SQ2_alias2.col_varchar_key <= SOME (SELECT C_SQ2_alias2.col_varchar_key AS C_SQ2_field1
                                                                       FROM (CC AS C_SQ2_alias1
                                                                             RIGHT JOIN
                                                                             D AS C_SQ2_alias2
                                                                             ON (C_SQ2_alias2.col_varchar_key = C_SQ2_alias1.col_varchar_nokey)))))
             ON (SQ2_alias3.col_varchar_key = SQ2_alias2 .col_varchar_key))) AS alias3
      ON (alias3.col_int_key = alias2 .col_int_nokey))
      ON (alias3.col_int_nokey = alias2.col_int_key))
";

[Timour Katchaounov (timour)]

This bug is likely related to either derived tables, or non-semijoin subquery execution.
Assigning to Igor so he can evaluate if it it is related to derived tables. If not, the bug
should be assigned to Timour.

[Philip Stoev (pstoev-askmonty)]

Not reproducible on maria-5.2, mysql-5.1. No valgrind warnings and the results from the two executions match.

[Philip Stoev (pstoev-askmonty)]

Simplified test case for the wrong result. Does not reproduce the valgrind warning though.

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 ( f10 int) ;
INSERT IGNORE INTO t1 VALUES (2),(2);

DROP TABLE IF EXISTS t2;
CREATE TABLE t2 (f10 int) ;
INSERT IGNORE INTO t2 VALUES (2),(2);

DROP TABLE IF EXISTS t3;
CREATE TABLE t3 ( f3 int) ;
INSERT IGNORE INTO t3 VALUES (2),(2);

PREPARE st1 FROM '
SELECT *
FROM t2, t3
WHERE t2.f10 <= SOME ( SELECT f10 FROM t1 );
';
EXECUTE st1;
EXECUTE st1;

EXPLAIN:

| 1 | PRIMARY | t2 | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
| 1 | PRIMARY | t3 | ALL | NULL | NULL | NULL | NULL | 2 | Using join buffer (flat, BNL join) |
| 2 | SUBQUERY | t1 | ALL | NULL | NULL | NULL | NULL | 4 | |

[Timour Katchaounov (timour)]

Analysis:

During the first execution, in
Item_allany_subselect::transform_into_max_min()
    if (... !select_lex->with_sum_func && ...)
Initially here select_lex->with_sum_func == false, we get inside
the IF branch, and
      /*
        (ALL && (> || =>)) || (ANY && (< || =<))
        for ALL condition is inverted
      */
      item= new Item_sum_max(*select_lex->ref_pointer_array);
creates a new Item_sum_max. Its constructor calls
Item_sum::mark_as_sum_func(), which sets
cur_select->with_sum_func= 1;

During the second execution, in the same place,
select_lex->with_sum_func = true, so we get into the
ELSE branch instead. This branch deals with aggregate
queries.

On one hand this transformation is repeated for each
PS execution, however, the transformation is not undone.
Thus during the next call to exec, it turns out that
select_lex->with_sum_func is TRUE instead of false,
which repeats the transformation in a different way
as if the query has an aggregate function. In this way
the transformation is not repeated the same way.