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:
MariaDB [test]> SELECT (c1 RLIKE c1) FROM t0;
+---------------+
| (c1 RLIKE c1) |
+---------------+
| 1 |
| 1 |
+---------------+
2 rowsinset (0.001 sec)
And naturally c0 IS NULL returns FALSE for both rows:
MariaDB [test]> SELECT (c0 ISNULL) FROM t0;
+--------------+
| (c0 ISNULL) |
+--------------+
| 0 |
| 0 |
+--------------+
2 rowsinset (0.000 sec)
But when they are run together, strange things happen:
MariaDB [test]> SELECT (c1 RLIKE c1), (c0 ISNULL) FROM t0;
+---------------+--------------+
| (c1 RLIKE c1) | (c0 ISNULL) |
+---------------+--------------+
| 1 | 0 |
| 0 | 0 |
+---------------+--------------+
2 rowsinset (0.000 sec)
Reproducible on 5.5-10.5. MySQL behaves differently comparing to MariaDB and between its own versions.
Elena Stepanova
added a comment - - edited 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:
MariaDB [test]> SELECT (c1 RLIKE c1) FROM t0;
+ ---------------+
| (c1 RLIKE c1) |
+ ---------------+
| 1 |
| 1 |
+ ---------------+
2 rows in set (0.001 sec)
And naturally c0 IS NULL returns FALSE for both rows:
MariaDB [test]> SELECT (c0 IS NULL ) FROM t0;
+ --------------+
| (c0 IS NULL ) |
+ --------------+
| 0 |
| 0 |
+ --------------+
2 rows in set (0.000 sec)
But when they are run together, strange things happen:
MariaDB [test]> SELECT (c1 RLIKE c1), (c0 IS NULL ) FROM t0;
+ ---------------+--------------+
| (c1 RLIKE c1) | (c0 IS NULL ) |
+ ---------------+--------------+
| 1 | 0 |
| 0 | 0 |
+ ---------------+--------------+
2 rows in set (0.000 sec)
Reproducible on 5.5-10.5. MySQL behaves differently comparing to MariaDB and between its own versions.
It erroneously performs return false instead of recompiling.
Alexander Barkov
added a comment - - edited 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.
Bug
Major
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.