diff options
| author | unknown <gluh@eagle.(none)> | 2007-12-13 14:52:49 +0400 |
|---|---|---|
| committer | unknown <gluh@eagle.(none)> | 2007-12-13 14:52:49 +0400 |
| commit | 33f82b17893edc7d39e523f3cf03858338b6a4a0 (patch) | |
| tree | b870e5e7b713672711344eb2e5b585f3e2c48375 /mysql-test | |
| parent | 0ced2a4d19839417ca6a6d6c8cc9c5cba259ef76 (diff) | |
| parent | 196f0c751f9a36b34f0dd5e15df4a9fcd51c0b81 (diff) | |
| download | mariadb-git-33f82b17893edc7d39e523f3cf03858338b6a4a0.tar.gz | |
Merge mysql.com:/home/gluh/MySQL/Merge/5.0
into mysql.com:/home/gluh/MySQL/Merge/5.0-opt
client/mysql.cc:
Auto merged
client/mysqltest.c:
Auto merged
include/mysql_com.h:
Auto merged
libmysql/CMakeLists.txt:
Auto merged
myisam/mi_check.c:
Auto merged
mysql-test/r/delayed.result:
Auto merged
mysql-test/r/innodb.result:
Auto merged
mysql-test/r/myisam.result:
Auto merged
mysql-test/r/ps.result:
Auto merged
mysql-test/t/merge.test:
Auto merged
mysql-test/t/myisam.test:
Auto merged
mysql-test/t/subselect.test:
Auto merged
mysql-test/t/type_datetime.test:
Auto merged
mysql-test/t/variables.test:
Auto merged
sql/field.cc:
Auto merged
sql/ha_myisam.cc:
Auto merged
sql/item.cc:
Auto merged
sql/item.h:
Auto merged
sql/item_cmpfunc.cc:
Auto merged
sql/item_func.cc:
Auto merged
sql/item_func.h:
Auto merged
sql/mysql_priv.h:
Auto merged
sql/mysqld.cc:
Auto merged
sql/opt_range.cc:
Auto merged
sql/set_var.cc:
Auto merged
sql/set_var.h:
Auto merged
sql/sql_class.h:
Auto merged
sql/sql_select.cc:
Auto merged
sql/sql_table.cc:
Auto merged
sql/sql_yacc.yy:
Auto merged
mysql-test/r/func_misc.result:
manual merge
mysql-test/r/innodb_mysql.result:
manual merge
mysql-test/t/func_misc.test:
manual merge
mysql-test/t/innodb_mysql.test:
manual merge
sql/sql_insert.cc:
manual merge
Diffstat (limited to 'mysql-test')
55 files changed, 1433 insertions, 219 deletions
diff --git a/mysql-test/include/ps_query.inc b/mysql-test/include/ps_query.inc index e96d666eaec..ae6027a0e07 100644 --- a/mysql-test/include/ps_query.inc +++ b/mysql-test/include/ps_query.inc @@ -434,8 +434,8 @@ execute stmt1 ; let $1= 3 ; while ($1) { - prepare stmt1 from ' SELECT a as ccc from t1 where a+1= - (SELECT 1+ccc from t1 where ccc+1=a+1 and a=1) '; + prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1= + (SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) '; execute stmt1 ; deallocate prepare stmt1 ; dec $1 ; diff --git a/mysql-test/r/delayed.result b/mysql-test/r/delayed.result index 9f6ebea7e1b..e7ee23b3e2a 100644 --- a/mysql-test/r/delayed.result +++ b/mysql-test/r/delayed.result @@ -109,12 +109,20 @@ c1 DROP TABLE t1; SET @@auto_increment_offset= @bug20627_old_auto_increment_offset; +Warnings: +Warning 1292 Truncated incorrect auto-increment-offset value: '0' SET @@auto_increment_increment= @bug20627_old_auto_increment_increment; +Warnings: +Warning 1292 Truncated incorrect auto-increment-increment value: '0' SET @@session.auto_increment_offset= @bug20627_old_session_auto_increment_offset; +Warnings: +Warning 1292 Truncated incorrect auto-increment-offset value: '0' SET @@session.auto_increment_increment= @bug20627_old_session_auto_increment_increment; +Warnings: +Warning 1292 Truncated incorrect auto-increment-increment value: '0' SET @bug20830_old_auto_increment_offset= @@auto_increment_offset; SET @bug20830_old_auto_increment_increment= @@ -237,12 +245,20 @@ SUM(c1) DROP TABLE t1; SET @@auto_increment_offset= @bug20830_old_auto_increment_offset; +Warnings: +Warning 1292 Truncated incorrect auto-increment-offset value: '0' SET @@auto_increment_increment= @bug20830_old_auto_increment_increment; +Warnings: +Warning 1292 Truncated incorrect auto-increment-increment value: '0' SET @@session.auto_increment_offset= @bug20830_old_session_auto_increment_offset; +Warnings: +Warning 1292 Truncated incorrect auto-increment-offset value: '0' SET @@session.auto_increment_increment= @bug20830_old_session_auto_increment_increment; +Warnings: +Warning 1292 Truncated incorrect auto-increment-increment value: '0' CREATE TABLE t1(a BIT); INSERT DELAYED INTO t1 VALUES(1); FLUSH TABLE t1; diff --git a/mysql-test/r/delete.result b/mysql-test/r/delete.result index eb93c69d960..a682d90fbf7 100644 --- a/mysql-test/r/delete.result +++ b/mysql-test/r/delete.result @@ -234,43 +234,6 @@ ERROR 42S22: Unknown column 't2.x' in 'order clause' DELETE FROM t1 ORDER BY (SELECT x); ERROR 42S22: Unknown column 'x' in 'field list' DROP TABLE t1; -CREATE TABLE t1 ( -a INT -); -CREATE TABLE t2 ( -a INT -); -CREATE DATABASE db1; -CREATE TABLE db1.t1 ( -a INT -); -INSERT INTO db1.t1 (a) SELECT * FROM t1; -CREATE DATABASE db2; -CREATE TABLE db2.t1 ( -a INT -); -INSERT INTO db2.t1 (a) SELECT * FROM t2; -DELETE FROM t1 alias USING t1, t2 alias WHERE t1.a = alias.a; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'alias USING t1, t2 alias WHERE t1.a = alias.a' at line 1 -DELETE FROM alias USING t1, t2 alias WHERE t1.a = alias.a; -DELETE FROM t1, alias USING t1, t2 alias WHERE t1.a = alias.a; -DELETE FROM t1, t2 USING t1, t2 alias WHERE t1.a = alias.a; -ERROR 42S02: Unknown table 't2' in MULTI DELETE -DELETE FROM db1.t1 alias USING db1.t1, db2.t1 alias WHERE db1.t1.a = alias.a; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'alias USING db1.t1, db2.t1 alias WHERE db1.t1.a = alias.a' at line 1 -DELETE FROM alias USING db1.t1, db2.t1 alias WHERE db1.t1.a = alias.a; -ERROR 42S02: Unknown table 'alias' in MULTI DELETE -DELETE FROM db2.alias USING db1.t1, db2.t1 alias WHERE db1.t1.a = alias.a; -DELETE FROM t1 USING t1 WHERE a = 1; -SELECT * FROM t1; -a -DELETE FROM t1 alias USING t1 alias WHERE a = 2; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'alias USING t1 alias WHERE a = 2' at line 1 -SELECT * FROM t1; -a -DROP TABLE t1, t2; -DROP DATABASE db1; -DROP DATABASE db2; CREATE FUNCTION f1() RETURNS INT RETURN 1; CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (0); diff --git a/mysql-test/r/explain.result b/mysql-test/r/explain.result index 24ff44945bf..a4c8432d2a4 100644 --- a/mysql-test/r/explain.result +++ b/mysql-test/r/explain.result @@ -87,3 +87,23 @@ Warnings: Note 1003 select '1' AS `f1`,'1' AS `f2` from `test`.`t1` having 1 drop view v1; drop table t1; +CREATE TABLE t1(c INT); +INSERT INTO t1 VALUES (),(); +CREATE TABLE t2 (b INT, +KEY(b),KEY(b),KEY(b),KEY(b),KEY(b), +KEY(b),KEY(b),KEY(b),KEY(b),KEY(b), +KEY(b),KEY(b),KEY(b),KEY(b),KEY(b), +KEY(b),KEY(b),KEY(b),KEY(b),KEY(b), +KEY(b),KEY(b),KEY(b),KEY(b),KEY(b), +KEY(b),KEY(b),KEY(b),KEY(b),KEY(b), +KEY(b),KEY(b),KEY(b),KEY(b),KEY(b), +KEY(b),KEY(b),KEY(b),KEY(b),KEY(b)); +INSERT INTO t2 VALUES (),(),(); +EXPLAIN SELECT 1 FROM +(SELECT 1 FROM t2,t1 WHERE b < c GROUP BY 1 LIMIT 1) AS d2; +id select_type table type possible_keys key key_len ref rows Extra +X X X X X X X X X const row not found +X X X X X X X X X +X X X X X X X X X Range checked for each record (index map: 0xFFFFFFFFFF) +DROP TABLE t2; +DROP TABLE t1; diff --git a/mysql-test/r/federated.result b/mysql-test/r/federated.result index 2e7d0ddcea7..a005db4deac 100644 --- a/mysql-test/r/federated.result +++ b/mysql-test/r/federated.result @@ -1934,6 +1934,117 @@ select * from federated.t2; a 1 drop table federated.t1, federated.t2; +create table t1 (a varchar(256)); +drop view if exists v1; +create view v1 as select a from t1; +create table t1 +(a varchar(256)) engine=federated +connection='mysql://root@127.0.0.1:SLAVE_PORT/test/v1'; +select 1 from t1 order by a; +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +drop table t1; +drop table t1; +drop view v1; DROP TABLE IF EXISTS federated.t1; DROP DATABASE IF EXISTS federated; DROP TABLE IF EXISTS federated.t1; diff --git a/mysql-test/r/func_misc.result b/mysql-test/r/func_misc.result index c0b666dbc79..4ba5ee674a9 100644 --- a/mysql-test/r/func_misc.result +++ b/mysql-test/r/func_misc.result @@ -207,6 +207,11 @@ test SELECT NAME_CONST('test', 'test'); test test +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (5), (2); +SELECT NAME_CONST(x,2) FROM (SELECT a x FROM t1) t; +ERROR HY000: The 'NAME_CONST' syntax is reserved for purposes internal to the MySQL server +DROP TABLE t1; CREATE TABLE t1(a INT); INSERT INTO t1 VALUES (), (), (); SELECT NAME_CONST(a, '1') FROM t1; diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index 74859be4d04..71234385c0d 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -1270,4 +1270,16 @@ select concat(a,ifnull(min(date_format(now(), '%Y-%m-%d')),' ull')) from t1; ERROR HY000: Illegal mix of collations (ascii_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation 'concat' set lc_time_names=en_US; drop table t1; +select DATE_ADD('20071108181000', INTERVAL 1 DAY); +DATE_ADD('20071108181000', INTERVAL 1 DAY) +2007-11-09 18:10:00 +select DATE_ADD(20071108181000, INTERVAL 1 DAY); +DATE_ADD(20071108181000, INTERVAL 1 DAY) +2007-11-09 18:10:00 +select DATE_ADD('20071108', INTERVAL 1 DAY); +DATE_ADD('20071108', INTERVAL 1 DAY) +2007-11-09 +select DATE_ADD(20071108, INTERVAL 1 DAY); +DATE_ADD(20071108, INTERVAL 1 DAY) +2007-11-09 End of 5.0 tests diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result index 40c70721347..ac6356f8203 100644 --- a/mysql-test/r/gis.result +++ b/mysql-test/r/gis.result @@ -680,7 +680,7 @@ def test t1 t1 g g 255 4294967295 0 Y 144 0 63 g select asbinary(g) from t1; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def asbinary(g) 252 8192 0 Y 128 0 63 +def asbinary(g) 252 4294967295 0 Y 128 0 63 asbinary(g) drop table t1; create table t1 (a TEXT, b GEOMETRY NOT NULL, SPATIAL KEY(b)); @@ -742,6 +742,38 @@ select geomfromtext(col9,col89) as a from t1; a NULL DROP TABLE t1; +CREATE TABLE t1 ( +geomdata polygon NOT NULL, +SPATIAL KEY index_geom (geomdata) +) ENGINE=MyISAM DEFAULT CHARSET=latin2 DELAY_KEY_WRITE=1 ROW_FORMAT=FIXED; +CREATE TABLE t2 ( +geomdata polygon NOT NULL, +SPATIAL KEY index_geom (geomdata) +) ENGINE=MyISAM DEFAULT CHARSET=latin2 DELAY_KEY_WRITE=1 ROW_FORMAT=FIXED; +CREATE TABLE t3 +select +aswkb(ws.geomdata) AS geomdatawkb +from +t1 ws +union +select +aswkb(ws.geomdata) AS geomdatawkb +from +t2 ws; +describe t3; +Field Type Null Key Default Extra +geomdatawkb longblob YES NULL +drop table t1; +drop table t2; +drop table t3; +create table t1(col1 geometry default null,col15 geometrycollection not +null,spatial index(col15),index(col1(15)))engine=myisam; +insert into t1 set col15 = GeomFromText('POINT(6 5)'); +insert into t1 set col15 = GeomFromText('POINT(6 5)'); +check table t1 extended; +Table Op Msg_type Msg_text +test.t1 check status OK +drop table t1; End of 4.1 tests create table t1 (s1 geometry not null,s2 char(100)); create trigger t1_bu before update on t1 for each row set new.s1 = null; @@ -933,4 +965,10 @@ COUNT(*) 2 DROP TABLE t1, t2; End of 5.0 tests +create table `t1` (`col002` point)engine=myisam; +insert into t1 values (),(),(); +select min(`col002`) from t1 union select `col002` from t1; +min(`col002`) +NULL +drop table t1; End of 5.0 tests diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result index 2e5193f8563..5982931e677 100644 --- a/mysql-test/r/group_min_max.result +++ b/mysql-test/r/group_min_max.result @@ -2307,3 +2307,49 @@ a 2 4 DROP TABLE t1; +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3); +INSERT INTO t1 SELECT a + 1, b FROM t1; +INSERT INTO t1 SELECT a + 2, b FROM t1; +EXPLAIN +SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 12 Using temporary; Using filesort +SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC; +a MIN(b) MAX(b) +4 1 3 +3 1 3 +2 1 3 +1 1 3 +CREATE INDEX break_it ON t1 (a, b); +EXPLAIN +SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range NULL break_it 10 NULL 7 Using index for group-by +SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a; +a MIN(b) MAX(b) +1 1 3 +2 1 3 +3 1 3 +4 1 3 +EXPLAIN +SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range NULL break_it 10 NULL 7 Using index for group-by; Using temporary; Using filesort +SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC; +a MIN(b) MAX(b) +4 1 3 +3 1 3 +2 1 3 +1 1 3 +EXPLAIN +SELECT a, MIN(b), MAX(b), AVG(b) FROM t1 GROUP BY a ORDER BY a DESC; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL break_it 10 NULL 12 Using index +SELECT a, MIN(b), MAX(b), AVG(b) FROM t1 GROUP BY a ORDER BY a DESC; +a MIN(b) MAX(b) AVG(b) +4 1 3 2.0000 +3 1 3 2.0000 +2 1 3 2.0000 +1 1 3 2.0000 +DROP TABLE t1; diff --git a/mysql-test/r/index_merge.result b/mysql-test/r/index_merge.result index 15aa636d740..3a152fb2327 100644 --- a/mysql-test/r/index_merge.result +++ b/mysql-test/r/index_merge.result @@ -340,6 +340,8 @@ create table t4 (a int); insert into t4 values (1),(4),(3); set @save_join_buffer_size=@@join_buffer_size; set join_buffer_size= 4000; +Warnings: +Warning 1292 Truncated incorrect join_buffer_size value: '4000' explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) from t0 as A force index(i1,i2), t0 as B force index (i1,i2) where (A.key1 < 500000 or A.key2 < 3) diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index 38b26425ec8..854712fdb1d 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -1846,6 +1846,8 @@ show variables like "innodb_thread_concurrency"; Variable_name Value innodb_thread_concurrency 8 set global innodb_thread_concurrency=1001; +Warnings: +Warning 1292 Truncated incorrect innodb_thread_concurrency value: '1001' show variables like "innodb_thread_concurrency"; Variable_name Value innodb_thread_concurrency 1000 @@ -1865,6 +1867,8 @@ show variables like "innodb_concurrency_tickets"; Variable_name Value innodb_concurrency_tickets 1000 set global innodb_concurrency_tickets=0; +Warnings: +Warning 1292 Truncated incorrect innodb_concurrency_tickets value: '0' show variables like "innodb_concurrency_tickets"; Variable_name Value innodb_concurrency_tickets 1 diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result index 5a85df76347..2ab463e7f85 100644 --- a/mysql-test/r/innodb_mysql.result +++ b/mysql-test/r/innodb_mysql.result @@ -712,6 +712,8 @@ INSERT INTO t1(b,c) SELECT b,c FROM t2; UPDATE t2 SET c='2007-01-03'; INSERT INTO t1(b,c) SELECT b,c FROM t2; set @@sort_buffer_size=8192; +Warnings: +Warning 1292 Truncated incorrect sort_buffer_size value: '8192' SELECT COUNT(*) FROM t1; COUNT(*) 3072 @@ -1211,6 +1213,18 @@ a b 3 2 1 1 DROP TABLE t1; +CREATE TABLE t1 (id int, type char(6), d int, INDEX idx(id,d)) ENGINE=InnoDB; +INSERT INTO t1 VALUES +(191, 'member', 1), (NULL, 'member', 3), (NULL, 'member', 4), (201, 'member', 2); +EXPLAIN SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL idx NULL NULL NULL 3 Using where; Using filesort +SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d; +id type d +191 member 1 +NULL member 3 +NULL member 4 +DROP TABLE t1; create table t1(a char(10) not null, unique key aa(a(1)), b char(4) not null, unique key bb(b(4))) engine=innodb; desc t1; diff --git a/mysql-test/r/key_cache.result b/mysql-test/r/key_cache.result index 08d8059f61b..9ada5dc0784 100644 --- a/mysql-test/r/key_cache.result +++ b/mysql-test/r/key_cache.result @@ -276,6 +276,8 @@ Variable_name Value Key_blocks_unused KEY_BLOCKS_UNUSED set global keycache2.key_buffer_size=0; set global keycache3.key_buffer_size=100; +Warnings: +Warning 1292 Truncated incorrect key_buffer_size value: '100' set global keycache3.key_buffer_size=0; create table t1 (mytext text, FULLTEXT (mytext)); insert t1 values ('aaabbb'); diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result index 24a56fbf575..2ceae95dfca 100644 --- a/mysql-test/r/merge.result +++ b/mysql-test/r/merge.result @@ -881,4 +881,41 @@ CREATE TABLE t2 (c1 INT) ENGINE=MERGE UNION=(t1) INSERT_METHOD=FIRST; CREATE TABLE IF NOT EXISTS t1 SELECT * FROM t2; ERROR HY000: You can't specify target table 't1' for update in FROM clause DROP TABLE t1, t2; +CREATE TABLE t1 (id INT NOT NULL, ref INT NOT NULL, INDEX (id)) ENGINE=MyISAM; +CREATE TABLE t2 LIKE t1; +INSERT INTO t2 (id, ref) VALUES (1,3), (2,1), (3,2), (4,5), (4,4); +INSERT INTO t1 SELECT * FROM t2; +INSERT INTO t1 SELECT * FROM t2; +CREATE TABLE t3 (id INT NOT NULL, ref INT NOT NULL, INDEX (id)) ENGINE=MERGE +UNION(t1); +SELECT * FROM t3 AS a INNER JOIN t3 AS b USING (id) WHERE a.ref < b.ref; +id ref ref +4 4 5 +4 4 5 +4 4 5 +4 4 5 +SELECT * FROM t3; +id ref +1 3 +2 1 +3 2 +4 5 +4 4 +1 3 +2 1 +3 2 +4 5 +4 4 +DELETE FROM a USING t3 AS a INNER JOIN t3 AS b USING (id) WHERE a.ref < b.ref; +SELECT * FROM t3; +id ref +1 3 +2 1 +3 2 +4 5 +1 3 +2 1 +3 2 +4 5 +DROP TABLE t1, t2, t3; End of 5.0 tests diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result index 56933f45fbf..33f64d600bb 100644 --- a/mysql-test/r/myisam.result +++ b/mysql-test/r/myisam.result @@ -1839,4 +1839,26 @@ CHECK TABLE t1 EXTENDED; Table Op Msg_type Msg_text test.t1 check status OK DROP TABLE t1; +CREATE TABLE t1 (id int NOT NULL, ref int NOT NULL, INDEX (id)) ENGINE=MyISAM; +CREATE TABLE t2 LIKE t1; +INSERT INTO t2 (id, ref) VALUES (1,3), (2,1), (3,2), (4,5), (4,4); +INSERT INTO t1 SELECT * FROM t2; +SELECT * FROM t1 AS a INNER JOIN t1 AS b USING (id) WHERE a.ref < b.ref; +id ref ref +4 4 5 +SELECT * FROM t1; +id ref +1 3 +2 1 +3 2 +4 5 +4 4 +DELETE FROM a USING t1 AS a INNER JOIN t1 AS b USING (id) WHERE a.ref < b.ref; +SELECT * FROM t1; +id ref +1 3 +2 1 +3 2 +4 5 +DROP TABLE t1, t2; End of 5.0 tests diff --git a/mysql-test/r/outfile_loaddata.result b/mysql-test/r/outfile_loaddata.result index 1bcaf308b7c..4a9bdcf412d 100644 --- a/mysql-test/r/outfile_loaddata.result +++ b/mysql-test/r/outfile_loaddata.result @@ -82,4 +82,22 @@ c1 c2 -r- =raker= DROP TABLE t2; DROP TABLE t1; +# +# Bug#32533: SELECT INTO OUTFILE never escapes multibyte character +# +CREATE TABLE t1 (c1 VARCHAR(256)); +INSERT INTO t1 VALUES (0xC3); +SELECT HEX(c1) FROM t1; +HEX(c1) +C3 +SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug32533.txt' FIELDS ENCLOSED BY 0xC3 FROM t1; +TRUNCATE t1; +SELECT HEX(LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug32533.txt')); +HEX(LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug32533.txt')) +C35CC3C30A +LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug32533.txt' INTO TABLE t1 FIELDS ENCLOSED BY 0xC3; +SELECT HEX(c1) FROM t1; +HEX(c1) +C3 +DROP TABLE t1; # End of 5.0 tests. diff --git a/mysql-test/r/packet.result b/mysql-test/r/packet.result index dfb5595e02d..df0d9ff9adc 100644 --- a/mysql-test/r/packet.result +++ b/mysql-test/r/packet.result @@ -1,7 +1,15 @@ set global max_allowed_packet=100; +Warnings: +Warning 1292 Truncated incorrect max_allowed_packet value: '100' set max_allowed_packet=100; +Warnings: +Warning 1292 Truncated incorrect max_allowed_packet value: '100' set global net_buffer_length=100; +Warnings: +Warning 1292 Truncated incorrect net_buffer_length value: '100' set net_buffer_length=100; +Warnings: +Warning 1292 Truncated incorrect net_buffer_length value: '100' SELECT length("aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa") as len; len 1024 diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result index 0682cae5c80..f547654bed1 100644 --- a/mysql-test/r/ps.result +++ b/mysql-test/r/ps.result @@ -806,6 +806,8 @@ select @@max_prepared_stmt_count; @@max_prepared_stmt_count 0 set global max_prepared_stmt_count=10000000000000000; +Warnings: +Warning 1292 Truncated incorrect max_prepared_stmt_count value: '10000000000000000' select @@max_prepared_stmt_count; @@max_prepared_stmt_count 1048576 diff --git a/mysql-test/r/ps_2myisam.result b/mysql-test/r/ps_2myisam.result index 57932a6c455..891e8e4ff02 100644 --- a/mysql-test/r/ps_2myisam.result +++ b/mysql-test/r/ps_2myisam.result @@ -783,20 +783,20 @@ a b 2 two 3 three 4 four -prepare stmt1 from ' SELECT a as ccc from t1 where a+1= - (SELECT 1+ccc from t1 where ccc+1=a+1 and a=1) '; +prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1= + (SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) '; execute stmt1 ; ccc 1 deallocate prepare stmt1 ; -prepare stmt1 from ' SELECT a as ccc from t1 where a+1= - (SELECT 1+ccc from t1 where ccc+1=a+1 and a=1) '; +prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1= + (SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) '; execute stmt1 ; ccc 1 deallocate prepare stmt1 ; -prepare stmt1 from ' SELECT a as ccc from t1 where a+1= - (SELECT 1+ccc from t1 where ccc+1=a+1 and a=1) '; +prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1= + (SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) '; execute stmt1 ; ccc 1 @@ -1933,7 +1933,7 @@ def @arg13 253 8192 10 Y 128 31 63 def @arg14 253 8192 19 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 8 Y 128 31 63 -def @arg17 253 20 4 Y 128 0 63 +def @arg17 253 20 4 Y 160 0 63 def @arg18 253 20 1 Y 128 0 63 def @arg19 253 20 1 Y 128 0 63 def @arg20 253 8192 1 Y 0 31 8 @@ -1980,7 +1980,7 @@ def @arg13 253 8192 0 Y 128 31 63 def @arg14 253 8192 0 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 0 Y 128 31 63 -def @arg17 253 20 0 Y 128 0 63 +def @arg17 253 20 0 Y 160 0 63 def @arg18 253 20 0 Y 128 0 63 def @arg19 253 20 0 Y 128 0 63 def @arg20 253 8192 0 Y 0 31 8 @@ -2030,7 +2030,7 @@ def @arg13 253 8192 10 Y 128 31 63 def @arg14 253 8192 19 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 8 Y 128 31 63 -def @arg17 253 20 4 Y 128 0 63 +def @arg17 253 20 4 Y 160 0 63 def @arg18 253 20 1 Y 128 0 63 def @arg19 253 20 1 Y 128 0 63 def @arg20 253 8192 1 Y 0 31 8 @@ -2070,7 +2070,7 @@ def @arg13 253 8192 0 Y 128 31 63 def @arg14 253 8192 0 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 0 Y 128 31 63 -def @arg17 253 20 0 Y 128 0 63 +def @arg17 253 20 0 Y 160 0 63 def @arg18 253 20 0 Y 128 0 63 def @arg19 253 20 0 Y 128 0 63 def @arg20 253 8192 0 Y 0 31 8 @@ -2118,7 +2118,7 @@ def @arg13 253 8192 10 Y 128 31 63 def @arg14 253 8192 19 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 8 Y 128 31 63 -def @arg17 253 20 4 Y 128 0 63 +def @arg17 253 20 4 Y 160 0 63 def @arg18 253 20 1 Y 128 0 63 def @arg19 253 20 1 Y 128 0 63 def @arg20 253 8192 1 Y 0 31 8 @@ -2162,7 +2162,7 @@ def @arg13 253 8192 0 Y 128 31 63 def @arg14 253 8192 0 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 0 Y 128 31 63 -def @arg17 253 20 0 Y 128 0 63 +def @arg17 253 20 0 Y 160 0 63 def @arg18 253 20 0 Y 128 0 63 def @arg19 253 20 0 Y 128 0 63 def @arg20 253 8192 0 Y 0 31 8 @@ -2208,7 +2208,7 @@ def @arg13 253 8192 10 Y 128 31 63 def @arg14 253 8192 19 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 8 Y 128 31 63 -def @arg17 253 20 4 Y 128 0 63 +def @arg17 253 20 4 Y 160 0 63 def @arg18 253 20 1 Y 128 0 63 def @arg19 253 20 1 Y 128 0 63 def @arg20 253 8192 1 Y 0 31 8 @@ -2246,7 +2246,7 @@ def @arg13 253 8192 0 Y 128 31 63 def @arg14 253 8192 0 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 0 Y 128 31 63 -def @arg17 253 20 0 Y 128 0 63 +def @arg17 253 20 0 Y 160 0 63 def @arg18 253 20 0 Y 128 0 63 def @arg19 253 20 0 Y 128 0 63 def @arg20 253 8192 0 Y 0 31 8 diff --git a/mysql-test/r/ps_3innodb.result b/mysql-test/r/ps_3innodb.result index fd24c29d558..abbaba7463c 100644 --- a/mysql-test/r/ps_3innodb.result +++ b/mysql-test/r/ps_3innodb.result @@ -783,20 +783,20 @@ a b 2 two 3 three 4 four -prepare stmt1 from ' SELECT a as ccc from t1 where a+1= - (SELECT 1+ccc from t1 where ccc+1=a+1 and a=1) '; +prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1= + (SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) '; execute stmt1 ; ccc 1 deallocate prepare stmt1 ; -prepare stmt1 from ' SELECT a as ccc from t1 where a+1= - (SELECT 1+ccc from t1 where ccc+1=a+1 and a=1) '; +prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1= + (SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) '; execute stmt1 ; ccc 1 deallocate prepare stmt1 ; -prepare stmt1 from ' SELECT a as ccc from t1 where a+1= - (SELECT 1+ccc from t1 where ccc+1=a+1 and a=1) '; +prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1= + (SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) '; execute stmt1 ; ccc 1 @@ -1916,7 +1916,7 @@ def @arg13 253 8192 10 Y 128 31 63 def @arg14 253 8192 19 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 8 Y 128 31 63 -def @arg17 253 20 4 Y 128 0 63 +def @arg17 253 20 4 Y 160 0 63 def @arg18 253 20 1 Y 128 0 63 def @arg19 253 20 1 Y 128 0 63 def @arg20 253 8192 1 Y 0 31 8 @@ -1963,7 +1963,7 @@ def @arg13 253 8192 0 Y 128 31 63 def @arg14 253 8192 0 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 0 Y 128 31 63 -def @arg17 253 20 0 Y 128 0 63 +def @arg17 253 20 0 Y 160 0 63 def @arg18 253 20 0 Y 128 0 63 def @arg19 253 20 0 Y 128 0 63 def @arg20 253 8192 0 Y 0 31 8 @@ -2013,7 +2013,7 @@ def @arg13 253 8192 10 Y 128 31 63 def @arg14 253 8192 19 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 8 Y 128 31 63 -def @arg17 253 20 4 Y 128 0 63 +def @arg17 253 20 4 Y 160 0 63 def @arg18 253 20 1 Y 128 0 63 def @arg19 253 20 1 Y 128 0 63 def @arg20 253 8192 1 Y 0 31 8 @@ -2053,7 +2053,7 @@ def @arg13 253 8192 0 Y 128 31 63 def @arg14 253 8192 0 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 0 Y 128 31 63 -def @arg17 253 20 0 Y 128 0 63 +def @arg17 253 20 0 Y 160 0 63 def @arg18 253 20 0 Y 128 0 63 def @arg19 253 20 0 Y 128 0 63 def @arg20 253 8192 0 Y 0 31 8 @@ -2101,7 +2101,7 @@ def @arg13 253 8192 10 Y 128 31 63 def @arg14 253 8192 19 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 8 Y 128 31 63 -def @arg17 253 20 4 Y 128 0 63 +def @arg17 253 20 4 Y 160 0 63 def @arg18 253 20 1 Y 128 0 63 def @arg19 253 20 1 Y 128 0 63 def @arg20 253 8192 1 Y 0 31 8 @@ -2145,7 +2145,7 @@ def @arg13 253 8192 0 Y 128 31 63 def @arg14 253 8192 0 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 0 Y 128 31 63 -def @arg17 253 20 0 Y 128 0 63 +def @arg17 253 20 0 Y 160 0 63 def @arg18 253 20 0 Y 128 0 63 def @arg19 253 20 0 Y 128 0 63 def @arg20 253 8192 0 Y 0 31 8 @@ -2191,7 +2191,7 @@ def @arg13 253 8192 10 Y 128 31 63 def @arg14 253 8192 19 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 8 Y 128 31 63 -def @arg17 253 20 4 Y 128 0 63 +def @arg17 253 20 4 Y 160 0 63 def @arg18 253 20 1 Y 128 0 63 def @arg19 253 20 1 Y 128 0 63 def @arg20 253 8192 1 Y 0 31 8 @@ -2229,7 +2229,7 @@ def @arg13 253 8192 0 Y 128 31 63 def @arg14 253 8192 0 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 0 Y 128 31 63 -def @arg17 253 20 0 Y 128 0 63 +def @arg17 253 20 0 Y 160 0 63 def @arg18 253 20 0 Y 128 0 63 def @arg19 253 20 0 Y 128 0 63 def @arg20 253 8192 0 Y 0 31 8 diff --git a/mysql-test/r/ps_4heap.result b/mysql-test/r/ps_4heap.result index b4596ab85bc..e6a4caadf1e 100644 --- a/mysql-test/r/ps_4heap.result +++ b/mysql-test/r/ps_4heap.result @@ -784,20 +784,20 @@ a b 2 two 3 three 4 four -prepare stmt1 from ' SELECT a as ccc from t1 where a+1= - (SELECT 1+ccc from t1 where ccc+1=a+1 and a=1) '; +prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1= + (SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) '; execute stmt1 ; ccc 1 deallocate prepare stmt1 ; -prepare stmt1 from ' SELECT a as ccc from t1 where a+1= - (SELECT 1+ccc from t1 where ccc+1=a+1 and a=1) '; +prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1= + (SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) '; execute stmt1 ; ccc 1 deallocate prepare stmt1 ; -prepare stmt1 from ' SELECT a as ccc from t1 where a+1= - (SELECT 1+ccc from t1 where ccc+1=a+1 and a=1) '; +prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1= + (SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) '; execute stmt1 ; ccc 1 @@ -1917,7 +1917,7 @@ def @arg13 253 8192 10 Y 128 31 63 def @arg14 253 8192 19 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 8 Y 128 31 63 -def @arg17 253 20 4 Y 128 0 63 +def @arg17 253 20 4 Y 160 0 63 def @arg18 253 20 1 Y 128 0 63 def @arg19 253 20 1 Y 128 0 63 def @arg20 253 8192 1 Y 0 31 8 @@ -1964,7 +1964,7 @@ def @arg13 253 8192 0 Y 128 31 63 def @arg14 253 8192 0 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 0 Y 128 31 63 -def @arg17 253 20 0 Y 128 0 63 +def @arg17 253 20 0 Y 160 0 63 def @arg18 253 20 0 Y 128 0 63 def @arg19 253 20 0 Y 128 0 63 def @arg20 253 8192 0 Y 0 31 8 @@ -2014,7 +2014,7 @@ def @arg13 253 8192 10 Y 128 31 63 def @arg14 253 8192 19 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 8 Y 128 31 63 -def @arg17 253 20 4 Y 128 0 63 +def @arg17 253 20 4 Y 160 0 63 def @arg18 253 20 1 Y 128 0 63 def @arg19 253 20 1 Y 128 0 63 def @arg20 253 8192 1 Y 0 31 8 @@ -2054,7 +2054,7 @@ def @arg13 253 8192 0 Y 128 31 63 def @arg14 253 8192 0 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 0 Y 128 31 63 -def @arg17 253 20 0 Y 128 0 63 +def @arg17 253 20 0 Y 160 0 63 def @arg18 253 20 0 Y 128 0 63 def @arg19 253 20 0 Y 128 0 63 def @arg20 253 8192 0 Y 0 31 8 @@ -2102,7 +2102,7 @@ def @arg13 253 8192 10 Y 128 31 63 def @arg14 253 8192 19 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 8 Y 128 31 63 -def @arg17 253 20 4 Y 128 0 63 +def @arg17 253 20 4 Y 160 0 63 def @arg18 253 20 1 Y 128 0 63 def @arg19 253 20 1 Y 128 0 63 def @arg20 253 8192 1 Y 0 31 8 @@ -2146,7 +2146,7 @@ def @arg13 253 8192 0 Y 128 31 63 def @arg14 253 8192 0 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 0 Y 128 31 63 -def @arg17 253 20 0 Y 128 0 63 +def @arg17 253 20 0 Y 160 0 63 def @arg18 253 20 0 Y 128 0 63 def @arg19 253 20 0 Y 128 0 63 def @arg20 253 8192 0 Y 0 31 8 @@ -2192,7 +2192,7 @@ def @arg13 253 8192 10 Y 128 31 63 def @arg14 253 8192 19 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 8 Y 128 31 63 -def @arg17 253 20 4 Y 128 0 63 +def @arg17 253 20 4 Y 160 0 63 def @arg18 253 20 1 Y 128 0 63 def @arg19 253 20 1 Y 128 0 63 def @arg20 253 8192 1 Y 0 31 8 @@ -2230,7 +2230,7 @@ def @arg13 253 8192 0 Y 128 31 63 def @arg14 253 8192 0 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 0 Y 128 31 63 -def @arg17 253 20 0 Y 128 0 63 +def @arg17 253 20 0 Y 160 0 63 def @arg18 253 20 0 Y 128 0 63 def @arg19 253 20 0 Y 128 0 63 def @arg20 253 8192 0 Y 0 31 8 diff --git a/mysql-test/r/ps_5merge.result b/mysql-test/r/ps_5merge.result index 18982db937a..34ade911e78 100644 --- a/mysql-test/r/ps_5merge.result +++ b/mysql-test/r/ps_5merge.result @@ -826,20 +826,20 @@ a b 2 two 3 three 4 four -prepare stmt1 from ' SELECT a as ccc from t1 where a+1= - (SELECT 1+ccc from t1 where ccc+1=a+1 and a=1) '; +prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1= + (SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) '; execute stmt1 ; ccc 1 deallocate prepare stmt1 ; -prepare stmt1 from ' SELECT a as ccc from t1 where a+1= - (SELECT 1+ccc from t1 where ccc+1=a+1 and a=1) '; +prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1= + (SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) '; execute stmt1 ; ccc 1 deallocate prepare stmt1 ; -prepare stmt1 from ' SELECT a as ccc from t1 where a+1= - (SELECT 1+ccc from t1 where ccc+1=a+1 and a=1) '; +prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1= + (SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) '; execute stmt1 ; ccc 1 @@ -1853,7 +1853,7 @@ def @arg13 253 8192 10 Y 128 31 63 def @arg14 253 8192 19 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 8 Y 128 31 63 -def @arg17 253 20 4 Y 128 0 63 +def @arg17 253 20 4 Y 160 0 63 def @arg18 253 20 1 Y 128 0 63 def @arg19 253 20 1 Y 128 0 63 def @arg20 253 8192 1 Y 0 31 8 @@ -1900,7 +1900,7 @@ def @arg13 253 8192 0 Y 128 31 63 def @arg14 253 8192 0 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 0 Y 128 31 63 -def @arg17 253 20 0 Y 128 0 63 +def @arg17 253 20 0 Y 160 0 63 def @arg18 253 20 0 Y 128 0 63 def @arg19 253 20 0 Y 128 0 63 def @arg20 253 8192 0 Y 0 31 8 @@ -1950,7 +1950,7 @@ def @arg13 253 8192 10 Y 128 31 63 def @arg14 253 8192 19 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 8 Y 128 31 63 -def @arg17 253 20 4 Y 128 0 63 +def @arg17 253 20 4 Y 160 0 63 def @arg18 253 20 1 Y 128 0 63 def @arg19 253 20 1 Y 128 0 63 def @arg20 253 8192 1 Y 0 31 8 @@ -1990,7 +1990,7 @@ def @arg13 253 8192 0 Y 128 31 63 def @arg14 253 8192 0 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 0 Y 128 31 63 -def @arg17 253 20 0 Y 128 0 63 +def @arg17 253 20 0 Y 160 0 63 def @arg18 253 20 0 Y 128 0 63 def @arg19 253 20 0 Y 128 0 63 def @arg20 253 8192 0 Y 0 31 8 @@ -2038,7 +2038,7 @@ def @arg13 253 8192 10 Y 128 31 63 def @arg14 253 8192 19 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 8 Y 128 31 63 -def @arg17 253 20 4 Y 128 0 63 +def @arg17 253 20 4 Y 160 0 63 def @arg18 253 20 1 Y 128 0 63 def @arg19 253 20 1 Y 128 0 63 def @arg20 253 8192 1 Y 0 31 8 @@ -2082,7 +2082,7 @@ def @arg13 253 8192 0 Y 128 31 63 def @arg14 253 8192 0 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 0 Y 128 31 63 -def @arg17 253 20 0 Y 128 0 63 +def @arg17 253 20 0 Y 160 0 63 def @arg18 253 20 0 Y 128 0 63 def @arg19 253 20 0 Y 128 0 63 def @arg20 253 8192 0 Y 0 31 8 @@ -2128,7 +2128,7 @@ def @arg13 253 8192 10 Y 128 31 63 def @arg14 253 8192 19 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 8 Y 128 31 63 -def @arg17 253 20 4 Y 128 0 63 +def @arg17 253 20 4 Y 160 0 63 def @arg18 253 20 1 Y 128 0 63 def @arg19 253 20 1 Y 128 0 63 def @arg20 253 8192 1 Y 0 31 8 @@ -2166,7 +2166,7 @@ def @arg13 253 8192 0 Y 128 31 63 def @arg14 253 8192 0 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 0 Y 128 31 63 -def @arg17 253 20 0 Y 128 0 63 +def @arg17 253 20 0 Y 160 0 63 def @arg18 253 20 0 Y 128 0 63 def @arg19 253 20 0 Y 128 0 63 def @arg20 253 8192 0 Y 0 31 8 @@ -3848,20 +3848,20 @@ a b 2 two 3 three 4 four -prepare stmt1 from ' SELECT a as ccc from t1 where a+1= - (SELECT 1+ccc from t1 where ccc+1=a+1 and a=1) '; +prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1= + (SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) '; execute stmt1 ; ccc 1 deallocate prepare stmt1 ; -prepare stmt1 from ' SELECT a as ccc from t1 where a+1= - (SELECT 1+ccc from t1 where ccc+1=a+1 and a=1) '; +prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1= + (SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) '; execute stmt1 ; ccc 1 deallocate prepare stmt1 ; -prepare stmt1 from ' SELECT a as ccc from t1 where a+1= - (SELECT 1+ccc from t1 where ccc+1=a+1 and a=1) '; +prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1= + (SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) '; execute stmt1 ; ccc 1 @@ -4875,7 +4875,7 @@ def @arg13 253 8192 10 Y 128 31 63 def @arg14 253 8192 19 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 8 Y 128 31 63 -def @arg17 253 20 4 Y 128 0 63 +def @arg17 253 20 4 Y 160 0 63 def @arg18 253 20 1 Y 128 0 63 def @arg19 253 20 1 Y 128 0 63 def @arg20 253 8192 1 Y 0 31 8 @@ -4922,7 +4922,7 @@ def @arg13 253 8192 0 Y 128 31 63 def @arg14 253 8192 0 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 0 Y 128 31 63 -def @arg17 253 20 0 Y 128 0 63 +def @arg17 253 20 0 Y 160 0 63 def @arg18 253 20 0 Y 128 0 63 def @arg19 253 20 0 Y 128 0 63 def @arg20 253 8192 0 Y 0 31 8 @@ -4972,7 +4972,7 @@ def @arg13 253 8192 10 Y 128 31 63 def @arg14 253 8192 19 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 8 Y 128 31 63 -def @arg17 253 20 4 Y 128 0 63 +def @arg17 253 20 4 Y 160 0 63 def @arg18 253 20 1 Y 128 0 63 def @arg19 253 20 1 Y 128 0 63 def @arg20 253 8192 1 Y 0 31 8 @@ -5012,7 +5012,7 @@ def @arg13 253 8192 0 Y 128 31 63 def @arg14 253 8192 0 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 0 Y 128 31 63 -def @arg17 253 20 0 Y 128 0 63 +def @arg17 253 20 0 Y 160 0 63 def @arg18 253 20 0 Y 128 0 63 def @arg19 253 20 0 Y 128 0 63 def @arg20 253 8192 0 Y 0 31 8 @@ -5060,7 +5060,7 @@ def @arg13 253 8192 10 Y 128 31 63 def @arg14 253 8192 19 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 8 Y 128 31 63 -def @arg17 253 20 4 Y 128 0 63 +def @arg17 253 20 4 Y 160 0 63 def @arg18 253 20 1 Y 128 0 63 def @arg19 253 20 1 Y 128 0 63 def @arg20 253 8192 1 Y 0 31 8 @@ -5104,7 +5104,7 @@ def @arg13 253 8192 0 Y 128 31 63 def @arg14 253 8192 0 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 0 Y 128 31 63 -def @arg17 253 20 0 Y 128 0 63 +def @arg17 253 20 0 Y 160 0 63 def @arg18 253 20 0 Y 128 0 63 def @arg19 253 20 0 Y 128 0 63 def @arg20 253 8192 0 Y 0 31 8 @@ -5150,7 +5150,7 @@ def @arg13 253 8192 10 Y 128 31 63 def @arg14 253 8192 19 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 8 Y 128 31 63 -def @arg17 253 20 4 Y 128 0 63 +def @arg17 253 20 4 Y 160 0 63 def @arg18 253 20 1 Y 128 0 63 def @arg19 253 20 1 Y 128 0 63 def @arg20 253 8192 1 Y 0 31 8 @@ -5188,7 +5188,7 @@ def @arg13 253 8192 0 Y 128 31 63 def @arg14 253 8192 0 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 0 Y 128 31 63 -def @arg17 253 20 0 Y 128 0 63 +def @arg17 253 20 0 Y 160 0 63 def @arg18 253 20 0 Y 128 0 63 def @arg19 253 20 0 Y 128 0 63 def @arg20 253 8192 0 Y 0 31 8 diff --git a/mysql-test/r/ps_6bdb.result b/mysql-test/r/ps_6bdb.result index 0e4086bc202..a4205ac134e 100644 --- a/mysql-test/r/ps_6bdb.result +++ b/mysql-test/r/ps_6bdb.result @@ -783,20 +783,20 @@ a b 2 two 3 three 4 four -prepare stmt1 from ' SELECT a as ccc from t1 where a+1= - (SELECT 1+ccc from t1 where ccc+1=a+1 and a=1) '; +prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1= + (SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) '; execute stmt1 ; ccc 1 deallocate prepare stmt1 ; -prepare stmt1 from ' SELECT a as ccc from t1 where a+1= - (SELECT 1+ccc from t1 where ccc+1=a+1 and a=1) '; +prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1= + (SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) '; execute stmt1 ; ccc 1 deallocate prepare stmt1 ; -prepare stmt1 from ' SELECT a as ccc from t1 where a+1= - (SELECT 1+ccc from t1 where ccc+1=a+1 and a=1) '; +prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1= + (SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) '; execute stmt1 ; ccc 1 @@ -1916,7 +1916,7 @@ def @arg13 253 8192 10 Y 128 31 63 def @arg14 253 8192 19 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 8 Y 128 31 63 -def @arg17 253 20 4 Y 128 0 63 +def @arg17 253 20 4 Y 160 0 63 def @arg18 253 20 1 Y 128 0 63 def @arg19 253 20 1 Y 128 0 63 def @arg20 253 8192 1 Y 0 31 8 @@ -1963,7 +1963,7 @@ def @arg13 253 8192 0 Y 128 31 63 def @arg14 253 8192 0 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 0 Y 128 31 63 -def @arg17 253 20 0 Y 128 0 63 +def @arg17 253 20 0 Y 160 0 63 def @arg18 253 20 0 Y 128 0 63 def @arg19 253 20 0 Y 128 0 63 def @arg20 253 8192 0 Y 0 31 8 @@ -2013,7 +2013,7 @@ def @arg13 253 8192 10 Y 128 31 63 def @arg14 253 8192 19 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 8 Y 128 31 63 -def @arg17 253 20 4 Y 128 0 63 +def @arg17 253 20 4 Y 160 0 63 def @arg18 253 20 1 Y 128 0 63 def @arg19 253 20 1 Y 128 0 63 def @arg20 253 8192 1 Y 0 31 8 @@ -2053,7 +2053,7 @@ def @arg13 253 8192 0 Y 128 31 63 def @arg14 253 8192 0 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 0 Y 128 31 63 -def @arg17 253 20 0 Y 128 0 63 +def @arg17 253 20 0 Y 160 0 63 def @arg18 253 20 0 Y 128 0 63 def @arg19 253 20 0 Y 128 0 63 def @arg20 253 8192 0 Y 0 31 8 @@ -2101,7 +2101,7 @@ def @arg13 253 8192 10 Y 128 31 63 def @arg14 253 8192 19 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 8 Y 128 31 63 -def @arg17 253 20 4 Y 128 0 63 +def @arg17 253 20 4 Y 160 0 63 def @arg18 253 20 1 Y 128 0 63 def @arg19 253 20 1 Y 128 0 63 def @arg20 253 8192 1 Y 0 31 8 @@ -2145,7 +2145,7 @@ def @arg13 253 8192 0 Y 128 31 63 def @arg14 253 8192 0 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 0 Y 128 31 63 -def @arg17 253 20 0 Y 128 0 63 +def @arg17 253 20 0 Y 160 0 63 def @arg18 253 20 0 Y 128 0 63 def @arg19 253 20 0 Y 128 0 63 def @arg20 253 8192 0 Y 0 31 8 @@ -2191,7 +2191,7 @@ def @arg13 253 8192 10 Y 128 31 63 def @arg14 253 8192 19 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 8 Y 128 31 63 -def @arg17 253 20 4 Y 128 0 63 +def @arg17 253 20 4 Y 160 0 63 def @arg18 253 20 1 Y 128 0 63 def @arg19 253 20 1 Y 128 0 63 def @arg20 253 8192 1 Y 0 31 8 @@ -2229,7 +2229,7 @@ def @arg13 253 8192 0 Y 128 31 63 def @arg14 253 8192 0 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 0 Y 128 31 63 -def @arg17 253 20 0 Y 128 0 63 +def @arg17 253 20 0 Y 160 0 63 def @arg18 253 20 0 Y 128 0 63 def @arg19 253 20 0 Y 128 0 63 def @arg20 253 8192 0 Y 0 31 8 diff --git a/mysql-test/r/ps_7ndb.result b/mysql-test/r/ps_7ndb.result index 7a20fb3146d..8eec789d061 100644 --- a/mysql-test/r/ps_7ndb.result +++ b/mysql-test/r/ps_7ndb.result @@ -783,20 +783,20 @@ a b 2 two 3 three 4 four -prepare stmt1 from ' SELECT a as ccc from t1 where a+1= - (SELECT 1+ccc from t1 where ccc+1=a+1 and a=1) '; +prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1= + (SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) '; execute stmt1 ; ccc 1 deallocate prepare stmt1 ; -prepare stmt1 from ' SELECT a as ccc from t1 where a+1= - (SELECT 1+ccc from t1 where ccc+1=a+1 and a=1) '; +prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1= + (SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) '; execute stmt1 ; ccc 1 deallocate prepare stmt1 ; -prepare stmt1 from ' SELECT a as ccc from t1 where a+1= - (SELECT 1+ccc from t1 where ccc+1=a+1 and a=1) '; +prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1= + (SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) '; execute stmt1 ; ccc 1 @@ -1916,7 +1916,7 @@ def @arg13 253 8192 10 Y 128 31 63 def @arg14 253 8192 19 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 8 Y 128 31 63 -def @arg17 253 20 4 Y 128 0 63 +def @arg17 253 20 4 Y 160 0 63 def @arg18 253 20 1 Y 128 0 63 def @arg19 253 20 1 Y 128 0 63 def @arg20 253 8192 1 Y 0 31 8 @@ -1963,7 +1963,7 @@ def @arg13 253 8192 0 Y 128 31 63 def @arg14 253 8192 0 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 0 Y 128 31 63 -def @arg17 253 20 0 Y 128 0 63 +def @arg17 253 20 0 Y 160 0 63 def @arg18 253 20 0 Y 128 0 63 def @arg19 253 20 0 Y 128 0 63 def @arg20 253 8192 0 Y 0 31 8 @@ -2013,7 +2013,7 @@ def @arg13 253 8192 10 Y 128 31 63 def @arg14 253 8192 19 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 8 Y 128 31 63 -def @arg17 253 20 4 Y 128 0 63 +def @arg17 253 20 4 Y 160 0 63 def @arg18 253 20 1 Y 128 0 63 def @arg19 253 20 1 Y 128 0 63 def @arg20 253 8192 1 Y 0 31 8 @@ -2053,7 +2053,7 @@ def @arg13 253 8192 0 Y 128 31 63 def @arg14 253 8192 0 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 0 Y 128 31 63 -def @arg17 253 20 0 Y 128 0 63 +def @arg17 253 20 0 Y 160 0 63 def @arg18 253 20 0 Y 128 0 63 def @arg19 253 20 0 Y 128 0 63 def @arg20 253 8192 0 Y 0 31 8 @@ -2101,7 +2101,7 @@ def @arg13 253 8192 10 Y 128 31 63 def @arg14 253 8192 19 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 8 Y 128 31 63 -def @arg17 253 20 4 Y 128 0 63 +def @arg17 253 20 4 Y 160 0 63 def @arg18 253 20 1 Y 128 0 63 def @arg19 253 20 1 Y 128 0 63 def @arg20 253 8192 1 Y 0 31 8 @@ -2145,7 +2145,7 @@ def @arg13 253 8192 0 Y 128 31 63 def @arg14 253 8192 0 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 0 Y 128 31 63 -def @arg17 253 20 0 Y 128 0 63 +def @arg17 253 20 0 Y 160 0 63 def @arg18 253 20 0 Y 128 0 63 def @arg19 253 20 0 Y 128 0 63 def @arg20 253 8192 0 Y 0 31 8 @@ -2191,7 +2191,7 @@ def @arg13 253 8192 10 Y 128 31 63 def @arg14 253 8192 19 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 8 Y 128 31 63 -def @arg17 253 20 4 Y 128 0 63 +def @arg17 253 20 4 Y 160 0 63 def @arg18 253 20 1 Y 128 0 63 def @arg19 253 20 1 Y 128 0 63 def @arg20 253 8192 1 Y 0 31 8 @@ -2229,7 +2229,7 @@ def @arg13 253 8192 0 Y 128 31 63 def @arg14 253 8192 0 Y 128 31 63 def @arg15 253 8192 19 Y 128 31 63 def @arg16 253 8192 0 Y 128 31 63 -def @arg17 253 20 0 Y 128 0 63 +def @arg17 253 20 0 Y 160 0 63 def @arg18 253 20 0 Y 128 0 63 def @arg19 253 20 0 Y 128 0 63 def @arg20 253 8192 0 Y 0 31 8 diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index 58f8d077ac5..e0084b53320 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -1135,3 +1135,21 @@ item started price A1 2005-11-01 08:00:00 1000.000 A1 2005-11-15 00:00:00 2000.000 DROP TABLE t1; + +BUG#32198 "Comparison of DATE with DATETIME still not using indexes correctly" + +CREATE TABLE t1 ( +id int(11) NOT NULL auto_increment, +dateval date default NULL, +PRIMARY KEY (id), +KEY dateval (dateval) +) AUTO_INCREMENT=173; +INSERT INTO t1 VALUES +(1,'2007-01-01'),(2,'2007-01-02'),(3,'2007-01-03'),(4,'2007-01-04'), +(5,'2007-01-05'),(6,'2007-01-06'),(7,'2007-01-07'),(8,'2007-01-08'), +(9,'2007-01-09'),(10,'2007-01-10'),(11,'2007-01-11'); +This must use range access: +explain select * from t1 where dateval >= '2007-01-01 00:00:00' and dateval <= '2007-01-02 23:59:59'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range dateval dateval 4 NULL 2 Using where +drop table t1; diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index a1ba58a536c..3ca84bcf34b 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -4322,4 +4322,10 @@ c3 DROP TABLE t1; DROP TABLE t2; +CREATE TABLE t1 (c1 BIGINT NOT NULL); +INSERT INTO t1 (c1) VALUES (1); +SELECT * FROM t1 WHERE c1 > NULL + 1; +c1 +DROP TABLE t1; + End of 5.0 tests diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 1c450269809..f6487ae3ddf 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -77,10 +77,9 @@ ERROR HY000: Incorrect usage of PROCEDURE and subquery SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE((SELECT 1)); ERROR HY000: Incorrect parameters to procedure 'ANALYSE' SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL; -a +ERROR 42S22: Unknown column 'a' in 'field list' SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NOT NULL; -a -1 +ERROR 42S22: Unknown column 'a' in 'field list' SELECT (SELECT 1,2,3) = ROW(1,2,3); (SELECT 1,2,3) = ROW(1,2,3) 1 @@ -1307,7 +1306,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 Using where; Using index 2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using where Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY where (`test`.`t1`.`b` <> 30)))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY where ((`test`.`t1`.`b` <> 30) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))) select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); a 2 @@ -1315,8 +1314,8 @@ a explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 Using where; Using index -2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 func 1 Using where -2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 Using where; Using index +2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 func 1 +2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 Using index Warnings: Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))) drop table t1, t2, t3; @@ -1334,9 +1333,9 @@ a explain extended select * from t2 where t2.a in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index NULL a 5 NULL 4 Using where; Using index -2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 Using index +2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 Using index; Using where Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a where (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))) select * from t2 where t2.a in (select a from t1 where t1.b <> 30); a 2 @@ -1346,7 +1345,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index NULL a 5 NULL 4 Using where; Using index 2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 Using index; Using where Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a where (`test`.`t1`.`b` <> 30)))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a where ((`test`.`t1`.`b` <> 30) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))) select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); a 2 @@ -1373,7 +1372,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index NULL a 5 NULL 4 Using where; Using index 2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 Using index; Using where Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a where (`test`.`t1`.`b` <> 30)))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a where ((`test`.`t1`.`b` <> 30) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))) drop table t1, t2, t3; create table t1 (a int, b int); create table t2 (a int, b int); @@ -1737,7 +1736,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where 2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index; Using where Warnings: -Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `test`.`t1` where (not(<in_optimizer>(`test`.`t1`.`id`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`id`) in t1 on PRIMARY where (`test`.`t1`.`id` < 8)))))) +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `test`.`t1` where (not(<in_optimizer>(`test`.`t1`.`id`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`id`) in t1 on PRIMARY where ((`test`.`t1`.`id` < 8) and (<cache>(`test`.`t1`.`id`) = `test`.`t1`.`id`))))))) explain extended select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY tt ALL NULL NULL NULL NULL 12 Using where @@ -2304,24 +2303,27 @@ drop table t1,t2; CREATE TABLE t1 ( a int, b int ); CREATE TABLE t2 ( c int, d int ); INSERT INTO t1 VALUES (1,2), (2,3), (3,4); -SELECT a AS abc, b FROM t1 WHERE b = (SELECT MIN(b) FROM t1 WHERE a=abc); +SELECT a AS abc, b FROM t1 outr WHERE b = +(SELECT MIN(b) FROM t1 WHERE a=outr.a); abc b 1 2 2 3 3 4 -INSERT INTO t2 SELECT a AS abc, b FROM t1 WHERE b = (SELECT MIN(b) FROM t1 WHERE a=abc); +INSERT INTO t2 SELECT a AS abc, b FROM t1 outr WHERE b = +(SELECT MIN(b) FROM t1 WHERE a=outr.a); select * from t2; c d 1 2 2 3 3 4 -CREATE TABLE t3 SELECT a AS abc, b FROM t1 WHERE b = (SELECT MIN(b) FROM t1 WHERE a=abc); +CREATE TABLE t3 SELECT a AS abc, b FROM t1 outr WHERE b = +(SELECT MIN(b) FROM t1 WHERE a=outr.a); select * from t3; abc b 1 2 2 3 3 4 -prepare stmt1 from "INSERT INTO t2 SELECT a AS abc, b FROM t1 WHERE b = (SELECT MIN(b) FROM t1 WHERE a=abc);"; +prepare stmt1 from "INSERT INTO t2 SELECT a AS abc, b FROM t1 outr WHERE b = (SELECT MIN(b) FROM t1 WHERE a=outr.a);"; execute stmt1; deallocate prepare stmt1; select * from t2; @@ -2333,7 +2335,7 @@ c d 2 3 3 4 drop table t3; -prepare stmt1 from "CREATE TABLE t3 SELECT a AS abc, b FROM t1 WHERE b = (SELECT MIN(b) FROM t1 WHERE a=abc);"; +prepare stmt1 from "CREATE TABLE t3 SELECT a AS abc, b FROM t1 outr WHERE b = (SELECT MIN(b) FROM t1 WHERE a=outr.a);"; execute stmt1; select * from t3; abc b @@ -2515,7 +2517,9 @@ INSERT INTO t1 VALUES ('ASM','American Samoa','Oceania','Polynesia',199.00,0,680 INSERT INTO t1 VALUES ('ATF','French Southern territories','Antarctica','Antarctica',7780.00,0,0,NULL,0.00,NULL,'Terres australes françaises','Nonmetropolitan Territory of France','Jacques Chirac',NULL,'TF'); INSERT INTO t1 VALUES ('UMI','United States Minor Outlying Islands','Oceania','Micronesia/Caribbean',16.00,0,0,NULL,0.00,NULL,'United States Minor Outlying Islands','Dependent Territory of the US','George W. Bush',NULL,'UM'); /*!40000 ALTER TABLE t1 ENABLE KEYS */; -SELECT DISTINCT Continent AS c FROM t1 WHERE Code <> SOME ( SELECT Code FROM t1 WHERE Continent = c AND Population < 200); +SELECT DISTINCT Continent AS c FROM t1 outr WHERE +Code <> SOME ( SELECT Code FROM t1 WHERE Continent = outr.Continent AND +Population < 200); c Oceania drop table t1; @@ -3558,22 +3562,19 @@ SELECT sql_no_cache * FROM t1 WHERE NOT EXISTS (SELECT i FROM t1) UNION (SELECT i FROM t1) ); -i +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION +(SELECT i FROM t1) +)' at line 3 SELECT * FROM t1 WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1))); -i +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION (SELECT i FROM t1)))' at line 2 explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12)) from t1; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'union (select t12.i from t1 t12)) from t1' at line 1 explain select * from t1 where not exists ((select t11.i from t1 t11) union (select t12.i from t1 t12)); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found -2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used -3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table -4 UNION t12 system NULL NULL NULL NULL 0 const row not found -NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'union (select t12.i from t1 t12))' at line 2 DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(250), b INT auto_increment, PRIMARY KEY (b)); insert into t1 (a) values (FLOOR(rand() * 100)); @@ -3666,6 +3667,8 @@ CREATE TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b)); CREATE TABLE t2 (x int auto_increment, y int, z int, PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b)); SET SESSION sort_buffer_size = 32 * 1024; +Warnings: +Warning 1292 Truncated incorrect sort_buffer_size value: '32768' SELECT SQL_NO_CACHE COUNT(*) FROM (SELECT a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c FROM t1) t; @@ -4101,6 +4104,8 @@ INSERT INTO `t1` VALUES ('asdf','2007-02-08 01:11:26'); INSERT INTO `t2` VALUES ('abcdefghijk'); INSERT INTO `t2` VALUES ('asdf'); SET session sort_buffer_size=8192; +Warnings: +Warning 1292 Truncated incorrect sort_buffer_size value: '8192' SELECT (SELECT 1 FROM t1 WHERE t1.a=t2.a ORDER BY t1.b LIMIT 1) AS d1 FROM t2; d1 1 @@ -4139,6 +4144,107 @@ SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=1) FROM t1; (SELECT SUM(t1.a) FROM t2 WHERE a=1) 3 DROP TABLE t1,t2; +create table t1(a int,b int,key(a),key(b)); +insert into t1(a,b) values (1,2),(2,1),(2,3),(3,4),(5,4),(5,5), +(6,7),(7,4),(5,3); +select sum(a),a from t1 where a> ( +select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( +select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( +select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( +select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( +select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( +select sum(a) from t1 where a> ( select sum(a) from t1 +)group by b limit 1)group by b limit 1 +)group by b limit 1)group by b limit 1)group by b limit 1 +)group by b limit 1)group by b limit 1)group by b limit 1 +)group by b limit 1)group by b limit 1)group by b limit 1) +group by a; +sum(a) a +select sum(a),a from t1 where a> ( +select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( +select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( +select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( +select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( +select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( +select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( +select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( +select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( +select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( +select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( +select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( +select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( +select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( +select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( +select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( +select sum(a) from t1 +)group by b limit 1)group by b limit 1)group by b limit 1 +)group by b limit 1)group by b limit 1)group by b limit 1 +)group by b limit 1)group by b limit 1)group by b limit 1 +)group by b limit 1)group by b limit 1)group by b limit 1 +)group by b limit 1)group by b limit 1)group by b limit 1 +)group by b limit 1)group by b limit 1)group by b limit 1 +)group by b limit 1)group by b limit 1)group by b limit 1 +)group by b limit 1)group by b limit 1)group by b limit 1 +)group by b limit 1)group by b limit 1)group by b limit 1 +)group by b limit 1)group by b limit 1)group by b limit 1) +group by a; +ERROR HY000: Thread stack overrun detected +explain select sum(a),a from t1 where a> ( +select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( +select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( +select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( +select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( +select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( +select sum(a) from t1 where a> ( select sum(a) from t1 +)group by b limit 1)group by b limit 1 +)group by b limit 1)group by b limit 1)group by b limit 1 +)group by b limit 1)group by b limit 1)group by b limit 1 +)group by b limit 1)group by b limit 1)group by b limit 1) +group by a; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index a a 5 NULL 9 Using where; Using index +2 SUBQUERY t1 range a a 5 NULL 9 Using where; Using temporary; Using filesort +3 SUBQUERY t1 range a a 5 NULL 9 Using where; Using temporary; Using filesort +4 SUBQUERY t1 range a a 5 NULL 9 Using where; Using temporary; Using filesort +5 SUBQUERY t1 range a a 5 NULL 9 Using where; Using temporary; Using filesort +6 SUBQUERY t1 range a a 5 NULL 9 Using where; Using temporary; Using filesort +7 SUBQUERY t1 range a a 5 NULL 9 Using where; Using temporary; Using filesort +8 SUBQUERY t1 range a a 5 NULL 9 Using where; Using temporary; Using filesort +9 SUBQUERY t1 range a a 5 NULL 9 Using where; Using temporary; Using filesort +10 SUBQUERY t1 range a a 5 NULL 9 Using where; Using temporary; Using filesort +11 SUBQUERY t1 range a a 5 NULL 9 Using where; Using temporary; Using filesort +12 SUBQUERY t1 range a a 5 NULL 1 Using where; Using temporary; Using filesort +13 SUBQUERY t1 index NULL a 5 NULL 9 Using index +explain select sum(a),a from t1 where a> ( +select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( +select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( +select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( +select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( +select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( +select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( +select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( +select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( +select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( +select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( +select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( +select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( +select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( +select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( +select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( +select sum(a) from t1 +)group by b limit 1)group by b limit 1)group by b limit 1 +)group by b limit 1)group by b limit 1)group by b limit 1 +)group by b limit 1)group by b limit 1)group by b limit 1 +)group by b limit 1)group by b limit 1)group by b limit 1 +)group by b limit 1)group by b limit 1)group by b limit 1 +)group by b limit 1)group by b limit 1)group by b limit 1 +)group by b limit 1)group by b limit 1)group by b limit 1 +)group by b limit 1)group by b limit 1)group by b limit 1 +)group by b limit 1)group by b limit 1)group by b limit 1 +)group by b limit 1)group by b limit 1)group by b limit 1) +group by a; +ERROR HY000: Thread stack overrun detected +drop table t1; CREATE TABLE t1 (a1 INT, a2 INT); CREATE TABLE t2 (b1 INT, b2 INT); INSERT INTO t1 VALUES (100, 200); @@ -4199,4 +4305,82 @@ LEFT(t1.a1,1) SELECT a2 FROM t2 WHERE t2.a2 IN (SELECT t1.a1 FROM t1,t3 WHERE t1.b1=t3.a3); a2 DROP TABLE t1, t2, t3; +CREATE TABLE t1 (a CHAR(1), b VARCHAR(10)); +INSERT INTO t1 VALUES ('a', 'aa'); +INSERT INTO t1 VALUES ('a', 'aaa'); +SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1); +a b +CREATE INDEX I1 ON t1 (a); +CREATE INDEX I2 ON t1 (b); +EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY t1 index_subquery I1 I1 2 func 2 Using index; Using where +SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1); +a b +CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10)); +INSERT INTO t2 SELECT * FROM t1; +CREATE INDEX I1 ON t2 (a); +CREATE INDEX I2 ON t2 (b); +EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY t2 index_subquery I1 I1 4 func 2 Using index; Using where +SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2); +a b +EXPLAIN +SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY t1 index_subquery I1 I1 2 func 2 Using index; Using where +SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500); +a b +DROP TABLE t1,t2; +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1,1), (1,2), (2,3), (2,4); +EXPLAIN +SELECT a AS out_a, MIN(b) FROM t1 +WHERE b > (SELECT MIN(b) FROM t1 WHERE a = out_a) +GROUP BY a; +ERROR 42S22: Unknown column 'out_a' in 'where clause' +SELECT a AS out_a, MIN(b) FROM t1 +WHERE b > (SELECT MIN(b) FROM t1 WHERE a = out_a) +GROUP BY a; +ERROR 42S22: Unknown column 'out_a' in 'where clause' +EXPLAIN +SELECT a AS out_a, MIN(b) FROM t1 t1_outer +WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a) +GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1_outer ALL NULL NULL NULL NULL 4 Using where; Using temporary; Using filesort +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 4 Using where +SELECT a AS out_a, MIN(b) FROM t1 t1_outer +WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a) +GROUP BY a; +out_a MIN(b) +1 2 +2 4 +DROP TABLE t1; +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (a INT); +INSERT INTO t1 VALUES (1),(2); +INSERT INTO t2 VALUES (1),(2); +SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a)); +2 +2 +2 +EXPLAIN EXTENDED +SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where +Warnings: +Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 +Note 1003 select 2 AS `2` from `test`.`t1` where exists(select 1 AS `1` from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)) +EXPLAIN EXTENDED +SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION +(SELECT 1 FROM t2 WHERE t1.a = t2.a)); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION +(SELECT 1 FROM t2 WHERE t1.a = t2.a))' at line 2 +DROP TABLE t1,t2; End of 5.0 tests. diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result index 098dacc8189..bdf00e4c307 100644 --- a/mysql-test/r/subselect3.result +++ b/mysql-test/r/subselect3.result @@ -757,6 +757,6 @@ a EXPLAIN SELECT a FROM t1 WHERE a NOT IN (SELECT a FROM t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where -2 DEPENDENT SUBQUERY t2 unique_subquery PRIMARY PRIMARY 4 func 1 Using index +2 DEPENDENT SUBQUERY t2 unique_subquery PRIMARY PRIMARY 4 func 1 Using index; Using where DROP TABLE t1; End of 5.0 tests diff --git a/mysql-test/r/type_bit.result b/mysql-test/r/type_bit.result index 4c1b80c2fd5..4c4fcb9a9ae 100644 --- a/mysql-test/r/type_bit.result +++ b/mysql-test/r/type_bit.result @@ -269,6 +269,8 @@ a+0 b+0 56 379 68 454 set @@max_length_for_sort_data=0; +Warnings: +Warning 1292 Truncated incorrect max_length_for_sort_data value: '0' select a+0, b+0 from t1 where a > 40 and a < 70 order by 2; a+0 b+0 57 135 @@ -672,4 +674,14 @@ COUNT(DISTINCT b,c) 2 2 DROP TABLE t2; +CREATE TABLE t1(a BIT(13), KEY(a)); +INSERT INTO t1(a) VALUES +(65535),(65525),(65535),(65535),(65535),(65535),(65535),(65535),(65535),(65535); +EXPLAIN SELECT 1 FROM t1 GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range NULL a 3 NULL 6 Using index for group-by +SELECT 1 FROM t1 GROUP BY a; +1 +1 +DROP TABLE t1; End of 5.0 tests diff --git a/mysql-test/r/type_bit_innodb.result b/mysql-test/r/type_bit_innodb.result index c4506231f27..d0cf15e59a5 100644 --- a/mysql-test/r/type_bit_innodb.result +++ b/mysql-test/r/type_bit_innodb.result @@ -269,6 +269,8 @@ a+0 b+0 56 379 68 454 set @@max_length_for_sort_data=0; +Warnings: +Warning 1292 Truncated incorrect max_length_for_sort_data value: '0' select a+0, b+0 from t1 where a > 40 and a < 70 order by 2; a+0 b+0 57 135 diff --git a/mysql-test/r/type_blob.result b/mysql-test/r/type_blob.result index c72ee005428..80d233ee16a 100644 --- a/mysql-test/r/type_blob.result +++ b/mysql-test/r/type_blob.result @@ -807,4 +807,18 @@ set @@sql_mode='TRADITIONAL'; create table t1 (a text default ''); ERROR 42000: BLOB/TEXT column 'a' can't have a default value set @@sql_mode=''; +CREATE TABLE t (c TEXT CHARSET ASCII); +INSERT INTO t (c) VALUES (REPEAT('1',65537)); +Warnings: +Warning 1265 Data truncated for column 'c' at row 1 +INSERT INTO t (c) VALUES (REPEAT('2',65536)); +Warnings: +Warning 1265 Data truncated for column 'c' at row 1 +INSERT INTO t (c) VALUES (REPEAT('3',65535)); +SELECT LENGTH(c), CHAR_LENGTH(c) FROM t; +LENGTH(c) CHAR_LENGTH(c) +65535 65535 +65535 65535 +65535 65535 +DROP TABLE t; End of 5.0 tests diff --git a/mysql-test/r/type_date.result b/mysql-test/r/type_date.result index bd2a43569dd..3cf1a166e7f 100644 --- a/mysql-test/r/type_date.result +++ b/mysql-test/r/type_date.result @@ -202,7 +202,6 @@ a Warnings: Warning 1292 Incorrect date value: '0000-00-00' for column 'a' at row 1 Warning 1292 Incorrect date value: '0000-00-00' for column 'a' at row 1 -Warning 1292 Incorrect date value: '0000-00-00' for column 'a' at row 1 SELECT * FROM t2 WHERE a = '0000-00-00'; a 0000-00-00 @@ -214,4 +213,42 @@ INSERT INTO t1 VALUES ('0000-00-00'); ERROR 22007: Incorrect date value: '0000-00-00' for column 'a' at row 1 SET SQL_MODE=DEFAULT; DROP TABLE t1,t2; +CREATE TABLE t1 (a DATE); +CREATE TABLE t2 (a DATE); +CREATE INDEX i ON t1 (a); +INSERT INTO t1 VALUES ('1000-00-00'),('1000-00-00'); +INSERT INTO t2 VALUES ('1000-00-00'),('1000-00-00'); +SELECT * FROM t1 WHERE a = '1000-00-00'; +a +1000-00-00 +1000-00-00 +SELECT * FROM t2 WHERE a = '1000-00-00'; +a +1000-00-00 +1000-00-00 +SET SQL_MODE=TRADITIONAL; +EXPLAIN SELECT * FROM t1 WHERE a = '1000-00-00'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref i i 4 const 1 Using where; Using index +Warnings: +Warning 1292 Incorrect date value: '1000-00-00' for column 'a' at row 1 +Warning 1292 Incorrect date value: '1000-00-00' for column 'a' at row 1 +SELECT * FROM t1 WHERE a = '1000-00-00'; +a +1000-00-00 +1000-00-00 +Warnings: +Warning 1292 Incorrect date value: '1000-00-00' for column 'a' at row 1 +Warning 1292 Incorrect date value: '1000-00-00' for column 'a' at row 1 +SELECT * FROM t2 WHERE a = '1000-00-00'; +a +1000-00-00 +1000-00-00 +Warnings: +Warning 1292 Incorrect date value: '1000-00-00' for column 'a' at row 1 +Warning 1292 Incorrect date value: '1000-00-00' for column 'a' at row 1 +INSERT INTO t1 VALUES ('1000-00-00'); +ERROR 22007: Incorrect date value: '1000-00-00' for column 'a' at row 1 +SET SQL_MODE=DEFAULT; +DROP TABLE t1,t2; End of 5.0 tests diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result index 73edfb75ff6..3814e2f8348 100644 --- a/mysql-test/r/type_datetime.result +++ b/mysql-test/r/type_datetime.result @@ -505,4 +505,59 @@ select sum(a) from t1 group by convert(a, datetime); sum(a) NULL drop table t1; +create table t1 (id int(10) not null, cur_date datetime not null); +create table t2 (id int(10) not null, cur_date date not null); +insert into t1 (id, cur_date) values (1, '2007-04-25 18:30:22'); +insert into t2 (id, cur_date) values (1, '2007-04-25'); +explain extended +select * from t1 +where id in (select id from t1 as x1 where (t1.cur_date is null)); +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 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +Warnings: +Note 1276 Field or reference 'test.t1.cur_date' of SELECT #2 was resolved in SELECT #1 +Note 1003 select '1' AS `id`,'2007-04-25 18:30:22' AS `cur_date` from `test`.`t1` where <in_optimizer>('1',<exists>(select 1 AS `Not_used` from `test`.`t1` `x1` where 0)) +select * from t1 +where id in (select id from t1 as x1 where (t1.cur_date is null)); +id cur_date +explain extended +select * from t2 +where id in (select id from t2 as x1 where (t2.cur_date is null)); +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 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +Warnings: +Note 1276 Field or reference 'test.t2.cur_date' of SELECT #2 was resolved in SELECT #1 +Note 1003 select '1' AS `id`,'2007-04-25' AS `cur_date` from `test`.`t2` where <in_optimizer>('1',<exists>(select 1 AS `Not_used` from `test`.`t2` `x1` where 0)) +select * from t2 +where id in (select id from t2 as x1 where (t2.cur_date is null)); +id cur_date +insert into t1 (id, cur_date) values (2, '2007-04-26 18:30:22'); +insert into t2 (id, cur_date) values (2, '2007-04-26'); +explain extended +select * from t1 +where id in (select id from t1 as x1 where (t1.cur_date is null)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY x1 ALL NULL NULL NULL NULL 2 Using where +Warnings: +Note 1276 Field or reference 'test.t1.cur_date' of SELECT #2 was resolved in SELECT #1 +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`cur_date` AS `cur_date` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`id`,<exists>(select 1 AS `Not_used` from `test`.`t1` `x1` where ((`test`.`t1`.`cur_date` = 0) and (<cache>(`test`.`t1`.`id`) = `test`.`x1`.`id`)))) +select * from t1 +where id in (select id from t1 as x1 where (t1.cur_date is null)); +id cur_date +explain extended +select * from t2 +where id in (select id from t2 as x1 where (t2.cur_date is null)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY x1 ALL NULL NULL NULL NULL 2 Using where +Warnings: +Note 1276 Field or reference 'test.t2.cur_date' of SELECT #2 was resolved in SELECT #1 +Note 1003 select `test`.`t2`.`id` AS `id`,`test`.`t2`.`cur_date` AS `cur_date` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`id`,<exists>(select 1 AS `Not_used` from `test`.`t2` `x1` where ((`test`.`t2`.`cur_date` = 0) and (<cache>(`test`.`t2`.`id`) = `test`.`x1`.`id`)))) +select * from t2 +where id in (select id from t2 as x1 where (t2.cur_date is null)); +id cur_date +drop table t1,t2; End of 5.0 tests diff --git a/mysql-test/r/udf.result b/mysql-test/r/udf.result index e6797796ea0..a79be1c3189 100644 --- a/mysql-test/r/udf.result +++ b/mysql-test/r/udf.result @@ -327,4 +327,31 @@ DROP FUNCTION check_const_len; DROP PROCEDURE check_const_len_sp; DROP TRIGGER check_const_len_trigger; DROP TABLE const_len_bug; +CREATE FUNCTION sequence RETURNS INTEGER SONAME "UDF_EXAMPLE_LIB"; +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (a INT PRIMARY KEY); +INSERT INTO t1 VALUES (4),(3),(2),(1); +INSERT INTO t2 SELECT * FROM t1; +SELECT sequence() AS seq, a FROM t1 ORDER BY seq ASC; +seq a +1 4 +2 3 +3 2 +4 1 +SELECT sequence() AS seq, a FROM t1 ORDER BY seq DESC; +seq a +4 1 +3 2 +2 3 +1 4 +SELECT * FROM t1 WHERE a = sequence(); +a +SELECT * FROM t2 WHERE a = sequence(); +a +1 +2 +3 +4 +DROP FUNCTION sequence; +DROP TABLE t1,t2; End of 5.0 tests. diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result index 47cd96b90b7..9c360ef4ab3 100644 --- a/mysql-test/r/variables.result +++ b/mysql-test/r/variables.result @@ -214,6 +214,8 @@ net_read_timeout 600 net_retry_count 10 net_write_timeout 500 set net_buffer_length=1; +Warnings: +Warning 1292 Truncated incorrect net_buffer_length value: '1' show variables like 'net_buffer_length'; Variable_name Value net_buffer_length 1024 @@ -238,7 +240,7 @@ show variables like '%alloc%'; Variable_name Value query_alloc_block_size 8192 query_prealloc_size 8192 -range_alloc_block_size 2048 +range_alloc_block_size 4096 transaction_alloc_block_size 8192 transaction_prealloc_size 4096 set @@range_alloc_block_size=1024*16; @@ -263,7 +265,7 @@ show variables like '%alloc%'; Variable_name Value query_alloc_block_size 8192 query_prealloc_size 8192 -range_alloc_block_size 2048 +range_alloc_block_size 4096 transaction_alloc_block_size 8192 transaction_prealloc_size 4096 SELECT @@version LIKE 'non-existent'; @@ -321,6 +323,8 @@ select @@autocommit, @@big_tables; @@autocommit @@big_tables 1 1 set global binlog_cache_size=100; +Warnings: +Warning 1292 Truncated incorrect binlog_cache_size value: '100' set bulk_insert_buffer_size=100; set character set cp1251_koi8; set character set default; @@ -349,17 +353,27 @@ set global flush_time=100; set insert_id=1; set interactive_timeout=100; set join_buffer_size=100; +Warnings: +Warning 1292 Truncated incorrect join_buffer_size value: '100' set last_insert_id=1; set global local_infile=1; set long_query_time=100; set low_priority_updates=1; set max_allowed_packet=100; +Warnings: +Warning 1292 Truncated incorrect max_allowed_packet value: '100' set global max_binlog_cache_size=100; +Warnings: +Warning 1292 Truncated incorrect max_binlog_cache_size value: '100' set global max_binlog_size=100; +Warnings: +Warning 1292 Truncated incorrect max_binlog_size value: '100' set global max_connect_errors=100; set global max_connections=100; set global max_delayed_threads=100; set max_heap_table_size=100; +Warnings: +Warning 1292 Truncated incorrect max_heap_table_size value: '100' set max_join_size=100; set max_sort_length=100; set max_tmp_tables=100; @@ -370,17 +384,25 @@ select @@max_user_connections; set global max_write_lock_count=100; set myisam_sort_buffer_size=100; set net_buffer_length=100; +Warnings: +Warning 1292 Truncated incorrect net_buffer_length value: '100' set net_read_timeout=100; set net_write_timeout=100; set global query_cache_limit=100; set global query_cache_size=100; set global query_cache_type=demand; set read_buffer_size=100; +Warnings: +Warning 1292 Truncated incorrect read_buffer_size value: '100' set read_rnd_buffer_size=100; +Warnings: +Warning 1292 Truncated incorrect read_rnd_buffer_size value: '100' set global rpl_recovery_rank=100; set global server_id=100; set global slow_launch_time=100; set sort_buffer_size=100; +Warnings: +Warning 1292 Truncated incorrect sort_buffer_size value: '100' set @@max_sp_recursion_depth=10; select @@max_sp_recursion_depth; @@max_sp_recursion_depth @@ -420,6 +442,8 @@ set storage_engine=myisam; set global thread_cache_size=100; set timestamp=1, timestamp=default; set tmp_table_size=100; +Warnings: +Warning 1292 Truncated incorrect tmp_table_size value: '100' set tx_isolation="READ-COMMITTED"; set wait_timeout=100; set log_warnings=1; @@ -570,6 +594,8 @@ SHOW VARIABLES LIKE 'MYISAM_DATA_POINTER_SIZE'; Variable_name Value myisam_data_pointer_size 7 SET GLOBAL table_cache=-1; +Warnings: +Warning 1292 Truncated incorrect table_cache value: '0' SHOW VARIABLES LIKE 'table_cache'; Variable_name Value table_cache 1 diff --git a/mysql-test/t/delete.test b/mysql-test/t/delete.test index 602e30687c8..ec70587c9d1 100644 --- a/mysql-test/t/delete.test +++ b/mysql-test/t/delete.test @@ -235,50 +235,6 @@ DELETE FROM t1 ORDER BY (SELECT x); DROP TABLE t1; # -# Bug #30234: Unexpected behavior using DELETE with AS and USING -# ' -CREATE TABLE t1 ( - a INT -); - -CREATE TABLE t2 ( - a INT -); - -CREATE DATABASE db1; -CREATE TABLE db1.t1 ( - a INT -); -INSERT INTO db1.t1 (a) SELECT * FROM t1; - -CREATE DATABASE db2; -CREATE TABLE db2.t1 ( - a INT -); -INSERT INTO db2.t1 (a) SELECT * FROM t2; - ---error ER_PARSE_ERROR -DELETE FROM t1 alias USING t1, t2 alias WHERE t1.a = alias.a; -DELETE FROM alias USING t1, t2 alias WHERE t1.a = alias.a; -DELETE FROM t1, alias USING t1, t2 alias WHERE t1.a = alias.a; ---error ER_UNKNOWN_TABLE -DELETE FROM t1, t2 USING t1, t2 alias WHERE t1.a = alias.a; ---error ER_PARSE_ERROR -DELETE FROM db1.t1 alias USING db1.t1, db2.t1 alias WHERE db1.t1.a = alias.a; ---error ER_UNKNOWN_TABLE -DELETE FROM alias USING db1.t1, db2.t1 alias WHERE db1.t1.a = alias.a; -DELETE FROM db2.alias USING db1.t1, db2.t1 alias WHERE db1.t1.a = alias.a; -DELETE FROM t1 USING t1 WHERE a = 1; -SELECT * FROM t1; ---error ER_PARSE_ERROR -DELETE FROM t1 alias USING t1 alias WHERE a = 2; -SELECT * FROM t1; - -DROP TABLE t1, t2; -DROP DATABASE db1; -DROP DATABASE db2; - -# # Bug 31742: delete from ... order by function call that causes an error, # asserts server # diff --git a/mysql-test/t/explain.test b/mysql-test/t/explain.test index 04cf37f457a..c9ae8aceaf6 100644 --- a/mysql-test/t/explain.test +++ b/mysql-test/t/explain.test @@ -66,4 +66,32 @@ explain extended select * from t1 having 1; drop view v1; drop table t1; +# +# Bug #32241: memory corruption due to large index map in 'Range checked for +# each record' +# + +CREATE TABLE t1(c INT); +INSERT INTO t1 VALUES (),(); + +CREATE TABLE t2 (b INT, +KEY(b),KEY(b),KEY(b),KEY(b),KEY(b), +KEY(b),KEY(b),KEY(b),KEY(b),KEY(b), +KEY(b),KEY(b),KEY(b),KEY(b),KEY(b), +KEY(b),KEY(b),KEY(b),KEY(b),KEY(b), +KEY(b),KEY(b),KEY(b),KEY(b),KEY(b), +KEY(b),KEY(b),KEY(b),KEY(b),KEY(b), +KEY(b),KEY(b),KEY(b),KEY(b),KEY(b), +KEY(b),KEY(b),KEY(b),KEY(b),KEY(b)); + +INSERT INTO t2 VALUES (),(),(); + +# We only need to make sure that there is no buffer overrun and the index map +# is displayed correctly +--replace_column 1 X 2 X 3 X 4 X 5 X 6 X 7 X 8 X 9 X +EXPLAIN SELECT 1 FROM + (SELECT 1 FROM t2,t1 WHERE b < c GROUP BY 1 LIMIT 1) AS d2; +DROP TABLE t2; +DROP TABLE t1; + # End of 5.0 tests. diff --git a/mysql-test/t/federated.test b/mysql-test/t/federated.test index cc66a6ab4bc..d4f22650a32 100644 --- a/mysql-test/t/federated.test +++ b/mysql-test/t/federated.test @@ -1686,4 +1686,35 @@ insert into federated.t1 (a) values (1); select * from federated.t2; drop table federated.t1, federated.t2; +# +# Bug #32374 crash with filesort when selecting from federated table and view +# +connection slave; +create table t1 (a varchar(256)); +--disable_warnings +drop view if exists v1; +--enable_warnings +create view v1 as select a from t1; +--disable_query_log +let $n= 100; +while ($n) +{ + insert into t1 values (repeat('a',200)); + dec $n; +} +--enable_query_log + +connection master; +--replace_result $SLAVE_MYPORT SLAVE_PORT +eval create table t1 + (a varchar(256)) engine=federated + connection='mysql://root@127.0.0.1:$SLAVE_MYPORT/test/v1'; + +select 1 from t1 order by a; +drop table t1; +connection slave; +drop table t1; +drop view v1; + + source include/federated_cleanup.inc; diff --git a/mysql-test/t/func_misc.test b/mysql-test/t/func_misc.test index 844e60ab7ae..82db9ddbd67 100644 --- a/mysql-test/t/func_misc.test +++ b/mysql-test/t/func_misc.test @@ -205,6 +205,21 @@ SELECT NAME_CONST('test', -1.0); SELECT NAME_CONST('test', 'test'); # +# Bug #27545: erroneous usage of NAME_CONST with a name as the first parameter +# resolved against a column name of a derived table hangs the client +# + +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (5), (2); + +--error ER_RESERVED_SYNTAX +SELECT NAME_CONST(x,2) FROM (SELECT a x FROM t1) t; + +DROP TABLE t1; + +--echo End of 5.0 tests + +# # Bug #32559: connection hangs on query with name_const # CREATE TABLE t1(a INT); @@ -224,3 +239,4 @@ select min(a) from t1 group by inet_ntoa(a); drop table t1; --echo End of 5.0 tests + diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index c0a449ac3f4..f8249b7cf7c 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -787,4 +787,14 @@ select concat(a,ifnull(min(date_format(now(), '%Y-%m-%d')),' ull')) from t1; set lc_time_names=en_US; drop table t1; +# +# Bug#32180: DATE_ADD treats datetime numeric argument as DATE +# instead of DATETIME +# + +select DATE_ADD('20071108181000', INTERVAL 1 DAY); +select DATE_ADD(20071108181000, INTERVAL 1 DAY); +select DATE_ADD('20071108', INTERVAL 1 DAY); +select DATE_ADD(20071108, INTERVAL 1 DAY); + --echo End of 5.0 tests diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test index b7da7db1d76..cf2e4a21419 100644 --- a/mysql-test/t/gis.test +++ b/mysql-test/t/gis.test @@ -439,6 +439,48 @@ INSERT INTO `t1` VALUES ('','0000-00-00'); select geomfromtext(col9,col89) as a from t1; DROP TABLE t1; +# +# Bug #31158 Spatial, Union, LONGBLOB vs BLOB bug (crops data) +# + +CREATE TABLE t1 ( + geomdata polygon NOT NULL, + SPATIAL KEY index_geom (geomdata) +) ENGINE=MyISAM DEFAULT CHARSET=latin2 DELAY_KEY_WRITE=1 ROW_FORMAT=FIXED; + +CREATE TABLE t2 ( + geomdata polygon NOT NULL, + SPATIAL KEY index_geom (geomdata) +) ENGINE=MyISAM DEFAULT CHARSET=latin2 DELAY_KEY_WRITE=1 ROW_FORMAT=FIXED; + +CREATE TABLE t3 +select + aswkb(ws.geomdata) AS geomdatawkb + from + t1 ws +union + select + aswkb(ws.geomdata) AS geomdatawkb + from + t2 ws; + +describe t3; + +drop table t1; +drop table t2; +drop table t3; + +# +# Bug #30284 spatial key corruption +# + +create table t1(col1 geometry default null,col15 geometrycollection not +null,spatial index(col15),index(col1(15)))engine=myisam; +insert into t1 set col15 = GeomFromText('POINT(6 5)'); +insert into t1 set col15 = GeomFromText('POINT(6 5)'); +check table t1 extended; +drop table t1; + --echo End of 4.1 tests # @@ -600,4 +642,13 @@ SELECT 1; -- source include/gis_keys.inc +# +# Bug #31155 gis types in union'd select cause crash +# + +create table `t1` (`col002` point)engine=myisam; +insert into t1 values (),(),(); +select min(`col002`) from t1 union select `col002` from t1; +drop table t1; + --echo End of 5.0 tests diff --git a/mysql-test/t/group_min_max.test b/mysql-test/t/group_min_max.test index e1010c9fcfe..7f2607b513d 100644 --- a/mysql-test/t/group_min_max.test +++ b/mysql-test/t/group_min_max.test @@ -888,7 +888,31 @@ SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1; DROP TABLE t1; +# +# Bug #32268: Indexed queries give bogus MIN and MAX results +# + +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3); +INSERT INTO t1 SELECT a + 1, b FROM t1; +INSERT INTO t1 SELECT a + 2, b FROM t1; +EXPLAIN +SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC; +SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC; +CREATE INDEX break_it ON t1 (a, b); +EXPLAIN +SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a; +SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a; +EXPLAIN +SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC; +SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC; + +EXPLAIN +SELECT a, MIN(b), MAX(b), AVG(b) FROM t1 GROUP BY a ORDER BY a DESC; +SELECT a, MIN(b), MAX(b), AVG(b) FROM t1 GROUP BY a ORDER BY a DESC; + +DROP TABLE t1; diff --git a/mysql-test/t/innodb_mysql.test b/mysql-test/t/innodb_mysql.test index 13aa6afd701..020295684b0 100644 --- a/mysql-test/t/innodb_mysql.test +++ b/mysql-test/t/innodb_mysql.test @@ -961,6 +961,19 @@ SELECT * FROM t1 ORDER BY b DESC, a ASC; DROP TABLE t1; # +# Bug #32815: query with ORDER BY and a possible ref_or_null access +# + +CREATE TABLE t1 (id int, type char(6), d int, INDEX idx(id,d)) ENGINE=InnoDB; +INSERT INTO t1 VALUES + (191, 'member', 1), (NULL, 'member', 3), (NULL, 'member', 4), (201, 'member', 2); + +EXPLAIN SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d; +SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d; + +DROP TABLE t1; + + # Bug #31137: Assertion failed: primary_key_no == -1 || primary_key_no == 0 # create table t1(a char(10) not null, unique key aa(a(1)), diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test index b6debadcc94..35443987858 100644 --- a/mysql-test/t/merge.test +++ b/mysql-test/t/merge.test @@ -521,4 +521,26 @@ CREATE TABLE t2 (c1 INT) ENGINE=MERGE UNION=(t1) INSERT_METHOD=FIRST; CREATE TABLE IF NOT EXISTS t1 SELECT * FROM t2; DROP TABLE t1, t2; +# +# Bug #28837: MyISAM storage engine error (134) doing delete with self-join +# + +CREATE TABLE t1 (id INT NOT NULL, ref INT NOT NULL, INDEX (id)) ENGINE=MyISAM; +CREATE TABLE t2 LIKE t1; + +INSERT INTO t2 (id, ref) VALUES (1,3), (2,1), (3,2), (4,5), (4,4); +INSERT INTO t1 SELECT * FROM t2; +INSERT INTO t1 SELECT * FROM t2; + +CREATE TABLE t3 (id INT NOT NULL, ref INT NOT NULL, INDEX (id)) ENGINE=MERGE + UNION(t1); + +SELECT * FROM t3 AS a INNER JOIN t3 AS b USING (id) WHERE a.ref < b.ref; +SELECT * FROM t3; +DELETE FROM a USING t3 AS a INNER JOIN t3 AS b USING (id) WHERE a.ref < b.ref; +SELECT * FROM t3; + +DROP TABLE t1, t2, t3; + + --echo End of 5.0 tests diff --git a/mysql-test/t/myisam.test b/mysql-test/t/myisam.test index 80c7a92c12f..90d4fc01afb 100644 --- a/mysql-test/t/myisam.test +++ b/mysql-test/t/myisam.test @@ -1193,4 +1193,21 @@ SET @@myisam_repair_threads=1; CHECK TABLE t1 EXTENDED; DROP TABLE t1; +# +# Bug#28837: MyISAM storage engine error (134) doing delete with self-join +# + +CREATE TABLE t1 (id int NOT NULL, ref int NOT NULL, INDEX (id)) ENGINE=MyISAM; +CREATE TABLE t2 LIKE t1; + +INSERT INTO t2 (id, ref) VALUES (1,3), (2,1), (3,2), (4,5), (4,4); +INSERT INTO t1 SELECT * FROM t2; + +SELECT * FROM t1 AS a INNER JOIN t1 AS b USING (id) WHERE a.ref < b.ref; +SELECT * FROM t1; +DELETE FROM a USING t1 AS a INNER JOIN t1 AS b USING (id) WHERE a.ref < b.ref; +SELECT * FROM t1; + +DROP TABLE t1, t2; + --echo End of 5.0 tests diff --git a/mysql-test/t/outfile_loaddata.test b/mysql-test/t/outfile_loaddata.test index 2f6ac998b3d..2a120871e7d 100644 --- a/mysql-test/t/outfile_loaddata.test +++ b/mysql-test/t/outfile_loaddata.test @@ -86,4 +86,28 @@ DROP TABLE t2; DROP TABLE t1; +--echo # +--echo # Bug#32533: SELECT INTO OUTFILE never escapes multibyte character +--echo # + +CREATE TABLE t1 (c1 VARCHAR(256)); +INSERT INTO t1 VALUES (0xC3); +SELECT HEX(c1) FROM t1; + +--let $file=$MYSQLTEST_VARDIR/tmp/bug32533.txt + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT * INTO OUTFILE '$file' FIELDS ENCLOSED BY 0xC3 FROM t1 +TRUNCATE t1; + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT HEX(LOAD_FILE('$file')) + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval LOAD DATA INFILE '$file' INTO TABLE t1 FIELDS ENCLOSED BY 0xC3 +SELECT HEX(c1) FROM t1; + +--remove_file $file +DROP TABLE t1; + --echo # End of 5.0 tests. diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test index b8d4b91f03d..87ba3510326 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -935,4 +935,24 @@ SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-02 00:00:00'; DROP TABLE t1; +--echo +--echo BUG#32198 "Comparison of DATE with DATETIME still not using indexes correctly" +--echo +CREATE TABLE t1 ( + id int(11) NOT NULL auto_increment, + dateval date default NULL, + PRIMARY KEY (id), + KEY dateval (dateval) +) AUTO_INCREMENT=173; + +INSERT INTO t1 VALUES +(1,'2007-01-01'),(2,'2007-01-02'),(3,'2007-01-03'),(4,'2007-01-04'), +(5,'2007-01-05'),(6,'2007-01-06'),(7,'2007-01-07'),(8,'2007-01-08'), +(9,'2007-01-09'),(10,'2007-01-10'),(11,'2007-01-11'); + +--echo This must use range access: +explain select * from t1 where dateval >= '2007-01-01 00:00:00' and dateval <= '2007-01-02 23:59:59'; + +drop table t1; + # End of 5.0 tests diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index 31c8a3f7d11..c48f2771aa8 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -3661,4 +3661,15 @@ DROP TABLE t2; ########################################################################### +# +# Bug #32335: Error on BIGINT > NULL + 1 +# + +CREATE TABLE t1 (c1 BIGINT NOT NULL); +INSERT INTO t1 (c1) VALUES (1); +SELECT * FROM t1 WHERE c1 > NULL + 1; +DROP TABLE t1; + +--echo + --echo End of 5.0 tests diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 06a64f518fb..ea080964058 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -32,7 +32,9 @@ SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a)); select (SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(1)); -- error 1108 SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE((SELECT 1)); +-- error ER_BAD_FIELD_ERROR SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL; +-- error ER_BAD_FIELD_ERROR SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NOT NULL; SELECT (SELECT 1,2,3) = ROW(1,2,3); SELECT (SELECT 1,2,3) = ROW(1,2,1); @@ -1346,17 +1348,20 @@ drop table t1,t2; CREATE TABLE t1 ( a int, b int ); CREATE TABLE t2 ( c int, d int ); INSERT INTO t1 VALUES (1,2), (2,3), (3,4); -SELECT a AS abc, b FROM t1 WHERE b = (SELECT MIN(b) FROM t1 WHERE a=abc); -INSERT INTO t2 SELECT a AS abc, b FROM t1 WHERE b = (SELECT MIN(b) FROM t1 WHERE a=abc); +SELECT a AS abc, b FROM t1 outr WHERE b = + (SELECT MIN(b) FROM t1 WHERE a=outr.a); +INSERT INTO t2 SELECT a AS abc, b FROM t1 outr WHERE b = + (SELECT MIN(b) FROM t1 WHERE a=outr.a); select * from t2; -CREATE TABLE t3 SELECT a AS abc, b FROM t1 WHERE b = (SELECT MIN(b) FROM t1 WHERE a=abc); +CREATE TABLE t3 SELECT a AS abc, b FROM t1 outr WHERE b = + (SELECT MIN(b) FROM t1 WHERE a=outr.a); select * from t3; -prepare stmt1 from "INSERT INTO t2 SELECT a AS abc, b FROM t1 WHERE b = (SELECT MIN(b) FROM t1 WHERE a=abc);"; +prepare stmt1 from "INSERT INTO t2 SELECT a AS abc, b FROM t1 outr WHERE b = (SELECT MIN(b) FROM t1 WHERE a=outr.a);"; execute stmt1; deallocate prepare stmt1; select * from t2; drop table t3; -prepare stmt1 from "CREATE TABLE t3 SELECT a AS abc, b FROM t1 WHERE b = (SELECT MIN(b) FROM t1 WHERE a=abc);"; +prepare stmt1 from "CREATE TABLE t3 SELECT a AS abc, b FROM t1 outr WHERE b = (SELECT MIN(b) FROM t1 WHERE a=outr.a);"; execute stmt1; select * from t3; deallocate prepare stmt1; @@ -1529,7 +1534,9 @@ INSERT INTO t1 VALUES ('ASM','American Samoa','Oceania','Polynesia',199.00,0,680 INSERT INTO t1 VALUES ('ATF','French Southern territories','Antarctica','Antarctica',7780.00,0,0,NULL,0.00,NULL,'Terres australes françaises','Nonmetropolitan Territory of France','Jacques Chirac',NULL,'TF'); INSERT INTO t1 VALUES ('UMI','United States Minor Outlying Islands','Oceania','Micronesia/Caribbean',16.00,0,0,NULL,0.00,NULL,'United States Minor Outlying Islands','Dependent Territory of the US','George W. Bush',NULL,'UM'); /*!40000 ALTER TABLE t1 ENABLE KEYS */; -SELECT DISTINCT Continent AS c FROM t1 WHERE Code <> SOME ( SELECT Code FROM t1 WHERE Continent = c AND Population < 200); +SELECT DISTINCT Continent AS c FROM t1 outr WHERE + Code <> SOME ( SELECT Code FROM t1 WHERE Continent = outr.Continent AND + Population < 200); drop table t1; # @@ -2448,12 +2455,16 @@ DROP TABLE t1, t2; CREATE TABLE t1 (i INT); (SELECT i FROM t1) UNION (SELECT i FROM t1); +#TODO:not supported +--error ER_PARSE_ERROR SELECT sql_no_cache * FROM t1 WHERE NOT EXISTS ( (SELECT i FROM t1) UNION (SELECT i FROM t1) ); +#TODO:not supported +--error ER_PARSE_ERROR SELECT * FROM t1 WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1))); @@ -2461,7 +2472,9 @@ WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1))); --error 1064 explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12)) from t1; -#supported + +#TODO:not supported +--error ER_PARSE_ERROR explain select * from t1 where not exists ((select t11.i from t1 t11) union (select t12.i from t1 t12)); @@ -2988,6 +3001,100 @@ SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=1) FROM t1; DROP TABLE t1,t2; # +# Bug31048: Many nested subqueries may cause server crash. +# +create table t1(a int,b int,key(a),key(b)); +insert into t1(a,b) values (1,2),(2,1),(2,3),(3,4),(5,4),(5,5), + (6,7),(7,4),(5,3); +# test for the stack overflow bug +select sum(a),a from t1 where a> ( + select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( + select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( + select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( + select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( + select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( + select sum(a) from t1 where a> ( select sum(a) from t1 + )group by b limit 1)group by b limit 1 + )group by b limit 1)group by b limit 1)group by b limit 1 + )group by b limit 1)group by b limit 1)group by b limit 1 + )group by b limit 1)group by b limit 1)group by b limit 1) +group by a; +--replace_regex /overrun.*$/overrun detected/ +--error 1436 +select sum(a),a from t1 where a> ( + select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( + select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( + select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( + select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( + select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( + select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( + select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( + select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( + select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( + select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( + select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( + select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( + select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( + select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( + select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( + select sum(a) from t1 + )group by b limit 1)group by b limit 1)group by b limit 1 + )group by b limit 1)group by b limit 1)group by b limit 1 + )group by b limit 1)group by b limit 1)group by b limit 1 + )group by b limit 1)group by b limit 1)group by b limit 1 + )group by b limit 1)group by b limit 1)group by b limit 1 + )group by b limit 1)group by b limit 1)group by b limit 1 + )group by b limit 1)group by b limit 1)group by b limit 1 + )group by b limit 1)group by b limit 1)group by b limit 1 + )group by b limit 1)group by b limit 1)group by b limit 1 + )group by b limit 1)group by b limit 1)group by b limit 1) +group by a; +# test for the memory consumption & subquery slowness bug +explain select sum(a),a from t1 where a> ( + select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( + select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( + select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( + select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( + select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( + select sum(a) from t1 where a> ( select sum(a) from t1 + )group by b limit 1)group by b limit 1 + )group by b limit 1)group by b limit 1)group by b limit 1 + )group by b limit 1)group by b limit 1)group by b limit 1 + )group by b limit 1)group by b limit 1)group by b limit 1) +group by a; +--replace_regex /overrun.*$/overrun detected/ +--error 1436 +explain select sum(a),a from t1 where a> ( + select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( + select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( + select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( + select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( + select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( + select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( + select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( + select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( + select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( + select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( + select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( + select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( + select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( + select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( + select sum(a) from t1 where a> ( select sum(a) from t1 where a> ( + select sum(a) from t1 + )group by b limit 1)group by b limit 1)group by b limit 1 + )group by b limit 1)group by b limit 1)group by b limit 1 + )group by b limit 1)group by b limit 1)group by b limit 1 + )group by b limit 1)group by b limit 1)group by b limit 1 + )group by b limit 1)group by b limit 1)group by b limit 1 + )group by b limit 1)group by b limit 1)group by b limit 1 + )group by b limit 1)group by b limit 1)group by b limit 1 + )group by b limit 1)group by b limit 1)group by b limit 1 + )group by b limit 1)group by b limit 1)group by b limit 1 + )group by b limit 1)group by b limit 1)group by b limit 1) +group by a; +drop table t1; + +# # Bug #31884: Assertion + crash in subquery in the SELECT clause. # @@ -3044,4 +3151,83 @@ SELECT LEFT(t1.a1,1) FROM t1,t3 WHERE t1.b1=t3.a3; SELECT a2 FROM t2 WHERE t2.a2 IN (SELECT t1.a1 FROM t1,t3 WHERE t1.b1=t3.a3); DROP TABLE t1, t2, t3; +# +# Bug #30788: Inconsistent retrieval of char/varchar +# + +CREATE TABLE t1 (a CHAR(1), b VARCHAR(10)); +INSERT INTO t1 VALUES ('a', 'aa'); +INSERT INTO t1 VALUES ('a', 'aaa'); +SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1); +CREATE INDEX I1 ON t1 (a); +CREATE INDEX I2 ON t1 (b); +EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1); +SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1); + +CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10)); +INSERT INTO t2 SELECT * FROM t1; +CREATE INDEX I1 ON t2 (a); +CREATE INDEX I2 ON t2 (b); +EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2); +SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2); +EXPLAIN +SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500); +SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500); + +DROP TABLE t1,t2; + +# +# Bug #32400: Complex SELECT query returns correct result only on some +# occasions +# + +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1,1), (1,2), (2,3), (2,4); + +--error ER_BAD_FIELD_ERROR +EXPLAIN +SELECT a AS out_a, MIN(b) FROM t1 +WHERE b > (SELECT MIN(b) FROM t1 WHERE a = out_a) +GROUP BY a; + +--error ER_BAD_FIELD_ERROR +SELECT a AS out_a, MIN(b) FROM t1 +WHERE b > (SELECT MIN(b) FROM t1 WHERE a = out_a) +GROUP BY a; + +EXPLAIN +SELECT a AS out_a, MIN(b) FROM t1 t1_outer +WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a) +GROUP BY a; + +SELECT a AS out_a, MIN(b) FROM t1 t1_outer +WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a) +GROUP BY a; + +DROP TABLE t1; + +# +# Bug #32036: EXISTS within a WHERE clause with a UNION crashes MySQL 5.122 +# + +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (a INT); + +INSERT INTO t1 VALUES (1),(2); +INSERT INTO t2 VALUES (1),(2); + +SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a)); +EXPLAIN EXTENDED +SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a)); + + +#TODO:not supported +--error ER_PARSE_ERROR +EXPLAIN EXTENDED +SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION + (SELECT 1 FROM t2 WHERE t1.a = t2.a)); + +DROP TABLE t1,t2; + + --echo End of 5.0 tests. diff --git a/mysql-test/t/type_bit.test b/mysql-test/t/type_bit.test index 4978f55d776..3095311bfbc 100644 --- a/mysql-test/t/type_bit.test +++ b/mysql-test/t/type_bit.test @@ -318,4 +318,19 @@ INSERT INTO t2 VALUES (3, 2, 'two'), (2, 3, 'three'), (2, 0, 'zero'), SELECT COUNT(DISTINCT b,c) FROM t2 GROUP BY a; DROP TABLE t2; +# +# BUG#32556 assert in "using index for group-by" : is_last_prefix <= 0, +# file .\opt_range.cc + +CREATE TABLE t1(a BIT(13), KEY(a)); +--disable_warnings +INSERT INTO t1(a) VALUES +(65535),(65525),(65535),(65535),(65535),(65535),(65535),(65535),(65535),(65535); +--enable_warnings + +EXPLAIN SELECT 1 FROM t1 GROUP BY a; +SELECT 1 FROM t1 GROUP BY a; + +DROP TABLE t1; + --echo End of 5.0 tests diff --git a/mysql-test/t/type_blob.test b/mysql-test/t/type_blob.test index ba9f374a24c..d79b749dd65 100644 --- a/mysql-test/t/type_blob.test +++ b/mysql-test/t/type_blob.test @@ -436,4 +436,15 @@ set @@sql_mode='TRADITIONAL'; create table t1 (a text default ''); set @@sql_mode=''; +# +# Bug #32282: TEXT silently truncates when value is exactly 65536 bytes +# + +CREATE TABLE t (c TEXT CHARSET ASCII); +INSERT INTO t (c) VALUES (REPEAT('1',65537)); +INSERT INTO t (c) VALUES (REPEAT('2',65536)); +INSERT INTO t (c) VALUES (REPEAT('3',65535)); +SELECT LENGTH(c), CHAR_LENGTH(c) FROM t; +DROP TABLE t; + --echo End of 5.0 tests diff --git a/mysql-test/t/type_date.test b/mysql-test/t/type_date.test index 507537457d3..885fad3b623 100644 --- a/mysql-test/t/type_date.test +++ b/mysql-test/t/type_date.test @@ -190,4 +190,24 @@ INSERT INTO t1 VALUES ('0000-00-00'); SET SQL_MODE=DEFAULT; DROP TABLE t1,t2; +# +# Bug #31928: Search fails on '1000-00-00' date after sql_mode change +# + +CREATE TABLE t1 (a DATE); +CREATE TABLE t2 (a DATE); +CREATE INDEX i ON t1 (a); +INSERT INTO t1 VALUES ('1000-00-00'),('1000-00-00'); +INSERT INTO t2 VALUES ('1000-00-00'),('1000-00-00'); +SELECT * FROM t1 WHERE a = '1000-00-00'; +SELECT * FROM t2 WHERE a = '1000-00-00'; +SET SQL_MODE=TRADITIONAL; +EXPLAIN SELECT * FROM t1 WHERE a = '1000-00-00'; +SELECT * FROM t1 WHERE a = '1000-00-00'; +SELECT * FROM t2 WHERE a = '1000-00-00'; +--error ER_TRUNCATED_WRONG_VALUE +INSERT INTO t1 VALUES ('1000-00-00'); +SET SQL_MODE=DEFAULT; +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 219af2336e1..722e4c082a7 100644 --- a/mysql-test/t/type_datetime.test +++ b/mysql-test/t/type_datetime.test @@ -351,4 +351,41 @@ insert into t1 values (), (), (); select sum(a) from t1 group by convert(a, datetime); drop table t1; +# +# Bug #32694: NOT NULL table field in a subquery produces invalid results +# +create table t1 (id int(10) not null, cur_date datetime not null); +create table t2 (id int(10) not null, cur_date date not null); +insert into t1 (id, cur_date) values (1, '2007-04-25 18:30:22'); +insert into t2 (id, cur_date) values (1, '2007-04-25'); + +explain extended +select * from t1 +where id in (select id from t1 as x1 where (t1.cur_date is null)); +select * from t1 +where id in (select id from t1 as x1 where (t1.cur_date is null)); + +explain extended +select * from t2 +where id in (select id from t2 as x1 where (t2.cur_date is null)); +select * from t2 +where id in (select id from t2 as x1 where (t2.cur_date is null)); + +insert into t1 (id, cur_date) values (2, '2007-04-26 18:30:22'); +insert into t2 (id, cur_date) values (2, '2007-04-26'); + +explain extended +select * from t1 +where id in (select id from t1 as x1 where (t1.cur_date is null)); +select * from t1 +where id in (select id from t1 as x1 where (t1.cur_date is null)); + +explain extended +select * from t2 +where id in (select id from t2 as x1 where (t2.cur_date is null)); +select * from t2 +where id in (select id from t2 as x1 where (t2.cur_date is null)); + +drop table t1,t2; + --echo End of 5.0 tests diff --git a/mysql-test/t/udf.test b/mysql-test/t/udf.test index 22b8ed10a49..648494b3df9 100644 --- a/mysql-test/t/udf.test +++ b/mysql-test/t/udf.test @@ -362,4 +362,25 @@ DROP PROCEDURE check_const_len_sp; DROP TRIGGER check_const_len_trigger; DROP TABLE const_len_bug; + +# +# Bug #30355: Incorrect ordering of UDF results +# + +--replace_result $UDF_EXAMPLE_LIB UDF_EXAMPLE_LIB +eval CREATE FUNCTION sequence RETURNS INTEGER SONAME "$UDF_EXAMPLE_LIB"; +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (a INT PRIMARY KEY); +INSERT INTO t1 VALUES (4),(3),(2),(1); +INSERT INTO t2 SELECT * FROM t1; + +SELECT sequence() AS seq, a FROM t1 ORDER BY seq ASC; +SELECT sequence() AS seq, a FROM t1 ORDER BY seq DESC; + +SELECT * FROM t1 WHERE a = sequence(); +SELECT * FROM t2 WHERE a = sequence(); + +DROP FUNCTION sequence; +DROP TABLE t1,t2; + --echo End of 5.0 tests. diff --git a/mysql-test/t/variables.test b/mysql-test/t/variables.test index a8f0a3425df..c86e7dfa7f3 100644 --- a/mysql-test/t/variables.test +++ b/mysql-test/t/variables.test @@ -126,7 +126,7 @@ set GLOBAL query_cache_size=100000; set GLOBAL myisam_max_sort_file_size=2000000; show global variables like 'myisam_max_sort_file_size'; set GLOBAL myisam_max_sort_file_size=default; ---replace_result 2147483647 FILE_SIZE 9223372036854775807 FILE_SIZE +--replace_result 9223372036853727232 FILE_SIZE 2146435072 FILE_SIZE show variables like 'myisam_max_sort_file_size'; set global net_retry_count=10, session net_retry_count=10; |