Error with PIVOT operation

Help
1

I was trying to get the PIVOT operation to work with MemSQL 6.7, but so far without success.

Based on the documentation I created a dummy table to match the example:

create table productionHistory(dayOfWeek varchar(10), itemsProduced int);

insert into productionHistory values ('Monday', 12);
insert into productionHistory values ('Saturday', 15);
insert into productionHistory values ('Sunday', 21);

Then I ran the example query:

SELECT "Items Produced" AS "Total Production by Day", Saturday, Sunday
FROM (
        SELECT dayOfWeek, itemsProduced 
        FROM productionHistory
) AS pvt_subquery
PIVOT (
        SUM(itemsProduced) 
        FOR dayOfWeek 
        IN ("Saturday", "Sunday")
) AS pvt_table;

But every time I run a query with PIVOT I get the error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 200' at line 11

2

Hello. In MemSQL 6.7, I was able to run this code without error. Can you please try again?

Thanks,
Scott

3

That is odd. I will give it another try next week. I will also try different SQL clients and report back my findings.

4

It would appear that my SQL client (DBeaver) automatically appends a LIMIT clause to any executed query. While I am sure this behavior can be disabled, it still appears to be a bug that PIVOT cannot be combined with LIMIT. Can you confirm this?

5

Yes, LIMIT is not supported with PIVOT operation (We are adding this support in 7.0).

Try this (nested select) instead

SELECT * from (
SELECT "Items Produced" AS "Total Production by Day", Saturday, Sunday
FROM (
        SELECT dayOfWeek, itemsProduced 
        FROM productionHistory
) AS pvt_subquery
PIVOT (
        SUM(itemsProduced) 
        FOR dayOfWeek 
        IN ("Saturday", "Sunday")
) AS pvt_table
);

This query should work with LIMIT.