Re: [sqlite] Does coalesce terminate early?

[Jan Hudec]

On Thu, Sep 15, 2011 at 18:58:00 +0100, Tim Streater wrote:
> On 15 Sep 2011 at 18:21, Jay A. Kreibich  wrote: 
> >  Why?  It is a function call.  One would expect all the parameters to
> >  be evaluated, and then the function called.  In almost all languages,
> >  short-circuit evaluation is reserved for operators, not function
> >  parameters.
> 
> Really? I'd like to think that if a parameter is not used due to the
> particular logic of the function, then it's not evaluated but I wouldn't go
> into court on that :-)

No. That only applies to non-strict (lazy) languages. Of which only Haskell
and possibly Miranda and Clean have any non-trivial user base outside
academia. Note, that all those languages are functional. Combining (implicit,
general) lazy evaluation with side effects would lead to unbelievable mess as
you would have hard time telling in which order the side-effect will happen.

Many languages do allow explicit lazy evaluation either by explicitly passing
functions or by having special kind of functions, macros, that take bits of
code instead of values and return bit of code to be evaluated (Lisp, Scheme,
Perl6, D).

That said, pure SQL does not allow side-effects and built-in functions may be
implemented specially just like operators can, so it's hard to tell whether
one should expect SQL to short-circuit in coalesce(). I would definitely
expect it to short-circuit in case/when/then/else/end, but I simply would not
know in function-like-looking coalesce().

-- 
 Jan 'Bulb' Hudec 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does coalesce terminate early?

[Tim Streater]

On 15 Sep 2011 at 18:21, Jay A. Kreibich  wrote: 

> On Thu, Sep 15, 2011 at 11:13:57AM -0500, Puneet Kishor scratched on the wall:
>
>> While your suggested documentation won't harm, and will likely help,
>> actually the above does suggest to me a short-circuit-ish kind of
>> logic from the assertion that "The coalesce() function returns a
>> copy of its first non-NULL argument."
>
>  Why?  It is a function call.  One would expect all the parameters to
>  be evaluated, and then the function called.  In almost all languages,
>  short-circuit evaluation is reserved for operators, not function
>  parameters.

Really? I'd like to think that if a parameter is not used due to the particular 
logic of the function, then it's not evaluated but I wouldn't go into court on 
that :-)

--
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does coalesce terminate early?

[Jay A. Kreibich]

On Thu, Sep 15, 2011 at 11:13:57AM -0500, Puneet Kishor scratched on the wall:

> While your suggested documentation won't harm, and will likely help,
> actually the above does suggest to me a short-circuit-ish kind of
> logic from the assertion that "The coalesce() function returns a
> copy of its first non-NULL argument."

  Why?  It is a function call.  One would expect all the parameters to
  be evaluated, and then the function called.  In almost all languages,
  short-circuit evaluation is reserved for operators, not function
  parameters.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does coalesce terminate early?

[Sam Carleton]

On Thu, Sep 15, 2011 at 12:24 PM, Simon Slavin  wrote:
>
> On 15 Sep 2011, at 5:00pm, Sam Carleton wrote:
>
>> I don't mean to be difficult, but I simply don't get any indication of
>> how exactly COALESCE actually functions from this description:
>>
>> "coalesce(X,Y,...)    The coalesce() function returns a copy of its first
>> non-NULL argument, or NULL if all arguments are NULL. Coalesce() must
>> be at least 2 arguments. "
>>
>> I get there has to be at least two arguments, but no where do I see
>> where it states it only executes an argument if all the previous one
>> fail.
>
> Fair point.
>
>> Now it would be crystal clear if it said something like:
>>
>> "coalesce(X,Y,...)    The coalesce() function returns a copy of its first
>> non-NULL argument, or NULL if all arguments are NULL. Coalesce() must
>> be at least 2 arguments. Coalesce() does use short-circuit
>> evaluation."
>
> That's a poor description because it relies on the reader knowing what 
> 'short-circuit' means.  A better one would be something like

As the saying goes, brevity is the soul of wit:)  My theory is that if
someone does not know what short-circuit evaluation means, they can
look it up and will very quickly end up at Wikipedia that has a
complete explaination of it.  They might even learn that they can use
it in other languages in which they use.  It also educates them to a
common term in the programming world which well help them communicate,
in general.

With all that said, I am happy with your more verbose version, too:)
I am simply all for improve the documentation:)

Sam
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does coalesce terminate early?

[Simon Slavin]

On 15 Sep 2011, at 5:00pm, Sam Carleton wrote:

> I don't mean to be difficult, but I simply don't get any indication of
> how exactly COALESCE actually functions from this description:
> 
> "coalesce(X,Y,...)The coalesce() function returns a copy of its first
> non-NULL argument, or NULL if all arguments are NULL. Coalesce() must
> be at least 2 arguments. "
> 
> I get there has to be at least two arguments, but no where do I see
> where it states it only executes an argument if all the previous one
> fail.

Fair point.

> Now it would be crystal clear if it said something like:
> 
> "coalesce(X,Y,...)The coalesce() function returns a copy of its first
> non-NULL argument, or NULL if all arguments are NULL. Coalesce() must
> be at least 2 arguments. Coalesce() does use short-circuit
> evaluation."

That's a poor description because it relies on the reader knowing what 
'short-circuit' means.  A better one would be something like

The coalesce() function evaluates its first argument.  If the value returns is 
not NULL, that value is returned.  Otherwise it checks its next argument for 
NULL, and so on through the list in sequence.  If it runs out of arguments it 
returns NULL.  Versions before 3.6.21 evaluated all arguments first, then 
inspected the values returned.

However perhaps this suggests that you can't depend on details and should 
depend only on the value returned, not how it was arrived at.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does coalesce terminate early?

[Puneet Kishor]

On Sep 15, 2011, at 11:00 AM, Sam Carleton wrote:

> On Thu, Sep 15, 2011 at 10:05 AM, Simon Slavin  wrote:
>> 
>> Documentation for COALESCE is here:
>> 
>> http://www.sqlite.org/lang_corefunc.html
>> 
>> It does not say whether it does short-circuit evaluation but the description 
>> does imply testing one by one, rather than evaluating all the conditions 
>> first.
> 
> Simon,
> 
> I don't mean to be difficult, but I simply don't get any indication of
> how exactly COALESCE actually functions from this description:
> 
> "coalesce(X,Y,...)The coalesce() function returns a copy of its first
> non-NULL argument, or NULL if all arguments are NULL. Coalesce() must
> be at least 2 arguments. "


While your suggested documentation won't harm, and will likely help, actually 
the above does suggest to me a short-circuit-ish kind of logic from the 
assertion that "The coalesce() function returns a copy of its first non-NULL 
argument."

On the other hand, yeah, I am all for potentially redundant documentation if it 
can help.

> 
> I get there has to be at least two arguments, but no where do I see
> where it states it only executes an argument if all the previous one
> fail.  Now it would be crystal clear if it said something like:
> 
> "coalesce(X,Y,...)The coalesce() function returns a copy of its first
> non-NULL argument, or NULL if all arguments are NULL. Coalesce() must
> be at least 2 arguments. Coalesce() does use short-circuit
> evaluation."
> 
> Sam
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does coalesce terminate early?

[Sam Carleton]

On Thu, Sep 15, 2011 at 10:05 AM, Simon Slavin  wrote:
>
> Documentation for COALESCE is here:
>
> http://www.sqlite.org/lang_corefunc.html
>
> It does not say whether it does short-circuit evaluation but the description 
> does imply testing one by one, rather than evaluating all the conditions 
> first.

Simon,

I don't mean to be difficult, but I simply don't get any indication of
how exactly COALESCE actually functions from this description:

"coalesce(X,Y,...)  The coalesce() function returns a copy of its first
non-NULL argument, or NULL if all arguments are NULL. Coalesce() must
be at least 2 arguments. "

I get there has to be at least two arguments, but no where do I see
where it states it only executes an argument if all the previous one
fail.  Now it would be crystal clear if it said something like:

"coalesce(X,Y,...)  The coalesce() function returns a copy of its first
non-NULL argument, or NULL if all arguments are NULL. Coalesce() must
be at least 2 arguments. Coalesce() does use short-circuit
evaluation."

Sam
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does coalesce terminate early?

[Simon Slavin]

On 15 Sep 2011, at 2:57pm, Alexey Pechnikov wrote:

> Richard, is the future documented anywhere?

Heh.  I assume you meant to type 'feature'.

Documentation for COALESCE is here:

http://www.sqlite.org/lang_corefunc.html

It does not say whether it does short-circuit evaluation but the description 
does imply testing one by one, rather than evaluating all the conditions first.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does coalesce terminate early?

[Alexey Pechnikov]

Richard, is the future documented anywhere?

2011/9/15 Richard Hipp :
> On Thu, Sep 15, 2011 at 8:08 AM, Igor Tandetnik  wrote:
>
>> Richard Hipp  wrote:
>> > On Wed, Sep 14, 2011 at 9:03 PM, Sam Carleton <
>> scarle...@miltonstreet.com>wrote:
>> >
>> >> Forgive me, fore I have forgotten the term used to describe the behavior
>> if
>> >> a C if statement where it stops executing on the first false statement,
>> >> but...  Does coalesce do that?
>> >>
>> >
>> > "Short-circuit evaluation" is the usual term applied to this kind of
>> thing,
>> > and yes, COALESCE() does short-circuit evaluation.  If you say
>> > "coalesce(A,B)" and A is not NULL than B is never evaluated, which can be
>> a
>> > significant performance win if, for example, B is a complex subquery.
>>
>> When did this start, with what SQLite version?
>
>
> 3.6.21 - December 2009
>
>
>> I must admit I'm somewhat behind (using 3.6.X for some X I don't recall at
>> the moment), but in the version I use, in expression coalesce(someField,
>> customFunction()) I definitely see customFunction() called even when
>> someField is not null.
>> --
>> Igor Tandetnik
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does coalesce terminate early?

[Sam Carleton]

On Thu, Sep 15, 2011 at 6:38 AM, Richard Hipp  wrote:

> On Wed, Sep 14, 2011 at 9:03 PM, Sam Carleton  >wrote:
>
> > Forgive me, fore I have forgotten the term used to describe the behavior
> if
> > a C if statement where it stops executing on the first false statement,
> > but...  Does coalesce do that?
> >
>
> "Short-circuit evaluation" is the usual term applied to this kind of thing,
> and yes, COALESCE() does short-circuit evaluation.  If you say
> "coalesce(A,B)" and A is not NULL than B is never evaluated, which can be a
> significant performance win if, for example, B is a complex subquery.
>

 Richard,

Thank you!  I did look on the web site to see if it said anything about
whether or not coalesce implemented short-circuit evaluations, but it did
not say one way or another.  How would I go about suggesting that change on
the web site?

Sam
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does coalesce terminate early?

[Richard Hipp]

On Thu, Sep 15, 2011 at 8:08 AM, Igor Tandetnik  wrote:

> Richard Hipp  wrote:
> > On Wed, Sep 14, 2011 at 9:03 PM, Sam Carleton <
> scarle...@miltonstreet.com>wrote:
> >
> >> Forgive me, fore I have forgotten the term used to describe the behavior
> if
> >> a C if statement where it stops executing on the first false statement,
> >> but...  Does coalesce do that?
> >>
> >
> > "Short-circuit evaluation" is the usual term applied to this kind of
> thing,
> > and yes, COALESCE() does short-circuit evaluation.  If you say
> > "coalesce(A,B)" and A is not NULL than B is never evaluated, which can be
> a
> > significant performance win if, for example, B is a complex subquery.
>
> When did this start, with what SQLite version?


3.6.21 - December 2009


> I must admit I'm somewhat behind (using 3.6.X for some X I don't recall at
> the moment), but in the version I use, in expression coalesce(someField,
> customFunction()) I definitely see customFunction() called even when
> someField is not null.
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does coalesce terminate early?

[Igor Tandetnik]

Richard Hipp  wrote:
> On Wed, Sep 14, 2011 at 9:03 PM, Sam Carleton 
> wrote:
> 
>> Forgive me, fore I have forgotten the term used to describe the behavior if
>> a C if statement where it stops executing on the first false statement,
>> but...  Does coalesce do that?
>> 
> 
> "Short-circuit evaluation" is the usual term applied to this kind of thing,
> and yes, COALESCE() does short-circuit evaluation.  If you say
> "coalesce(A,B)" and A is not NULL than B is never evaluated, which can be a
> significant performance win if, for example, B is a complex subquery.

When did this start, with what SQLite version? I must admit I'm somewhat behind 
(using 3.6.X for some X I don't recall at the moment), but in the version I 
use, in expression coalesce(someField, customFunction()) I definitely see 
customFunction() called even when someField is not null.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does coalesce terminate early?

[Richard Hipp]

On Wed, Sep 14, 2011 at 9:03 PM, Sam Carleton wrote:

> Forgive me, fore I have forgotten the term used to describe the behavior if
> a C if statement where it stops executing on the first false statement,
> but...  Does coalesce do that?
>

"Short-circuit evaluation" is the usual term applied to this kind of thing,
and yes, COALESCE() does short-circuit evaluation.  If you say
"coalesce(A,B)" and A is not NULL than B is never evaluated, which can be a
significant performance win if, for example, B is a complex subquery.


>
> I have to put together a query that has a coalesce such that if the row
> from
> the table is null, it then does a non-trival query to calculate the value
> for the row:  It is for an invoice total and will only be used while the
> user is building the invoice.  Once the invoice is finalized, the values
> will be placed into the invoice row.  The reason I need this logic is
> because the prices of invoice items can change on the fly, each time the
> user displays an invoice that is being built, it needs to show the current
> prices/totals, thus it looks like this:
>
> select
>  coalesce( sub_total, (select round(sum( (select pli.PRICE from
> PRICE_LIST_ITEM pli where pli.EVENT_PRICE_LIST_ID = (select ITEMVALUE from
> DBLOOKUP where CATEGORY = 'SalesCenter' and  ITEMNAME = 'PriceListId') AND
> pli.PRODUCT_ID = PRODUCT_ID) * QTY) from EventDB.INVOICE_ITEM  where
> INVOICE_ID = @invoiceId)) as sub_total,
>  coalesce( tax, (select round(sum( (select pli.PRICE from PRICE_LIST_ITEM
> pli where pli.EVENT_PRICE_LIST_ID = (select ITEMVALUE from DBLOOKUP where
> CATEGORY = 'SalesCenter' and  ITEMNAME = 'PriceListId') AND pli.PRODUCT_ID
> =
> PRODUCT_ID) * QTY) / (1 + (@tax / 100)) - .005, 2) * ( @tax / 100) + .005,
> 2) from EventDB.INVOICE_ITEM  where INVOICE_ID = @invoiceId)) as tax,
>  coalesce( total, (select round(sum( (select pli.PRICE from PRICE_LIST_ITEM
> pli where pli.EVENT_PRICE_LIST_ID = (select ITEMVALUE from DBLOOKUP where
> CATEGORY = 'SalesCenter' and  ITEMNAME = 'PriceListId') AND pli.PRODUCT_ID
> =
> PRODUCT_ID) * QTY) / (1 + (@tax / 100)) - .005, 2)  from
> EventDB.INVOICE_ITEM  where INVOICE_ID = @invoiceId)) as total
> where
>  INVOICE_ID = @invoiceId
>
> I tend to favor creating larger select statements and letting the DB do the
> work rather than adding lots of conditional code in my C code, so is this a
> good time to break it into two statements or should I be fine with the
> above
> code?
>
> Sam
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does coalesce terminate early?

[Igor Tandetnik]

On 9/14/2011 9:03 PM, Sam Carleton wrote:

Forgive me, fore I have forgotten the term used to describe the behavior if
a C if statement where it stops executing on the first false statement,
but...  Does coalesce do that?


The word you are looking for is "short-circuit", and no, unfortunately, 
coalesce doesn't do it in SQLite.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users