Re: Parts explosion with repeated subtrees

>> So here you have it: the "Standard SQL" you seem so proud of is
useless
for a very basic and common task. If I understand you correctly WITH RECURSIVE will not work decently for anything but trees. <<

No, the WITH operator is quite powerful and general in Standard SQL-99. Look up the details SQL-99 COMPLETE, REALLY by Gulutzan & Pelzer or in SQL-99 by Melton & Simon. The best examples of actual use are in Chamberlain's DB2 book. Yes, you can do a parts explosion in one SELECT with SQL-99 -- and a lot more.

>> Would you be willing to shortly present here the semantics of WITH
RECURSIVE? My understanding of it (and I'm sorry that I can't really spend time and money on the standard) is that it adds successive generations (levels) of bags of rows, until no longer possible (for example in case of trees) or until the equivalent of Stack Overflow Error happens. <<

That is the most basic form. You also have SEARCH [DEPTH FIRST BY |BREADTH FIRST BY], CYCLE, etc. etc. You are hiding a pretty general and complex program inside one statement. (That complexity is why I don't like it and vendors don't implement it)

>> SQL is a false standard, it always has been. It's been implemented
only fractionally and SQL 92 has still yet to be implemented ... <<

It is a real standard; ISO approved it. Most SQL vendors are (finally!) at intermediate or full SQL-92. They are slowly making SQL-92 syntax an option when they already had a proprietary syntax (i.e. SQL Server uses both getdate() and CURRENT_TIMESTAMP now, etc.).

>> so people would better focus very carefully on their product SQL
reference and not on the SQL standard. <<

I write very Standard SQL and have very little trouble translating my Standard SQL into a dialect. I can also get products that will do this for me. Otherwise, ODBC and JDBC would not work.

>> Like for example users will have to spend money and trees will need
to be cut for Joe Celko's book on ... trees. I imagine you'll later want to write a
follow-up on directed acyclic graphs. <<

Actually, I was thinking about doing book six on either data quality or on how to write bad SQL. We have poor data quality in database, so this is a good topic. We also have new SQL programers who are determined to write bad code, so perhaps a book that shows them the common errors would help.

The only way I have found to model a graph in SQL is with an adjacency list model. The algorithms from procedural languages adapt fairly easily.

>> ...the set-oriented transitive closure is an *absolutely essential*
feature for database users, that with all his knowledge of SQL he can't solve trivial problem.And a standard that doesn't meet the needs of its users becomes slowly but surely irrelevant. <<

I can do a transitive closure for a tree in the nested set model. My problem is that I want to be able to extract and insert sub-trees easily. I would prefer a declarative way, so I can hide the insertion in non-procedural code.

This might not be possible. For example, the nested set model cannot handle a general graph because it is a partial ordering on a set. And you cannot color a map with less than four colors, but life goes on. Received on Wed Dec 18 2002 - 21:24:31 CET