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] Updated: (DERBY-1784) When qualifying a column name with its synonym name in the select list, Derby throws SQLSTATE 42X04.
Date Tue, 12 Sep 2006 23:25:23 GMT
     [ http://issues.apache.org/jira/browse/DERBY-1784?page=all ]

Yip Ng updated DERBY-1784:
--------------------------

    Attachment: derby1784-trunk-stat03a.txt
                derby1784-trunk-diff03a.txt

Attaching derby1784-trunk-diff03a.txt for DERBY-1784.  The previous patches'
technique is not optimal and does not work in all cases.  After studying the
compiler code abit more, I found that DML statements such as INSERT, UPDATE and DELETE also
suffer from the same problem (they use different bind logic)

With that said, this patch attempts to address all the stated problems above 
when column reference is qualified with a synonym table name.

The fundamental problem is that Derby does not keep the original unbound table name around
once the synonym is resolved.  So the fix is to address this case and apply the qualification
properly. 

In the VIEW resolution case, the system needs to preserve the synonym name as VIEW gets expanded
to a subquery, the name to be set should be the exposed name of the table and not the resolved
table name.  

For * expansion in the SELECT list, if the FROM clause happens to be a synonym, the system
should prepend it with the unbound name and not the resolved table name.  This way the binding
logic is normalized.   

For DML cases, the synonym name needs to be normalized to its base table so that 
setColumnDescriptor can apply correctly.  When the system binds the expression for this result
column, it will resolve this properly since the column binding logic are in the respective
FromTable subclasses implementation where they will use the exposed name this time to check
for qualification.

I wrote more testcases for synonym.sql but I found out that this SQL file is actually not
part of derbylang suite, so the patch added this back to the test bucket.

derbyall ran successfully.  Appreciate if someone can spend some time to review it.
Thanks.
 

> When qualifying a column name with its synonym name in the select list, Derby throws
SQLSTATE 42X04.
> ----------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-1784
>                 URL: http://issues.apache.org/jira/browse/DERBY-1784
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.2.1.0, 10.3.0.0, 10.1.3.1
>         Environment: Any environment
>            Reporter: Yip Ng
>         Assigned To: Yip Ng
>         Attachments: derby1784-trunk-diff01.txt, derby1784-trunk-diff02.txt, derby1784-trunk-diff03a.txt,
derby1784-trunk-stat01.txt, derby1784-trunk-stat02.txt, derby1784-trunk-stat03a.txt
>
>
> When qualifying a synonym with its name in the select list, Derby throws SQLSTATE 42X04
but if there is no qualification of the column, then the select statement executes successfully.
> ij version 10.3
> ij> connect 'jdbc:derby:wombat;create=true' user 'user1' as user1;
> WARNING 01J01: Database 'wombat' not created, connection made to existing database instead.
> ij> create schema test1;
> 0 rows inserted/updated/deleted
> ij> create schema test2;
> 0 rows inserted/updated/deleted
> ij> create table test1.t1 ( id bigint not null );
> 0 rows inserted/updated/deleted
> ij> create synonym test2.t1 for test1.t1;
> 0 rows inserted/updated/deleted
> ij> set schema test1;
> 0 rows inserted/updated/deleted
> ij> select t1.id from t1;
> ID
> --------------------
> 0 rows selected
> ij> set schema test2;
> 0 rows inserted/updated/deleted
> ij> select t1.id from t1;
> ERROR 42X04: Column 'T1.ID' is either not in any table in the FROM list or appears within
a join specification and is outside the scope of the join specification or appears in a HAVING
clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then
'T1.ID' is not a column in the target table
> ij> select id from t1;
> ID
> --------------------
> 0 rows selected
> ------------------ Java Information ------------------
> Java Version:    1.4.2_12
> Java Vendor:     Sun Microsystems Inc.
> Java home:       C:\Program Files\Java\j2re1.4.2_12
> Java classpath:  classes;.
> OS name:         Windows XP
> OS architecture: x86
> OS version:      5.1
> Java user name:  Yip
> Java user home:  C:\Documents and Settings\Yip
> Java user dir:   C:\work3\derby\trunk
> java.specification.name: Java Platform API Specification
> java.specification.version: 1.4
> --------- Derby Information --------
> JRE - JDBC: J2SE 1.4.2 - JDBC 3.0
> [C:\work3\derby\trunk\classes] 10.3.0.0 alpha - (438358)
> ------------------------------------------------------
> ----------------- Locale Information -----------------
> Current Locale :  [English/United States [en_US]]
> Found support for locale: [de_DE]
>          version: 10.3.0.0 alpha - (438358)
> Found support for locale: [es]
>          version: 10.3.0.0 alpha - (438358)
> Found support for locale: [fr]
>          version: 10.3.0.0 alpha - (438358)
> Found support for locale: [it]
>          version: 10.3.0.0 alpha - (438358)
> Found support for locale: [ja_JP]
>          version: 10.3.0.0 alpha - (438358)
> Found support for locale: [ko_KR]
>          version: 10.3.0.0 alpha - (438358)
> Found support for locale: [pt_BR]
>          version: 10.3.0.0 alpha - (438358)
> Found support for locale: [zh_CN]
>          version: 10.3.0.0 alpha - (438358)
> Found support for locale: [zh_TW]
>          version: 10.3.0.0 alpha - (438358)
> ------------------------------------------------------

-- 
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