Below is the list of changes that have just been committed into a local
5.0 repository of marcsql. When marcsql 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-03-01 15:10:14-07:00, malff@weblab.(none) +2 -0
Manual merge of 5.0 into 5.0-runtime
mysql-test/r/subselect.result@stripped, 2007-03-01 15:10:11-07:00, malff@weblab.(none) +51 -9
Manual merge
mysql-test/t/subselect.test@stripped, 2007-03-01 15:10:11-07:00, malff@weblab.(none) +28 -0
Manual merge
# 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: malff
# Host: weblab.(none)
# Root: /home/marcsql/TREE/mysql-5.0-rt-merge
--- 1.175/mysql-test/r/subselect.result 2007-03-01 15:10:19 -07:00
+++ 1.176/mysql-test/r/subselect.result 2007-03-01 15:10:19 -07:00
@@ -224,7 +224,7 @@ id select_type table type possible_keys
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2
3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 Using where
Warnings:
-Note 1276 Field or reference 't4.a' of SELECT #3 was resolved in SELECT #1
+Note 1276 Field or reference 'test.t4.a' of SELECT #3 was resolved in SELECT #1
Note 1003 select `test`.`t4`.`b` AS `b`,(select avg((`test`.`t2`.`a` + (select min(`test`.`t3`.`a`) AS `min(t3.a)` from `test`.`t3` where (`test`.`t3`.`a` >= `test`.`t4`.`a`)))) AS `avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a))` from `test`.`t2`) AS `(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2)` from `test`.`t4`
select * from t3 where exists (select * from t2 where t2.b=t3.a);
a
@@ -313,8 +313,8 @@ id select_type table type possible_keys
3 DEPENDENT UNION t5 ALL NULL NULL NULL NULL 2 Using where
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
Warnings:
-Note 1276 Field or reference 't2.a' of SELECT #2 was resolved in SELECT #1
-Note 1276 Field or reference 't2.a' of SELECT #3 was resolved in SELECT #1
+Note 1276 Field or reference 'test.t2.a' of SELECT #2 was resolved in SELECT #1
+Note 1276 Field or reference 'test.t2.a' of SELECT #3 was resolved in SELECT #1
Note 1003 select (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = `test`.`t2`.`a`) union select `test`.`t5`.`a` AS `a` from `test`.`t5` where (`test`.`t5`.`a` = `test`.`t2`.`a`)) AS `(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)`,`test`.`t2`.`a` AS `a` from `test`.`t2`
select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2;
ERROR 21000: Subquery returns more than 1 row
@@ -330,9 +330,9 @@ patient_uq clinic_uq
explain extended select * from t6 where exists (select * from t7 where uq = clinic_uq);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t6 ALL NULL NULL NULL NULL 4 Using where
-2 DEPENDENT SUBQUERY t7 eq_ref PRIMARY PRIMARY 4 test.t6.clinic_uq 1 Using index
+2 DEPENDENT SUBQUERY t7 eq_ref PRIMARY PRIMARY 4 t6.clinic_uq 1 Using where; Using index
Warnings:
-Note 1276 Field or reference 'clinic_uq' of SELECT #2 was resolved in SELECT #1
+Note 1276 Field or reference 'test.t6.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`.`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
@@ -868,7 +868,7 @@ explain extended select (select a+1) fro
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3
Warnings:
-Note 1276 Field or reference 'a' of SELECT #2 was resolved in SELECT #1
+Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
Note 1249 Select 2 was reduced during optimization
Note 1003 select (`test`.`t1`.`a` + 1) AS `(select a+1)` from `test`.`t1`
select (select a+1) from t1;
@@ -1741,9 +1741,9 @@ Note 1003 select `test`.`t1`.`id` AS `id
explain extended select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY tt ALL NULL NULL NULL NULL 12 Using where
-2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 test.tt.id 1 Using where; Using index
+2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 tt.id 1 Using where; Using index
Warnings:
-Note 1276 Field or reference 'tt.id' of SELECT #2 was resolved in SELECT #1
+Note 1276 Field or reference 'test.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`.`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));
@@ -2279,7 +2279,7 @@ id select_type table type possible_keys
1 PRIMARY up ALL NULL NULL NULL NULL 2 Using where
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 1276 Field or reference 'test.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`.`t1`.`a` = `test`.`up`.`a`))
drop table t1;
CREATE TABLE t1 (t1_a int);
@@ -3712,6 +3712,48 @@ bb 2
cc 3
dd 1
DROP TABLE t1,t2,t3;
+CREATE TABLE t1(f1 int);
+CREATE TABLE t2(f2 int, f21 int, f3 timestamp);
+INSERT INTO t1 VALUES (1),(1),(2),(2);
+INSERT INTO t2 VALUES (1,1,"2004-02-29 11:11:11"), (2,2,"2004-02-29 11:11:11");
+SELECT ((SELECT f2 FROM t2 WHERE f21=f1 LIMIT 1) * COUNT(f1)) AS sq FROM t1 GROUP BY f1;
+sq
+2
+4
+SELECT (SELECT SUM(1) FROM t2 ttt GROUP BY t2.f3 LIMIT 1) AS tt FROM t2;
+tt
+2
+2
+PREPARE stmt1 FROM 'SELECT ((SELECT f2 FROM t2 WHERE f21=f1 LIMIT 1) * COUNT(f1)) AS sq FROM t1 GROUP BY f1';
+EXECUTE stmt1;
+sq
+2
+4
+EXECUTE stmt1;
+sq
+2
+4
+DEALLOCATE PREPARE stmt1;
+SELECT f2, AVG(f21),
+(SELECT t.f3 FROM t2 AS t WHERE t2.f2=t.f2 AND t.f3=MAX(t2.f3)) AS test
+FROM t2 GROUP BY f2;
+f2 AVG(f21) test
+1 1.0000 2004-02-29 11:11:11
+2 2.0000 2004-02-29 11:11:11
+DROP TABLE t1,t2;
+CREATE TABLE t1 (a int, b INT, c CHAR(10) NOT NULL);
+INSERT INTO t1 VALUES
+(1,1,'a'), (1,2,'b'), (1,3,'c'), (1,4,'d'), (1,5,'e'),
+(2,1,'f'), (2,2,'g'), (2,3,'h'), (3,4,'i'), (3,3,'j'),
+(3,2,'k'), (3,1,'l'), (1,9,'m');
+SELECT a, MAX(b),
+(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b)) AS test
+FROM t1 GROUP BY a;
+a MAX(b) test
+1 9 m
+2 3 h
+3 4 i
+DROP TABLE t1;
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t1xt2;
--- 1.142/mysql-test/t/subselect.test 2007-03-01 15:10:19 -07:00
+++ 1.143/mysql-test/t/subselect.test 2007-03-01 15:10:19 -07:00
@@ -2602,6 +2602,34 @@ SELECT * FROM t1
DROP TABLE t1,t2,t3;
+#
+# Bug#23800: Outer fields in correlated subqueries is used in a temporary
+# table created for sorting.
+#
+CREATE TABLE t1(f1 int);
+CREATE TABLE t2(f2 int, f21 int, f3 timestamp);
+INSERT INTO t1 VALUES (1),(1),(2),(2);
+INSERT INTO t2 VALUES (1,1,"2004-02-29 11:11:11"), (2,2,"2004-02-29 11:11:11");
+SELECT ((SELECT f2 FROM t2 WHERE f21=f1 LIMIT 1) * COUNT(f1)) AS sq FROM t1 GROUP BY f1;
+SELECT (SELECT SUM(1) FROM t2 ttt GROUP BY t2.f3 LIMIT 1) AS tt FROM t2;
+PREPARE stmt1 FROM 'SELECT ((SELECT f2 FROM t2 WHERE f21=f1 LIMIT 1) * COUNT(f1)) AS sq FROM t1 GROUP BY f1';
+EXECUTE stmt1;
+EXECUTE stmt1;
+DEALLOCATE PREPARE stmt1;
+SELECT f2, AVG(f21),
+ (SELECT t.f3 FROM t2 AS t WHERE t2.f2=t.f2 AND t.f3=MAX(t2.f3)) AS test
+ FROM t2 GROUP BY f2;
+DROP TABLE t1,t2;
+CREATE TABLE t1 (a int, b INT, c CHAR(10) NOT NULL);
+INSERT INTO t1 VALUES
+ (1,1,'a'), (1,2,'b'), (1,3,'c'), (1,4,'d'), (1,5,'e'),
+ (2,1,'f'), (2,2,'g'), (2,3,'h'), (3,4,'i'), (3,3,'j'),
+ (3,2,'k'), (3,1,'l'), (1,9,'m');
+SELECT a, MAX(b),
+ (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b)) AS test
+ FROM t1 GROUP BY a;
+DROP TABLE t1;
+
#
# Bug#21904 (parser problem when using IN with a double "(())")
#
| Thread |
|---|
| • bk commit into 5.0 tree (malff:1.2434) | marc.alff | 1 Mar |
