diff options
Diffstat (limited to 'mysql-test/t')
27 files changed, 494 insertions, 14 deletions
diff --git a/mysql-test/t/bdb_gis.test b/mysql-test/t/bdb_gis.test index 88dcbb7cbe9..cb6d0683874 100644 --- a/mysql-test/t/bdb_gis.test +++ b/mysql-test/t/bdb_gis.test @@ -1,3 +1,4 @@ -- source include/have_bdb.inc SET storage_engine=bdb; --source include/gis_generic.inc +--source include/gis_keys.inc diff --git a/mysql-test/t/ctype_ucs.test b/mysql-test/t/ctype_ucs.test index bca3a9c3a96..5525a5beb6f 100644 --- a/mysql-test/t/ctype_ucs.test +++ b/mysql-test/t/ctype_ucs.test @@ -651,4 +651,9 @@ select * from t1 where a=if(b<10,_ucs2 0x00C0,_ucs2 0x0062); select * from t1 where a=if(b<10,_ucs2 0x0062,_ucs2 0x00C0); drop table t1; +# +# Bug#30981 CHAR(0x41 USING ucs2) doesn't add leading zero +# +select hex(char(0x41 using ucs2)); + --echo End of 5.0 tests diff --git a/mysql-test/t/ctype_utf8.test b/mysql-test/t/ctype_utf8.test index f8eed0bae9a..0ae4b2be0ca 100644 --- a/mysql-test/t/ctype_utf8.test +++ b/mysql-test/t/ctype_utf8.test @@ -1403,3 +1403,30 @@ SELECT b FROM t2 UNION SELECT c FROM t1; SELECT i FROM t2 UNION SELECT c FROM t1; DROP TABLE t1, t2; + +# +# Bug#30982: CHAR(..USING..) can return a not-well-formed string +# Bug #30986: Character set introducer followed by a HEX string can return bad result +# +set sql_mode=traditional; +select hex(char(0xFF using utf8)); +select hex(convert(0xFF using utf8)); +--error ER_INVALID_CHARACTER_STRING +select hex(_utf8 0x616263FF); +--error ER_INVALID_CHARACTER_STRING +select hex(_utf8 X'616263FF'); +--error ER_INVALID_CHARACTER_STRING +select hex(_utf8 B'001111111111'); +--error ER_INVALID_CHARACTER_STRING +select (_utf8 X'616263FF'); +set sql_mode=default; +select hex(char(0xFF using utf8)); +select hex(convert(0xFF using utf8)); +--error ER_INVALID_CHARACTER_STRING +select hex(_utf8 0x616263FF); +--error ER_INVALID_CHARACTER_STRING +select hex(_utf8 X'616263FF'); +--error ER_INVALID_CHARACTER_STRING +select hex(_utf8 B'001111111111'); +--error ER_INVALID_CHARACTER_STRING +select (_utf8 X'616263FF'); diff --git a/mysql-test/t/delete.test b/mysql-test/t/delete.test index 8a03cb6c715..602e30687c8 100644 --- a/mysql-test/t/delete.test +++ b/mysql-test/t/delete.test @@ -277,3 +277,18 @@ 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 +# + +CREATE FUNCTION f1() RETURNS INT RETURN 1; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (0); +--error 1318 +DELETE FROM t1 ORDER BY (f1(10)) LIMIT 1; +DROP TABLE t1; +DROP FUNCTION f1; + +--echo End of 5.0 tests diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test index 4d8a8e3c3af..4e79fac584f 100644 --- a/mysql-test/t/derived.test +++ b/mysql-test/t/derived.test @@ -211,7 +211,8 @@ drop table t2; # select list counter # CREATE TABLE `t1` ( `itemid` int(11) NOT NULL default '0', `grpid` varchar(15) NOT NULL default '', `vendor` int(11) NOT NULL default '0', `date_` date NOT NULL default '0000-00-00', `price` decimal(12,2) NOT NULL default '0.00', PRIMARY KEY (`itemid`,`grpid`,`vendor`,`date_`), KEY `itemid` (`itemid`,`vendor`), KEY `itemid_2` (`itemid`,`date_`)); -insert into t1 values (128, 'rozn', 2, now(), 10),(128, 'rozn', 1, now(), 10); +insert into t1 values (128, 'rozn', 2, curdate(), 10), + (128, 'rozn', 1, curdate(), 10); SELECT MIN(price) min, MAX(price) max, AVG(price) avg FROM (SELECT SUBSTRING( MAX(concat(date_,";",price)), 12) price FROM t1 WHERE itemid=128 AND grpid='rozn' GROUP BY itemid, grpid, vendor) lastprices; DROP TABLE t1; diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index 2293ac71454..8c020eb3dc8 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -860,5 +860,18 @@ SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1 and b > 1; DROP TABLE t1, t2, t3, t4, t5; +# +# Bug #31156: mysqld: item_sum.cc:918: +# virtual bool Item_sum_distinct::setup(THD*): Assertion +# + +CREATE TABLE t1 (a INT); +INSERT INTO t1 values (),(),(); +SELECT (SELECT SLEEP(0) FROM t1 ORDER BY AVG(DISTINCT a) ) as x FROM t1 + GROUP BY x; +SELECT 1 FROM t1 GROUP BY (SELECT SLEEP(0) FROM t1 ORDER BY AVG(DISTINCT a) ); + +DROP TABLE t1; + ### --echo End of 5.0 tests diff --git a/mysql-test/t/func_math.test b/mysql-test/t/func_math.test index 2ba07dfc581..9f12fdd696e 100644 --- a/mysql-test/t/func_math.test +++ b/mysql-test/t/func_math.test @@ -205,4 +205,29 @@ select mod(cast(-2 as unsigned), 3), mod(18446744073709551614, 3), mod(-2, 3); select mod(5, cast(-2 as unsigned)), mod(5, 18446744073709551614), mod(5, -2); select pow(cast(-2 as unsigned), 5), pow(18446744073709551614, 5), pow(-2, 5); +# +# Bug #30587: mysql crashes when trying to group by TIME div NUMBER +# + +CREATE TABLE t1 (a timestamp, b varchar(20), c bit(1)); +INSERT INTO t1 VALUES('1998-09-23', 'str1', 1), ('2003-03-25', 'str2', 0); +SELECT a DIV 900 y FROM t1 GROUP BY y; +SELECT DISTINCT a DIV 900 y FROM t1; +SELECT b DIV 900 y FROM t1 GROUP BY y; +SELECT c DIV 900 y FROM t1 GROUP BY y; +DROP TABLE t1; + +CREATE TABLE t1(a LONGBLOB); +INSERT INTO t1 VALUES('1'),('2'),('3'); +SELECT DISTINCT (a DIV 254576881) FROM t1; +SELECT (a DIV 254576881) FROM t1 UNION ALL + SELECT (a DIV 254576881) FROM t1; +DROP TABLE t1; + +CREATE TABLE t1(a SET('a','b','c')); +INSERT INTO t1 VALUES ('a'); +SELECT a DIV 2 FROM t1 UNION SELECT a DIV 2 FROM t1; +DROP TABLE t1; + + --echo End of 5.0 tests diff --git a/mysql-test/t/func_misc.test b/mysql-test/t/func_misc.test index 8ff62f68e45..2c34f77b1ff 100644 --- a/mysql-test/t/func_misc.test +++ b/mysql-test/t/func_misc.test @@ -189,4 +189,20 @@ drop table table_26093; drop function func_26093_a; drop function func_26093_b; +# +# Bug #30832: Assertion + crash with select name_const('test',now()); +# +--error ER_WRONG_ARGUMENTS +SELECT NAME_CONST('test', NOW()); +--error ER_WRONG_ARGUMENTS +SELECT NAME_CONST('test', UPPER('test')); + +SELECT NAME_CONST('test', NULL); +SELECT NAME_CONST('test', 1); +SELECT NAME_CONST('test', -1); +SELECT NAME_CONST('test', 1.0); +SELECT NAME_CONST('test', -1.0); +SELECT NAME_CONST('test', 'test'); + --echo End of 5.0 tests + diff --git a/mysql-test/t/func_regexp.test b/mysql-test/t/func_regexp.test index 23070c71fe9..5eff404bc0f 100644 --- a/mysql-test/t/func_regexp.test +++ b/mysql-test/t/func_regexp.test @@ -74,4 +74,13 @@ execute stmt1 using @a; deallocate prepare stmt1; drop table t1; -# End of 4.1 tests +--echo End of 4.1 tests + + +# +# Bug #31440: 'select 1 regex null' asserts debug server +# + +SELECT 1 REGEXP NULL; + +--echo End of 5.0 tests diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index 1a32580aa0f..e04276fa305 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -1124,4 +1124,16 @@ SELECT SUBSTR(a,1,len) FROM t1; DROP TABLE t1; +# +# Bug #28850: Potential bugs related to the return type of the CHAR function +# + +CREATE TABLE t1 AS SELECT CHAR(0x414243) as c1; +SELECT HEX(c1) from t1; +DROP TABLE t1; + +CREATE VIEW v1 AS SELECT CHAR(0x414243) as c1; +SELECT HEX(c1) from v1; +DROP VIEW v1; + --echo End of 5.0 tests diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index 86c848983fa..c0a449ac3f4 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -545,6 +545,16 @@ DROP TABLE testBug8868; SET NAMES DEFAULT; +# +# Bug #31160: MAKETIME() crashes server when returning NULL in ORDER BY using +# filesort +# +CREATE TABLE t1 ( + a TIMESTAMP +); +INSERT INTO t1 VALUES (now()), (now()); +SELECT 1 FROM t1 ORDER BY MAKETIME(1, 1, a); +DROP TABLE t1; # # Bug #19844 time_format in Union truncates values diff --git a/mysql-test/t/gis-rtree.test b/mysql-test/t/gis-rtree.test index 74b12caca41..e68e5039adf 100644 --- a/mysql-test/t/gis-rtree.test +++ b/mysql-test/t/gis-rtree.test @@ -797,6 +797,42 @@ UPDATE t1 set spatial_point=GeomFromText('POINT(41 46)') where c1 like 'f%'; CHECK TABLE t1 EXTENDED; DROP TABLE t1; +# +# Bug #30286 spatial index cause corruption and server crash! +# + +create table t1 (a geometry not null, spatial index(a)); +insert into t1 values (PointFromWKB(POINT(1.1517219314031e+164, 131072))); +insert into t1 values (PointFromWKB(POINT(9.1248812352444e+192, 2.9740338169556e+284))); +insert into t1 values (PointFromWKB(POINT(4.7783097267365e-299, -0))); +insert into t1 values (PointFromWKB(POINT(1.49166814624e-154, 2.0880974297595e-53))); +insert into t1 values (PointFromWKB(POINT(4.0917382598702e+149, 1.2024538023802e+111))); +insert into t1 values (PointFromWKB(POINT(2.0349165139404e+236, 2.9993936277913e-241))); +insert into t1 values (PointFromWKB(POINT(2.5243548967072e-29, 1.2024538023802e+111))); +insert into t1 values (PointFromWKB(POINT(0, 6.9835074892995e-251))); +insert into t1 values (PointFromWKB(POINT(2.0880974297595e-53, 3.1050361846014e+231))); +insert into t1 values (PointFromWKB(POINT(2.8728483499323e-188, 2.4600631144627e+260))); +insert into t1 values (PointFromWKB(POINT(3.0517578125e-05, 2.0349165139404e+236))); +insert into t1 values (PointFromWKB(POINT(1.1517219314031e+164, 1.1818212630766e-125))); +insert into t1 values (PointFromWKB(POINT(2.481040258324e-265, 5.7766220027675e-275))); +insert into t1 values (PointFromWKB(POINT(2.0880974297595e-53, 2.5243548967072e-29))); +insert into t1 values (PointFromWKB(POINT(5.7766220027675e-275, 9.9464647281957e+86))); +insert into t1 values (PointFromWKB(POINT(2.2181357552967e+130, 3.7857669957337e-270))); +insert into t1 values (PointFromWKB(POINT(4.5767114681874e-246, 3.6893488147419e+19))); +insert into t1 values (PointFromWKB(POINT(4.5767114681874e-246, 3.7537584144024e+255))); +insert into t1 values (PointFromWKB(POINT(3.7857669957337e-270, 1.8033161362863e-130))); +insert into t1 values (PointFromWKB(POINT(0, 5.8774717541114e-39))); +insert into t1 values (PointFromWKB(POINT(1.1517219314031e+164, 2.2761049594727e-159))); +insert into t1 values (PointFromWKB(POINT(6.243497100632e+144, 3.7857669957337e-270))); +insert into t1 values (PointFromWKB(POINT(3.7857669957337e-270, 2.6355494858076e-82))); +insert into t1 values (PointFromWKB(POINT(2.0349165139404e+236, 3.8518598887745e-34))); +insert into t1 values (PointFromWKB(POINT(4.6566128730774e-10, 2.0880974297595e-53))); +insert into t1 values (PointFromWKB(POINT(2.0880974297595e-53, 1.8827498946116e-183))); +insert into t1 values (PointFromWKB(POINT(1.8033161362863e-130, 9.1248812352444e+192))); +insert into t1 values (PointFromWKB(POINT(4.7783097267365e-299, 2.2761049594727e-159))); +insert into t1 values (PointFromWKB(POINT(1.94906280228e+289, 1.2338789709327e-178))); +drop table t1; + # End of 4.1 tests # diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test index 79743a0b8f4..b7da7db1d76 100644 --- a/mysql-test/t/gis.test +++ b/mysql-test/t/gis.test @@ -431,6 +431,14 @@ INSERT INTO t1 VALUES (NULL); SELECT * FROM t1; DROP TABLE t1; +# +# Bug #30955 geomfromtext() crasher +# +CREATE TABLE `t1` ( `col9` set('a'), `col89` date); +INSERT INTO `t1` VALUES ('','0000-00-00'); +select geomfromtext(col9,col89) as a from t1; +DROP TABLE t1; + --echo End of 4.1 tests # @@ -590,4 +598,6 @@ SELECT AsText(GeometryFromText(CONCAT( --enable_query_log SELECT 1; +-- source include/gis_keys.inc + --echo End of 5.0 tests diff --git a/mysql-test/t/grant2.test b/mysql-test/t/grant2.test index a3a8e2d5d53..e2d92ee58d4 100644 --- a/mysql-test/t/grant2.test +++ b/mysql-test/t/grant2.test @@ -585,5 +585,37 @@ drop user mysqltest_1@localhost; drop user mysqltest_2@localhost; +# +# Bug #30468: column level privileges not respected when joining tables +# +CREATE DATABASE db1; + +USE db1; +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1,1),(2,2); + +CREATE TABLE t2 (b INT, c INT); +INSERT INTO t2 VALUES (1,100),(2,200); + +GRANT SELECT ON t1 TO mysqltest1@localhost; +GRANT SELECT (b) ON t2 TO mysqltest1@localhost; + +connect (conn1,localhost,mysqltest1,,); +connection conn1; +USE db1; +--error ER_COLUMNACCESS_DENIED_ERROR +SELECT c FROM t2; +--error ER_COLUMNACCESS_DENIED_ERROR +SELECT * FROM t2; +--error ER_COLUMNACCESS_DENIED_ERROR +SELECT * FROM t1 JOIN t2 USING (b); + +connection default; +disconnect conn1; +DROP TABLE db1.t1, db1.t2; +DROP USER mysqltest1@localhost; +DROP DATABASE db1; + + --echo End of 5.0 tests diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test index e9a06c7186e..04ffd30ec62 100644 --- a/mysql-test/t/information_schema.test +++ b/mysql-test/t/information_schema.test @@ -1089,3 +1089,12 @@ show columns from t1; drop table t1; --echo End of 5.0 tests. + +# +# Bug#30079 A check for "hidden" I_S tables is flawed +# +--error 1109 +show fields from information_schema.table_names; +--error 1109 +show keys from information_schema.table_names; + diff --git a/mysql-test/t/innodb_gis.test b/mysql-test/t/innodb_gis.test index 142b526af92..024d17c5363 100644 --- a/mysql-test/t/innodb_gis.test +++ b/mysql-test/t/innodb_gis.test @@ -1,3 +1,4 @@ --source include/have_innodb.inc SET storage_engine=innodb; --source include/gis_generic.inc +--source include/gis_keys.inc diff --git a/mysql-test/t/insert_select.test b/mysql-test/t/insert_select.test index fcd07a21821..78a903e0d18 100644 --- a/mysql-test/t/insert_select.test +++ b/mysql-test/t/insert_select.test @@ -385,3 +385,15 @@ INSERT INTO t1 (prev_id) SELECT id SELECT * FROM t1; DROP TABLE t1,t2; + +--echo # +--echo # Bug#30384: Having SQL_BUFFER_RESULT option in the +--echo # CREATE .. KEY(..) .. SELECT led to creating corrupted index. +--echo # +create table t1(f1 int); +insert into t1 values(1),(2),(3); +create table t2 (key(f1)) engine=myisam select sql_buffer_result f1 from t1; +check table t2 extended; +drop table t1,t2; +--echo ################################################################## + diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test index 3c62577e781..6dba0a590d0 100644 --- a/mysql-test/t/mysqldump.test +++ b/mysql-test/t/mysqldump.test @@ -1577,5 +1577,22 @@ SELECT * FROM t2; DROP TABLE t1,t2; --echo # +--echo # Bug#29815: new option for suppressing last line of mysqldump: +--echo # "Dump completed on" +--echo # + +--echo # --skip-dump-date: +--replace_regex /-- [^D][^u][^m][^p].*// /\/\*!.*// +--exec $MYSQL_DUMP --skip-dump-date test + +--echo # --dump-date: +--replace_regex /-- [^D][^u][^m][^p].*// /\/\*!.*// / on [0-9 :-]+/ on DATE/ +--exec $MYSQL_DUMP --dump-date test + +--echo # --dump-date (default): +--replace_regex /-- [^D][^u][^m][^p].*// /\/\*!.*// / on [0-9 :-]+/ on DATE/ +--exec $MYSQL_DUMP test + +--echo # --echo # End of 5.0 tests --echo # diff --git a/mysql-test/t/null.test b/mysql-test/t/null.test index 65e09b006ec..2878b54c357 100644 --- a/mysql-test/t/null.test +++ b/mysql-test/t/null.test @@ -1,6 +1,6 @@ # Initialise --disable_warnings -drop table if exists t1; +drop table if exists t1, t2; --enable_warnings # @@ -231,4 +231,27 @@ drop table bug19145a; drop table bug19145b; drop table bug19145c; -# End of 4.1 tests +--echo # End of 4.1 tests + +--echo # +--echo # Bug #31471: decimal_bin_size: Assertion `scale >= 0 && +--echo # precision > 0 && scale <= precision' +--echo # + +CREATE TABLE t1 (a DECIMAL (1, 0) ZEROFILL, b DECIMAL (1, 0) ZEROFILL); +INSERT INTO t1 (a, b) VALUES (0, 0); + +CREATE TABLE t2 SELECT IFNULL(a, b) FROM t1; +DESCRIBE t2; +DROP TABLE t2; + +CREATE TABLE t2 SELECT IFNULL(a, NULL) FROM t1; +DESCRIBE t2; +DROP TABLE t2; + +CREATE TABLE t2 SELECT IFNULL(NULL, b) FROM t1; +DESCRIBE t2; + +DROP TABLE t1, t2; + +--echo # End of 5.0 tests diff --git a/mysql-test/t/olap.test b/mysql-test/t/olap.test index 05934bff492..1ac99d9c39f 100644 --- a/mysql-test/t/olap.test +++ b/mysql-test/t/olap.test @@ -358,3 +358,12 @@ SELECT * FROM (SELECT a, SUM(a) FROM t1 GROUP BY a WITH ROLLUP) as t; DROP TABLE t1; +--echo # +--echo # Bug#31095: Unexpected NULL constant caused server crash. +--echo # +create table t1(a int); +insert into t1 values (1),(2),(3); +select count(a) from t1 group by null with rollup; +drop table t1; +--echo ############################################################## + diff --git a/mysql-test/t/rpl_flush_log_loop.test b/mysql-test/t/rpl_flush_log_loop.test index 6e45047bd30..f0b368c285b 100644 --- a/mysql-test/t/rpl_flush_log_loop.test +++ b/mysql-test/t/rpl_flush_log_loop.test @@ -3,6 +3,9 @@ source include/master-slave.inc; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +show variables like 'relay_log%'; + connection slave; stop slave; --replace_result $MASTER_MYPORT MASTER_PORT diff --git a/mysql-test/t/type_date.test b/mysql-test/t/type_date.test index 02cd07e3c16..507537457d3 100644 --- a/mysql-test/t/type_date.test +++ b/mysql-test/t/type_date.test @@ -136,3 +136,58 @@ insert into t1 values (9912101,9912101,9912101); insert into t1 values (11111,11111,11111); select * from t1; drop table t1; + +# +# Bug #30942: select str_to_date from derived table returns varying results +# +CREATE TABLE t1 ( + a INT +); + +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (NULL); + +SELECT str_to_date( '', a ) FROM t1; +DROP TABLE t1; + + +# +# Bug #31221: Optimizer incorrectly identifies impossible WHERE clause +# + +CREATE TABLE t1 (a DATE, b int, PRIMARY KEY (a,b)); +INSERT INTO t1 VALUES (DATE(NOW()), 1); +SELECT COUNT(*) FROM t1 WHERE a = NOW(); +EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW(); +INSERT INTO t1 VALUES (DATE(NOW()), 2); +SELECT COUNT(*) FROM t1 WHERE a = NOW(); +EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW(); +SELECT COUNT(*) FROM t1 WHERE a = NOW() AND b = 1; +EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW() AND b = 1; +ALTER TABLE t1 DROP PRIMARY KEY; +SELECT COUNT(*) FROM t1 WHERE a = NOW(); +EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW(); + +DROP TABLE t1; + +# +# Bug #28687: Search fails on '0000-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 ('0000-00-00'),('0000-00-00'); +INSERT INTO t2 VALUES ('0000-00-00'),('0000-00-00'); +SELECT * FROM t1 WHERE a = '0000-00-00'; +SELECT * FROM t2 WHERE a = '0000-00-00'; +SET SQL_MODE=TRADITIONAL; +EXPLAIN SELECT * FROM t1 WHERE a = '0000-00-00'; +SELECT * FROM t1 WHERE a = '0000-00-00'; +SELECT * FROM t2 WHERE a = '0000-00-00'; +--error ER_TRUNCATED_WRONG_VALUE +INSERT INTO t1 VALUES ('0000-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 20733a14d46..08a94384aec 100644 --- a/mysql-test/t/type_datetime.test +++ b/mysql-test/t/type_datetime.test @@ -283,6 +283,43 @@ select * from t1 where f1 between 2002010 and 20070101000000; select * from t1 where f1 between 20020101 and 2007010100000; drop table t1; +--echo # +--echo # Bug#27216: functions with parameters of different date types may +--echo # return wrong type of the result. +--echo # +create table t1 (f1 date, f2 datetime, f3 varchar(20)); +create table t2 as select coalesce(f1,f1) as f4 from t1; +desc t2; +create table t3 as select coalesce(f1,f2) as f4 from t1; +desc t3; +create table t4 as select coalesce(f2,f2) as f4 from t1; +desc t4; +create table t5 as select coalesce(f1,f3) as f4 from t1; +desc t5; +create table t6 as select coalesce(f2,f3) as f4 from t1; +desc t6; +create table t7 as select coalesce(makedate(1997,1),f2) as f4 from t1; +desc t7; +create table t8 as select coalesce(cast('01-01-01' as datetime),f2) as f4 + from t1; +desc t8; +create table t9 as select case when 1 then cast('01-01-01' as date) + when 0 then cast('01-01-01' as date) end as f4 from t1; +desc t9; +create table t10 as select case when 1 then cast('01-01-01' as datetime) + when 0 then cast('01-01-01' as datetime) end as f4 from t1; +desc t10; +create table t11 as select if(1, cast('01-01-01' as datetime), + cast('01-01-01' as date)) as f4 from t1; +desc t11; +create table t12 as select least(cast('01-01-01' as datetime), + cast('01-01-01' as date)) as f4 from t1; +desc t12; +create table t13 as select ifnull(cast('01-01-01' as datetime), + cast('01-01-01' as date)) as f4 from t1; +desc t13; +drop tables t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13; +--echo ################################################################### # # Bug #31253: crash comparing datetime to double # Should return 1st row only. Crashes if NULL propagation fails. diff --git a/mysql-test/t/type_decimal.test b/mysql-test/t/type_decimal.test index 458583fca81..4d61350a613 100644 --- a/mysql-test/t/type_decimal.test +++ b/mysql-test/t/type_decimal.test @@ -410,6 +410,13 @@ SELECT ROUND(qty,3), dps, ROUND(qty,dps) FROM t1; DROP TABLE t1; # +# Bug#31019: MOD() function and operator crashes MySQL when +# divisor is very long and < 1 +# + +SELECT 1 % .123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS '%'; +SELECT MOD(1, .123456789123456789123456789123456789123456789123456789123456789123456789123456789) AS 'MOD()'; + # Bug #31227: memory overrun with decimal (6,6) and zerofill and group_concat # valgrind will complain about this (the group_concat(f2)) on unpatched mysqld. # @@ -419,3 +426,4 @@ select group_concat(f1),group_concat(f2) from t1; drop table t1; --echo End of 5.0 tests + diff --git a/mysql-test/t/variables.test b/mysql-test/t/variables.test index ccd487a72ea..63bc3c5e273 100644 --- a/mysql-test/t/variables.test +++ b/mysql-test/t/variables.test @@ -536,7 +536,14 @@ set @test = @@query_prealloc_size; set @@query_prealloc_size = @test; select @@query_prealloc_size = @test; -# End of 4.1 tests +# +# Bug#31588 buffer overrun when setting variables +# +# Buffer-size Off By One. Should throw valgrind-warning without fix #31588. +--error 1231 +set global sql_mode=repeat('a',80); + +--echo End of 4.1 tests # # Bug#6282 Packet error with SELECT INTO diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index fc3fdc932ba..0faa8e7a785 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -510,7 +510,7 @@ drop table t1; # create table t1 (a int, b int); create view v1 as select a, sum(b) from t1 group by a; --- error 1176 +--error ER_WRONG_USAGE select b from v1 use index (some_index) where b=1; drop view v1; drop table t1; @@ -2548,19 +2548,20 @@ CREATE TABLE t1( fName varchar(25) NOT NULL, lName varchar(25) NOT NULL, DOB date NOT NULL, + test_date date NOT NULL, uID int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY); -INSERT INTO t1(fName, lName, DOB) VALUES - ('Hank', 'Hill', '1964-09-29'), - ('Tom', 'Adams', '1908-02-14'), - ('Homer', 'Simpson', '1968-03-05'); +INSERT INTO t1(fName, lName, DOB, test_date) VALUES + ('Hank', 'Hill', '1964-09-29', '2007-01-01'), + ('Tom', 'Adams', '1908-02-14', '2007-01-01'), + ('Homer', 'Simpson', '1968-03-05', '2007-01-01'); CREATE VIEW v1 AS - SELECT (year(now())-year(DOB)) AS Age + SELECT (year(test_date)-year(DOB)) AS Age FROM t1 HAVING Age < 75; SHOW CREATE VIEW v1; -SELECT (year(now())-year(DOB)) AS Age FROM t1 HAVING Age < 75; +SELECT (year(test_date)-year(DOB)) AS Age FROM t1 HAVING Age < 75; SELECT * FROM v1; DROP VIEW v1; @@ -3415,5 +3416,45 @@ select table_name, is_updatable from information_schema.views drop view v1; drop table t1; +# +# Bug #28701: SELECTs from VIEWs completely ignore USE/FORCE KEY, allowing +# invalid statements +# + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); +CREATE VIEW v1 AS SELECT * FROM t1; +--error ER_WRONG_USAGE +SELECT * FROM v1 USE KEY(non_existant); +--error ER_WRONG_USAGE +SELECT * FROM v1 FORCE KEY(non_existant); +--error ER_WRONG_USAGE +SELECT * FROM v1 IGNORE KEY(non_existant); + +DROP VIEW v1; +DROP TABLE t1; + + +# +# Bug #28702: VIEWs defined with USE/FORCE KEY ignore that request +# +CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT, b INT NOT NULL DEFAULT 0, + PRIMARY KEY(a), KEY (b)); +INSERT INTO t1 VALUES (),(),(),(),(),(),(),(),(),(),(),(),(),(),(); +CREATE VIEW v1 AS SELECT * FROM t1 FORCE KEY (PRIMARY,b) ORDER BY a; +SHOW CREATE VIEW v1; +EXPLAIN SELECT * FROM v1; +CREATE VIEW v2 AS SELECT * FROM t1 USE KEY () ORDER BY a; +SHOW CREATE VIEW v2; +EXPLAIN SELECT * FROM v2; +CREATE VIEW v3 AS SELECT * FROM t1 IGNORE KEY (b) ORDER BY a; +SHOW CREATE VIEW v3; +EXPLAIN SELECT * FROM v3; + +DROP VIEW v1; +DROP VIEW v2; +DROP VIEW v3; +DROP TABLE t1; + --echo End of 5.0 tests. diff --git a/mysql-test/t/view_grant.test b/mysql-test/t/view_grant.test index a102f87c4e8..7f9eb4e1cff 100644 --- a/mysql-test/t/view_grant.test +++ b/mysql-test/t/view_grant.test @@ -1034,10 +1034,11 @@ GRANT SELECT ON db26813.t1 TO u26813@localhost; connect (u1,localhost,u26813,,db26813); connection u1; ---error 1142 +--error ER_SPECIFIC_ACCESS_DENIED_ERROR ALTER VIEW v1 AS SELECT f2 FROM t1; ---error 1142 +--error ER_SPECIFIC_ACCESS_DENIED_ERROR ALTER VIEW v2 AS SELECT f2 FROM t1; +--error ER_SPECIFIC_ACCESS_DENIED_ERROR ALTER VIEW v3 AS SELECT f2 FROM t1; connection root; @@ -1047,6 +1048,51 @@ DROP USER u26813@localhost; DROP DATABASE db26813; disconnect u1; +--echo # +--echo # Bug#29908: A user can gain additional access through the ALTER VIEW. +--echo # +connection root; +CREATE DATABASE mysqltest_29908; +USE mysqltest_29908; +CREATE TABLE t1(f1 INT, f2 INT); +CREATE USER u29908_1@localhost; +CREATE DEFINER = u29908_1@localhost VIEW v1 AS SELECT f1 FROM t1; +CREATE DEFINER = u29908_1@localhost SQL SECURITY INVOKER VIEW v2 AS + SELECT f1 FROM t1; +GRANT DROP, CREATE VIEW, SHOW VIEW ON mysqltest_29908.v1 TO u29908_1@localhost; +GRANT DROP, CREATE VIEW, SHOW VIEW ON mysqltest_29908.v2 TO u29908_1@localhost; +GRANT SELECT ON mysqltest_29908.t1 TO u29908_1@localhost; +CREATE USER u29908_2@localhost; +GRANT DROP, CREATE VIEW ON mysqltest_29908.v1 TO u29908_2@localhost; +GRANT DROP, CREATE VIEW, SHOW VIEW ON mysqltest_29908.v2 TO u29908_2@localhost; +GRANT SELECT ON mysqltest_29908.t1 TO u29908_2@localhost; + +connect (u2,localhost,u29908_2,,mysqltest_29908); +--error ER_SPECIFIC_ACCESS_DENIED_ERROR +ALTER VIEW v1 AS SELECT f2 FROM t1; +--error ER_SPECIFIC_ACCESS_DENIED_ERROR +ALTER VIEW v2 AS SELECT f2 FROM t1; +SHOW CREATE VIEW v2; + +connect (u1,localhost,u29908_1,,mysqltest_29908); +ALTER VIEW v1 AS SELECT f2 FROM t1; +SHOW CREATE VIEW v1; +ALTER VIEW v2 AS SELECT f2 FROM t1; +SHOW CREATE VIEW v2; + +connection root; +ALTER VIEW v1 AS SELECT f1 FROM t1; +SHOW CREATE VIEW v1; +ALTER VIEW v2 AS SELECT f1 FROM t1; +SHOW CREATE VIEW v2; + +DROP USER u29908_1@localhost; +DROP USER u29908_2@localhost; +DROP DATABASE mysqltest_29908; +disconnect u1; +disconnect u2; +--echo ####################################################################### + # # BUG#24040: Create View don't succed with "all privileges" on a database. # |