Re: Question on Structuring Product Attributes
Date: Sat, 09 Feb 2013 04:01:22 +0100
Message-ID: <kf4e53$3hu$1_at_dont-email.me>
On 2013-02-08 23:30, derek.asirvadem_at_gmail.com wrote:
> On Saturday, 9 February 2013 06:29:42 UTC+11, Lennart Jonsson wrote:
>> BTW, is the COALESCE necessary? Either we have a row with tuple(1) or we >> don't have a row at all?
>
> Correct.
>
> I generalised that [tutorial] code to work with most SQLs (eg. COALESCE() is SQL but Sybase and others had ISNULL() to do the same thing. The syntax and operation of ISNULL is intuitive; COALESCE is non-intuitive.). If we don't have a row at all, the SELECT returns NULL. The COALESCE converts the NULL to zero, in order to RETURN an INT.
>
> 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:
[1] SELECT COALESCE(1,0) from LATERAL(VALUES(1)) WHERE 1=0
[2] SELECT COALESCE(1,0) from LATERAL(VALUES(1)) WHERE 1=1
The result of [1] should be an empty set, and the result of [2] should be {(1)}, so:
SELECT 1 from LATERAL(VALUES(1)) WHERE ...
looks equivalent. I assume that the empty set in [1] is transformed into null in the equals predicate:
(SELECT 1 from LATERAL(VALUES(1)) WHERE 1=0) = 1
null = 1
null
If not mistaken a check constraint must not evaluate to FALSE. My
question was therefor whether the construction should have been:
COALESCE((SELECT 1 from LATERAL(VALUES(1)) WHERE ...), 0)
/Lennart Received on Sat Feb 09 2013 - 04:01:22 CET