Does coalesce terminate early?

classic Classic list List threaded Threaded
15 messages Options
Reply | Threaded
Open this post in threaded view
|

Does coalesce terminate early?

Sam Carleton-2
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?

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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Does coalesce terminate early?

Richard Hipp-3
In reply to this post by Sam Carleton-2
On Wed, Sep 14, 2011 at 9:03 PM, Sam Carleton <[hidden email]>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
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Does coalesce terminate early?

Igor Tandetnik
Richard Hipp <[hidden email]> wrote:

> On Wed, Sep 14, 2011 at 9:03 PM, Sam Carleton <[hidden email]>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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Does coalesce terminate early?

Richard Hipp-3
On Thu, Sep 15, 2011 at 8:08 AM, Igor Tandetnik <[hidden email]> wrote:

> Richard Hipp <[hidden email]> wrote:
> > On Wed, Sep 14, 2011 at 9:03 PM, Sam Carleton <
> [hidden email]>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
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Does coalesce terminate early?

Sam Carleton-2
In reply to this post by Richard Hipp-3
On Thu, Sep 15, 2011 at 6:38 AM, Richard Hipp <[hidden email]> wrote:

> On Wed, Sep 14, 2011 at 9:03 PM, Sam Carleton <[hidden email]
> >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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Does coalesce terminate early?

Alexey Pechnikov-2
In reply to this post by Richard Hipp-3
Richard, is the future documented anywhere?

2011/9/15 Richard Hipp <[hidden email]>:

> On Thu, Sep 15, 2011 at 8:08 AM, Igor Tandetnik <[hidden email]> wrote:
>
>> Richard Hipp <[hidden email]> wrote:
>> > On Wed, Sep 14, 2011 at 9:03 PM, Sam Carleton <
>> [hidden email]>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
>> [hidden email]
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



--
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Does coalesce terminate early?

Simon Slavin-3

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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Does coalesce terminate early?

Sam Carleton-2
On Thu, Sep 15, 2011 at 10:05 AM, Simon Slavin <[hidden email]> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Does coalesce terminate early?

Puneet Kishor-2

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

> On Thu, Sep 15, 2011 at 10:05 AM, Simon Slavin <[hidden email]> 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
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Does coalesce terminate early?

Simon Slavin-3
In reply to this post by Sam Carleton-2

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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Does coalesce terminate early?

Sam Carleton-2
On Thu, Sep 15, 2011 at 12:24 PM, Simon Slavin <[hidden email]> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Does coalesce terminate early?

Jay Kreibich
In reply to this post by Puneet Kishor-2
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Does coalesce terminate early?

Tim Streater-3
In reply to this post by Sam Carleton-2
On 15 Sep 2011 at 18:21, Jay A. Kreibich <[hidden email]> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Does coalesce terminate early?

Jan Hudec
In reply to this post by Sam Carleton-2
On Thu, Sep 15, 2011 at 18:58:00 +0100, Tim Streater wrote:
> On 15 Sep 2011 at 18:21, Jay A. Kreibich <[hidden email]> 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 <[hidden email]>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users