summaryrefslogtreecommitdiff
path: root/mysql-test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test')
-rw-r--r--mysql-test/include/mix1.inc76
-rw-r--r--mysql-test/include/ps_conv.inc4
-rw-r--r--mysql-test/r/distinct.result2
-rw-r--r--mysql-test/r/group_by.result12
-rw-r--r--mysql-test/r/innodb_mysql.result73
-rw-r--r--mysql-test/r/join.result43
-rw-r--r--mysql-test/r/ps_2myisam.result4
-rw-r--r--mysql-test/r/ps_3innodb.result4
-rw-r--r--mysql-test/r/ps_4heap.result4
-rw-r--r--mysql-test/r/ps_5merge.result8
-rw-r--r--mysql-test/r/ps_7ndb.result4
-rw-r--r--mysql-test/r/query_cache.result12
-rw-r--r--mysql-test/r/subselect.result27
-rw-r--r--mysql-test/r/type_datetime.result74
-rw-r--r--mysql-test/r/windows.result7
-rw-r--r--mysql-test/t/group_by.test14
-rw-r--r--mysql-test/t/join.test56
-rw-r--r--mysql-test/t/subselect.test56
-rw-r--r--mysql-test/t/type_datetime.test40
-rw-r--r--mysql-test/t/windows.test8
20 files changed, 485 insertions, 43 deletions
diff --git a/mysql-test/include/mix1.inc b/mysql-test/include/mix1.inc
index f0d143c4570..aea1b6e0b58 100644
--- a/mysql-test/include/mix1.inc
+++ b/mysql-test/include/mix1.inc
@@ -168,6 +168,35 @@ connection default;
drop table t1;
disconnect con1;
+#
+# Bug #13191: INSERT...ON DUPLICATE KEY UPDATE of UTF-8 string fields
+# used in partial unique indices.
+#
+
+CREATE TABLE t1(c1 TEXT, UNIQUE (c1(1)), cnt INT DEFAULT 1)
+ ENGINE=INNODB CHARACTER SET UTF8;
+INSERT INTO t1 (c1) VALUES ('1a');
+SELECT * FROM t1;
+INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1;
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1(c1 VARCHAR(2), UNIQUE (c1(1)), cnt INT DEFAULT 1)
+ ENGINE=INNODB CHARACTER SET UTF8;
+INSERT INTO t1 (c1) VALUES ('1a');
+SELECT * FROM t1;
+INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1;
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1(c1 CHAR(2), UNIQUE (c1(1)), cnt INT DEFAULT 1)
+ ENGINE=INNODB CHARACTER SET UTF8;
+INSERT INTO t1 (c1) VALUES ('1a');
+SELECT * FROM t1;
+INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1;
+SELECT * FROM t1;
+DROP TABLE t1;
+
--echo End of 4.1 tests
@@ -415,7 +444,54 @@ DROP TABLE t1;
--source include/innodb_rollback_on_timeout.inc
+-- source include/have_innodb.inc
+
+#
+# Bug #27650: INSERT fails after multi-row INSERT of the form:
+# INSERT INTO t (id...) VALUES (NULL...) ON DUPLICATE KEY UPDATE id=VALUES(id)
+#
+
+create table t1(
+id int auto_increment,
+c char(1) not null,
+counter int not null default 1,
+primary key (id),
+unique key (c)
+) engine=innodb;
+
+insert into t1 (id, c) values
+(NULL, 'a'),
+(NULL, 'a')
+on duplicate key update id = values(id), counter = counter + 1;
+
+select * from t1;
+
+insert into t1 (id, c) values
+(NULL, 'b')
+on duplicate key update id = values(id), counter = counter + 1;
+
+select * from t1;
+
+truncate table t1;
+
+insert into t1 (id, c) values (NULL, 'a');
+
+select * from t1;
+
+insert into t1 (id, c) values (NULL, 'b'), (NULL, 'b')
+on duplicate key update id = values(id), c = values(c), counter = counter + 1;
+
+select * from t1;
+
+insert into t1 (id, c) values (NULL, 'a')
+on duplicate key update id = values(id), c = values(c), counter = counter + 1;
+
+select * from t1;
+
+drop table t1;
+
--echo End of 5.0 tests
+
#
# Test of behaviour with CREATE ... SELECT
#
diff --git a/mysql-test/include/ps_conv.inc b/mysql-test/include/ps_conv.inc
index 09290d760ce..195d1061664 100644
--- a/mysql-test/include/ps_conv.inc
+++ b/mysql-test/include/ps_conv.inc
@@ -1171,7 +1171,7 @@ execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
######## SELECT .. WHERE column(date/time/..)=value(DATETIME/LONGBLOB) ########
set @arg00= CAST('1991-01-01 01:01:01' as datetime) ;
select 'true' as found from t9
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
c14= CAST('1991-01-01 01:01:01' as datetime) and
c15= CAST('1991-01-01 01:01:01' as datetime) and
c16= CAST('1991-01-01 01:01:01' as datetime) and
@@ -1180,7 +1180,7 @@ select 'true' as found from t9
where c1= 20 and c13= CAST(@arg00 AS DATE) and c14= @arg00 and c15= @arg00 and c16= @arg00
and c17= @arg00 ;
prepare stmt1 from "select 'true' as found from t9
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
c14= CAST('1991-01-01 01:01:01' as datetime) and
c15= CAST('1991-01-01 01:01:01' as datetime) and
c16= CAST('1991-01-01 01:01:01' as datetime) and
diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result
index 20538694218..8bf8c95c6fb 100644
--- a/mysql-test/r/distinct.result
+++ b/mysql-test/r/distinct.result
@@ -633,7 +633,7 @@ EXPLAIN SELECT (SELECT DISTINCT ADDDATE(a,1) FROM t1
WHERE ADDDATE(a,1) = '2002-08-03');
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
-2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where; Using temporary
+2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where
CREATE TABLE t2 (a CHAR(5) CHARACTER SET latin1 COLLATE latin1_general_ci);
INSERT INTO t2 VALUES (0xf6);
INSERT INTO t2 VALUES ('oe');
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index c7464bb21d2..7068b62993b 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -1035,6 +1035,18 @@ HAVING SUM(t1_inner.b)+t1_outer.b > 5);
ERROR 42000: 'test.t1_outer.b' isn't in GROUP BY
DROP TABLE t1;
SET SQL_MODE = '';
+SET SQL_MODE = 'ONLY_FULL_GROUP_BY';
+create table t1(f1 int, f2 int);
+select * from t1 group by f1;
+ERROR 42000: 'test.t1.f2' isn't in GROUP BY
+select * from t1 group by f2;
+ERROR 42000: 'test.t1.f1' isn't in GROUP BY
+select * from t1 group by f1, f2;
+f1 f2
+select t1.f1,t.* from t1, t1 t group by 1;
+ERROR 42000: 'test.t.f1' isn't in GROUP BY
+drop table t1;
+SET SQL_MODE = '';
CREATE TABLE t1 (a INT, b INT,
PRIMARY KEY (a),
KEY i2(a,b));
diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result
index e720c38ec8d..895cb88a018 100644
--- a/mysql-test/r/innodb_mysql.result
+++ b/mysql-test/r/innodb_mysql.result
@@ -112,6 +112,39 @@ c1
Before and after comparison
0
drop table t1;
+CREATE TABLE t1(c1 TEXT, UNIQUE (c1(1)), cnt INT DEFAULT 1)
+ENGINE=INNODB CHARACTER SET UTF8;
+INSERT INTO t1 (c1) VALUES ('1a');
+SELECT * FROM t1;
+c1 cnt
+1a 1
+INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1;
+SELECT * FROM t1;
+c1 cnt
+1a 2
+DROP TABLE t1;
+CREATE TABLE t1(c1 VARCHAR(2), UNIQUE (c1(1)), cnt INT DEFAULT 1)
+ENGINE=INNODB CHARACTER SET UTF8;
+INSERT INTO t1 (c1) VALUES ('1a');
+SELECT * FROM t1;
+c1 cnt
+1a 1
+INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1;
+SELECT * FROM t1;
+c1 cnt
+1a 2
+DROP TABLE t1;
+CREATE TABLE t1(c1 CHAR(2), UNIQUE (c1(1)), cnt INT DEFAULT 1)
+ENGINE=INNODB CHARACTER SET UTF8;
+INSERT INTO t1 (c1) VALUES ('1a');
+SELECT * FROM t1;
+c1 cnt
+1a 1
+INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1;
+SELECT * FROM t1;
+c1 cnt
+1a 2
+DROP TABLE t1;
End of 4.1 tests
create table t1m (a int) engine = MEMORY;
create table t1i (a int);
@@ -387,7 +420,45 @@ a
2
5
drop table t1;
-End of 5.0 tests
+create table t1(
+id int auto_increment,
+c char(1) not null,
+counter int not null default 1,
+primary key (id),
+unique key (c)
+) engine=innodb;
+insert into t1 (id, c) values
+(NULL, 'a'),
+(NULL, 'a')
+on duplicate key update id = values(id), counter = counter + 1;
+select * from t1;
+id c counter
+2 a 2
+insert into t1 (id, c) values
+(NULL, 'b')
+on duplicate key update id = values(id), counter = counter + 1;
+select * from t1;
+id c counter
+2 a 2
+3 b 1
+truncate table t1;
+insert into t1 (id, c) values (NULL, 'a');
+select * from t1;
+id c counter
+1 a 1
+insert into t1 (id, c) values (NULL, 'b'), (NULL, 'b')
+on duplicate key update id = values(id), c = values(c), counter = counter + 1;
+select * from t1;
+id c counter
+1 a 1
+3 b 2
+insert into t1 (id, c) values (NULL, 'a')
+on duplicate key update id = values(id), c = values(c), counter = counter + 1;
+select * from t1;
+id c counter
+3 b 2
+4 a 2
+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/join.result b/mysql-test/r/join.result
index 0a45aa2eb9b..840a92dcce2 100644
--- a/mysql-test/r/join.result
+++ b/mysql-test/r/join.result
@@ -764,6 +764,49 @@ natural join t5;
y c b a z
1 3 2 1 4
drop table t1, t2, t3, t4, t5;
+CREATE TABLE t1 (ID INTEGER, Name VARCHAR(50));
+CREATE TABLE t2 (Test_ID INTEGER);
+CREATE VIEW v1 (Test_ID, Description) AS SELECT ID, Name FROM t1;
+CREATE TABLE tv1 SELECT Description AS Name FROM v1 JOIN t2
+USING (Test_ID);
+DESCRIBE tv1;
+Field Type Null Key Default Extra
+Name varchar(50) YES NULL
+CREATE TABLE tv2 SELECT Description AS Name FROM v1 JOIN t2
+ON v1.Test_ID = t2.Test_ID;
+DESCRIBE tv2;
+Field Type Null Key Default Extra
+Name varchar(50) YES NULL
+DROP VIEW v1;
+DROP TABLE t1,t2,tv1,tv2;
+create table t1 (a int, b int);
+insert into t1 values
+(NULL, 1),
+(NULL, 2),
+(NULL, 3),
+(NULL, 4);
+create table t2 (a int not null, primary key(a));
+insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t3 (a int not null, primary key(a));
+insert into t3 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+flush status;
+select * from t1, t2, t3 where t3.a=t1.a and t2.a=t1.b;
+a b a a
+explain select * from t1, t2, t3 where t3.a=t1.a and t2.a=t1.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 Using index
+1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index
+We expect rnd_next=5, and read_key must be 0 because of short-cutting:
+show status like 'Handler_read%';
+Variable_name Value
+Handler_read_first 0
+Handler_read_key 0
+Handler_read_next 0
+Handler_read_prev 0
+Handler_read_rnd 0
+Handler_read_rnd_next 5
+drop table t1, t2, t3;
create table t1 (a int);
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t2 (a int, b int, filler char(100), key(a), key(b));
diff --git a/mysql-test/r/ps_2myisam.result b/mysql-test/r/ps_2myisam.result
index d18bb8dc434..2ce35dae092 100644
--- a/mysql-test/r/ps_2myisam.result
+++ b/mysql-test/r/ps_2myisam.result
@@ -3077,7 +3077,7 @@ found
true
set @arg00= CAST('1991-01-01 01:01:01' as datetime) ;
select 'true' as found from t9
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
c14= CAST('1991-01-01 01:01:01' as datetime) and
c15= CAST('1991-01-01 01:01:01' as datetime) and
c16= CAST('1991-01-01 01:01:01' as datetime) and
@@ -3090,7 +3090,7 @@ and c17= @arg00 ;
found
true
prepare stmt1 from "select 'true' as found from t9
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
c14= CAST('1991-01-01 01:01:01' as datetime) and
c15= CAST('1991-01-01 01:01:01' as datetime) and
c16= CAST('1991-01-01 01:01:01' as datetime) and
diff --git a/mysql-test/r/ps_3innodb.result b/mysql-test/r/ps_3innodb.result
index e6ff668760c..70181ecccdc 100644
--- a/mysql-test/r/ps_3innodb.result
+++ b/mysql-test/r/ps_3innodb.result
@@ -3060,7 +3060,7 @@ found
true
set @arg00= CAST('1991-01-01 01:01:01' as datetime) ;
select 'true' as found from t9
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
c14= CAST('1991-01-01 01:01:01' as datetime) and
c15= CAST('1991-01-01 01:01:01' as datetime) and
c16= CAST('1991-01-01 01:01:01' as datetime) and
@@ -3073,7 +3073,7 @@ and c17= @arg00 ;
found
true
prepare stmt1 from "select 'true' as found from t9
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
c14= CAST('1991-01-01 01:01:01' as datetime) and
c15= CAST('1991-01-01 01:01:01' as datetime) and
c16= CAST('1991-01-01 01:01:01' as datetime) and
diff --git a/mysql-test/r/ps_4heap.result b/mysql-test/r/ps_4heap.result
index 046992806cc..19be5a2707e 100644
--- a/mysql-test/r/ps_4heap.result
+++ b/mysql-test/r/ps_4heap.result
@@ -3061,7 +3061,7 @@ found
true
set @arg00= CAST('1991-01-01 01:01:01' as datetime) ;
select 'true' as found from t9
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
c14= CAST('1991-01-01 01:01:01' as datetime) and
c15= CAST('1991-01-01 01:01:01' as datetime) and
c16= CAST('1991-01-01 01:01:01' as datetime) and
@@ -3074,7 +3074,7 @@ and c17= @arg00 ;
found
true
prepare stmt1 from "select 'true' as found from t9
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
c14= CAST('1991-01-01 01:01:01' as datetime) and
c15= CAST('1991-01-01 01:01:01' as datetime) and
c16= CAST('1991-01-01 01:01:01' as datetime) and
diff --git a/mysql-test/r/ps_5merge.result b/mysql-test/r/ps_5merge.result
index bbdb80df5d4..ebdc5c8c9fd 100644
--- a/mysql-test/r/ps_5merge.result
+++ b/mysql-test/r/ps_5merge.result
@@ -2997,7 +2997,7 @@ found
true
set @arg00= CAST('1991-01-01 01:01:01' as datetime) ;
select 'true' as found from t9
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
c14= CAST('1991-01-01 01:01:01' as datetime) and
c15= CAST('1991-01-01 01:01:01' as datetime) and
c16= CAST('1991-01-01 01:01:01' as datetime) and
@@ -3010,7 +3010,7 @@ and c17= @arg00 ;
found
true
prepare stmt1 from "select 'true' as found from t9
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
c14= CAST('1991-01-01 01:01:01' as datetime) and
c15= CAST('1991-01-01 01:01:01' as datetime) and
c16= CAST('1991-01-01 01:01:01' as datetime) and
@@ -6018,7 +6018,7 @@ found
true
set @arg00= CAST('1991-01-01 01:01:01' as datetime) ;
select 'true' as found from t9
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
c14= CAST('1991-01-01 01:01:01' as datetime) and
c15= CAST('1991-01-01 01:01:01' as datetime) and
c16= CAST('1991-01-01 01:01:01' as datetime) and
@@ -6031,7 +6031,7 @@ and c17= @arg00 ;
found
true
prepare stmt1 from "select 'true' as found from t9
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
c14= CAST('1991-01-01 01:01:01' as datetime) and
c15= CAST('1991-01-01 01:01:01' as datetime) and
c16= CAST('1991-01-01 01:01:01' as datetime) and
diff --git a/mysql-test/r/ps_7ndb.result b/mysql-test/r/ps_7ndb.result
index 20d38ba84bd..2cffb698fc0 100644
--- a/mysql-test/r/ps_7ndb.result
+++ b/mysql-test/r/ps_7ndb.result
@@ -3060,7 +3060,7 @@ found
true
set @arg00= CAST('1991-01-01 01:01:01' as datetime) ;
select 'true' as found from t9
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
c14= CAST('1991-01-01 01:01:01' as datetime) and
c15= CAST('1991-01-01 01:01:01' as datetime) and
c16= CAST('1991-01-01 01:01:01' as datetime) and
@@ -3073,7 +3073,7 @@ and c17= @arg00 ;
found
true
prepare stmt1 from "select 'true' as found from t9
-where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
+where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
c14= CAST('1991-01-01 01:01:01' as datetime) and
c15= CAST('1991-01-01 01:01:01' as datetime) and
c16= CAST('1991-01-01 01:01:01' as datetime) and
diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result
index 866701d2204..66283f16120 100644
--- a/mysql-test/r/query_cache.result
+++ b/mysql-test/r/query_cache.result
@@ -947,25 +947,19 @@ COUNT(*)
0
Warnings:
Warning 1292 Incorrect datetime value: '20050327 invalid' for column 'date' at row 1
-Warning 1292 Incorrect datetime value: '20050327 invalid' for column 'date' at row 1
-Warning 1292 Truncated incorrect INTEGER value: '20050327 invalid'
-Warning 1292 Truncated incorrect INTEGER value: '20050327 invalid'
+Warning 1292 Incorrect datetime value: '20050327 invalid' for column 'date' at row 0
SELECT COUNT(*) FROM t1 WHERE date BETWEEN '20050326' AND '20050328 invalid';
COUNT(*)
0
Warnings:
Warning 1292 Incorrect datetime value: '20050328 invalid' for column 'date' at row 1
-Warning 1292 Incorrect datetime value: '20050328 invalid' for column 'date' at row 1
-Warning 1292 Truncated incorrect INTEGER value: '20050328 invalid'
-Warning 1292 Truncated incorrect INTEGER value: '20050328 invalid'
+Warning 1292 Incorrect datetime value: '20050328 invalid' for column 'date' at row 0
SELECT COUNT(*) FROM t1 WHERE date BETWEEN '20050326' AND '20050327 invalid';
COUNT(*)
0
Warnings:
Warning 1292 Incorrect datetime value: '20050327 invalid' for column 'date' at row 1
-Warning 1292 Incorrect datetime value: '20050327 invalid' for column 'date' at row 1
-Warning 1292 Truncated incorrect INTEGER value: '20050327 invalid'
-Warning 1292 Truncated incorrect INTEGER value: '20050327 invalid'
+Warning 1292 Incorrect datetime value: '20050327 invalid' for column 'date' at row 0
show status like "Qcache_queries_in_cache";
Variable_name Value
Qcache_queries_in_cache 0
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 8263350fe58..1fff29cd72c 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -394,13 +394,13 @@ EXPLAIN EXTENDED SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 index NULL PRIMARY 43 NULL 2 100.00 Using where; Using index
Warnings:
-Note 1003 select distinct `test`.`t1`.`date` AS `date` from `test`.`t1` where (`test`.`t1`.`date` = 20020803)
+Note 1003 select distinct `test`.`t1`.`date` AS `date` from `test`.`t1` where (`test`.`t1`.`date` = _latin1'2002-08-03')
EXPLAIN EXTENDED SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03');
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2 SUBQUERY t1 index NULL PRIMARY 43 NULL 2 100.00 Using where; Using index
Warnings:
-Note 1003 select (select distinct `test`.`t1`.`date` AS `date` from `test`.`t1` where (`test`.`t1`.`date` = 20020803)) AS `(SELECT DISTINCT date FROM t1 WHERE date='2002-08-03')`
+Note 1003 select (select distinct `test`.`t1`.`date` AS `date` from `test`.`t1` where (`test`.`t1`.`date` = _latin1'2002-08-03')) AS `(SELECT DISTINCT date FROM t1 WHERE date='2002-08-03')`
SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
date
2002-08-03
@@ -4012,6 +4012,29 @@ WHERE (SELECT COUNT(t0.b) FROM t1 t WHERE t.b>20) GROUP BY a;
ERROR HY000: Invalid use of group function
SET @@sql_mode=default;
DROP TABLE t1;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 values (1),(1),(1),(1);
+CREATE TABLE t2 (x INT);
+INSERT INTO t1 values (1000),(1001),(1002);
+SELECT SUM( (SELECT COUNT(a) FROM t2) ) FROM t1;
+ERROR HY000: Invalid use of group function
+SELECT SUM( (SELECT SUM(COUNT(a)) FROM t2) ) FROM t1;
+ERROR HY000: Invalid use of group function
+SELECT COUNT(1) FROM DUAL;
+COUNT(1)
+1
+SELECT SUM( (SELECT AVG( (SELECT t1.a FROM t2) ) FROM DUAL) ) FROM t1;
+ERROR HY000: Invalid use of group function
+SELECT
+SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING t1.a < 12) ) FROM t2) )
+FROM t1;
+ERROR HY000: Invalid use of group function
+SELECT t1.a as XXA,
+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;
+End of 5.0 tests.
CREATE TABLE t1 (s1 char(1));
INSERT INTO t1 VALUES ('a');
SELECT * FROM t1 WHERE _utf8'a' = ANY (SELECT s1 FROM t1);
diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result
index 045a6cd1c0a..95bd3061fa6 100644
--- a/mysql-test/r/type_datetime.result
+++ b/mysql-test/r/type_datetime.result
@@ -192,6 +192,80 @@ CAST(CAST('2006-08-10 10:11:12' AS DATETIME) + INTERVAL 14 MICROSECOND AS DECIMA
SELECT CAST(CAST('10:11:12.098700' AS TIME) AS DECIMAL(20,6));
CAST(CAST('10:11:12.098700' AS TIME) AS DECIMAL(20,6))
101112.098700
+create table t1 (f1 date, f2 datetime, f3 timestamp);
+insert into t1(f1) values(curdate());
+select curdate() < now(), f1 < now(), cast(f1 as date) < now() from t1;
+curdate() < now() f1 < now() cast(f1 as date) < now()
+1 1 1
+delete from t1;
+insert into t1 values('2001-01-01','2001-01-01 01:01:01','2001-01-01 01:01:01');
+insert into t1 values('2001-02-05','2001-02-05 00:00:00','2001-02-05 01:01:01');
+insert into t1 values('2001-03-10','2001-03-09 01:01:01','2001-03-10 01:01:01');
+insert into t1 values('2001-04-15','2001-04-15 00:00:00','2001-04-15 00:00:00');
+insert into t1 values('2001-05-20','2001-05-20 01:01:01','2001-05-20 01:01:01');
+select f1, f3 from t1 where f1 >= '2001-02-05 00:00:00' and f3 <= '2001-04-15';
+f1 f3
+2001-02-05 2001-02-05 01:01:01
+2001-03-10 2001-03-10 01:01:01
+2001-04-15 2001-04-15 00:00:00
+select f1, f3 from t1 where f1 >= '2001-2-5 0:0:0' and f2 <= '2001-4-15';
+f1 f3
+2001-02-05 2001-02-05 01:01:01
+2001-03-10 2001-03-10 01:01:01
+2001-04-15 2001-04-15 00:00:00
+select f1, f2 from t1 where if(1, f1, 0) >= f2;
+f1 f2
+2001-02-05 2001-02-05 00:00:00
+2001-03-10 2001-03-09 01:01:01
+2001-04-15 2001-04-15 00:00:00
+select 1 from dual where cast('2001-1-1 2:3:4' as date) = cast('2001-01-01' as datetime);
+1
+1
+select f1, f2, UNIX_TIMESTAMP(f2), UNIX_TIMESTAMP(f1),
+f1 > f2, f1 = f2, f1 < f2
+from t1;
+f1 f2 UNIX_TIMESTAMP(f2) UNIX_TIMESTAMP(f1) f1 > f2 f1 = f2 f1 < f2
+2001-01-01 2001-01-01 01:01:01 978300061 978296400 0 0 1
+2001-02-05 2001-02-05 00:00:00 981320400 981320400 0 1 0
+2001-03-10 2001-03-09 01:01:01 984088861 984171600 1 0 0
+2001-04-15 2001-04-15 00:00:00 987282000 987282000 0 1 0
+2001-05-20 2001-05-20 01:01:01 990309661 990306000 0 0 1
+drop table t1;
+create table t1 (f1 date, f2 datetime, f3 timestamp);
+insert into t1 values('2001-01-01','2001-01-01 01:01:01','2001-01-01 01:01:01');
+insert into t1 values('2001-02-05','2001-02-05 00:00:00','2001-02-05 01:01:01');
+insert into t1 values('2001-03-10','2001-03-09 01:01:01','2001-03-10 01:01:01');
+insert into t1 values('2001-04-15','2001-04-15 00:00:00','2001-04-15 00:00:00');
+insert into t1 values('2001-05-20','2001-05-20 01:01:01','2001-05-20 01:01:01');
+select f2 from t1 where f2 between '2001-2-5' and '01-04-14';
+f2
+2001-02-05 00:00:00
+2001-03-09 01:01:01
+select f1, f2, f3 from t1 where f1 between f2 and f3;
+f1 f2 f3
+2001-02-05 2001-02-05 00:00:00 2001-02-05 01:01:01
+2001-03-10 2001-03-09 01:01:01 2001-03-10 01:01:01
+2001-04-15 2001-04-15 00:00:00 2001-04-15 00:00:00
+select f1, f2, f3 from t1 where cast(f1 as datetime) between f2 and
+cast(f3 as date);
+f1 f2 f3
+2001-02-05 2001-02-05 00:00:00 2001-02-05 01:01:01
+2001-03-10 2001-03-09 01:01:01 2001-03-10 01:01:01
+2001-04-15 2001-04-15 00:00:00 2001-04-15 00:00:00
+select f2 from t1 where '2001-04-10 12:34:56' between f2 and '01-05-01';
+f2
+2001-01-01 01:01:01
+2001-02-05 00:00:00
+2001-03-09 01:01:01
+select f2, f3 from t1 where '01-03-10' between f2 and f3;
+f2 f3
+2001-03-09 01:01:01 2001-03-10 01:01:01
+select f2 from t1 where DATE(f2) between "2001-4-15" AND "01-4-15";
+f2
+2001-04-15 00:00:00
+SELECT 1 from dual where NOW() BETWEEN CURRENT_DATE() - INTERVAL 1 DAY AND CURRENT_DATE();
+1
+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/r/windows.result b/mysql-test/r/windows.result
index 9f3828bff61..adb0048b90e 100644
--- a/mysql-test/r/windows.result
+++ b/mysql-test/r/windows.result
@@ -12,6 +12,13 @@ Warnings:
Warning 0 DATA DIRECTORY option ignored
Warning 0 INDEX DIRECTORY option ignored
drop table t1;
+CREATE TABLE t1 (a int, b int);
+INSERT INTO t1 VALUES (1,1);
+EXPLAIN SELECT * FROM t1 WHERE b = (SELECT max(2));
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
+End of 5.0 tests.
create procedure proc_1() install plugin my_plug soname '\\root\\some_plugin.dll';
call proc_1();
ERROR HY000: No paths allowed for shared library
diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test
index fb9c09d4763..4e21568377f 100644
--- a/mysql-test/t/group_by.test
+++ b/mysql-test/t/group_by.test
@@ -752,6 +752,20 @@ SELECT b FROM t1 AS t1_outer GROUP BY a HAVING t1_outer.a IN
HAVING SUM(t1_inner.b)+t1_outer.b > 5);
DROP TABLE t1;
SET SQL_MODE = '';
+#
+# Bug#27874: Non-grouped columns are allowed by * in ONLY_FULL_GROUP_BY mode.
+#
+SET SQL_MODE = 'ONLY_FULL_GROUP_BY';
+create table t1(f1 int, f2 int);
+--error 1055
+select * from t1 group by f1;
+--error 1055
+select * from t1 group by f2;
+select * from t1 group by f1, f2;
+--error 1055
+select t1.f1,t.* from t1, t1 t group by 1;
+drop table t1;
+SET SQL_MODE = '';
#
# Bug #21174: Index degrades sort performance and
diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test
index 72d78dd7074..6c9270f76be 100644
--- a/mysql-test/t/join.test
+++ b/mysql-test/t/join.test
@@ -593,6 +593,46 @@ drop table t1, t2, t3, t4, t5;
# End of tests for WL#2486 - natural/using join
#
+# BUG#25106: A USING clause in combination with a VIEW results in column
+# aliases ignored
+#
+CREATE TABLE t1 (ID INTEGER, Name VARCHAR(50));
+CREATE TABLE t2 (Test_ID INTEGER);
+CREATE VIEW v1 (Test_ID, Description) AS SELECT ID, Name FROM t1;
+
+CREATE TABLE tv1 SELECT Description AS Name FROM v1 JOIN t2
+ USING (Test_ID);
+DESCRIBE tv1;
+CREATE TABLE tv2 SELECT Description AS Name FROM v1 JOIN t2
+ ON v1.Test_ID = t2.Test_ID;
+DESCRIBE tv2;
+
+DROP VIEW v1;
+DROP TABLE t1,t2,tv1,tv2;
+
+
+# BUG#27939: Early NULLs filtering doesn't work for eq_ref access
+create table t1 (a int, b int);
+insert into t1 values
+ (NULL, 1),
+ (NULL, 2),
+ (NULL, 3),
+ (NULL, 4);
+
+create table t2 (a int not null, primary key(a));
+insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+create table t3 (a int not null, primary key(a));
+insert into t3 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+flush status;
+select * from t1, t2, t3 where t3.a=t1.a and t2.a=t1.b;
+explain select * from t1, t2, t3 where t3.a=t1.a and t2.a=t1.b;
+--echo We expect rnd_next=5, and read_key must be 0 because of short-cutting:
+show status like 'Handler_read%';
+drop table t1, t2, t3;
+
+#
# BUG#14940: Make E(#rows) from "range" access be re-used by range optimizer
#
create table t1 (a int);
@@ -631,21 +671,5 @@ select '^^: The above should be ~= 20 + cost(select * from t1). Value less than
drop table t1, t2;
-# BUG#25106: A USING clause in combination with a VIEW results in column
-# aliases ignored
-#
-CREATE TABLE t1 (ID INTEGER, Name VARCHAR(50));
-CREATE TABLE t2 (Test_ID INTEGER);
-CREATE VIEW v1 (Test_ID, Description) AS SELECT ID, Name FROM t1;
-
-CREATE TABLE tv1 SELECT Description AS Name FROM v1 JOIN t2
- USING (Test_ID);
-DESCRIBE tv1;
-CREATE TABLE tv2 SELECT Description AS Name FROM v1 JOIN t2
- ON v1.Test_ID = t2.Test_ID;
-DESCRIBE tv2;
-
-DROP VIEW v1;
-DROP TABLE t1,t2,tv1,tv2;
--echo End of 5.0 tests.
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 3b7cc2abb73..0648c3ebda0 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -2819,6 +2819,62 @@ SELECT tt.a, MAX(
FROM t1 as tt GROUP BY tt.a;
DROP TABLE t1;
+#
+# Bug #27348: SET FUNCTION used in a subquery from WHERE condition
+#
+
+CREATE TABLE t1 (a int, b int);
+INSERT INTO t1 VALUES (2,22),(1,11),(2,22);
+
+SELECT a FROM t1 WHERE (SELECT COUNT(b) FROM DUAL) > 0 GROUP BY a;
+SELECT a FROM t1 WHERE (SELECT COUNT(b) FROM DUAL) > 1 GROUP BY a;
+
+SELECT a FROM t1 t0
+ WHERE (SELECT COUNT(t0.b) FROM t1 t WHERE t.b>20) GROUP BY a;
+
+SET @@sql_mode='ansi';
+--error 1111
+SELECT a FROM t1 WHERE (SELECT COUNT(b) FROM DUAL) > 0 GROUP BY a;
+--error 1111
+SELECT a FROM t1 WHERE (SELECT COUNT(b) FROM DUAL) > 1 GROUP BY a;
+
+--error 1111
+SELECT a FROM t1 t0
+ WHERE (SELECT COUNT(t0.b) FROM t1 t WHERE t.b>20) GROUP BY a;
+
+SET @@sql_mode=default;
+
+DROP TABLE t1;
+
+#
+# Bug #27363: nested aggregates in outer, subquery / sum(select
+# count(outer))
+#
+CREATE TABLE t1 (a INT); INSERT INTO t1 values (1),(1),(1),(1);
+CREATE TABLE t2 (x INT); INSERT INTO t1 values (1000),(1001),(1002);
+
+--error ER_INVALID_GROUP_FUNC_USE
+SELECT SUM( (SELECT COUNT(a) FROM t2) ) FROM t1;
+--error ER_INVALID_GROUP_FUNC_USE
+SELECT SUM( (SELECT SUM(COUNT(a)) FROM t2) ) FROM t1;
+SELECT COUNT(1) FROM DUAL;
+
+--error ER_INVALID_GROUP_FUNC_USE
+SELECT SUM( (SELECT AVG( (SELECT t1.a FROM t2) ) FROM DUAL) ) FROM t1;
+
+--error ER_INVALID_GROUP_FUNC_USE
+SELECT
+ SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING t1.a < 12) ) FROM t2) )
+FROM t1;
+
+--error ER_INVALID_GROUP_FUNC_USE
+SELECT t1.a as XXA,
+ SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING XXA < 12) ) FROM t2) )
+FROM t1;
+
+DROP TABLE t1,t2;
+
+--echo End of 5.0 tests.
#
# Bug #27348: SET FUNCTION used in a subquery from WHERE condition
diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test
index b4c10408b37..eeed14acf15 100644
--- a/mysql-test/t/type_datetime.test
+++ b/mysql-test/t/type_datetime.test
@@ -142,6 +142,46 @@ SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME) + INTERVAL 14 MICROSECOND AS
SELECT CAST(CAST('10:11:12.098700' AS TIME) AS DECIMAL(20,6));
#
+# Bug#27590: Wrong DATE/DATETIME comparison.
+#
+create table t1 (f1 date, f2 datetime, f3 timestamp);
+insert into t1(f1) values(curdate());
+select curdate() < now(), f1 < now(), cast(f1 as date) < now() from t1;
+delete from t1;
+insert into t1 values('2001-01-01','2001-01-01 01:01:01','2001-01-01 01:01:01');
+insert into t1 values('2001-02-05','2001-02-05 00:00:00','2001-02-05 01:01:01');
+insert into t1 values('2001-03-10','2001-03-09 01:01:01','2001-03-10 01:01:01');
+insert into t1 values('2001-04-15','2001-04-15 00:00:00','2001-04-15 00:00:00');
+insert into t1 values('2001-05-20','2001-05-20 01:01:01','2001-05-20 01:01:01');
+select f1, f3 from t1 where f1 >= '2001-02-05 00:00:00' and f3 <= '2001-04-15';
+select f1, f3 from t1 where f1 >= '2001-2-5 0:0:0' and f2 <= '2001-4-15';
+select f1, f2 from t1 where if(1, f1, 0) >= f2;
+select 1 from dual where cast('2001-1-1 2:3:4' as date) = cast('2001-01-01' as datetime);
+select f1, f2, UNIX_TIMESTAMP(f2), UNIX_TIMESTAMP(f1),
+ f1 > f2, f1 = f2, f1 < f2
+ from t1;
+drop table t1;
+
+#
+# Bug#16377: Wrong DATE/DATETIME comparison in BETWEEN function.
+#
+create table t1 (f1 date, f2 datetime, f3 timestamp);
+insert into t1 values('2001-01-01','2001-01-01 01:01:01','2001-01-01 01:01:01');
+insert into t1 values('2001-02-05','2001-02-05 00:00:00','2001-02-05 01:01:01');
+insert into t1 values('2001-03-10','2001-03-09 01:01:01','2001-03-10 01:01:01');
+insert into t1 values('2001-04-15','2001-04-15 00:00:00','2001-04-15 00:00:00');
+insert into t1 values('2001-05-20','2001-05-20 01:01:01','2001-05-20 01:01:01');
+select f2 from t1 where f2 between '2001-2-5' and '01-04-14';
+select f1, f2, f3 from t1 where f1 between f2 and f3;
+select f1, f2, f3 from t1 where cast(f1 as datetime) between f2 and
+ cast(f3 as date);
+select f2 from t1 where '2001-04-10 12:34:56' between f2 and '01-05-01';
+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;
+
+#
# Test of storing datetime into date fields
#
diff --git a/mysql-test/t/windows.test b/mysql-test/t/windows.test
index efdf0963b80..c28df20f090 100644
--- a/mysql-test/t/windows.test
+++ b/mysql-test/t/windows.test
@@ -30,6 +30,14 @@ drop table t1;
# End of 4.1 tests
#
+# Bug #27811: The variable 'join_tab' is being used without being defined
+#
+CREATE TABLE t1 (a int, b int); INSERT INTO t1 VALUES (1,1);
+EXPLAIN SELECT * FROM t1 WHERE b = (SELECT max(2));
+
+--echo End of 5.0 tests.
+
+#
# Bug #20665: All commands supported in Stored Procedures should work in
# Prepared Statements
#
View Lorry Controller Status