diff options
author | unknown <sanja@montyprogram.com> | 2013-08-20 14:48:29 +0300 |
---|---|---|
committer | unknown <sanja@montyprogram.com> | 2013-08-20 14:48:29 +0300 |
commit | 35b2883643e337a8ec9c3cf7494363ae9889119c (patch) | |
tree | eb2e7d833ed965b30f8b8485cfdac31abfea6c97 /mysql-test | |
parent | dafa458262fb47a2c9534bcc36088b15e26307c2 (diff) | |
parent | b59738a598569ace75be5e63b7ed6ca69afe6ebc (diff) | |
download | mariadb-git-35b2883643e337a8ec9c3cf7494363ae9889119c.tar.gz |
merge 5.5 -> 10.0-base
Diffstat (limited to 'mysql-test')
34 files changed, 405 insertions, 58 deletions
diff --git a/mysql-test/r/dyncol.result b/mysql-test/r/dyncol.result index 172179bd7f4..7316a1c2d17 100644 --- a/mysql-test/r/dyncol.result +++ b/mysql-test/r/dyncol.result @@ -1404,6 +1404,27 @@ v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VI drop view v1; drop table t1; # +# MDEV-4811: Assertion `offset < 0x1f' fails in type_and_offset_store +# on COLUMN_ADD +# +CREATE TABLE t1 (dyn TINYBLOB) ENGINE=MyISAM; +INSERT INTO t1 SET dyn = COLUMN_CREATE( 40, REPEAT('a', 233), 4, REPEAT('b', 322) ); +Warnings: +Warning 1265 Data truncated for column 'dyn' at row 1 +SELECT COLUMN_ADD( dyn, 6, REPEAT('x',80), 4, REPEAT('y',215) AS INTEGER ) FROM t1; +ERROR HY000: Encountered illegal format of dynamic column string +DROP table t1; +# +# MDEV-4812: Valgrind warnings (Invalid write) in +# dynamic_column_update_many on COLUMN_ADD +# +CREATE TABLE t1 (dyncol TINYBLOB) ENGINE=MyISAM; +INSERT INTO t1 SET dyncol = COLUMN_CREATE( 7, REPEAT('k',487), 209, REPEAT('x',464) ); +Warnings: +Warning 1265 Data truncated for column 'dyncol' at row 1 +SELECT COLUMN_ADD( dyncol, 7, '22:22:22', 8, REPEAT('x',270) AS CHAR ) FROM t1; +DROP table t1; +# # end of 5.3 tests # # diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index 38aa3f49c4d..ba90707ca40 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -1971,6 +1971,7 @@ MIN(t2.pk) NULL Warnings: Warning 1292 Truncated incorrect INTEGER value: 'j' +Warning 1292 Truncated incorrect INTEGER value: 'j' EXPLAIN SELECT MIN(t2.pk) @@ -1984,6 +1985,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Warnings: Warning 1292 Truncated incorrect INTEGER value: 'j' +Warning 1292 Truncated incorrect INTEGER value: 'j' # # 2) Test that subquery materialization is setup for query with diff --git a/mysql-test/r/func_set.result b/mysql-test/r/func_set.result index 492349fab2d..93757800505 100644 --- a/mysql-test/r/func_set.result +++ b/mysql-test/r/func_set.result @@ -160,6 +160,25 @@ CONVERT( a USING latin1 ) DROP TABLE t1, t2; # +# Start of 5.3 tests +# +# +# MDEV-4512 Valgrind warnings in my_long10_to_str_8bit on INTERVAL and DATE_ADD with incorrect types +# +CREATE TABLE t1 (pk INT PRIMARY KEY); +INSERT INTO t1 VALUES (10),(11); +SELECT INTERVAL( 9, 1, DATE_ADD( pk, INTERVAL pk MINUTE_SECOND ), 9, 8, 3, 5, 2, 1 ) FROM t1; +INTERVAL( 9, 1, DATE_ADD( pk, INTERVAL pk MINUTE_SECOND ), 9, 8, 3, 5, 2, 1 ) +8 +8 +Warnings: +Warning 1292 Incorrect datetime value: '10' +Warning 1292 Incorrect datetime value: '11' +DROP TABLE t1; +# +# End of 5.3 tests +# +# # BUG#59405: FIND_IN_SET won't work normaly after upgrade from 5.1 to 5.5 # CREATE TABLE t1(days set('1','2','3','4','5','6','7')); diff --git a/mysql-test/r/insert.result b/mysql-test/r/insert.result index 7110f541fb7..a722ab8d97f 100644 --- a/mysql-test/r/insert.result +++ b/mysql-test/r/insert.result @@ -663,9 +663,10 @@ Warning 1365 Division by 0 Warning 1048 Column 'data' cannot be null update t1 set data='envelope' where 1/0 or 1; affected rows: 2 -info: Rows matched: 2 Changed: 2 Warnings: 3 +info: Rows matched: 2 Changed: 2 Warnings: 4 Warnings: Warning 1365 Division by 0 +Warning 1365 Division by 0 Warning 1265 Data truncated for column 'data' at row 1 Warning 1265 Data truncated for column 'data' at row 2 insert t1 (data) values (default), (1/0), ('dead beef'); diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index 8920539ef2e..fb4cc58d607 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -2117,4 +2117,25 @@ SELECT a.* FROM t1 a LEFT JOIN t1 b ON a.id = b.id WHERE a.modified > b.modified or b.modified IS NULL; id modified DROP TABLE t1; +# +# MDEV-4817: Optimizer fails to optimize expression of the form 'FOO' IS NULL +# +create table t0 (a int not null); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +alter table t0 add person_id varchar(255) not null; +create table t1 (pk int not null primary key); +insert into t1 select A.a + 10*B.a from t0 A, t0 B; +explain select * from t1 left join t0 on t0.a=t1.pk where t0.person_id='fooo' or 'xyz' IS NULL; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 Using index +explain select * from t1 left join t0 on t0.a=t1.pk where t0.person_id='fooo'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 Using index +explain select * from t1 left join t0 on t0.a=t1.pk where t0.person_id='fooo' or t0.person_id='bar'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 Using index +drop table t0, t1; SET optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/r/join_outer_innodb.result b/mysql-test/r/join_outer_innodb.result index 1081fc0eed3..336fb5ee6b6 100644 --- a/mysql-test/r/join_outer_innodb.result +++ b/mysql-test/r/join_outer_innodb.result @@ -14,8 +14,8 @@ EXPLAIN SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id WHERE t1.name LIKE 'A%' OR FALSE; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index NULL fkey 5 NULL 5 Using index -1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.fkey 1 Using where +1 SIMPLE t1 index PRIMARY,name name 23 NULL 3 Using where; Using index +1 SIMPLE t2 ref fkey fkey 5 test.t1.id 1 Using index DROP TABLE t1,t2; # # BUG#58456: Assertion 0 in QUICK_INDEX_MERGE_SELECT::need_sorted_output diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result index 43443aa2fef..b5460d43e2b 100644 --- a/mysql-test/r/join_outer_jcl6.result +++ b/mysql-test/r/join_outer_jcl6.result @@ -2128,6 +2128,27 @@ SELECT a.* FROM t1 a LEFT JOIN t1 b ON a.id = b.id WHERE a.modified > b.modified or b.modified IS NULL; id modified DROP TABLE t1; +# +# MDEV-4817: Optimizer fails to optimize expression of the form 'FOO' IS NULL +# +create table t0 (a int not null); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +alter table t0 add person_id varchar(255) not null; +create table t1 (pk int not null primary key); +insert into t1 select A.a + 10*B.a from t0 A, t0 B; +explain select * from t1 left join t0 on t0.a=t1.pk where t0.person_id='fooo' or 'xyz' IS NULL; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 Using index +explain select * from t1 left join t0 on t0.a=t1.pk where t0.person_id='fooo'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 Using index +explain select * from t1 left join t0 on t0.a=t1.pk where t0.person_id='fooo' or t0.person_id='bar'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 Using index +drop table t0, t1; SET optimizer_switch=@save_optimizer_switch; set join_cache_level=default; show variables like 'join_cache_level'; diff --git a/mysql-test/r/lock_multi.result b/mysql-test/r/lock_multi.result index 67712ccabe6..f9e4a88fe95 100644 --- a/mysql-test/r/lock_multi.result +++ b/mysql-test/r/lock_multi.result @@ -18,7 +18,7 @@ n 4 drop table t1; create table t1(n int); -insert into t1 values (1); +insert into t1 values (1),(2); select get_lock("mysqltest_lock", 100); get_lock("mysqltest_lock", 100) 1 @@ -27,11 +27,13 @@ update low_priority t1 set n = 4; select n from t1; n 1 +2 select release_lock("mysqltest_lock"); release_lock("mysqltest_lock") 1 n 1 +2 select release_lock("mysqltest_lock"); release_lock("mysqltest_lock") 1 diff --git a/mysql-test/r/mysql.result b/mysql-test/r/mysql.result index 4d2ac25a0eb..cb705d285fe 100644 --- a/mysql-test/r/mysql.result +++ b/mysql-test/r/mysql.result @@ -515,5 +515,9 @@ aa`bb``cc drop database `aa``bb````cc`; a >>\ndelimiter\n<< ++-------------------+ +| a | +| aaaaaaaaaaaaaaaaa | ++-------------------+ End of tests diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index 146d250d687..86d98fdcc54 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -1590,6 +1590,8 @@ NULL Warnings: Warning 1411 Incorrect datetime value: '2007-20-00' for function str_to_date Warning 1411 Incorrect datetime value: '2007-20-00' for function str_to_date +Warning 1411 Incorrect datetime value: '2007-20-00' for function str_to_date +Warning 1411 Incorrect datetime value: '2007-20-00' for function str_to_date SELECT str_to_date('2007-10-00', '%Y-%m-%d') BETWEEN '' AND '2007/10/20'; str_to_date('2007-10-00', '%Y-%m-%d') BETWEEN '' AND '2007/10/20' 1 diff --git a/mysql-test/r/range_mrr_icp.result b/mysql-test/r/range_mrr_icp.result index 3769ceb9145..0527449fa9b 100644 --- a/mysql-test/r/range_mrr_icp.result +++ b/mysql-test/r/range_mrr_icp.result @@ -1592,6 +1592,8 @@ NULL Warnings: Warning 1411 Incorrect datetime value: '2007-20-00' for function str_to_date Warning 1411 Incorrect datetime value: '2007-20-00' for function str_to_date +Warning 1411 Incorrect datetime value: '2007-20-00' for function str_to_date +Warning 1411 Incorrect datetime value: '2007-20-00' for function str_to_date SELECT str_to_date('2007-10-00', '%Y-%m-%d') BETWEEN '' AND '2007/10/20'; str_to_date('2007-10-00', '%Y-%m-%d') BETWEEN '' AND '2007/10/20' 1 diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index c086a62275a..806afb1c843 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -5358,3 +5358,38 @@ WHERE c = a AND a b c DROP TABLE t1, t2; End of 5.3 tests +# +# mysql BUG#1271 Undefined variable in PASSWORD() +# function is not handled correctly +# +create table t1 ( +name VARCHAR(50) NOT NULL PRIMARY KEY, +pw VARCHAR(41) NOT NULL); +INSERT INTO t1 (name, pw) +VALUES ('tom', PASSWORD('my_pw')); +SET @pass='my_pw'; +SET @wrong='incorrect'; +select * from t1; +name pw +tom *F305E8EC27734F687F2EB6EC03CF0F7AF27C18E1 +select length(PASSWORD(@pass)); +length(PASSWORD(@pass)) +41 +SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@pass); +name +tom +SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@wrong); +name +SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@undefined); +name +select (SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@pass)); +(SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@pass)) +tom +select (SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@wrong)); +(SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@wrong)) +NULL +select (SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@undefined)); +(SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@undefined)) +NULL +drop table t1; +End of 10.0 tests diff --git a/mysql-test/r/select_jcl6.result b/mysql-test/r/select_jcl6.result index 00b356fc1c0..97ffa8413cb 100644 --- a/mysql-test/r/select_jcl6.result +++ b/mysql-test/r/select_jcl6.result @@ -5369,6 +5369,41 @@ WHERE c = a AND a b c DROP TABLE t1, t2; End of 5.3 tests +# +# mysql BUG#1271 Undefined variable in PASSWORD() +# function is not handled correctly +# +create table t1 ( +name VARCHAR(50) NOT NULL PRIMARY KEY, +pw VARCHAR(41) NOT NULL); +INSERT INTO t1 (name, pw) +VALUES ('tom', PASSWORD('my_pw')); +SET @pass='my_pw'; +SET @wrong='incorrect'; +select * from t1; +name pw +tom *F305E8EC27734F687F2EB6EC03CF0F7AF27C18E1 +select length(PASSWORD(@pass)); +length(PASSWORD(@pass)) +41 +SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@pass); +name +tom +SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@wrong); +name +SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@undefined); +name +select (SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@pass)); +(SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@pass)) +tom +select (SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@wrong)); +(SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@wrong)) +NULL +select (SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@undefined)); +(SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@undefined)) +NULL +drop table t1; +End of 10.0 tests set join_cache_level=default; show variables like 'join_cache_level'; Variable_name Value diff --git a/mysql-test/r/select_pkeycache.result b/mysql-test/r/select_pkeycache.result index c086a62275a..806afb1c843 100644 --- a/mysql-test/r/select_pkeycache.result +++ b/mysql-test/r/select_pkeycache.result @@ -5358,3 +5358,38 @@ WHERE c = a AND a b c DROP TABLE t1, t2; End of 5.3 tests +# +# mysql BUG#1271 Undefined variable in PASSWORD() +# function is not handled correctly +# +create table t1 ( +name VARCHAR(50) NOT NULL PRIMARY KEY, +pw VARCHAR(41) NOT NULL); +INSERT INTO t1 (name, pw) +VALUES ('tom', PASSWORD('my_pw')); +SET @pass='my_pw'; +SET @wrong='incorrect'; +select * from t1; +name pw +tom *F305E8EC27734F687F2EB6EC03CF0F7AF27C18E1 +select length(PASSWORD(@pass)); +length(PASSWORD(@pass)) +41 +SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@pass); +name +tom +SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@wrong); +name +SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@undefined); +name +select (SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@pass)); +(SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@pass)) +tom +select (SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@wrong)); +(SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@wrong)) +NULL +select (SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@undefined)); +(SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@undefined)) +NULL +drop table t1; +End of 10.0 tests diff --git a/mysql-test/r/sp_notembedded.result b/mysql-test/r/sp_notembedded.result index 540a66e7508..d34d57450d8 100644 --- a/mysql-test/r/sp_notembedded.result +++ b/mysql-test/r/sp_notembedded.result @@ -169,7 +169,7 @@ drop procedure if exists p1; create table t1 (value varchar(15)); create procedure p1() update t1 set value='updated' where value='old'; call p1(); -insert into t1 (value) values ("old"); +insert into t1 (value) values ("old"),("irrelevant"); select get_lock('b26162',120); get_lock('b26162',120) 1 @@ -179,11 +179,13 @@ call p1();; select 'rl_contender', value from t1; rl_contender value rl_contender old +rl_contender irrelevant select release_lock('b26162'); release_lock('b26162') 1 rl_acquirer value rl_acquirer old +rl_acquirer irrelevant drop procedure p1; drop table t1; set session low_priority_updates=default; diff --git a/mysql-test/r/subselect_cache.result b/mysql-test/r/subselect_cache.result index 7bd10dc11a6..95f935981c2 100644 --- a/mysql-test/r/subselect_cache.result +++ b/mysql-test/r/subselect_cache.result @@ -3129,6 +3129,7 @@ WHERE table1 .`col_varchar_key` ) field10 1 NULL f Warnings: Warning 1292 Truncated incorrect DOUBLE value: 'f' +Warning 1292 Truncated incorrect DOUBLE value: 'f' SET @@optimizer_switch = 'subquery_cache=on'; /* cache is on */ SELECT COUNT( DISTINCT table2 .`col_int_key` ) , ( SELECT SUBQUERY2_t1 .`col_int_key` @@ -3144,6 +3145,7 @@ WHERE table1 .`col_varchar_key` ) field10 1 NULL f Warnings: Warning 1292 Truncated incorrect DOUBLE value: 'f' +Warning 1292 Truncated incorrect DOUBLE value: 'f' drop table t1,t2,t3,t4; set @@optimizer_switch= default; #launchpad BUG#611625 diff --git a/mysql-test/r/timezone2.result b/mysql-test/r/timezone2.result index e2e337628ce..70f5ef9edc1 100644 --- a/mysql-test/r/timezone2.result +++ b/mysql-test/r/timezone2.result @@ -309,3 +309,22 @@ CONVERT_TZ(1, 1, a) NULL DROP TABLE t1; End of 5.1 tests +# +# Start of 5.3 tests +# +# +# MDEV-4653 Wrong result for CONVERT_TZ(TIME('00:00:00'),'+00:00','+7:5') +# +SELECT CONVERT_TZ(TIME('00:00:00'),'+00:00','+7:5'); +CONVERT_TZ(TIME('00:00:00'),'+00:00','+7:5') +NULL +Warnings: +Warning 1292 Incorrect datetime value: '00:00:00' +SELECT CONVERT_TZ(TIME('2010-01-01 00:00:00'),'+00:00','+7:5'); +CONVERT_TZ(TIME('2010-01-01 00:00:00'),'+00:00','+7:5') +NULL +Warnings: +Warning 1292 Incorrect datetime value: '00:00:00' +# +# End of 5.3 tests +# diff --git a/mysql-test/r/type_time.result b/mysql-test/r/type_time.result index 4167cbbe252..5a04913553b 100644 --- a/mysql-test/r/type_time.result +++ b/mysql-test/r/type_time.result @@ -183,6 +183,15 @@ NULL Warnings: Warning 1292 Incorrect datetime value: '0000-00-00 00:00:00' # +# MDEV-4652 Wrong result for CONCAT(GREATEST(TIME('00:00:01'),TIME('00:00:00'))) +# +SELECT CONCAT(GREATEST(TIME('00:00:01'),TIME('00:00:00'))); +CONCAT(GREATEST(TIME('00:00:01'),TIME('00:00:00'))) +00:00:01.000000 +SELECT CONCAT(GREATEST(TIME('32 00:00:01'),TIME('00:00:00'))); +CONCAT(GREATEST(TIME('32 00:00:01'),TIME('00:00:00'))) +768:00:01.000000 +# # End of 5.3 tests # CREATE TABLE t1 (f1 TIME); diff --git a/mysql-test/r/user_var.result b/mysql-test/r/user_var.result index 78ed5bb65d5..178f9fb7db4 100644 --- a/mysql-test/r/user_var.result +++ b/mysql-test/r/user_var.result @@ -126,14 +126,14 @@ select @a+0, @a:=@a+0+count(*), count(*), @a+0 from t1 group by i; set @a=0; select @a,@a:="hello",@a,@a:=3,@a,@a:="hello again" from t1 group by i; @a @a:="hello" @a @a:=3 @a @a:="hello again" -0 hello 0 3 0 hello again -0 hello 0 3 0 hello again -0 hello 0 3 0 hello again +0 hello 0 3 3 hello again +0 hello 0 3 3 hello again +0 hello 0 3 3 hello again select @a,@a:="hello",@a,@a:=3,@a,@a:="hello again" from t1 group by i; @a @a:="hello" @a @a:=3 @a @a:="hello again" -hello again hello hello again 3 hello again hello again -hello again hello hello again 3 hello again hello again -hello again hello hello again 3 hello again hello again +hello again hello hello 3 3 hello again +hello again hello hello 3 3 hello again +hello again hello hello 3 3 hello again drop table t1; set @a=_latin2'test'; select charset(@a),collation(@a),coercibility(@a); diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result index d907e18b932..083a5e92a8e 100644 --- a/mysql-test/r/variables.result +++ b/mysql-test/r/variables.result @@ -1549,7 +1549,7 @@ one 1 explain SELECT 1 as 'one' FROM t1 GROUP BY @a:= (SELECT ROUND(f1) FROM t1 WHERE f1 = 0); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where SELECT 1 as 'one' FROM t1 GROUP BY @a:= (SELECT ROUND(f1) FROM t1 WHERE f1 = 0); one @@ -1560,7 +1560,7 @@ one set sql_buffer_result=1; explain SELECT 1 as 'one' FROM t1 GROUP BY @a:= (SELECT ROUND(f1) FROM t1 WHERE f1 = 0); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using temporary +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where SELECT 1 as 'one' FROM t1 GROUP BY @a:= (SELECT ROUND(f1) FROM t1 WHERE f1 = 0); one diff --git a/mysql-test/suite/innodb/r/innodb_mysql.result b/mysql-test/suite/innodb/r/innodb_mysql.result index 7247b26e86b..11a2321b0f4 100644 --- a/mysql-test/suite/innodb/r/innodb_mysql.result +++ b/mysql-test/suite/innodb/r/innodb_mysql.result @@ -361,8 +361,8 @@ EXPLAIN SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id WHERE t1.name LIKE 'A%' OR FALSE; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index NULL fkey 5 NULL 5 Using index -1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.fkey 1 Using where +1 SIMPLE t1 index PRIMARY,name name 23 NULL 3 Using where; Using index +1 SIMPLE t2 ref fkey fkey 5 test.t1.id 1 Using index DROP TABLE t1,t2; CREATE TABLE t1 ( id int NOT NULL, diff --git a/mysql-test/suite/plugins/r/cassandra.result b/mysql-test/suite/plugins/r/cassandra.result index e26df069f93..65a81c429b0 100644 --- a/mysql-test/suite/plugins/r/cassandra.result +++ b/mysql-test/suite/plugins/r/cassandra.result @@ -74,8 +74,8 @@ Variable_name Value cassandra_insert_batch_size 100 show status like 'cassandra_row_insert%'; Variable_name Value -Cassandra_row_insert_batches 7 Cassandra_row_inserts 8 +Cassandra_row_insert_batches 7 CREATE TABLE t1 (rowkey BIGINT PRIMARY KEY, a BIGINT) ENGINE=CASSANDRA thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf2'; delete from t1; @@ -84,14 +84,14 @@ DELETE FROM t1 ORDER BY a LIMIT 1; DROP TABLE t1; show status like 'cassandra_row_insert%'; Variable_name Value -Cassandra_row_insert_batches 8 Cassandra_row_inserts 10 +Cassandra_row_insert_batches 8 # FLUSH STATUS doesn't work for our variables, just like with InnoDB. flush status; show status like 'cassandra_row_insert%'; Variable_name Value -Cassandra_row_insert_batches 0 -Cassandra_row_inserts 0 +Cassandra_row_inserts 10 +Cassandra_row_insert_batches 8 # # Batched Key Access # diff --git a/mysql-test/suite/storage_engine/alter_table_online.test b/mysql-test/suite/storage_engine/alter_table_online.test index 94dec2a72bb..c19ec0199eb 100644 --- a/mysql-test/suite/storage_engine/alter_table_online.test +++ b/mysql-test/suite/storage_engine/alter_table_online.test @@ -112,7 +112,7 @@ if ($mysql_errname!=ER_CANT_DO_ONLINE) --let $alter_definition = MODIFY b BIGINT $default_col_opts --source alter_table.inc ---let $alternative_engine = `SELECT engine FROM information_schema.engines WHERE engine != '$storage_engine' AND support IN ('YES','DEFAULT')` +--let $alternative_engine = `SELECT engine FROM information_schema.engines WHERE engine IN ('MEMORY','MyISAM') AND engine != '$storage_engine' ORDER BY engine LIMIT 1` --let $error_codes = ER_CANT_DO_ONLINE --let $online = 1 diff --git a/mysql-test/suite/storage_engine/type_spatial.result b/mysql-test/suite/storage_engine/type_spatial.result index 94e3b22fd3c..6c2f5a4d50f 100644 --- a/mysql-test/suite/storage_engine/type_spatial.result +++ b/mysql-test/suite/storage_engine/type_spatial.result @@ -414,20 +414,20 @@ FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second first second w c o e d t i r 120 120 1 1 0 1 0 1 1 0 120 121 0 0 1 0 0 0 1 0 -120 122 0 1 NULL 0 NULL 0 NULL 0 -120 123 0 1 NULL 0 NULL 0 NULL 0 +120 122 NULL NULL NULL NULL NULL NULL NULL NULL +120 123 NULL NULL NULL NULL NULL NULL NULL NULL 121 120 0 0 1 0 0 0 1 0 121 121 1 1 0 1 0 1 1 0 -121 122 0 1 NULL 0 NULL 0 NULL 0 -121 123 0 1 NULL 0 NULL 0 NULL 0 -122 120 1 0 NULL 0 NULL 0 NULL 0 -122 121 1 0 NULL 0 NULL 0 NULL 0 -122 122 1 1 NULL 1 NULL 0 NULL 0 -122 123 1 1 NULL 1 NULL 0 NULL 0 -123 120 1 0 NULL 0 NULL 0 NULL 0 -123 121 1 0 NULL 0 NULL 0 NULL 0 -123 122 1 1 NULL 1 NULL 0 NULL 0 -123 123 1 1 NULL 1 NULL 0 NULL 0 +121 122 NULL NULL NULL NULL NULL NULL NULL NULL +121 123 NULL NULL NULL NULL NULL NULL NULL NULL +122 120 NULL NULL NULL NULL NULL NULL NULL NULL +122 121 NULL NULL NULL NULL NULL NULL NULL NULL +122 122 NULL NULL NULL NULL NULL NULL NULL NULL +122 123 NULL NULL NULL NULL NULL NULL NULL NULL +123 120 NULL NULL NULL NULL NULL NULL NULL NULL +123 121 NULL NULL NULL NULL NULL NULL NULL NULL +123 122 NULL NULL NULL NULL NULL NULL NULL NULL +123 123 NULL NULL NULL NULL NULL NULL NULL NULL DROP TABLE gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry; USE gis_ogs; # Lakes diff --git a/mysql-test/suite/storage_engine/type_spatial_indexes.result b/mysql-test/suite/storage_engine/type_spatial_indexes.result index cebcb80d14f..93bb4c617e5 100644 --- a/mysql-test/suite/storage_engine/type_spatial_indexes.result +++ b/mysql-test/suite/storage_engine/type_spatial_indexes.result @@ -414,20 +414,20 @@ FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second first second w c o e d t i r 120 120 1 1 0 1 0 1 1 0 120 121 0 0 1 0 0 0 1 0 -120 122 0 1 NULL 0 NULL 0 NULL 0 -120 123 0 1 NULL 0 NULL 0 NULL 0 +120 122 NULL NULL NULL NULL NULL NULL NULL NULL +120 123 NULL NULL NULL NULL NULL NULL NULL NULL 121 120 0 0 1 0 0 0 1 0 121 121 1 1 0 1 0 1 1 0 -121 122 0 1 NULL 0 NULL 0 NULL 0 -121 123 0 1 NULL 0 NULL 0 NULL 0 -122 120 1 0 NULL 0 NULL 0 NULL 0 -122 121 1 0 NULL 0 NULL 0 NULL 0 -122 122 1 1 NULL 1 NULL 0 NULL 0 -122 123 1 1 NULL 1 NULL 0 NULL 0 -123 120 1 0 NULL 0 NULL 0 NULL 0 -123 121 1 0 NULL 0 NULL 0 NULL 0 -123 122 1 1 NULL 1 NULL 0 NULL 0 -123 123 1 1 NULL 1 NULL 0 NULL 0 +121 122 NULL NULL NULL NULL NULL NULL NULL NULL +121 123 NULL NULL NULL NULL NULL NULL NULL NULL +122 120 NULL NULL NULL NULL NULL NULL NULL NULL +122 121 NULL NULL NULL NULL NULL NULL NULL NULL +122 122 NULL NULL NULL NULL NULL NULL NULL NULL +122 123 NULL NULL NULL NULL NULL NULL NULL NULL +123 120 NULL NULL NULL NULL NULL NULL NULL NULL +123 121 NULL NULL NULL NULL NULL NULL NULL NULL +123 122 NULL NULL NULL NULL NULL NULL NULL NULL +123 123 NULL NULL NULL NULL NULL NULL NULL NULL DROP TABLE gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry; USE gis_ogs; # Lakes @@ -1114,20 +1114,20 @@ FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second first second w c o e d t i r 120 120 1 1 0 1 0 1 1 0 120 121 0 0 1 0 0 0 1 0 -120 122 0 1 NULL 0 NULL 0 NULL 0 -120 123 0 1 NULL 0 NULL 0 NULL 0 +120 122 NULL NULL NULL NULL NULL NULL NULL NULL +120 123 NULL NULL NULL NULL NULL NULL NULL NULL 121 120 0 0 1 0 0 0 1 0 121 121 1 1 0 1 0 1 1 0 -121 122 0 1 NULL 0 NULL 0 NULL 0 -121 123 0 1 NULL 0 NULL 0 NULL 0 -122 120 1 0 NULL 0 NULL 0 NULL 0 -122 121 1 0 NULL 0 NULL 0 NULL 0 -122 122 1 1 NULL 1 NULL 0 NULL 0 -122 123 1 1 NULL 1 NULL 0 NULL 0 -123 120 1 0 NULL 0 NULL 0 NULL 0 -123 121 1 0 NULL 0 NULL 0 NULL 0 -123 122 1 1 NULL 1 NULL 0 NULL 0 -123 123 1 1 NULL 1 NULL 0 NULL 0 +121 122 NULL NULL NULL NULL NULL NULL NULL NULL +121 123 NULL NULL NULL NULL NULL NULL NULL NULL +122 120 NULL NULL NULL NULL NULL NULL NULL NULL +122 121 NULL NULL NULL NULL NULL NULL NULL NULL +122 122 NULL NULL NULL NULL NULL NULL NULL NULL +122 123 NULL NULL NULL NULL NULL NULL NULL NULL +123 120 NULL NULL NULL NULL NULL NULL NULL NULL +123 121 NULL NULL NULL NULL NULL NULL NULL NULL +123 122 NULL NULL NULL NULL NULL NULL NULL NULL +123 123 NULL NULL NULL NULL NULL NULL NULL NULL DROP TABLE gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry; USE gis_ogs; # Lakes diff --git a/mysql-test/t/dyncol.test b/mysql-test/t/dyncol.test index c817e7209f6..12bb2f0690a 100644 --- a/mysql-test/t/dyncol.test +++ b/mysql-test/t/dyncol.test @@ -604,6 +604,29 @@ drop view v1; drop table t1; --echo # +--echo # MDEV-4811: Assertion `offset < 0x1f' fails in type_and_offset_store +--echo # on COLUMN_ADD +--echo # + +CREATE TABLE t1 (dyn TINYBLOB) ENGINE=MyISAM; +INSERT INTO t1 SET dyn = COLUMN_CREATE( 40, REPEAT('a', 233), 4, REPEAT('b', 322) ); +--error ER_DYN_COL_WRONG_FORMAT +SELECT COLUMN_ADD( dyn, 6, REPEAT('x',80), 4, REPEAT('y',215) AS INTEGER ) FROM t1; + +DROP table t1; + +--echo # +--echo # MDEV-4812: Valgrind warnings (Invalid write) in +--echo # dynamic_column_update_many on COLUMN_ADD +--echo # +CREATE TABLE t1 (dyncol TINYBLOB) ENGINE=MyISAM; + +INSERT INTO t1 SET dyncol = COLUMN_CREATE( 7, REPEAT('k',487), 209, REPEAT('x',464) ); +--error 0,ER_DYN_COL_WRONG_FORMAT +SELECT COLUMN_ADD( dyncol, 7, '22:22:22', 8, REPEAT('x',270) AS CHAR ) FROM t1; +DROP table t1; + +--echo # --echo # end of 5.3 tests --echo # diff --git a/mysql-test/t/func_set.test b/mysql-test/t/func_set.test index c3428c2a868..13f8661db49 100644 --- a/mysql-test/t/func_set.test +++ b/mysql-test/t/func_set.test @@ -99,6 +99,23 @@ SELECT CONVERT( a USING latin1 ) FROM t2; DROP TABLE t1, t2; --echo # +--echo # Start of 5.3 tests +--echo # + +--echo # +--echo # MDEV-4512 Valgrind warnings in my_long10_to_str_8bit on INTERVAL and DATE_ADD with incorrect types +--echo # +CREATE TABLE t1 (pk INT PRIMARY KEY); +INSERT INTO t1 VALUES (10),(11); +SELECT INTERVAL( 9, 1, DATE_ADD( pk, INTERVAL pk MINUTE_SECOND ), 9, 8, 3, 5, 2, 1 ) FROM t1; +DROP TABLE t1; + +--echo # +--echo # End of 5.3 tests +--echo # + + +--echo # --echo # BUG#59405: FIND_IN_SET won't work normaly after upgrade from 5.1 to 5.5 --echo # diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test index 24885c056b8..b0000b2b943 100644 --- a/mysql-test/t/join_outer.test +++ b/mysql-test/t/join_outer.test @@ -1670,4 +1670,21 @@ SELECT a.* FROM t1 a LEFT JOIN t1 b ON a.id = b.id DROP TABLE t1; +--echo # +--echo # MDEV-4817: Optimizer fails to optimize expression of the form 'FOO' IS NULL +--echo # +create table t0 (a int not null); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +alter table t0 add person_id varchar(255) not null; +create table t1 (pk int not null primary key); +insert into t1 select A.a + 10*B.a from t0 A, t0 B; + +explain select * from t1 left join t0 on t0.a=t1.pk where t0.person_id='fooo' or 'xyz' IS NULL; +explain select * from t1 left join t0 on t0.a=t1.pk where t0.person_id='fooo'; +explain select * from t1 left join t0 on t0.a=t1.pk where t0.person_id='fooo' or t0.person_id='bar'; + +drop table t0, t1; + + SET optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/t/lock_multi.test b/mysql-test/t/lock_multi.test index fc507608bdb..753b6bd6379 100644 --- a/mysql-test/t/lock_multi.test +++ b/mysql-test/t/lock_multi.test @@ -57,7 +57,7 @@ drop table t1; connection locker; create table t1(n int); -insert into t1 values (1); +insert into t1 values (1),(2); connection locker2; select get_lock("mysqltest_lock", 100); connection locker; diff --git a/mysql-test/t/mysql.test b/mysql-test/t/mysql.test index 662cd4c7c0c..2b4b1e69ab6 100644 --- a/mysql-test/t/mysql.test +++ b/mysql-test/t/mysql.test @@ -603,5 +603,10 @@ delimiter EOF --exec $MYSQL < $MYSQLTEST_VARDIR/tmp/13639125.sql +# +# --skip-column-names and alignment +# +--exec $MYSQL -t -N -e "SELECT 'a' union select 'aaaaaaaaaaaaaaaaa'" + --echo --echo End of tests diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index 6a82a2901c5..80ca862b74e 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -4518,3 +4518,34 @@ DROP TABLE t1, t2; --echo End of 5.3 tests +--echo # +--echo # mysql BUG#1271 Undefined variable in PASSWORD() +--echo # function is not handled correctly +--echo # + +create table t1 ( +name VARCHAR(50) NOT NULL PRIMARY KEY, +pw VARCHAR(41) NOT NULL); + +INSERT INTO t1 (name, pw) +VALUES ('tom', PASSWORD('my_pw')); + +SET @pass='my_pw'; +SET @wrong='incorrect'; + +select * from t1; +select length(PASSWORD(@pass)); + + +SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@pass); +SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@wrong); +SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@undefined); + + +select (SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@pass)); +select (SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@wrong)); +select (SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@undefined)); + +drop table t1; + +--echo End of 10.0 tests diff --git a/mysql-test/t/sp_notembedded.test b/mysql-test/t/sp_notembedded.test index 447a1db762e..c950af7eb1b 100644 --- a/mysql-test/t/sp_notembedded.test +++ b/mysql-test/t/sp_notembedded.test @@ -248,7 +248,7 @@ create procedure p1() update t1 set value='updated' where value='old'; # load the procedure into sp cache and execute once call p1(); -insert into t1 (value) values ("old"); +insert into t1 (value) values ("old"),("irrelevant"); connect (rl_holder, localhost, root,,); connect (rl_acquirer, localhost, root,,); diff --git a/mysql-test/t/timezone2.test b/mysql-test/t/timezone2.test index c4445da107c..7764b39bf33 100644 --- a/mysql-test/t/timezone2.test +++ b/mysql-test/t/timezone2.test @@ -284,3 +284,19 @@ SELECT CONVERT_TZ(1, 1, a) FROM t1; DROP TABLE t1; --echo End of 5.1 tests + + +--echo # +--echo # Start of 5.3 tests +--echo # + +--echo # +--echo # MDEV-4653 Wrong result for CONVERT_TZ(TIME('00:00:00'),'+00:00','+7:5') +--echo # + +SELECT CONVERT_TZ(TIME('00:00:00'),'+00:00','+7:5'); +SELECT CONVERT_TZ(TIME('2010-01-01 00:00:00'),'+00:00','+7:5'); + +--echo # +--echo # End of 5.3 tests +--echo # diff --git a/mysql-test/t/type_time.test b/mysql-test/t/type_time.test index 19e0c45fbc0..1dd0f4363b9 100644 --- a/mysql-test/t/type_time.test +++ b/mysql-test/t/type_time.test @@ -129,6 +129,12 @@ drop table t1; SELECT CONVERT_TZ(GREATEST(TIME('00:00:00'),TIME('00:00:00')),'+00:00','+7:5'); --echo # +--echo # MDEV-4652 Wrong result for CONCAT(GREATEST(TIME('00:00:01'),TIME('00:00:00'))) +--echo # +SELECT CONCAT(GREATEST(TIME('00:00:01'),TIME('00:00:00'))); +SELECT CONCAT(GREATEST(TIME('32 00:00:01'),TIME('00:00:00'))); + +--echo # --echo # End of 5.3 tests --echo # |