Re: Multivariate relation (Was topological databases)

> On Thursday, 29 January 2015 04:51:56 UTC+11, Tegiri Nenashi wrote:
> > On Tuesday, January 27, 2015 at 10:01:12 PM UTC-8, Derek Asirvadem wrote:
> >
> > SELECT /column_name_list/
> > ____FROM { /table/ | /view/ | /derived_table/ }
> > ____ORDER BY { /column_name/ | /column_no } [, { /column_name/ | /column_no } ] ...
>
> This syntax is wrong,

Hang on. I am telling you what the manuals in two commercial products state, what millions of developers have been using, since 1985. That is physical evidence. I don't see how that can be "wrong".

If you are trying to say something else, sorry, I do not understand.

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

It is finite and constrained, nothing arbitrary about it.

If you are thinking along the lines of some relational algebra or other, I think I have already stated, in various posts: a. SQL is an implementation (to whatever extent, let's not get side-tracked) of Codd's relational algebra, not any of the others

b. the "unnamed/named perspective" is nonsensical drivel, good for Alice readers, and people who Drop ACID, only ___If you are saying it is a part of mathematics, well-established, before the Alice book, feel free to correct me, in which case, I will correct my statement to: the "unnamed/named perspective" as described in the Alice book is nonsensical drivel, good for Alice readers, and people who Drop ACID, only (one needs the Kool-Aid)

c. but even if we were to discuss it (Alice, 1994), there is no basis to expect that SQL (1984) cares about it or implemented it

d. The only thing implemented in SQL (the standard) is the "named perspective", and that notion of those perspectives is known only to Wonderlanders. It is not known to anyone else: as you stated yourself, people do not know that there are two "unnamed/named perspectives" to choose from. The one they have, since there is only one, does not have a label. ___Wherein, ORDER BY allows only /column_names/

e. And separately, at the high end of SQL implementations, we have many, many extensions, such as referencing a column by number. ___Wherein ORDER BY allows any mix of /column_names/ and /column_nos/

Now, in my previous post, I didn't say that I posted the full syntax, I posted that which was relevant to the conversation. The conversation has changed.

f. Our platforms do more, much more.
___Wherein the ORDER BY clause *additionally* allows Subqueries, and those Subqueries may be correlated. Pretty much anywhere that you can stick a column, by name or other means, we can stick a Subquery, scalar or vector.

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

(You typed it, I have no idea what you meant.)

If you typed that into an interactive SQL window, the Parser would give you an error, because it fails the syntax published in the manual. (It doesn't try to figure out what you meant, it is most probably a typo. Or you could have some expectation that is beyond the scope of the product, the syntax.)

> I understand that it is possible that the original SQL standard might not have expressions.

The standard is less than perfect, but save for a couple of gross errors (eg. the Null Problem), it is actually quite good, it is way more than what mathematicians communicate amongst themselves to be. It is not broken. Compare with the garbage that is published re UML and OMG, which is a "standard" only by their private definitions of the word.

I.e the papers and articles published by mathematicians on this subject are false; fraudulent; schizophrenic (denial of evidenced facts in reality). I am happy to take up this issue if you give me a good example to deal with.

But the *reality* is, just like any language, and wrt any of the standards that apply to the implementation of that language, as well as science and commercial considerations, SQL as implemented has many extensions. I would say that the extensions are a large part of the implementation, and it is a fact that people choose a particular platform on the basis of the extensions (having already chosen SQL). And we have had that since 1984, and we have a progression and maturity over thirty years. Therefore again, the notion that mathematicians have about SQL and its "limitations" are far, far, removed from reality.

Importantly, the commercial vendors have done two things:

1. They have closed what some people (non-technical of course) suggest are holes or errors in the SQL standard. The result being, it is completely use-able, without having to fret about the silliness in the standard
2. Likewise, to the extent that some people (non-technical and non-relational, of course) suggest that the Relational Model is "incomplete", and therefore SQL is "incomplete", being faithful to Codd and the spirit and intent of the RM, we have "completed" it. The result is, given a genuine Relational database implementation, we can perform Relational operations against it. With ease. All reports are a single SELECT command.

Therefore, again, the notion that mathematicians have about SQL and its "limitations" are far, far, removed from commercial reality.

There is a sort of exceptional case. In the last few years, there has been a raft of freeware/shareware/vapourware that fraudulently labels itself "SQL", fraudulent because it does not comply, and the label is therefore a gross misrepresentation. But sadly, they have gotten away with it. So if you are using MyNonSQL, PostgresNonSQL, etc. that is definitely not what an SQL is, either the minimum entry level, or the reality of the extensions in commercial products.

And there is an exception. Oracle may well have the largest market share, but it fails SQL entry level, and it is not commercial by any means (commercial meaning that it ia success to both the customer and the vendor). Eg. same as the freeware/shareware/vapourware, it has no architecture, let alone a commercial Server Architecture. One is forced to code in Oracle-style, to get around its missing features and can't-dos and known-to-be-slows. Hardly portable, good for permanently enslaving the customer base. Ie. in terms of an implementation of SQL, Oracle fits squarely in the non-commercial, freeware/shareware/vapourware category.

> Still, in normal programming language as soon as one feature is introduced, other is deprecated.

Not at all. There are millions of systems using and running previous versions. After 9/11, American banks went around the world, scouring it for 2MB disks, because that is what they had in their trading systems. We have realtime control systems that are running todays version of Unix and Sybase from 1991. Any code written in Sybase SQL since 1991 will run on todays version (except of course where the program did something illegal, that was not caught in 1991, that is caught today).

All the languages that I have used have either full backward-compatibility, or new features with and without the old ones being deprecated.

Absolutely all of them allow something to be programmed in more than one way.

> What are those column numbers are for, to save a user few keystrokes? In modern world with auto completion?

Auto-completion is a query window ? No. You need an SQL IDE for that, and then sure, it completes what it can, such as long tables names. But it cannot pick out syntax errors or override what you type. Besides, the feature hs existed since 1984, when auto-completion and such did not exist. The good old days of PCs before MicroShaft.

Two scenarios. Keystrokes, sure, but that is minor. The real value is this. You might have to be a programmer to appreciate its value. Let's say that I have a nice dirty great big query (note, that means the database is a dog; not Relational; the code is not mine; it is the customers; etc). And I have to make it work for some developer or fix some bug. And I have been fiddling with the columns and their contents, to get it right. Note that columns are filled by either table.column or Subquery, which may be a scalar or a vector, so the program might get a little intense. The columns may not be named (ie. I have not used AS or [labels] or they can't be named. When I get to the ORDER BY, I just want to reference the column, by number, instead of copy-pasting the entire subquery that populates the column. The Optimiser already has the subquery parsed, compiled, and a tree is built (without the ORDER BY), so I know it works. Of course, it also reduces the program size; increases performance; reduces the carbon footprint; gives food to the poor; etc.

The second scenario is a couple of programming tricks.

Cheers
Derek Received on Fri Jan 30 2015 - 05:13:14 CET