db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Roman Puchkovskiy <roman.puchkovs...@blandware.com>
Subject Re: Derby complains on SQL that has ORDER BY that order column is not in query result, but it's there
Date Sat, 16 Feb 2008 09:30:00 GMT

Army-4 wrote:
> The code that throws this error was deliberately added as part of 
> DERBY-2351 (which went into, and comments in that Jira suggest 
> that there is a valid reason for doing so.
> But that said, the fact that the query executes if you use the alias 
> instead of the actual column name seems odd.  If the query is supposed 
> to be rejected for the sake of correctness, then why is it that use of 
> the alias makes it "safe" while use of the direct column name makes it 
> "unsafe"?  Is that the way things are _supposed_ to work, or is there a 
> glitch somewhere?
> These are not necessarily questions for you (Roman), just questions that 
> occurred me in re-reading this thread.
> Apologies if I'm missing something obvious.  And of course, if using the 
> alias does *not* make the query run, then ignore everything I've just 
> written...
> Army

Yes, using alias in ORDER BY makes the initial query to execute
successfully. A little illustration:

ij version 10.3
ij> connect 'jdbc:derby:C:\TMP\derby\test_db;create=true';
ij> create table t(c1 int, c2 int);
0 rows inserted/updated/deleted
ij> select distinct c1 from t order by c1;

0 rows selected
ij> select distinct c1 as a1 from t order by c1;
ERROR 42879: The ORDER BY clause may not contain column 'C1', since the
query specifies DISTINCT and that column does not appear in the query
ij> select distinct c1 as a1 from t order by a1;

0 rows selected

View this message in context: http://www.nabble.com/Derby-complains-on-SQL-that-has-ORDER-BY-that-order-column-is-not-in-query-result%2C-but-it%27s-there-tp14982335p15516420.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

View raw message