Group by on a Group by not working as expected!!!

Database
Junior Contributor

Re: Group by on a Group by not working as expected!!!

Hi Peter, 

of course it's a bug, all queries return the same (correct) result on a 15.00.00.03 :-) 

Enthusiast

Re: Group by on a Group by not working as expected!!!

Hi Dieter

We are going to 14.xx in near future. I tried logging into the new machine and here all queries returned the same correct result. It must be a bug in 13.10 then. I do not think my DBA will use the time to submit any incidents, because the 13.10 will go out of production in near future.

I wonder how many out there are still on 13.10 and do not realize that there is an issue here!

Peter

Enthusiast

Re: Group by on a Group by not working as expected!!!

SyntaxEditor Code Snippet

Has anyone else run into this scenario?   Accidential use of a column alias ina subquery running and resulting in ZERO rows?
Specifically, this only retunrs zero rows when used on a text column alias used in an aggregate subquery.
All other exmples actually return the correct results.

Below are the variosu examples. We logged an incident for this.
SyntaxEditor Code Snippet
1    RELEASE    15.00.06.03
2    VERSION    15.00.06.04


create
table abc (c1 int, c2 int); insert into abc values(1,1); insert into abc values(1,2); insert into abc values(1,3); /* Query 1*/ sel c1, c2 as c_ALIAS from abc WHERE c1 = 1 and c2 = 2; --Expected Output c1 c_ALIAS 1 2 /*Query 2 */ sel c1, c_ALIAS from abc; --Expected Output SELECT Failed. 5628: Column c_ALIAS not found in dbnam.abc. /*Query 3 */ sel c1, c2 as c_ALIAS from abc WHERE c1 = 1 and c2 = 2 AND (c1, c_ALIAS) /* Multi Column Subquery Filter */ in ( sel c1, c_ALIAS from abc); /* Outer Query Alias used in Subquery */ -- Expected SELECT Failed. 5628: Column c_ALIAS not found in dbnam.abc. -- returns correct results c1 c_ALIAS 1 2 /*Query 4 */ sel c1, c2 as c_ALIAS from abc WHERE c1 = 1 and c2 = 2 AND (c1, c_ALIAS) /* Multi Column Subquery Filter */ in ( sel max(c1), c_ALIAS from abc group by 2); /* Outer Query Alias used in Aggregate Subquery */ -- Expected SELECT Failed. 5628: Column c_ALIAS not found in dbnam.abc. --returns correct results c1 c_ALIAS 1 2 alter table abc add c3 char(5); insert into abc values(1,1,'one'); insert into abc values(1,2,'two'); insert into abc values(1,3,'three'); show table abc; CREATE SET TABLE abc ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT, DEFAULT MERGEBLOCKRATIO ( c1 INTEGER, c2 INTEGER, c3 CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC) PRIMARY INDEX ( c1 ); sel * from abc; c1 c2 c3 1 1 ? 1 2 ? 1 3 ? 1 1 one 1 2 two 1 3 three /*Query 5 */ -- Returns ZERO Rows when TEXT COLUMN Alias Used in Subquery sel c1, c2 , c3 as c3_ALIAS from abc WHERE c1 = 1 and c2 = 2 AND (c1, c2, c3_ALIAS) /* Multi Column Subquery Filter */ in ( sel c1, max(c2), c3_ALIAS from abc group by 1,3); /* Outer Query Alias used in Aggregate Subquery */ -- Expected SELECT Failed. 5628: Column c3_ALIAS not found in dbnam.abc. --returns ZERO rows /*Query 6 */ -- Returns ZERO Rows when TEXT COLUMN Alias Used in Subquery sel c1, c2 , c3 as c3_ALIAS from abc WHERE c1 = 1 and c2 = 2 AND (c1, c2, c3_ALIAS) /* Multi Column Subquery Filter */ in ( sel c1, max(c2), c3 from abc group by 1,3); /* Fix by referencing subquery reference to actual columnname */ --Expected results returned c1 c2 c3_ALIAS 1 2 two /*Query 7*/ -- Returns Rows when TEXT COLUMN Alias Used in NON-Aggregate Subquery sel c1, c2 , c3 as c3_ALIAS from abc WHERE c1 = 1 and c2 = 2 AND (c1, c2, c3_ALIAS) /* Multi Column Subquery Filter */ in ( sel c1, c2, c3_ALIAS from abc ); /* Outer Query Alias used in NON_AGGREGATE Subquery */ -- Expected SELECT Failed. 5628: Column c3_ALIAS not found in dbnam.abc. --returns rows c1 c2 c3_ALIAS 1 2 two

 

Enthusiast

Re: Group by on a Group by not working as expected!!!

SyntaxEditor Code Snippet

create  table abc (c1 int, c2 int);
insert into abc values(1,1);
insert into abc values(1,2);
insert into abc values(1,3);

/* Query 1*/
sel c1, c2 as c_ALIAS from abc
WHERE c1 = 1 and c2 = 2;
--Expected Outputc1    c_ALIAS
1        2

/*Query 2 */
sel c1, c_ALIAS from abc;
--Expected OutputSELECT Failed. 5628:  Column c_ALIAS not found in dbnam.abc.

/*Query 3 */
sel c1, c2 as c_ALIAS from abc
WHERE c1 = 1 and c2 = 2
AND  (c1, c_ALIAS)   /* Multi Column Subquery Filter */
in ( sel c1, c_ALIAS from abc);   /* Outer Query Alias used in Subquery */

-- Expected SELECT Failed. 5628:  Column c_ALIAS not found in dbnam.abc.
-- returns correct resultsc1    c_ALIAS
1    2

/*Query 4 */
sel c1, c2 as c_ALIAS from abc
WHERE c1 = 1 and c2 = 2
AND  (c1, c_ALIAS)   /* Multi Column Subquery Filter */
in ( sel max(c1), c_ALIAS from abc group by 2);   /* Outer Query Alias used in Aggregate Subquery */

-- Expected SELECT Failed. 5628:  Column c_ALIAS not found in dbnam.abc.
--returns correct resultsc1    c_ALIAS
1    2


alter  table abc add  c3 char(5);
insert into abc values(1,1,'one');
insert into abc values(1,2,'two');
insert into abc values(1,3,'three');

show table abc;

CREATE SET TABLE abc ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO     (
      c1 INTEGER,
      c2 INTEGER,
      c3 CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC)PRIMARY INDEX ( c1 );

sel * from abc;
    c1    c2    c3
    1    1    ?
    1    2    ?
    1    3    ?
    1    1    one  
    1    2    two  
    1    3    three


/*Query 5 */ -- Returns ZERO Rows when TEXT COLUMN Alias Used in Subquerysel c1, c2 , c3 as c3_ALIAS from abc
WHERE c1 = 1 and c2 = 2
AND  (c1, c2, c3_ALIAS)   /* Multi Column Subquery Filter */
in ( sel c1, max(c2), c3_ALIAS from abc group by 1,3);   /* Outer Query Alias used in Aggregate Subquery */

-- Expected SELECT Failed. 5628:  Column c3_ALIAS not found in dbnam.abc.
--returns ZERO rows
/*Query 6 */ -- Returns ZERO Rows when TEXT COLUMN Alias Used in Subquerysel c1, c2 , c3 as c3_ALIAS from abc
WHERE c1 = 1 and c2 = 2
AND  (c1, c2, c3_ALIAS)   /* Multi Column Subquery Filter */
in ( sel c1, max(c2), c3 from abc group by 1,3);   /* Fix by referencing subquery reference to actual columnname  */

--Expected results returnedc1    c2    c3_ALIAS
1        2        two  

/*Query 7*/ -- Returns  Rows when TEXT COLUMN Alias Used in NON-Aggregate Subquerysel c1, c2 , c3 as c3_ALIAS from abc
WHERE c1 = 1 and c2 = 2
AND  (c1, c2, c3_ALIAS)   /* Multi Column Subquery Filter */
in ( sel c1, c2, c3_ALIAS from abc );   /* Outer Query Alias used in NON_AGGREGATE Subquery   */

-- Expected SELECT Failed. 5628:  Column c3_ALIAS not found in dbnam.abc.
--returns rowsc1    c2    c3_ALIAS
1        2        two  
Highlighted
Teradata Employee

Re: Group by on a Group by not working as expected!!!

A column reference is first resolved within the local select - the subquery in these cases. When the reference cannot be resolved there, it is resolved within the containing query in these cases the outer query. When it is successfully resolved in the outer query, the subquery becomes a correlated subquery with all of the semantics implied by being a correlated subquery.

Query3 for instance is interpreted as if it had been written:
sel c1, c2 from abc_outer
WHERE c1 = 1 and c2 = 2
AND (c1, c2) /* Multi Column Subquery Filter */
in ( sel c1, abc_outer.c2 from abc ); /* correlated subquery with outer table */
Enthusiast

Re: Group by on a Group by not working as expected!!!

Thanks.  This makes sense and I thoguh that was what it was doing, but we get inconsisten results when it is using text versus and integer value.

Teradata Employee

Re: Group by on a Group by not working as expected!!!

All the results are correct and consistent. Perhaps not obvious but correct.

 

Q5:

The outer query will return 2 rows:

1 2 ?

1 2 two

The null row cannot compare true so it can be eliminated.

The second row value 'two' will be substituted into the inner query making it:

sel c1, max(c2), 'two' from abc group by 1,3

the result of this will be:

1 3 two (because two is a constant and there is only one value of C1 so the max(c2) will be the max of that column in the table)

This is not equal to:

1 2 two

so the result of the query is zero rows.

 

Q6:

The result of the inner query will be:

1    3    ?

1    1    one  
1    2    two  
1    3    three

The outer query will return:

1 2 ?

1 2 two

joining the two will result in

1 2 two

 

Q7:

The inner query will return (for the non null row):

1    1    two

1    2    two
1    3    two
1    1    two  
1    2    two  
1    3    two

The outer row:

1    2    two

is in the set so it is the result of the query.

 

 

Enthusiast

Re: Group by on a Group by not working as expected!!!

Thanks Todd. A little Sql 101 refresher is helpful. Thanks for a taking the time to break it down.