MDEV-20773 Error from UPDATE when estimating selectivity of a range

This bug could affect multi-update statements as well as single-table
update statements processed as multi-updates when the where condition
contained a range condition over a non-indexed varchar column. The
optimizer calculates selectivity of such range conditions using histograms.
For each range the buckets containing endpoints of the the range are
determined with a procedure that stores the values of the endpoints in the
space of the record buffer where values of the columns are usually stored.
For a range over a varchar column the value of a endpoint may exceed the
size of the buffer and in such case the value is stored with truncation.
This truncations cannot affect the result of the calculation of the range
selectivity as the calculation employes only the beginning of the value
string. However it can trigger generation of an unexpected error on this
truncation if an update statement is processed.
This patch prohibits truncation messages when selectivity of a range
condition is calculated for a non-indexed column.

Approved by Oleksandr Byelkin <sanja@mariadb.com>

3 files changed, 55 insertions, 0 deletions

diff --git a/mysql-test/main/update.result b/mysql-test/main/update.result
index f5edf1c6be3..7b6426d2ec5 100644
--- a/mysql-test/main/update.result
+++ b/mysql-test/main/update.result
@@ -734,3 +734,32 @@ UPDATE t1,t2 SET t1.i1 = -39 WHERE t2.d1 <> t1.i1 AND t2.d1 = t1.d2;
 ERROR 22007: Incorrect datetime value: '19' for column `test`.`t1`.`i1` at row 1
 DROP TABLE t1,t2;
 # End of MariaDB 10.2 tests
+#
+# MDEV-20773: UPDATE with LIKE predicate over non-indexed column
+#             of VARCHAR type
+#
+create table t1 (a1 varchar(30), a2 varchar(30) collate utf8_bin);
+insert into t1 values
+('aa','zzz'), ('b','xxaa'), ('ccc','yyy'), ('ddd','xxb');
+analyze table t1 persistent for all;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+explain extended
+update t1 set a1 = 'u'
+  where a2 like 'xx%' and exists(select 1 from t1 where t1.a1 < 'c');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	49.22	Using where
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	4	50.00	Using where
+Warnings:
+Note	1003	/* select#1 */ update `test`.`t1` set `test`.`t1`.`a1` = 'u' where `test`.`t1`.`a2` like 'xx%'
+update t1 set a1 = 'u'
+  where a2 like 'xx%' and exists(select 1 from t1 where t1.a1 < 'c');
+select * from t1;
+a1	a2
+aa	zzz
+u	xxaa
+ccc	yyy
+u	xxb
+drop table t1;
+# End of MariaDB 10.4 tests
diff --git a/mysql-test/main/update.test b/mysql-test/main/update.test
index 8a6949447ee..147d69d50c9 100644
--- a/mysql-test/main/update.test
+++ b/mysql-test/main/update.test
@@ -676,3 +676,26 @@ UPDATE t1,t2 SET t1.i1 = -39 WHERE t2.d1 <> t1.i1 AND t2.d1 = t1.d2;
 DROP TABLE t1,t2;
 
 --echo # End of MariaDB 10.2 tests
+
+--echo #
+--echo # MDEV-20773: UPDATE with LIKE predicate over non-indexed column
+--echo #             of VARCHAR type
+--echo #
+
+create table t1 (a1 varchar(30), a2 varchar(30) collate utf8_bin);
+insert into t1 values
+  ('aa','zzz'), ('b','xxaa'), ('ccc','yyy'), ('ddd','xxb');
+analyze table t1 persistent for all;
+
+explain extended
+update t1 set a1 = 'u'
+  where a2 like 'xx%' and exists(select 1 from t1 where t1.a1 < 'c');
+
+update t1 set a1 = 'u'
+  where a2 like 'xx%' and exists(select 1 from t1 where t1.a1 < 'c');
+
+select * from t1;
+
+drop table t1;
+
+--echo # End of MariaDB 10.4 tests
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index c34420181a2..69a95f8da44 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -3518,7 +3518,10 @@ bool calculate_cond_selectivity_for_table(THD *thd, TABLE *table, Item **cond)
         }          
         else
         {
+          enum_check_fields save_count_cuted_fields= thd->count_cuted_fields;
+          thd->count_cuted_fields= CHECK_FIELD_IGNORE;
           rows= records_in_column_ranges(&param, idx, key);
+          thd->count_cuted_fields= save_count_cuted_fields;
           if (rows != DBL_MAX)
           {
             key->field->cond_selectivity= rows/table_records;