[MDEV-3564] LP:719198 - Ordered_key::cmp_key_with_search_key(rownum_t): Assertion `!compare_pred[i]->null_value' failed with subquery on both sides of NOT IN and materialization Created: 2011-02-15  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Trivial
Reporter: Philip Stoev (Inactive) Assignee: Timour Katchaounov (Inactive)
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug719198.xml    

 Description   

Repeatable with maria-5.3 and maria-5.3-mwl89. A crash happens when there is a subquery on both sides of a NOT IN statement. It appears that the right-side table must have at least 100 rows for the crash to occur.

backtrace:

mysqld: item_subselect.cc:4512: int Ordered_key::cmp_key_with_search_key(rownum_t): Assertion `!compare_pred[i]->null_value' failed.

#8 0x006b0d98 in __assert_fail () from /lib/libc.so.6
#9 0x0823f8fd in Ordered_key::cmp_key_with_search_key (this=0xae675bb8, row_num=49) at item_subselect.cc:4512
#10 0x0823f9e0 in Ordered_key::lookup (this=0xae675bb8) at item_subselect.cc:4538
#11 0x08240bdd in subselect_rowid_merge_engine::partial_match (this=0xae675af8) at item_subselect.cc:5056
#12 0x0823fefc in subselect_partial_match_engine::exec (this=0xae675af8) at item_subselect.cc:4698
#13 0x08236995 in Item_subselect::exec (this=0xae62ed10) at item_subselect.cc:484
#14 0x08236b8e in Item_in_subselect::exec (this=0xae62ed10) at item_subselect.cc:581
#15 0x082369c4 in Item_subselect::exec (this=0xae62ed10) at item_subselect.cc:489
#16 0x08236b8e in Item_in_subselect::exec (this=0xae62ed10) at item_subselect.cc:581
#17 0x0823850d in Item_in_subselect::val_bool (this=0xae62ed10) at item_subselect.cc:1257
#18 0x081d659c in Item::val_bool_result (this=0xae62ed10) at item.h:785
#19 0x08201f4d in Item_in_optimizer::val_int (this=0xae62f198) at item_cmpfunc.cc:1960
#20 0x081d653e in Item::val_int_result (this=0xae62f198) at item.h:781
#21 0x081d2262 in Item_cache_int::cache_value (this=0xae674860) at item.cc:7820
#22 0x081da1e2 in Item_cache_wrapper::cache (this=0xae6747f8) at item.cc:6825
#23 0x081cffe1 in Item_cache_wrapper::val_bool (this=0xae6747f8) at item.cc:6989
#24 0x081fe1f7 in Item_func_not::val_int (this=0xae62ee30) at item_cmpfunc.cc:287
#25 0x0832475a in evaluate_join_record (join=0xae665260, join_tab=0xae62f6f0, error=0) at sql_select.cc:13923
#26 0x083243c5 in sub_select (join=0xae665260, join_tab=0xae62f6f0, end_of_records=false) at sql_select.cc:13828
#27 0x08323747 in do_select (join=0xae665260, fields=0xae81624, table=0x0, procedure=0x0) at sql_select.cc:13363
#28 0x0830a3cb in JOIN::exec (this=0xae665260) at sql_select.cc:2435
#29 0x0830ab99 in mysql_select (thd=0xae7fbf8, rref_pointer_array=0xae81694, tables=0xae62dbc0, wild_num=1, fields=..., conds=0xae62ee30, og_num=0,
order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0xae62ef28, unit=0xae812f4, select_lex=0xae81590)
at sql_select.cc:2652
#30 0x08303441 in handle_select (thd=0xae7fbf8, lex=0xae81298, result=0xae62ef28, setup_tables_done_option=0) at sql_select.cc:282
#31 0x082a0f54 in execute_sqlcom_select (thd=0xae7fbf8, all_tables=0xae62dbc0) at sql_parse.cc:5070
#32 0x08297f63 in mysql_execute_command (thd=0xae7fbf8) at sql_parse.cc:2234
#33 0x082a34ec in mysql_parse (thd=0xae7fbf8, rawbuf=0xae62da10 "SELECT *\nFROM t2\nWHERE ( SELECT f1 FROM t4 ) NOT IN ( SELECT f1 FROM t1 )", length=73,
found_semicolon=0xae9a0228) at sql_parse.cc:6077
#34 0x08295bfb in dispatch_command (command=COM_QUERY, thd=0xae7fbf8, packet=0xae99d31 "", packet_length=74) at sql_parse.cc:1210
#35 0x082950a8 in do_command (thd=0xae7fbf8) at sql_parse.cc:903
#36 0x08292186 in handle_one_connection (arg=0xae7fbf8) at sql_connect.cc:1154
#37 0x00821919 in start_thread () from /lib/libpthread.so.0
#38 0x0076acce in clone () from /lib/libc.so.6

test case:

CREATE TABLE t1 ( f1 int(11)) ;
INSERT IGNORE INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),(40),(41),(42),(43),(44),(45),(46),(47),(48),(49),(50),(51),(52),(53),(54),(55),(56),(57),(58),(59),(60),(61),(62),(63),(64),(65),(66),(67),(68),(69),(70),(71),(72),(73),(74),(75),(76),(77),(78),(79),(80),(81),(82),(83),(84),(85),(86),(87),(88),(89),(90),(91),(92),(93),(94),(95),(96),(97),(98),(99),(100);

CREATE TABLE t2 ( f11 varchar(1) );
INSERT IGNORE INTO t2 VALUES (f),(d);

CREATE TABLE t4 ( f1 int(11)) ;

set session optimizer_switch='materialization=on;in_to_exists=off';

SELECT *
FROM t2
WHERE ( SELECT f1 FROM t4 ) NOT IN ( SELECT f1 FROM t1 ) ;

 Comments   
Comment by Philip Stoev (Inactive) [ 2011-02-15 ]

Re: Ordered_key::cmp_key_with_search_key(rownum_t): Assertion `!compare_pred[i]->null_value' failed with subquery on both sides of NOT IN
This bug was not detected previously since subselects on the left side of the IN statement were only introduced recently.

Comment by Philip Stoev (Inactive) [ 2011-02-15 ]

Re: Ordered_key::cmp_key_with_search_key(rownum_t): Assertion `!compare_pred[i]->null_value' failed with subquery on both sides of NOT IN
By "introduced recently" I meant added to the space of queries the RQG considers.

Comment by Philip Stoev (Inactive) [ 2011-02-15 ]

Re: Ordered_key::cmp_key_with_search_key(rownum_t): Assertion `!compare_pred[i]->null_value' failed with subquery on both sides of NOT IN
Fixed test case, some quoting and separators were garbled:

CREATE TABLE t1 ( f1 int(11)) ;
INSERT IGNORE INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),(40),(41),(42),(43),(44),(45),(46),(47),(48),(49),(50),(51),(52),(53),(54),(55),(56),(57),(58),(59),(60),(61),(62),(63),(64),(65),(66),(67),(68),(69),(70),(71),(72),(73),(74),(75),(76),(77),(78),(79),(80),(81),(82),(83),(84),(85),(86),(87),(88),(89),(90),(91),(92),(93),(94),(95),(96),(97),(98),(99),(100);

CREATE TABLE t2 ( f11 varchar(1) );
INSERT IGNORE INTO t2 VALUES ('f'),('d');

CREATE TABLE t4 ( f1 int(11)) ;

set session optimizer_switch='materialization=on,in_to_exists=off';

SELECT *
FROM t2
WHERE ( SELECT f1 FROM t4 ) NOT IN ( SELECT f1 FROM t1 ) ;

Comment by Philip Stoev (Inactive) [ 2011-03-07 ]

Re: Ordered_key::cmp_key_with_search_key(rownum_t): Assertion `!compare_pred[i]->null_value' failed with subquery on both sides of NOT IN and materialization
see also https://bugs.launchpad.net/bugs/730604

Comment by Timour Katchaounov (Inactive) [ 2011-03-30 ]

Re: Ordered_key::cmp_key_with_search_key(rownum_t): Assertion `!compare_pred[i]->null_value' failed with subquery on both sides of NOT IN and materialization
The committed fix for the bug is incomplete, because in the following case the
result must be empty, while it isn't.

CREATE TABLE t1 (f1a int, f1b int) ;
INSERT IGNORE INTO t1 VALUES (1,1),(2,2);
CREATE TABLE t2 ( f2 int);
INSERT IGNORE INTO t2 VALUES (3),(4);
CREATE TABLE t3 (f3a int, f3b int);
insert into t3 values (1,1),(2,2);

set @@optimizer_switch='materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off,in_to_exists=off';

– wrong result with a single column:
SELECT * FROM t2 WHERE (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1);

– correct empty result with two columns:
SELECT * FROM t2 WHERE (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1);

Comment by Timour Katchaounov (Inactive) [ 2011-03-30 ]

Re: Ordered_key::cmp_key_with_search_key(rownum_t): Assertion `!compare_pred[i]->null_value' failed with subquery on both sides of NOT IN and materialization
In fact, the bug has been fixed properly in 5.3, however, due to the different
query processing of subqueries in MWL#89, the bug is still present there.

Comment by Timour Katchaounov (Inactive) [ 2011-04-04 ]

Re: Ordered_key::cmp_key_with_search_key(rownum_t): Assertion `!compare_pred[i]->null_value' failed with subquery on both sides of NOT IN and materialization
The wrong result mentioned above seems to be unrelated to this bug,
and has been filed as a separate BUG #747278. Thus I am closing this bug.

Comment by Rasmus Johansson (Inactive) [ 2011-12-13 ]

Launchpad bug id: 719198

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