Re: A Topological Relational Algebra in Lisp

> On Saturday, 31 January 2015 04:29:17 UTC+11, Tegiri Nenashi wrote:
> On Thursday, January 29, 2015 at 8:13:16 PM UTC-8, Derek Asirvadem wrote:
> > > because "order by" clause allows arbitrary expressions,
> >
> > No it doesn't. As per the syntax above, it allows either a /column_name/ or a /column_no/ (where 0 < /column_no/ ≤ /no_of_columns/
>
> "order by emp_no+1" works in Oracle and MySql ... I honestly don't understand why are you reluctant to allow users to sort their result by salary+bonus.

> Ouch. You are right. I have made a mistake, sorry. I should have examined it instead of glancing at the manuals. Thank you for staying with it until it was resolved. So let me back up to the point where we were before I took the wrong fork in the road.

> This syntax is wrong, because "order by" clause allows arbitrary expressions, e.g.

Yes.

Corrected:

SELECT /column_name_list/

____FROM { /table/ | /view/ | /derived_table/ }
____ORDER BY { 
________/column_name/ | 
________/expression/ |
________/column_no/ 
________} 
________[, { /column_name/ | /expression/ | /column_no } ] ...

Where /expression/ may or may not reference a column; subquery; etc. Where /column_no/ may be an expression, that resolves to an integer (a real is truncated) 0 < cn ≤ /no_of_cols/.

If you have a problem with that, note that the DB2 and Sybase Parsers are far more intelligent (Sybase implemented it fourth major Parser version in 2007; the first was 1984) than Oracle and MyNonSql, which I would say are hardly parsers. Ie. it handles context well.

> "order by emp_no+1" works in Oracle and MySql

And now that I have had my coffee, it works in Sybase.

> the two the most ubiquitous databases on the planet.

[Distraction] Sure, but they are not SQL compliant. Their use of "SQL" is fraudulent.

> select * from employees order by salary+1
>
> The "1" there, is it a column number? What column does it refer to?

No, that is an /expression/, which happens to be a /column_name/ and some arithmetic. Any SQL arithmetic will be fine. Any whitespace is fine. It resolves to the value of salary for the result set row plus the value 1.

If the /expression/ does not reference a column_name, then it is a /column_no/.

> Still, in normal programming language as soon as one feature is introduced, other is deprecated. What are those column numbers are for, to save a user few keystrokes? In modern world with auto completion?

My comments on those issues stand.

Cheers
Derek Received on Sat Jan 31 2015 - 07:54:52 CET