summaryrefslogtreecommitdiff
path: root/mysql-test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test')
-rw-r--r--mysql-test/r/cast.result6
-rw-r--r--mysql-test/r/join.result50
-rw-r--r--mysql-test/r/join_outer.result25
-rw-r--r--mysql-test/r/outfile.resultbin1382 -> 2135 bytes
-rw-r--r--mysql-test/r/sp-vars.result4
-rw-r--r--mysql-test/r/subselect.result7
-rw-r--r--mysql-test/r/type_datetime.result30
-rw-r--r--mysql-test/t/cast.test6
-rw-r--r--mysql-test/t/join.test24
-rw-r--r--mysql-test/t/join_outer.test20
-rw-r--r--mysql-test/t/outfile.test35
-rw-r--r--mysql-test/t/subselect.test8
-rw-r--r--mysql-test/t/type_datetime.test26
13 files changed, 239 insertions, 2 deletions
diff --git a/mysql-test/r/cast.result b/mysql-test/r/cast.result
index d8e50128902..454a3766572 100644
--- a/mysql-test/r/cast.result
+++ b/mysql-test/r/cast.result
@@ -351,6 +351,12 @@ DROP TABLE t1;
select isnull(date(NULL)), isnull(cast(NULL as DATE));
isnull(date(NULL)) isnull(cast(NULL as DATE))
1 1
+SELECT CAST(cast('01-01-01' as date) AS UNSIGNED);
+CAST(cast('01-01-01' as date) AS UNSIGNED)
+20010101
+SELECT CAST(cast('01-01-01' as date) AS SIGNED);
+CAST(cast('01-01-01' as date) AS SIGNED)
+20010101
End of 4.1 tests
select cast('1.2' as decimal(3,2));
cast('1.2' as decimal(3,2))
diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result
index c785c3be379..80dd055a170 100644
--- a/mysql-test/r/join.result
+++ b/mysql-test/r/join.result
@@ -391,6 +391,56 @@ i i i
2 NULL 4
2 2 2
drop table t1,t2,t3;
+CREATE TABLE t1 (a int, b int default 0, c int default 1);
+INSERT INTO t1 (a) VALUES (1),(2),(3),(4),(5),(6),(7),(8);
+INSERT INTO t1 (a) SELECT a + 8 FROM t1;
+INSERT INTO t1 (a) SELECT a + 16 FROM t1;
+CREATE TABLE t2 (a int, d int, e int default 0);
+INSERT INTO t2 (a, d) VALUES (1,1),(2,2),(3,3),(4,4);
+INSERT INTO t2 (a, d) SELECT a+4, a+4 FROM t2;
+INSERT INTO t2 (a, d) SELECT a+8, a+8 FROM t2;
+EXPLAIN
+SELECT STRAIGHT_JOIN t2.e FROM t1,t2 WHERE t2.d=1 AND t1.b=t2.e
+ORDER BY t1.b, t1.c;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 32 Using temporary; Using filesort
+1 SIMPLE t2 ALL NULL NULL NULL NULL 16 Using where
+SELECT STRAIGHT_JOIN t2.e FROM t1,t2 WHERE t2.d=1 AND t1.b=t2.e
+ORDER BY t1.b, t1.c;
+e
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+DROP TABLE t1,t2;
create table t1 (c int, b int);
create table t2 (a int, b int);
create table t3 (b int, c int);
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index df66336bd81..c62601946c2 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -1214,3 +1214,28 @@ SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='Bla';
f1 f2 f3
bla blah sheep
DROP TABLE t1,t2;
+CREATE TABLE t1 (id int PRIMARY KEY, a varchar(8));
+CREATE TABLE t2 (id int NOT NULL, b int NOT NULL, INDEX idx(id));
+INSERT INTO t1 VALUES
+(1,'aaaaaaa'), (5,'eeeeeee'), (4,'ddddddd'), (2,'bbbbbbb'), (3,'ccccccc');
+INSERT INTO t2 VALUES
+(3,10), (2,20), (5,30), (3,20), (5,10), (3,40), (3,30), (2,10), (2,40);
+EXPLAIN
+SELECT t1.id, a FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.b IS NULL;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 5
+1 SIMPLE t2 ref idx idx 4 test.t1.id 2 Using where; Not exists
+flush status;
+SELECT t1.id, a FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.b IS NULL;
+id a
+1 aaaaaaa
+4 ddddddd
+show status like 'Handler_read%';
+Variable_name Value
+Handler_read_first 0
+Handler_read_key 5
+Handler_read_next 0
+Handler_read_prev 0
+Handler_read_rnd 0
+Handler_read_rnd_next 6
+DROP TABLE t1,t2;
diff --git a/mysql-test/r/outfile.result b/mysql-test/r/outfile.result
index 023c4ea205f..8503df545d2 100644
--- a/mysql-test/r/outfile.result
+++ b/mysql-test/r/outfile.result
Binary files differ
diff --git a/mysql-test/r/sp-vars.result b/mysql-test/r/sp-vars.result
index 6090dfdf737..b112c6bece6 100644
--- a/mysql-test/r/sp-vars.result
+++ b/mysql-test/r/sp-vars.result
@@ -690,12 +690,12 @@ END|
CALL p1(NOW());
Table Create Table
t1 CREATE TABLE "t1" (
- "x" varbinary(19) default NULL
+ "x" datetime default NULL
)
CALL p1('test');
Table Create Table
t1 CREATE TABLE "t1" (
- "x" varbinary(19) default NULL
+ "x" datetime default NULL
)
Warnings:
Warning 1264 Out of range value adjusted for column 'x' at row 1
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 06ff23b32b7..2e82d948edb 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -4034,4 +4034,11 @@ SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING XXA < 12) ) FROM t2) )
FROM t1;
ERROR HY000: Invalid use of group function
DROP TABLE t1,t2;
+CREATE TABLE t1 (a int, b int, KEY (a));
+INSERT INTO t1 VALUES (1,1),(2,1);
+EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ref a a 5 const 1 Using where; Using index
+2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
+DROP TABLE t1;
End of 5.0 tests.
diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result
index 3a28410b7dc..3a7313f48eb 100644
--- a/mysql-test/r/type_datetime.result
+++ b/mysql-test/r/type_datetime.result
@@ -264,3 +264,33 @@ f2
SELECT 1 from dual where NOW() BETWEEN CURRENT_DATE() - INTERVAL 1 DAY AND CURRENT_DATE();
1
drop table t1;
+select least(cast('01-01-01' as date), '01-01-02');
+least(cast('01-01-01' as date), '01-01-02')
+2001-01-01
+select greatest(cast('01-01-01' as date), '01-01-02');
+greatest(cast('01-01-01' as date), '01-01-02')
+01-01-02
+select least(cast('01-01-01' as date), '01-01-02') + 0;
+least(cast('01-01-01' as date), '01-01-02') + 0
+20010101
+select greatest(cast('01-01-01' as date), '01-01-02') + 0;
+greatest(cast('01-01-01' as date), '01-01-02') + 0
+20010102
+select least(cast('01-01-01' as datetime), '01-01-02') + 0;
+least(cast('01-01-01' as datetime), '01-01-02') + 0
+20010101000000
+DROP PROCEDURE IF EXISTS test27759 ;
+CREATE PROCEDURE test27759()
+BEGIN
+declare v_a date default '2007-4-10';
+declare v_b date default '2007-4-11';
+declare v_c datetime default '2004-4-9 0:0:0';
+select v_a as a,v_b as b,
+least( v_a, v_b ) as a_then_b,
+least( v_b, v_a ) as b_then_a,
+least( v_c, v_a ) as c_then_a;
+END;|
+call test27759();
+a b a_then_b b_then_a c_then_a
+2007-04-10 2007-04-11 2007-04-10 2007-04-10 2004-04-09 00:00:00
+drop procedure test27759;
diff --git a/mysql-test/t/cast.test b/mysql-test/t/cast.test
index 502c5781f1f..004ef69182d 100644
--- a/mysql-test/t/cast.test
+++ b/mysql-test/t/cast.test
@@ -182,6 +182,12 @@ DROP TABLE t1;
select isnull(date(NULL)), isnull(cast(NULL as DATE));
+#
+# Bug#23656: Wrong result of CAST from DATE to int
+#
+SELECT CAST(cast('01-01-01' as date) AS UNSIGNED);
+SELECT CAST(cast('01-01-01' as date) AS SIGNED);
+
--echo End of 4.1 tests
diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test
index a0fc7059179..68b97854c3b 100644
--- a/mysql-test/t/join.test
+++ b/mysql-test/t/join.test
@@ -333,6 +333,30 @@ select t1.i,t2.i,t3.i from t2 natural right join t3,t1 order by t1.i,t2.i,t3.i;
select t1.i,t2.i,t3.i from t2 right join t3 on (t2.i=t3.i),t1 order by t1.i,t2.i,t3.i;
drop table t1,t2,t3;
+#
+# Bug #27531: Query performance degredation in 4.1.22 and greater
+#
+CREATE TABLE t1 (a int, b int default 0, c int default 1);
+
+INSERT INTO t1 (a) VALUES (1),(2),(3),(4),(5),(6),(7),(8);
+INSERT INTO t1 (a) SELECT a + 8 FROM t1;
+INSERT INTO t1 (a) SELECT a + 16 FROM t1;
+
+CREATE TABLE t2 (a int, d int, e int default 0);
+
+INSERT INTO t2 (a, d) VALUES (1,1),(2,2),(3,3),(4,4);
+INSERT INTO t2 (a, d) SELECT a+4, a+4 FROM t2;
+INSERT INTO t2 (a, d) SELECT a+8, a+8 FROM t2;
+
+# should use join cache
+EXPLAIN
+SELECT STRAIGHT_JOIN t2.e FROM t1,t2 WHERE t2.d=1 AND t1.b=t2.e
+ ORDER BY t1.b, t1.c;
+SELECT STRAIGHT_JOIN t2.e FROM t1,t2 WHERE t2.d=1 AND t1.b=t2.e
+ ORDER BY t1.b, t1.c;
+
+DROP TABLE t1,t2;
+
# End of 4.1 tests
#
diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test
index a0620e144c2..51e79a20d65 100644
--- a/mysql-test/t/join_outer.test
+++ b/mysql-test/t/join_outer.test
@@ -825,3 +825,23 @@ SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='bla';
SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='Bla';
DROP TABLE t1,t2;
+
+#
+# Bug 28188: 'not exists' optimization for outer joins
+#
+
+CREATE TABLE t1 (id int PRIMARY KEY, a varchar(8));
+CREATE TABLE t2 (id int NOT NULL, b int NOT NULL, INDEX idx(id));
+INSERT INTO t1 VALUES
+ (1,'aaaaaaa'), (5,'eeeeeee'), (4,'ddddddd'), (2,'bbbbbbb'), (3,'ccccccc');
+INSERT INTO t2 VALUES
+ (3,10), (2,20), (5,30), (3,20), (5,10), (3,40), (3,30), (2,10), (2,40);
+
+EXPLAIN
+SELECT t1.id, a FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.b IS NULL;
+
+flush status;
+SELECT t1.id, a FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.b IS NULL;
+show status like 'Handler_read%';
+
+DROP TABLE t1,t2;
diff --git a/mysql-test/t/outfile.test b/mysql-test/t/outfile.test
index 7c90fd32909..f285407efd4 100644
--- a/mysql-test/t/outfile.test
+++ b/mysql-test/t/outfile.test
@@ -96,3 +96,38 @@ create table t1(a int);
eval select * into outfile "$MYSQL_TEST_DIR/outfile-test1" from t1;
drop table t1;
+#
+# Bug#28181 Access denied to 'information_schema when
+# select into out file (regression)
+#
+create database mysqltest;
+create user user_1@localhost;
+grant all on mysqltest.* to user_1@localhost;
+connect (con28181_1,localhost,user_1,,mysqltest);
+
+--error 1044
+eval select schema_name
+into outfile "../tmp/outfile-test.4"
+fields terminated by ',' optionally enclosed by '"'
+ lines terminated by '\n'
+from information_schema.schemata
+where schema_name like 'mysqltest';
+
+connection default;
+grant file on *.* to user_1@localhost;
+
+connect (con28181_2,localhost,user_1,,mysqltest);
+eval select schema_name
+into outfile "../tmp/outfile-test.4"
+fields terminated by ',' optionally enclosed by '"'
+ lines terminated by '\n'
+from information_schema.schemata
+where schema_name like 'mysqltest';
+
+connection default;
+--exec rm $MYSQLTEST_VARDIR/tmp/outfile-test.4
+use test;
+revoke all privileges on *.* from user_1@localhost;
+drop user user_1@localhost;
+drop database mysqltest;
+
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 5cd2cd5fa7d..64de2ada93c 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -2874,4 +2874,12 @@ FROM t1;
DROP TABLE t1,t2;
+#
+# Bug #27807: Server crash when executing subquery with EXPLAIN
+#
+CREATE TABLE t1 (a int, b int, KEY (a));
+INSERT INTO t1 VALUES (1,1),(2,1);
+EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
+DROP TABLE t1;
+
--echo End of 5.0 tests.
diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test
index fc7b20d77a4..c82dee168d2 100644
--- a/mysql-test/t/type_datetime.test
+++ b/mysql-test/t/type_datetime.test
@@ -178,3 +178,29 @@ select f2, f3 from t1 where '01-03-10' between f2 and f3;
select f2 from t1 where DATE(f2) between "2001-4-15" AND "01-4-15";
SELECT 1 from dual where NOW() BETWEEN CURRENT_DATE() - INTERVAL 1 DAY AND CURRENT_DATE();
drop table t1;
+
+#
+# Bug#27759: Wrong DATE/DATETIME comparison in LEAST()/GREATEST() functions.
+#
+select least(cast('01-01-01' as date), '01-01-02');
+select greatest(cast('01-01-01' as date), '01-01-02');
+select least(cast('01-01-01' as date), '01-01-02') + 0;
+select greatest(cast('01-01-01' as date), '01-01-02') + 0;
+select least(cast('01-01-01' as datetime), '01-01-02') + 0;
+--disable_warnings
+DROP PROCEDURE IF EXISTS test27759 ;
+--enable_warnings
+DELIMITER |;
+CREATE PROCEDURE test27759()
+BEGIN
+declare v_a date default '2007-4-10';
+declare v_b date default '2007-4-11';
+declare v_c datetime default '2004-4-9 0:0:0';
+select v_a as a,v_b as b,
+ least( v_a, v_b ) as a_then_b,
+ least( v_b, v_a ) as b_then_a,
+ least( v_c, v_a ) as c_then_a;
+END;|
+DELIMITER ;|
+call test27759();
+drop procedure test27759;
View Lorry Controller Status