SUM() curiosity
Fernando Nunes asked why `select sum(0) from sysmaster:sysdual where 1=2` returns one row containing NULL rather than no rows at all. Replies explained that an aggregate without GROUP BY always produces exactly one result row, and summing over zero qualifying rows yields NULL (a NULL is still a row, unlike no rows); the behaviour matches other RDBMSs and apparently follows the SQL standard, though no one pinned down the exact clause. The practical fix, which solved his shell script's parsing problem, was to wrap the aggregate in NVL(), e.g. nvl(sum(0),0).
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
select sum(0) from sysmaster:sysdual where 1 = 2; This returns and empty (NULL) row instead of no rows at all. It's not a bug. Other RDBMS act the same way. But I'm struggling to understand the reason(s) for this (and I can't really explain it to a customer). Any suggestion? Regards. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --0016e6542484f4310f04b2e1facb
Summing a constant effectively joins the constant like a table to each row of data returned from the query. Since SELECT * FROM any_table WHERE 1 = 2 always returns no rows, SUM has no data to sum on, and therefor returns a null. Anyone disagree? --EEM >-----Original Message----- >From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of >Fernando Nunes >Sent: Tuesday, November 29, 2011 10:19 AM >To: ids@iiug.org >Subject: SUM() curiosity [25502] > >select sum(0) from sysmaster:sysdual where 1 = 2; This returns and empty >(NULL) row instead of no rows at all. >It's not a bug. Other RDBMS act the same way. > >But I'm struggling to understand the reason(s) for this (and I can't >really explain it to a customer). >Any suggestion? > >Regards. > >-- >Fernando Nunes >Portugal > >http://informix-technology.blogspot.com >My email works... but I don't check it frequently... > >--0016e6542484f4310f04b2e1facb > > >************************************************************************ >******* > Forum Note: Use "Reply" to post a response in the discussion forum.
It's not that I disagree... Just two remarks: 1- Being a constant is irrelevant 2-"SUM has no data to sum on, and therefore returns a null".... This is precisely the point. It could return nothing :) Regards On Tue, Nov 29, 2011 at 4:57 PM, Everett Mills < Everett.Mills@nationalbeef.com> wrote: > Summing a constant effectively joins the constant like a table to each row > of > data returned from the query. Since SELECT * FROM any_table WHERE 1 = 2 > always > returns no rows, SUM has no data to sum on, and therefor returns a null. > > Anyone disagree? > > --EEM > > >-----Original Message----- > >From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > >Fernando Nunes > >Sent: Tuesday, November 29, 2011 10:19 AM > >To: ids@iiug.org > >Subject: SUM() curiosity [25502] > > > >select sum(0) from sysmaster:sysdual where 1 = 2; This returns and empty > >(NULL) row instead of no rows at all. > >It's not a bug. Other RDBMS act the same way. > > > >But I'm struggling to understand the reason(s) for this (and I can't > >really explain it to a customer). > >Any suggestion? > > > >Regards. > > > >-- > >Fernando Nunes > >Portugal > > > >http://informix-technology.blogspot.com > >My email works... but I don't check it frequently... > > > >--0016e6542484f4310f04b2e1facb > > > > > >************************************************************************ > >******* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --20cf3010ed8374e58404b2e2b436
Hi Fernando, I believe the reason for one row is that the query asks for a sum. That's different from asking for rows from the table. The null value is returned since the sum of nulls (because no rows qualify) is null. If you remove the predicate, you'll get the value of zero rather than the null value. Cheers, Dick Snoke Executive IT Specialist IBM - Channel Works (404) 487-1595 dsnoke@us.ibm.com From: "Fernando Nunes" <domusonline@gmail.com> To: ids@iiug.org Date: 11/29/11 11:20 AM Subject: SUM() curiosity [25502] Sent by: ids-bounces@iiug.org select sum(0) from sysmaster:sysdual where 1 = 2; This returns and empty (NULL) row instead of no rows at all. It's not a bug. Other RDBMS act the same way. But I'm struggling to understand the reason(s) for this (and I can't really explain it to a customer). Any suggestion? Regards. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --0016e6542484f4310f04b2e1facb ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
I my understand it must return at least one row to sum and return the value you wish "0" (zero). Actually it's not summing anything, as it doesn't return any rows to sum. Again, it's my opinion. You shoud use the query instead. select nvl(sum(0),0) from sysmaster:sysdual where 1 = 2 Celso Cabral Coimbra Administrador de Banco de Dados ClearTech Ltda "Trust at the heart of Communications" Tel. (11) 3576-4509 -----Mensagem original----- De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Em nome de Fernando Nunes Enviada em: terça-feira, 29 de novembro de 2011 15:11 Para: ids@iiug.org Assunto: Re: SUM() curiosity [25504] It's not that I disagree... Just two remarks: 1- Being a constant is irrelevant 2-"SUM has no data to sum on, and therefore returns a null".... This is precisely the point. It could return nothing :) Regards On Tue, Nov 29, 2011 at 4:57 PM, Everett Mills < Everett.Mills@nationalbeef.com> wrote: > Summing a constant effectively joins the constant like a table to each row > of > data returned from the query. Since SELECT * FROM any_table WHERE 1 = 2 > always > returns no rows, SUM has no data to sum on, and therefor returns a null. > > Anyone disagree? > > --EEM > > >-----Original Message----- > >From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > >Fernando Nunes > >Sent: Tuesday, November 29, 2011 10:19 AM > >To: ids@iiug.org > >Subject: SUM() curiosity [25502] > > > >select sum(0) from sysmaster:sysdual where 1 = 2; This returns and empty > >(NULL) row instead of no rows at all. > >It's not a bug. Other RDBMS act the same way. > > > >But I'm struggling to understand the reason(s) for this (and I can't > >really explain it to a customer). > >Any suggestion? > > > >Regards. > > > >-- > >Fernando Nunes > >Portugal > > > >http://informix-technology.blogspot.com > >My email works... but I don't check it frequently... > > > >--0016e6542484f4310f04b2e1facb > > > > > >************************************************************************ > >******* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --20cf3010ed8374e58404b2e2b436 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
But what's the difference between returning nothing and returning the null value? Dick Snoke Executive IT Specialist IBM - Channel Works (404) 487-1595 dsnoke@us.ibm.com From: "Fernando Nunes" <domusonline@gmail.com> To: ids@iiug.org Date: 11/29/11 12:20 PM Subject: Re: SUM() curiosity [25504] Sent by: ids-bounces@iiug.org It's not that I disagree... Just two remarks: 1- Being a constant is irrelevant 2-"SUM has no data to sum on, and therefore returns a null".... This is precisely the point. It could return nothing :) Regards On Tue, Nov 29, 2011 at 4:57 PM, Everett Mills < Everett.Mills@nationalbeef.com> wrote: > Summing a constant effectively joins the constant like a table to each row > of > data returned from the query. Since SELECT * FROM any_table WHERE 1 = 2 > always > returns no rows, SUM has no data to sum on, and therefor returns a null. > > Anyone disagree? > > --EEM > > >-----Original Message----- > >From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > >Fernando Nunes > >Sent: Tuesday, November 29, 2011 10:19 AM > >To: ids@iiug.org > >Subject: SUM() curiosity [25502] > > > >select sum(0) from sysmaster:sysdual where 1 = 2; This returns and empty > >(NULL) row instead of no rows at all. > >It's not a bug. Other RDBMS act the same way. > > > >But I'm struggling to understand the reason(s) for this (and I can't > >really explain it to a customer). > >Any suggestion? > > > >Regards. > > > >-- > >Fernando Nunes > >Portugal > > > >http://informix-technology.blogspot.com > >My email works... but I don't check it frequently... > > > >--0016e6542484f4310f04b2e1facb > > > > > >************************************************************************ > >******* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --20cf3010ed8374e58404b2e2b436 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
The real query was inside a SHELL script that tried to parse the result. It was able to handle "no rows", but had problems handling an "empty" row. It's fixed and for the purpose a NVL around the SUM seems to be a good solution. But I've found the situation interesting and the customer had some troubles understanding why the engine behaves like this. I suppose this is another situation where it does the correct thing but the right thing causes confusion. Other examples of situations like that are: - CURRENT inside a procedure is always the same - String concatenation with null returns null - MDY(3,31,2011) - 1 UNITS MONTH raises error - etc....? Regards On Tue, Nov 29, 2011 at 6:07 PM, Richard Snoke <dsnoke@us.ibm.com> wrote: > But what's the difference between returning nothing and returning the null > value? > > Dick Snoke > Executive IT Specialist > IBM - Channel Works > (404) 487-1595 > dsnoke@us.ibm.com > > From: "Fernando Nunes" <domusonline@gmail.com> > To: ids@iiug.org > Date: 11/29/11 12:20 PM > Subject: Re: SUM() curiosity [25504] > Sent by: ids-bounces@iiug.org > > It's not that I disagree... Just two remarks: > 1- Being a constant is irrelevant > 2-"SUM has no data to sum on, and therefore returns a null".... This is > precisely the point. It could return nothing :) > > Regards > > On Tue, Nov 29, 2011 at 4:57 PM, Everett Mills < > Everett.Mills@nationalbeef.com> wrote: > > > Summing a constant effectively joins the constant like a table to each > row > > of > > data returned from the query. Since SELECT * FROM any_table WHERE 1 = 2 > > always > > returns no rows, SUM has no data to sum on, and therefor returns a null. > > > > > Anyone disagree? > > > > --EEM > > > > >-----Original Message----- > > >From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > > >Fernando Nunes > > >Sent: Tuesday, November 29, 2011 10:19 AM > > >To: ids@iiug.org > > >Subject: SUM() curiosity [25502] > > > > > >select sum(0) from sysmaster:sysdual where 1 = 2; This returns and > empty > > >(NULL) row instead of no rows at all. > > >It's not a bug. Other RDBMS act the same way. > > > > > >But I'm struggling to understand the reason(s) for this (and I can't > > >really explain it to a customer). > > >Any suggestion? > > > > > >Regards. > > > > > >-- > > >Fernando Nunes > > >Portugal > > > > > >http://informix-technology.blogspot.com > > >My email works... but I don't check it frequently... > > > > > >--0016e6542484f4310f04b2e1facb > > > > > > > > > >************************************************************************ > > >******* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --20cf3010ed8374e58404b2e2b436 > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --0016e653854a6ffe6704b2e3e670
A null is still a row, nothing is nothing. Art On Nov 29, 2011 11:07 AM, "Richard Snoke" <dsnoke@us.ibm.com> wrote: > But what's the difference between returning nothing and returning the null > value? > > Dick Snoke > Executive IT Specialist > IBM - Channel Works > (404) 487-1595 > dsnoke@us.ibm.com > > From: "Fernando Nunes" <domusonline@gmail.com> > To: ids@iiug.org > Date: 11/29/11 12:20 PM > Subject: Re: SUM() curiosity [25504] > Sent by: ids-bounces@iiug.org > > It's not that I disagree... Just two remarks: > 1- Being a constant is irrelevant > 2-"SUM has no data to sum on, and therefore returns a null".... This is > precisely the point. It could return nothing :) > > Regards > > On Tue, Nov 29, 2011 at 4:57 PM, Everett Mills < > Everett.Mills@nationalbeef.com> wrote: > > > Summing a constant effectively joins the constant like a table to each > row > > of > > data returned from the query. Since SELECT * FROM any_table WHERE 1 = 2 > > always > > returns no rows, SUM has no data to sum on, and therefor returns a null. > > > > > Anyone disagree? > > > > --EEM > > > > >-----Original Message----- > > >From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > > >Fernando Nunes > > >Sent: Tuesday, November 29, 2011 10:19 AM > > >To: ids@iiug.org > > >Subject: SUM() curiosity [25502] > > > > > >select sum(0) from sysmaster:sysdual where 1 = 2; This returns and > empty > > >(NULL) row instead of no rows at all. > > >It's not a bug. Other RDBMS act the same way. > > > > > >But I'm struggling to understand the reason(s) for this (and I can't > > >really explain it to a customer). > > >Any suggestion? > > > > > >Regards. > > > > > >-- > > >Fernando Nunes > > >Portugal > > > > > >http://informix-technology.blogspot.com > > >My email works... but I don't check it frequently... > > > > > >--0016e6542484f4310f04b2e1facb > > > > > > > > > >************************************************************************ > > >******* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --20cf3010ed8374e58404b2e2b436 > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec52e601f0ec24a04b2e78533
On 29/11/2011 18:36, Fernando Nunes wrote: > The real query was inside a SHELL script that tried to parse the result. It > was able to handle "no rows", but had problems handling an "empty" row. > It's fixed and for the purpose a NVL around the SUM seems to be a good > solution. But I've found the situation interesting and the customer had > some troubles understanding why the engine behaves like this. > I suppose this is another situation where it does the correct thing but the > right thing causes confusion. Other examples of situations like that are: > > - CURRENT inside a procedure is always the same > - String concatenation with null returns null > - MDY(3,31,2011) - 1 UNITS MONTH raises error > - etc....? Surely the answer is - because that's what the sql standard says they should do? Or have I missed something? -- Clive
For this situation (sum) I suppose so, since at least Informix, mySQL and Oracle behave the same way. But I couldn't find the explanation. For the other situations I mentioned, I think I was able to find the part of the SQL standard that justifies the behavior. Regards On Wed, Nov 30, 2011 at 10:54 PM, Clive Eisen <clive@serendipita.com> wrote: > On 29/11/2011 18:36, Fernando Nunes wrote: > > The real query was inside a SHELL script that tried to parse the result. > It > > was able to handle "no rows", but had problems handling an "empty" row. > > It's fixed and for the purpose a NVL around the SUM seems to be a good > > solution. But I've found the situation interesting and the customer had > > some troubles understanding why the engine behaves like this. > > I suppose this is another situation where it does the correct thing but > the > > right thing causes confusion. Other examples of situations like that are: > > > > - CURRENT inside a procedure is always the same > > - String concatenation with null returns null > > - MDY(3,31,2011) - 1 UNITS MONTH raises error > > - etc....? > > Surely the answer is - because that's what the sql standard says they > should do? > > Or have I missed something? > > -- > Clive > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001636920cf05198bd04b2fc87a7
Researching about SQL-92 , I don't found any clear (if exists...) for me... http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt I found something what give some clue or at least some direction... Some rules over "value expression" on "Scalar expressions" section what on my interpretation of the text include the aggregate functions... (<set function specification>) Item 2 .... " General Rules 1) When a <value expression> V is evaluated for a row of a table, each reference to a column of that table by a <column reference> directly contained in V is a reference to the value of that column in that row. 2) If a <value expression primary> is a <scalar subquery> and the result of the <subquery> is empty, then the result of the <value expression primary> is the null value. " On 30/11/2011 21:59, Fernando Nunes wrote: > For this situation (sum) I suppose so, since at least Informix, mySQL and > Oracle behave the same way. But I couldn't find the explanation. > For the other situations I mentioned, I think I was able to find the part > of the SQL standard that justifies the behavior. > Regards > > On Wed, Nov 30, 2011 at 10:54 PM, Clive Eisen<clive@serendipita.com> wrote: > >> On 29/11/2011 18:36, Fernando Nunes wrote: >>> The real query was inside a SHELL script that tried to parse the result. >> It >>> was able to handle "no rows", but had problems handling an "empty" row. >>> It's fixed and for the purpose a NVL around the SUM seems to be a good >>> solution. But I've found the situation interesting and the customer had >>> some troubles understanding why the engine behaves like this. >>> I suppose this is another situation where it does the correct thing but >> the >>> right thing causes confusion. Other examples of situations like that are: >>> >>> - CURRENT inside a procedure is always the same >>> - String concatenation with null returns null >>> - MDY(3,31,2011) - 1 UNITS MONTH raises error >>> - etc....? >> Surely the answer is - because that's what the sql standard says they >> should do? >> >> Or have I missed something? >> >> -- >> Clive >> >> >> >> > ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >>