MySQL Bugs: #104720: Ref scan with impossible where condition returns wrong result

Bug #104720	Ref scan with impossible where condition returns wrong result
Submitted:	25 Aug 2021 13:08	Modified:	26 Aug 2021 12:07
Reporter:	ting du	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Ref scan with a impossible where condition returns wrong result, it seems to do not check the condition.

How to repeat:
CREATE TABLE `t` (
  `c1` double unsigned,
  KEY `idx` (`c1`)
);
insert into t select 0;
SELECT c1, LEAST("-1", 1)=c1 from t where LEAST("-1", 1)=c1;

mysql> explain format=tree SELECT c1, LEAST("-1", 1)=c1 from t where LEAST("-1", 1)=c1;
+------------------------------------------------------------------------+
| EXPLAIN                                                                |
+------------------------------------------------------------------------+
| -> Index lookup on t using idx (c1=least('-1',1))  (cost=0.35 rows=1)
 |
+------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT c1, LEAST("-1", 1)=c1 from t where LEAST("-1", 1)=c1;
+------+-------------------+
| c1   | LEAST("-1", 1)=c1 |
+------+-------------------+
|    0 |                 0 |
+------+-------------------+
1 row in set (0.01 sec)

the where condition is 0, but this query returns one row.

Hi Mr. du,

Thank you for your bug report.

However, this is not a bug.

Simply, your query is not written correctly. First of all, in any where and especially in the WHERE clause, you can not compare strings with integers and then integer with floating point numbers.

When your query is rewritten, it returns zero results.

If you run it without WHERE, it shows the correct output:

c1	LEAST(-1, 1)=c1
0	0

because -1 != 0.00.

With query like your original one, MySQL tries to do the best that it can. It has to compare the non-comparable  data types and it returns what it can. Most other relational databases return the error, but we can not yet introduce strong typing, since there are many applications that rely on the weak typing.

Not a bug.

Thank you for your reply!
But from the Reference Manual:
If the arguments comprise a mix of numbers and strings, they are compared as strings.
least() can compare the strings with numbers. 
And for "=" equal, it also can compare strings and float.  

When rewrite this query, it is optimized as "no matching row in const table". And it will scan thd index.
mysql> explain  SELECT c1, LEAST(-1, 1)=c1 from t where LEAST(-1, 1)=c1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | no matching row in const table |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
1 row in set, 1 warning (1 min 48.30 sec)

For this problem, it is due to Field_double::store(const char *to, size_t length,const CHARSET_INFO *charset) just check the error of converting string to double, and do not check the return of storing double to Field_double which  may have error due to out of range. For this query, -1 is out of range of double unsigned.  

diff --git a/sql/field.cc b/sql/field.cc
index bbf1e48bf24..5d761efc622 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -4243,6 +4243,7 @@ type_conversion_status Field_double::store(const char *from, size_t len,
                                            const CHARSET_INFO *cs) {
   int conv_error;
   type_conversion_status error = TYPE_OK;
+  type_conversion_status store_error = TYPE_OK;
   const char *end;
   double nr = my_strntod(cs, from, len, &end, &conv_error);
   if (conv_error != 0 || len == 0 ||
@@ -4253,8 +4254,8 @@ type_conversion_status Field_double::store(const char *from, size_t len,
                 1);
     error = conv_error ? TYPE_WARN_OUT_OF_RANGE : TYPE_WARN_TRUNCATED;
   }
-  Field_double::store(nr);
-  return error;
+  store_error = Field_double::store(nr);
+  return error != TYPE_OK ? error : store_error;
 }
this patch can solve this problem.  

As same time, Field_xxx has same problem, such as Field_float.

Sorry, change
"When rewrite this query, it is optimized as "no matching row in const table". And it will scan thd index."
to
"When rewrite this query, it is optimized as "no matching row in const table". And it will not scan thd index."

Note that the query returns different result with and without the index:

mysql> SELECT c1, LEAST("-1", 1)=c1 from t where LEAST("-1", 1)=c1;
+------+-------------------+
| c1   | LEAST("-1", 1)=c1 |
+------+-------------------+
|    0 |                 0 |
+------+-------------------+
1 row in set (0.00 sec)

mysql> SELECT c1, LEAST("-1", 1)=c1 from t ignore index (idx) where LEAST("-1", 1)=c1;
Empty set (0.00 sec)

So yes, this is indeed a bug.

Hi All,

Thank you for your contributions.

We must agree that this is truly a bug, although of the very, very low priority. Since we have yet to decide on whether to foster further lax typing or not, this verified report will not get a high priority.

Thanks a lot.