Re: isNumeric

In SQL, what's being aggregated is statically typed and may involve 
implicit conversion.

    SQL standard requires nulls to be skipped in SUM.
    MySQL skips errors just like NULLs.
    PostgreSQL skips nulls and expression errors on sum(val+5)
    PostgreSQL causes an error on casting error.
    Addition with + and nulls does not work as sum over nulls.


3/ SQL92 defines sum in language, the core of which seems to be:

[[
If SUM is specified, then the result is the sum of the
                  values in TXA.
]]

[1]
http://lists.w3.org/Archives/Public/public-rdf-dawg/2009OctDec/0433.html

 Andy

On 27/09/10 13:43, Polleres, Axel wrote:
> How does SQL deal with that?
>
> Axel
>
> ----- Original Message -----
> From: Steve Harris<steve.harris@garlik.com>
> To: Polleres, Axel
> Cc: Andy Seaborne<andy.seaborne@epimorphics.com>; SPARQL Working Group<public-rdf-dawg@w3.org>
> Sent: Mon Sep 27 12:43:58 2010
> Subject: Re: isNumeric
>
> On 2010-09-20, at 09:55, Axel Polleres wrote:
>
>> On 20 Sep 2010, at 10:26, Andy Seaborne wrote:
>>> On 19/09/10 21:13, Axel Polleres wrote:
>>>> Hi all,
>>>>
>>>> Don't we need something like a function isNumeric to test for a numeric argument?
>>>> Seems to be handy, useful for instance for numeric aggregates, or no?
>>>>
>>>> It seems COALESCE together with a cast would work as well, but
>>>> something like
>>>>    SUM(IF(isNumeric(?X), ?X, 0))
>>>> looks better - at least to me - than:
>>>>      SUM(COALESCE(xs:double(?X) , 0))
>>>>
>>>> Opinions?
>>>>
>>>> Axel
>>>
>>> I believe we eventually agreed that sum() would skip any evaluation
>>> errors of the summation as being more consistent in style for SPARQL.
>>
>> Hmmm, I read up in the F2F minutes
>> http://www.w3.org/2009/sparql/meeting/2010-03-26#SUM
>> and couldn't find it there... according to the current definition SUM just delegates to op:numeric-add
>> which would error on non-numeric values, wouldn't it?
>>
>> Note that Resolution http://www.w3.org/2009/sparql/meeting/2010-03-26#resolution_2
>> doesn't cover that, since the argument passed is not an error, but just
>> the wrong datatype.
>>
>> We might have that resolved otherwise somewhere, but frankly I can't find that.
>
> My recollection matches Axels, but I don't have a clear memory of the discussions. I would expect to have to do a cast and/or coalesce to protect from type errors.
>
> - Steve
>

Received on Monday, 27 September 2010 14:05:39 UTC