Details
Minor Description
I tested this issue with MsSQL server, on identical data and table. In MsSQL it works correctly.
Suppose you have a table with field1, field2, etc.
if you do a query like
select field1, sum(field2) |
group by field1 |
with rollup |
in MariaDB the result is correct and looks like this, where x and y are the contents of columns field1 and field2
x, sum(y)
|
x1,sum(y)
|
null, sum(y)
|
That is: the null on the lower left indicates that this is the grouping field.
However, suppose that this column is a string, and then we are grouping on a part of the column. Here is a concrete example.
CREATE TABLE demo(field1 varchar(100) PRIMARY KEY,field2 int(11)); |
INSERT demo(field1,field2) VALUES('192.168.1.20-Servere1',100),('192.168.1.3-Server3',200),('192.168.1.20-Server1',100),('192.168.1.3-Server2',400); |
select * from demo; |
SELECT SUBSTRING(field1,1,instr(field1, '-')-1) AS Name, SUM(field2) |
FROM demo |
GROUP BY SUBSTRING(field1,1,instr(field1, '-')-1) |
WITH rollup |
- RESULT IS INCORRECT, THE LOWER LEFT NULL IS NOT PRESENT
By contrast, in MsSQL, this identical query is correct:
SELECT SUBSTRING(field1,1, charINDEX('-',field1, 0)-1) AS Mycol, SUM(field2) |
FROM demo |
GROUP BY SUBSTRING(field1,1, charINDEX('-',field1, 0)-1) |
WITH ROLLUP |
Attachments
Issue Links
- relates to
-
MDEV-19039 Wrong result from query, using window function and GROUP BY .. WITH ROLLUP
-
- Stalled
-
-
-
- Confirmed
-