Does a volatile table remove duplicated rows?

Database
Enthusiast

Does a volatile table remove duplicated rows?

If I create a volatile table with the following SQL codes:

CREATE VOLATILE TABLE partner_overall_sku_ranks AS (
SELECT
sku_and_partner_data.item_sku_num
,partner_num
,best_metric
,RANK() OVER(PARTITION BY partner_num
ORDER BY overall_improved_sales DESC,
overall_improved_clicks DESC,
overall_improved_impressions DESC,
sku_and_partner_data.item_sku_num) partner_sku_overall_rank
FROM sku_and_partner_data
JOIN sku_overall_scores ON sku_and_partner_data.item_sku_num = sku_overall_scores.item_sku_num
) WITH DATA
PRIMARY INDEX(item_sku_num,partner_num)
ON COMMIT PRESERVE ROWS;

And then run the following query:

SELECT *
FROM partner_overall_sku_ranks
WHERE item_sku_num = 11990009

A single row is returned. However, if I run the following SQL query:

SELECT * FROM
(
SELECT
sku_and_partner_data.item_sku_num
,partner_num
,best_metric
,RANK() OVER(PARTITION BY partner_num
ORDER BY overall_improved_sales DESC,
overall_improved_clicks DESC,
overall_improved_impressions DESC,
sku_and_partner_data.item_sku_num) partner_sku_overall_rank
FROM sku_and_partner_data
JOIN sku_overall_scores ON sku_and_partner_data.item_sku_num = sku_overall_scores.item_sku_num) AS spam
WHERE item_sku_num = 11990009

I get 14 identical rows returned. What's going on here? Why don't the two queries return the same result? Does a volatile table remove duplicated rows? Inquiring minds want to know!

Thanks.

-Dylan

2 REPLIES
Enthusiast

Re: Does a volatile table remove duplicated rows?

if SET/MULTISET was not specified then by default

Multiset will be considered if it is ANSI mode

Set will be considered if it is Teradata mode.

CREATE VOLATILE TABLE "anil".tblname(a INT)
PRIMARY INDEX(a)
ON COMMIT PRESERVE ROWS

SHOW TABLE "anil".tblname returned below result

CREATE SET VOLATILE TABLE "anil".tblname,NO FALLBACK ,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO,
LOG
(
a INTEGER)
PRIMARY INDEX ( a )
ON COMMIT PRESERVE ROWS;
Enthusiast

Re: Does a volatile table remove duplicated rows?

Thank you Anilnandala!