diff options
author | Sergei Golubchik <serg@mariadb.org> | 2022-07-29 12:39:37 +0200 |
---|---|---|
committer | Oleksandr Byelkin <sanja@mariadb.com> | 2022-08-10 15:02:56 +0200 |
commit | 4ce1470a700809c7b62ade40ddb95063a0e47402 (patch) | |
tree | aa384d4b82ecd6407043a73c661a02e1640bc13b | |
parent | 1c192843f2619f528aaf334d0346cf14976b3845 (diff) | |
download | mariadb-git-4ce1470a700809c7b62ade40ddb95063a0e47402.tar.gz |
cleanup: tests
sequence tests verify that one cannot change the structure
of the table. for that they need a valid alter table that
adds an index over an existing column. there's no column 'start'
in the table
-rw-r--r-- | mysql-test/main/alter_table.test | 6 | ||||
-rw-r--r-- | mysql-test/main/null.result | 50 | ||||
-rw-r--r-- | mysql-test/main/null.test | 113 | ||||
-rw-r--r-- | mysql-test/suite/sql_sequence/gtid.result | 2 | ||||
-rw-r--r-- | mysql-test/suite/sql_sequence/gtid.test | 2 | ||||
-rw-r--r-- | mysql-test/suite/sql_sequence/replication.result | 2 | ||||
-rw-r--r-- | mysql-test/suite/sql_sequence/replication.test | 2 |
7 files changed, 101 insertions, 76 deletions
diff --git a/mysql-test/main/alter_table.test b/mysql-test/main/alter_table.test index 05e04f11f9e..eda820abdfb 100644 --- a/mysql-test/main/alter_table.test +++ b/mysql-test/main/alter_table.test @@ -647,11 +647,11 @@ set sql_mode="no_zero_date"; create table t1(f1 int); alter table t1 add column f2 datetime not null, add column f21 date not null; insert into t1 values(1,'2000-01-01','2000-01-01'); ---error 1292 +--error ER_TRUNCATED_WRONG_VALUE alter table t1 add column f3 datetime not null; ---error 1292 +--error ER_TRUNCATED_WRONG_VALUE alter table t1 add column f3 date not null; ---error 1292 +--error ER_TRUNCATED_WRONG_VALUE alter table t1 add column f4 datetime not null default '2002-02-02', add column f41 date not null; alter table t1 add column f4 datetime not null default '2002-02-02', diff --git a/mysql-test/main/null.result b/mysql-test/main/null.result index 0a3f996aa4b..42dfa6d24db 100644 --- a/mysql-test/main/null.result +++ b/mysql-test/main/null.result @@ -1,4 +1,6 @@ -drop table if exists t1, t2; +# +# Testing of NULL in a lot of different places +# select null,\N,isnull(null),isnull(1/0),isnull(1/0 = null),ifnull(null,1),ifnull(null,"TRUE"),ifnull("TRUE","ERROR"),1/0 is null,1 is not null; NULL NULL isnull(null) isnull(1/0) isnull(1/0 = null) ifnull(null,1) ifnull(null,"TRUE") ifnull("TRUE","ERROR") 1/0 is null 1 is not null NULL NULL 1 1 1 1 TRUE TRUE 1 1 @@ -62,6 +64,9 @@ insert into t1 values (null); select * from t1 where x != 0; x drop table t1; +# +# Test problem med index on NULL columns and testing with =NULL; +# CREATE TABLE t1 ( indexed_field int default NULL, KEY indexed_field (indexed_field) @@ -78,6 +83,9 @@ indexed_field NULL NULL DROP TABLE t1; +# +# Testing of IFNULL +# create table t1 (a int, b int) engine=myisam; insert into t1 values(20,null); select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on @@ -95,6 +103,9 @@ b ifnull(t2.b,"this is null") NULL this is null NULL this is null drop table t1; +# +# Test inserting and updating with NULL +# CREATE TABLE t1 (a varchar(16) NOT NULL default '', b smallint(6) NOT NULL default 0, c datetime NOT NULL default '0000-00-00 00:00:00', d smallint(6) NOT NULL default 0); INSERT IGNORE INTO t1 SET a = "", d= "2003-01-14 03:54:55"; Warnings: @@ -149,6 +160,10 @@ a b c d 0 0000-00-00 00:00:00 0 0 0000-00-00 00:00:00 0 drop table t1; +# +# Test to check elimination of IS NULL predicate for a non-nullable attribute +# (bug #1990) +# create table t1 (a int not null, b int not null, index idx(a)); insert into t1 values (1,1), (2,2), (3,3), (4,4), (5,5), (6,6), @@ -163,6 +178,10 @@ drop table t1; select cast(NULL as signed); cast(NULL as signed) NULL +# +# IS NULL is unable to use index in range if column is declared not null +# (Bug #4256) +# create table t1(i int, key(i)); insert into t1 values(1); insert into t1 select i*2 from t1; @@ -192,6 +211,11 @@ select count(*) from t1 where i=2 or i is null; count(*) 9 drop table t1; +# +# NULL has its own type BINARY(0) by default. +# But NULL should be weaker than a constant +# when mixing charsets/collations +# set names latin2; create table t1 select null as c00, @@ -275,6 +299,11 @@ t1 CREATE TABLE `t1` ( `c38` varchar(10) CHARACTER SET latin2 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; +# +# Check that comparison is done according to +# non-null string collation, i.e. case insensitively, +# rather than according to NULL's collation, i.e. case sensitively +# select case 'str' when 'STR' then 'str' when null then 'null' end as c01, case 'str' when null then 'null' when 'STR' then 'str' end as c02, @@ -286,10 +315,13 @@ field('str1', null, 'STR1') as c05, c01 c02 c03 c04 c05 c08 c09 str str 0 1 2 1 1 set names latin1; -create table bug19145a (e enum('a','b','c') default 'b' , s set('x', 'y', 'z') default 'y' ) engine=MyISAM; -create table bug19145b (e enum('a','b','c') default null, s set('x', 'y', 'z') default null) engine=MyISAM; +# +# Bug#19145: mysqld crashes if you set the default value of an enum field to NULL +# +create table bug19145a (e enum('a','b','c') default 'b' , s set('x', 'y', 'z') default 'y' ) engine=MyISAM; +create table bug19145b (e enum('a','b','c') default null, s set('x', 'y', 'z') default null) engine=MyISAM; create table bug19145c (e enum('a','b','c') not null default 'b' , s set('x', 'y', 'z') not null default 'y' ) engine=MyISAM; -create table bug19145setnotnulldefaultnull (e enum('a','b','c') default null, s set('x', 'y', 'z') not null default null) engine=MyISAM; +create table bug19145setnotnulldefaultnull (e enum('a','b','c') default null, s set('x', 'y', 'z') not null default null) engine=MyISAM; ERROR 42000: Invalid default value for 's' create table bug19145enumnotnulldefaultnull (e enum('a','b','c') not null default null, s set('x', 'y', 'z') default null) engine=MyISAM; ERROR 42000: Invalid default value for 'e' @@ -328,8 +360,10 @@ bug19145c CREATE TABLE `bug19145c` ( drop table bug19145a; drop table bug19145b; drop table bug19145c; +# # End of 4.1 tests # +# # Bug #31471: decimal_bin_size: Assertion `scale >= 0 && # precision > 0 && scale <= precision' # @@ -350,8 +384,10 @@ DESCRIBE t2; Field Type Null Key Default Extra IFNULL(NULL, b) decimal(1,0) YES NULL DROP TABLE t1, t2; +# # End of 5.0 tests # +# # MDEV-4895 Valgrind warnings (Conditional jump or move depends on uninitialised value) in Field_datetime::get_date on GREATEST(..) IS NULL # CREATE TABLE t1 (dt DATETIME NOT NULL); @@ -382,8 +418,7 @@ SELECT * FROM t1 WHERE NOT (concat( dt, '1' ) IS NOT NULL); dt DROP TABLE t1; # -# Bug mdev-5132: crash when exeicuting a join query -# with IS NULL and IS NOT NULL in where +# MDEV-5132 crash when exeicuting a join query with IS NULL and IS NOT NULL in where # CREATE TABLE t1 (a DATE, b INT, c INT, KEY(a), KEY(b), KEY(c)) ENGINE=MyISAM; CREATE TABLE t2 (d DATE) ENGINE=MyISAM; @@ -391,9 +426,6 @@ SELECT * FROM t1,t2 WHERE 1 IS NOT NULL AND t1.b IS NULL; a b c d DROP TABLE t1,t2; # -# Start of 10.0 tests -# -# # MDEV-7001 Bad result for NOT NOT STRCMP('a','b') and NOT NOT NULLIF(2,3) # SELECT NOT NOT NULLIF(2,3); diff --git a/mysql-test/main/null.test b/mysql-test/main/null.test index 4e230f72543..4ec8262167e 100644 --- a/mysql-test/main/null.test +++ b/mysql-test/main/null.test @@ -1,11 +1,6 @@ -# Initialise ---disable_warnings -drop table if exists t1, t2; ---enable_warnings - -# -# Testing of NULL in a lot of different places -# +--echo # +--echo # Testing of NULL in a lot of different places +--echo # select null,\N,isnull(null),isnull(1/0),isnull(1/0 = null),ifnull(null,1),ifnull(null,"TRUE"),ifnull("TRUE","ERROR"),1/0 is null,1 is not null; explain extended select null,\N,isnull(null),isnull(1/0),isnull(1/0 = null),ifnull(null,1),ifnull(null,"TRUE"),ifnull("TRUE","ERROR"),1/0 is null,1 is not null; @@ -29,9 +24,9 @@ insert into t1 values (null); select * from t1 where x != 0; drop table t1; -# -# Test problem med index on NULL columns and testing with =NULL; -# +--echo # +--echo # Test problem med index on NULL columns and testing with =NULL; +--echo # CREATE TABLE t1 ( indexed_field int default NULL, @@ -43,9 +38,9 @@ SELECT * FROM t1 WHERE indexed_field IS NULL; SELECT * FROM t1 WHERE indexed_field<=>NULL; DROP TABLE t1; -# -# Testing of IFNULL -# +--echo # +--echo # Testing of IFNULL +--echo # create table t1 (a int, b int) engine=myisam; insert into t1 values(20,null); select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on @@ -57,40 +52,40 @@ select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on t2.b=t3.a order by 1; drop table t1; -# -# Test inserting and updating with NULL -# +--echo # +--echo # Test inserting and updating with NULL +--echo # CREATE TABLE t1 (a varchar(16) NOT NULL default '', b smallint(6) NOT NULL default 0, c datetime NOT NULL default '0000-00-00 00:00:00', d smallint(6) NOT NULL default 0); INSERT IGNORE INTO t1 SET a = "", d= "2003-01-14 03:54:55"; UPDATE IGNORE t1 SET d=1/NULL; UPDATE IGNORE t1 SET d=NULL; ---error 1048 +--error ER_BAD_NULL_ERROR INSERT INTO t1 (a) values (null); ---error 1048 +--error ER_BAD_NULL_ERROR INSERT INTO t1 (a) values (1/null); INSERT IGNORE INTO t1 (a) values (null),(null); ---error 1048 +--error ER_BAD_NULL_ERROR INSERT INTO t1 (b) values (null); ---error 1048 +--error ER_BAD_NULL_ERROR INSERT INTO t1 (b) values (1/null); INSERT IGNORE INTO t1 (b) values (null),(null); ---error 1048 +--error ER_BAD_NULL_ERROR INSERT INTO t1 (c) values (null); ---error 1048 +--error ER_BAD_NULL_ERROR INSERT INTO t1 (c) values (1/null); INSERT IGNORE INTO t1 (c) values (null),(null); ---error 1048 +--error ER_BAD_NULL_ERROR INSERT INTO t1 (d) values (null); ---error 1048 +--error ER_BAD_NULL_ERROR INSERT INTO t1 (d) values (1/null); INSERT IGNORE INTO t1 (d) values (null),(null); select * from t1; drop table t1; -# -# Test to check elimination of IS NULL predicate for a non-nullable attribute -# (bug #1990) -# +--echo # +--echo # Test to check elimination of IS NULL predicate for a non-nullable attribute +--echo # (bug #1990) +--echo # create table t1 (a int not null, b int not null, index idx(a)); insert into t1 values (1,1), (2,2), (3,3), (4,4), (5,5), (6,6), @@ -100,10 +95,10 @@ explain select * from t1 where a between 2 and 3 or b is null; drop table t1; select cast(NULL as signed); -# -# IS NULL is unable to use index in range if column is declared not null -# (Bug #4256) -# +--echo # +--echo # IS NULL is unable to use index in range if column is declared not null +--echo # (Bug #4256) +--echo # create table t1(i int, key(i)); insert into t1 values(1); insert into t1 select i*2 from t1; @@ -124,11 +119,11 @@ explain select * from t1 where i=2 or i is null; select count(*) from t1 where i=2 or i is null; drop table t1; -# -# NULL has its own type BINARY(0) by default. -# But NULL should be weaker than a constant -# when mixing charsets/collations -# +--echo # +--echo # NULL has its own type BINARY(0) by default. +--echo # But NULL should be weaker than a constant +--echo # when mixing charsets/collations +--echo # set names latin2; # Check that result type is taken from a non-null string create table t1 select @@ -174,11 +169,11 @@ create table t1 select show create table t1; drop table t1; -# -# Check that comparison is done according to -# non-null string collation, i.e. case insensitively, -# rather than according to NULL's collation, i.e. case sensitively -# +--echo # +--echo # Check that comparison is done according to +--echo # non-null string collation, i.e. case insensitively, +--echo # rather than according to NULL's collation, i.e. case sensitively +--echo # # in field select case 'str' when 'STR' then 'str' when null then 'null' end as c01, @@ -192,20 +187,20 @@ select # Restore charset to the default value. set names latin1; -# -# Bug#19145: mysqld crashes if you set the default value of an enum field to NULL -# -create table bug19145a (e enum('a','b','c') default 'b' , s set('x', 'y', 'z') default 'y' ) engine=MyISAM; -create table bug19145b (e enum('a','b','c') default null, s set('x', 'y', 'z') default null) engine=MyISAM; +--echo # +--echo # Bug#19145: mysqld crashes if you set the default value of an enum field to NULL +--echo # +create table bug19145a (e enum('a','b','c') default 'b' , s set('x', 'y', 'z') default 'y' ) engine=MyISAM; +create table bug19145b (e enum('a','b','c') default null, s set('x', 'y', 'z') default null) engine=MyISAM; create table bug19145c (e enum('a','b','c') not null default 'b' , s set('x', 'y', 'z') not null default 'y' ) engine=MyISAM; # Invalid default value for 's' ---error 1067 -create table bug19145setnotnulldefaultnull (e enum('a','b','c') default null, s set('x', 'y', 'z') not null default null) engine=MyISAM; +--error ER_INVALID_DEFAULT +create table bug19145setnotnulldefaultnull (e enum('a','b','c') default null, s set('x', 'y', 'z') not null default null) engine=MyISAM; # Invalid default value for 'e' ---error 1067 +--error ER_INVALID_DEFAULT create table bug19145enumnotnulldefaultnull (e enum('a','b','c') not null default null, s set('x', 'y', 'z') default null) engine=MyISAM; alter table bug19145a alter column e set default null; @@ -217,11 +212,11 @@ alter table bug19145b alter column s set default null; alter table bug19145b add column (i int); # Invalid default value for 'e' ---error 1067 +--error ER_INVALID_DEFAULT alter table bug19145c alter column e set default null; # Invalid default value for 's' ---error 1067 +--error ER_INVALID_DEFAULT alter table bug19145c alter column s set default null; alter table bug19145c add column (i int); @@ -233,7 +228,9 @@ drop table bug19145a; drop table bug19145b; drop table bug19145c; +--echo # --echo # End of 4.1 tests +--echo # --echo # --echo # Bug #31471: decimal_bin_size: Assertion `scale >= 0 && @@ -256,7 +253,9 @@ DESCRIBE t2; DROP TABLE t1, t2; +--echo # --echo # End of 5.0 tests +--echo # --echo # --echo # MDEV-4895 Valgrind warnings (Conditional jump or move depends on uninitialised value) in Field_datetime::get_date on GREATEST(..) IS NULL @@ -287,8 +286,7 @@ SELECT * FROM t1 WHERE NOT (concat( dt, '1' ) IS NOT NULL); DROP TABLE t1; --echo # ---echo # Bug mdev-5132: crash when exeicuting a join query ---echo # with IS NULL and IS NOT NULL in where +--echo # MDEV-5132 crash when exeicuting a join query with IS NULL and IS NOT NULL in where --echo # CREATE TABLE t1 (a DATE, b INT, c INT, KEY(a), KEY(b), KEY(c)) ENGINE=MyISAM; @@ -299,10 +297,6 @@ SELECT * FROM t1,t2 WHERE 1 IS NOT NULL AND t1.b IS NULL; DROP TABLE t1,t2; --echo # ---echo # Start of 10.0 tests ---echo # - ---echo # --echo # MDEV-7001 Bad result for NOT NOT STRCMP('a','b') and NOT NOT NULLIF(2,3) --echo # SELECT NOT NOT NULLIF(2,3); @@ -1177,7 +1171,6 @@ SELECT * FROM t1 WHERE ((c1 IS NOT NULL) >= (NOT TRUE)) IS NOT NULL; SELECT * FROM t1 WHERE ((c1 IS NOT NULL) >= (NOT TRUE)) IS NOT NULL; DROP TABLE t1; - --echo # --echo # End of 10.1 tests --echo # diff --git a/mysql-test/suite/sql_sequence/gtid.result b/mysql-test/suite/sql_sequence/gtid.result index 495e4b806b7..34d6ee90598 100644 --- a/mysql-test/suite/sql_sequence/gtid.result +++ b/mysql-test/suite/sql_sequence/gtid.result @@ -68,7 +68,7 @@ connection master; create sequence s2; alter table s2 add id int; ERROR HY000: Sequence 's_db.s2' table structure is invalid (Wrong number of columns) -alter table s2 add index ind_x(start); +alter table s2 add index ind_x(start_value); ERROR HY000: Sequence 's_db.s2' table structure is invalid (Sequence tables cannot have any keys) drop sequence s2; ########################################### diff --git a/mysql-test/suite/sql_sequence/gtid.test b/mysql-test/suite/sql_sequence/gtid.test index 63ed7ec5264..4a096246108 100644 --- a/mysql-test/suite/sql_sequence/gtid.test +++ b/mysql-test/suite/sql_sequence/gtid.test @@ -70,7 +70,7 @@ create sequence s2; alter table s2 add id int; --error ER_SEQUENCE_INVALID_TABLE_STRUCTURE -alter table s2 add index ind_x(start); +alter table s2 add index ind_x(start_value); drop sequence s2; --echo ########################################### diff --git a/mysql-test/suite/sql_sequence/replication.result b/mysql-test/suite/sql_sequence/replication.result index c429b74b4cd..8d8aee5080c 100644 --- a/mysql-test/suite/sql_sequence/replication.result +++ b/mysql-test/suite/sql_sequence/replication.result @@ -64,7 +64,7 @@ connection master; create sequence s2; alter table s2 add id int; ERROR HY000: Sequence 's_db.s2' table structure is invalid (Wrong number of columns) -alter table s2 add index ind_x(start); +alter table s2 add index ind_x(start_value); ERROR HY000: Sequence 's_db.s2' table structure is invalid (Sequence tables cannot have any keys) drop sequence s2; ########################################### diff --git a/mysql-test/suite/sql_sequence/replication.test b/mysql-test/suite/sql_sequence/replication.test index e26fde8a329..0354737788f 100644 --- a/mysql-test/suite/sql_sequence/replication.test +++ b/mysql-test/suite/sql_sequence/replication.test @@ -70,7 +70,7 @@ create sequence s2; alter table s2 add id int; --error ER_SEQUENCE_INVALID_TABLE_STRUCTURE -alter table s2 add index ind_x(start); +alter table s2 add index ind_x(start_value); drop sequence s2; --echo ########################################### |