summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/include/mix1.inc25
-rw-r--r--mysql-test/r/func_gconcat.result26
-rw-r--r--mysql-test/r/func_group.result47
-rw-r--r--mysql-test/r/innodb_mysql.result21
-rw-r--r--mysql-test/r/ps.result208
-rw-r--r--mysql-test/r/subselect.result30
-rw-r--r--mysql-test/r/type_datetime.result30
-rw-r--r--mysql-test/t/func_gconcat.test14
-rw-r--r--mysql-test/t/func_group.test34
-rw-r--r--mysql-test/t/ps.test108
-rw-r--r--mysql-test/t/subselect.test24
-rw-r--r--mysql-test/t/type_datetime.test22
-rw-r--r--sql/item.cc3
-rw-r--r--sql/item_cmpfunc.cc20
-rw-r--r--sql/item_sum.cc9
-rw-r--r--sql/opt_sum.cc86
-rw-r--r--sql/sql_select.cc6
-rw-r--r--sql/sql_union.cc10
18 files changed, 701 insertions, 22 deletions
diff --git a/mysql-test/include/mix1.inc b/mysql-test/include/mix1.inc
index 4d7d86ccd10..7b90653e2c7 100644
--- a/mysql-test/include/mix1.inc
+++ b/mysql-test/include/mix1.inc
@@ -197,6 +197,31 @@ INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1;
SELECT * FROM t1;
DROP TABLE t1;
+#
+# Bug #28272: EXPLAIN for SELECT from an empty InnoDB table
+#
+
+CREATE TABLE t1 (
+ a1 decimal(10,0) DEFAULT NULL,
+ a2 blob,
+ a3 time DEFAULT NULL,
+ a4 blob,
+ a5 char(175) DEFAULT NULL,
+ a6 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
+ a7 tinyblob,
+ INDEX idx (a6,a7(239),a5)
+) ENGINE=InnoDB;
+
+EXPLAIN SELECT a4 FROM t1 WHERE
+a6=NULL AND
+a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS';
+
+EXPLAIN SELECT t1.a4 FROM t1, t1 t WHERE
+t.a6=t.a6 AND t1.a6=NULL AND
+t1.a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS';
+
+DROP TABLE t1;
+
--echo End of 4.1 tests
diff --git a/mysql-test/r/func_gconcat.result b/mysql-test/r/func_gconcat.result
index 4c7baac051a..bb09217d9d7 100644
--- a/mysql-test/r/func_gconcat.result
+++ b/mysql-test/r/func_gconcat.result
@@ -743,4 +743,30 @@ SELECT GROUP_CONCAT(DISTINCT UCASE(b)) FROM t1;
GROUP_CONCAT(DISTINCT UCASE(b))
ONE.1,TWO.2,ONE.3
DROP TABLE t1;
+CREATE TABLE t1( a VARCHAR( 10 ), b INT );
+INSERT INTO t1 VALUES ( repeat( 'a', 10 ), 1),
+( repeat( 'b', 10 ), 2);
+SET group_concat_max_len = 20;
+SELECT GROUP_CONCAT( a ) FROM t1;
+GROUP_CONCAT( a )
+aaaaaaaaaa,bbbbbbbbb
+Warnings:
+Warning 1260 1 line(s) were cut by GROUP_CONCAT()
+SELECT GROUP_CONCAT( DISTINCT a ) FROM t1;
+GROUP_CONCAT( DISTINCT a )
+aaaaaaaaaa,bbbbbbbbb
+Warnings:
+Warning 1260 1 line(s) were cut by GROUP_CONCAT()
+SELECT GROUP_CONCAT( a ORDER BY b ) FROM t1;
+GROUP_CONCAT( a ORDER BY b )
+aaaaaaaaaa,bbbbbbbbb
+Warnings:
+Warning 1260 1 line(s) were cut by GROUP_CONCAT()
+SELECT GROUP_CONCAT( DISTINCT a ORDER BY b ) FROM t1;
+GROUP_CONCAT( DISTINCT a ORDER BY b )
+aaaaaaaaaa,bbbbbbbbb
+Warnings:
+Warning 1260 1 line(s) were cut by GROUP_CONCAT()
+SET group_concat_max_len = DEFAULT;
+DROP TABLE t1;
End of 5.0 tests
diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index 0e6cae2f103..1f640dec0a4 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -1321,4 +1321,51 @@ SELECT a,AVG(DISTINCT b) AS average FROM t1 GROUP BY a HAVING average > 50;
a average
1 32768.5000
DROP TABLE t1;
+CREATE TABLE t1 ( a INT, b INT, KEY(a) );
+INSERT INTO t1 VALUES (NULL, 1), (NULL, 2);
+EXPLAIN SELECT MIN(a), MIN(b) FROM t1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2
+SELECT MIN(a), MIN(b) FROM t1;
+MIN(a) MIN(b)
+NULL 1
+CREATE TABLE t2( a INT, b INT, c INT, KEY(a, b) );
+INSERT INTO t2 ( a, b, c ) VALUES ( 1, NULL, 2 ), ( 1, 3, 4 ), ( 1, 4, 4 );
+EXPLAIN SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ref a a 5 const 2 Using where
+SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1;
+MIN(b) MIN(c)
+3 2
+CREATE TABLE t3 (a INT, b INT, c int, KEY(a, b));
+INSERT INTO t3 VALUES (1, NULL, 1), (2, NULL, 2), (2, NULL, 2), (3, NULL, 3);
+EXPLAIN SELECT MIN(a), MIN(b) FROM t3 where a = 2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+SELECT MIN(a), MIN(b) FROM t3 where a = 2;
+MIN(a) MIN(b)
+2 NULL
+CREATE TABLE t4 (a INT, b INT, c int, KEY(a, b));
+INSERT INTO t4 VALUES (1, 1, 1), (2, NULL, 2), (2, NULL, 2), (3, 1, 3);
+EXPLAIN SELECT MIN(a), MIN(b) FROM t4 where a = 2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+SELECT MIN(a), MIN(b) FROM t4 where a = 2;
+MIN(a) MIN(b)
+2 NULL
+SELECT MIN(b), min(c) FROM t4 where a = 2;
+MIN(b) min(c)
+NULL 2
+CREATE TABLE t5( a INT, b INT, KEY( a, b) );
+INSERT INTO t5 VALUES( 1, 1 ), ( 1, 2 );
+EXPLAIN SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1;
+MIN(a) MIN(b)
+1 1
+SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1 and b > 1;
+MIN(a) MIN(b)
+1 2
+DROP TABLE t1, t2, t3, t4, t5;
End of 5.0 tests
diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result
index c4bae224846..631d9fc2e68 100644
--- a/mysql-test/r/innodb_mysql.result
+++ b/mysql-test/r/innodb_mysql.result
@@ -145,6 +145,27 @@ SELECT * FROM t1;
c1 cnt
1a 2
DROP TABLE t1;
+CREATE TABLE t1 (
+a1 decimal(10,0) DEFAULT NULL,
+a2 blob,
+a3 time DEFAULT NULL,
+a4 blob,
+a5 char(175) DEFAULT NULL,
+a6 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
+a7 tinyblob,
+INDEX idx (a6,a7(239),a5)
+) ENGINE=InnoDB;
+EXPLAIN SELECT a4 FROM t1 WHERE
+a6=NULL AND
+a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+EXPLAIN SELECT t1.a4 FROM t1, t1 t WHERE
+t.a6=t.a6 AND t1.a6=NULL AND
+t1.a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+DROP TABLE t1;
End of 4.1 tests
create table t1m (a int) engine = MEMORY;
create table t1i (a int);
diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result
index 15e1c8730f0..ee92d549912 100644
--- a/mysql-test/r/ps.result
+++ b/mysql-test/r/ps.result
@@ -1190,6 +1190,214 @@ EXECUTE b12651;
DROP VIEW b12651_V1;
DROP TABLE b12651_T1, b12651_T2;
DEALLOCATE PREPARE b12651;
+DROP TABLE IF EXISTS t1, t2;
+CREATE TABLE t1 (i INT);
+PREPARE st_19182
+FROM "CREATE TABLE t2 (i INT, j INT, KEY (i), KEY(j)) SELECT i FROM t1";
+EXECUTE st_19182;
+DESC t2;
+Field Type Null Key Default Extra
+j int(11) YES MUL NULL
+i int(11) YES MUL NULL
+DROP TABLE t2;
+EXECUTE st_19182;
+DESC t2;
+Field Type Null Key Default Extra
+j int(11) YES MUL NULL
+i int(11) YES MUL NULL
+DEALLOCATE PREPARE st_19182;
+DROP TABLE t2, t1;
+drop database if exists mysqltest;
+drop table if exists t1, t2;
+create database mysqltest character set utf8;
+prepare stmt1 from "create table mysqltest.t1 (c char(10))";
+prepare stmt2 from "create table mysqltest.t2 select 'test'";
+execute stmt1;
+execute stmt2;
+show create table mysqltest.t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c` char(10) default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=utf8
+show create table mysqltest.t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `test` varchar(4) character set latin1 NOT NULL default ''
+) ENGINE=MyISAM DEFAULT CHARSET=utf8
+drop table mysqltest.t1;
+drop table mysqltest.t2;
+alter database mysqltest character set latin1;
+execute stmt1;
+execute stmt2;
+show create table mysqltest.t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c` char(10) character set utf8 default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+show create table mysqltest.t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `test` varchar(4) NOT NULL default ''
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop database mysqltest;
+deallocate prepare stmt1;
+deallocate prepare stmt2;
+execute stmt;
+show create table t1;
+drop table t1;
+execute stmt;
+show create table t1;
+drop table t1;
+deallocate prepare stmt;
+CREATE TABLE t1(a int);
+INSERT INTO t1 VALUES (2), (3), (1);
+PREPARE st1 FROM
+'(SELECT a FROM t1) UNION (SELECT a+10 FROM t1) ORDER BY RAND()*0+a';
+EXECUTE st1;
+a
+1
+2
+3
+11
+12
+13
+EXECUTE st1;
+a
+1
+2
+3
+11
+12
+13
+DEALLOCATE PREPARE st1;
+DROP TABLE t1;
+End of 4.1 tests.
+create table t1 (a varchar(20));
+insert into t1 values ('foo');
+prepare stmt FROM 'SELECT char_length (a) FROM t1';
+ERROR 42000: FUNCTION test.char_length does not exist
+drop table t1;
+create table t1 (a char(3) not null, b char(3) not null,
+c char(3) not null, primary key (a, b, c));
+create table t2 like t1;
+prepare stmt from
+"select t1.a from (t1 left outer join t2 on t2.a=1 and t1.b=t2.b)
+ where t1.a=1";
+execute stmt;
+a
+execute stmt;
+a
+execute stmt;
+a
+prepare stmt from
+"select t1.a, t1.b, t1.c, t2.a, t2.b, t2.c from
+(t1 left outer join t2 on t2.a=? and t1.b=t2.b)
+left outer join t2 t3 on t3.a=? where t1.a=?";
+set @a:=1, @b:=1, @c:=1;
+execute stmt using @a, @b, @c;
+a b c a b c
+execute stmt using @a, @b, @c;
+a b c a b c
+execute stmt using @a, @b, @c;
+a b c a b c
+deallocate prepare stmt;
+drop table t1,t2;
+SET @aux= "SELECT COUNT(*)
+ FROM INFORMATION_SCHEMA.COLUMNS A,
+ INFORMATION_SCHEMA.COLUMNS B
+ WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA
+ AND A.TABLE_NAME = B.TABLE_NAME
+ AND A.COLUMN_NAME = B.COLUMN_NAME AND
+ A.TABLE_NAME = 'user'";
+prepare my_stmt from @aux;
+execute my_stmt;
+COUNT(*)
+37
+execute my_stmt;
+COUNT(*)
+37
+execute my_stmt;
+COUNT(*)
+37
+deallocate prepare my_stmt;
+drop procedure if exists p1|
+drop table if exists t1|
+create table t1 (id int)|
+insert into t1 values(1)|
+create procedure p1(a int, b int)
+begin
+declare c int;
+select max(id)+1 into c from t1;
+insert into t1 select a+b;
+insert into t1 select a-b;
+insert into t1 select a-c;
+end|
+set @a= 3, @b= 4|
+prepare stmt from "call p1(?, ?)"|
+execute stmt using @a, @b|
+execute stmt using @a, @b|
+select * from t1|
+id
+1
+7
+-1
+1
+7
+-1
+-5
+deallocate prepare stmt|
+drop procedure p1|
+drop table t1|
+create table t1 (a int);
+insert into t1 (a) values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+prepare stmt from "select * from t1 limit ?, ?";
+set @offset=0, @limit=1;
+execute stmt using @offset, @limit;
+a
+1
+select * from t1 limit 0, 1;
+a
+1
+set @offset=3, @limit=2;
+execute stmt using @offset, @limit;
+a
+4
+5
+select * from t1 limit 3, 2;
+a
+4
+5
+prepare stmt from "select * from t1 limit ?";
+execute stmt using @limit;
+a
+1
+2
+prepare stmt from "select * from t1 where a in (select a from t1 limit ?)";
+ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
+prepare stmt from "select * from t1 union all select * from t1 limit ?, ?";
+set @offset=9;
+set @limit=2;
+execute stmt using @offset, @limit;
+a
+10
+1
+prepare stmt from "(select * from t1 limit ?, ?) union all
+ (select * from t1 limit ?, ?) order by a limit ?";
+execute stmt using @offset, @limit, @offset, @limit, @limit;
+a
+10
+10
+drop table t1;
+deallocate prepare stmt;
+CREATE TABLE b12651_T1(a int) ENGINE=MYISAM;
+CREATE TABLE b12651_T2(b int) ENGINE=MYISAM;
+CREATE VIEW b12651_V1 as SELECT b FROM b12651_T2;
+PREPARE b12651 FROM 'SELECT 1 FROM b12651_T1 WHERE a IN (SELECT b FROM b12651_V1)';
+EXECUTE b12651;
+1
+DROP VIEW b12651_V1;
+DROP TABLE b12651_T1, b12651_T2;
+DEALLOCATE PREPARE b12651;
create table t1 (id int);
prepare ins_call from "insert into t1 (id) values (1)";
execute ins_call;
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index e90f43b9870..3dc1795a65a 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -4041,6 +4041,36 @@ 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;
+CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
+INSERT INTO t1 VALUES
+(3,'FL'), (2,'GA'), (4,'FL'), (1,'GA'), (5,'NY'), (7,'FL'), (6,'NY');
+CREATE TABLE t2 (id int NOT NULL, INDEX idx(id));
+INSERT INTO t2 VALUES (7), (5), (1), (3);
+SELECT id, st FROM t1
+WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
+id st
+3 FL
+1 GA
+7 FL
+SELECT id, st FROM t1
+WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
+GROUP BY id;
+id st
+1 GA
+3 FL
+7 FL
+SELECT id, st FROM t1
+WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
+id st
+2 GA
+4 FL
+SELECT id, st FROM t1
+WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
+GROUP BY id;
+id st
+2 GA
+4 FL
+DROP TABLE t1,t2;
End of 5.0 tests.
CREATE TABLE t1 (a int, b int);
INSERT INTO t1 VALUES (2,22),(1,11),(2,22);
diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result
index e1be32aa7eb..9a6dd44faa1 100644
--- a/mysql-test/r/type_datetime.result
+++ b/mysql-test/r/type_datetime.result
@@ -346,6 +346,36 @@ 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;
+create table t1 (f1 date);
+insert into t1 values (curdate());
+select left(f1,10) = curdate() from t1;
+left(f1,10) = curdate()
+1
+drop table t1;
+create table t1(f1 date);
+insert into t1 values('01-01-01'),('02-02-02'),('01-01-01'),('02-02-02');
+set @bug28261='';
+select if(@bug28261 = f1, '', @bug28261:= f1) from t1;
+if(@bug28261 = f1, '', @bug28261:= f1)
+2001-01-01
+2002-02-02
+2001-01-01
+2002-02-02
+Warnings:
+Warning 1292 Incorrect date value: '' for column 'f1' at row 1
+select if(@bug28261 = f1, '', @bug28261:= f1) from t1;
+if(@bug28261 = f1, '', @bug28261:= f1)
+2001-01-01
+2002-02-02
+2001-01-01
+2002-02-02
+select if(@bug28261 = f1, '', @bug28261:= f1) from t1;
+if(@bug28261 = f1, '', @bug28261:= f1)
+2001-01-01
+2002-02-02
+2001-01-01
+2002-02-02
+drop table t1;
set @org_mode=@@sql_mode;
create table t1 (da date default '1962-03-03 23:33:34', dt datetime default '1962-03-03');
Warnings:
diff --git a/mysql-test/t/func_gconcat.test b/mysql-test/t/func_gconcat.test
index 431d582be50..4e965d7e8c9 100644
--- a/mysql-test/t/func_gconcat.test
+++ b/mysql-test/t/func_gconcat.test
@@ -507,4 +507,18 @@ SELECT GROUP_CONCAT(DISTINCT UCASE(a)) FROM t1;
SELECT GROUP_CONCAT(DISTINCT UCASE(b)) FROM t1;
DROP TABLE t1;
+#
+# Bug #28273: GROUP_CONCAT and ORDER BY: No warning when result gets truncated.
+#
+CREATE TABLE t1( a VARCHAR( 10 ), b INT );
+INSERT INTO t1 VALUES ( repeat( 'a', 10 ), 1),
+ ( repeat( 'b', 10 ), 2);
+SET group_concat_max_len = 20;
+SELECT GROUP_CONCAT( a ) FROM t1;
+SELECT GROUP_CONCAT( DISTINCT a ) FROM t1;
+SELECT GROUP_CONCAT( a ORDER BY b ) FROM t1;
+SELECT GROUP_CONCAT( DISTINCT a ORDER BY b ) FROM t1;
+SET group_concat_max_len = DEFAULT;
+DROP TABLE t1;
+
--echo End of 5.0 tests
diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test
index 778bfc9528f..d1b4919c83e 100644
--- a/mysql-test/t/func_group.test
+++ b/mysql-test/t/func_group.test
@@ -834,4 +834,38 @@ SELECT a,AVG(DISTINCT b) AS average FROM t1 GROUP BY a HAVING average > 50;
DROP TABLE t1;
+#
+# Bug #27573: MIN() on an indexed column which is always NULL sets _other_
+# results to NULL
+#
+CREATE TABLE t1 ( a INT, b INT, KEY(a) );
+INSERT INTO t1 VALUES (NULL, 1), (NULL, 2);
+EXPLAIN SELECT MIN(a), MIN(b) FROM t1;
+SELECT MIN(a), MIN(b) FROM t1;
+
+CREATE TABLE t2( a INT, b INT, c INT, KEY(a, b) );
+INSERT INTO t2 ( a, b, c ) VALUES ( 1, NULL, 2 ), ( 1, 3, 4 ), ( 1, 4, 4 );
+EXPLAIN SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1;
+SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1;
+
+CREATE TABLE t3 (a INT, b INT, c int, KEY(a, b));
+INSERT INTO t3 VALUES (1, NULL, 1), (2, NULL, 2), (2, NULL, 2), (3, NULL, 3);
+EXPLAIN SELECT MIN(a), MIN(b) FROM t3 where a = 2;
+SELECT MIN(a), MIN(b) FROM t3 where a = 2;
+
+CREATE TABLE t4 (a INT, b INT, c int, KEY(a, b));
+INSERT INTO t4 VALUES (1, 1, 1), (2, NULL, 2), (2, NULL, 2), (3, 1, 3);
+EXPLAIN SELECT MIN(a), MIN(b) FROM t4 where a = 2;
+SELECT MIN(a), MIN(b) FROM t4 where a = 2;
+SELECT MIN(b), min(c) FROM t4 where a = 2;
+
+CREATE TABLE t5( a INT, b INT, KEY( a, b) );
+INSERT INTO t5 VALUES( 1, 1 ), ( 1, 2 );
+EXPLAIN SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1;
+SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1;
+SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1 and b > 1;
+
+DROP TABLE t1, t2, t3, t4, t5;
+
+###
--echo End of 5.0 tests
diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test
index 44f9bf350b2..2336ccf0b58 100644
--- a/mysql-test/t/ps.test
+++ b/mysql-test/t/ps.test
@@ -1120,6 +1120,114 @@ DROP TABLE t1;
--echo End of 4.1 tests.
+#
+# Bug#19182: CREATE TABLE bar (m INT) SELECT n FROM foo; doesn't work
+# from stored procedure.
+#
+# The cause of a bug was that cached LEX::create_list was modified,
+# and then together with LEX::key_list was reset.
+#
+--disable_warnings
+DROP TABLE IF EXISTS t1, t2;
+--enable_warnings
+
+CREATE TABLE t1 (i INT);
+
+PREPARE st_19182
+FROM "CREATE TABLE t2 (i INT, j INT, KEY (i), KEY(j)) SELECT i FROM t1";
+
+EXECUTE st_19182;
+DESC t2;
+
+DROP TABLE t2;
+
+# Check that on second execution we don't loose 'j' column and the keys
+# on 'i' and 'j' columns.
+EXECUTE st_19182;
+DESC t2;
+
+DEALLOCATE PREPARE st_19182;
+DROP TABLE t2, t1;
+
+#
+# Bug #22060 "ALTER TABLE x AUTO_INCREMENT=y in SP crashes server"
+#
+# Code which implemented CREATE/ALTER TABLE and CREATE DATABASE
+# statement modified HA_CREATE_INFO structure in LEX, making these
+# statements PS/SP-unsafe (their re-execution might have resulted
+# in incorrect results).
+#
+--disable_warnings
+drop database if exists mysqltest;
+drop table if exists t1, t2;
+--enable_warnings
+# CREATE TABLE and CREATE TABLE ... SELECT
+create database mysqltest character set utf8;
+prepare stmt1 from "create table mysqltest.t1 (c char(10))";
+prepare stmt2 from "create table mysqltest.t2 select 'test'";
+execute stmt1;
+execute stmt2;
+show create table mysqltest.t1;
+show create table mysqltest.t2;
+drop table mysqltest.t1;
+drop table mysqltest.t2;
+alter database mysqltest character set latin1;
+execute stmt1;
+execute stmt2;
+show create table mysqltest.t1;
+show create table mysqltest.t2;
+drop database mysqltest;
+deallocate prepare stmt1;
+deallocate prepare stmt2;
+#
+# CREATE TABLE with DATA DIRECTORY option
+#
+# Protect ourselves from data left in tmp/ by a previos possibly failed
+# test
+--system rm -f $MYSQLTEST_VARDIR/tmp/t1.*
+--disable_warnings
+--disable_query_log
+eval prepare stmt from "create table t1 (c char(10)) data directory='$MYSQLTEST_VARDIR/tmp'";
+--enable_query_log
+execute stmt;
+#
+# DATA DIRECTORY option does not always work: if the operating
+# system does not support symlinks, have_symlinks option is automatically
+# disabled.
+# In this case DATA DIRECTORY is silently ignored when
+# creating a table, and is not output by SHOW CREATE TABLE.
+#
+--disable_result_log
+show create table t1;
+--enable_result_log
+drop table t1;
+execute stmt;
+--disable_result_log
+show create table t1;
+--enable_result_log
+--enable_warnings
+drop table t1;
+deallocate prepare stmt;
+#
+
+#
+# Bug #27937: crash on the second execution for prepared statement
+# from UNION with ORDER BY an expression containing RAND()
+#
+
+CREATE TABLE t1(a int);
+INSERT INTO t1 VALUES (2), (3), (1);
+
+PREPARE st1 FROM
+ '(SELECT a FROM t1) UNION (SELECT a+10 FROM t1) ORDER BY RAND()*0+a';
+
+EXECUTE st1;
+EXECUTE st1;
+
+DEALLOCATE PREPARE st1;
+DROP TABLE t1;
+
+--echo End of 4.1 tests.
############################# 5.0 tests start ################################
#
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index aaeb998d0c7..12e2e4f1f25 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -2882,6 +2882,30 @@ 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;
+#
+# Bug #28377: grouping query with a correlated subquery in WHERE condition
+#
+
+CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
+INSERT INTO t1 VALUES
+ (3,'FL'), (2,'GA'), (4,'FL'), (1,'GA'), (5,'NY'), (7,'FL'), (6,'NY');
+CREATE TABLE t2 (id int NOT NULL, INDEX idx(id));
+INSERT INTO t2 VALUES (7), (5), (1), (3);
+
+SELECT id, st FROM t1
+ WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
+SELECT id, st FROM t1
+ WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
+ GROUP BY id;
+
+SELECT id, st FROM t1
+ WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
+SELECT id, st FROM t1
+ WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
+ GROUP BY id;
+
+DROP TABLE t1,t2;
+
--echo End of 5.0 tests.
#
diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test
index 7689f16baa4..23804633666 100644
--- a/mysql-test/t/type_datetime.test
+++ b/mysql-test/t/type_datetime.test
@@ -227,6 +227,28 @@ call test27759();
drop procedure test27759;
#
+# Bug#28208: Wrong result of a non-const STRING function with a const
+# DATETIME function.
+#
+create table t1 (f1 date);
+insert into t1 values (curdate());
+select left(f1,10) = curdate() from t1;
+drop table t1;
+
+#
+# Bug#28261: Wrong DATETIME comparison result when the GET_USER_VAR function
+# is involved.
+#
+create table t1(f1 date);
+insert into t1 values('01-01-01'),('02-02-02'),('01-01-01'),('02-02-02');
+set @bug28261='';
+select if(@bug28261 = f1, '', @bug28261:= f1) from t1;
+select if(@bug28261 = f1, '', @bug28261:= f1) from t1;
+select if(@bug28261 = f1, '', @bug28261:= f1) from t1;
+drop table t1;
+
+
+#
# Test of storing datetime into date fields
#
diff --git a/sql/item.cc b/sql/item.cc
index e39a1199bae..dc9c331a300 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -3558,7 +3558,8 @@ Item_field::fix_outer_field(THD *thd, Field **from_field, Item **reference)
prev_subselect_item->const_item_cache= 0;
set_field(*from_field);
if (!last_checked_context->select_lex->having_fix_field &&
- select->group_list.elements)
+ select->group_list.elements &&
+ (place == SELECT_LIST || place == IN_HAVING))
{
Item_outer_ref *rf;
/*
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 2e6081ba5f8..4a18de8ce4f 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -690,7 +690,13 @@ Arg_comparator::can_compare_as_dates(Item *a, Item *b, ulonglong *const_value)
if (cmp_type != CMP_DATE_DFLT)
{
- if (cmp_type != CMP_DATE_WITH_DATE && str_arg->const_item())
+ /*
+ Do not cache GET_USER_VAR() function as its const_item() may return TRUE
+ for the current thread but it still may change during the execution.
+ */
+ if (cmp_type != CMP_DATE_WITH_DATE && str_arg->const_item() &&
+ (str_arg->type() != Item::FUNC_ITEM ||
+ ((Item_func*)str_arg)->functype() != Item_func::GUSERVAR_FUNC))
{
THD *thd= current_thd;
ulonglong value;
@@ -718,7 +724,7 @@ int Arg_comparator::set_cmp_func(Item_bool_func2 *owner_arg,
Item_result type)
{
enum enum_date_cmp_type cmp_type;
- ulonglong const_value;
+ ulonglong const_value= (ulonglong)-1;
a= a1;
b= a2;
@@ -731,8 +737,7 @@ int Arg_comparator::set_cmp_func(Item_bool_func2 *owner_arg,
a_cache= 0;
b_cache= 0;
- if (cmp_type != CMP_DATE_WITH_DATE &&
- ((*b)->const_item() || (*a)->const_item()))
+ if (const_value != (ulonglong)-1)
{
Item_cache_int *cache= new Item_cache_int();
/* Mark the cache as non-const to prevent re-caching. */
@@ -838,7 +843,12 @@ get_datetime_value(THD *thd, Item ***item_arg, Item **cache_arg,
MYSQL_TYPE_DATE ? MYSQL_TIMESTAMP_DATE : MYSQL_TIMESTAMP_DATETIME;
value= get_date_from_str(thd, str, t_type, warn_item->name, &error);
}
- if (item->const_item() && cache_arg)
+ /*
+ Do not cache GET_USER_VAR() function as its const_item() may return TRUE
+ for the current thread but it still may change during the execution.
+ */
+ if (item->const_item() && cache_arg && (item->type() != Item::FUNC_ITEM ||
+ ((Item_func*)item)->functype() != Item_func::GUSERVAR_FUNC))
{
Item_cache_int *cache= new Item_cache_int();
/* Mark the cache as non-const to prevent re-caching. */
diff --git a/sql/item_sum.cc b/sql/item_sum.cc
index 6215aacde64..f962d067e17 100644
--- a/sql/item_sum.cc
+++ b/sql/item_sum.cc
@@ -3360,6 +3360,10 @@ String* Item_func_group_concat::val_str(String* str)
DBUG_ASSERT(fixed == 1);
if (null_value)
return 0;
+ if (!result.length() && tree)
+ /* Tree is used for sorting as in ORDER BY */
+ tree_walk(tree, (tree_walk_action)&dump_leaf_key, (void*)this,
+ left_root_right);
if (count_cut_values && !warning)
{
/*
@@ -3371,11 +3375,6 @@ String* Item_func_group_concat::val_str(String* str)
ER_CUT_VALUE_GROUP_CONCAT,
ER(ER_CUT_VALUE_GROUP_CONCAT));
}
- if (result.length())
- return &result;
- if (tree)
- tree_walk(tree, (tree_walk_action)&dump_leaf_key, (void*)this,
- left_root_right);
return &result;
}
diff --git a/sql/opt_sum.cc b/sql/opt_sum.cc
index f9a06f3fb6e..c020b9ab53e 100644
--- a/sql/opt_sum.cc
+++ b/sql/opt_sum.cc
@@ -249,12 +249,68 @@ int opt_sum_query(TABLE_LIST *tables, List<Item> &all_fields,COND *conds)
if (!ref.key_length)
error= table->file->index_first(table->record[0]);
- else
- error= table->file->index_read(table->record[0],key_buff,
- make_prev_keypart_map(ref.key_parts),
- range_fl & NEAR_MIN ?
- HA_READ_AFTER_KEY :
- HA_READ_KEY_OR_NEXT);
+ else
+ {
+ /*
+ Use index to replace MIN/MAX functions with their values
+ according to the following rules:
+
+ 1) Insert the minimum non-null values where the WHERE clause still
+ matches, or
+ 2) a NULL value if there are only NULL values for key_part_k.
+ 3) Fail, producing a row of nulls
+
+ Implementation: Read the smallest value using the search key. If
+ the interval is open, read the next value after the search
+ key. If read fails, and we're looking for a MIN() value for a
+ nullable column, test if there is an exact match for the key.
+ */
+ if (!(range_fl & NEAR_MIN))
+ /*
+ Closed interval: Either The MIN argument is non-nullable, or
+ we have a >= predicate for the MIN argument.
+ */
+ error= table->file->index_read(table->record[0], ref.key_buff,
+ ref.key_length,
+ HA_READ_KEY_OR_NEXT);
+ else
+ {
+ /*
+ Open interval: There are two cases:
+ 1) We have only MIN() and the argument column is nullable, or
+ 2) there is a > predicate on it, nullability is irrelevant.
+ We need to scan the next bigger record first.
+ */
+ error= table->file->index_read(table->record[0], ref.key_buff,
+ ref.key_length, HA_READ_AFTER_KEY);
+ /*
+ If the found record is outside the group formed by the search
+ prefix, or there is no such record at all, check if all
+ records in that group have NULL in the MIN argument
+ column. If that is the case return that NULL.
+
+ Check if case 1 from above holds. If it does, we should read
+ the skipped tuple.
+ */
+ if (ref.key_buff[prefix_len] == 1 &&
+ /*
+ Last keypart (i.e. the argument to MIN) is set to NULL by
+ find_key_for_maxmin only if all other keyparts are bound
+ to constants in a conjunction of equalities. Hence, we
+ can detect this by checking only if the last keypart is
+ NULL.
+ */
+ (error == HA_ERR_KEY_NOT_FOUND ||
+ key_cmp_if_same(table, ref.key_buff, ref.key, prefix_len)))
+ {
+ DBUG_ASSERT(item_field->field->real_maybe_null());
+ error= table->file->index_read(table->record[0], ref.key_buff,
+ ref.key_length,
+ HA_READ_KEY_EXACT);
+ }
+ }
+ }
+ /* Verify that the read tuple indeed matches the search key */
if (!error && reckey_in_range(0, &ref, item_field->field,
conds, range_fl, prefix_len))
error= HA_ERR_KEY_NOT_FOUND;
@@ -784,16 +840,26 @@ static bool find_key_for_maxmin(bool max_fl, TABLE_REF *ref,
if (!max_fl && key_part_used == key_part_to_use && part->null_bit)
{
/*
- SELECT MIN(key_part2) FROM t1 WHERE key_part1=const
- If key_part2 may be NULL, then we want to find the first row
- that is not null
+ The query is on this form:
+
+ SELECT MIN(key_part_k)
+ FROM t1
+ WHERE key_part_1 = const and ... and key_part_k-1 = const
+
+ If key_part_k is nullable, we want to find the first matching row
+ where key_part_k is not null. The key buffer is now {const, ...,
+ NULL}. This will be passed to the handler along with a flag
+ indicating open interval. If a tuple is read that does not match
+ these search criteria, an attempt will be made to read an exact
+ match for the key buffer.
*/
+ /* Set the first byte of key_part_k to 1, that means NULL */
ref->key_buff[ref->key_length]= 1;
ref->key_length+= part->store_length;
ref->key_parts++;
DBUG_ASSERT(ref->key_parts == jdx+1);
*range_fl&= ~NO_MIN_RANGE;
- *range_fl|= NEAR_MIN; // > NULL
+ *range_fl|= NEAR_MIN; // Open interval
}
/*
The following test is false when the key in the key tree is
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 8de3e041bfc..ab0437dd7b5 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -988,6 +988,12 @@ JOIN::optimize()
}
}
+ if (conds &&!outer_join && const_table_map != found_const_table_map &&
+ (select_options & SELECT_DESCRIBE) &&
+ select_lex->master_unit() == &thd->lex->unit) // upper level SELECT
+ {
+ conds=new Item_int((longlong) 0,1); // Always false
+ }
if (make_join_select(this, select, conds))
{
zero_result_cause=
diff --git a/sql/sql_union.cc b/sql/sql_union.cc
index 2a1db422058..dbae7977d62 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -147,8 +147,16 @@ st_select_lex_unit::init_prepare_fake_select_lex(THD *thd_arg)
fake_select_lex->table_list.link_in_list((byte *)&result_table_list,
(byte **)
&result_table_list.next_local);
- fake_select_lex->context.table_list= fake_select_lex->context.first_name_resolution_table=
+ fake_select_lex->context.table_list=
+ fake_select_lex->context.first_name_resolution_table=
fake_select_lex->get_table_list();
+ if (!fake_select_lex->first_execution)
+ {
+ for (ORDER *order= (ORDER *) global_parameters->order_list.first;
+ order;
+ order= order->next)
+ order->item= &order->item_ptr;
+ }
for (ORDER *order= (ORDER *)global_parameters->order_list.first;
order;
order=order->next)
View Lorry Controller Status