Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11, 11.4, 11.8, 12.3, 13.1, 12.3.3
-
Linux x86_64; MariaDB 12.3.3-MariaDB-ubu2404
Description
MariaDB 12.3.3-MariaDB-ubu2404 on Linux x86_64 returns modified projected values from grouped SQL_BIG_RESULT queries. The corresponding queries without SQL_BIG_RESULT return the original value.
Reproducer
Run the following script in a MariaDB client:
CREATE DATABASE string_grouping; |
USE string_grouping; |
SET SESSION optimizer_switch='condition_pushdown_from_having=off'; |
CREATE TABLE t1 (g INT, s TEXT) ENGINE=InnoDB; |
INSERT INTO t1 VALUES (1,'abc'),(1,'abc'),(2,'abc'),(2,'abc'); |
SELECT 'replace' AS test_case, g, CONCAT(s,'') AS v, COUNT(*) AS n FROM t1 GROUP BY g,s HAVING REPLACE(v,'a','z')='zbc' ORDER BY g; |
SELECT 'lpad' AS test_case, g, CONCAT(s,'') AS v, COUNT(*) AS n FROM t1 GROUP BY g,s HAVING LPAD(v,1,'x')='a' ORDER BY g; |
SELECT 'rpad' AS test_case, g, CONCAT(s,'') AS v, COUNT(*) AS n FROM t1 GROUP BY g,s HAVING RPAD(v,1,'x')='a' ORDER BY g; |
SELECT 'insert' AS test_case, g, s AS v, COUNT(*) AS n FROM t1 GROUP BY g,s HAVING INSERT(v,1,1,'z')='zbc' ORDER BY g; |
EXPLAIN SELECT SQL_BIG_RESULT 'replace_sorted' AS test_case, g, CONCAT(s,'') AS v, COUNT(*) AS n FROM t1 GROUP BY g,s HAVING REPLACE(v,'a','z')='zbc' ORDER BY g; |
SELECT SQL_BIG_RESULT 'replace_sorted' AS test_case, g, CONCAT(s,'') AS v, COUNT(*) AS n FROM t1 GROUP BY g,s HAVING REPLACE(v,'a','z')='zbc' ORDER BY g; |
EXPLAIN SELECT SQL_BIG_RESULT 'lpad_sorted' AS test_case, g, CONCAT(s,'') AS v, COUNT(*) AS n FROM t1 GROUP BY g,s HAVING LPAD(v,1,'x')='a' ORDER BY g; |
SELECT SQL_BIG_RESULT 'lpad_sorted' AS test_case, g, CONCAT(s,'') AS v, COUNT(*) AS n FROM t1 GROUP BY g,s HAVING LPAD(v,1,'x')='a' ORDER BY g; |
EXPLAIN SELECT SQL_BIG_RESULT 'rpad_sorted' AS test_case, g, CONCAT(s,'') AS v, COUNT(*) AS n FROM t1 GROUP BY g,s HAVING RPAD(v,1,'x')='a' ORDER BY g; |
SELECT SQL_BIG_RESULT 'rpad_sorted' AS test_case, g, CONCAT(s,'') AS v, COUNT(*) AS n FROM t1 GROUP BY g,s HAVING RPAD(v,1,'x')='a' ORDER BY g; |
EXPLAIN SELECT SQL_BIG_RESULT 'insert_sorted' AS test_case, g, s AS v, COUNT(*) AS n FROM t1 GROUP BY g,s HAVING INSERT(v,1,1,'z')='zbc' ORDER BY g; |
SELECT SQL_BIG_RESULT 'insert_sorted' AS test_case, g, s AS v, COUNT(*) AS n FROM t1 GROUP BY g,s HAVING INSERT(v,1,1,'z')='zbc' ORDER BY g; |
DROP DATABASE string_grouping; |
Observed behavior
The ordinary grouped queries project abc for both groups. The SQL_BIG_RESULT variants instead project the values produced while evaluating their HAVING expressions:
replace_sorted 1 zbc 2
|
replace_sorted 2 zbc 2
|
lpad_sorted 1 a 2
|
lpad_sorted 2 a 2
|
rpad_sorted 1 a 2
|
rpad_sorted 2 a 2
|
insert_sorted 1 zbc 2
|
insert_sorted 2 zbc 2
|
The plans for these variants report Using filesort. The statements complete without an error.
Expected behavior
Each group contains only the value abc, and both g and s are in the GROUP BY list. Evaluating a string function on alias v in HAVING must not modify the value later returned for v. Every variant should therefore return abc with a count of 2 for each group, as the corresponding queries without SQL_BIG_RESULT do.
This occurs when a SQL_BIG_RESULT grouped query projects a string alias and evaluates REPLACE, LPAD, RPAD, or INSERT on that alias in HAVING.