Incorrect columns result from dynamic pivot with limit, Snowlake

[MariuszRKMT]

Description

The pivot query returns incorrect result when LIMIT is applied (it applies also to default limit 200!). The number of pivoted columns is limited to the LIMIT value, while only the number result rows should be limited.

This issue doesn't occur when the query is executed in Snowflake web UI.

Example:

• The table my_table has 300 distinct PROPERTY_NAME values

SELECT * 
FROM (
	SELECT o.ID, o.PROPERTY_NAME, o.PROPERTY_VALUE
	FROM my_table t
)
PIVOT (MAX(PROPERTY_VALUE) FOR PROPERTY_NAME IN (ANY))
ORDER BY ID
LIMIT 10;

Query result in dBeaver:

• 10 rows with 11 columns (ID and 10 columns from PROPERTY_NAME)

Query result in Snowflake:

• 10 rows with 301 columns

DBeaver Version

Community Edition 24.0.5.202405191503

Operating System

Windows 10

Database and driver

Snowflake, driver 3.14.4

Steps to reproduce

Example:

• The table my_table has 300 distinct PROPERTY_NAME values

SELECT * 
FROM (
	SELECT o.ID, o.PROPERTY_NAME, o.PROPERTY_VALUE
	FROM my_table t
)
PIVOT (MAX(PROPERTY_VALUE) FOR PROPERTY_NAME IN (ANY))
ORDER BY ID
LIMIT 10;

Query result in dBeaver:

• 10 rows with 11 columns (ID and 10 columns from PROPERTY_NAME)

Query result in Snowflake:

• 10 rows with 301 columns

Additional context

No response

[MashaKorax]

Hello @MariuszRKMT . Im trying to reproduce your issue
I created a table

create or replace TABLE MYDATABASE.PUBLIC.PIVOT_TEST (
	ID NUMBER(38,0),
	NAME_COL VARCHAR(1000),
	VALUE_COL VARCHAR(1000)
);

Then iserted values like

insert into pivot_test values (6, 'column13', 11);
insert into pivot_test values (7, 'column14', 22);
insert into pivot_test values (8, 'column15', 31);
insert into pivot_test values (7, 'column16', 31);
insert into pivot_test values (6, 'column17', 11);
insert into pivot_test values (7, 'column18', 22);
insert into pivot_test values (8, 'column19', 31);
insert into pivot_test values (7, 'column20', 31);

I am using your query

SELECT * 
FROM (
	SELECT PIVOT_TEST.ID , PIVOT_TEST.NAME_COL , PIVOT_TEST.VALUE_COL 
	FROM PIVOT_TEST
)
PIVOT (MAX(PIVOT_TEST.VALUE_COL) FOR PIVOT_TEST.NAME_COL IN (ANY))
ORDER BY ID
LIMIT 5;

But Snowflake web U gives me the same results as DBeaver. What am I doing wrong?

[MariuszRKMT]

@MashaKorax you test is correct, but today it surprisingly returns a different (correct) result. Event with my archived script, which I'm 100% sure returned incorrect values.