MySQL Bugs: #99182: BETWEEN computes incorrect result when comparing DECIMAL with a string

Bug #99182	BETWEEN computes incorrect result when comparing DECIMAL with a string
Submitted:	5 Apr 2020 15:08	Modified:	6 Apr 2020 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

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.

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>
                               ^                                 
--------------------------------------------------------------------------------------

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)

The MySQL result is the same as Oracle !8c.

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.

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.

Anyway I assigned a coworker to check it too.

Okay, thanks a lot and sorry for the additional work that I'm causing!

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.

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.

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.

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.