CODEWITHSUNDEEP
sqlteradata
Andrei Vasilev
Andrei Vasilev

Reputation: 607

Do volatile tables truncate results by default?

I have two queries that supposed to bring equivalent results. However the second query gives only partial results (less than 10 % of the total).

First query gives more than 4 million rows

SELECT id, amount 
FROM table1 t1 LEFT OUTER JOIN table2 t2  ON t1.id = t2.id;

Second give only 18 thousand records

CREATE  VOLATILE TABLE vt AS
(
SELECT id, amount 
FROM table1 t1 LEFT OUTER JOIN table2 t2  ON t1.id = t2.id;
)
WITH DATA
NO PRIMARY INDEX
ON COMMIT PRESERVE ROWS;

SELECT * 
  FROM vt ;

Why does the second query give less records ???

Upvotes: 0

Views: 805

Answers (1)

dnoeth
dnoeth

Reputation: 60482

When you do a SHOW TABLE vt; you'll notice that it's created as a SET table, which doesn't store duplicate rows. There are only 18 thousand distinct (id,amount) combinations.

Either add DISTINCT to your first Select or use CREATE MULTISET VOLATILE TABLE.

Upvotes: 2

Related Questions