GROUPING SETS returning unexpected results with calculated expression - Databases

TopAnswers Databases

Meta

Databases

TeX

Code Golf

APL

C++

.net

db<>fiddle

Java

*nix

PHP

PowerShell

Python

Rust

टेक्-मराठी

Typst

Web Client Dev

Web Server Dev

GROUPING SETS returning unexpected results with calculated expression

sql-server add tag

5 years ago sepupic (imported from SE)

Here we have two similar queries using grouping sets where the SELECT clause contains some expressions calculated in aggregation:

SELECT RN10, RN10 / 10, COUNT(*) FROM 
(
       SELECT RN, RN/10 AS RN10, RN/100 AS RN100 FROM 
       (
               SELECT RN = -1 + ROW_NUMBER() OVER (ORDER BY 1/0) 
			   FROM master..spt_values
       ) A
) B
GROUP BY GROUPING SETS ((RN10), (RN10 / 10), ())
ORDER BY 1, 2

xxxxxxxxxx
 
SELECT RN10, RN10 / 10, COUNT(*) FROM (       SELECT RN, RN/10 AS RN10, RN/100 AS RN100 FROM        (               SELECT RN = -1 + ROW_NUMBER() OVER (ORDER BY 1/0)                FROM master..spt_values       ) A) BGROUP BY GROUPING SETS ((RN10), (RN10 / 10), ())ORDER BY 1, 2

it’s plan is here: first query plan

and

SELECT RN10, SUBSTRING(RN,3,99), COUNT(*) FROM 
(
       SELECT RN, SUBSTRING(RN,2,99) AS RN10 FROM 
       (
               SELECT RN = CAST(-1 + ROW_NUMBER() OVER (ORDER BY 1/0) AS VARCHAR(99)) 
			   FROM master..spt_values
       ) A
) B
GROUP BY GROUPING SETS ((RN10), (SUBSTRING(RN,3,99)), ())
ORDER BY 1, 2

xxxxxxxxxx
 
SELECT RN10, SUBSTRING(RN,3,99), COUNT(*) FROM (       SELECT RN, SUBSTRING(RN,2,99) AS RN10 FROM        (               SELECT RN = CAST(-1 + ROW_NUMBER() OVER (ORDER BY 1/0) AS VARCHAR(99))                FROM master..spt_values       ) A) BGROUP BY GROUPING SETS ((RN10), (SUBSTRING(RN,3,99)), ())ORDER BY 1, 2

the corresponding plan is here: second query plan

Both the queries first calculate some expression for aggregation, RN10 / 10 in the first case and SUBSTRING(RN,3,99) in the second, then the same expression is used in the SELECT clause but as the first plan shows it’s re-calculated in the first query and it’s not in the second.

As the result we have NULLs in the first result set that is quite unexpectedly:

Can someone explain why the first query makes the calculation 2 times (one in aggregation and one more time in the final select) while the second makes it one time only?

Top Answer

5 years ago Martin Smith (imported from SE)

I’m going to use a simpler example where it is clear to see what the expected results are.

CREATE TABLE Queen
(
   FirstName        VARCHAR(7),
   Surname          VARCHAR(7)
); 
    
INSERT INTO Queen
    (FirstName, Surname)
VALUES
    ('Brian',   'May'),
    ('Freddie', 'Mercury'),
    ('John',    'Deacon'),
    ('Roger',   'Taylor')
;

xxxxxxxxxx
 
CREATE TABLE Queen(   FirstName        VARCHAR(7),   Surname          VARCHAR(7));     INSERT INTO Queen    (FirstName, Surname)VALUES    ('Brian',   'May'),    ('Freddie', 'Mercury'),    ('John',    'Deacon'),    ('Roger',   'Taylor');
show all 14 lines

Query 1

SELECT Surname,
       NULL AS SurnameInitial,
       COUNT(*) AS Count
FROM   Queen
GROUP  BY Surname
UNION ALL
SELECT NULL AS Surname,
       LEFT(Surname,1) AS SurnameInitial,
       COUNT(*) AS Count
FROM   Queen
GROUP  BY LEFT(Surname,1)

xxxxxxxxxx
 
SELECT Surname,       NULL AS SurnameInitial,       COUNT(*) AS CountFROM   QueenGROUP  BY SurnameUNION ALLSELECT NULL AS Surname,       LEFT(Surname,1) AS SurnameInitial,       COUNT(*) AS CountFROM   QueenGROUP  BY LEFT(Surname,1)

Query 1 Results

+---------+----------------+-------+
| Surname | SurnameInitial | Count |
+---------+----------------+-------+
| Deacon  | NULL           |     1 |
| May     | NULL           |     1 |
| Mercury | NULL           |     1 |
| Taylor  | NULL           |     1 |
| NULL    | D              |     1 |
| NULL    | M              |     2 |
| NULL    | T              |     1 |
+---------+----------------+-------+

xxxxxxxxxx
 
+---------+----------------+-------+| Surname | SurnameInitial | Count |+---------+----------------+-------+| Deacon  | NULL           |     1 || May     | NULL           |     1 || Mercury | NULL           |     1 || Taylor  | NULL           |     1 || NULL    | D              |     1 || NULL    | M              |     2 || NULL    | T              |     1 |+---------+----------------+-------+

Query 2

SELECT Surname,
       LEFT(Surname,1) AS SurnameInitial,
       COUNT(*) AS Count
FROM   Queen
GROUP  BY GROUPING SETS ( ( Surname ), (LEFT(Surname,1)) ) 
ORDER BY SurnameInitial, Surname

xxxxxxxxxx
 
SELECT Surname,       LEFT(Surname,1) AS SurnameInitial,       COUNT(*) AS CountFROM   QueenGROUP  BY GROUPING SETS ( ( Surname ), (LEFT(Surname,1)) ) ORDER BY SurnameInitial, Surname

Query 2 Results

Despite the ORDER BY SurnameInitial and the fact that NULL sorts first in SQL Server the rows with SurnameInitial as NULL are ordered last.

+---------+----------------+-------+
| Surname | SurnameInitial | Count |
+---------+----------------+-------+
| Deacon  | D              |     1 |
| May     | M              |     1 |
| Mercury | M              |     1 |
| Taylor  | T              |     1 |
| NULL    | NULL           |     1 |
| NULL    | NULL           |     2 |
| NULL    | NULL           |     1 |
+---------+----------------+-------+

xxxxxxxxxx
 
+---------+----------------+-------+| Surname | SurnameInitial | Count |+---------+----------------+-------+| Deacon  | D              |     1 || May     | M              |     1 || Mercury | M              |     1 || Taylor  | T              |     1 || NULL    | NULL           |     1 || NULL    | NULL           |     2 || NULL    | NULL           |     1 |+---------+----------------+-------+

Query 1 and 2 should return the same results. The problem is that SQL Server decides to treat it like the following SQL

WITH GrpSets AS
(
SELECT Surname,
       COUNT(*) AS Count
FROM   Queen
GROUP  BY Surname
UNION ALL
SELECT NULL AS Surname,
       COUNT(*) AS Count
FROM   Queen
GROUP  BY LEFT(Surname,1)
)
SELECT Surname,
       LEFT(Surname,1) AS SurnameInitial,
       Count
FROM GrpSets

xxxxxxxxxx
 
WITH GrpSets AS(SELECT Surname,       COUNT(*) AS CountFROM   QueenGROUP  BY SurnameUNION ALLSELECT NULL AS Surname,       COUNT(*) AS CountFROM   QueenGROUP  BY LEFT(Surname,1))SELECT Surname,       LEFT(Surname,1) AS SurnameInitial,       CountFROM GrpSets
show all 16 lines

This just looks like a bug to me (trace flag 8605 shows that the damage is already done in the initial query tree representation). BUG REPORT.

Query 3

SELECT Surname,
       LEFT(FirstName,1) AS FirstNameInitial,
       COUNT(*) AS Count
FROM   Queen
GROUP  BY GROUPING SETS ( ( Surname ), (LEFT(FirstName,1)) )

xxxxxxxxxx
 
SELECT Surname,       LEFT(FirstName,1) AS FirstNameInitial,       COUNT(*) AS CountFROM   QueenGROUP  BY GROUPING SETS ( ( Surname ), (LEFT(FirstName,1)) ) 

Query 3 Results

+---------+------------------+-------+
| Surname | FirstNameInitial | Count |
+---------+------------------+-------+
| NULL    | B                |     1 |
| NULL    | F                |     1 |
| NULL    | J                |     1 |
| NULL    | R                |     1 |
| Deacon  | NULL             |     1 |
| May     | NULL             |     1 |
| Mercury | NULL             |     1 |
| Taylor  | NULL             |     1 |
+---------+------------------+-------+

xxxxxxxxxx
 
+---------+------------------+-------+| Surname | FirstNameInitial | Count |+---------+------------------+-------+| NULL    | B                |     1 || NULL    | F                |     1 || NULL    | J                |     1 || NULL    | R                |     1 || Deacon  | NULL             |     1 || May     | NULL             |     1 || Mercury | NULL             |     1 || Taylor  | NULL             |     1 |+---------+------------------+-------+

Query3 does not meet the problematic pattern of grouping on a column and an expression referencing that column. It wouldn’t even be possible for the same issue to occur here anyway because the grouping sets part is equivalent to

SELECT Surname,
       NULL AS FirstNameInitial,
       COUNT(*) AS Count
FROM   Queen
GROUP  BY Surname
UNION ALL
SELECT NULL AS Surname,
       LEFT(FirstName,1) AS FirstNameInitial,
       COUNT(*) AS Count
FROM   Queen
GROUP  BY LEFT(FirstName,1)

xxxxxxxxxx
 
SELECT Surname,       NULL AS FirstNameInitial,       COUNT(*) AS CountFROM   QueenGROUP  BY SurnameUNION ALLSELECT NULL AS Surname,       LEFT(FirstName,1) AS FirstNameInitial,       COUNT(*) AS CountFROM   QueenGROUP  BY LEFT(FirstName,1)

This does not pass out the entire FirstName column upstream (or even have a guaranteed unique FirstName column that could be passed out) so it isn’t possible for the LEFT(FirstName,1) expression to be calculated on top of that.

For the same reason you don’t see the issue with (RN10), (SUBSTRING(RN,3,99)).

@i-one reasons in the comments that it is likely

a bug in normalization (algebrization). It has logic that finds a match for non-aggregated columns and expressions in the SELECT list within members of GROUP BY. The same logic seemingly allows us to write for example
SELECT Surname, LEFT(Surname, 1), COUNT(*) FROM Queen GROUP BY Surname
xxxxxxxxxx
SELECT Surname, LEFT(Surname, 1), COUNT(*)
FROM   Queen
GROUP BY Surname

without having to explicitly add the calculated expression as below

GROUP BY Surname, LEFT(Surname, 1)

xxxxxxxxxx
 
GROUP BY Surname, LEFT(Surname, 1)

Or another example would be

SELECT Surname,
       LEFT(Surname,1) AS SurnameInitial,
       LEFT(Surname,2) AS SurnamePrefix,
       COUNT(*) AS Count
FROM   Queen
GROUP  BY GROUPING SETS ( ( Surname ), (LEFT(Surname,1)) )

xxxxxxxxxx
 
SELECT Surname,       LEFT(Surname,1) AS SurnameInitial,       LEFT(Surname,2) AS SurnamePrefix,       COUNT(*) AS CountFROM   QueenGROUP  BY GROUPING SETS ( ( Surname ), (LEFT(Surname,1)) ) 

In this case the LEFT(Surname,2) is allowed and the only way of computing it would be to do it in the manner that is problematic for the LEFT(Surname,1) case.

Col0	Col1	Col2
NULL	Deacon	1
NULL	May	1
NULL	Mercury	1
NULL	Taylor	1
D	NULL	1
M	NULL	2
T	NULL	1

1 Answer