From: Date: May 18 2005 12:44pm Subject: bk commit into 5.0 tree (bell:1.1847) BUG#6384 List-Archive: http://lists.mysql.com/internals/25026 X-Bug: 6384 Message-Id: <20050518104449.E63C259AE39@book.sanja.is.com.ua> 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;