Bug #99182 BETWEEN computes incorrect result when comparing DECIMAL with a string
Submitted: 5 Apr 15:08 Modified: 6 Apr 13:56
Reporter: Manuel Rigger Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:8.0.19 OS:Any
Assigned to: CPU Architecture:Any

[5 Apr 15:08] Manuel Rigger
Description:
Consider the following statements:

How to repeat:
CREATE TABLE t0(c0 DECIMAL UNIQUE);
INSERT INTO t0(c0) VALUES(0);
SELECT * FROM t0 WHERE '' BETWEEN t0.c0 AND t0.c0; -- expected: {0}, actual: {}

Unexpectedly, no row is fetched. When removing the UNIQUE constraint, the row is fetched, as expected.

This bug seems to only be triggered for DECIMAL columns. Columns of other numerical types, such as DOUBLE and INT, do not trigger the bug.
[5 Apr 16:20] MySQL Verification Team
Thank you for the bug report.
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.21 Source distribution BUILT: 2020-MAR-28

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 8.0 > USE a;
Database changed
mysql 8.0 > CREATE TABLE t0(c0 DECIMAL UNIQUE);
Query OK, 0 rows affected (0.04 sec)

mysql 8.0 > INSERT INTO t0(c0) VALUES(0);
Query OK, 1 row affected (0.01 sec)

mysql 8.0 > SELECT * FROM t0 WHERE '' BETWEEN t0.c0 AND t0.c0;
Empty set, 1 warning (0.00 sec)

mysql 8.0 > SHOW WARNINGS;
+---------+------+------------------------------------------------------+
| Level   | Code | Message                                              |
+---------+------+------------------------------------------------------+
| Warning | 1366 | Incorrect decimal value: '' for column 'c0' at row 1 |
+---------+------+------------------------------------------------------+
1 row in set (0.00 sec)

mysql 8.0 >      
------------------------------------------------------------------------------------
SQL*Plus: Release 18.0.0.0.0 - Production on Sun Apr 5 13:15:29 2020
Version 18.4.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Enter user-name: system
Enter password:
Last Successful login time: Wed Apr 01 2020 09:09:37 -03:00

Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

SQL> drop table t0;

Table dropped.

SQL> CREATE TABLE t0(c0 DECIMAL UNIQUE);

Table created.

SQL> INSERT INTO t0(c0) VALUES(0);

1 row created.

SQL> SELECT * FROM t0 WHERE '' BETWEEN t0.c0 AND t0.c0;

no rows selected

SQL>
                               ^                                 
--------------------------------------------------------------------------------------
[5 Apr 16:29] Manuel Rigger
Thanks for looking into this! Why was the bug report closed, given that you could reproduce this?

To make it clearer why I think that this is a bug:

mysql> CREATE TABLE t0(c0 DECIMAL UNIQUE);

Query OK, 0 rows affected (1.03 sec)

mysql> INSERT INTO t0(c0) VALUES(0);
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM t0; -- {0}
+------+
| c0   |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t0 WHERE '' BETWEEN t0.c0 AND t0.c0; -- expected: {0}, actual: {}
Empty set, 1 warning (0.00 sec)

Unexpectedly, no row is fetched.

mysql> CREATE TABLE t0(c0 DECIMAL);
Query OK, 0 rows affected (0.24 sec)

mysql> INSERT INTO t0(c0) VALUES(0);
Query OK, 1 row affected (0.04 sec)

mysql> SELECT * FROM t0; -- {0}
+------+
| c0   |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t0 WHERE '' BETWEEN t0.c0 AND t0.c0; -- expected: {0}, actual: {}
+------+
| c0   |
+------+
|    0 |
+------+
1 row in set (0.00 sec)
[5 Apr 16:30] MySQL Verification Team
The MySQL result is the same as Oracle !8c.
[5 Apr 17:59] Manuel Rigger
Thanks for checking! I still believe that this might warrant further investigation, and might demonstrate a bug both in MySQL and Oracle !8c. Due to this consistency, no rows are fetched both for the predicate and its negated form:

SELECT * FROM t0 WHERE '' BETWEEN t0.c0 AND t0.c0; -- {}
SELECT * FROM t0 WHERE '' NOT BETWEEN t0.c0 AND t0.c0; -- {}

I believe that one of them should fetch a row.
[5 Apr 18:10] MySQL Verification Team
MySQL like you see gives the warning:
mysql 8.0 > SHOW WARNINGS;
+---------+------+------------------------------------------------------+
| Level   | Code | Message                                              |
+---------+------+------------------------------------------------------+
| Warning | 1366 | Incorrect decimal value: '' for column 'c0' at row 1 |
+---------+------+------------------------------------------------------+
1 row in set (0.00 sec)

PostgreSQL instead of warning gives an error for such WHERE '' value.
[5 Apr 18:12] MySQL Verification Team
Anyway I assigned a coworker to check it too.
[5 Apr 18:20] Manuel Rigger
Okay, thanks a lot and sorry for the additional work that I'm causing!
[6 Apr 12:59] MySQL Verification Team
Hi Mr. Rigger,

I am afraid that my colleague Miguel Solorzano was quite correct in his diagnosis.

An empty string can not represent a valid DECIMAL value. In this respect, we are following SQL standard, particularly chapters 6.2 and 9.1.

Also, our Reference Manual recommends using CONVERT() and CAST() functionality in order to obtain a DECIMAL value that is compliant with SQL.

Not a bug.
[6 Apr 13:56] Manuel Rigger
Thanks for the explanation! By the way, based on http://sqlfiddle.com, I found that MySQL 5.6 computes what I believe is the correct result.
[7 Jul 8:37] Lukas Eder
I've noticed this issue as well. Clearly, a UNIQUE constraint or an index should not have any effect on the outcome of a query. There seems to be a bug that may manifest in many other subtle ways. I recommend reopening and investigating this issue more in depth.

Regarding the comment about Oracle Database, Oracle does not have empty strings so '' is effectively NULL, so the query always returns an empty set in Oracle 18c XE for example. This is different from MySQL, which is fine, but the important point is that the behaviour is consistent in Oracle Database regardless of the presence of a UNIQUE constraint.
[7 Jul 12:39] MySQL Verification Team
Hi,

This is a marginal case, whose change would require major code and behavioural changes in our server.

Also, we have provided you with elegant workarounds.

Not a bug.