Re: Extending my question. Was: The relational model and relational algebra - why did SQL become the industry standard?

From: Lauri Pietarinen <lauri.pietarinen_at_atbusiness.com>
Date: Mon, 03 Mar 2003 11:25:45 +0200
Message-ID: <3E631F99.8010404_at_atbusiness.com>


Steve Kass wrote:

Thanks for taking the time to read and understand my posting!

> 1. [You said] There are lots of interesting questions that can be
> easily answered using this view.
> The question "give me all products that are supplied
> by somebody" is easy to state:
> SELECT P#, PNAME
> FROM V_S_SP_P;
>
>
> 1. [I'm hearing]
> SELECT P#, PNAME
> FROM V_S_SP_P;
> should return the _set_ of P#, PNAME pairs that appear in V_S_SP_P.
>
> If you want this to happen, then the query language can't
> be SQL. I don't have a problem with a language the does this,
> but SQL Server doesn't understand that language.

OK, agreed. I should have said SELECT DISTICNT P# etc...

Yes, that would be a different language.

> 2. [You said] Why can't it transform this query
>
>
> SELECT DISTINCT P#, PNAME
> FROM V_S_SP_P;
>
> [assuming you admit DISTINCT is ok]

yes yes...

> into
>
> SELECT P#, PNAME
> FROM P
> WHERE EXISTS
> (SELECT * FROM SP
> WHERE P.P# = SP.P#)?
>
>
>
> 2. [I'm hearing] You can perhaps live with the requirement
> that DISTINCT be added, but you want SQL Server to
> figure out from the metadata that because of FK constraints
> and NOT NULL constraints and correspondence of types,
> the optimization should happen. You think that if the query
> without DISTINCT "eliminated duplicates", then it would
> be easier to generate an efficient plan.
>
> I'm fairly sure but not absolutely sure, that the faster EXISTS
> query is identical to the DISTINCT query,

It is, assuming the obvious constraints are in place.

> but I don't think
> the reason SQL Server fails to optimize the query has
> anything to do with bags or sets. I don't think SQL Server
> will ever eliminate tables from the query plan in situations
> like this. Could it? Sure, if the query optimizer had more
> features than it does.

Well, that's the whole point! Here is (once again) a quote from Hugh Darwen in
http://www.mcjones.org/System_R/bs12.html:

"Because BS12 spurned duplicate rows, it was obliged to make "duplicate removal avoidance" a strong feature of its optimiser--something that SQL implementations are only now beginning to catch up with (in SQL terms, this means not always firing up the duplicate elimination mechanism just because the user said DISTINCT--you might be able instead to prove that there cannot be any duplicates)."

> I guess I'm not really sure what you mean when you say
> that multisets are a hndrance to query optimization.

Well, at least they are a DISTRACTION. Instead of going forward and enabling
new and exciting possibilities we are basically stuck in the same situation we
were 10-15 years ago. Granted there are lots of things we can do with SQL, there could be _much more_.

Lauri Received on Mon Mar 03 2003 - 10:25:45 CET

Original text of this message