db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jeremy Boynes <jboy...@apache.org>
Subject Re: About improvement of DERBY-134
Date Sat, 12 Mar 2005 02:20:01 GMT
On further investigation, it appears that the use of literal values as 
sort keys is not valid SQL in the 1999 version (18.d on page 653). 
However, in SQL-2003 that restriction has been removed, I would think 
because ORDER BY can now be used in more places (e.g. arrays, analytics).

Further, on page 654 of SQL-1999 note 287 expressly says that the 
facility from SQL-92 to use a signed integer as the sort specification 
no longer exists (although I don't have that spec doc with me to confirm 
the older syntax form).

Given that, I think the ability to use an integer literal is a useful 
extension for compatibility with SQL-1992 but beyond that we should 
treat everything as a value expression per the 1999 and 2003 specs.


Jeremy Boynes wrote:
> Daniel John Debrunner wrote:
>  > If it made it clearer I would prefer that a compilation error is raised
>> for order by expressions that do resolve to constants, since supporting
>> them adds no value. How this error would be raised I'm not sure about.
>> ORDER BY 1 - ok (column position 1)
>> ORDER BY C1 - ok
>> ORDER BY FN(C1) - ok
>> ORDER BY 1 + 1 - disallow
>> ORDER BY C1/C1 - disallow
> The spec says that the sort keys are value expressions which would make 
> both "ORDER BY 1+1" and "ORDER BY C1/C1" valid SQL so we can't raise an 
> error.
> In both cases the result is implementation dependent as the key will be 
> the same for all rows. I think /choosing/ to define the result when the 
> value expression is a simple integer literal is useful so we can support 
> the "ORDER BY 1,2" syntax common on other platforms; I believe anything 
> more complex than that is going to be confusing.
> This may make the parser more complex, but that's a development issue 
> not a user one :-)
> -- 
> Jeremy

View raw message