[MDEV-521] LP:1046882 - Wrong result (missing rows) with a 2nd-level EXISTS subquery - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.0.0, 5.5.27, 5.3.8
Fix Version/s: 10.0.0, 5.5.28, 5.3.9
Component/s: None
Labels:
None

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;

Attachments

Activity

Ascending order - Click to sort in descending order

View 5 older comments

Oleksandr Byelkin added a comment - 2012-09-18 17:26

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)

Oleksandr Byelkin added a comment - 2012-09-18 17:26 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)

Oleksandr Byelkin added a comment - 2012-09-20 09:32

join_read_key2 has junk in table->record[0] during table->file->ha_index_read_map call...

Oleksandr Byelkin added a comment - 2012-09-20 09:32 join_read_key2 has junk in table->record [0] during table->file->ha_index_read_map call...

Oleksandr Byelkin added a comment - 2012-09-20 10:46

OK, See what should not be here: subselect_single_select_engine::exec()
It should be excluded already...

Oleksandr Byelkin added a comment - 2012-09-20 10:46 OK, See what should not be here: subselect_single_select_engine::exec() It should be excluded already...

Oleksandr Byelkin added a comment - 2012-09-20 11:03

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)

Oleksandr Byelkin added a comment - 2012-09-20 11:03 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)

Oleksandr Byelkin added a comment - 2012-09-20 12:11

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.

Oleksandr Byelkin added a comment - 2012-09-20 12:11 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.

People

Assignee:: Oleksandr Byelkin

Reporter:: Elena Stepanova

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Due:: 2012-09-21

Created:: 2012-09-10 22:13

Updated:: 2012-09-21 23:54

Resolved:: 2012-09-21 23:54

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server