Incorrect Alias Column Name in Aggregate Subquery Reference is not Failing - Zero rows returned

Database
Enthusiast

Incorrect Alias Column Name in Aggregate Subquery Reference is not Failing - Zero rows returned

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  

 

1 REPLY
Enthusiast

Re: Incorrect Alias Column Name in Aggregate Subquery Reference is not Failing - Zero rows returned

Found the behavior inconsistent when referening integer column alias verus text column alias. 

 

In both scenarios, an alias was referenced by mistake in a subquery selection list.  When the text column was referenced with an aggregate in the subquery, it returned zero rows.

 

When the aggregate was removed, it rerurned the correct row even though the column name in the subquery did not exist.  It was defined as an alias inthe outer query.

 

Using the same scenario with integer values in an aggregate subquery, the correct data was returned.