MDEV-8747 Wrong result for SELECT..WHERE derived_table_column='a' AND derived_table_column<>_latin1'A' COLLATE latin1_bin

MDEV-8749 Wrong result for SELECT..WHERE derived_table_enum_column='number' AND derived_table_enum_column OP number

5 files changed, 217 insertions, 12 deletions

diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result
index bb1e4d51dd6..a59f8a6dfee 100644
--- a/mysql-test/r/derived.result
+++ b/mysql-test/r/derived.result
@@ -604,3 +604,48 @@ where coalesce(message,0) <> 0;
 id	message
 drop table t1,t2;
 set optimizer_switch=@save_derived_optimizer_switch;
+#
+# Start of 10.1 tests
+#
+#
+# MDEV-8747 Wrong result for SELECT..WHERE derived_table_column='a' AND derived_table_column<>_latin1'A' COLLATE latin1_bin
+#
+CREATE TABLE t1 (a VARCHAR(10));
+INSERT INTO t1 VALUES ('a'),('A');
+SELECT * FROM t1 WHERE a='a' AND a <> _latin1'A' COLLATE latin1_bin;
+a
+a
+SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='a' AND a <> _latin1'A' COLLATE latin1_bin;
+a
+a
+DROP TABLE t1;
+CREATE TABLE t1 (a ENUM('5','6'));
+INSERT INTO t1 VALUES ('5'),('6');
+SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='5';
+a
+5
+SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a=1;
+a
+5
+SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='5' AND a=1;
+a
+5
+DROP TABLE t1;
+#
+# MDEV-8749 Wrong result for SELECT..WHERE derived_table_enum_column='number' AND derived_table_enum_column OP number2
+#
+CREATE TABLE t1 (a ENUM('5','6'));
+INSERT INTO t1 VALUES ('5'),('6');
+SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='5';
+a
+5
+SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a=1;
+a
+5
+SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='5' AND a=1;
+a
+5
+DROP TABLE t1;
+#
+# End of 10.1 tests
+#
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index 483d4d1ca8e..7f32643b727 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -2289,20 +2289,16 @@ field1	field2
 2004-10-11 18:13:00	1
 2009-02-19 02:05:00	5
 Warnings:
-Warning	1292	Truncated incorrect DOUBLE value: 'x'
 Warning	1292	Truncated incorrect DOUBLE value: 'g'
 Warning	1292	Truncated incorrect DOUBLE value: 'o'
-Warning	1292	Truncated incorrect DOUBLE value: 'g'
 Warning	1292	Truncated incorrect DOUBLE value: 'v'
 SELECT alias2.f3 AS field1 , alias2.f1 AS field2 FROM t1 AS alias1 JOIN t1 AS alias2 ON alias2.f1 = alias1.f2 AND alias2.f1 != alias1.f4 GROUP BY field1 , field2 ;
 field1	field2
 2004-10-11 18:13:00	1
 2009-02-19 02:05:00	5
 Warnings:
-Warning	1292	Truncated incorrect DOUBLE value: 'x'
 Warning	1292	Truncated incorrect DOUBLE value: 'g'
 Warning	1292	Truncated incorrect DOUBLE value: 'o'
-Warning	1292	Truncated incorrect DOUBLE value: 'g'
 Warning	1292	Truncated incorrect DOUBLE value: 'v'
 SET SESSION SQL_MODE=default;
 drop table t1;
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index df2b48564ad..4b46a67fc23 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -4430,36 +4430,58 @@ CREATE TABLE t1 (a varchar(10), KEY (a)) ;
 INSERT INTO t1 VALUES
 ('DD'), ('ZZ'), ('ZZ'), ('KK'), ('FF'), ('HH'),('MM');
 CREATE VIEW v1 AS SELECT * FROM t1;
+# t1 and v1 should return the same result set
 SELECT * FROM v1 WHERE a > 'JJ' OR a <> 0 AND a = 'VV';
 a
 KK
 MM
 ZZ
 ZZ
-Warnings:
-Warning	1292	Truncated incorrect DOUBLE value: 'VV'
+SELECT * FROM t1 WHERE a > 'JJ' OR a <> 0 AND a = 'VV';
+a
+KK
+MM
+ZZ
+ZZ
+# t1 and v1 should propagate constants in the same way
 EXPLAIN EXTENDED
 SELECT * FROM v1 WHERE a > 'JJ' OR a <> 0 AND a = 'VV';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	range	a	a	13	NULL	4	100.00	Using where; Using index
 Warnings:
-Warning	1292	Truncated incorrect DOUBLE value: 'VV'
-Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` > 'JJ')
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` > 'JJ') or ((`test`.`t1`.`a` = 'VV') and (`test`.`t1`.`a` <> 0)))
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE a > 'JJ' OR a <> 0 AND a = 'VV';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	range	a	a	13	NULL	4	100.00	Using where; Using index
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` > 'JJ') or ((`test`.`t1`.`a` = 'VV') and (`test`.`t1`.`a` <> 0)))
+# t1 and v1 should return the same result set
 SELECT * FROM v1 WHERE a > 'JJ' OR a AND a = 'VV';
 a
 KK
 MM
 ZZ
 ZZ
-Warnings:
-Warning	1292	Truncated incorrect DOUBLE value: 'VV'
+SELECT * FROM t1 WHERE a > 'JJ' OR a AND a = 'VV';
+a
+KK
+MM
+ZZ
+ZZ
+# t1 and v1 should propagate constants in the same way
 EXPLAIN EXTENDED
 SELECT * FROM v1 WHERE a > 'JJ' OR a AND a = 'VV';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	range	a	a	13	NULL	4	100.00	Using where; Using index
 Warnings:
-Warning	1292	Truncated incorrect DOUBLE value: 'VV'
-Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` > 'JJ')
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` > 'JJ') or ((`test`.`t1`.`a` = 'VV') and (`test`.`t1`.`a` <> 0)))
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE a > 'JJ' OR a AND a = 'VV';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	range	a	a	13	NULL	4	100.00	Using where; Using index
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` > 'JJ') or ((`test`.`t1`.`a` = 'VV') and (`test`.`t1`.`a` <> 0)))
 DROP VIEW v1;
 DROP TABLE t1;
 #
@@ -5571,3 +5593,55 @@ drop view v3;
 # -- End of 10.0 tests.
 # -----------------------------------------------------------------
 SET optimizer_switch=@save_optimizer_switch;
+#
+# Start of 10.1 tests
+#
+#
+# MDEV-8747 Wrong result for SELECT..WHERE derived_table_column='a' AND derived_table_column<>_latin1'A' COLLATE latin1_bin
+#
+CREATE TABLE t1 (a varchar(10) character set cp1251 collate cp1251_ukrainian_ci, KEY (a)) ;
+INSERT INTO t1 VALUES ('DD'), ('ZZ'), ('ZZ'), ('KK'), ('FF'), ('HH'),('MM'),('`1');
+CREATE VIEW v1 AS SELECT * FROM t1;
+SELECT * FROM t1 WHERE a <> 0 AND a = ' 1';
+a
+SELECT * FROM v1 WHERE a <> 0 AND a = ' 1';
+a
+DROP VIEW v1;
+DROP TABLE t1;
+CREATE TABLE t1 (a ENUM('5','6'));
+INSERT INTO t1 VALUES ('5'),('6');
+CREATE VIEW v1 AS SELECT * FROM t1;
+SELECT * FROM t1 WHERE a='5' AND a<2;
+a
+5
+SELECT * FROM v1 WHERE a='5' AND a<2;
+a
+5
+DROP VIEW v1;
+DROP TABLE t1;
+#
+# MDEV-8749 Wrong result for SELECT..WHERE derived_table_enum_column='number' AND derived_table_enum_column OP number2
+#
+CREATE TABLE t1 (a varchar(10) character set cp1251 collate cp1251_ukrainian_ci, KEY (a));
+INSERT INTO t1 VALUES ('DD'), ('ZZ'), ('ZZ'), ('KK'), ('FF'), ('HH'),('MM'),('`1');
+CREATE VIEW v1 AS SELECT * FROM t1;
+SELECT * FROM t1 WHERE a <> 0 AND a = ' 1';
+a
+SELECT * FROM v1 WHERE a <> 0 AND a = ' 1';
+a
+DROP VIEW v1;
+DROP TABLE t1;
+CREATE TABLE t1 (a ENUM('5','6'));
+INSERT INTO t1 VALUES ('5'),('6');
+CREATE VIEW v1 AS SELECT * FROM t1;
+SELECT * FROM t1 WHERE a='5' AND a<2;
+a
+5
+SELECT * FROM v1 WHERE a='5' AND a<2;
+a
+5
+DROP VIEW v1;
+DROP TABLE t1;
+#
+# End of 10.1 tests
+#
diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test
index d98e7b56905..38636b0e971 100644
--- a/mysql-test/t/derived.test
+++ b/mysql-test/t/derived.test
@@ -539,3 +539,37 @@ where coalesce(message,0) <> 0;
 drop table t1,t2;
 
 set optimizer_switch=@save_derived_optimizer_switch;
+
+--echo #
+--echo # Start of 10.1 tests
+--echo #
+
+--echo #
+--echo # MDEV-8747 Wrong result for SELECT..WHERE derived_table_column='a' AND derived_table_column<>_latin1'A' COLLATE latin1_bin
+--echo #
+CREATE TABLE t1 (a VARCHAR(10));
+INSERT INTO t1 VALUES ('a'),('A');
+SELECT * FROM t1 WHERE a='a' AND a <> _latin1'A' COLLATE latin1_bin;
+SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='a' AND a <> _latin1'A' COLLATE latin1_bin;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a ENUM('5','6'));
+INSERT INTO t1 VALUES ('5'),('6');
+SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='5';
+SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a=1;
+SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='5' AND a=1;
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-8749 Wrong result for SELECT..WHERE derived_table_enum_column='number' AND derived_table_enum_column OP number2
+--echo #
+CREATE TABLE t1 (a ENUM('5','6'));
+INSERT INTO t1 VALUES ('5'),('6');
+SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='5';
+SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a=1;
+SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='5' AND a=1;
+DROP TABLE t1;
+
+--echo #
+--echo # End of 10.1 tests
+--echo #
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index 26faae545d8..d580e98ecb6 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -4318,13 +4318,23 @@ INSERT INTO t1 VALUES
 
 CREATE VIEW v1 AS SELECT * FROM t1;
 
+--echo # t1 and v1 should return the same result set
 SELECT * FROM v1 WHERE a > 'JJ' OR a <> 0 AND a = 'VV'; 
+SELECT * FROM t1 WHERE a > 'JJ' OR a <> 0 AND a = 'VV';
+--echo # t1 and v1 should propagate constants in the same way
 EXPLAIN EXTENDED
 SELECT * FROM v1 WHERE a > 'JJ' OR a <> 0 AND a = 'VV'; 
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE a > 'JJ' OR a <> 0 AND a = 'VV';
 
+--echo # t1 and v1 should return the same result set
 SELECT * FROM v1 WHERE a > 'JJ' OR a AND a = 'VV'; 
+SELECT * FROM t1 WHERE a > 'JJ' OR a AND a = 'VV';
+--echo # t1 and v1 should propagate constants in the same way
 EXPLAIN EXTENDED
 SELECT * FROM v1 WHERE a > 'JJ' OR a AND a = 'VV'; 
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE a > 'JJ' OR a AND a = 'VV';
 
 DROP VIEW v1;
 DROP TABLE t1;
@@ -5450,3 +5460,49 @@ drop view v3;
 --echo # -- End of 10.0 tests.
 --echo # -----------------------------------------------------------------
 SET optimizer_switch=@save_optimizer_switch;
+
+--echo #
+--echo # Start of 10.1 tests
+--echo #
+
+--echo #
+--echo # MDEV-8747 Wrong result for SELECT..WHERE derived_table_column='a' AND derived_table_column<>_latin1'A' COLLATE latin1_bin
+--echo #
+CREATE TABLE t1 (a varchar(10) character set cp1251 collate cp1251_ukrainian_ci, KEY (a)) ;
+INSERT INTO t1 VALUES ('DD'), ('ZZ'), ('ZZ'), ('KK'), ('FF'), ('HH'),('MM'),('`1');
+CREATE VIEW v1 AS SELECT * FROM t1;
+SELECT * FROM t1 WHERE a <> 0 AND a = ' 1';
+SELECT * FROM v1 WHERE a <> 0 AND a = ' 1';
+DROP VIEW v1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a ENUM('5','6'));
+INSERT INTO t1 VALUES ('5'),('6');
+CREATE VIEW v1 AS SELECT * FROM t1;
+SELECT * FROM t1 WHERE a='5' AND a<2;
+SELECT * FROM v1 WHERE a='5' AND a<2;
+DROP VIEW v1;
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-8749 Wrong result for SELECT..WHERE derived_table_enum_column='number' AND derived_table_enum_column OP number2
+--echo #
+CREATE TABLE t1 (a varchar(10) character set cp1251 collate cp1251_ukrainian_ci, KEY (a));
+INSERT INTO t1 VALUES ('DD'), ('ZZ'), ('ZZ'), ('KK'), ('FF'), ('HH'),('MM'),('`1');
+CREATE VIEW v1 AS SELECT * FROM t1;
+SELECT * FROM t1 WHERE a <> 0 AND a = ' 1';
+SELECT * FROM v1 WHERE a <> 0 AND a = ' 1';
+DROP VIEW v1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a ENUM('5','6'));
+INSERT INTO t1 VALUES ('5'),('6');
+CREATE VIEW v1 AS SELECT * FROM t1;
+SELECT * FROM t1 WHERE a='5' AND a<2;
+SELECT * FROM v1 WHERE a='5' AND a<2;
+DROP VIEW v1;
+DROP TABLE t1;
+
+--echo #
+--echo # End of 10.1 tests
+--echo #