Details
Major
Description
Originally filed by Daniel Heimann in Launchpad: https://bugs.launchpad.net/maria/+bug/1046882
The following query
SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) )
on the test data returns an empty result set while there are rows in the table t1.
Please note that the behavior is different on a debug and release builds which I tried: on a debug build, the query seems to always return the wrong result, while on a release build it starts happening after some other action on the table: the reporter encountered it with SHOW FULL COLUMNS FROM t1, I also tried ANALYZE TABLE t1 an FLUSH TABLES, each of them causes the same effect. The provided test case uses the latter. I tried two machines and observed the same, but it might still depend on the build, system or the box.
Reproducible with the default optimizer_switch, as well as with all OFF values except for in_to_exists=on, or with all OFF values except for materialization=on (either of them is required to execute the query).
bzr version-info
revision-id: monty@askmonty.org-20120910105319-ga1tpymia69h306w date: 2012-09-10 13:53:19 +0300 build-date: 2012-09-10 22:59:38 +0400 revno: 3531
Also reproducible on 5.5.25 and 5.5.27 releases, on 5.3 tree and 10.0-base tree.
Could not reproduce on MariaDB 5.2, MySQL 5.5, MySQL 5.6.
EXPLAIN (with the default optimizer_switch):
id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY a index NULL PRIMARY 2 NULL 2 100.00 Using where; Using index 3 DEPENDENT SUBQUERY b eq_ref PRIMARY PRIMARY 2 test.a.f1 1 100.00 Using index Warnings: Note 1276 Field or reference 'test.a.f1' of SELECT #3 was resolved in SELECT #1 Note 1249 Select 2 was reduced during optimization Note 1003 select `test`.`a`.`f1` AS `f1` from `test`.`t1` `a` where exists(select 1 from `test`.`t1` `b` where (`test`.`b`.`f1` = `test`.`a`.`f1`))
Test case:
CREATE TABLE t1 (f1 char(2), PRIMARY KEY (f1)) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('u1'),('u2');
SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) );
FLUSH TABLES;
SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) );
# Cleanup
DROP TABLE t1;
Expected result:
CREATE TABLE t1 (f1 char(2), PRIMARY KEY (f1)) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('u1'),('u2');
SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) );
f1
u1
u2
FLUSH TABLES;
SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) );
f1
u1
u2
DROP TABLE t1;
Actual result, debug build:
CREATE TABLE t1 (f1 char(2), PRIMARY KEY (f1)) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('u1'),('u2');
SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) );
f1
FLUSH TABLES;
SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) );
f1
DROP TABLE t1;
Actual result, release build:
INSERT INTO t1 VALUES ('u1'),('u2');
SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) );
f1
u1
u2
FLUSH TABLES;
SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) );
f1
DROP TABLE t1;
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
with --debug it complain differently:
==21801== Thread 4:
==21801== Conditional jump or move depends on uninitialised value(s)
==21801== at 0x4C2BF7E: __GI_strnlen (in /usr/lib/valgrind/vgpreload_memcheck-amd64-linux.so)
==21801== by 0x5EEEAED: vfprintf (vfprintf.c:1624)
==21801== by 0x5EF7606: fprintf (fprintf.c:33)
==21801== by 0x983E8E: _mi_print_key (mi_dbug.c:71)
==21801== by 0x9A5418: mi_rkey (mi_rkey.c:67)
==21801== by 0x9695C0: ha_myisam::index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function) (ha_myisam.cc:1819)
==21801== by 0x5B4985: handler::ha_index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function) (sql_class.h:3673)
==21801== by 0x77CE45: join_read_key2(THD*, st_join_table*, st_table*, st_table_ref*) (sql_select.cc:16335)
==21801== by 0x77CCE0: join_read_key(st_join_table*) (sql_select.cc:16298)
==21801== by 0x77B591: sub_select(JOIN*, st_join_table*, bool) (sql_select.cc:15714)
==21801== by 0x77ADC9: do_select(JOIN*, List<Item>, st_table, Procedure*) (sql_select.cc:15378)
==21801== by 0x75B880: JOIN::exec() (sql_select.cc:2761)
==21801== by 0x6615ED: subselect_single_select_engine::exec() (item_subselect.cc:3131)
==21801== by 0x659D40: Item_subselect::exec() (item_subselect.cc:588)
==21801== by 0x65C426: Item_exists_subselect::val_int() (item_subselect.cc:1415)
==21801== by 0x77AD93: do_select(JOIN*, List<Item>, st_table, Procedure*) (sql_select.cc:15375)
==21801== Conditional jump or move depends on uninitialised value(s)
==21801== at 0x4C2BF8C: __GI_strnlen (in /usr/lib/valgrind/vgpreload_memcheck-amd64-linux.so)
==21801== by 0x5EEEAED: vfprintf (vfprintf.c:1624)
==21801== by 0x5EF7606: fprintf (fprintf.c:33)
==21801== by 0x983E8E: _mi_print_key (mi_dbug.c:71)
==21801== by 0x9A5418: mi_rkey (mi_rkey.c:67)
==21801== by 0x9695C0: ha_myisam::index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function) (ha_myisam.cc:1819)
==21801== by 0x5B4985: handler::ha_index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function) (sql_class.h:3673)
==21801== by 0x77CE45: join_read_key2(THD*, st_join_table*, st_table*, st_table_ref*) (sql_select.cc:16335)
==21801== by 0x77CCE0: join_read_key(st_join_table*) (sql_select.cc:16298)
==21801== by 0x77B591: sub_select(JOIN*, st_join_table*, bool) (sql_select.cc:15714)
==21801== by 0x77ADC9: do_select(JOIN*, List<Item>, st_table, Procedure*) (sql_select.cc:15378)
==21801== by 0x75B880: JOIN::exec() (sql_select.cc:2761)
==21801== by 0x6615ED: subselect_single_select_engine::exec() (item_subselect.cc:3131)
==21801== by 0x659D40: Item_subselect::exec() (item_subselect.cc:588)
==21801== by 0x65C426: Item_exists_subselect::val_int() (item_subselect.cc:1415)
==21801== by 0x77AD93: do_select(JOIN*, List<Item>, st_table, Procedure*) (sql_select.cc:15375)
==21801== Syscall param write(buf) points to uninitialised byte(s)
==21801== at 0x5F8A11D: ??? (syscall-template.S:82)
==21801== by 0x5F1E312: _IO_file_write@@GLIBC_2.2.5 (fileops.c:1289)
==21801== by 0x5F1E1D9: new_do_write (fileops.c:543)
==21801== by 0x5F1F944: _IO_do_write@@GLIBC_2.2.5 (fileops.c:516)
==21801== by 0x5F1E84F: _IO_file_sync@@GLIBC_2.2.5 (fileops.c:918)
==21801== by 0x5F1378A: fflush (iofflush.c:43)
==21801== by 0xCB7A66: DbugFlush (dbug.c:2430)
==21801== by 0xCB623F: db_enter (dbug.c:1211)
==21801== by 0x9A805D: _mi_search (mi_search.c:72)
==21801== by 0x9A55F9: mi_rkey (mi_rkey.c:98)
==21801== by 0x9695C0: ha_myisam::index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function) (ha_myisam.cc:1819)
==21801== by 0x5B4985: handler::ha_index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function) (sql_class.h:3673)
==21801== by 0x77CE45: join_read_key2(THD*, st_join_table*, st_table*, st_table_ref*) (sql_select.cc:16335)
==21801== by 0x77CCE0: join_read_key(st_join_table*) (sql_select.cc:16298)
==21801== by 0x77B591: sub_select(JOIN*, st_join_table*, bool) (sql_select.cc:15714)
==21801== by 0x77ADC9: do_select(JOIN*, List<Item>, st_table, Procedure*) (sql_select.cc:15378)
==21801== Address 0x4026006 is not stack'd, malloc'd or (recently) free'd
==21801== Use of uninitialised value of size 8
==21801== at 0xCC4AF3: my_strnncollsp_simple (ctype-simple.c:167)
==21801== by 0xC9A0A6: ha_compare_text (my_compare.c:26)
==21801== by 0xC9A5E5: ha_key_cmp (my_compare.c:198)
==21801== by 0x9A8938: _mi_bin_search (mi_search.c:205)
==21801== by 0x9A8221: _mi_search (mi_search.c:91)
==21801== by 0x9A55F9: mi_rkey (mi_rkey.c:98)
==21801== by 0x9695C0: ha_myisam::index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function) (ha_myisam.cc:1819)
==21801== by 0x5B4985: handler::ha_index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function) (sql_class.h:3673)
==21801== by 0x77CE45: join_read_key2(THD*, st_join_table*, st_table*, st_table_ref*) (sql_select.cc:16335)
==21801== by 0x77CCE0: join_read_key(st_join_table*) (sql_select.cc:16298)
==21801== by 0x77B591: sub_select(JOIN*, st_join_table*, bool) (sql_select.cc:15714)
==21801== by 0x77ADC9: do_select(JOIN*, List<Item>, st_table, Procedure*) (sql_select.cc:15378)
==21801== by 0x75B880: JOIN::exec() (sql_select.cc:2761)
==21801== by 0x6615ED: subselect_single_select_engine::exec() (item_subselect.cc:3131)
==21801== by 0x659D40: Item_subselect::exec() (item_subselect.cc:588)
==21801== by 0x65C426: Item_exists_subselect::val_int() (item_subselect.cc:1415)
==21801== Use of uninitialised value of size 8
==21801== at 0xCC4B33: my_strnncollsp_simple (ctype-simple.c:168)
==21801== by 0xC9A0A6: ha_compare_text (my_compare.c:26)
==21801== by 0xC9A5E5: ha_key_cmp (my_compare.c:198)
==21801== by 0x9A8938: _mi_bin_search (mi_search.c:205)
==21801== by 0x9A8221: _mi_search (mi_search.c:91)
==21801== by 0x9A55F9: mi_rkey (mi_rkey.c:98)
==21801== by 0x9695C0: ha_myisam::index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function) (ha_myisam.cc:1819)
==21801== by 0x5B4985: handler::ha_index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function) (sql_class.h:3673)
==21801== by 0x77CE45: join_read_key2(THD*, st_join_table*, st_table*, st_table_ref*) (sql_select.cc:16335)
==21801== by 0x77CCE0: join_read_key(st_join_table*) (sql_select.cc:16298)
==21801== by 0x77B591: sub_select(JOIN*, st_join_table*, bool) (sql_select.cc:15714)
==21801== by 0x77ADC9: do_select(JOIN*, List<Item>, st_table, Procedure*) (sql_select.cc:15378)
==21801== by 0x75B880: JOIN::exec() (sql_select.cc:2761)
==21801== by 0x6615ED: subselect_single_select_engine::exec() (item_subselect.cc:3131)
==21801== by 0x659D40: Item_subselect::exec() (item_subselect.cc:588)
==21801== by 0x65C426: Item_exists_subselect::val_int() (item_subselect.cc:1415)
join_read_key2 has junk in table->record[0] during table->file->ha_index_read_map call...
OK, See what should not be here: subselect_single_select_engine::exec()
It should be excluded already...
The real problem is that table with alias 'a' has no legal record in the buffer (without flush table it could be last value left there somehow)
The problem is that most outer JOIN has outer_ref_cond set (which should not happen because it can't have outer references), and so the value calculated before tables read.
BTW correct result on this query in first case I got only on non-debugging build. Debugging build gives incorrect in both cases. It is strange that valgrind do not see problem with memory usage.