MySQL Bugs: #18503: Queries with "> ALL (subquery)" on InnoDB tables may return wrong result.

Bug #18503	Queries with "> ALL (subquery)" on InnoDB tables may return wrong result.
Submitted:	25 Mar 2006 0:06	Modified:	24 Jul 2006 17:19
Reporter:	Maciej Babinski	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	Tested on 4.1.16, 5.0.18	OS:	Linux (FedoraCore 4 (x86-64) and FC5)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
In some circumstances, comparisons using "> ALL (subquery)" do not function correctly on InnoDB tables when the subquery returns no rows.

If the subquery is flagged by the planner as having an impossible WHERE clause, there is no problem. When a target table is actually scanned, despite the fact that no rows are returned, the comparison may be false. Using MyISAM tables yields correct results.

How to repeat:
CREATE TABLE foo (col1 INT) type='InnoDB';
CREATE TABLE bar (col2 INT) type='InnoDB';

INSERT INTO foo VALUES (1);

SELECT * FROM foo WHERE col1 > ALL (SELECT col2 FROM bar);
SELECT * FROM foo WHERE col1 > ALL (SELECT col2 FROM bar WHERE col2=0);
SELECT * FROM foo WHERE col1 > ALL (SELECT col2 FROM bar WHERE 1=0);

DROP TABLE foo;
DROP TABLE bar;

Since `bar` contains no rows, all three subqueries return no results, and we expect each query to return the single row from `foo`. However, the first two queries return no rows.

Changed incorrect Version in bug report.

Hi!

InnoDB does correctly return HA_ERR_END_OF_FILE from bar.

The probable reason for the bug is that MySQL first opens a cursor to foo, retrieves one row from foo, and then, WITHOUT CLOSING THE CURSOR on foo, continues to fetch from foo. Since there are no more rows in foo, InnoDB returns HA_ERR_END_OF_FILE, and the query returns from MySQL.

Breakpoint 2, ha_innobase::rnd_init (this=0x8981540) at ha_innodb.cc:4298
4298            row_prebuilt_t* prebuilt = (row_prebuilt_t*) innobase_prebuilt;
Current language:  auto; currently c++
(gdb)
Continuing.

Breakpoint 3, ha_innobase::rnd_next (this=0x8441da8,
    buf=0x89a87c4 "\220é\231\b@\025\230\bÄ\207\232\b°\225 \bÀ")
    at mysql_priv.h:473
473     {

HERE MYSQL retrieves one row from foo:

(gdb) bt
#0  ha_innobase::rnd_next (this=0x8441da8,
    buf=0x89a87c4 "\220é\231\b@\025\230\bÄ\207\232\b°\225 \bÀ")
    at mysql_priv.h:473
#1  0x082095d5 in rr_sequential (info=0x89a87c4) at records.cc:295
#2  0x081b8ac9 in join_init_read_record (tab=0x89a8788) at sql_select.cc:10262
#3  0x081b7840 in sub_select (join=0x899b720, join_tab=0x89a8788,
    end_of_records=3) at sql_select.cc:9654
#4  0x081b7392 in do_select (join=0x899b720, fields=0x89803b0, table=0x0,
    procedure=0x0) at sql_select.cc:9418
#5  0x081abf08 in JOIN::exec (this=0x899b720) at sql_select.cc:1721
#6  0x081ac86a in mysql_select (thd=0x89800c0, rref_pointer_array=0x898044c,
    tables=0x899ab50, wild_num=1, fields=@0x8981660, conds=0x899b618,
    og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0,
    select_options=2156153344, result=0x899b710, unit=0x898010c,
    select_lex=0x898032c) at sql_select.cc:1885
#7  0x081a89a2 in handle_select (thd=0x89800c0, lex=0x89800fc,
    result=0x899b710, setup_tables_done_option=0) at sql_select.cc:238
#8  0x08175d1c in mysql_execute_command (thd=0x89800c0) at sql_parse.cc:2494
#9  0x0817e610 in mysql_parse (thd=0x89800c0,
    inBuf=0x899aa18 "SELECT * FROM foo WHERE col1 > ALL (SELECT col2 FROM bar where col2 = 10)", length=144179452) at sql_parse.cc:5628
#10 0x08174483 in dispatch_command (command=144179392, thd=0x89800c0,
    packet=0x89929e9 "", packet_length=144288280) at sql_parse.cc:1713
#11 0x08173fcd in do_command (thd=0x89800c0) at sql_parse.cc:1514
#12 0x081733a2 in handle_one_connection (arg=0x89800c0) at sql_parse.cc:1158
#13 0x40042b63 in start_thread () from /lib/tls/libpthread.so.0
#14 0x4024c18a in clone () from /lib/tls/libc.so.6
(gdb)

(gdb)
Continuing.

Breakpoint 1, row_search_for_mysql (buf=0x8981660 "ý\001", mode=1,
    prebuilt=0x42b4c868, match_mode=0, direction=0) at row0sel.c:3059
3059            dict_index_t*   index           = prebuilt->index;
Current language:  auto; currently c
(gdb)
Continuing.

Breakpoint 2, ha_innobase::rnd_init (this=0x89a7490) at ha_innodb.cc:4298
4298            row_prebuilt_t* prebuilt = (row_prebuilt_t*) innobase_prebuilt;
Current language:  auto; currently c++
(gdb)
Continuing.

MySQL TRIES TO RETRIEVE A ROW FROM bar, INNODB RETURNS HA_ERR_END_OF_FILE:

Breakpoint 3, ha_innobase::rnd_next (this=0x8441da8,
    buf=0x89a8e3c "øn\232\b\220t\232\b<\216\232\b°\225 \bÀ")
    at mysql_priv.h:473
473     {
(gdb)
Continuing.

Breakpoint 1, row_search_for_mysql (buf=0x89a75b0 "ÿ", mode=1,
    prebuilt=0x42b4d868, match_mode=0, direction=0) at row0sel.c:3059
3059            dict_index_t*   index           = prebuilt->index;
Current language:  auto; currently c
(gdb)
Continuing.

THE BUG: MYSQL FOR SOME REASON CONTINUES TO RETRIEVE FROM foo, WITHOUT OPENING THE CURSOR ANEW:

Breakpoint 3, ha_innobase::rnd_next (this=0x8441da8,
    buf=0x89a87c4 "\220é\231\b@\025\230\bÄ\207\232\b°\225 \bÀ")
    at mysql_priv.h:473
473     {
Current language:  auto; currently c++
(gdb)

(gdb) bt
#0  ha_innobase::rnd_next (this=0x8441da8,
    buf=0x89a87c4 "\220é\231\b@\025\230\bÄ\207\232\b°\225 \bÀ")
    at mysql_priv.h:473
#1  0x082095d5 in rr_sequential (info=0x89a87c4) at records.cc:295
#2  0x081b77a6 in sub_select (join=0x899b720, join_tab=0x89a8788,
    end_of_records=false) at sql_select.cc:9660
#3  0x081b7392 in do_select (join=0x899b720, fields=0x89803b0, table=0x0,
    procedure=0x0) at sql_select.cc:9418
#4  0x081abf08 in JOIN::exec (this=0x899b720) at sql_select.cc:1721
#5  0x081ac86a in mysql_select (thd=0x89800c0, rref_pointer_array=0x898044c,
    tables=0x899ab50, wild_num=1, fields=@0x8981660, conds=0x899b618,
    og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0,
    select_options=2156153344, result=0x899b710, unit=0x898010c,
    select_lex=0x898032c) at sql_select.cc:1885
#6  0x081a89a2 in handle_select (thd=0x89800c0, lex=0x89800fc,
    result=0x899b710, setup_tables_done_option=0) at sql_select.cc:238
#7  0x08175d1c in mysql_execute_command (thd=0x89800c0) at sql_parse.cc:2494
#8  0x0817e610 in mysql_parse (thd=0x89800c0,
    inBuf=0x899aa18 "SELECT * FROM foo WHERE col1 > ALL (SELECT col2 FROM bar where col2 = 10)", length=144179452) at sql_parse.cc:5628
#9  0x08174483 in dispatch_command (command=144179392, thd=0x89800c0,
    packet=0x89929e9 "", packet_length=144288280) at sql_parse.cc:1713
#10 0x08173fcd in do_command (thd=0x89800c0) at sql_parse.cc:1514
#11 0x081733a2 in handle_one_connection (arg=0x89800c0) at sql_parse.cc:1158
#12 0x40042b63 in start_thread () from /lib/tls/libpthread.so.0
#13 0x4024c18a in clone () from /lib/tls/libc.so.6
(gdb)
 
Continuing.

Breakpoint 1, row_search_for_mysql (buf=0x8981660 "ý\001", mode=0,
    prebuilt=0x42b4c868, match_mode=0, direction=1) at row0sel.c:3059
3059            dict_index_t*   index           = prebuilt->index;
Current language:  auto; currently c
(gdb)
Continuing.

Regards,

Heikki

Verified on 5.0.18 on Linux.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/8948

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9018

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9190

An Item_sum_hybrid object has the was_values flag which indicates whether any
values were added to the sum function. By default it is set to true and reset
to false on any no_rows_in_result() call. This method is called only in
return_zero_rows() function. An ALL/ANY subquery can be optimized by MIN/MAX
optimization. The was_values flag is used to indicate whether the subquery
has returned at least one row. This bug occurs because return_zero_rows() is
called only when we know that the select will return zero rows before
starting any scans but often such information is not known.
In the reported case the return_zero_rows() function is not called and
the was_values flag is not reset to false and yet the subquery return no rows
Item_func_not_all and Item_func_nop_all functions return a wrong
comparison result.

Fixed in 4.1.22, 5.0.25

Noted in 4.1.22, 5.0.25 changelogs.

Using > ALL with subqueries that return no rows yields incorrect
results under certain circumstances due to incorrect application of
MIN()/MAX() optimization.