2

In the pivot query below, why is every cell in a given row identical?

I was able to fix the query by replacing count(voo) with sum(voo), but I don't understand why count(voo) does not respect the AppVersion column. I tried to follow Microsoft's Complex PIVOT Example, which also used Count.

select pv.EventDate, [VersionAA],[VersionBB],[VersionCC],[VersionDD]
from 
(
    select ah.EventDate,ah.AppVersion,1 voo
    from AppHistory ah
    where EventDate > '2018-02-04' and reason = 1
) source
PIVOT(
    count(voo) --Fix: Replace "count(voo)" with "sum(voo)"
    For AppVersion in ([VersionAA],[VersionBB],[VersionCC],[VersionDD])
) as pv
order by EventDate

Example output with count(voo):

+------------+-----------+-----------+-----------+-----------+
| EventDate  | VersionAA | VersionBB | VersionCC | VersionDD |
+------------+-----------+-----------+-----------+-----------+
| 2018-02-05 |      1315 |      1315 |      1315 |      1315 |
| 2018-02-06 |      1200 |      1200 |      1200 |      1200 |
+------------+-----------+-----------+-----------+-----------+

Corrected output with sum(voo):

+------------+-----------+-----------+-----------+-----------+
| EventDate  | VersionAA | VersionBB | VersionCC | VersionDD |
+------------+-----------+-----------+-----------+-----------+
| 2018-02-05 |        12 |        18 |       300 |         3 |
| 2018-02-06 |        22 |        30 |       310 |         5 |
+------------+-----------+-----------+-----------+-----------+

My intent, which was met by sum(voo), was to to create a query which returned:
1) One row per day
2) One column per AppVersion (happily, the list of AppVersions is static)
3) Each cell contains a count of how many rows within AppHistory match the date and AppVersion of the column.

For reference AppHistory looks something like this:

CREATE TABLE [dbo].[AppHistory](
    [UserID] [bigint] NOT NULL,
    [AppVersion] [varchar](100) NOT NULL,
    [Reason] [int] NOT NULL,
    [EventDate] [datetime] NOT NULL,
    [TotalActions] [int] NOT NULL,
    [created] [datetime] NOT NULL
)

EDIT

I tried to reproduce the problem using a small amount of data and failed. In fact, the behavior goes away if I run select * into #AppHistory from AppHistory where EventDate > '2018-02-04' and reason = 1 and then replace AppHistory with #AppHistory in my queries. So now I'm even more confused. CheckDB says the database is fine.

I honestly didn't think of this until now, but AppHistory is a bit crazy: A) Has almost 1 billion rows B) Has a partition scheme C) Has a clustered columnstore index.

That said, creating a new table with the same partition scheme and columnstore index also failed to reproduce this behavior. So, now I'm even more mystified.

Query Plans:
(Bad Result)Plan using Count
(Good Result)Plan using Sum

  • You need to show a small amount of sample data that still reproduces the problem. – Aaron Bertrand Feb 13 '18 at 20:52
  • @AaronBertrand: An excellent idea. I edited my question with the results of trying to reproduce the problem. I was assuming I was using pivot incorrectly, but now I'm suspicious that they're something weird about my data. – Brian Feb 13 '18 at 22:09
  • Try adding OPTION (MAXDOP 1) to the original query. Don't quote me on this, but that's how I solved a similar problem a colleague was having yesterday. That won't be the ultimate solution but it got him through for now. – Aaron Bertrand Feb 13 '18 at 23:51
  • Could you post a querplan, xml version for the query that has the wrong result? You could use brentozar.com/pastetheplan and update your question with a link. – Mikael Eriksson Feb 14 '18 at 7:33
  • my first doubts is that you are confuse about your own data.so according to your data,correct way of counting is sum and not count.like if your voo contain 0 and 1 in that case you do not want to count 0 or null. – KumarHarsh Feb 14 '18 at 11:17
3

Looks to me like a bug when producing the query plan when you have batch mode execution.

Here is a repro that shows the issue both in SQL Server 2016 (13.0.4001.0) and SQL Server 2017 (14.0.3015.40).

create table dbo.CS(CS bit not null, index IX_CS clustered columnstore);
create table dbo.X(C int not null);

go

insert into dbo.X(C)
select top(10000) row_number() over(order by (select null)) / 10
from sys.columns as c1, sys.columns as c2;

go

select P.[1], P.[2]
from (
     select X.C, 1 as X
     from dbo.X
     ) as T
pivot (
      count(T.X) for T.C in ([1],[2])
      ) as P
left outer join dbo.CS on 1 = 0;

select P.[1], P.[2]
from (
     select X.C, 1 as X
     from dbo.X
     ) as T
pivot (
      count(T.X) for T.C in ([1],[2])
      ) as P
left outer join dbo.CS on 1 = 0
option (querytraceon 9453);

Result:

1           2
----------- -----------
10000       10000


1           2
----------- -----------
10          10

Left join on dbo.CS here makes the query use batch mode and the trace flag 9453 disables batch mode.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.