Re: Question on Structuring Product Attributes

On Saturday, 9 February 2013 14:01:22 UTC+11, Lennart Jonsson wrote:
>
> > I use strict Null handling, to avoid ambiguities and avoidable errors (if you have standards, there is no "Null Problem" in SQL).
>
> But is COALESCE() evaluated at all? We have two cases:
>
> <snipped>
>
> If not mistaken a check constraint must not evaluate to FALSE.

Yes. The detail you presented is good for novice coders; just that sentence is the relevant issue to be addressed. The intent of the function is to return zero for the empty set. Taking into consideration that the reader is responsible for making any changes that are required to make that function work on their platform, we could say, that is the end of the story, the rest is coding detail issues.

(My example CHECK Constraint specifically requires 1, but I think you understand all that)

Thank you for giving my words attention.

> My question was therefor whether the construction should have been:
>
> COALESCE((SELECT 1 from LATERAL(VALUES(1)) WHERE ...), 0)

(Apologies, I did not get that from your previous post.)

I looked at the function code again with your comments in mind, and I realised that it may be "too clever", and therefore may need some explanation.

I am not going to divert and give explanations for each of them, but keep in mind that SQL requires that COALESCE is implemented as a Function, however, that does not limit the scope of any particular implementation. So for most SQLs, you still need a SELECT outside your code above:

SELECT COALESCE(
____(SELECT 1 from LATERAL(VALUES(1)) WHERE ...) ____, 0)

In Sybase we check for existence by:
SELECT 1

____FROM Product
____WHERE ProductId = _at_ProductId
____AND ProductType = _at_ProductType

The 1 is recommended over asterisk. The WHERE clause in this case grabs one row (or not), but in many cases of existence checking there is more than one row (thousands, whatever). Unlike some SQL platforms, in Sybase the 1 will cause the existence check to succeed on the first qualifying row, and the remaining [thousands of] rows are not scanned or processed by the back end.

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 ?

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

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.

I have no idea if DB2 can handle [2]; I would hazard a guess that z/OS would but LUW wouldn't. If it doesn't, revert to [1]

Of course this is a simple example; in large applications, the SELECTs are "more complex". I have noticed in my dealings with many MS SQL teams, that they always code SELECT [1] with (eg) 16 subqueries, where I code a single SELECT [2] with no subqueries. MS SQL (up to 2008, no idea what the latest MS abortion does) has very much the same Optimiser as Sybase 12 (superceded in 2007); it handles [2] beautifully. When I ask them why they did not know that, why they had not thought it out, applied logic and tried that, they always give the same answer: they read it in some blog on SQL Central or SQLTeam or the back of a bus, and they did not think for themselves. Great for herd animals being lead around by the nose. Not only do blog posters have a lot to answer for when they die; but blog readers have a liability in their own enslavement.

(In case it needs to be said, that para is not aimed at you, Lennart, you are precise and thoughtful.)

Thanks again for pointing that out to me. I will think about adding explanatory notes to my posted function.

Cheers
Derek Received on Sat Feb 09 2013 - 07:48:03 CET