summaryrefslogtreecommitdiff
path: root/mysql-test/r
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r')
-rw-r--r--mysql-test/r/func_gconcat.result5
-rw-r--r--mysql-test/r/func_str.result4
-rw-r--r--mysql-test/r/group_by.result116
-rw-r--r--mysql-test/r/group_min_max.result20
-rw-r--r--mysql-test/r/innodb.result16
-rw-r--r--mysql-test/r/innodb_mysql.result16
-rw-r--r--mysql-test/r/merge.result2
-rw-r--r--mysql-test/r/myisam.result16
-rw-r--r--mysql-test/r/olap.result15
-rw-r--r--mysql-test/r/rename.result10
-rw-r--r--mysql-test/r/select.result94
-rw-r--r--mysql-test/r/subselect.result36
-rw-r--r--mysql-test/r/type_decimal.result11
13 files changed, 330 insertions, 31 deletions
diff --git a/mysql-test/r/func_gconcat.result b/mysql-test/r/func_gconcat.result
index 43646bd3d0e..619c8dafeae 100644
--- a/mysql-test/r/func_gconcat.result
+++ b/mysql-test/r/func_gconcat.result
@@ -74,11 +74,6 @@ grp group_concat(c order by 1)
1 a
2 b,c
3 C,D,d,d,D,E
-select grp,group_concat(c order by "c") from t1 group by grp;
-grp group_concat(c order by "c")
-1 a
-2 b,c
-3 C,D,d,d,D,E
select grp,group_concat(distinct c order by c) from t1 group by grp;
grp group_concat(distinct c order by c)
1 a
diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result
index 4d31b2ba496..d85d3eabe5e 100644
--- a/mysql-test/r/func_str.result
+++ b/mysql-test/r/func_str.result
@@ -1143,9 +1143,9 @@ EXPLAIN EXTENDED
SELECT * FROM t1 INNER JOIN t2 ON code=id
WHERE id='a12' AND (LENGTH(code)=5 OR code < 'a00');
id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 const PRIMARY PRIMARY 12 const 1 100.00 Using index
1 SIMPLE t1 ref code code 13 const 3 100.00 Using where; Using index
-1 SIMPLE t2 ref PRIMARY PRIMARY 12 const 1 100.00 Using where; Using index
Warnings:
-Note 1003 select `test`.`t1`.`code` AS `code`,`test`.`t2`.`id` AS `id` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`code` = _latin1'a12') and (`test`.`t2`.`id` = _latin1'a12') and (length(`test`.`t1`.`code`) = 5))
+Note 1003 select `test`.`t1`.`code` AS `code`,`test`.`t2`.`id` AS `id` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`code` = _latin1'a12') and (length(`test`.`t1`.`code`) = 5))
DROP TABLE t1,t2;
End of 5.0 tests
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index 80988bd8047..663ef6cced4 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -303,10 +303,10 @@ spid sum(userid)
1 1
explain select sql_big_result score,count(*) from t1 group by score desc;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL score 3 NULL 8 Using index
+1 SIMPLE t1 index NULL score 3 NULL 8 Using index; Using filesort
explain select sql_big_result score,count(*) from t1 group by score desc order by null;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL score 3 NULL 8 Using index
+1 SIMPLE t1 index NULL score 3 NULL 8 Using index; Using filesort
select sql_big_result score,count(*) from t1 group by score desc;
score count(*)
3 5
@@ -775,6 +775,55 @@ select sql_buffer_result max(f1)+1 from t1;
max(f1)+1
3
drop table t1;
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES (1),(2);
+SELECT a FROM t1 GROUP BY 'a';
+a
+1
+SELECT a FROM t1 GROUP BY "a";
+a
+1
+SELECT a FROM t1 GROUP BY `a`;
+a
+1
+2
+set sql_mode=ANSI_QUOTES;
+SELECT a FROM t1 GROUP BY "a";
+a
+1
+2
+SELECT a FROM t1 GROUP BY 'a';
+a
+1
+SELECT a FROM t1 GROUP BY `a`;
+a
+1
+2
+set sql_mode='';
+SELECT a FROM t1 HAVING 'a' > 1;
+a
+Warnings:
+Warning 1292 Truncated incorrect DOUBLE value: 'a'
+SELECT a FROM t1 HAVING "a" > 1;
+a
+Warnings:
+Warning 1292 Truncated incorrect DOUBLE value: 'a'
+SELECT a FROM t1 HAVING `a` > 1;
+a
+2
+SELECT a FROM t1 ORDER BY 'a' DESC;
+a
+1
+2
+SELECT a FROM t1 ORDER BY "a" DESC;
+a
+1
+2
+SELECT a FROM t1 ORDER BY `a` DESC;
+a
+2
+1
+DROP TABLE t1;
create table t1 (c1 char(3), c2 char(3));
create table t2 (c3 char(3), c4 char(3));
insert into t1 values ('aaa', 'bb1'), ('aaa', 'bb2');
@@ -821,6 +870,69 @@ a b real_b
68 France France
DROP VIEW v1;
DROP TABLE t1,t2;
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT, key (b));
+INSERT INTO t1 VALUES (1, 1);
+INSERT INTO t1 SELECT a + 1 , MOD(a + 1 , 20) FROM t1;
+INSERT INTO t1 SELECT a + 2 , MOD(a + 2 , 20) FROM t1;
+INSERT INTO t1 SELECT a + 4 , MOD(a + 4 , 20) FROM t1;
+INSERT INTO t1 SELECT a + 8 , MOD(a + 8 , 20) FROM t1;
+INSERT INTO t1 SELECT a + 16, MOD(a + 16, 20) FROM t1;
+INSERT INTO t1 SELECT a + 32, MOD(a + 32, 20) FROM t1;
+INSERT INTO t1 SELECT a + 64, MOD(a + 64, 20) FROM t1;
+SELECT MIN(b), MAX(b) from t1;
+MIN(b) MAX(b)
+0 19
+EXPLAIN SELECT b, sum(1) FROM t1 GROUP BY b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL b 5 NULL 128 Using index
+EXPLAIN SELECT SQL_BIG_RESULT b, sum(1) FROM t1 GROUP BY b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL b 5 NULL 128 Using index; Using filesort
+SELECT b, sum(1) FROM t1 GROUP BY b;
+b sum(1)
+0 6
+1 7
+2 7
+3 7
+4 7
+5 7
+6 7
+7 7
+8 7
+9 6
+10 6
+11 6
+12 6
+13 6
+14 6
+15 6
+16 6
+17 6
+18 6
+19 6
+SELECT SQL_BIG_RESULT b, sum(1) FROM t1 GROUP BY b;
+b sum(1)
+0 6
+1 7
+2 7
+3 7
+4 7
+5 7
+6 7
+7 7
+8 7
+9 6
+10 6
+11 6
+12 6
+13 6
+14 6
+15 6
+16 6
+17 6
+18 6
+19 6
+DROP TABLE t1;
CREATE TABLE t1 (a INT, b INT, KEY(a));
INSERT INTO t1 VALUES (1, 1), (2, 2), (3,3), (4,4);
EXPLAIN SELECT a, SUM(b) FROM t1 GROUP BY a LIMIT 2;
diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result
index 2d72540c8a9..5485db73fbb 100644
--- a/mysql-test/r/group_min_max.result
+++ b/mysql-test/r/group_min_max.result
@@ -2162,3 +2162,23 @@ t1;
id2 id3 id5 id4 id3 id6 id5 id1
1 1 1 1 1 1 1 1
DROP TABLE t1,t2,t3,t4,t5,t6;
+CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b), KEY b (b));
+INSERT INTO t1 VALUES (1,1),(1,2),(1,0),(1,3);
+explain SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range PRIMARY,b PRIMARY 8 NULL 1 Using where; Using index for group-by
+SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
+MAX(b) a
+1 1
+SELECT MIN(b), a FROM t1 WHERE b > 1 AND a = 1 GROUP BY a;
+MIN(b) a
+2 1
+CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a,b,c));
+INSERT INTO t2 SELECT a,b,b FROM t1;
+explain SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 range PRIMARY PRIMARY 12 NULL 1 Using where; Using index for group-by
+SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a;
+MIN(c)
+2
+DROP TABLE t1,t2;
diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result
index e26e6fb271a..e7fa950a131 100644
--- a/mysql-test/r/innodb.result
+++ b/mysql-test/r/innodb.result
@@ -2063,15 +2063,15 @@ i 10
select sql_big_result v,count(c) from t1 group by v limit 10;
v count(c)
a 1
-a 10
-b 10
-c 10
-d 10
-e 10
-f 10
-g 10
+a 10
+b 10
+c 10
+d 10
+e 10
+f 10
+g 10
h 10
-i 10
+i 10
select c,count(*) from t1 group by c limit 10;
c count(*)
a 1
diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result
index 690ebeb5607..4acfaf58a7d 100644
--- a/mysql-test/r/innodb_mysql.result
+++ b/mysql-test/r/innodb_mysql.result
@@ -290,6 +290,22 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 index NULL fkey 5 NULL 5 Using index
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.fkey 1 Using where
DROP TABLE t1,t2;
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c FLOAT, KEY b(b)) ENGINE = INNODB;
+INSERT INTO t1 VALUES ( 1 , 1 , 1);
+INSERT INTO t1 SELECT a + 1 , MOD(a + 1 , 20), 1 FROM t1;
+INSERT INTO t1 SELECT a + 2 , MOD(a + 2 , 20), 1 FROM t1;
+INSERT INTO t1 SELECT a + 4 , MOD(a + 4 , 20), 1 FROM t1;
+INSERT INTO t1 SELECT a + 8 , MOD(a + 8 , 20), 1 FROM t1;
+INSERT INTO t1 SELECT a + 16, MOD(a + 16, 20), 1 FROM t1;
+INSERT INTO t1 SELECT a + 32, MOD(a + 32, 20), 1 FROM t1;
+INSERT INTO t1 SELECT a + 64, MOD(a + 64, 20), 1 FROM t1;
+EXPLAIN SELECT b, SUM(c) FROM t1 GROUP BY b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL b 5 NULL 128
+EXPLAIN SELECT SQL_BIG_RESULT b, SUM(c) FROM t1 GROUP BY b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 128 Using filesort
+DROP TABLE t1;
CREATE TABLE t1 (a int, b int);
insert into t1 values (1,1),(1,2);
CREATE TABLE t2 (primary key (a)) select * from t1;
diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result
index 1e0207683ee..277891788d1 100644
--- a/mysql-test/r/merge.result
+++ b/mysql-test/r/merge.result
@@ -629,7 +629,7 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN SELECT * FROM t2 WHERE fileset_id = 2
AND file_code = '0000000115' LIMIT 1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ref PRIMARY,files PRIMARY 35 const,const 1 Using where
+1 SIMPLE t2 const PRIMARY,files PRIMARY 35 const,const 1
DROP TABLE t2, t1;
create table t1 (x int, y int, index xy(x, y));
create table t2 (x int, y int, index xy(x, y));
diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result
index 43deb4ce846..5cf094cfa2c 100644
--- a/mysql-test/r/myisam.result
+++ b/mysql-test/r/myisam.result
@@ -1002,15 +1002,15 @@ i 10
select sql_big_result v,count(c) from t1 group by v limit 10;
v count(c)
a 1
-a 10
-b 10
-c 10
-d 10
-e 10
-f 10
-g 10
+a 10
+b 10
+c 10
+d 10
+e 10
+f 10
+g 10
h 10
-i 10
+i 10
select c,count(*) from t1 group by c limit 10;
c count(*)
a 1
diff --git a/mysql-test/r/olap.result b/mysql-test/r/olap.result
index f9e211c8186..fd657c93bd7 100644
--- a/mysql-test/r/olap.result
+++ b/mysql-test/r/olap.result
@@ -592,6 +592,21 @@ a max(b)
NULL 2
a 1
drop table t1;
+create table t1 (a varchar(22) not null , b int);
+insert into t1 values ("2006-07-01 21:30", 1), ("2006-07-01 23:30", 10);
+select left(a,10), a, sum(b) from t1 group by 1,2 with rollup;
+left(a,10) a sum(b)
+2006-07-01 2006-07-01 21:30 1
+2006-07-01 2006-07-01 23:30 10
+2006-07-01 NULL 11
+NULL NULL 11
+select left(a,10) x, a, sum(b) from t1 group by x,a with rollup;
+x a sum(b)
+2006-07-01 2006-07-01 21:30 1
+2006-07-01 2006-07-01 23:30 10
+2006-07-01 NULL 11
+NULL NULL 11
+drop table t1;
CREATE TABLE t1(id int, type char(1));
INSERT INTO t1 VALUES
(1,"A"),(2,"C"),(3,"A"),(4,"A"),(5,"B"),
diff --git a/mysql-test/r/rename.result b/mysql-test/r/rename.result
index 76c0f4422fe..345270231ef 100644
--- a/mysql-test/r/rename.result
+++ b/mysql-test/r/rename.result
@@ -54,3 +54,13 @@ Tables_in_test
t2
t4
drop table t2, t4;
+create table t1(f1 int);
+create view v1 as select * from t1;
+alter table v1 rename to v2;
+alter table v1 rename to v2;
+ERROR 42S02: Table 'test.v1' doesn't exist
+rename table v2 to v1;
+rename table v2 to v1;
+ERROR 42S01: Table 'v1' already exists
+drop view v1;
+drop table t1;
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index 8800ce248d5..f24b109d75a 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -3517,3 +3517,97 @@ id a b c d e
2 NULL NULL NULL 2 40
2 NULL NULL NULL 2 50
DROP TABLE t1,t2,t3;
+create table t1 (c1 varchar(1), c2 int, c3 int, c4 int, c5 int, c6 int,
+c7 int, c8 int, c9 int, fulltext key (`c1`));
+select distinct match (`c1`) against ('z') , c2, c3, c4,c5, c6,c7, c8
+from t1 where c9=1 order by c2, c2;
+match (`c1`) against ('z') c2 c3 c4 c5 c6 c7 c8
+drop table t1;
+CREATE TABLE t1 (pk varchar(10) PRIMARY KEY, fk varchar(16));
+CREATE TABLE t2 (pk varchar(16) PRIMARY KEY, fk varchar(10));
+INSERT INTO t1 VALUES
+('d','dddd'), ('i','iii'), ('a','aa'), ('b','bb'), ('g','gg'),
+('e','eee'), ('c','cccc'), ('h','hhh'), ('j','jjj'), ('f','fff');
+INSERT INTO t2 VALUES
+('jjj', 'j'), ('cc','c'), ('ccc','c'), ('aaa', 'a'), ('jjjj','j'),
+('hhh','h'), ('gg','g'), ('fff','f'), ('ee','e'), ('ffff','f'),
+('bbb','b'), ('ff','f'), ('cccc','c'), ('dddd','d'), ('jj','j'),
+('aaaa','a'), ('bb','b'), ('eeee','e'), ('aa','a'), ('hh','h');
+EXPLAIN SELECT t2.*
+FROM t1 JOIN t2 ON t2.fk=t1.pk
+WHERE t2.fk < 'c' AND t2.pk=t1.fk;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 3 Using where
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where
+EXPLAIN SELECT t2.*
+FROM t1 JOIN t2 ON t2.fk=t1.pk
+WHERE t2.fk BETWEEN 'a' AND 'b' AND t2.pk=t1.fk;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using where
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where
+EXPLAIN SELECT t2.*
+FROM t1 JOIN t2 ON t2.fk=t1.pk
+WHERE t2.fk IN ('a','b') AND t2.pk=t1.fk;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using where
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where
+DROP TABLE t1,t2;
+CREATE TABLE t1 (a int, b varchar(20) NOT NULL, PRIMARY KEY(a));
+CREATE TABLE t2 (a int, b varchar(20) NOT NULL,
+PRIMARY KEY (a), UNIQUE KEY (b));
+INSERT INTO t1 VALUES (1,'a'),(2,'b'),(3,'c');
+INSERT INTO t2 VALUES (1,'a'),(2,'b'),(3,'c');
+EXPLAIN SELECT t1.a FROM t1 LEFT JOIN t2 ON t2.b=t1.b WHERE t1.a=3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
+1 SIMPLE t2 const b b 22 const 1 Using index
+DROP TABLE t1,t2;
+CREATE TABLE t1(id int PRIMARY KEY, b int, e int);
+CREATE TABLE t2(i int, a int, INDEX si(i), INDEX ai(a));
+CREATE TABLE t3(a int PRIMARY KEY, c char(4), INDEX ci(c));
+INSERT INTO t1 VALUES
+(1,10,19), (2,20,22), (4,41,42), (9,93,95), (7, 77,79),
+(6,63,67), (5,55,58), (3,38,39), (8,81,89);
+INSERT INTO t2 VALUES
+(21,210), (41,410), (82,820), (83,830), (84,840),
+(65,650), (51,510), (37,370), (94,940), (76,760),
+(22,220), (33,330), (40,400), (95,950), (38,380),
+(67,670), (88,880), (57,570), (96,960), (97,970);
+INSERT INTO t3 VALUES
+(210,'bb'), (950,'ii'), (400,'ab'), (500,'ee'), (220,'gg'),
+(440,'gg'), (310,'eg'), (380,'ee'), (840,'bb'), (830,'ff'),
+(230,'aa'), (960,'ii'), (410,'aa'), (510,'ee'), (290,'bb'),
+(450,'gg'), (320,'dd'), (390,'hh'), (850,'jj'), (860,'ff');
+EXPLAIN
+SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3
+WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND
+t3.a=t2.a AND t3.c IN ('bb','ee');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
+1 SIMPLE t2 range si si 5 NULL 4 Using where
+1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where
+EXPLAIN
+SELECT t3.a FROM t1,t2,t3
+WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND
+t3.a=t2.a AND t3.c IN ('bb','ee') ;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
+1 SIMPLE t2 range si,ai si 5 NULL 4 Using where
+1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where
+EXPLAIN
+SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3
+WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND
+t3.c IN ('bb','ee');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
+1 SIMPLE t2 range si si 5 NULL 2 Using where
+1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where
+EXPLAIN
+SELECT t3.a FROM t1,t2,t3
+WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND
+t3.c IN ('bb','ee');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
+1 SIMPLE t2 range si,ai si 5 NULL 2 Using where
+1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where
+DROP TABLE t1,t2,t3;
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index a1463bc2b3c..e57cc44ddd4 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -363,12 +363,12 @@ INSERT INTO t8 (pseudo,email) VALUES ('joce1','test1');
INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1');
EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t8 ref PRIMARY PRIMARY 37 const 1 100.00 Using where; Using index
-4 SUBQUERY t8 ref PRIMARY PRIMARY 37 1 100.00 Using where; Using index
-2 SUBQUERY t8 ref PRIMARY PRIMARY 37 const 1 100.00 Using where
-3 SUBQUERY t8 ref PRIMARY PRIMARY 37 1 100.00 Using where; Using index
+1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index
+4 SUBQUERY t8 const PRIMARY PRIMARY 37 1 100.00 Using index
+2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00
+3 SUBQUERY t8 const PRIMARY PRIMARY 37 1 100.00 Using index
Warnings:
-Note 1003 select `test`.`t8`.`pseudo` AS `pseudo`,(select `test`.`t8`.`email` AS `email` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select `test`.`t8`.`pseudo` AS `pseudo` from `test`.`t8` where (`test`.`t8`.`pseudo` = _latin1'joce')))) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select `test`.`t8`.`pseudo` AS `pseudo` from `test`.`t8` where (`test`.`t8`.`pseudo` = _latin1'joce')))
+Note 1003 select `test`.`t8`.`pseudo` AS `pseudo`,(select `test`.`t8`.`email` AS `email` from `test`.`t8` where 1) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where 1
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM
t8 WHERE pseudo='joce');
ERROR 21000: Operand should contain 1 column(s)
@@ -3458,6 +3458,32 @@ id select_type table type possible_keys key key_len ref rows Extra
4 UNION t12 system NULL NULL NULL NULL 0 const row not found
NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL
DROP TABLE t1;
+CREATE TABLE t1 (a VARCHAR(250), b INT auto_increment, PRIMARY KEY (b));
+insert into t1 (a) values (FLOOR(rand() * 100));
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+SELECT a,
+(SELECT REPEAT(' ',250) FROM t1 i1
+WHERE i1.b=t1.a ORDER BY RAND() LIMIT 1) AS a
+FROM t1 ORDER BY a LIMIT 5;
+a a
+0 NULL
+0 NULL
+0 NULL
+0 NULL
+0 NULL
+DROP TABLE t1;
CREATE TABLE t1 (a INT, b INT);
CREATE TABLE t2 (a INT);
INSERT INTO t2 values (1);
diff --git a/mysql-test/r/type_decimal.result b/mysql-test/r/type_decimal.result
index 9c6a9b07544..0840872a7a9 100644
--- a/mysql-test/r/type_decimal.result
+++ b/mysql-test/r/type_decimal.result
@@ -779,3 +779,14 @@ select f1 from t1 where f1 in (select f1 from t1);
f1
40
drop table t1;
+create table t1 as
+select from_days(s) as date,t
+from (select 1 as s,'t' as t union select null, null ) as sub1;
+select group_concat(t) from t1 group by week(date)/10;
+group_concat(t)
+t
+Warnings:
+Warning 1292 Truncated incorrect datetime value: '0000-00-00'
+Warning 1292 Truncated incorrect datetime value: '0000-00-00'
+Warning 1292 Truncated incorrect datetime value: '0000-00-00'
+drop table t1;
View Lorry Controller Status