db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Yip Ng (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-1894) SQLSTATE 42X10 occurs when qualifying a column with a synonym in ORDER BY clause
Date Mon, 02 Oct 2006 20:40:20 GMT
    [ http://issues.apache.org/jira/browse/DERBY-1894?page=comments#action_12439306 ] 
            
Yip Ng commented on DERBY-1894:
-------------------------------

Good question, Mamta.  This is actually not related to synonym.  If you do issue the query
not using synonym below, it will also fail in Derby.

select t1.id as c1 from t1 where c1 > 0 order by c1; 

One reason is the derived column can be an aggregate function, such as count(*), which can
be aliased.

select count(t1.id) as cnt from t1 where cnt > 1;

This is illegal, since where clause semantics does not allow this.  

> SQLSTATE 42X10 occurs when qualifying a column with a synonym in ORDER BY clause
> --------------------------------------------------------------------------------
>
>                 Key: DERBY-1894
>                 URL: http://issues.apache.org/jira/browse/DERBY-1894
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.2.1.6, 10.3.0.0, 10.1.3.1, 10.2.2.0
>         Environment: Any
>            Reporter: Yip Ng
>         Assigned To: Yip Ng
>         Attachments: derby1894-trunk-diff01.txt, derby1894-trunk-diff02.txt, derby1894-trunk-stat01.txt,
derby1894-trunk-stat02.txt
>
>
> SQLSTATE 42X10 occurs when qualifying a column with a synonym in ORDER BY clause, where
the synonym was declared in a different schema:
> ij version 10.3
> ij> connect 'jdbc:derby:wombat;create=true';
> ij> create schema test1;
> 0 rows inserted/updated/deleted
> ij> create schema test2;
> 0 rows inserted/updated/deleted
> ij> create table test1.testtable(id bigint not null);
> 0 rows inserted/updated/deleted
> ij> create synonym test2.testtable for test1.testtable;
> 0 rows inserted/updated/deleted
> ij> set schema test1;
> 0 rows inserted/updated/deleted
> ij> select testtable.id from testtable;
> ID                  
> --------------------
> 0 rows selected
> ij> set schema test2;
> 0 rows inserted/updated/deleted
> ij> select testtable.id from testtable;
> ID                  
> --------------------
> 0 rows selected
> ij> select testtable.id from testtable order by testtable.id;
> ERROR 42X10: 'TESTTABLE' is not an exposed table name in the scope in which it appears.
> ij> 

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

Mime
View raw message