| Bug #82638 | Incorrect evaluation of MIN/MAX referring to an outer query block | ||
|---|---|---|---|
| Submitted: | 18 Aug 2016 17:48 | Modified: | 16 Nov 2016 1:13 |
| Reporter: | John Embretsen | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 5.7.2 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[18 Aug 2016 17:52]
John Embretsen
Posted by developer: Asserts with debug build. No crash with non-debug build (result is the Empty set).
[14 Sep 2016 10:25]
Marko Mäkelä
Posted by developer: I filed Bug#24657798 for the separate issue that is repeatable with MyISAM only. This bug goes away if I remove HA_DO_INDEX_COND_PUSHDOWN from ha_innobase::index_flags(). Curiously, we get different results with ENGINE=MEMORY for the following test case: CREATE TABLE t1 (pk INT PRIMARY KEY, nk INT, k INT UNIQUE) ENGINE=MEMORY; INSERT INTO t1 VALUES (1,1,1),(2,2,2),(4,4,4); CREATE TABLE t3 (k INT); INSERT INTO t3 VALUES (1),(2),(4); SELECT t3.k FROM t1, t3 WHERE t3.k = t1.k AND t1.pk<>3 AND t1.nk = (SELECT MAX(t1.k) FROM t1 s1); DROP TABLE t1, t3; With ENGINE=MEMORY, we would get all records 1,2,4. The subquery (SELECT MAX(t1.k) FROM t1 s1) is apparently interpreted as t1.k or (SELECT t1.k FROM t1 s1 LIMIT 1). With ENGINE=MyISAM or ENGINE=InnoDB, we would only get the record corresponding to MAX(t1.k), or MIN(t1.k), depending on which aggregate function is used. No warning is being issued for the missing GROUP BY on t1, even though SQL_MODE includes ONLY_FULL_GROUP_BY.
[15 Sep 2016 5:32]
Marko Mäkelä
Posted by developer: The assertion failure occurred in index condition pushdown (ICP) when InnoDB was trying to evaluate t1.pk<>3 while opt_sum_query() is processing the MIN/MAX expression. At this point, only the column of the MIN/MAX argument is marked in the read_set. For the test case in my previous comment, we will get a wrong result when using ENGINE=InnoDB (with ICP disabled) or ENGINE=MyISAM for table t1. The correct result (rows 1,2,4 in my previous comment) will be delivered only if using ENGINE=MEMORY or if there is no KEY that could be used for evaluating the MIN/MAX expression. MySQL 5.5 and 5.6 are delivering the correct result for my test case.
[15 Sep 2016 6:35]
Marko Mäkelä
Posted by developer: I repeated the InnoDB assertion failure and the wrong result with MyISAM using MySQL 5.7.6, 5.7.7, 5.7.8. With MySQL 5.7.5, no InnoDB assertion will fail (possibly because ICP is not being used), but instead we will get a wrong result (1 row instead of 3) with both InnoDB and MyISAM. (ENGINE=MEMORY is delivering all 3 rows.) I cannot find any older release package in the 5.7 series than 5.7.5.
[15 Sep 2016 7:59]
Marko Mäkelä
Posted by developer:
The wrong result starts with the following changeset:
commit c5accd92e6fef8717e0209fbb44515947d348ca3
Author: Evgeny Potemkin <evgeny.potemkin@oracle.com>
Date: Tue Jul 2 18:48:24 2013 +0400
WL#6369: EXPLAIN for other thread.
Implementation and fixed assiciated bugs.
[16 Nov 2016 1:13]
Paul DuBois
Posted by developer: Noted in 8.0.1 changelog. A query could produce incorrect results if MIN() or MAX() in a subquery referred to an indexed column.
Description: An assertion in the server is triggered by a SELECT statement: SELECT col_datetime_key FROM t2 WHERE ( SELECT SUBQUERY1_t2.col_varchar FROM t1 AS SUBQUERY1_t1 JOIN t3 AS SUBQUERY1_t2 ON SUBQUERY1_t2.col_varchar_key = SUBQUERY1_t1.col_varchar_key AND SUBQUERY1_t1.col_varchar = ( SELECT MAX(SUBQUERY1_t1.col_varchar_key) FROM t1 ) WHERE SUBQUERY1_t1.pk != 3 ) ; mysqld-debug: /sql/field.cc:4163: virtual longlong Field_long::val_int(): Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed. 12:59:07 UTC - mysqld got signal 6 ; Stacktrace: #2 handle_fatal_signal (sig=6) at sql/signal_handler.cc:219 #3 <signal handler called> #4 raise () from /lib64/libc.so.6 #5 abort () from /lib64/libc.so.6 #6 __assert_fail_base () from /lib64/libc.so.6 #7 __assert_fail () from /lib64/libc.so.6 #8 Field_long::val_int (this=) at sql/field.cc:4163 #9 Item_field::val_int (this=) at sql/item.cc:2826 #10 Arg_comparator::compare_int_signed (this=) at sql/item_cmpfunc.cc:1849 #11 Arg_comparator::compare (this=) at sql/item_cmpfunc.h:87 #12 Item_func_ne::val_int (this=) at sql/item_cmpfunc.cc:2552 #13 innobase_index_cond (h=) at storage/innobase/handler/ha_innodb.cc:20578 #14 row_search_idx_cond_check (mysql_rec="\370\003", prebuilt=, rec="W\200", offsets=) at storage/innobase/row/row0sel.cc:3976 #15 row_search_mvcc (buf="\370\003", mode=PAGE_CUR_L, prebuilt=, match_mode=0, direction=0) at storage/innobase/row/row0sel.cc:5431 #16 ha_innobase::index_read (this=, buf="\370\003", key_ptr=, key_len=0, find_flag=HA_READ_BEFORE_KEY) at storage/innobase/handler/ha_innodb.cc:9066 #17 ha_innobase::index_last (this=, buf="\370\003") at storage/innobase/handler/ha_innodb.cc:9509 #18 handler::ha_index_last (this=, buf="\370\003") at sql/handler.cc:3236 #19 get_index_max_value (table=, ref=, range_fl=3) at sql/opt_sum.cc:208 #20 opt_sum_query (thd=, tables=, all_fields=..., conds=) at sql/opt_sum.cc:452 #21 JOIN::optimize (this=) at sql/sql_optimizer.cc:302 #22 SELECT_LEX::optimize (this=, thd=) at sql/sql_select.cc:1028 #23 SELECT_LEX_UNIT::optimize (this=, thd=) at sql/sql_union.cc:691 #24 SELECT_LEX::optimize (this=, thd=) at sql/sql_select.cc:1034 #25 SELECT_LEX_UNIT::optimize (this=, thd=) at sql/sql_union.cc:691 #26 Item_subselect::exec (this=) at sql/item_subselect.cc:625 #27 Item_singlerow_subselect::val_int (this=) at sql/item_subselect.cc:1279 #28 eval_const_cond (thd=, cond=, value=) at sql/item_func.cc:86 #29 internal_remove_eq_conds (thd=, cond=, retcond=, cond_value=) at sql/sql_optimizer.cc:10169 #30 remove_eq_conds (thd=, cond=, retcond=, cond_value=) at sql/sql_optimizer.cc:10269 #31 optimize_cond (thd=, cond=, cond_equal=, join_list=, cond_value=) at sql/sql_optimizer.cc:9984 #32 JOIN::optimize (this=) at sql/sql_optimizer.cc:246 #33 SELECT_LEX::optimize (this=, thd=) at sql/sql_select.cc:1028 #34 handle_query (thd=, lex=, result=, added_options=0, removed_options=0) at sql/sql_select.cc:174 #35 execute_sqlcom_select (thd=, all_tables=) at sql/sql_parse.cc:4871 #36 mysql_execute_command (thd=, first_level=true) at sql/sql_parse.cc:2783 #37 mysql_parse (thd=, parser_state=) at sql/sql_parse.cc:5241 Seen on mysql-trunk (8.0.0) medio aug 2016. Details to be provided as bug comment later. How to repeat: See above for query. See attached MTR test for tables and data.