Re: Question on Structuring Product Attributes

From: Lennart Jonsson <erik.lennart.jonsson_at_gmail.com>
Date: Sat, 09 Feb 2013 19:36:01 +0100
Message-ID: <kf64tg$gc6$1_at_dont-email.me>

On 2013-02-09 07:48, derek.asirvadem_at_gmail.com wrote: [...]
> So what we need is:
> --[1]--
> SELECT COALESCE(
> ____SELECT 1
> ________FROM Product
> ________WHERE ProductId = _at_ProductId
> ________AND ProductType = _at_ProductType
> ________)
> ____, 0 )
>
> I think you will be totally with me, so far, yes ?
>

Yes, I think we are in agreement all and all. I'll just lay of a few more thoughts below.

> Ok, now for the Schnitzel mit Rot Kohl und Wein.
>
> If you know that COALESCE is a function that returns **the first** non-Null element from a list;
> that a function can be used wherever a column or value can be used (the value being 1 in this case);
> that if the existence check fails, the inner SELECT will return NULL
instead of 1; **and** you have Sybase or MySQL, we can write the above as:
> --[2]--
> SELECT COALESCE( 1, 0 )
> ____FROM Product
> ____WHERE ProductId = _at_ProductId
> ____AND ProductType = _at_ProductType
>

The following are my arguments for why I believe that COALESCE((select ...),0) should be the norm, and that select COALESCE(0, 1) is an exception that happens to work in some products. Not terribly important, we can agree to disagree :-)

I think that it is mandated that we select FROM something (atleast SQL2003). I tried to find where it is stated in SQL2003 and SQL2008, but boy, those papers are not for the heart of fainted. Cheating a bit and checking with:

http://developer.mimer.se/validator/parser200x/index.tml#parser

indicates that this is the case:

select 1;

        ^
syntax error: ;
  correction: . * FROM <identifier> ;

Furthermore we are allowed to declare a function like:

CREATE FUNCTION f (x INT, y CHAR(1))

    RETURNS SMALLINT
    LANGUAGE SQL
    DETERMINISTIC
    READS SQL DATA
       RETURN COALESCE((select 1 from T),0);

 Conforms to Core SQL-2003

But not:

CREATE FUNCTION f (x INT, y CHAR(1))

    RETURNS SMALLINT
    LANGUAGE SQL
    DETERMINISTIC
    READS SQL DATA

       RETURN select COALESCE(1,0);
              ^-----              ^
syntax error: RETURN select
  correction: BEGIN select

syntax error: ;
  correction: . * INTO <identifier> FROM <identifier> ;

> The placement of COALESCE binds it to the SELECT. The SELECT returns 1 or NULL.
> The COALESCE does nothing if it is 1 (because I placed it first in the
list),
> but it converts a Null to 0. Tested on Sybase ASSE 15.0 and 15.5.
>

My understanding is that a query is - logically - evaluated in the following order:

1. FROM (JOIN)
2. WHERE (ON)
3. GROUP BY -- irrelevant here
4. HAVING -- irrelevant here
5. SELECT (Projection)
6. ORDER BY

The DBMS is of course free to apply another evaluation order, as long as it preserves the semantic of the query

Also COALESCE is a function from [a', a', ...,a'] -> a', for simplicity I'll use [int, int] -> int

Given the query:

SELECT COALESCE(1, 0) FROM T WHERE 1=0 there is nothing that we can apply COALESCE on. We have to create something from nothing in order to return 0.

Perhaps it is just a matter of definition, the standard is rather vague on a number of corner cases (I have a vague memory of a discussion concerning partitions of an empty set, it turned out that you could implement an operation (grouping sets(?)) with several semantics without violating any rules).

However, to me the construction COALESCE((select ...),0) is more obvious than select COALESCE(1,0) ...

> Since my MySQL colleague whom I posted that to, didn't mention anything, except
> that all the functions and DDL and transaction code worked as planned
(implemented from
> my directions), I can only presume that either the above worked as is,
or that if it did
> not, given that he understands the intent, the minor change required
was **so** trivial
> that it was unworthy of mention. I know that it worked on MySQL but I
don't know if it
> worked in that exact, literal, syntactical form.
>

Some things I'm pretty sure that they had to change. MySQL does not support CHECK constraints. A common trick I've seen people use to mimic CHECK constraints is an in-memory table such as:

create table whatever ( msg varchar(255) not null primary key ) engine=MEMORY

and then insert and update triggers that does a primary key violation in that table when the condition of the "CHECK" constraint triggers are violated. Now we are talking ugly ;-)

Cheers
/Lennart

[...] Received on Sat Feb 09 2013 - 19:36:01 CET