bk commit into 5.0 tree (bell:1.1847) BUG#6384

[sanja]

Below is the list of changes that have just been committed into a local
5.0 repository of bell. When bell 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
  1.1847 05/05/18 13:44:20 bell@stripped +3 -0
  do not try to use equal field from outer query as field from local join (BUG#6384)

  sql/sql_select.cc
    1.318 05/05/18 13:44:05 bell@stripped +7 -3
    do not set equal field for oputer query

  mysql-test/t/subselect.test
    1.99 05/05/18 13:44:05 bell@stripped +13 -1
    test suite for bug#6384

  mysql-test/r/subselect.result
    1.113 05/05/18 13:44:05 bell@stripped +13 -3
    changes in plan
    test suite for bug#6384

# 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:	bell
# Host:	book.sanja.is.com.ua
# Root:	/Users/bell/mysql/bk/work-bug2-5.0

--- 1.317/sql/sql_select.cc	2005-05-16 13:36:15 +03:00
+++ 1.318/sql/sql_select.cc	2005-05-18 13:44:05 +03:00
@@ -6337,7 +6337,9 @@
     Item *left_item= ((Item_func*) item)->arguments()[0];
     Item *right_item= ((Item_func*) item)->arguments()[1];
     if (left_item->type() == Item::FIELD_ITEM &&
-        right_item->type() == Item::FIELD_ITEM)
+        right_item->type() == Item::FIELD_ITEM &&
+        !((Item_field*)left_item)->depended_from &&
+        !((Item_field*)right_item)->depended_from)
     {
       /* The predicate the form field1=field2 is processed */
 
@@ -6416,13 +6418,15 @@
       /* The predicate of the form field=const/const=field is processed */
       Item *const_item= 0;
       Item_field *field_item= 0;
-      if (left_item->type() == Item::FIELD_ITEM && 
+      if (left_item->type() == Item::FIELD_ITEM &&
+          !((Item_field*)left_item)->depended_from &&
           right_item->const_item())
       {
         field_item= (Item_field*) left_item;
         const_item= right_item;
       }
-      else if (right_item->type() == Item::FIELD_ITEM && 
+      else if (right_item->type() == Item::FIELD_ITEM &&
+               !((Item_field*)right_item)->depended_from &&
                left_item->const_item())
       {
         field_item= (Item_field*) right_item;

--- 1.112/mysql-test/r/subselect.result	2005-05-17 23:45:52 +03:00
+++ 1.113/mysql-test/r/subselect.result	2005-05-18 13:44:05 +03:00
@@ -333,7 +333,7 @@
 2	DEPENDENT SUBQUERY	t7	eq_ref	PRIMARY	PRIMARY	4	test.t6.clinic_uq	1	Using index
 Warnings:
 Note	1276	Field or reference 'clinic_uq' of SELECT #2 was resolved in SELECT #1
-Note	1003	select `test`.`t6`.`patient_uq` AS `patient_uq`,`test`.`t6`.`clinic_uq` AS
`clinic_uq` from `test`.`t6` where exists(select 1 AS `Not_used` from `test`.`t7` where
(`test`.`t6`.`clinic_uq` = `test`.`t7`.`uq`))
+Note	1003	select `test`.`t6`.`patient_uq` AS `patient_uq`,`test`.`t6`.`clinic_uq` AS
`clinic_uq` from `test`.`t6` where exists(select 1 AS `Not_used` from `test`.`t7` where
(`test`.`t7`.`uq` = `test`.`t6`.`clinic_uq`))
 select * from t1 where a= (select a from t2,t4 where t2.b=t4.b);
 ERROR 23000: Column 'a' in field list is ambiguous
 drop table t1,t2,t3;
@@ -1744,7 +1744,7 @@
 2	DEPENDENT SUBQUERY	t1	eq_ref	PRIMARY	PRIMARY	4	test.tt.id	1	Using where; Using index
 Warnings:
 Note	1276	Field or reference 'tt.id' of SELECT #2 was resolved in SELECT #1
-Note	1003	select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1`
`tt` where not(exists(select `test`.`t1`.`id` AS `id` from `test`.`t1` where
((`test`.`t1`.`id` < 8) and (`test`.`tt`.`id` = `test`.`t1`.`id`)) having
(`test`.`t1`.`id` is not null)))
+Note	1003	select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1`
`tt` where not(exists(select `test`.`t1`.`id` AS `id` from `test`.`t1` where
((`test`.`t1`.`id` < 8) and (`test`.`t1`.`id` = `test`.`tt`.`id`)) having
(`test`.`t1`.`id` is not null)))
 insert into t1 (id, text) values (1000, 'text1000'), (1001, 'text1001');
 create table t2 (id int not null, text varchar(20) not null default '', primary key
(id));
 insert into t2 (id, text) values (1, 'text1'), (2, 'text2'), (3, 'text3'), (4, 'text4'),
(5, 'text5'), (6, 'text6'), (7, 'text7'), (8, 'text8'), (9, 'text9'), (10, 'text10'),
(11, 'text1'), (12, 'text2'), (13, 'text3'), (14, 'text4'), (15, 'text5'), (16, 'text6'),
(17, 'text7'), (18, 'text8'), (19, 'text9'), (20, 'text10'),(21, 'text1'), (22, 'text2'),
(23, 'text3'), (24, 'text4'), (25, 'text5'), (26, 'text6'), (27, 'text7'), (28, 'text8'),
(29, 'text9'), (30, 'text10'), (31, 'text1'), (32, 'text2'), (33, 'text3'), (34, 'text4'),
(35, 'text5'), (36, 'text6'), (37, 'text7'), (38, 'text8'), (39, 'text9'), (40, 'text10'),
(41, 'text1'), (42, 'text2'), (43, 'text3'), (44, 'text4'), (45, 'text5'), (46, 'text6'),
(47, 'text7'), (48, 'text8'), (49, 'text9'), (50, 'text10');
@@ -2272,7 +2272,7 @@
 2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
 Warnings:
 Note	1276	Field or reference 'up.a' of SELECT #2 was resolved in SELECT #1
-Note	1003	select `test`.`up`.`a` AS `a`,`test`.`up`.`b` AS `b` from `test`.`t1` `up`
where exists(select 1 AS `Not_used` from `test`.`t1` where (`test`.`up`.`a` =
`test`.`t1`.`a`))
+Note	1003	select `test`.`up`.`a` AS `a`,`test`.`up`.`b` AS `b` from `test`.`t1` `up`
where exists(select 1 AS `Not_used` from `test`.`t1` where (`test`.`t1`.`a` =
`test`.`up`.`a`))
 drop table t1;
 CREATE TABLE t1 (t1_a int);
 INSERT INTO t1 VALUES (1);
@@ -2804,3 +2804,13 @@
 2	NULL
 3	NULL
 DROP TABLE t1,t2,t3;
+CREATE TABLE t1 (EMPNUM   CHAR(3));
+CREATE TABLE t2 (EMPNUM   CHAR(3) );
+INSERT INTO t1 VALUES ('E1');
+INSERT INTO t2 VALUES ('E1');
+DELETE FROM t1
+WHERE t1.EMPNUM NOT IN
+(SELECT t2.EMPNUM
+FROM t2
+WHERE t1.EMPNUM = t2.EMPNUM);
+DROP TABLE t1,t2;

--- 1.98/mysql-test/t/subselect.test	2005-05-17 23:45:52 +03:00
+++ 1.99/mysql-test/t/subselect.test	2005-05-18 13:44:05 +03:00
@@ -1801,7 +1801,6 @@
 #
 # Test for bug #9516: wrong evaluation of not_null_tables attribute in SQ 
 #
-
 CREATE TABLE t1 ( c1 integer );
 INSERT INTO t1 VALUES ( 1 );
 INSERT INTO t1 VALUES ( 2 );
@@ -1823,3 +1822,16 @@
 
 DROP TABLE t1,t2,t3;
 
+#
+# Correct building of equal fields list (do not include outer fields)
+#
+CREATE TABLE t1 (EMPNUM   CHAR(3));
+CREATE TABLE t2 (EMPNUM   CHAR(3) );
+INSERT INTO t1 VALUES ('E1');
+INSERT INTO t2 VALUES ('E1');
+DELETE FROM t1
+WHERE t1.EMPNUM NOT IN
+      (SELECT t2.EMPNUM
+       FROM t2
+       WHERE t1.EMPNUM = t2.EMPNUM);
+DROP TABLE t1,t2;