| List: | Commits | « Previous MessageNext Message » | |
| From: | igor | Date: | September 25 2006 3:26pm |
| Subject: | bk commit into 5.0 tree (igor:1.2278) | ||
| View as plain text | |||
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, 2006-09-25 06:25:53-07:00, igor@stripped +3 -0 Merge rurik.mysql.com:/home/igor/mysql-4.1-opt into rurik.mysql.com:/home/igor/mysql-5.0-opt MERGE: 1.1616.2144.220 mysql-test/r/subselect.result@stripped, 2006-09-25 06:25:49-07:00, igor@stripped +2 -2 Manual merge MERGE: 1.67.1.116 mysql-test/t/subselect.test@stripped, 2006-09-25 06:25:49-07:00, igor@stripped +2 -484 Manual merge MERGE: 1.58.1.102 sql/item.cc@stripped, 2006-09-25 06:25:49-07:00, igor@stripped +1 -1 manual merge MERGE: 1.58.1.175 # 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: rurik.mysql.com # Root: /home/igor/mysql-5.0-opt/RESYNC --- 1.234/sql/item.cc 2006-09-25 06:26:01 -07:00 +++ 1.235/sql/item.cc 2006-09-25 06:26:01 -07:00 @@ -1182,6 +1182,7 @@ split_sum_func(thd, ref_pointer_array, fields); } else if ((type() == SUM_FUNC_ITEM || (used_tables() & ~PARAM_TABLE_BIT)) && + type() != SUBSELECT_ITEM && (type() != REF_ITEM || ((Item_ref*)this)->ref_type() == Item_ref::VIEW_REF)) { --- 1.160/mysql-test/r/subselect.result 2006-09-25 06:26:03 -07:00 +++ 1.161/mysql-test/r/subselect.result 2006-09-25 06:26:03 -07:00 @@ -1160,7 +1160,7 @@ PRIMARY KEY (Code) ) ENGINE=MyISAM; INSERT INTO t2 VALUES ('AUS','Australia','Oceania','Australia and New Zealand',7741220.00,1901,18886000,79.8,351182.00,392911.00,'Australia','Constitutional Monarchy, Federation','Elisabeth II',135,'AU'); -INSERT INTO t2 VALUES ('AZE','Azerbaijan','Asia','Middle East',86600.00,1991,7734000,62.9,4127.00,4100.00,'Azärbaycan','Federal Republic','Heydär Äliyev',144,'AZ'); +INSERT INTO t2 VALUES ('AZE','Azerbaijan','Asia','Middle East',86600.00,1991,7734000,62.9,4127.00,4100.00,'Azärbaycan','Federal Republic','Heydär Äliyev',144,'AZ'); select t2.Continent, t1.Name, t1.Population from t2 LEFT JOIN t1 ON t2.Code = t1.t2 where t1.Population IN (select max(t1.Population) AS Population from t1, t2 where t1.t2 = t2.Code group by Continent); Continent Name Population Oceania Sydney 3276207 @@ -2512,7 +2512,7 @@ ) ENGINE=MyISAM; INSERT INTO t1 VALUES ('XXX','Xxxxx','Oceania','Xxxxxx',26.00,0,0,0,0,0,'Xxxxx','Xxxxx','Xxxxx',NULL,'XX'); INSERT INTO t1 VALUES ('ASM','American Samoa','Oceania','Polynesia',199.00,0,68000,75.1,334.00,NULL,'Amerika Samoa','US Territory','George W. Bush',54,'AS'); -INSERT INTO t1 VALUES ('ATF','French Southern territories','Antarctica','Antarctica',7780.00,0,0,NULL,0.00,NULL,'Terres australes françaises','Nonmetropolitan Territory of France','Jacques Chirac',NULL,'TF'); +INSERT INTO t1 VALUES ('ATF','French Southern territories','Antarctica','Antarctica',7780.00,0,0,NULL,0.00,NULL,'Terres australes françaises','Nonmetropolitan Territory of France','Jacques Chirac',NULL,'TF'); INSERT INTO t1 VALUES ('UMI','United States Minor Outlying Islands','Oceania','Micronesia/Caribbean',16.00,0,0,NULL,0.00,NULL,'United States Minor Outlying Islands','Dependent Territory of the US','George W. Bush',NULL,'UM'); /*!40000 ALTER TABLE t1 ENABLE KEYS */; SELECT DISTINCT Continent AS c FROM t1 WHERE Code <> SOME ( SELECT Code FROM t1 WHERE Continent = c AND Population < 200); @@ -2966,6 +2966,42 @@ a a b 10 1 359 drop table t1,t2; +CREATE TABLE t1 ( +field1 int NOT NULL, +field2 int NOT NULL, +field3 int NOT NULL, +PRIMARY KEY (field1,field2,field3) +); +CREATE TABLE t2 ( +fieldA int NOT NULL, +fieldB int NOT NULL, +PRIMARY KEY (fieldA,fieldB) +); +INSERT INTO t1 VALUES +(1,1,1), (1,1,2), (1,2,1), (1,2,2), (1,2,3), (1,3,1); +INSERT INTO t2 VALUES (1,1), (1,2), (1,3); +SELECT field1, field2, COUNT(*) +FROM t1 GROUP BY field1, field2; +field1 field2 COUNT(*) +1 1 2 +1 2 3 +1 3 1 +SELECT field1, field2 +FROM t1 +GROUP BY field1, field2 +HAVING COUNT(*) >= ALL (SELECT fieldB +FROM t2 WHERE fieldA = field1); +field1 field2 +1 2 +SELECT field1, field2 +FROM t1 +GROUP BY field1, field2 +HAVING COUNT(*) < ANY (SELECT fieldB +FROM t2 WHERE fieldA = field1); +field1 field2 +1 1 +1 3 +DROP TABLE t1, t2; create table t1 (df decimal(5,1)); insert into t1 values(1.1); insert into t1 values(2.2); --- 1.128/mysql-test/t/subselect.test 2006-09-25 06:26:03 -07:00 +++ 1.129/mysql-test/t/subselect.test 2006-09-25 06:26:03 -07:00 @@ -665,7 +665,7 @@ ) ENGINE=MyISAM; INSERT INTO t2 VALUES ('AUS','Australia','Oceania','Australia and New Zealand',7741220.00,1901,18886000,79.8,351182.00,392911.00,'Australia','Constitutional Monarchy, Federation','Elisabeth II',135,'AU'); -INSERT INTO t2 VALUES ('AZE','Azerbaijan','Asia','Middle East',86600.00,1991,7734000,62.9,4127.00,4100.00,'Azärbaycan','Federal Republic','Heydär Äliyev',144,'AZ'); +INSERT INTO t2 VALUES ('AZE','Azerbaijan','Asia','Middle East',86600.00,1991,7734000,62.9,4127.00,4100.00,'Azärbaycan','Federal Republic','Heydär Äliyev',144,'AZ'); select t2.Continent, t1.Name, t1.Population from t2 LEFT JOIN t1 ON t2.Code = t1.t2 where t1.Population IN (select max(t1.Population) AS Population from t1, t2 where t1.t2 = t2.Code group by Continent); @@ -1526,7 +1526,7 @@ ) ENGINE=MyISAM; INSERT INTO t1 VALUES ('XXX','Xxxxx','Oceania','Xxxxxx',26.00,0,0,0,0,0,'Xxxxx','Xxxxx','Xxxxx',NULL,'XX'); INSERT INTO t1 VALUES ('ASM','American Samoa','Oceania','Polynesia',199.00,0,68000,75.1,334.00,NULL,'Amerika Samoa','US Territory','George W. Bush',54,'AS'); -INSERT INTO t1 VALUES ('ATF','French Southern territories','Antarctica','Antarctica',7780.00,0,0,NULL,0.00,NULL,'Terres australes françaises','Nonmetropolitan Territory of France','Jacques Chirac',NULL,'TF'); +INSERT INTO t1 VALUES ('ATF','French Southern territories','Antarctica','Antarctica',7780.00,0,0,NULL,0.00,NULL,'Terres australes françaises','Nonmetropolitan Territory of France','Jacques Chirac',NULL,'TF'); INSERT INTO t1 VALUES ('UMI','United States Minor Outlying Islands','Oceania','Micronesia/Caribbean',16.00,0,0,NULL,0.00,NULL,'United States Minor Outlying Islands','Dependent Territory of the US','George W. Bush',NULL,'UM'); /*!40000 ALTER TABLE t1 ENABLE KEYS */; SELECT DISTINCT Continent AS c FROM t1 WHERE Code <> SOME ( SELECT Code FROM t1 WHERE Continent = c AND Population < 200); @@ -1918,448 +1918,3 @@ drop table t1,t2; -# End of 4.1 tests - -# -#decimal-related tests -# -create table t1 (df decimal(5,1)); -insert into t1 values(1.1); -insert into t1 values(2.2); - -select * from t1 where df <= all (select avg(df) from t1 group by df); -select * from t1 where df >= all (select avg(df) from t1 group by df); -drop table t1; - -create table t1 (df decimal(5,1)); -insert into t1 values(1.1); -select 1.1 * exists(select * from t1); -drop table t1; - -CREATE TABLE t1 ( - grp int(11) default NULL, - a decimal(10,2) default NULL); - -insert into t1 values (1, 1), (2, 2), (2, 3), (3, 4), (3, 5), (3, 6), (NULL, NULL); -select * from t1; -select min(a) from t1 group by grp; -drop table t1; - -# -# Test for bug #9338: lame substitution of c1 instead of c2 -# - -CREATE table t1 ( c1 integer ); -INSERT INTO t1 VALUES ( 1 ); -INSERT INTO t1 VALUES ( 2 ); -INSERT INTO t1 VALUES ( 3 ); - -CREATE TABLE t2 ( c2 integer ); -INSERT INTO t2 VALUES ( 1 ); -INSERT INTO t2 VALUES ( 4 ); -INSERT INTO t2 VALUES ( 5 ); - -SELECT * FROM t1 LEFT JOIN t2 ON c1 = c2 WHERE c2 IN (1); - -SELECT * FROM t1 LEFT JOIN t2 ON c1 = c2 - WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2 IN ( 1 ) ); - -DROP TABLE t1,t2; - -# -# 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 ); -INSERT INTO t1 VALUES ( 3 ); -INSERT INTO t1 VALUES ( 6 ); - -CREATE TABLE t2 ( c2 integer ); -INSERT INTO t2 VALUES ( 1 ); -INSERT INTO t2 VALUES ( 4 ); -INSERT INTO t2 VALUES ( 5 ); -INSERT INTO t2 VALUES ( 6 ); - -CREATE TABLE t3 ( c3 integer ); -INSERT INTO t3 VALUES ( 7 ); -INSERT INTO t3 VALUES ( 8 ); - -SELECT c1,c2 FROM t1 LEFT JOIN t2 ON c1 = c2 - WHERE EXISTS (SELECT c3 FROM t3 WHERE c2 IS NULL ); - -DROP TABLE t1,t2,t3; - -# -# Item_int_with_ref check (BUG#10020) -# -CREATE TABLE `t1` ( - `itemid` bigint(20) unsigned NOT NULL auto_increment, - `sessionid` bigint(20) unsigned default NULL, - `time` int(10) unsigned NOT NULL default '0', - `type` set('A','D','E','F','G','I','L','N','U') collate latin1_general_ci NOT -NULL default '', - `data` text collate latin1_general_ci NOT NULL, - PRIMARY KEY (`itemid`) -) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; -INSERT INTO `t1` VALUES (1, 1, 1, 'D', ''); -CREATE TABLE `t2` ( - `sessionid` bigint(20) unsigned NOT NULL auto_increment, - `pid` int(10) unsigned NOT NULL default '0', - `date` int(10) unsigned NOT NULL default '0', - `ip` varchar(15) collate latin1_general_ci NOT NULL default '', - PRIMARY KEY (`sessionid`) -) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; -INSERT INTO `t2` VALUES (1, 1, 1, '10.10.10.1'); -SELECT s.ip, count( e.itemid ) FROM `t1` e JOIN t2 s ON s.sessionid = e.sessionid WHERE e.sessionid = ( SELECT sessionid FROM t2 ORDER BY sessionid DESC LIMIT 1 ) GROUP BY s.ip HAVING count( e.itemid ) >0 LIMIT 0 , 30; -drop tables t1,t2; - -# -# Correct building of equal fields list (do not include outer -# fields) (BUG#6384) -# -CREATE TABLE t1 (EMPNUM CHAR(3)); -CREATE TABLE t2 (EMPNUM CHAR(3) ); -INSERT INTO t1 VALUES ('E1'),('E2'); -INSERT INTO t2 VALUES ('E1'); -DELETE FROM t1 -WHERE t1.EMPNUM NOT IN - (SELECT t2.EMPNUM - FROM t2 - WHERE t1.EMPNUM = t2.EMPNUM); -select * from t1; -DROP TABLE t1,t2; - -# -# Test for bug #11487: range access in a subquery -# - -CREATE TABLE t1(select_id BIGINT, values_id BIGINT); -INSERT INTO t1 VALUES (1, 1); -CREATE TABLE t2 (select_id BIGINT, values_id BIGINT, - PRIMARY KEY(select_id,values_id)); -INSERT INTO t2 VALUES (0, 1), (0, 2), (0, 3), (1, 5); - -SELECT values_id FROM t1 -WHERE values_id IN (SELECT values_id FROM t2 - WHERE select_id IN (1, 0)); -SELECT values_id FROM t1 -WHERE values_id IN (SELECT values_id FROM t2 - WHERE select_id BETWEEN 0 AND 1); -SELECT values_id FROM t1 -WHERE values_id IN (SELECT values_id FROM t2 - WHERE select_id = 0 OR select_id = 1); - -DROP TABLE t1, t2; - -# BUG#11821 : Select from subselect using aggregate function on an enum -# segfaults: -create table t1 (fld enum('0','1')); -insert into t1 values ('1'); -select * from (select max(fld) from t1) as foo; -drop table t1; - -# -# Test for bug #11762: subquery with an aggregate function in HAVING -# - -CREATE TABLE t1 (a int, b int); -CREATE TABLE t2 (c int, d int); -CREATE TABLE t3 (e int); - -INSERT INTO t1 VALUES - (1,10), (2,10), (1,20), (2,20), (3,20), (2,30), (4,40); -INSERT INTO t2 VALUES - (2,10), (2,20), (4,10), (5,10), (3,20), (2,40); -INSERT INTO t3 VALUES (10), (30), (10), (20) ; - -SELECT a, MAX(b), MIN(b) FROM t1 GROUP BY a; -SELECT * FROM t2; -SELECT * FROM t3; - -SELECT a FROM t1 GROUP BY a - HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>20); -SELECT a FROM t1 GROUP BY a - HAVING a IN (SELECT c FROM t2 WHERE MAX(b)<d); -SELECT a FROM t1 GROUP BY a - HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>d); -SELECT a FROM t1 GROUP BY a - HAVING a IN (SELECT c FROM t2 - WHERE d >= SOME(SELECT e FROM t3 WHERE MAX(b)=e)); -SELECT a FROM t1 GROUP BY a - HAVING a IN (SELECT c FROM t2 - WHERE EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e <= d)); -SELECT a FROM t1 GROUP BY a - HAVING a IN (SELECT c FROM t2 - WHERE d > SOME(SELECT e FROM t3 WHERE MAX(b)=e)); -SELECT a FROM t1 GROUP BY a - HAVING a IN (SELECT c FROM t2 - WHERE EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e < d)); -SELECT a FROM t1 GROUP BY a - HAVING a IN (SELECT c FROM t2 - WHERE MIN(b) < d AND - EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e <= d)); - -SELECT a, SUM(a) FROM t1 GROUP BY a; - -SELECT a FROM t1 - WHERE EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) = c) GROUP BY a; -SELECT a FROM t1 GROUP BY a - HAVING EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) = c); - -SELECT a FROM t1 - WHERE a < 3 AND - EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) != c) GROUP BY a; -SELECT a FROM t1 - WHERE a < 3 AND - EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) != c); - -SELECT t1.a FROM t1 GROUP BY t1.a - HAVING t1.a < ALL(SELECT t2.c FROM t2 GROUP BY t2.c - HAVING EXISTS(SELECT t3.e FROM t3 GROUP BY t3.e - HAVING SUM(t1.a+t2.c) < t3.e/4)); -SELECT t1.a FROM t1 GROUP BY t1.a - HAVING t1.a > ALL(SELECT t2.c FROM t2 - WHERE EXISTS(SELECT t3.e FROM t3 GROUP BY t3.e - HAVING SUM(t1.a+t2.c) < t3.e/4)); --- error 1111 -SELECT t1.a FROM t1 GROUP BY t1.a - HAVING t1.a > ALL(SELECT t2.c FROM t2 - WHERE EXISTS(SELECT t3.e FROM t3 - WHERE SUM(t1.a+t2.c) < t3.e/4)); --- error 1111 -SELECT t1.a from t1 GROUP BY t1.a HAVING AVG(SUM(t1.b)) > 20; - -SELECT t1.a FROM t1 GROUP BY t1.a - HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c - HAVING AVG(t2.c+SUM(t1.b)) > 20); -SELECT t1.a FROM t1 GROUP BY t1.a - HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c - HAVING AVG(SUM(t1.b)) > 20); - -SELECT t1.a, SUM(b) AS sum FROM t1 GROUP BY t1.a - HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c - HAVING t2.c+sum > 20); - -DROP TABLE t1,t2,t3; - -# -# Test for bug #16603: GROUP BY in a row subquery with a quantifier -# when an index is defined on the grouping field - -CREATE TABLE t1 (a varchar(5), b varchar(10)); -INSERT INTO t1 VALUES - ('AAA', 5), ('BBB', 4), ('BBB', 1), ('CCC', 2), - ('CCC', 7), ('AAA', 2), ('AAA', 4), ('BBB', 3), ('AAA', 8); - -SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); -EXPLAIN -SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); - -ALTER TABLE t1 ADD INDEX(a); - -SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); -EXPLAIN -SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); - -DROP TABLE t1; - -# -# Bug#17366: Unchecked Item_int results in server crash -# -create table t1( f1 int,f2 int); -insert into t1 values (1,1),(2,2); -select tt.t from (select 'crash1' as t, f2 from t1) as tt left join t1 on tt.t = 'crash2' and tt.f2 = t1.f2 where tt.t = 'crash1'; -drop table t1; - -# -# Bug #18306: server crash on delete using subquery. -# - -create table t1 (c int, key(c)); -insert into t1 values (1142477582), (1142455969); -create table t2 (a int, b int); -insert into t2 values (2, 1), (1, 0); -delete from t1 where c <= 1140006215 and (select b from t2 where a = 2) = 1; -drop table t1, t2; - -# -# Bug #7549: Missing error message for invalid view selection with subquery -# - -CREATE TABLE t1 (a INT); - ---error 1054 -CREATE VIEW v1 AS SELECT * FROM t1 WHERE no_such_column = ANY (SELECT 1); ---error 1054 -CREATE VIEW v2 AS SELECT * FROM t1 WHERE no_such_column = (SELECT 1); ---error 1054 -SELECT * FROM t1 WHERE no_such_column = ANY (SELECT 1); - -DROP TABLE t1; - -# -# Bug#19077: A nested materialized derived table is used before being populated. -# -create table t1 (i int, j bigint); -insert into t1 values (1, 2), (2, 2), (3, 2); -select * from (select min(i) from t1 where j=(select * from (select min(j) from t1) t2)) t3; -drop table t1; - -# -# Bug#19700: subselect returning BIGINT always returned it as SIGNED -# -CREATE TABLE t1 (i BIGINT UNSIGNED); -INSERT INTO t1 VALUES (10000000000000000000); -- > MAX SIGNED BIGINT 9323372036854775807 -INSERT INTO t1 VALUES (1); - -CREATE TABLE t2 (i BIGINT UNSIGNED); -INSERT INTO t2 VALUES (10000000000000000000); -- same as first table -INSERT INTO t2 VALUES (1); - -/* simple test */ -SELECT t1.i FROM t1 JOIN t2 ON t1.i = t2.i; - -/* subquery test */ -SELECT t1.i FROM t1 WHERE t1.i = (SELECT MAX(i) FROM t2); - -/* subquery test with cast*/ -SELECT t1.i FROM t1 WHERE t1.i = CAST((SELECT MAX(i) FROM t2) AS UNSIGNED); - -DROP TABLE t1; -DROP TABLE t2; - -# -# Bug#20519: subselect with LIMIT M, N -# - -CREATE TABLE t1 ( - id bigint(20) unsigned NOT NULL auto_increment, - name varchar(255) NOT NULL, - PRIMARY KEY (id) -); -INSERT INTO t1 VALUES - (1, 'Balazs'), (2, 'Joe'), (3, 'Frank'); - -CREATE TABLE t2 ( - id bigint(20) unsigned NOT NULL auto_increment, - mid bigint(20) unsigned NOT NULL, - date date NOT NULL, - PRIMARY KEY (id) -); -INSERT INTO t2 VALUES - (1, 1, '2006-03-30'), (2, 2, '2006-04-06'), (3, 3, '2006-04-13'), - (4, 2, '2006-04-20'), (5, 1, '2006-05-01'); - -SELECT *, - (SELECT date FROM t2 WHERE mid = t1.id - ORDER BY date DESC LIMIT 0, 1) AS date_last, - (SELECT date FROM t2 WHERE mid = t1.id - ORDER BY date DESC LIMIT 3, 1) AS date_next_to_last - FROM t1; -SELECT *, - (SELECT COUNT(*) FROM t2 WHERE mid = t1.id - ORDER BY date DESC LIMIT 1, 1) AS date_count - FROM t1; -SELECT *, - (SELECT date FROM t2 WHERE mid = t1.id - ORDER BY date DESC LIMIT 0, 1) AS date_last, - (SELECT date FROM t2 WHERE mid = t1.id - ORDER BY date DESC LIMIT 1, 1) AS date_next_to_last - FROM t1; -DROP TABLE t1,t2; - -# -# Bug#20869: subselect with range access by DESC -# - -CREATE TABLE t1 ( - i1 int(11) NOT NULL default '0', - i2 int(11) NOT NULL default '0', - t datetime NOT NULL default '0000-00-00 00:00:00', - PRIMARY KEY (i1,i2,t) -); -INSERT INTO t1 VALUES -(24,1,'2005-03-03 16:31:31'),(24,1,'2005-05-27 12:40:07'), -(24,1,'2005-05-27 12:40:08'),(24,1,'2005-05-27 12:40:10'), -(24,1,'2005-05-27 12:40:25'),(24,1,'2005-05-27 12:40:30'), -(24,2,'2005-03-03 13:43:05'),(24,2,'2005-03-03 16:23:31'), -(24,2,'2005-03-03 16:31:30'),(24,2,'2005-05-27 12:37:02'), -(24,2,'2005-05-27 12:40:06'); - -CREATE TABLE t2 ( - i1 int(11) NOT NULL default '0', - i2 int(11) NOT NULL default '0', - t datetime default NULL, - PRIMARY KEY (i1) -); -INSERT INTO t2 VALUES (24,1,'2006-06-20 12:29:40'); - -EXPLAIN -SELECT * FROM t1,t2 - WHERE t1.t = (SELECT t1.t FROM t1 - WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1 - ORDER BY t1.t DESC LIMIT 1); -SELECT * FROM t1,t2 - WHERE t1.t = (SELECT t1.t FROM t1 - WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1 - ORDER BY t1.t DESC LIMIT 1); - -DROP TABLE t1, t2; - -# -# Bug#14654 : Cannot select from the same table twice within a UNION -# statement -# -CREATE TABLE t1 (i INT); - -(SELECT i FROM t1) UNION (SELECT i FROM t1); -SELECT sql_no_cache * FROM t1 WHERE NOT EXISTS - ( - (SELECT i FROM t1) UNION - (SELECT i FROM t1) - ); - -SELECT * FROM t1 -WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1))); - -#TODO:not supported ---error 1064 -explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12)) - from t1; -#supported -explain select * from t1 where not exists - ((select t11.i from t1 t11) union (select t12.i from t1 t12)); - -DROP TABLE t1; - -# -# Bug #21540: Subqueries with no from and aggregate functions return -# wrong results -CREATE TABLE t1 (a INT, b INT); -CREATE TABLE t2 (a INT); -INSERT INTO t2 values (1); -INSERT INTO t1 VALUES (1,1),(1,2),(2,3),(3,4); -SELECT (SELECT COUNT(DISTINCT t1.b) from t2) FROM t1 GROUP BY t1.a; -SELECT (SELECT COUNT(DISTINCT t1.b) from t2 union select 1 from t2 where 12 < 3) - FROM t1 GROUP BY t1.a; -SELECT COUNT(DISTINCT t1.b), (SELECT COUNT(DISTINCT t1.b)) FROM t1 GROUP BY t1.a; -SELECT COUNT(DISTINCT t1.b), - (SELECT COUNT(DISTINCT t1.b) union select 1 from DUAL where 12 < 3) - FROM t1 GROUP BY t1.a; -SELECT ( - SELECT ( - SELECT COUNT(DISTINCT t1.b) - ) -) -FROM t1 GROUP BY t1.a; -SELECT ( - SELECT ( - SELECT ( - SELECT COUNT(DISTINCT t1.b) - ) - ) - FROM t1 GROUP BY t1.a LIMIT 1) -FROM t1 t2 -GROUP BY t2.a; -DROP TABLE t1,t2;
| Thread | ||
|---|---|---|
| • bk commit into 5.0 tree (igor:1.2278) | igor | 25 Sep |
© 1995-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc.
Page generated in 0.694 sec. using MySQL 5.1.14-beta-log