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
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
