[ 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
