diff options
Diffstat (limited to 'mysql-test/main/null.test')
| -rw-r--r-- | mysql-test/main/null.test | 1181 |
1 files changed, 1181 insertions, 0 deletions
diff --git a/mysql-test/main/null.test b/mysql-test/main/null.test new file mode 100644 index 00000000000..403790356ce --- /dev/null +++ b/mysql-test/main/null.test @@ -0,0 +1,1181 @@ +# Initialise +--disable_warnings +drop table if exists t1, t2; +--enable_warnings + +# +# 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; +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; +select 1 | NULL,1 & NULL,1+NULL,1-NULL; +select NULL=NULL,NULL<>NULL,IFNULL(NULL,1.1)+0,IFNULL(NULL,1) | 0; +select strcmp("a",NULL),(1<NULL)+0.0,NULL regexp "a",null like "a%","a%" like null; +select concat("a",NULL),replace(NULL,"a","b"),replace("string","i",NULL),replace("string",NULL,"i"),insert("abc",1,1,NULL),left(NULL,1); +select repeat("a",0),repeat("ab",5+5),repeat("ab",-1),reverse(NULL); +select field(NULL,"a","b","c"); +select 2 between null and 1,2 between 3 AND NULL,NULL between 1 and 2,2 between NULL and 3, 2 between 1 AND null; +explain extended select 2 between null and 1,2 between 3 AND NULL,NULL between 1 and 2,2 between NULL and 3, 2 between 1 AND null; +SELECT NULL AND NULL, 1 AND NULL, NULL AND 1, NULL OR NULL, 0 OR NULL, NULL OR 0; +SELECT (NULL OR NULL) IS NULL; +select NULL AND 0, 0 and NULL; +select inet_ntoa(null),inet_aton(null),inet_aton("122.256"),inet_aton("122.226."),inet_aton(""); +explain extended select inet_ntoa(null),inet_aton(null),inet_aton("122.256"),inet_aton("122.226."),inet_aton(""); +select not null is true, not null or true, not null and false, not null <=> null; + +create table t1 (x int); +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; +# + +CREATE TABLE t1 ( + indexed_field int default NULL, + KEY indexed_field (indexed_field) +); +INSERT INTO t1 VALUES (NULL),(NULL); +SELECT * FROM t1 WHERE indexed_field=NULL; +SELECT * FROM t1 WHERE indexed_field IS NULL; +SELECT * FROM t1 WHERE indexed_field<=>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 +t2.b=t3.a; +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; +insert into t1 values(10,null); +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 +# +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 +INSERT INTO t1 (a) values (null); +--error 1048 +INSERT INTO t1 (a) values (1/null); +INSERT IGNORE INTO t1 (a) values (null),(null); +--error 1048 +INSERT INTO t1 (b) values (null); +--error 1048 +INSERT INTO t1 (b) values (1/null); +INSERT IGNORE INTO t1 (b) values (null),(null); +--error 1048 +INSERT INTO t1 (c) values (null); +--error 1048 +INSERT INTO t1 (c) values (1/null); +INSERT IGNORE INTO t1 (c) values (null),(null); +--error 1048 +INSERT INTO t1 (d) values (null); +--error 1048 +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) +# +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), + (7,7), (8,8), (9,9), (10,10), (11,11), (12,12); +explain select * from t1 where a between 2 and 3; +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) +# +create table t1(i int, key(i)); +insert into t1 values(1); +insert into t1 select i*2 from t1; +insert into t1 select i*2 from t1; +insert into t1 select i*2 from t1; +insert into t1 select i*2 from t1; +insert into t1 select i*2 from t1; +insert into t1 select i*2 from t1; +insert into t1 select i*2 from t1; +insert into t1 select i*2 from t1; +insert into t1 select i*2 from t1; +insert into t1 values(null); +explain select * from t1 where i=2 or i is null; +select count(*) from t1 where i=2 or i is null; +SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR +alter table t1 change i i int not null; +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 +# +set names latin2; +# Check that result type is taken from a non-null string +create table t1 select + null as c00, + if(1, null, 'string') as c01, + if(0, null, 'string') as c02, + ifnull(null, 'string') as c03, + ifnull('string', null) as c04, + case when 0 then null else 'string' end as c05, + case when 1 then null else 'string' end as c06, + coalesce(null, 'string') as c07, + coalesce('string', null) as c08, + least('string',null) as c09, + least(null, 'string') as c10, + greatest('string',null) as c11, + greatest(null, 'string') as c12, + nullif('string', null) as c13, + nullif(null, 'string') as c14, + trim('string' from null) as c15, + trim(null from 'string') as c16, + substring_index('string', null, 1) as c17, + substring_index(null, 'string', 1) as c18, + elt(1, null, 'string') as c19, + elt(1, 'string', null) as c20, + concat('string', null) as c21, + concat(null, 'string') as c22, + concat_ws('sep', 'string', null) as c23, + concat_ws('sep', null, 'string') as c24, + concat_ws(null, 'string', 'string') as c25, + make_set(3, 'string', null) as c26, + make_set(3, null, 'string') as c27, + export_set(3, null, 'off', 'sep') as c29, + export_set(3, 'on', null, 'sep') as c30, + export_set(3, 'on', 'off', null) as c31, + replace(null, 'from', 'to') as c32, + replace('str', null, 'to') as c33, + replace('str', 'from', null) as c34, + insert('str', 1, 2, null) as c35, + insert(null, 1, 2, 'str') as c36, + lpad('str', 10, null) as c37, + rpad(null, 10, 'str') as c38; + +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 +# +# in field +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, + field(null, 'str1', 'str2') as c03, + field('str1','STR1', null) as c04, + field('str1', null, 'STR1') as c05, + 'string' in ('STRING', null) as c08, + 'string' in (null, 'STRING') as c09; + +# 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; + +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; + +# Invalid default value for 'e' +--error 1067 +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; +alter table bug19145a alter column s set default null; +alter table bug19145a add column (i int); + +alter table bug19145b alter column e set default null; +alter table bug19145b alter column s set default null; +alter table bug19145b add column (i int); + +# Invalid default value for 'e' +--error 1067 +alter table bug19145c alter column e set default null; + +# Invalid default value for 's' +--error 1067 +alter table bug19145c alter column s set default null; +alter table bug19145c add column (i int); + +show create table bug19145a; +show create table bug19145b; +show create table bug19145c; + +drop table bug19145a; +drop table bug19145b; +drop table bug19145c; + +--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 + +--echo # +--echo # MDEV-4895 Valgrind warnings (Conditional jump or move depends on uninitialised value) in Field_datetime::get_date on GREATEST(..) IS NULL +--echo # + +CREATE TABLE t1 (dt DATETIME NOT NULL); +INSERT INTO t1 VALUES (NOW()),(NOW()); + +EXPLAIN +SELECT * FROM t1 WHERE concat( dt, '2012-12-21 12:12:12' ) IS NULL; +SELECT * FROM t1 WHERE concat( dt, '2012-12-21 12:12:12' ) IS NULL; + +DROP TABLE t1; +CREATE TABLE t1 (dt INT NOT NULL); +INSERT INTO t1 VALUES (1),(2); +EXPLAIN +SELECT * FROM t1 WHERE concat( dt, '1' ) IS NULL; +SELECT * FROM t1 WHERE concat( dt, '1' ) IS NULL; + +DROP TABLE t1; +CREATE TABLE t1 (dt INT NOT NULL); +INSERT INTO t1 VALUES (1),(2); + +EXPLAIN +SELECT * FROM t1 WHERE NOT (concat( dt, '1' ) IS NOT NULL); +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 # + +CREATE TABLE t1 (a DATE, b INT, c INT, KEY(a), KEY(b), KEY(c)) ENGINE=MyISAM; +CREATE TABLE t2 (d DATE) ENGINE=MyISAM; + +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); + +--echo # +--echo # End of 10.0 tests +--echo # + +--echo # +--echo # Start of 10.1 tests +--echo # + +--echo # +--echo # MDEV-7146 NULLIF returns unexpected result with a YEAR field +--echo # +CREATE TABLE t1 (a YEAR(2)); +INSERT INTO t1 VALUES (0); +SELECT a,NULLIF(a,2000),NULLIF(2000,a) FROM t1; +SELECT a,NULLIF(a,2001),NULLIF(2001,a) FROM t1; +DROP TABLE t1; + +--echo # +--echo # MDEV-7005 NULLIF does not work as documented +--echo # + +CREATE TABLE t1 (a TIME); +CREATE TABLE t2 AS SELECT a,NULLIF(a,a), CASE WHEN a=a THEN NULL ELSE a END FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t1,t2; + +--error ER_CANT_AGGREGATE_2COLLATIONS +SELECT NULLIF(_latin1'a' COLLATE latin1_general_ci, _latin1'a' COLLATE latin1_bin); + +SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR +CREATE TABLE t1 AS SELECT + NULLIF(1,1), + NULLIF(1,1.0), + NULLIF(1,1e0), + NULLIF(1,'2001-01-01'), + NULLIF(1,TIME'00:00:00'); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR +CREATE TABLE t1 AS SELECT + NULLIF(1.0,1), + NULLIF(1.0,1.0), + NULLIF(1.0,1e0), + NULLIF(1.0,'2001-01-01'), + NULLIF(1.0,TIME'00:00:00'); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR +CREATE TABLE t1 AS SELECT + NULLIF(1e0,1), + NULLIF(1e0,1.0), + NULLIF(1e0,1e0), + NULLIF(1e0,'2001-01-01'), + NULLIF(1e0,TIME'00:00:00'); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 AS SELECT + NULLIF('1',1), + NULLIF('1',1.0), + NULLIF('1',1e0), + NULLIF('1','2001-01-01'), + NULLIF('1',TIME'00:00:00'); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR +CREATE TABLE t1 AS SELECT + NULLIF(TIMESTAMP'2001-01-01 00:00:00',1), + NULLIF(TIMESTAMP'2001-01-01 00:00:00',1.0), + NULLIF(TIMESTAMP'2001-01-01 00:00:00',1e0), + NULLIF(TIMESTAMP'2001-01-01 00:00:00','2001-01-01'), + NULLIF(TIMESTAMP'2001-01-01 00:00:00',TIME'00:00:00'); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR +CREATE TABLE t1 AS SELECT + NULLIF(DATE'2001-01-01',1), + NULLIF(DATE'2001-01-01',1.0), + NULLIF(DATE'2001-01-01',1e0), + NULLIF(DATE'2001-01-01','2001-01-01'), + NULLIF(DATE'2001-01-01',TIME'00:00:00'); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 AS SELECT + NULLIF(TIME'00:00:01',1), + NULLIF(TIME'00:00:01',1.0), + NULLIF(TIME'00:00:01',1e0), + NULLIF(TIME'00:00:01','00:00:00'), + NULLIF(TIME'00:00:01',DATE'2001-01-01'); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 +( + c_tinyint TINYINT, + c_smallint SMALLINT, + c_int INT, + c_bigint BIGINT, + c_float FLOAT, + c_double DOUBLE, + c_decimal103 DECIMAL(10,3), + c_varchar10 VARCHAR(10), + c_tinytext TINYTEXT, + c_text TEXT, + c_mediumtext MEDIUMTEXT, + c_longtext LONGTEXT, + c_tinyblob TINYBLOB, + c_blob BLOB, + c_mediumblob MEDIUMBLOB, + c_longblob LONGBLOB, + c_enum ENUM('one','two','tree'), + c_datetime3 DATETIME(3), + c_timestamp3 TIMESTAMP(3), + c_date DATE, + c_time TIME +); + +--echo # +--echo # Checking that the return type depends only on args[0], even when compared to a super type +--echo # + +CREATE TABLE t2 AS SELECT + NULLIF(c_tinyint, 1), + NULLIF(c_tinyint, c_smallint), + NULLIF(c_tinyint, c_tinyint), + NULLIF(c_tinyint, c_int), + NULLIF(c_tinyint, c_bigint), + NULLIF(c_tinyint, c_float), + NULLIF(c_tinyint, c_double), + NULLIF(c_tinyint, c_decimal103), + NULLIF(c_tinyint, c_varchar10), + NULLIF(c_tinyint, c_text), + NULLIF(c_tinyint, c_blob), + NULLIF(c_tinyint, c_enum), + NULLIF(c_tinyint, c_datetime3), + NULLIF(c_tinyint, c_timestamp3), + NULLIF(c_tinyint, c_date), + NULLIF(c_tinyint, c_time) +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE TABLE t2 AS SELECT + NULLIF(c_smallint, 1), + NULLIF(c_smallint, c_smallint), + NULLIF(c_smallint, c_tinyint), + NULLIF(c_smallint, c_int), + NULLIF(c_smallint, c_bigint), + NULLIF(c_smallint, c_float), + NULLIF(c_smallint, c_double), + NULLIF(c_smallint, c_decimal103), + NULLIF(c_smallint, c_varchar10), + NULLIF(c_smallint, c_text), + NULLIF(c_smallint, c_blob), + NULLIF(c_smallint, c_enum), + NULLIF(c_smallint, c_datetime3), + NULLIF(c_smallint, c_timestamp3), + NULLIF(c_smallint, c_date), + NULLIF(c_smallint, c_time) +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE TABLE t2 AS SELECT + NULLIF(c_int, 1), + NULLIF(c_int, c_smallint), + NULLIF(c_int, c_tinyint), + NULLIF(c_int, c_int), + NULLIF(c_int, c_bigint), + NULLIF(c_int, c_float), + NULLIF(c_int, c_double), + NULLIF(c_int, c_decimal103), + NULLIF(c_int, c_varchar10), + NULLIF(c_int, c_text), + NULLIF(c_int, c_blob), + NULLIF(c_int, c_enum), + NULLIF(c_int, c_datetime3), + NULLIF(c_int, c_timestamp3), + NULLIF(c_int, c_date), + NULLIF(c_int, c_time) +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE TABLE t2 AS SELECT + NULLIF(c_bigint, 1), + NULLIF(c_bigint, c_smallint), + NULLIF(c_bigint, c_tinyint), + NULLIF(c_bigint, c_int), + NULLIF(c_bigint, c_bigint), + NULLIF(c_bigint, c_float), + NULLIF(c_bigint, c_double), + NULLIF(c_bigint, c_decimal103), + NULLIF(c_bigint, c_varchar10), + NULLIF(c_bigint, c_text), + NULLIF(c_bigint, c_blob), + NULLIF(c_bigint, c_enum), + NULLIF(c_bigint, c_datetime3), + NULLIF(c_bigint, c_timestamp3), + NULLIF(c_bigint, c_date), + NULLIF(c_bigint, c_time) +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +# QQ: this should probably create a FLOAT column instead of a DOUBLE column +CREATE TABLE t2 AS SELECT + NULLIF(c_float, 1), + NULLIF(c_float, c_smallint), + NULLIF(c_float, c_tinyint), + NULLIF(c_float, c_int), + NULLIF(c_float, c_bigint), + NULLIF(c_float, c_float), + NULLIF(c_float, c_double), + NULLIF(c_float, c_decimal103), + NULLIF(c_float, c_varchar10), + NULLIF(c_float, c_text), + NULLIF(c_float, c_blob), + NULLIF(c_float, c_enum), + NULLIF(c_float, c_datetime3), + NULLIF(c_float, c_timestamp3), + NULLIF(c_float, c_date), + NULLIF(c_float, c_time) +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE TABLE t2 AS SELECT + NULLIF(c_double, 1), + NULLIF(c_double, c_smallint), + NULLIF(c_double, c_tinyint), + NULLIF(c_double, c_int), + NULLIF(c_double, c_bigint), + NULLIF(c_double, c_float), + NULLIF(c_double, c_double), + NULLIF(c_double, c_decimal103), + NULLIF(c_double, c_varchar10), + NULLIF(c_double, c_text), + NULLIF(c_double, c_blob), + NULLIF(c_double, c_enum), + NULLIF(c_double, c_datetime3), + NULLIF(c_double, c_timestamp3), + NULLIF(c_double, c_date), + NULLIF(c_double, c_time) +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE TABLE t2 AS SELECT + NULLIF(c_decimal103, 1), + NULLIF(c_decimal103, c_smallint), + NULLIF(c_decimal103, c_tinyint), + NULLIF(c_decimal103, c_int), + NULLIF(c_decimal103, c_bigint), + NULLIF(c_decimal103, c_float), + NULLIF(c_decimal103, c_double), + NULLIF(c_decimal103, c_decimal103), + NULLIF(c_decimal103, c_varchar10), + NULLIF(c_decimal103, c_text), + NULLIF(c_decimal103, c_blob), + NULLIF(c_decimal103, c_enum), + NULLIF(c_decimal103, c_datetime3), + NULLIF(c_decimal103, c_timestamp3), + NULLIF(c_decimal103, c_date), + NULLIF(c_decimal103, c_time) +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE TABLE t2 AS SELECT + NULLIF(c_varchar10, 1), + NULLIF(c_varchar10, c_smallint), + NULLIF(c_varchar10, c_tinyint), + NULLIF(c_varchar10, c_int), + NULLIF(c_varchar10, c_bigint), + NULLIF(c_varchar10, c_float), + NULLIF(c_varchar10, c_double), + NULLIF(c_varchar10, c_decimal103), + NULLIF(c_varchar10, c_varchar10), + NULLIF(c_varchar10, c_text), + NULLIF(c_varchar10, c_blob), + NULLIF(c_varchar10, c_enum), + NULLIF(c_varchar10, c_datetime3), + NULLIF(c_varchar10, c_timestamp3), + NULLIF(c_varchar10, c_date), + NULLIF(c_varchar10, c_time) +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE TABLE t2 AS SELECT + NULLIF(c_tinytext, 1), + NULLIF(c_tinytext, c_smallint), + NULLIF(c_tinytext, c_tinyint), + NULLIF(c_tinytext, c_int), + NULLIF(c_tinytext, c_bigint), + NULLIF(c_tinytext, c_float), + NULLIF(c_tinytext, c_double), + NULLIF(c_tinytext, c_decimal103), + NULLIF(c_tinytext, c_varchar10), + NULLIF(c_tinytext, c_text), + NULLIF(c_tinytext, c_blob), + NULLIF(c_tinytext, c_enum), + NULLIF(c_tinytext, c_datetime3), + NULLIF(c_tinytext, c_timestamp3), + NULLIF(c_tinytext, c_date), + NULLIF(c_tinytext, c_time) +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE TABLE t2 AS SELECT + NULLIF(c_text, 1), + NULLIF(c_text, c_smallint), + NULLIF(c_text, c_tinyint), + NULLIF(c_text, c_int), + NULLIF(c_text, c_bigint), + NULLIF(c_text, c_float), + NULLIF(c_text, c_double), + NULLIF(c_text, c_decimal103), + NULLIF(c_text, c_varchar10), + NULLIF(c_text, c_text), + NULLIF(c_text, c_blob), + NULLIF(c_text, c_enum), + NULLIF(c_text, c_datetime3), + NULLIF(c_text, c_timestamp3), + NULLIF(c_text, c_date), + NULLIF(c_text, c_time) +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE TABLE t2 AS SELECT + NULLIF(c_mediumtext, 1), + NULLIF(c_mediumtext, c_smallint), + NULLIF(c_mediumtext, c_tinyint), + NULLIF(c_mediumtext, c_int), + NULLIF(c_mediumtext, c_bigint), + NULLIF(c_mediumtext, c_float), + NULLIF(c_mediumtext, c_double), + NULLIF(c_mediumtext, c_decimal103), + NULLIF(c_mediumtext, c_varchar10), + NULLIF(c_mediumtext, c_text), + NULLIF(c_mediumtext, c_blob), + NULLIF(c_mediumtext, c_enum), + NULLIF(c_mediumtext, c_datetime3), + NULLIF(c_mediumtext, c_timestamp3), + NULLIF(c_mediumtext, c_date), + NULLIF(c_mediumtext, c_time) +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE TABLE t2 AS SELECT + NULLIF(c_longtext, 1), + NULLIF(c_longtext, c_smallint), + NULLIF(c_longtext, c_tinyint), + NULLIF(c_longtext, c_int), + NULLIF(c_longtext, c_bigint), + NULLIF(c_longtext, c_float), + NULLIF(c_longtext, c_double), + NULLIF(c_longtext, c_decimal103), + NULLIF(c_longtext, c_varchar10), + NULLIF(c_longtext, c_text), + NULLIF(c_longtext, c_blob), + NULLIF(c_longtext, c_enum), + NULLIF(c_longtext, c_datetime3), + NULLIF(c_longtext, c_timestamp3), + NULLIF(c_longtext, c_date), + NULLIF(c_longtext, c_time) +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + + +CREATE TABLE t2 AS SELECT + NULLIF(c_tinyblob, 1), + NULLIF(c_tinyblob, c_smallint), + NULLIF(c_tinyblob, c_tinyint), + NULLIF(c_tinyblob, c_int), + NULLIF(c_tinyblob, c_bigint), + NULLIF(c_tinyblob, c_float), + NULLIF(c_tinyblob, c_double), + NULLIF(c_tinyblob, c_decimal103), + NULLIF(c_tinyblob, c_varchar10), + NULLIF(c_tinyblob, c_text), + NULLIF(c_tinyblob, c_blob), + NULLIF(c_tinyblob, c_enum), + NULLIF(c_tinyblob, c_datetime3), + NULLIF(c_tinyblob, c_timestamp3), + NULLIF(c_tinyblob, c_date), + NULLIF(c_tinyblob, c_time) +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE TABLE t2 AS SELECT + NULLIF(c_blob, 1), + NULLIF(c_blob, c_smallint), + NULLIF(c_blob, c_tinyint), + NULLIF(c_blob, c_int), + NULLIF(c_blob, c_bigint), + NULLIF(c_blob, c_float), + NULLIF(c_blob, c_double), + NULLIF(c_blob, c_decimal103), + NULLIF(c_blob, c_varchar10), + NULLIF(c_blob, c_text), + NULLIF(c_blob, c_blob), + NULLIF(c_blob, c_enum), + NULLIF(c_blob, c_datetime3), + NULLIF(c_blob, c_timestamp3), + NULLIF(c_blob, c_date), + NULLIF(c_blob, c_time) +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE TABLE t2 AS SELECT + NULLIF(c_mediumblob, 1), + NULLIF(c_mediumblob, c_smallint), + NULLIF(c_mediumblob, c_tinyint), + NULLIF(c_mediumblob, c_int), + NULLIF(c_mediumblob, c_bigint), + NULLIF(c_mediumblob, c_float), + NULLIF(c_mediumblob, c_double), + NULLIF(c_mediumblob, c_decimal103), + NULLIF(c_mediumblob, c_varchar10), + NULLIF(c_mediumblob, c_text), + NULLIF(c_mediumblob, c_blob), + NULLIF(c_mediumblob, c_enum), + NULLIF(c_mediumblob, c_datetime3), + NULLIF(c_mediumblob, c_timestamp3), + NULLIF(c_mediumblob, c_date), + NULLIF(c_mediumblob, c_time) +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE TABLE t2 AS SELECT + NULLIF(c_longblob, 1), + NULLIF(c_longblob, c_smallint), + NULLIF(c_longblob, c_tinyint), + NULLIF(c_longblob, c_int), + NULLIF(c_longblob, c_bigint), + NULLIF(c_longblob, c_float), + NULLIF(c_longblob, c_double), + NULLIF(c_longblob, c_decimal103), + NULLIF(c_longblob, c_varchar10), + NULLIF(c_longblob, c_text), + NULLIF(c_longblob, c_blob), + NULLIF(c_longblob, c_enum), + NULLIF(c_longblob, c_datetime3), + NULLIF(c_longblob, c_timestamp3), + NULLIF(c_longblob, c_date), + NULLIF(c_longblob, c_time) +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + + +# QQ: this should probably create a ENUM column instead of VARCHAR(4) +CREATE TABLE t2 AS SELECT + NULLIF(c_enum, 1), + NULLIF(c_enum, c_smallint), + NULLIF(c_enum, c_tinyint), + NULLIF(c_enum, c_int), + NULLIF(c_enum, c_bigint), + NULLIF(c_enum, c_float), + NULLIF(c_enum, c_double), + NULLIF(c_enum, c_decimal103), + NULLIF(c_enum, c_varchar10), + NULLIF(c_enum, c_text), + NULLIF(c_enum, c_blob), + NULLIF(c_enum, c_enum), + NULLIF(c_enum, c_datetime3), + NULLIF(c_enum, c_timestamp3), + NULLIF(c_enum, c_date), + NULLIF(c_enum, c_time) +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE TABLE t2 AS SELECT + NULLIF(c_datetime3, 1), + NULLIF(c_datetime3, c_smallint), + NULLIF(c_datetime3, c_tinyint), + NULLIF(c_datetime3, c_int), + NULLIF(c_datetime3, c_bigint), + NULLIF(c_datetime3, c_float), + NULLIF(c_datetime3, c_double), + NULLIF(c_datetime3, c_decimal103), + NULLIF(c_datetime3, c_varchar10), + NULLIF(c_datetime3, c_text), + NULLIF(c_datetime3, c_blob), + NULLIF(c_datetime3, c_enum), + NULLIF(c_datetime3, c_datetime3), + NULLIF(c_datetime3, c_timestamp3), + NULLIF(c_datetime3, c_date), + NULLIF(c_datetime3, c_time) +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE TABLE t2 AS SELECT + NULLIF(c_timestamp3, 1), + NULLIF(c_timestamp3, c_smallint), + NULLIF(c_timestamp3, c_tinyint), + NULLIF(c_timestamp3, c_int), + NULLIF(c_timestamp3, c_bigint), + NULLIF(c_timestamp3, c_float), + NULLIF(c_timestamp3, c_double), + NULLIF(c_timestamp3, c_decimal103), + NULLIF(c_timestamp3, c_varchar10), + NULLIF(c_timestamp3, c_text), + NULLIF(c_timestamp3, c_blob), + NULLIF(c_timestamp3, c_enum), + NULLIF(c_timestamp3, c_datetime3), + NULLIF(c_timestamp3, c_timestamp3), + NULLIF(c_timestamp3, c_date), + NULLIF(c_timestamp3, c_time) +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE TABLE t2 AS SELECT + NULLIF(c_date, 1), + NULLIF(c_date, c_smallint), + NULLIF(c_date, c_tinyint), + NULLIF(c_date, c_int), + NULLIF(c_date, c_bigint), + NULLIF(c_date, c_float), + NULLIF(c_date, c_double), + NULLIF(c_date, c_decimal103), + NULLIF(c_date, c_varchar10), + NULLIF(c_date, c_text), + NULLIF(c_date, c_blob), + NULLIF(c_date, c_enum), + NULLIF(c_date, c_datetime3), + NULLIF(c_date, c_timestamp3), + NULLIF(c_date, c_date), + NULLIF(c_date, c_time) +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE TABLE t2 AS SELECT + NULLIF(c_time, 1), + NULLIF(c_time, c_smallint), + NULLIF(c_time, c_tinyint), + NULLIF(c_time, c_int), + NULLIF(c_time, c_bigint), + NULLIF(c_time, c_float), + NULLIF(c_time, c_double), + NULLIF(c_time, c_decimal103), + NULLIF(c_time, c_varchar10), + NULLIF(c_time, c_text), + NULLIF(c_time, c_blob), + NULLIF(c_time, c_enum), + NULLIF(c_time, c_datetime3), + NULLIF(c_time, c_timestamp3), + NULLIF(c_time, c_date), + NULLIF(c_time, c_time) +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +--echo # +--echo # Checking that the return type depends only on args[0], even if compared to a field +--echo # + +CREATE TABLE t2 AS SELECT + NULLIF(1, 1), + NULLIF(1, c_smallint), + NULLIF(1, c_tinyint), + NULLIF(1, c_int), + NULLIF(1, c_bigint), + NULLIF(1, c_float), + NULLIF(1, c_double), + NULLIF(1, c_decimal103), + NULLIF(1, c_varchar10), + NULLIF(1, c_text), + NULLIF(1, c_blob), + NULLIF(1, c_enum), + NULLIF(1, c_datetime3), + NULLIF(1, c_timestamp3), + NULLIF(1, c_date), + NULLIF(1, c_time) +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE TABLE t2 AS SELECT + NULLIF(1.0, 1), + NULLIF(1.0, c_smallint), + NULLIF(1.0, c_tinyint), + NULLIF(1.0, c_int), + NULLIF(1.0, c_bigint), + NULLIF(1.0, c_float), + NULLIF(1.0, c_double), + NULLIF(1.0, c_decimal103), + NULLIF(1.0, c_varchar10), + NULLIF(1.0, c_text), + NULLIF(1.0, c_blob), + NULLIF(1.0, c_enum), + NULLIF(1.0, c_datetime3), + NULLIF(1.0, c_timestamp3), + NULLIF(1.0, c_date), + NULLIF(1.0, c_time) +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE TABLE t2 AS SELECT + NULLIF(1e0, 1), + NULLIF(1e0, c_smallint), + NULLIF(1e0, c_tinyint), + NULLIF(1e0, c_int), + NULLIF(1e0, c_bigint), + NULLIF(1e0, c_float), + NULLIF(1e0, c_double), + NULLIF(1e0, c_decimal103), + NULLIF(1e0, c_varchar10), + NULLIF(1e0, c_text), + NULLIF(1e0, c_blob), + NULLIF(1e0, c_enum), + NULLIF(1e0, c_datetime3), + NULLIF(1e0, c_timestamp3), + NULLIF(1e0, c_date), + NULLIF(1e0, c_time) +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE TABLE t2 AS SELECT + NULLIF('1', 1), + NULLIF('1', c_smallint), + NULLIF('1', c_tinyint), + NULLIF('1', c_int), + NULLIF('1', c_bigint), + NULLIF('1', c_float), + NULLIF('1', c_double), + NULLIF('1', c_decimal103), + NULLIF('1', c_varchar10), + NULLIF('1', c_text), + NULLIF('1', c_blob), + NULLIF('1', c_enum), + NULLIF('1', c_datetime3), + NULLIF('1', c_timestamp3), + NULLIF('1', c_date), + NULLIF('1', c_time) +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE TABLE t2 AS SELECT + NULLIF(TIME'10:10:10', 1), + NULLIF(TIME'10:10:10', c_smallint), + NULLIF(TIME'10:10:10', c_tinyint), + NULLIF(TIME'10:10:10', c_int), + NULLIF(TIME'10:10:10', c_bigint), + NULLIF(TIME'10:10:10', c_float), + NULLIF(TIME'10:10:10', c_double), + NULLIF(TIME'10:10:10', c_decimal103), + NULLIF(TIME'10:10:10', c_varchar10), + NULLIF(TIME'10:10:10', c_text), + NULLIF(TIME'10:10:10', c_blob), + NULLIF(TIME'10:10:10', c_enum), + NULLIF(TIME'10:10:10', c_datetime3), + NULLIF(TIME'10:10:10', c_timestamp3), + NULLIF(TIME'10:10:10', c_date), + NULLIF(TIME'10:10:10', c_time) +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +DROP TABLE t1; + +--echo # +--echo # MDEV-7759 NULLIF(x,y) is not equal to CASE WHEN x=y THEN NULL ELSE x END +--echo # +CREATE TABLE t1 (a YEAR); +INSERT INTO t1 VALUES (2010),(2020); +SELECT * FROM t1 WHERE a=2010 AND NULLIF(10.1,a) IS NULL; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2010 AND NULLIF(10.1,a) IS NULL; +SELECT * FROM t1 WHERE a=2010 AND CASE WHEN 10.1=a THEN NULL ELSE 10.1 END IS NULL; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2010 AND CASE WHEN 10.1=a THEN NULL ELSE 10.1 END IS NULL; +DROP TABLE t1; + +--echo # Two warnings expected +SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR +CREATE TABLE t1 AS SELECT + NULLIF(TIMESTAMP'2001-01-01 00:00:00',1) AS a, + CASE WHEN TIMESTAMP'2001-01-01 00:00:00'=1 THEN NULL + ELSE TIMESTAMP'2001-01-01 00:00:00' + END AS b; +DROP TABLE t1; + +--echo # +--echo # MDEV-8785 Wrong results for EXPLAIN EXTENDED...WHERE NULLIF(latin1_col, _utf8'a' COLLATE utf8_bin) IS NOT NULL +--echo # +CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET latin1); +INSERT INTO t1 VALUES ('a'),('A'); +SELECT a, NULLIF(a,_utf8'a' COLLATE utf8_bin) IS NULL FROM t1; +SELECT CHARSET(NULLIF(a,_utf8'a' COLLATE utf8_bin)) FROM t1; +EXPLAIN EXTENDED SELECT NULLIF(a,_utf8'a' COLLATE utf8_bin) IS NULL AS expr FROM t1; +DROP TABLE t1; + + +--echo # +--echo # MDEV-8740 Wrong result for SELECT..WHERE year_field=10 AND NULLIF(year_field,2011.1)='2011' +--echo # +CREATE TABLE t1 (a YEAR); +INSERT INTO t1 VALUES (2010),(2011); +SELECT a=10 AND NULLIF(a,2011.1)='2011' AS cond FROM t1; +SELECT * FROM t1 WHERE a=10; +SELECT * FROM t1 WHERE NULLIF(a,2011.1)='2011'; +SELECT * FROM t1 WHERE a=10 AND NULLIF(a,2011.1)='2011'; +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE a=10 AND NULLIF(a,2011.1)='2011'; +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE a=10 AND NULLIF(a,2011.1)=CONCAT('2011',RAND()); +DROP TABLE t1; + + +--echo # +--echo # MDEV-8754 Wrong result for SELECT..WHERE year_field=2020 AND NULLIF(year_field,2010)='2020' +--echo # +CREATE TABLE t1 (a YEAR); +INSERT INTO t1 VALUES (2010),(2020); +SELECT * FROM t1 WHERE a=2020; +SELECT * FROM t1 WHERE NULLIF(a,2010)='2020'; +SELECT * FROM t1 WHERE a=2020 AND NULLIF(a,2010)='2020'; +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE a=2020 AND NULLIF(a,2010)='2020'; +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE a=2020 AND NULLIF(a,2010)=CONCAT('2020',RAND()); +DROP TABLE t1; + +--echo # +--echo # MDEV-9181 (NULLIF(count(table.col)), 0) gives wrong result on 10.1.x +--echo # +CREATE TABLE t1 (c1 varchar(50) DEFAULT NULL); +INSERT INTO t1 (c1) VALUES ('hello'), ('hello\r\n'), ('hello'),('hello'); +SELECT NULLIF(COUNT(c1),0) FROM t1; +SELECT CASE WHEN COUNT(c1)=0 THEN NULL ELSE COUNT(c1) END FROM t1; +SELECT NULLIF(COUNT(c1)+0,0) AS c1,NULLIF(CAST(COUNT(c1) AS SIGNED),0) AS c2,NULLIF(CONCAT(COUNT(c1)),0) AS c3 FROM t1; +SELECT NULLIF(COUNT(DISTINCT c1),0) FROM t1; +SELECT CASE WHEN COUNT(DISTINCT c1)=0 THEN NULL ELSE COUNT(DISTINCT c1) END FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 ( + id INT NOT NULL, + c1 INT DEFAULT NULL +); +INSERT INTO t1 VALUES (1,1),(1,2),(2,3),(2,4); +SELECT NULLIF(COUNT(c1),0) AS c1,NULLIF(COUNT(c1)+0,0) AS c1_wrapped,CASE WHEN COUNT(c1) IS NULL THEN 0 ELSE COUNT(c1) END AS c1_case FROM t1 GROUP BY id; +DROP TABLE t1; + +# Testing with side effects + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2),(3); +SET @a=0; +SELECT NULLIF(LAST_VALUE(@a:=@a+1,a),0) FROM t1; +SELECT @a; +SET @a=0; +SELECT NULLIF(AVG(a),0), NULLIF(AVG(LAST_VALUE(@a:=@a+1,a)),0) FROM t1; +SELECT @a; + +# There should not be cache in here: + +EXPLAIN EXTENDED SELECT NULLIF(a,0) FROM t1; + +# But there should be a cache in here: +EXPLAIN EXTENDED SELECT NULLIF(AVG(a),0) FROM t1; + +DROP TABLE t1; + +# +# MDEV-9576 syntax error on view with nullif and count +# +create table t1 (col1 varchar(50)); +create view v1 AS select nullif(count(distinct col1),0) from t1; +show create view v1; +drop view v1; +drop table t1; + +# +# MDEV-9637 select nullif(count(col1),0) gives wrong result if in a view +# +create table t1 (col1 varchar(50) default null); +insert into t1 (col1) values ('hello'), ('hello'), ('hello'); +create view v1 as select nullif(count(col1),0) from t1; +select * from v1; +select nullif(count(col1),0) from t1; +drop view v1; +drop table t1; + +# +# MDEV-9644 Assertion `args[0] == args[2] || thd->stmt_arena->is_stmt_execute()' failed in Item_func_nullif::fix_length_and_dec() +# +select nullif((select 1), (select 2)); +create table t1 (f int); +insert into t1 values (1),(2); +select nullif( not f, 1 ) from t1; +drop table t1; + +# +# MDEV-9641 Assertion `args[0] == args[2] || _current_thd()->lex->context_analysis_only' failed in Item_func_nullif::print(String*, enum_query_type) +# +set names utf8; +create table t1 (f1 varchar(10)); +insert into t1 values ('2015-12-31'); +--error ER_DATA_OUT_OF_RANGE +select power( timestamp( nullif( '2002-09-08', f1 ) ), 24 ) from t1; +drop table t1; + +# +# MDEV-9682 Assertion `0' failed in Item_cache_row::illegal_method_call on 2nd execution of PS with NULLIF +# +CREATE TABLE t1 (f1 INT); +INSERT INTO t1 VALUES (1),(2); +PREPARE stmt FROM "SELECT * FROM t1 WHERE NULLIF( ( 1, 2 ) IN ( SELECT 3, 4 ), 1 )"; +EXECUTE stmt; +EXECUTE stmt; +DROP TABLE t1; + +# +# MDEV-9683 Server crashes in Item::basic_const_item on numerous nested NULLIFs +# +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (1),(2); +SELECT * FROM t1 WHERE NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(i = ROUND(0), 14), 13), 12), 11), 10), 9), 8), 7), 6), 5), 4), 3), 2), 1); +DROP TABLE t1; + +--echo # +--echo # MDEV-10347 mysqld got signal 11 +--echo # + +CREATE TABLE t1 (f1 VARCHAR(10), f2 VARCHAR(40)); +CREATE TABLE t2 (f3 VARCHAR(20)); +PREPARE stmt FROM " + SELECT ( + SELECT IFNULL(f3,4) FROM t2 + WHERE IFNULL(NULLIF(f1,''),1) + ) AS sq + FROM t1 + GROUP BY f2 +"; +EXECUTE stmt; +DEALLOCATE PREPARE stmt; +DROP TABLE t2,t1; + +--echo # +--echo # MDEV-10236 Where expression with NOT function gives incorrect result +--echo # +CREATE TABLE t1 (c1 INT); +INSERT INTO t1 VALUES (1),(2),(3); +EXPLAIN EXTENDED +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 # |