Another attempt to fix bug mdev-5103.

The earlier pushed fix for the bug was incomplete. It did not remove
the main cause of the problem: the function remove_eq_conds()
removed always true multiple equalities from any conjunct, but did not
adjust the list of them stored in Item_cond_and::cond_equal.current_level.

Simplified the test case for the bug and moved it to another test file.

The fix triggered changes in EXPLAIN EXTENDED for some queries. 

11 files changed, 55 insertions, 102 deletions

diff --git a/mysql-test/r/func_equal.result b/mysql-test/r/func_equal.result
index 02593529a91..f20b259191c 100644
--- a/mysql-test/r/func_equal.result
+++ b/mysql-test/r/func_equal.result
@@ -43,46 +43,3 @@ a
 4828532208463511553
 drop table t1;
 #End of 4.1 tests
-#
-# MDEV-5103: server crashed on singular Item_equal
-#
-CREATE TABLE `t1` (
-`tipo` enum('p','r') NOT NULL DEFAULT 'r',
-`arquivo_id` bigint(20) unsigned NOT NULL DEFAULT '0',
-`arquivo_md5` char(32) NOT NULL,
-`conteudo` longblob NOT NULL,
-`usuario` varchar(15) NOT NULL,
-`datahora_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
-`tipo_arquivo` varchar(255) NOT NULL,
-`nome_arquivo` varchar(255) NOT NULL,
-`tamanho_arquivo` bigint(20) unsigned NOT NULL DEFAULT '0',
-PRIMARY KEY (`tipo`,`arquivo_id`),
-UNIQUE KEY `tipo` (`tipo`,`arquivo_md5`)
-) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;
-INSERT INTO `t1` (`tipo`, `arquivo_id`, `arquivo_md5`, `conteudo`, `usuario`, `datahora_gmt`, `tipo_arquivo`, `nome_arquivo`, `tamanho_arquivo`) VALUES
-('r', 1, 'ad18832202b199728921807033a8a515', '', 'rspadim', '2013-10-05 13:55:50', '001_cbr643', 'CBR6431677410201314132.ret', 21306);
-CREATE TABLE `t2` (
-`tipo` enum('p','r') NOT NULL DEFAULT 'p',
-`arquivo_id` bigint(20) NOT NULL DEFAULT '0',
-`usuario` varchar(25) NOT NULL,
-`datahora` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
-`erros` longblob NOT NULL,
-`importados` bigint(20) unsigned NOT NULL DEFAULT '0',
-`n_importados` bigint(20) unsigned NOT NULL DEFAULT '0',
-PRIMARY KEY (`tipo`,`arquivo_id`,`datahora`)
-) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;
-INSERT INTO `t2` (`tipo`, `arquivo_id`, `usuario`, `datahora`, `erros`, `importados`, `n_importados`) VALUES
-('r', 1, 'rspadim', '2013-10-05 14:25:30', '', 32, 0);
-SELECT
-arquivo_id,usuario,datahora_gmt,tipo_arquivo,nome_arquivo,tamanho_arquivo
-FROM t1 AS a
-WHERE datahora_gmt>='0000-00-00 00:00:00' AND
-datahora_gmt<='2013-10-07 02:59:59' AND tipo='r' AND
-(tipo_arquivo,arquivo_id) NOT IN
-(SELECT tipo_arquivo,arquivo_id
-FROM t2
-WHERE (tipo_arquivo,arquivo_id)=(a.tipo_arquivo,a.arquivo_id))
-ORDER BY arquivo_id DESC;
-arquivo_id	usuario	datahora_gmt	tipo_arquivo	nome_arquivo	tamanho_arquivo
-drop table t2, t1;
-#End of 5.3 tests
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index 284a285dbe3..63d341d1c33 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -1773,7 +1773,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	const	PRIMARY,idx	PRIMARY	4	const	1	100.00	
 1	SIMPLE	t2	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
 Warnings:
-Note	1003	select 5 AS `pk` from `test`.`t2` join `test`.`t1` where ((((1 between 5 and 6) and isnull(5)) or 1)) order by 5
+Note	1003	select 5 AS `pk` from `test`.`t2` join `test`.`t1` where 1 order by 5
 SELECT t1.pk FROM t2 LEFT JOIN t1 ON t2.pk = t1.a
 WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5
 ORDER BY t1.pk;
diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result
index 69fe7cbd6b9..eff47bf7c31 100644
--- a/mysql-test/r/join_outer_jcl6.result
+++ b/mysql-test/r/join_outer_jcl6.result
@@ -1784,7 +1784,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	const	PRIMARY,idx	PRIMARY	4	const	1	100.00	
 1	SIMPLE	t2	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
 Warnings:
-Note	1003	select 5 AS `pk` from `test`.`t2` join `test`.`t1` where ((((1 between 5 and 6) and isnull(5)) or 1)) order by 5
+Note	1003	select 5 AS `pk` from `test`.`t2` join `test`.`t1` where 1 order by 5
 SELECT t1.pk FROM t2 LEFT JOIN t1 ON t2.pk = t1.a
 WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5
 ORDER BY t1.pk;
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index f0de9622368..e8bcba8043a 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -5125,7 +5125,7 @@ SELECT * FROM t1 WHERE (1=2 OR t1.pk=2) AND t1.a <> 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 Warnings:
-Note	1003	select 2 AS `pk`,0 AS `a` from `test`.`t1` where ((0 <> 0))
+Note	1003	select 2 AS `pk`,0 AS `a` from `test`.`t1` where (0 <> 0)
 DROP TABLE t1;
 SELECT * FROM mysql.time_zone 
 WHERE ( NOT (Use_leap_seconds <= Use_leap_seconds AND Time_zone_id != 1) 
diff --git a/mysql-test/r/select_jcl6.result b/mysql-test/r/select_jcl6.result
index 3c3d6188301..c8d206e161d 100644
--- a/mysql-test/r/select_jcl6.result
+++ b/mysql-test/r/select_jcl6.result
@@ -5136,7 +5136,7 @@ SELECT * FROM t1 WHERE (1=2 OR t1.pk=2) AND t1.a <> 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 Warnings:
-Note	1003	select 2 AS `pk`,0 AS `a` from `test`.`t1` where ((0 <> 0))
+Note	1003	select 2 AS `pk`,0 AS `a` from `test`.`t1` where (0 <> 0)
 DROP TABLE t1;
 SELECT * FROM mysql.time_zone 
 WHERE ( NOT (Use_leap_seconds <= Use_leap_seconds AND Time_zone_id != 1) 
diff --git a/mysql-test/r/select_pkeycache.result b/mysql-test/r/select_pkeycache.result
index f0de9622368..e8bcba8043a 100644
--- a/mysql-test/r/select_pkeycache.result
+++ b/mysql-test/r/select_pkeycache.result
@@ -5125,7 +5125,7 @@ SELECT * FROM t1 WHERE (1=2 OR t1.pk=2) AND t1.a <> 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 Warnings:
-Note	1003	select 2 AS `pk`,0 AS `a` from `test`.`t1` where ((0 <> 0))
+Note	1003	select 2 AS `pk`,0 AS `a` from `test`.`t1` where (0 <> 0)
 DROP TABLE t1;
 SELECT * FROM mysql.time_zone 
 WHERE ( NOT (Use_leap_seconds <= Use_leap_seconds AND Time_zone_id != 1) 
diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index 83716429efe..20cb28c3ffb 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -2326,5 +2326,27 @@ x	NULL	NULL	NULL
 d	NULL	NULL	NULL
 drop table t1,t2;
 set @@optimizer_switch = @optimizer_switch_MDEV4056;
+#
+# MDEV-5103: server crashed on singular Item_equal
+#
+CREATE TABLE t1 (
+a enum('p','r') NOT NULL DEFAULT 'r',
+b int NOT NULL DEFAULT '0',
+c char(32) NOT NULL,
+d varchar(255) NOT NULL,
+PRIMARY KEY (a, b), UNIQUE KEY idx(a, c)
+);
+INSERT INTO t1 VALUES ('r', 1, 'ad18832202b199728921807033a8a515', '001_cbr643');
+CREATE TABLE t2 (
+a enum('p','r') NOT NULL DEFAULT 'r',
+b int NOT NULL DEFAULT '0',
+e datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
+PRIMARY KEY (a, b, e)
+);
+INSERT INTO t2 VALUES ('r', 1, '2013-10-05 14:25:30');
+SELECT * FROM t1 AS t 
+WHERE a='r' AND (c,b) NOT IN (SELECT c,b FROM t2 WHERE (c,b)=(t.c,t.b));
+a	b	c	d
+DROP TABLE t1, t2;
 SET optimizer_switch= @@global.optimizer_switch;
 set @@tmp_table_size= @@global.tmp_table_size;
diff --git a/mysql-test/r/subselect_extra.result b/mysql-test/r/subselect_extra.result
index 53a38bf6e8e..d29d57c764c 100644
--- a/mysql-test/r/subselect_extra.result
+++ b/mysql-test/r/subselect_extra.result
@@ -46,7 +46,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 Warnings:
 Note	1276	Field or reference 'test.t1.cur_date' of SELECT #2 was resolved in SELECT #1
-Note	1003	select 1 AS `id`,'2007-04-25 18:30:22' AS `cur_date` from `test`.`t1` semi join (`test`.`t1` `x1`) where (('2007-04-25 18:30:22' = 0))
+Note	1003	select 1 AS `id`,'2007-04-25 18:30:22' AS `cur_date` from `test`.`t1` semi join (`test`.`t1` `x1`) where ('2007-04-25 18:30:22' = 0)
 select * from t1
 where id in (select id from t1 as x1 where (t1.cur_date is null));
 id	cur_date
@@ -57,7 +57,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 Warnings:
 Note	1276	Field or reference 'test.t2.cur_date' of SELECT #2 was resolved in SELECT #1
-Note	1003	select 1 AS `id`,'2007-04-25' AS `cur_date` from `test`.`t2` semi join (`test`.`t2` `x1`) where (('2007-04-25' = 0))
+Note	1003	select 1 AS `id`,'2007-04-25' AS `cur_date` from `test`.`t2` semi join (`test`.`t2` `x1`) where ('2007-04-25' = 0)
 select * from t2
 where id in (select id from t2 as x1 where (t2.cur_date is null));
 id	cur_date
diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result
index de0bc2a0b80..861ae974b9a 100644
--- a/mysql-test/r/type_datetime.result
+++ b/mysql-test/r/type_datetime.result
@@ -519,7 +519,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 Warnings:
 Note	1276	Field or reference 'test.t1.cur_date' of SELECT #2 was resolved in SELECT #1
-Note	1003	select 1 AS `id`,'2007-04-25 18:30:22' AS `cur_date` from `test`.`t1` semi join (`test`.`t1` `x1`) where (('2007-04-25 18:30:22' = 0))
+Note	1003	select 1 AS `id`,'2007-04-25 18:30:22' AS `cur_date` from `test`.`t1` semi join (`test`.`t1` `x1`) where ('2007-04-25 18:30:22' = 0)
 select * from t1
 where id in (select id from t1 as x1 where (t1.cur_date is null));
 id	cur_date
@@ -530,7 +530,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 Warnings:
 Note	1276	Field or reference 'test.t2.cur_date' of SELECT #2 was resolved in SELECT #1
-Note	1003	select 1 AS `id`,'2007-04-25' AS `cur_date` from `test`.`t2` semi join (`test`.`t2` `x1`) where (('2007-04-25' = 0))
+Note	1003	select 1 AS `id`,'2007-04-25' AS `cur_date` from `test`.`t2` semi join (`test`.`t2` `x1`) where ('2007-04-25' = 0)
 select * from t2
 where id in (select id from t2 as x1 where (t2.cur_date is null));
 id	cur_date
diff --git a/mysql-test/t/func_equal.test b/mysql-test/t/func_equal.test
index 990b6d8e74e..f17ebb5bd84 100644
--- a/mysql-test/t/func_equal.test
+++ b/mysql-test/t/func_equal.test
@@ -44,53 +44,3 @@ select * from t1 where a in ('4828532208463511553');
 drop table t1;
 
 --echo #End of 4.1 tests
-
---echo #
---echo # MDEV-5103: server crashed on singular Item_equal
---echo #
-
-CREATE TABLE `t1` (
-  `tipo` enum('p','r') NOT NULL DEFAULT 'r',
-  `arquivo_id` bigint(20) unsigned NOT NULL DEFAULT '0',
-  `arquivo_md5` char(32) NOT NULL,
-  `conteudo` longblob NOT NULL,
-  `usuario` varchar(15) NOT NULL,
-  `datahora_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
-  `tipo_arquivo` varchar(255) NOT NULL,
-  `nome_arquivo` varchar(255) NOT NULL,
-  `tamanho_arquivo` bigint(20) unsigned NOT NULL DEFAULT '0',
-  PRIMARY KEY (`tipo`,`arquivo_id`),
-  UNIQUE KEY `tipo` (`tipo`,`arquivo_md5`)
-) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;
-
-INSERT INTO `t1` (`tipo`, `arquivo_id`, `arquivo_md5`, `conteudo`, `usuario`, `datahora_gmt`, `tipo_arquivo`, `nome_arquivo`, `tamanho_arquivo`) VALUES
-	('r', 1, 'ad18832202b199728921807033a8a515', '', 'rspadim', '2013-10-05 13:55:50', '001_cbr643', 'CBR6431677410201314132.ret', 21306);
-
-
-CREATE TABLE `t2` (
-  `tipo` enum('p','r') NOT NULL DEFAULT 'p',
-  `arquivo_id` bigint(20) NOT NULL DEFAULT '0',
-  `usuario` varchar(25) NOT NULL,
-  `datahora` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
-  `erros` longblob NOT NULL,
-  `importados` bigint(20) unsigned NOT NULL DEFAULT '0',
-  `n_importados` bigint(20) unsigned NOT NULL DEFAULT '0',
-  PRIMARY KEY (`tipo`,`arquivo_id`,`datahora`)
-) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;
-
-INSERT INTO `t2` (`tipo`, `arquivo_id`, `usuario`, `datahora`, `erros`, `importados`, `n_importados`) VALUES
-	('r', 1, 'rspadim', '2013-10-05 14:25:30', '', 32, 0);
-
-SELECT
-arquivo_id,usuario,datahora_gmt,tipo_arquivo,nome_arquivo,tamanho_arquivo
-  FROM t1 AS a
-  WHERE datahora_gmt>='0000-00-00 00:00:00' AND
-    datahora_gmt<='2013-10-07 02:59:59' AND tipo='r' AND
-    (tipo_arquivo,arquivo_id) NOT IN
-      (SELECT tipo_arquivo,arquivo_id
-        FROM t2
-        WHERE (tipo_arquivo,arquivo_id)=(a.tipo_arquivo,a.arquivo_id))
-  ORDER BY arquivo_id DESC;
-
-drop table t2, t1;
---echo #End of 5.3 tests
diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test
index 51247e2c3ea..ad0e135f37e 100644
--- a/mysql-test/t/subselect4.test
+++ b/mysql-test/t/subselect4.test
@@ -1910,7 +1910,31 @@ WHERE b IS NULL OR a < 'u';
 drop table t1,t2;
 set @@optimizer_switch = @optimizer_switch_MDEV4056;
 
+--echo #
+--echo # MDEV-5103: server crashed on singular Item_equal
+--echo #
+
+CREATE TABLE t1 (
+  a enum('p','r') NOT NULL DEFAULT 'r',
+  b int NOT NULL DEFAULT '0',
+  c char(32) NOT NULL,
+  d varchar(255) NOT NULL,
+  PRIMARY KEY (a, b), UNIQUE KEY idx(a, c)
+);
+INSERT INTO t1 VALUES ('r', 1, 'ad18832202b199728921807033a8a515', '001_cbr643');
+
+CREATE TABLE t2 (
+  a enum('p','r') NOT NULL DEFAULT 'r',
+  b int NOT NULL DEFAULT '0',
+  e datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
+  PRIMARY KEY (a, b, e)
+);
+INSERT INTO t2 VALUES ('r', 1, '2013-10-05 14:25:30');
+
+SELECT * FROM t1 AS t 
+  WHERE a='r' AND (c,b) NOT IN (SELECT c,b FROM t2 WHERE (c,b)=(t.c,t.b));
 
+DROP TABLE t1, t2;
 
 SET optimizer_switch= @@global.optimizer_switch;
 set @@tmp_table_size= @@global.tmp_table_size;