Below is the list of changes that have just been committed into a local
5.0 repository of igor. When igor does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet@stripped, 2007-05-11 19:31:51-07:00, igor@stripped +4 -0
Fixed bug #28375: a query with an NOT IN subquery predicate may cause
a crash when the left operand of the predicate is evaluated to NULL.
It happens when the rows from the inner tables (tables from the subquery)
are accessed by index methods with key values obtained by evaluation of
the left operand of the subquery predicate. When this predicate is
evaluated to NULL an alternative access with full table scan is used
to check whether the result set returned by the subquery is empty or not.
The crash was due to the fact the info about the access methods used for
regular key values was not properly restored after a switch back from the
full scan access method had occurred.
The patch restores this info properly.
The same problem existed for queries with IN subquery predicates if they
were used not at the top level of the queries.
mysql-test/r/subselect3.result@stripped, 2007-05-11 19:31:49-07:00, igor@stripped +31 -0
Added a test case for bug #28375.
mysql-test/t/subselect3.test@stripped, 2007-05-11 19:31:49-07:00, igor@stripped +25 -0
Added a test case for bug #28375.
sql/item_subselect.cc@stripped, 2007-05-11 19:31:49-07:00, igor@stripped +4 -2
Fixed bug #28375: a query with an NOT IN subquery predicate may cause
a crash when the left operand of the predicate is evaluated to NULL.
It happens when the rows from the inner tables (tables from the subquery)
are accessed by index methods with key values obtained by evaluation of
the left operand of the subquery predicate. When this predicate is
evaluated to NULL an alternative access with full table scan is used
to check whether the result set returned by the subquery is empty or not.
The crash was due to the fact the info about the access methods used for
regular key values was not properly restored after a switch back from the
full scan access method had occurred.
The patch restores this info properly.
sql/sql_select.h@stripped, 2007-05-11 19:31:49-07:00, igor@stripped +7 -0
Fixed bug #28375: a query with an NOT IN subquery predicate may cause
a crash when the left operand of the predicate is evaluated to NULL.
In the JOIN_TAB structure two fields have been added to save info about
index methods used to access the subquery rows. The saved info is used
after a switch back from the alternative full scan access method has
occurred. The full scan is used when the left operand of the subquery
predicate is evaluated to NULL.
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: igor
# Host: olga.mysql.com
# Root: /home/igor/dev-opt/mysql-5.0-opt-bug28375
--- 1.118/sql/sql_select.h 2007-05-11 19:31:58 -07:00
+++ 1.119/sql/sql_select.h 2007-05-11 19:31:58 -07:00
@@ -159,6 +159,13 @@
Read_record_func read_first_record;
Next_select_func next_select;
READ_RECORD read_record;
+ /*
+ Currently the following two fields are used only for a [NOT] IN subquery
+ if it is executed by an alternative full table scan when the left operand of
+ the subquery predicate is evaluated to NULL.
+ */
+ Read_record_func save_read_first_record;/* to save read_first_record */
+ int (*save_read_record) (READ_RECORD *);/* to save read_record.read_record */
double worst_seeks;
key_map const_keys; /* Keys with constant part */
key_map checked_keys; /* Keys checked in find_best */
--- 1.157/sql/item_subselect.cc 2007-05-11 19:31:58 -07:00
+++ 1.158/sql/item_subselect.cc 2007-05-11 19:31:58 -07:00
@@ -1829,6 +1829,8 @@
if (cond_guard && !*cond_guard)
{
/* Change the access method to full table scan */
+ tab->save_read_first_record= tab->read_first_record;
+ tab->save_read_record= tab->read_record.read_record;
tab->read_first_record= init_read_record_seq;
tab->read_record.record= tab->table->record[0];
tab->read_record.thd= join->thd;
@@ -1849,8 +1851,8 @@
JOIN_TAB *tab= *ptab;
tab->read_record.record= 0;
tab->read_record.ref_length= 0;
- tab->read_first_record= join_read_always_key_or_null;
- tab->read_record.read_record= join_read_next_same_or_null;
+ tab->read_first_record= tab->save_read_first_record;
+ tab->read_record.read_record= tab->save_read_record;
}
executed= 1;
thd->where= save_where;
--- 1.9/mysql-test/r/subselect3.result 2007-05-11 19:31:58 -07:00
+++ 1.10/mysql-test/r/subselect3.result 2007-05-11 19:31:58 -07:00
@@ -711,3 +711,34 @@
1
4
DROP TABLE t1,t2;
+CREATE TABLE t1 (id int);
+CREATE TABLE t2 (id int PRIMARY KEY);
+CREATE TABLE t3 (id int PRIMARY KEY, name varchar(10));
+INSERT INTO t1 VALUES (2), (NULL), (3), (1);
+INSERT INTO t2 VALUES (234), (345), (457);
+INSERT INTO t3 VALUES (222,'bbb'), (333,'ccc'), (111,'aaa');
+EXPLAIN
+SELECT * FROM t1
+WHERE t1.id NOT IN (SELECT t2.id FROM t2,t3
+WHERE t3.name='xxx' AND t2.id=t3.id);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where
+2 DEPENDENT SUBQUERY t2 eq_ref PRIMARY PRIMARY 4 func 1 Using where; Using index; Full scan on NULL key
+2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 func 1 Using where; Full scan on NULL key
+SELECT * FROM t1
+WHERE t1.id NOT IN (SELECT t2.id FROM t2,t3
+WHERE t3.name='xxx' AND t2.id=t3.id);
+id
+2
+NULL
+3
+1
+SELECT (t1.id IN (SELECT t2.id FROM t2,t3
+WHERE t3.name='xxx' AND t2.id=t3.id)) AS x
+FROM t1;
+x
+0
+0
+0
+0
+DROP TABLE t1,t2,t3;
--- 1.9/mysql-test/t/subselect3.test 2007-05-11 19:31:58 -07:00
+++ 1.10/mysql-test/t/subselect3.test 2007-05-11 19:31:58 -07:00
@@ -546,3 +546,28 @@
SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1 WHERE a > 4));
DROP TABLE t1,t2;
+
+#
+# Bug #28375: crash for NOT IN subquery predicate when left operand becomes NULL
+#
+
+CREATE TABLE t1 (id int);
+CREATE TABLE t2 (id int PRIMARY KEY);
+CREATE TABLE t3 (id int PRIMARY KEY, name varchar(10));
+INSERT INTO t1 VALUES (2), (NULL), (3), (1);
+INSERT INTO t2 VALUES (234), (345), (457);
+INSERT INTO t3 VALUES (222,'bbb'), (333,'ccc'), (111,'aaa');
+
+EXPLAIN
+SELECT * FROM t1
+ WHERE t1.id NOT IN (SELECT t2.id FROM t2,t3
+ WHERE t3.name='xxx' AND t2.id=t3.id);
+SELECT * FROM t1
+ WHERE t1.id NOT IN (SELECT t2.id FROM t2,t3
+ WHERE t3.name='xxx' AND t2.id=t3.id);
+
+SELECT (t1.id IN (SELECT t2.id FROM t2,t3
+ WHERE t3.name='xxx' AND t2.id=t3.id)) AS x
+ FROM t1;
+
+DROP TABLE t1,t2,t3;
| Thread |
|---|
| • bk commit into 5.0 tree (igor:1.2488) BUG#28375 | igor | 12 May |
