summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorAlexander Barkov <bar@mariadb.com>2022-01-18 15:32:01 +0400
committerAlexander Barkov <bar@mariadb.com>2022-01-18 15:32:01 +0400
commitbf9bc991066df78a37e917127d61d044700c3950 (patch)
treec09d5af89e588fa64aeb9c83f7ae4e1affef6fc9
parent47e18af906f41c3b15796b8d4e6da9b744491b91 (diff)
downloadmariadb-git-bb-10.2-MDEV-26129.tar.gz
MDEV-26129 Bad results with join comparing case insensitive VARCHAR/ENUM/SET expression to a _bin ENUM columnbb-10.2-MDEV-26129
Range optimizer incorrectly was used for ENUM columns when the operation collation did not match the column collation. Adding a virtual implementation of Field_enum::can_optimize_range() which tests if the column and the operation collation match.
-rw-r--r--mysql-test/r/type_enum.result21
-rw-r--r--mysql-test/t/type_enum.test20
-rw-r--r--sql/field.cc4
-rw-r--r--sql/field.h13
4 files changed, 55 insertions, 3 deletions
diff --git a/mysql-test/r/type_enum.result b/mysql-test/r/type_enum.result
index 6ad75339847..c99e18ed109 100644
--- a/mysql-test/r/type_enum.result
+++ b/mysql-test/r/type_enum.result
@@ -2219,3 +2219,24 @@ SELECT * FROM t1;
a
DROP TABLE t1;
+#
+# MDEV-26129 Bad results with join comparing case insensitive VARCHAR/ENUM/SET expression to a _bin ENUM column
+#
+CREATE TABLE t1 (a ENUM('a') CHARACTER SET latin1 PRIMARY KEY);
+INSERT INTO t1 VALUES ('a');
+CREATE TABLE t2 (a ENUM('a','A','b','B','c','C','d','D','e','E') CHARACTER SET latin1 COLLATE latin1_bin);
+INSERT INTO t2 VALUES ('a'),('A');
+INSERT INTO t2 VALUES ('b'),('B'),('c'),('C'),('d'),('D'),('e'),('E');
+ALTER TABLE t2 ADD PRIMARY KEY(a);
+SELECT t1.a res FROM t1 JOIN t2 ON t1.a COLLATE latin1_swedish_ci=t2.a;
+res
+a
+a
+SELECT t1.a res FROM t1 LEFT JOIN t2 ON t1.a COLLATE latin1_swedish_ci=t2.a;
+res
+a
+a
+DROP TABLE IF EXISTS t1,t2;
+#
+# End of 10.2. tests
+#
diff --git a/mysql-test/t/type_enum.test b/mysql-test/t/type_enum.test
index 105da427219..2576b87ee51 100644
--- a/mysql-test/t/type_enum.test
+++ b/mysql-test/t/type_enum.test
@@ -454,3 +454,23 @@ SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
ALTER TABLE t1 MODIFY a ENUM('2001','2002');
SELECT * FROM t1;
DROP TABLE t1;
+
+--echo #
+--echo # MDEV-26129 Bad results with join comparing case insensitive VARCHAR/ENUM/SET expression to a _bin ENUM column
+--echo #
+
+CREATE TABLE t1 (a ENUM('a') CHARACTER SET latin1 PRIMARY KEY);
+INSERT INTO t1 VALUES ('a');
+CREATE TABLE t2 (a ENUM('a','A','b','B','c','C','d','D','e','E') CHARACTER SET latin1 COLLATE latin1_bin);
+INSERT INTO t2 VALUES ('a'),('A');
+# without the following insert the bug doesn't show, was fixed in MDEV-6978
+INSERT INTO t2 VALUES ('b'),('B'),('c'),('C'),('d'),('D'),('e'),('E');
+ALTER TABLE t2 ADD PRIMARY KEY(a);
+SELECT t1.a res FROM t1 JOIN t2 ON t1.a COLLATE latin1_swedish_ci=t2.a;
+SELECT t1.a res FROM t1 LEFT JOIN t2 ON t1.a COLLATE latin1_swedish_ci=t2.a;
+DROP TABLE IF EXISTS t1,t2;
+
+
+--echo #
+--echo # End of 10.2. tests
+--echo #
diff --git a/sql/field.cc b/sql/field.cc
index 4e6bc6b8341..74317a4d70b 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -9484,8 +9484,8 @@ uint Field_num::is_equal(Create_field *new_field)
}
-bool Field_enum::can_optimize_keypart_ref(const Item_bool_func *cond,
- const Item *item) const
+bool Field_enum::can_optimize_range_or_keypart_ref(const Item_bool_func *cond,
+ const Item *item) const
{
DBUG_ASSERT(cmp_type() == INT_RESULT);
DBUG_ASSERT(result_type() == STRING_RESULT);
diff --git a/sql/field.h b/sql/field.h
index 6d8e2aecd6e..78b6dcb516b 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -3623,6 +3623,8 @@ uint gis_field_options_read(const uchar *buf, uint buf_len,
class Field_enum :public Field_str {
static void do_field_enum(Copy_field *copy_field);
+ bool can_optimize_range_or_keypart_ref(const Item_bool_func *cond,
+ const Item *item) const;
protected:
uint packlength;
public:
@@ -3700,7 +3702,10 @@ public:
const uchar *from_end, uint param_data);
bool can_optimize_keypart_ref(const Item_bool_func *cond,
- const Item *item) const;
+ const Item *item) const
+ {
+ return can_optimize_range_or_keypart_ref(cond, item);
+ }
bool can_optimize_group_min_max(const Item_bool_func *cond,
const Item *const_item) const
{
@@ -3713,6 +3718,12 @@ public:
*/
return false;
}
+ bool can_optimize_range(const Item_bool_func *cond,
+ const Item *item,
+ bool is_eq_func) const
+ {
+ return can_optimize_range_or_keypart_ref(cond, item);
+ }
private:
int do_save_field_metadata(uchar *first_byte);
uint is_equal(Create_field *new_field);