1. When mixing NULL to a character string,

the result takes its charset/collation
attributes from the character string,
e.g.  SELECT func(NULL, _latin2'string')
now returns a latin2 result. This is
done by introducing a new derivation
(aka coercibility) level DERIVATION_IGNORABLE,
which is used with Item_null.
2. 'Pure' NULL is now BINARY(0), not CHAR(0).
I.e. NULL is now more typeless.


mysql-test/r/metadata.result:
  Fixing test results:
  CHAR(0) -> BINARY(0) for NULLs
mysql-test/r/null.result:
  Testing mixing NULL with a character string with a number of functions.
mysql-test/r/ps_2myisam.result:
  Fixing test results:
  CHAR(0) -> BINARY(0) for NULLs
mysql-test/r/ps_3innodb.result:
  Fixing test results:
  CHAR(0) -> BINARY(0) for NULLs
mysql-test/r/ps_4heap.result:
  Fixing test results:
  CHAR(0) -> BINARY(0) for NULLs
mysql-test/r/ps_5merge.result:
  Fixing test results:
  CHAR(0) -> BINARY(0) for NULLs
mysql-test/r/ps_6bdb.result:
  Fixing test results:
  CHAR(0) -> BINARY(0) for NULLs
mysql-test/r/ps_7ndb.result:
  Fixing test results:
  CHAR(0) -> BINARY(0) for NULLs
mysql-test/t/null.test:
  Testing mixing NULL with a character string with a number of functions.
sql/item.cc:
  New derivation level.
sql/item.h:
  New derivation level.

1 files changed, 94 insertions, 0 deletions

diff --git a/mysql-test/r/null.result b/mysql-test/r/null.result
index bd90b3fe3f3..3e233eb512a 100644
--- a/mysql-test/r/null.result
+++ b/mysql-test/r/null.result
@@ -175,3 +175,97 @@ explain select * from t1 where i=2 or i is null;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ref	i	i	4	const	7	Using where; Using index
 drop table t1;
+set names latin2;
+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;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c00` binary(0) default NULL,
+  `c01` varchar(6) character set latin2 default NULL,
+  `c02` varchar(6) character set latin2 default NULL,
+  `c03` varchar(6) character set latin2 NOT NULL default '',
+  `c04` varchar(6) character set latin2 default NULL,
+  `c05` varchar(6) character set latin2 default NULL,
+  `c06` varchar(6) character set latin2 default NULL,
+  `c07` varchar(6) character set latin2 default NULL,
+  `c08` varchar(6) character set latin2 default NULL,
+  `c09` varchar(6) character set latin2 NOT NULL default '',
+  `c10` varchar(6) character set latin2 NOT NULL default '',
+  `c11` varchar(6) character set latin2 NOT NULL default '',
+  `c12` varchar(6) character set latin2 NOT NULL default '',
+  `c13` varchar(6) character set latin2 default NULL,
+  `c14` char(0) character set latin2 default NULL,
+  `c15` char(0) character set latin2 default NULL,
+  `c16` varchar(6) character set latin2 default NULL,
+  `c17` varchar(6) character set latin2 default NULL,
+  `c18` char(0) character set latin2 default NULL,
+  `c19` varchar(6) character set latin2 default NULL,
+  `c20` varchar(6) character set latin2 default NULL,
+  `c21` varchar(6) character set latin2 default NULL,
+  `c22` varchar(6) character set latin2 default NULL,
+  `c23` varchar(9) character set latin2 default NULL,
+  `c24` varchar(9) character set latin2 default NULL,
+  `c25` varchar(12) character set latin2 default NULL,
+  `c26` varchar(7) character set latin2 default NULL,
+  `c27` varchar(7) character set latin2 default NULL,
+  `c29` longtext character set latin2,
+  `c30` longtext character set latin2,
+  `c31` varchar(192) character set latin2 default NULL,
+  `c32` char(0) character set latin2 default NULL,
+  `c33` char(3) character set latin2 default NULL,
+  `c34` char(3) character set latin2 default NULL,
+  `c35` char(3) character set latin2 default NULL,
+  `c36` char(3) character set latin2 default NULL,
+  `c37` varchar(10) character set latin2 default NULL,
+  `c38` varchar(10) character set latin2 default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop table t1;
+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;
+c01	c02	c03	c04	c05	c08	c09
+str	str	0	1	2	1	1
+set names latin1;