Details
-
Bug
-
Status: In Progress (View Workflow)
-
Critical
-
Resolution: Unresolved
-
10.11, 11.4, 11.8, 12.2, 10.11.14, 12.1.2
-
12.1.2-MariaDB-ubu2404
Description
The server returns an incorrect Empty Set when optimizer_switch='derived_merge=on'. The issue occurs when a Derived Table is merged into the outer query, and the HAVING clause contains an AVG() function on a TEXT/VARCHAR column combined with RLIKE.
When derived_merge=off, the query returns the correct result.
AVG(string) should implicitly convert to 0 (with warnings).
0 RLIKE (expression) should evaluate to TRUE while the character_length is even.
CREATE TABLE t1(c1 TEXT); |
INSERT INTO t1(c1) VALUES ('a'),('ab'),('cc'); |
|
|
SELECT MAX(ca1) FROM (SELECT c1 AS ca1 FROM t1) AS ta1 GROUP BY ca1 HAVING AVG(ca1) RLIKE (TRUE & CHARACTER_LENGTH(ca1)); |
+----------+ |
| MAX(ca1) | |
+----------+ |
| a |
|
| ab |
|
| cc |
|
+----------+ |
3 rows in set, 3 warnings (0.002 sec) |
|
|
SET optimizer_switch='derived_merge=off'; |
SELECT MAX(ca1) FROM (SELECT c1 AS ca1 FROM t1) AS ta1 GROUP BY ca1 HAVING AVG(ca1) RLIKE (TRUE & CHARACTER_LENGTH(ca1)); |
+----------+ |
| MAX(ca1) | |
+----------+ |
| ab |
|
| cc |
|
+----------+ |
2 rows in set, 3 warnings (0.003 sec) |
Attachments
Issue Links
- includes
-
-
- Confirmed
-
- is caused by
-
-
- Closed
-
- relates to
-
-
- Confirmed
-