Two samplings in one Macro

[whitestorm]

Dear All,
When I created a macro (see below, ttemp.test_mac, DB Version: "RELEASE V2R.05.00.02.03,VERSION
05.00.02.06") and ran this macro, a strange thing happened! The inserted rows of "exec ttemp.test_mac" ARE "2", NOT "3"!! AND, There is only one "SAMPLING" in the EXPLANATION.
Does anybody know why?

Best Regard.

========== DDL ==========
CREATE SET TABLE ttemp.t2 ,NO FALLBACK ,NO BEFORE JOURNAL,NO AFTER JOURNAL
(c1 INTEGER,c2 INTEGER) PRIMARY INDEX (c1 );
/*There are 5 records in this table*/

CREATE MULTISET TABLE ttemp.t1 ,NO FALLBACK ,NO BEFORE JOURNAL, NO AFTER JOURNAL
(c1 INTEGER)PRIMARY INDEX ( c1 );

create macro ttemp.test_mac as
(
ins into ttemp.t1 sel 1 from ttemp.t2 sample 1;
/*sample 1 from 5 records*/
ins into ttemp.t1 sel 2 from ttemp.t2 sample 2;
/*sample 2 from 5 recodes*/
);

========== Explanation ==========
1) First, we lock a distinct ttemp."pseudo table" for write on a
RowHash to prevent global deadlock for ttemp.t1.
2) Next, we lock a distinct ttemp."pseudo table" for read on a
RowHash to prevent global deadlock for ttemp.t2.
3) We lock ttemp.t1 for write, and we lock ttemp.t2 for read.
4) We do an all-AMPs RETRIEVE step from ttemp.t2 by way of an
all-rows scan with no residual conditions into Spool 3 (Last Use),
which is redistributed by hash code to all AMPs. The size of
Spool 3 (Last Use) is estimated with low confidence to be 76 rows.
The estimated time for this step is 0.56 seconds.
5) We do an all-AMPs SAMPLING step from ttemp.t2 by way of an
all-rows scan with no residual conditions into Spool 1 (all_amps),
which is redistributed by hash code to all AMPs. Samples are
specified as a number of rows.
6) We do a SORT to order Spool 1 by row hash.
7) We do an all-AMPs MERGE into ttemp.t1 from Spool 1 (Last Use).
8) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> No rows are returned to the user as the result of statement 1.
No rows are returned to the user as the result of statement 2.

 

[tdatgod]

Hi,
this was already explained in another Teradata fourm.

http://www.teradata.com/teradataForum/shwmessage.aspx?ForumID=1&MessageID=412

The basic premise was that the table was a SET table and therefore the answer set removed duplicates making 3 rows appear as 2.

----

 

[whitestorm]

I knew that and I think it is not correct!
Because, the table which I inserted is "MULTISET".
The STRANGEST thing is that the inserted rows of 'exec ttemp.test_mac' in "RELEASE V2R.05.00.02.03,VERSION 05.00.02.06" are 2 rows, but in "RELEASE V2R.05.00.00.11 VERSION 05.00.00.11" are 3 rows.

 

[whitestorm]

You mistook the SQLs which I posted.
I tried to insert into ttemp.t1 which is 'MULTISET' and no matter SET or MULTISET ttemp.t2 is, the answer will always be:
2
2
which means that the fisrt sampling in the macro DOES NOT work.

The STRANGEST thing is that the inserted rows of 'exec ttemp.test_mac' in "RELEASE V2R.05.00.02.03,VERSION 05.00.02.06" are 2 rows, but in "RELEASE V2R.05.00.00.11 VERSION 05.00.00.11" are 3 rows.

Best Regards.

 

[dnoeth]

Open an incident, it's definitly a bug and may be reproduced...

Even if the samples are from different tables -> only the second tables is sampled, the first one is spooled, but not merged (last use)!

Dieter