diff options
Diffstat (limited to 'mysql-test/t')
-rw-r--r-- | mysql-test/t/alter_table.test | 2 | ||||
-rw-r--r-- | mysql-test/t/check_constraint.test | 11 | ||||
-rw-r--r-- | mysql-test/t/count_distinct.test | 29 | ||||
-rw-r--r-- | mysql-test/t/date_formats.test | 9 | ||||
-rw-r--r-- | mysql-test/t/func_math.test | 4 | ||||
-rw-r--r-- | mysql-test/t/func_misc.test | 11 | ||||
-rw-r--r-- | mysql-test/t/insert.test | 29 | ||||
-rw-r--r-- | mysql-test/t/locale.test | 17 | ||||
-rw-r--r-- | mysql-test/t/log_tables-big.test | 9 | ||||
-rw-r--r-- | mysql-test/t/partition_symlink.test | 3 | ||||
-rw-r--r-- | mysql-test/t/range_interrupted-13751.test | 25 | ||||
-rw-r--r-- | mysql-test/t/symlink.test | 25 | ||||
-rw-r--r-- | mysql-test/t/win.test | 71 | ||||
-rw-r--r-- | mysql-test/t/win_as_arg_to_aggregate_func.test | 139 | ||||
-rw-r--r-- | mysql-test/t/win_insert_select.test | 6 |
15 files changed, 376 insertions, 14 deletions
diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test index ca2f73db5ca..487990b61cd 100644 --- a/mysql-test/t/alter_table.test +++ b/mysql-test/t/alter_table.test @@ -1852,6 +1852,7 @@ create table t1 (a int, b int, check(a>b)); alter table t1 drop column a; --error ER_BAD_FIELD_ERROR alter table t1 drop column b, add column b bigint first; +alter table t1 drop column a, drop constraint constraint_1; show create table t1; drop table t1; @@ -1873,5 +1874,6 @@ drop table t1; create table t1 (a int, b int, c int, unique(a,b)); --error ER_KEY_COLUMN_DOES_NOT_EXITS alter table t1 drop column a; +alter table t1 drop column a, drop index a; show create table t1; drop table t1; diff --git a/mysql-test/t/check_constraint.test b/mysql-test/t/check_constraint.test index 43b4417cfa3..f72ce38087e 100644 --- a/mysql-test/t/check_constraint.test +++ b/mysql-test/t/check_constraint.test @@ -92,3 +92,14 @@ create or replace table t1( c1 int auto_increment primary key, check( c1 > 0 or create table t1 (a int check (@b in (select user from mysql.user))); --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED create table t1 (a int check (a > @b)); + +# +# MDEV-13596 CHECK constraints disallow NULL to pass through, violating SQL +# +create table t1 (a int check (a = 1)); +insert t1 values (1); +--error ER_CONSTRAINT_FAILED +insert t1 values (2); +insert t1 values (NULL); +select * from t1; +drop table t1; diff --git a/mysql-test/t/count_distinct.test b/mysql-test/t/count_distinct.test index a00574b6cba..86045e862e7 100644 --- a/mysql-test/t/count_distinct.test +++ b/mysql-test/t/count_distinct.test @@ -121,5 +121,34 @@ drop table t1; set @@tmp_table_size = default; # +# MDEV-13457: Wrong result for aggregate function with distinct clause when the value for +# tmp_table_size is small +# + +create table t1 ( +a VARCHAR(1020), +b int +); +insert into t1 values +( 0 , 1 ), +( 1 , 2 ), +( 2 , 3 ), +( 3 , 4 ), +( 4 , 5 ), +( 5 , 6 ), +( 6 , 7 ), +( 7 , 8 ), +( 8 , 9 ), +( 9 , 10 ), +( 0 , 11 ), +( 1 , 12 ), +( 2 , 13 ), +( 3 , 14 ); +set @@tmp_table_size=1024; +select count(distinct a) from t1; +drop table t1; +set @@tmp_table_size = default; + +# # End of 5.5 tests # diff --git a/mysql-test/t/date_formats.test b/mysql-test/t/date_formats.test index 972543aefc2..3bf6fabbf6d 100644 --- a/mysql-test/t/date_formats.test +++ b/mysql-test/t/date_formats.test @@ -366,3 +366,12 @@ SET NAMES latin1; --echo # --echo # End of 5.1 tests --echo # + +# +# TIME_FORMAT and non-time format specifiers +# +select time_format('2001-01-01 02:02:02', '%d.%m.%Y'); +select time_format('2001-01-01 02:02:02', '%d %T'); +select time_format('01 02:02:02', '%d %T'); +select time_format('01 02:02:02', '%T'); +select time_format('2001-01-01 02:02:02', '%T'); diff --git a/mysql-test/t/func_math.test b/mysql-test/t/func_math.test index ee70102c496..83e345ec890 100644 --- a/mysql-test/t/func_math.test +++ b/mysql-test/t/func_math.test @@ -609,6 +609,10 @@ select 0=0, 0=-0, 0.0= -0.0, 0.0 = -(0.0), 0.0E1=-0.0E1, 0.0E1=-(0.0E1); select CRC32(NULL), CRC32(''), CRC32('MySQL'), CRC32('mysql'), CRC32('01234567'), CRC32('012345678'); +# +# MDEV-13673 Bad result in view +# +explain extended select (3-2)+1, (3/2)*1, 3-(2+1), 3/(2*1); --echo # --echo # Start of 10.3 tests diff --git a/mysql-test/t/func_misc.test b/mysql-test/t/func_misc.test index c661819424a..ac983048129 100644 --- a/mysql-test/t/func_misc.test +++ b/mysql-test/t/func_misc.test @@ -1092,6 +1092,17 @@ select release_lock('test'); --echo # -- Done. --echo + +--echo # +--echo # MDEV-13685 Can not replay binary log due to Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation 'concat' +--echo # +SET NAMES utf8; +SELECT COERCIBILITY(NAME_CONST('name','test')); +SELECT COERCIBILITY(NAME_CONST('name',TIME'00:00:00')); +SELECT COERCIBILITY(NAME_CONST('name',15)); +SELECT CONCAT(NAME_CONST('name',15),'오'); +SET NAMES latin1; + --echo # --echo # Start of 10.2 tests --echo # diff --git a/mysql-test/t/insert.test b/mysql-test/t/insert.test index 206c5553100..80caefa5a0f 100644 --- a/mysql-test/t/insert.test +++ b/mysql-test/t/insert.test @@ -573,3 +573,32 @@ insert ignore into t1 values (1,12) on duplicate key update f2=13; set @@old_mode=""; insert ignore into t1 values (1,12); DROP TABLE t1; + +--echo # +--echo # MDEV-13290 Assertion Assertion `!is_set() || (m_status == DA_OK_BULK +--echo # && is_bulk_op())' or `! is_set()' failed +--echo # + +SET @save_mode= @@sql_mode; +SET sql_mode= 'STRICT_ALL_TABLES'; +CREATE TABLE t1 (f1 INT DEFAULT 0, f2 INT); +CREATE ALGORITHM = MERGE VIEW v1 AS SELECT f1, f2 FROM t1 WHERE f1 = 'x' WITH CHECK OPTION; +--error ER_TRUNCATED_WRONG_VALUE +REPLACE INTO v1 SET f2 = 1; +SELECT * from t1; +drop view v1; +CREATE ALGORITHM = MERGE VIEW v1 AS SELECT f1, f2 FROM t1 WHERE f1 = cast('' as decimal) WITH CHECK OPTION; +--error ER_TRUNCATED_WRONG_VALUE +REPLACE INTO v1 SET f2 = 1; +SELECT * from t1; +drop view v1; +SELECT 0,0 INTO OUTFILE 't1.txt'; +CREATE ALGORITHM = MERGE VIEW v1 AS SELECT f1, f2 FROM t1 WHERE f1 = 'x' WITH CHECK OPTION; +--error ER_TRUNCATED_WRONG_VALUE +LOAD DATA INFILE 't1.txt' INTO TABLE v1; +SELECT * from t1; +let $MYSQLD_DATADIR= `select @@datadir`; +remove_file $MYSQLD_DATADIR/test/t1.txt; +drop view v1; +drop table t1; +SET @@sql_mode= @save_mode; diff --git a/mysql-test/t/locale.test b/mysql-test/t/locale.test index b1fbc40f0c8..4570b968ecf 100644 --- a/mysql-test/t/locale.test +++ b/mysql-test/t/locale.test @@ -134,7 +134,22 @@ SELECT * FROM non_existent; SET lc_time_names=@old_50915_lc_time_names; +--echo # +--echo # End of 10.0 tests +--echo # + +--echo # +--echo # End of 10.1 tests +--echo # + +# Item::print +create view v1 as select + format(123456789,2) as b, + format(123456789,2,'rm_CH') as b1; +select * from v1; +show create view v1; +drop view v1; --echo # ---echo # End of 5.6 tests +--echo # End of 10.2 tests --echo # diff --git a/mysql-test/t/log_tables-big.test b/mysql-test/t/log_tables-big.test index 8936a163d73..fa8810ecd3b 100644 --- a/mysql-test/t/log_tables-big.test +++ b/mysql-test/t/log_tables-big.test @@ -7,6 +7,7 @@ # check that CSV engine was compiled in --source include/have_csv.inc +set @log_output.saved = @@global.log_output; set @@global.log_output = 'TABLE'; connect (con1,localhost,root,,); @@ -21,13 +22,13 @@ select get_lock('bug27638', 1); connection con2; set session long_query_time=1; select get_lock('bug27638', 2); -select if (query_time >= '00:00:01', 'OK', 'WRONG') as qt, sql_text from mysql.slow_log +select if (query_time >= '00:00:01', 'OK', concat('WRONG: ',query_time)) as qt, sql_text from mysql.slow_log where sql_text = 'select get_lock(\'bug27638\', 2)'; select get_lock('bug27638', 60); -select if (query_time >= '00:00:59', 'OK', 'WRONG') as qt, sql_text from mysql.slow_log +select if (query_time >= '00:00:59', 'OK', concat('WRONG: ',query_time)) as qt, sql_text from mysql.slow_log where sql_text = 'select get_lock(\'bug27638\', 60)'; select get_lock('bug27638', 101); -select if (query_time >= '00:01:40', 'OK', 'WRONG') as qt, sql_text from mysql.slow_log +select if (query_time >= '00:01:40', 'OK', concat('WRONG: ',query_time)) as qt, sql_text from mysql.slow_log where sql_text = 'select get_lock(\'bug27638\', 101)'; connection con1; select release_lock('bug27638'); @@ -36,4 +37,4 @@ connection default; disconnect con1; disconnect con2; -set @@global.log_output=default; +set @@global.log_output = @log_output.saved; diff --git a/mysql-test/t/partition_symlink.test b/mysql-test/t/partition_symlink.test index f2e3eba5de6..8f6e837299a 100644 --- a/mysql-test/t/partition_symlink.test +++ b/mysql-test/t/partition_symlink.test @@ -38,11 +38,10 @@ SHOW CREATE TABLE t1; --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR SHOW CREATE TABLE t2; INSERT INTO t1 VALUES (0), (1), (2); ---error ER_TABLES_DIFFERENT_METADATA ALTER TABLE t1 EXCHANGE PARTITION p0 WITH TABLE t2; --error ER_TABLES_DIFFERENT_METADATA ALTER TABLE t1 EXCHANGE PARTITION p2 WITH TABLE t2; ---sorted_result +SELECT * FROM t1; SELECT * FROM t2; DROP TABLE t1, t2; # skipped because of bug#52354 diff --git a/mysql-test/t/range_interrupted-13751.test b/mysql-test/t/range_interrupted-13751.test new file mode 100644 index 00000000000..000a46383e8 --- /dev/null +++ b/mysql-test/t/range_interrupted-13751.test @@ -0,0 +1,25 @@ +source include/have_debug.inc; +# +# MDEV-13751 Interrupted SELECT fails with 1030: 'Got error 1 "Operation not permitted" from storage engine MyISAM' +# +CREATE TABLE t1 (i INT AUTO_INCREMENT, c VARCHAR(1), KEY(i), KEY(c,i)) ENGINE=MyISAM; +INSERT INTO t1 (c) VALUES ('a'),('b'),('c'),('d'); +INSERT INTO t1 (c) SELECT c FROM t1; +INSERT INTO t1 (c) SELECT c FROM t1; +INSERT INTO t1 (c) SELECT c FROM t1; +INSERT INTO t1 (c) SELECT c FROM t1; +INSERT INTO t1 (c) SELECT c FROM t1; +INSERT INTO t1 (c) SELECT c FROM t1; + +set @old_dbug=@@session.debug_dbug; +set debug_dbug="+d,kill_join_init_read_record"; + +--error ER_QUERY_INTERRUPTED +SELECT 1 FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 +WHERE alias1.c = alias2.c OR alias1.i <= 1 +; + +set debug_dbug=@old_dbug; + +DROP TABLE t1; + diff --git a/mysql-test/t/symlink.test b/mysql-test/t/symlink.test index e17ea07ca3c..cf95d4cb938 100644 --- a/mysql-test/t/symlink.test +++ b/mysql-test/t/symlink.test @@ -316,3 +316,28 @@ eval CREATE TABLE test.t1(id INT(11)) ENGINE MYISAM DATA DIRECTORY "$MYSQLTEST_VARDIR/tmp"; DROP TABLE test.t1; +use test; + +# +# End of 5.5 tests +# + +# +# End of 10.0 tests +# + +# +# MDEV-13636 ALTER TABLE ... DELAY_KEY_WRITE=1 creates table copy for MyISAM table with DATA DIRECTORY/INDEX DIRECTORY options +# +replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR; +eval +create table t1(c1 int, c2 int, c3 varchar(100)) engine=MyISAM data directory='$MYSQL_TMP_DIR' index directory = '$MYSQL_TMP_DIR'; +insert t1 values (1,2,3), (2,3,4), (3,4,5), (4,5,6), (5,6,7), (6,7,8), (7,8,9); +alter online table t1 delay_key_write=1; +replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR; +show create table t1; +drop table t1; + +# +# End of 10.1 tests +# diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test index c2022499acf..c353cd8b599 100644 --- a/mysql-test/t/win.test +++ b/mysql-test/t/win.test @@ -1966,10 +1966,6 @@ SELECT i AS fld FROM t1 UNION SELECT COUNT(*) AS fld FROM t1; DROP TABLE t1; --echo # ---echo # Start of 10.3 tests ---echo # - ---echo # --echo # MDEV-13240 Wrong warning with MAX(datetime_field) OVER (...) --echo # @@ -1977,3 +1973,70 @@ CREATE TABLE t1 (dt DATETIME); INSERT INTO t1 VALUES ('2017-05-17'); SELECT MAX(dt) OVER (ORDER BY dt ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) FROM t1; DROP TABLE t1; + +--echo # +--echo # MDEV-13358 FIRST_VALUE throws SQL Error (1292): Incorrect datetime value +--echo # +CREATE TABLE IF NOT EXISTS `fv_test` ( + `SOME_DATE` datetime NOT NULL + ); + +INSERT INTO `fv_test` (`SOME_DATE`) VALUES ('2017-07-20 12:47:56'); + +CREATE TABLE fv_result +SELECT +FIRST_VALUE(SOME_DATE) OVER(ORDER BY SOME_DATE DESC) AS somedate +FROM fv_test; + +SHOW CREATE TABLE fv_result; + +SELECT * FROM fv_result; + +DROP TABLE fv_test, fv_result; + +--echo # +--echo # MDEV-13649: Server crashes in set_field_to_null_with_conversions or in Field::set_notnull +--echo # + +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (0),(1),(2); +SELECT LEAD(a) OVER (PARTITION BY a) as lead, + a AND LEAD(a) OVER (PARTITION BY a) AS a_and_lead_part +FROM t1; + +SELECT a OR LEAD(a) OVER (ORDER BY a) AS a_or_lead_order +FROM t1 +ORDER BY a; + +SELECT a AND LEAD(a) OVER (ORDER BY a) AS a_and_lead_order +FROM t1 +ORDER BY a; + +SELECT a XOR LEAD(a) OVER (ORDER BY a) AS a_xor_lead_order +FROM t1 +ORDER BY a; + +SELECT NOT LEAD(a) OVER (ORDER BY a) AS not_lead_order +FROM t1 +ORDER BY a; + +SELECT LEAD(a) OVER (ORDER BY a) is not null AS is_not_null_lead_order +FROM t1 +ORDER BY a; + +drop table t1; + +--echo # +--echo # MDEV-13354: Server crashes in find_field_in_tables upon PS with window function and subquery +--echo # + +CREATE TABLE t1 (i INT, a char); +INSERT INTO t1 VALUES (1, 'a'),(2, 'b'); +PREPARE stmt FROM "SELECT row_number() over (partition by i order by i), i FROM (SELECT * from t1) as sq"; +EXECUTE stmt; + +DROP TABLE t1; + +--echo # +--echo # Start of 10.3 tests +--echo # diff --git a/mysql-test/t/win_as_arg_to_aggregate_func.test b/mysql-test/t/win_as_arg_to_aggregate_func.test new file mode 100644 index 00000000000..93c9238bedf --- /dev/null +++ b/mysql-test/t/win_as_arg_to_aggregate_func.test @@ -0,0 +1,139 @@ +create table t1 (i int); +insert into t1 values (5),(6),(0); + +--echo # +--echo # Try out all set functions with window functions as arguments. +--echo # Any such usage should return an error. +--echo # +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select MIN( SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select MIN(DISTINCT SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select MAX( SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select MAX(DISTINCT SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select SUM( SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select SUM(DISTINCT SUM(i) OVER (order by i) ) +from t1; + + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select AVG( SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select AVG(DISTINCT SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select COUNT( SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select COUNT(DISTINCT SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select BIT_AND( SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select BIT_OR( SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select BIT_XOR( SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select STD( SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select STDDEV( SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select STDDEV_POP( SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select STDDEV_SAMP(SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select VARIANCE(SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select VAR_POP(SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select VAR_SAMP(SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select GROUP_CONCAT(SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select GROUP_CONCAT(DISTINCT SUM(i) OVER (order by i) ) +from t1; + +--echo # +--echo # Test that partition instead of order by in over doesn't change result. +--echo # + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select SUM( SUM(i) OVER (PARTITION BY i) ) +from t1; + +--echo # +--echo # Test that no arguments in OVER() clause lead to crash in this case. +--echo # +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select SUM( SUM(i) OVER () ) +from t1; +drop table t1; + +-- echo # +-- echo # MDEV-13774: Server Crash on Execuate of SQL Statement +-- echo # +create table t1 (i int); +insert into t1 values (5),(6),(0); + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select SUM( + IF( SUM( IF(i,1,0)) OVER (PARTITION BY i) > 0 + AND + SUM( IF(i,1,0)) OVER (PARTITION BY i) > 0, + 1, + 0) ) +from t1; + +--echo # +--echo # A way to get the aggregation result. +--echo # + +select i, IF(SUM(IF(i,1,0)) OVER (PARTITION BY i) > 0 AND SUM( IF(i,1,0)) OVER (PARTITION BY i) > 0,1,0) AS if_col +from t1 +order by i; + +select sum(if_col) +from (select IF(SUM(IF(i,1,0)) OVER (PARTITION BY i) > 0 AND SUM( IF(i,1,0)) OVER (PARTITION BY i) > 0,1,0) AS if_col + from t1) tmp; +drop table t1; diff --git a/mysql-test/t/win_insert_select.test b/mysql-test/t/win_insert_select.test index a9e7e8f322f..6b2e0da4175 100644 --- a/mysql-test/t/win_insert_select.test +++ b/mysql-test/t/win_insert_select.test @@ -3,12 +3,12 @@ CREATE TABLE t1 (c1 INT, c2 VARCHAR(30)); PREPARE populate_table FROM "INSERT into t1 values (1, 'manual_insert_1'), (4, 'manual_insert_2')"; -INSERT INTO t1 SELECT row_number() over(), "should_have_NULL" FROM t1; -INSERT INTO t1 SELECT 1 + row_number() over(), "should_have_2" FROM t1; +INSERT INTO t1 SELECT row_number() over(), "should_not_add_any_rows" FROM t1; +INSERT INTO t1 SELECT 1 + row_number() over(), "should_not_add_any_rows" FROM t1; EXECUTE populate_table; -INSERT INTO t1 SELECT 10 + row_number() over(), "should repeat 4 times [11-14]" FROM t1; +INSERT INTO t1 SELECT 10 + row_number() over(), "should repeat 2 times [11-12]" FROM t1; SELECT c1, c2 FROM t1 ORDER BY c2, c1; |