[MDEV-5399] Wrong result (missing rows) on a NOT IN subquery with NULL values, materialization=off, TokuDB Created: 2013-12-06  Updated: 2013-12-19  Resolved: 2013-12-19

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.5.34, 10.0.6
Fix Version/s: 5.5.35, 10.0.8

Type: Bug Priority: Minor
Reporter: Elena Stepanova Assignee: Timour Katchaounov (Inactive)
Resolution: Fixed Votes: 0
Labels: upstream

Issue Links:
Relates

 Description   

I set it to Minor because the combination of pre-conditions (NOT IN subquery with two values in the left expression and with NULLs, non-default materialization=off, TokuDB) makes it a corner case.

Test case:

CREATE TABLE t1 (a INT) ENGINE=TokuDB;
 
INSERT INTO t1 VALUES (NULL),(3);
 
 
 
CREATE TABLE t2 (b INT, INDEX(b)) ENGINE=TokuDB;
 
INSERT INTO t2 VALUES (4),(5);
 
 
 
SELECT * FROM t1 t1a, t1 t1b WHERE ( t1a.a, t1b.a ) NOT IN ( SELECT b, b FROM t2 );
 
 
 
SET optimizer_switch = 'materialization=off';
 
 
 
SELECT * FROM t1 t1a, t1 t1b WHERE ( t1a.a, t1b.a ) NOT IN ( SELECT b, b FROM t2 );

The semantics of this NOT NULL is a bit vague for me here, but considering the following

MariaDB [test]> select (null, null) not in (select 4, 4 union select 5, 5);
 
+-----------------------------------------------------+
 
| (null, null) not in (select 4, 4 union select 5, 5) |
 
+-----------------------------------------------------+
 
|                                                NULL |
 
+-----------------------------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> select (null, 3) not in (select 4, 4 union select 5, 5);
 
+--------------------------------------------------+
 
| (null, 3) not in (select 4, 4 union select 5, 5) |
 
+--------------------------------------------------+
 
|                                                1 |
 
+--------------------------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> select (3, null) not in (select 4, 4 union select 5, 5);
 
+--------------------------------------------------+
 
| (3, null) not in (select 4, 4 union select 5, 5) |
 
+--------------------------------------------------+
 
|                                                1 |
 
+--------------------------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> select (3, 3) not in (select 4, 4 union select 5, 5);
 
+-----------------------------------------------+
 
| (3, 3) not in (select 4, 4 union select 5, 5) |
 
+-----------------------------------------------+
 
|                                             1 |
 
+-----------------------------------------------+
 
1 row in set (0.00 sec)

I presume the correct result is the one that is returned with materialization=on:

MariaDB [test]> SELECT * FROM t1 t1a, t1 t1b WHERE ( t1a.a, t1b.a ) NOT IN ( SELECT b, b FROM t2 );
 
+------+------+
 
| a    | a    |
 
+------+------+
 
|    3 | NULL |
 
| NULL |    3 |
 
|    3 |    3 |
 
+------+------+
 
3 rows in set (0.01 sec)

and the result with materialization=off is wrong:

MariaDB [test]> SET optimizer_switch = 'materialization=off';
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> 
MariaDB [test]> SELECT * FROM t1 t1a, t1 t1b WHERE ( t1a.a, t1b.a ) NOT IN ( SELECT b, b FROM t2 );
 
+------+------+
 
| a    | a    |
 
+------+------+
 
|    3 |    3 |
 
+------+------+
 
1 row in set (0.00 sec)

Also reproducible on mariadb-5.5.30-tokudb-7.0.1-linux-x86_64, but not on mysql-5.5.30-tokudb-7.1.0-linux-x86_64.

Could not reproduce with InnoDB or MyISAM.

 Comments   
Comment by Timour Katchaounov (Inactive) [ 2013-12-10 ]

A test case without a join:

CREATE TABLE t1 (a1 char(2), a2 char(2)) ENGINE=InnoDB;
INSERT INTO t1 VALUES (NULL, 'xx');

CREATE TABLE t2t (b char(2), INDEX(b)) ENGINE=TokuDB;
CREATE TABLE t2i (b char(2), INDEX(b)) ENGINE=InnoDB;
INSERT INTO t2t VALUES ('yy'),('zz');
INSERT INTO t2i VALUES ('yy'),('zz');

SET optimizer_switch = 'materialization=on';

explain extended
SELECT * FROM t1 WHERE (a1, a2) NOT IN ( SELECT b, b FROM t2t);
SELECT * FROM t1 WHERE (a1, a2) NOT IN ( SELECT b, b FROM t2t);

explain extended
SELECT * FROM t1 WHERE (a1, a2) NOT IN ( SELECT b, b FROM t2i);
SELECT * FROM t1 WHERE (a1, a2) NOT IN ( SELECT b, b FROM t2i);

SET optimizer_switch = 'materialization=off';

explain extended
SELECT * FROM t1 WHERE (a1, a2) NOT IN ( SELECT b, b FROM t2t);
SELECT * FROM t1 WHERE (a1, a2) NOT IN ( SELECT b, b FROM t2t);

explain extended
SELECT * FROM t1 WHERE (a1, a2) NOT IN ( SELECT b, b FROM t2i);
SELECT * FROM t1 WHERE (a1, a2) NOT IN ( SELECT b, b FROM t2i);

Comment by Timour Katchaounov (Inactive) [ 2013-12-10 ]

The above two queries only differ in that the inner table t2i is InnoDB, t2t is Tokutek.
As one can see, the result for Tokutek is wrong. This is repeatable both with MariaDB+Tokutek, and with Mysql 5.5+Tokutek, so the bug is not MariaDB specific.

mysql> select a1, a2, (a1, a2) IN ( SELECT b, b FROM t2t) as sq from t1;
--------------

a1 a2 sq

--------------

NULL xx NULL

--------------
1 row in set (0.00 sec)

mysql> select a1, a2, (a1, a2) IN ( SELECT b, b FROM t2i) as sq from t1;
--------------

a1 a2 sq

--------------

NULL xx 0

--------------

Comment by Timour Katchaounov (Inactive) [ 2013-12-10 ]

The immediate cause for the wrong result is that Tokutek doesn't set properly Field::null_ptr. It sets it to all bits '1', so a value is interpreted as a NULL when it shouldn't.

Comment by Timour Katchaounov (Inactive) [ 2013-12-11 ]

Upon analysis, this seems to be a bug in TokuDB. Filed as:
https://github.com/Tokutek/ft-engine/issues/152

Comment by Rich Prohaska [ 2013-12-12 ]

What is a good method to debug this problem? For example, how did you find that Field::null_ptr is not set correctly? Thanks.

Comment by Rich Prohaska [ 2013-12-12 ]

ha_tokudb::extra gets called to do a key read. then rnd_init gets called to do a scan on the primary. since there is no key on the primary, tokudb gets confused. here are some stack traces:

Breakpoint 4, ha_tokudb::extra (this=0x7ffda38c1078, operation=HA_EXTRA_KEYREAD) at /home/prohaska/ma5534/mariadb-5.5.34/storage/tokudb/ha_tokudb.cc:5971
(gdb) up
#1 0x0000000000674c57 in TABLE::enable_keyread (this=0x7ffda3830660) at /home/prohaska/ma5534/mariadb-5.5.34/sql/table.h:1283
(gdb)
#2 0x0000000000652bf4 in make_join_readinfo (join=0x7ffda3a26b70, options=0, no_jbuf_after=1) at /home/prohaska/ma5534/mariadb-5.5.34/sql/sql_select.cc:10164
(gdb) p tab->ref.key
$36 = 0
(gdb) p table->covering_keys
$37 =

{map = 1}

(gdb) bt
#0 ha_tokudb::extra (this=0x7ffda38c1078, operation=HA_EXTRA_KEYREAD) at /home/prohaska/ma5534/mariadb-5.5.34/storage/tokudb/ha_tokudb.cc:5971
#1 0x0000000000674c57 in TABLE::enable_keyread (this=0x7ffda3830660) at /home/prohaska/ma5534/mariadb-5.5.34/sql/table.h:1283
#2 0x0000000000652bf4 in make_join_readinfo (join=0x7ffda3a26b70, options=0, no_jbuf_after=1) at /home/prohaska/ma5534/mariadb-5.5.34/sql/sql_select.cc:10164
#3 0x000000000063c4db in JOIN::optimize (this=0x7ffda3a26b70) at /home/prohaska/ma5534/mariadb-5.5.34/sql/sql_select.cc:1646
#4 0x00000000006006b6 in st_select_lex::optimize_unflattened_subqueries (this=0x7ffd9b216a60, const_only=false) at /home/prohaska/ma5534/mariadb-5.5.34/sql/sql_lex.cc:3493
#5 0x00000000007524ee in JOIN::optimize_unflattened_subqueries (this=0x7ffda3a26640) at /home/prohaska/ma5534/mariadb-5.5.34/sql/opt_subselect.cc:4924
#6 0x000000000063c560 in JOIN::optimize (this=0x7ffda3a26640) at /home/prohaska/ma5534/mariadb-5.5.34/sql/sql_select.cc:1653
#7 0x0000000000641333 in mysql_select (thd=0x7ffd9b213060, rref_pointer_array=0x7ffd9b216cc8, tables=0x7ffda3a26078, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_p\
aram=0x0, select_options=2147748608, result=0x7ffda38d5c10, unit=0x7ffd9b216388, select_lex=0x7ffd9b216a60) at /home/prohaska/ma5534/mariadb-5.5.34/sql/sql_select.cc:3065
#8 0x0000000000637fa5 in handle_select (thd=0x7ffd9b213060, lex=0x7ffd9b2162d8, result=0x7ffda38d5c10, setup_tables_done_option=0) at /home/prohaska/ma5534/mariadb-5.5.34/sql/sql_select.cc:318
#9 0x00000000006116d2 in execute_sqlcom_select (thd=0x7ffd9b213060, all_tables=0x7ffda3a26078) at /home/prohaska/ma5534/mariadb-5.5.34/sql/sql_parse.cc:4701
#10 0x000000000060a7b0 in mysql_execute_command (thd=0x7ffd9b213060) at /home/prohaska/ma5534/mariadb-5.5.34/sql/sql_parse.cc:2234
#11 0x0000000000613de0 in mysql_parse (thd=0x7ffd9b213060, rawbuf=0x7ffda38d4078 "select a1, a2, (a1, a2) IN ( SELECT b, b FROM t2t) as sq from t1", length=64, parser_state=0x7ffff7d9a650) at /home/proha\
ska/ma5534/mariadb-5.5.34/sql/sql_parse.cc:5812
#12 0x0000000000607d28 in dispatch_command (command=COM_QUERY, thd=0x7ffd9b213060, packet=0x7ffd9b235061 "select a1, a2, (a1, a2) IN ( SELECT b, b FROM t2t) as sq from t1", packet_length=64) at /home/pro\
haska/ma5534/mariadb-5.5.34/sql/sql_parse.cc:1082
#13 0x0000000000606ecb in do_command (thd=0x7ffd9b213060) at /home/prohaska/ma5534/mariadb-5.5.34/sql/sql_parse.cc:797
#14 0x000000000070a0c2 in do_handle_one_connection (thd_arg=0x7ffd9b213060) at /home/prohaska/ma5534/mariadb-5.5.34/sql/sql_connect.cc:1266
#15 0x0000000000709b81 in handle_one_connection (arg=0x7ffd9b213060) at /home/prohaska/ma5534/mariadb-5.5.34/sql/sql_connect.cc:1181
#16 0x0000003efa607851 in start_thread () from /lib64/libpthread.so.0
#17 0x0000003ef9ae894d in clone () from /lib64/libc.so.6
(gdb) c
Continuing.
15303:/home/prohaska/ma5534/mariadb-5.5.34/storage/tokudb/ha_tokudb.cc:5985:extra:return 0

Breakpoint 1, ha_tokudb::rnd_init (this=0x7ffda38c1078, scan=true) at /home/prohaska/ma5534/mariadb-5.5.34/storage/tokudb/ha_tokudb.cc:5513
(gdb) bt
#0 ha_tokudb::rnd_init (this=0x7ffda38c1078, scan=true) at /home/prohaska/ma5534/mariadb-5.5.34/storage/tokudb/ha_tokudb.cc:5513
#1 0x00000000005a4df2 in handler::ha_rnd_init (this=0x7ffda38c1078, scan=true) at /home/prohaska/ma5534/mariadb-5.5.34/sql/handler.h:2162
#2 0x0000000000859237 in subselect_uniquesubquery_engine::scan_table (this=0x7ffda3a2a4b0) at /home/prohaska/ma5534/mariadb-5.5.34/sql/item_subselect.cc:3289
#3 0x0000000000859b0c in subselect_indexsubquery_engine::exec (this=0x7ffda3a2a4b0) at /home/prohaska/ma5534/mariadb-5.5.34/sql/item_subselect.cc:3592
#4 0x0000000000851c0a in Item_subselect::exec (this=0x7ffda38d5920) at /home/prohaska/ma5534/mariadb-5.5.34/sql/item_subselect.cc:655
#5 0x0000000000852136 in Item_in_subselect::exec (this=0x7ffda38d5920) at /home/prohaska/ma5534/mariadb-5.5.34/sql/item_subselect.cc:828
#6 0x000000000085455b in Item_in_subselect::val_bool (this=0x7ffda38d5920) at /home/prohaska/ma5534/mariadb-5.5.34/sql/item_subselect.cc:1654
#7 0x000000000057532d in Item::val_bool_result (this=0x7ffda38d5920) at /home/prohaska/ma5534/mariadb-5.5.34/sql/item.h:977
#8 0x00000000007f1db2 in Item_in_optimizer::val_int (this=0x7ffda38d5f98) at /home/prohaska/ma5534/mariadb-5.5.34/sql/item_cmpfunc.cc:1729
#9 0x00000000005752a1 in Item::val_int_result (this=0x7ffda38d5f98) at /home/prohaska/ma5534/mariadb-5.5.34/sql/item.h:973
#10 0x00000000007e3685 in Item_cache_int::cache_value (this=0x7ffda3a2a5b8) at /home/prohaska/ma5534/mariadb-5.5.34/sql/item.cc:8771
#11 0x00000000007ea8a0 in Item_cache_wrapper::cache (this=0x7ffda3a2a500) at /home/prohaska/ma5534/mariadb-5.5.34/sql/item.cc:7577
#12 0x00000000007e0433 in Item_cache_wrapper::val_int (this=0x7ffda3a2a500) at /home/prohaska/ma5534/mariadb-5.5.34/sql/item.cc:7631
#13 0x00000000007dcded in Item::send (this=0x7ffda3a2a500, protocol=0x7ffd9b213628, buffer=0x7ffff7d99120) at /home/prohaska/ma5534/mariadb-5.5.34/sql/item.cc:6421
#14 0x00000000007dff9c in Item_cache_wrapper::send (this=0x7ffda3a2a500, protocol=0x7ffd9b213628, buffer=0x7ffff7d99120) at /home/prohaska/ma5534/mariadb-5.5.34/sql/item.cc:7496
#15 0x0000000000571612 in Protocol::send_result_set_row (this=0x7ffd9b213628, row_items=0x7ffd9b216b70) at /home/prohaska/ma5534/mariadb-5.5.34/sql/protocol.cc:900
#16 0x00000000005d32a0 in select_send::send_data (this=0x7ffda38d5c10, items=...) at /home/prohaska/ma5534/mariadb-5.5.34/sql/sql_class.cc:2370
#17 0x00000000006646a4 in end_send (join=0x7ffda3a26640, join_tab=0x0, end_of_records=false) at /home/prohaska/ma5534/mariadb-5.5.34/sql/sql_select.cc:17821
#18 0x000000000066144d in do_select (join=0x7ffda3a26640, fields=0x7ffd9b216b70, table=0x0, procedure=0x0) at /home/prohaska/ma5534/mariadb-5.5.34/sql/sql_select.cc:16340
#19 0x0000000000640bbe in JOIN::exec (this=0x7ffda3a26640) at /home/prohaska/ma5534/mariadb-5.5.34/sql/sql_select.cc:2859
#20 0x00000000006413c5 in mysql_select (thd=0x7ffd9b213060, rref_pointer_array=0x7ffd9b216cc8, tables=0x7ffda3a26078, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_p\
aram=0x0, select_options=2147748608, result=0x7ffda38d5c10, unit=0x7ffd9b216388, select_lex=0x7ffd9b216a60) at /home/prohaska/ma5534/mariadb-5.5.34/sql/sql_select.cc:3079

Comment by Sergei Golubchik [ 2013-12-12 ]

So, you're saying, TokuDB gets confused, because MariaDB enables keyread, but then uses the tables scan (with rnd_init()) and not the index scan (with index_init()) ?

I admit, this is a bit weird, but I still don't see why that could be a problem.

Comment by Rich Prohaska [ 2013-12-12 ]

if key_read is set, rnd_next on a tokudb table without a PK will not unpack any of the column data.

Comment by Sergei Golubchik [ 2013-12-12 ]

Rich, we'll try to make sure that keyread is only requested for index scans, not for table scans.

But I'd still suggest you to fix TokuDB to ignore key_read flag in rnd_next. It is simply not applicable there.

Comment by Rich Prohaska [ 2013-12-12 ]

What about a query in which the PK is a covering key?

Comment by Sergei Golubchik [ 2013-12-12 ]

When MariaDB (or MySQL, fwiw) is doing rnd_init/rnd_next it means a "table scan". Like, not using index, just read rows one by one in no specific order. So, a concept of "key read" is simply not applicable here.

Now many engines, InnoDB, NDB, and, as far as I understand, TokuDB too, implement a "table scan" via an index scan by the primary key. Which is fine, but the optimizer doesn't really care how the "table scan" is implemented, the optimizer doesn't specifically ask for a PK scan. From the optimizer point of view no index is used, and "key read" does not apply.

If the optimizer would want you to scan the PK, it would do index_init(idx=0), index_first, index_next, etc. And if there's a query where the PK is a covering key, optimizer usually prefers that.

Comment by Rich Prohaska [ 2013-12-13 ]

in ha_tokudb::rnd_init, we now set key_read = 0 and the problem is fixed.

Comment by Timour Katchaounov (Inactive) [ 2013-12-13 ]

Rich,

Please note that during the execution of the same query, the query engine may alternatively access the same table with different access methods. Specifically for the subquery test in this bug, if the outer record contains NULL we use a table scan, if there are no NULLs, then we use an index lookup. So when we initialize the next index access, if key_read was chosen initially it has to be restored again.

I extended the test case as follows to cover this situation. Please check that all results for the same query against InnoDB and TokuDB are the same, as well as all results with materialization on/off are the same:

CREATE TABLE t1 (a1 char(2), a2 char(2)) ENGINE=InnoDB;
INSERT INTO t1 VALUES (NULL, 'xx');
INSERT INTO t1 VALUES ('yy', 'xx');
INSERT INTO t1 VALUES ('yy', NULL);
INSERT INTO t1 VALUES ('zz', 'zz');

CREATE TABLE t2t (b char(2), INDEX(b)) ENGINE=TokuDB;
CREATE TABLE t2i (b char(2), INDEX(b)) ENGINE=InnoDB;
INSERT INTO t2t VALUES ('yy'),('zz');
INSERT INTO t2i VALUES ('yy'),('zz');

SET optimizer_switch = 'materialization=on';

explain extended
SELECT * FROM t1 WHERE (a1, a2) NOT IN ( SELECT b, b FROM t2t);
SELECT * FROM t1 WHERE (a1, a2) NOT IN ( SELECT b, b FROM t2t);

explain extended
SELECT * FROM t1 WHERE (a1, a2) NOT IN ( SELECT b, b FROM t2i);
SELECT * FROM t1 WHERE (a1, a2) NOT IN ( SELECT b, b FROM t2i);

explain extended
select a1, a2, (a1, a2) IN ( SELECT b, b FROM t2t) as sq from t1;
select a1, a2, (a1, a2) IN ( SELECT b, b FROM t2t) as sq from t1;

explain extended
select a1, a2, (a1, a2) IN ( SELECT b, b FROM t2i) as sq from t1;
select a1, a2, (a1, a2) IN ( SELECT b, b FROM t2i) as sq from t1;

SET optimizer_switch = 'materialization=off';

explain extended
SELECT * FROM t1 WHERE (a1, a2) NOT IN ( SELECT b, b FROM t2t);
SELECT * FROM t1 WHERE (a1, a2) NOT IN ( SELECT b, b FROM t2t);

explain extended
SELECT * FROM t1 WHERE (a1, a2) NOT IN ( SELECT b, b FROM t2i);
SELECT * FROM t1 WHERE (a1, a2) NOT IN ( SELECT b, b FROM t2i);

explain extended
select a1, a2, (a1, a2) IN ( SELECT b, b FROM t2t) as sq from t1;
select a1, a2, (a1, a2) IN ( SELECT b, b FROM t2t) as sq from t1;

explain extended
select a1, a2, (a1, a2) IN ( SELECT b, b FROM t2i) as sq from t1;
select a1, a2, (a1, a2) IN ( SELECT b, b FROM t2i) as sq from t1;

Comment by Rich Prohaska [ 2013-12-14 ]

bug fix is here:
https://github.com/Tokutek/ft-engine/commit/a16c8ef8caa9f5b14267c3599fdd6bb9395e6749

Comment by Timour Katchaounov (Inactive) [ 2013-12-19 ]

Rich,

I don't see any test case in the mentioned commit. I can add a test case, but only after we merge in your fix. Alternatively I could send you a test case that you can just copy/paste in a .test file (e.g. subselect4.test, or some TokuDB-specific test file).

Let me know how do you prefer to proceed.

Comment by Rich Prohaska [ 2013-12-19 ]

I am using your test case. See this commit: https://github.com/Tokutek/mariadb/commit/219f3382426ca84eb80fa23d70719ebd46736dab. I would be interested in any additional tests.

Comment by Timour Katchaounov (Inactive) [ 2013-12-19 ]

I don't have additional tests, these cover the cases I am aware of. Perhaps I could discover other execution strategies with the same problem, and make tests for them, but that would be a rather time-consuming exercise. Since you added the test case as well, I will close this bug.

Comment by Timour Katchaounov (Inactive) [ 2013-12-19 ]

Fixed in TokuDB. Test case added to TokuDB:
https://github.com/Tokutek/mariadb/commit/219f3382426ca84eb80fa23d70719ebd46736dab

Generated at Thu Feb 08 07:04:06 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.