Error with PIVOT operation


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
        SELECT dayOfWeek, itemsProduced 
        FROM productionHistory
) AS pvt_subquery
        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


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



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


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?