Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 1190 insertions, 0 deletions

diff --git a/mysql-test/main/null.test b/mysql-test/main/null.test
new file mode 100644
index 00000000..7256ce1c
--- /dev/null
+++ b/mysql-test/main/null.test
@@ -0,0 +1,1190 @@
+--echo #
+--echo # Testing of NULL in a lot of different places
+--echo #
+
+#enable view protocol after fix MDEV-28535
+--disable_view_protocol
+
+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;
+
+--enable_view_protocol
+
+create table t1 (x int);
+insert into t1 values (null);
+select * from t1 where x != 0;
+drop table t1;
+
+--echo #
+--echo # Test problem med index on NULL columns and testing with =NULL;
+--echo #
+
+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;
+
+--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
+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;
+
+--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 ER_BAD_NULL_ERROR
+INSERT INTO t1 (a) values (null);
+--error ER_BAD_NULL_ERROR
+INSERT INTO t1 (a) values (1/null);
+INSERT IGNORE INTO t1 (a) values (null),(null);
+--error ER_BAD_NULL_ERROR
+INSERT INTO t1 (b) values (null);
+--error ER_BAD_NULL_ERROR
+INSERT INTO t1 (b) values (1/null);
+INSERT IGNORE INTO t1 (b) values (null),(null);
+--error ER_BAD_NULL_ERROR
+INSERT INTO t1 (c) values (null);
+--error ER_BAD_NULL_ERROR
+INSERT INTO t1 (c) values (1/null);
+INSERT IGNORE INTO t1 (c) values (null),(null);
+--error ER_BAD_NULL_ERROR
+INSERT INTO t1 (d) values (null);
+--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;
+
+--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),
+  (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);
+
+--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;
+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;
+
+--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
+  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;
+
+--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,
+  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;
+
+--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 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 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;
+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 ER_INVALID_DEFAULT
+alter table bug19145c alter column e set default null;
+
+# Invalid default value for 's'
+--error ER_INVALID_DEFAULT
+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 #
+--echo # End of 4.1 tests
+--echo #
+
+--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 #
+--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
+--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 # 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; 
+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 # 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 #
+--enable_prepare_warnings
+CREATE TABLE t1 (a YEAR(2));
+--disable_prepare_warnings
+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 as exp 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
+
+--disable_ps2_protocol
+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;
+--enable_ps2_protocol
+
+# 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)
+#
+
+#it is necessary that the view protocol uses the same connection,
+# not util connection
+--disable_service_connection
+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;
+--enable_service_connection
+
+#
+# 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 #