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

Lauri,

  It's up to you to evaluate the potential of your users and decide what answers they should get from what questions. But perhaps that should only apply to questions processed or preprocessed by your code. This is a well-defined SQL query, with a well-defined answer:

SELECT P#, PNAME
FROM V_S_SP_P; Now I'm NOT assuming you want your language to be SQL., but please correct me if I'm wrong. What I'm hearing is this:

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;
2. [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.

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]
  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, 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.

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

SK

Lauri Pietarinen wrote:

>
>
> Steve Kass wrote:
>
>>
>> and easily into the optimized result you desire. Why do you blame
>> bags here when you should blame
>> the programmer who believes this question to be represented
>> by
>> SELECT P#, PNAME
>> FROM V_S_SP_P;
>> ??
>
>
> Listen, I am trying to make things _easier_ for my
> programmers and end users. I am hiding the complexity
> of the underlying tables. Are you saying that I should not
> be able to do this? You think each individual user and
> programmer should necessarily be always exposed to
> underlying complexity???
>
> Please note that I even accepted that the user must
> remember to give DISTICT. Not even in that case
> did SQLServer know how to optimize the
> query properly.
>
> regards,
> Lauri Pietarinen
>
>
Received on Mon Mar 03 2003 - 09:32:49 CET