summaryrefslogtreecommitdiff
path: root/mysql-test/t/derived.test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/t/derived.test')
-rw-r--r--mysql-test/t/derived.test70
1 files changed, 69 insertions, 1 deletions
diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test
index caf673d95c1..a9341ada416 100644
--- a/mysql-test/t/derived.test
+++ b/mysql-test/t/derived.test
@@ -35,6 +35,7 @@ select a from (select 1 as a) as b;
select 1 from (select 1) as a;
select * from (select * from t1 union select * from t1) a;
select * from (select * from t1 union all select * from t1) a;
+select * from (select * from t1 union all select * from t1 limit 2) a;
explain select * from (select * from t1 union select * from t1) a;
explain select * from (select * from t1 union all select * from t1) a;
CREATE TABLE t2 (a int not null);
@@ -115,7 +116,7 @@ select mail_id, if(folder.f_description!='', folder.f_description, folder.f_nam
#
create table t1 (a int);
insert into t1 values (1),(2),(3);
--- error 1149
+-- error 1287
update (select * from t1) as t1 set a = 5;
-- error 1064
delete from (select * from t1);
@@ -138,3 +139,70 @@ insert into t1 values (1),(2);
select * from ( select * from t1 union select * from t1) a,(select * from t1 union select * from t1) b;
explain select * from ( select * from t1 union select * from t1) a,(select * from t1 union select * from t1) b;
drop table t1;
+
+
+#
+# multi-update & multi-delete with derived tables
+#
+CREATE TABLE `t1` (
+ `N` int(11) unsigned NOT NULL default '0',
+ `M` tinyint(1) default '0',
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+INSERT INTO `t1` (N, M) VALUES (1, 0),(1, 0),(1, 0),(2, 0),(2, 0),(3, 0);
+UPDATE `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N SET P1.M = 2;
+select * from t1;
+-- error 1287
+UPDATE `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N SET P1.M = 2, P2.N = 2;
+-- error 1054
+UPDATE `t1` AS P1 INNER JOIN (SELECT aaaa FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N SET P1.M = 2;
+delete P1.* from `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N;
+select * from t1;
+-- error 1287
+delete P1.*,P2.* from `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N;
+-- error 1054
+delete P1.* from `t1` AS P1 INNER JOIN (SELECT aaa FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N;
+drop table t1;
+
+#
+# correct lex->current_select
+#
+CREATE TABLE t1 (
+ OBJECTID int(11) NOT NULL default '0',
+ SORTORDER int(11) NOT NULL auto_increment,
+ KEY t1_SortIndex (SORTORDER),
+ KEY t1_IdIndex (OBJECTID)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+CREATE TABLE t2 (
+ ID int(11) default NULL,
+ PARID int(11) default NULL,
+ UNIQUE KEY t2_ID_IDX (ID),
+ KEY t2_PARID_IDX (PARID)
+) engine=MyISAM DEFAULT CHARSET=latin1;
+INSERT INTO t2 VALUES (1000,0),(1001,0),(1002,0),(1003,0),(1008,1),(1009,1),(1010,1),(1011,1),(1016,2);
+CREATE TABLE t3 (
+ ID int(11) default NULL,
+ DATA decimal(10,2) default NULL,
+ UNIQUE KEY t3_ID_IDX (ID)
+) engine=MyISAM DEFAULT CHARSET=latin1;
+INSERT INTO t3 VALUES (1000,0.00),(1001,0.25),(1002,0.50),(1003,0.75),(1008,1.00),(1009,1.25),(1010,1.50),(1011,1.75);
+select 497, TMP.ID, NULL from (select 497 as ID, MAX(t3.DATA) as DATA from t1 join t2 on (t1.ObjectID = t2.ID) join t3 on (t1.ObjectID = t3.ID) group by t2.ParID order by DATA DESC) as TMP;
+drop table t1, t2, t3;
+
+
+#
+# explain derived
+#
+CREATE TABLE t1 (name char(1) default NULL, val int(5) default NULL);
+INSERT INTO t1 VALUES ('a',1), ('a',2), ('a',2), ('a',2), ('a',3), ('a',6), ('a',7), ('a',11), ('a',11), ('a',12), ('a',13), ('a',13), ('a',20), ('b',2), ('b',3), ('b',4), ('b',5);
+SELECT s.name, AVG(s.val) AS median FROM (SELECT x.name, x.val FROM t1 x, t1 y WHERE x.name=y.name GROUP BY x.name, x.val HAVING SUM(y.val <= x.val) >= COUNT(*)/2 AND SUM(y.val >= x.val) >= COUNT(*)/2) AS s GROUP BY s.name;
+explain SELECT s.name, AVG(s.val) AS median FROM (SELECT x.name, x.val FROM t1 x, t1 y WHERE x.name=y.name GROUP BY x.name, x.val HAVING SUM(y.val <= x.val) >= COUNT(*)/2 AND SUM(y.val >= x.val) >= COUNT(*)/2) AS s GROUP BY s.name;
+drop table t1;
+
+#
+# "Using index" in explain
+#
+create table t2 (a int, b int, primary key (a));
+insert into t2 values (1,7),(2,7);
+explain select a from t2 where a>1;
+explain select a from (select a from t2 where a>1) tt;
+drop table t2;