| Bug #99130 | BETWEEN malfunctions when comparing a string containing a newline | ||
|---|---|---|---|
| Submitted: | 31 Mar 2020 12:18 | Modified: | 31 Mar 2020 12:41 |
| Reporter: | Manuel Rigger | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.0.19,5.6.47, 5.7.29 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[31 Mar 2020 12:41]
MySQL Verification Team
Hello Manuel Rigger, Thank you for the report. regards, Umesh
[1 Apr 2020 13:10]
MySQL Verification Team
I also noticed index having an impact on results, years ago: see: https://bugs.mysql.com/bug.php?id=45680 (wrong results when using index for lookup with implicitly casted values)
Description: Consider the following statements: How to repeat: CREATE TABLE t0(c0 INT, c1 INT UNIQUE); INSERT INTO t0(c1) VALUES(1); SELECT * FROM t0 WHERE t0.c1 BETWEEN 0 AND ("\n2"); -- expected: {NULL, 1}, actual: {} Unexpectedly, the SELECT does not fetch any rows. When removing the UNIQUE constraint from t0, the query works as expected and fetches the row NULL, 1. The query also works as expected when removing the column c0. I found this bug in MySQL version 8.0.19.