SQLSTATE 42X10 occurs when qualifying a column with a synonym in ORDER BY clause

• Key: DERBY-1894
• Type: Bug
• Status: Closed
• Resolution: Fixed
• Priority: Major
• Assignee: Yip Ng
• Reporter: Yip Ng
• Component/s: SQL
• Affects Version/s: 10.1.3.1, 10.2.1.6, 10.2.2.0, 10.3.1.4
• Fix Version/s: 10.1.3.2, 10.2.2.0, 10.3.1.4

Description

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>

Comments

Attaching patch derby1894-trunk-diff01.txt for DERBY-1894. The fix is in FromBaseTable's getFromTableByName() method, where it is using the
resolved synonym table name to do the binding for ORDER BY column.
Patch includes additional tests and derbyall passes. Appreciate if someone
can review it. Thanks.

[Mamta A. Satoor]

Yip, I reviewed the code and it looks good to me. For the tests, should we have a test case where a correlation name is used in order by? May be there is already such a test or may be this test will not test the changes you made but here is a test case based on the sql script included in this Jira entry
set schema test2;
select testtable.id as c1 from testtable order by c1;

[Yip Ng]

Thanks for reviewing the patch, Mamta. I added a couple more testcases this week and also included your suggested testcase. Attaching derby1894-trunk-diff02.txt.

[Mamta A. Satoor]

Yip, I have a feeling that I missing something obvious here but why should following statement (towards the end of the tests that you added) fail?
select t1.id as c1 from t1 where c1 > 0 order by c1;

[Yip Ng]

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.

[Mamta A. Satoor]

Thanks, Yip. I have no more questions on the patch and I give it a +1.

[Mike Matrigali]

committed to trunk:

m3_ibm142:172>svn commit

Sending java\engine\org\apache\derby\impl\sql\compile\FromBaseTable.java
Sending java\testing\org\apache\derbyTesting\functionTests\master\synonym
.out
Sending java\testing\org\apache\derbyTesting\functionTests\tests\lang\syn
onym.sql
Transmitting file data ...
Committed revision 452259.

[Mike Matrigali]

merged from trunk to 10.1 branch:
m101_142:12>svn commit

Sending java\engine\org\apache\derby\impl\sql\compile\FromBaseTable.java
Sending java\testing\org\apache\derbyTesting\functionTests\master\synonym
.out
Sending java\testing\org\apache\derbyTesting\functionTests\tests\lang\syn
onym.sql
Transmitting file data ...
Committed revision 452269.

[Mike Matrigali]

backported fix from trunk to 10.2.1.7 (version does not exist in jira right now - so marked fixed in 10.2.2)

[Mike Matrigali]

fix has made it to trunk, 10.2 branch and 10.1 branch

[Andrew McIntyre]

This issue has been resolved for over a year with no further movement. Closing.