Re: SQL Bashing - the Sport of Champions!

James

> On Thursday, 12 February 2015 17:23:44 UTC+11, James K. Lowden wrote:

Ok, now that we have defined the problem, the context, the creatures who propose it for what they are, that you are just the messenger, the sock puppet, we can deal with the specific charges.

Please keep in mind, I am SQL-neutral, SQL-capable, and my tolerance for maggots is pretty low.

> > Please give me an example of what SQL cannot do.

Aw crap, you missed the "Re RDBs" part, and the real world example part. That would have limited the scope of discussion to relevant issues re SQL. Instead, we are reduced to dealing with the comparing the fictitious capabilities and expectations of a fictitious language RADBL, against a real language.

Ok, as long as that context is keep in mind, not forgotten, I will entertain you.

> Tuple comparison,
> select ... where R.(a, b) = S(a, b)
> select ... where (a,b) in (select a, b from S)

Huh ?

Row comparison is dead easy in SQL. Multiple column comparisons are not a problem.

You might be confusing yourself by mixing SQL syntax with the non-existent RADBL syntax, as you have above. If you give me a real world example, I will code it for you, probably while I am sleeping. Please nominate one, or direct me to the Security columns, or use this model (probably good for all your queries), and nominate the rows that you cannot compare. http://www.softwaregems.com.au/Documents/Student%20Resolutions/Parent%20Child.pdf

Or, if you have figured the SQL out, retract the charge

> Column comparison,
> check R.a = S.a where R.b = 'Y'

Ditto, ditto, ditto.

We do have a CHECK command, but it can't be used in that manner. Do you mean SELECT ?

Not sure, but if I translate the cryptic fictions into meaningful intentions, and then translate that into language code, are you trying to do something like this (using my supplied data model, plus obvious inventions): SELECT LastName,

________FirstName
____FROM (
____( SELECT LastName,
____________ FirstName
________FROM Person
________WHERE Gender = "M"
________) AS MALE
____( SELECT LastName,
____________ FirstName
________FROM Person
________WHERE Gender = "F"
____________AND Status = "Pregnant"
________) AS FEMALE
____WHERE MALE.LastName != FEMALE.LastName

As is true for all real languages (ie. excluding fictitious ones, of course), SQL is much easier to read, to debug, if it is (a) formatted consistently, and (b) limited to one operand per line. But you are free retain your cryptic style, to excise the white space, and treat the entire command as a single string. That is what the code generators and report tools do, not for reading by humans. SELECT LastName,FirstName FROM(SELECT LastName,FirstName FROM Person WHERE Gender="M") AS MALE (SELECT LastName,FirstName FROM Person WHERE Gender="F" AND Status="Pregnant") AS FEMALE WHERE MALE.LastName!=FEMALE.LastName

Sorry if something got lost in the two levels of translation. Please feel free to correct me, and I will re-code it for you.

> Universal quantification is similar. Needed for relational division.

Been done to death. You are operating at a level that is possibly a notch or two above the imbecile Celko, but well below the boys from the backwoods of Wisconsin.

Celko (for reference only, do not read): https://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/

McCann (read):
http://www.cs.arizona.edu/people/mccann/research/divpresentation.pdf

Go hunting in Wisconsin.  

Universal quantification is a non-issue.

Relational division is a non-issue.

Note in particular, McCann's caution, slide 18, item 2. You are using the most difficult of four methods.

Of course, you must use the right tool for the job. Of the four methods, for any given scenario, one is usually easier and clearer than the others, meaning that there is not one best method for all scenarios.

Additionally, when you get experience with each method, your code will improve. Eg, I can code the quantification method without the butt-ugly GROUP BYs.

> Yes, you can accomplish those in SQL by writing it out more verbosely

There is no non-verbose switch in SQL. Deal with it. It is dishonest to posit "more verbosely" when there is no "non-verbose" option.

> or, as with universal quantification (find students who have taken
> all required classes), you can employ De Morgan and use "not not
> exists".

Only if you do not understand Derived Tables (Inline Views in Oracle). Ie. a full SELECT command feeding a FROM item. Theoreticians don't even know that it exists.

> If you have that filed under "can do", not only do we disagree
> on the meaning of "can",

No. "Can" is defined in the SQL manual of your platform choice. You have weird and unreasonable expectations of "can", ala relational algebra as commands. Oh, oh, if the horse can jump one metre, why can't it jump ten metres. We read the RA on one side, the manual on the other side, and we fill in the requirement between the two sides, without ado, fanfare, hair-pulling, dummy-spitting.

You are stuck on one side, dummy-spitting.

Can land is much more peaceful that JW land.

> but you will find yourself defending the use
> of lower-level constructs to implement concepts defined by relational
> algebra.

Your statement is self-contradictory. You absolutely do understand that SQL is a low-level language, there is nothing in it but "low-level constructs", and you cry about it not being a high-level language. And especially silly when that high-level language is fictitious.

The non-acceptance of RADBL, that SQL is not declared to accept is a non-issue, Straw Man, already treated.

Besides SQL being indefensible, which I agree, I am not even called to defend anything here. Thus far, all I am doing is showing someone how to do something in a tool that they are less familiar with than I.

> That smell you smell is awk.

Yes. A bit silly to try inside an SQL platform.

> Table comparison,
> where T = (select ... from S where ...)

Stupid to execute inside a server during production, but lets do so anyway.

Dead simple. Use the couplet I gave and restate the gibberish as a real world example please.

> SQL can't constrain views in any way, can't use views as a FK target.

???

The View is in fact a SELECT statement.

1. While it is very silly to implement views of views, and views of views of views, it is not illegal. You must be using a non-SQL.

___ Very Silly.  Theoreticians often do not differentiate between base relations (tables) and derived relations (views).  That is a huge impediment, and it poses a problem everywhere, here as well.  
___ When you implement in implementation land, you cannot remain in the freedom of abstraction in theory land.  That will trip you up. 
___The fact that you are writing SQL means you are stepping out of fiction land, and into implementation land.  Deal with it, or do not take the job. 
___ Get your head around the notion of a single-level view, ie. that uses tables only, not views, and your problem will disappear.

2. If "Constraining" means stating a set of conditions, then "constraining" a View is legal. You must be using a non-SQL.

3. In any case, even if you are using a non-SQL, to overcome that obstacle, simply write a single-level view, with the "constraint" in the WHERE clause.

> I would like to be able to use a union as a domain; SQL cannot.

It sounds simple enough, but there is not enough detail to code from. Can you provide a real world example. Use the couplet is it is suitable.

> When people say it's
> "not relational", that's not what they mean. They mean it does not
> express relational concepts directly or particularly well, and
> sometimes -- bags, column order -- ignores it entirely.

Yeah, I know all that.

awk doesn't launch windows or translate Swahili. So what. If you use it, you know that, and you are using it for the things that awk DOES, not for what it does NOT do.

SQL doesn't accept RADBL as input. So what. No one ever said it could.

> When people say it's
> "not relational", that's not what they mean. They mean it does not
> express relational concepts directly or particularly well, and
> sometimes -- bags, column order -- ignores it entirely.

SQL doesn't accept RADBL as input. So what. No one ever said it could.

You married the girl for her dowry. It is stupid, self-damaging, to then spend your life complaining about the fact that she snores in bed; leaves the toilet seat down; passes wind without opening the window; doesn't speak Swahili; doesn't shave her armpits often enough; and has an obsession with shoes. If you do, it will damage your own life, as well as your married life. Keep the dowry in the forefront of your mind.

No one has ever declared that SQL is anything but a low-level data sub-language. So it is really really silly to cry about the fact that it is: - NOT a full language
___ it is what it is, and it isn't what it isn't, choose the correct set of layers ___ do not attempt to do everything in SQL - NOT a high-level language
___ it is not a can opener either
- NOT capable of accepting RADBL
___ it doesn't accept Swahili or Urdu either - NOT capable of performing all relational functions elegantly ___ it does perform all functions, some of them are inelegant - flatulent, verbose
___ all low-level languages are, especially if you have meaningful (long) column names

Get an IDE, it costs only fifty bucks. They have been availble since 1993. You are working on a low-level data sublanguage with tools that are twenty two years behind the times. Or, if you can't afford fifty bucks, stop complaining about SQL being the problem, start complaining about your income stream.

Relational algebra is not a god. It is not the definitive set of operations that a data sub-language CAN let alone SHOULD implement. There are many things that are not acceptable in an implementation, that are quite acceptable in the algebra. But for the sound understanding of that in the commercial vendors, we would have more chaos than we have now.

Another category is this. The algebra quite rightly is limited to relational operations, and in a database implementation, there is a whole set of operations that must be implemented for databases, that are absent the algebra.

If you do not understand the above two issues, you will have problems with any implementation.

The remainder of your complaints are this. They are not in fact complaints, they are marketing, the marketing of a longing that only you, that tiny fraction of the market, that 1%, have. We couldn't care less about a language that accepts relational algebra as commands to change the database. You care a lot. Ok, fine. End of story, for happily married men.

But that is not enough for you. You have to market the idea of what we are missing. No one in the 99% cares. They only care that the database operations have a sound theory behind it, they don't care about how that theory is expressed, let alone that that expression should be taken literally, as a command. But that doesn't stop you, you have to go, house to house, like a jehovah's witness, and market the very very very strange "heaven" that we are missing out on.

Get a grip.

And don't be "hurt" that we slam the door in your face and go back to our newspaper.

> SQL is a relic of another age, the last man standing after RJE, COBOL,
> Cullinet, PL/1 and all the rest have disappeared. To the economics of
> those days we owe the fact that we use SQL and not Ingres's QUEL, a
> much better language.

Piece of garbage that never worked, beloved by academics (they have a fetish for things that do not work); keeps re-inventing itself as the latest greatest thing that doesn't work. At least it is better than a /D/og, because (a) Stonebraker wrote some code, and lead the way, while Darwen write write a single line, but seduces young minds into writing the impossible dream, complete with windmills, and (b) proved it as a possibility.

Back to SQL.

You seem to have totally missed the reality on the physical plane. SQL is not "of another age", it is of this age. It is current, and growing both within the language and the vendor offerings and features, and with the great number of flavours being written. You are in flat denial of reality.

Of course, it is a low-level language, fortunately and unfortunately, depending on you particular task at hand. For the developers, I give them a full-blown IDE, such as RapidSQL or SQLProgrammer. Get one. I myself use DBArtisan, which is really a DBA IDE, big bucks.

Consider this. Take any one of my projects. I implement the entire RDB plus one entire app (full OO, etc). The whole ship is running. Now what about the hundreds of users who need to access the RDB for various scheduled and ad hoc reports ? Do they sit there and cry like a baby because SQL is low-level; verbose; flatulent; doesn't take statistical formulæ as input; etc ? Do they ? No.

Why ? I sit down with each group, discuss their needs, and give them and and all of the following. Remember it is an Open Architecture World out here:

- ODBC connection to MS Access/Excel
- Simple report tool, ala Crystal Reports
- SAS for statisticians and number crunchers

Everyone is blissfully happy. No one writes SQL, they just know it exists somewhere, call one of the developers if the see a funny message.

So the point is this, even the dumbest of dumb users is operating at a level that is significantly above yours. You are working in a SQL developer role, you refuse to get a developer tool, and you complain about the langauge, instead of complaining about the lack of a tool. G.E.T__A.N__S.Q.L__I.D.E.

> The strange, ignorant time we currently live in
> promises very little progress, if any, because users of databases
> doen't realize how much is being lost, never mind forgone.

Progress that is relevant only to that 1% who can't get an IDE.

How much is lost. Hilarious. You are marketing again, about a false heaven, the fictitious dreams of the fictional language. We couldn't care less.

> If your assertion is that SQL can, after a fashion, express any
> relational algebra function, I concede the point.

I did, and with the above qualifiers and direction, which makes the "after a fashion" a very silly statement.

We could end the SQL section here. But there are strange things, that I did not expect an old hand such as *you*, would present under this heading, so let me tackle them.

Now for the really tiny ... unbelievable, coming from you.

> Why does UNION (and similar) require column order to match, but not
> name?

You do understand that each set that feeds an UNION is a result set, a derived relation, don't you ? That it is your job as a programmer to feed the UNION with consistent sets ? There is no suggestion that the platform performs magical mystical transformations ?

The notion of the Heading being tightly coupled to the column is a TTM absurdity. Complete pig poop. It doesn't happen in SQL, so in any case, it is silly to expect the non-feature here. Same as complaining about the 1930's guitar in your hands not having the range of an electric guitar of 1990's, it does not apply. Either use the guitar in your hands or get off the stage. Complaining is for maggot-ridden old women such as Darwen.

The column DOMAINS must match, otherwise the UNION breaks.

The only way to get the domains to match is to get the column order in each of the sets to match.

The column names are therefore irrelevant. Most platforms take the headings in the first set as nominal. They could just as easily take them from the last set. Since only you know the meaning of the union-ed columns, you are free to GIVE the headings. But you don't, you complain that the low level language doesn't perform high-level functions that no one declared it could.

> Why does SELECT return duplicate column names

Because *you* told it to return duplicate columns, 
--AND-- *you* failed to distinguish the difference to between them, 
--AND-- *you* failed to inform the SELECT about such.
 

I never have duplicate column names returned to me.

awk does the same, why don't you complain about that ?

> or permit unnamed
> columns?

Why not ?

It is a disgusting lie, typical of Darwen, that it is a "column".

The reason the "column" has no name is because it is computed, it is not a column, and your platform could not figure out which source column applied the most (ie. my platform will do a better job than yours, but still, naming a computed column is not the parsers job; it is your job).

Just give the result set column a name. AS or [].

awk does the same, why don't you complain about that ?

> Why SELECT DISTINCT but UNION ALL?

???

I have UNION ALL, UNION nothing, and UNION DISTINCT. They each return different results. Yours must be non-SQL.

> Why must FROM appear only
> between SELECT and WHERE?

It was arbitrary. Now it is history. Deal with it.

awk requires print operands to appear between "print" and the line terminator. Why don't you complain about awk being so stupid ?

> (Why even say "SELECT"?)

No idea what you mean. To differentiate it from the other three verbs ? A default verb would be a stupid concept in a data sub-language.

> What purpose does
> HAVING serve anymore?

Well, it is less of an use now than it was in the 80's, but that doesn't make it use-less, refer McCann Division method 4, slide 27.

It cannot be deprecated. We have code out there that we need not change, simply because better operators have arrived since then.

> Why do subqueries require aliases even when
> unreferenced?

SQL doesn't. Mine doesn't. Yours must be non-SQL.

> Just look at the butt-ugly porcine ungainliness of UPDATE. Updating
> one table from another has to be the most cumbersome, verbose, and
> redundant aspect of SQL, not that it lacks competition (except in any
> other language). Why can we not say instead
>
> R(a, b, c) = S(a, b, c) WHERE R.x = S.x

Because SQL does not parse Swahili, or Urdu, or RADBL, no one stated that it could, silly to expect bread from the butcher.

If you translated that giberish into recognisable operations, it is very easy to code in SQL.

UPDATE is cumbersome only if you do not understand that: - the target is a single table
- the FROM should be a full SELECT.

> SQL is indefensible.

Of course. Only a fool would attack a low-level language. Only another fool would defend it.

But, SQL is not the problem. As detailed in this and the first post.

Your serve. ;-)

I trust you will appreciate my new use of colour, for this new thread.

Now, if you do give me any further SQL "problems" to deal with, please limit them to: - real world example
- Relational Databases

While I can read gibberish, I hope I have detailed the silliness of expecting either the SQL parser, or me, to translate gibberish (or Swahili) to SQL, enough, that you will refrain from doing so in future.

Thank you for your other posts, which are excellent.

Cheers
Derek Received on Wed Feb 18 2015 - 10:55:58 CET