You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Yip Ng (JIRA)" <de...@db.apache.org> on 2006/09/27 22:30:50 UTC
[jira] Created: (DERBY-1894) SQLSTATE 42X10 occurs when qualifying
a column with a synonym in ORDER BY clause
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.1.3.1, 10.2.1.5, 10.2.2.0, 10.3.0.0
Environment: Any
Reporter: Yip Ng
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
[jira] Updated: (DERBY-1894) SQLSTATE 42X10 occurs when qualifying
a column with a synonym in ORDER BY clause
Posted by "Yip Ng (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1894?page=all ]
Yip Ng updated DERBY-1894:
--------------------------
Attachment: derby1894-trunk-stat02.txt
derby1894-trunk-diff02.txt
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.
> 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
[jira] Commented: (DERBY-1894) SQLSTATE 42X10 occurs when
qualifying a column with a synonym in ORDER BY clause
Posted by "Mamta A. Satoor (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1894?page=comments#action_12439244 ]
Mamta A. Satoor commented on DERBY-1894:
----------------------------------------
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;
> 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-stat01.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
[jira] Resolved: (DERBY-1894) SQLSTATE 42X10 occurs when qualifying
a column with a synonym in ORDER BY clause
Posted by "Mike Matrigali (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1894?page=all ]
Mike Matrigali resolved DERBY-1894.
-----------------------------------
Resolution: Fixed
fix has made it to trunk, 10.2 branch and 10.1 branch
> 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
> Fix For: 10.1.3.2, 10.2.2.0, 10.3.0.0
>
> 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
[jira] Assigned: (DERBY-1894) SQLSTATE 42X10 occurs when qualifying
a column with a synonym in ORDER BY clause
Posted by "Yip Ng (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1894?page=all ]
Yip Ng reassigned DERBY-1894:
-----------------------------
Assignee: Yip Ng
> 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.5, 10.3.0.0, 10.1.3.1, 10.2.2.0
> Environment: Any
> Reporter: Yip Ng
> Assigned To: Yip Ng
>
> 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
[jira] Updated: (DERBY-1894) SQLSTATE 42X10 occurs when qualifying
a column with a synonym in ORDER BY clause
Posted by "Yip Ng (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1894?page=all ]
Yip Ng updated DERBY-1894:
--------------------------
Attachment: derby1894-trunk-stat01.txt
derby1894-trunk-diff01.txt
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.
> 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.5, 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-stat01.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
[jira] Updated: (DERBY-1894) SQLSTATE 42X10 occurs when qualifying
a column with a synonym in ORDER BY clause
Posted by "Yip Ng (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1894?page=all ]
Yip Ng updated DERBY-1894:
--------------------------
Derby Info: [Patch Available]
> 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.5, 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-stat01.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
[jira] Commented: (DERBY-1894) SQLSTATE 42X10 occurs when
qualifying a column with a synonym in ORDER BY clause
Posted by "Mamta A. Satoor (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1894?page=comments#action_12439294 ]
Mamta A. Satoor commented on DERBY-1894:
----------------------------------------
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;
> 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
[jira] Commented: (DERBY-1894) SQLSTATE 42X10 occurs when
qualifying a column with a synonym in ORDER BY clause
Posted by "Mamta A. Satoor (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1894?page=comments#action_12439314 ]
Mamta A. Satoor commented on DERBY-1894:
----------------------------------------
Thanks, Yip. I have no more questions on the patch and I give it a +1.
> 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
[jira] Updated: (DERBY-1894) SQLSTATE 42X10 occurs when qualifying
a column with a synonym in ORDER BY clause
Posted by "Mike Matrigali (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1894?page=all ]
Mike Matrigali updated DERBY-1894:
----------------------------------
Fix Version/s: 10.2.2.0
backported fix from trunk to 10.2.1.7 (version does not exist in jira right now - so marked fixed in 10.2.2)
> 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
> Fix For: 10.3.0.0, 10.1.3.2, 10.2.2.0
>
> 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
[jira] Updated: (DERBY-1894) SQLSTATE 42X10 occurs when qualifying
a column with a synonym in ORDER BY clause
Posted by "Mike Matrigali (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1894?page=all ]
Mike Matrigali updated DERBY-1894:
----------------------------------
Fix Version/s: 10.1.3.2
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.
> 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
> Fix For: 10.3.0.0, 10.1.3.2
>
> 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
[jira] Updated: (DERBY-1894) SQLSTATE 42X10 occurs when qualifying
a column with a synonym in ORDER BY clause
Posted by "Mike Matrigali (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1894?page=all ]
Mike Matrigali updated DERBY-1894:
----------------------------------
Fix Version/s: 10.3.0.0
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.
> 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
> Fix For: 10.3.0.0
>
> 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
[jira] Commented: (DERBY-1894) SQLSTATE 42X10 occurs when
qualifying a column with a synonym in ORDER BY clause
Posted by "Yip Ng (JIRA)" <de...@db.apache.org>.
[ 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