Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.4.11, 5.5(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
-
None
-
Ubuntu 19.04
Description
Consider the following statements:
CREATE TABLE t0(c0 INT NOT NULL, c1 CHAR UNIQUE);
|
INSERT INTO t0 VALUES (0, 1);
|
INSERT INTO t0 VALUES (0, '');
|
SELECT SUM(a.t) FROM (SELECT (c1 RLIKE c1) = (c0 IS NULL) as t FROM t0) as a; -- expected: 1, actual: 0
|
I would expect SUM() to yield 1, because the expression evaluates to TRUE for one row:
SELECT (c1 RLIKE c1) = (c0 IS NULL) FROM t0; -- 1, 0
|
Attachments
Issue Links
- is duplicated by
-
-
- Closed
-
- relates to
-
-
- Closed
-
Activity
The problem is also demonstrated in this script:
CREATE OR REPLACE TABLE t1(c0 INT NOT NULL, c1 CHAR UNIQUE); |
INSERT INTO t1 VALUES (0, 1); |
INSERT INTO t1 VALUES (0, ''); |
This query returns a correct result:
SELECT c1, (c1 RLIKE c1) FROM t1; |
+------+---------------+
|
| c1 | (c1 RLIKE c1) |
|
+------+---------------+
|
| | 1 |
|
| 1 | 1 |
|
+------+---------------+
|
Now if a add an extra column, the expression (c1 RLIKE c1) starts to return a wrong result for one row:
SELECT c1, (c1 RLIKE c1), (c0 is null) FROM t1; |
+------+---------------+--------------+
|
| c1 | (c1 RLIKE c1) | (c0 is null) |
|
+------+---------------+--------------+
|
| 1 | 1 | 0 |
|
| | 0 | 0 |
|
+------+---------------+--------------+
|
If I now add the ORDER BY clause:
SELECT c1, (c1 RLIKE c1), (c0 is null) FROM t1 ORDER BY c1; |
it returns correct results again:
+------+---------------+--------------+
|
| c1 | (c1 RLIKE c1) | (c0 is null) |
|
+------+---------------+--------------+
|
| | 1 | 0 |
|
| 1 | 1 | 0 |
|
+------+---------------+--------------+
|
The problem seem to reside in this code:
if (is_compiled()) |
{
|
if (!stringcmp(pattern, &m_prev_pattern)) |
return false; |
cleanup();
|
m_prev_pattern.copy(*pattern);
|
}
|
It erroneously performs return false instead of recompiling.
This problem was earlier fixed by the patch for MDEV-33344.
Added MTR tests into 10.5.
It seems more likely the expression evaluates to TRUE incorrectly.
If we run (c1 RLIKE c1) separately, it returns TRUE to both, which seems quite natural:
| (c1 RLIKE c1) || 1 || 1 |And naturally c0 IS NULL returns FALSE for both rows:
| 0 || 0 |But when they are run together, strange things happen:
| 1 | 0 || 0 | 0 |Reproducible on 5.5-10.5. MySQL behaves differently comparing to MariaDB and between its own versions.