Skip to Content.
Sympa Menu

freetds - Re: [freetds] Set a warning on null values.

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "James K. Lowden" <jklowden AT freetds.org>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] Set a warning on null values.
  • Date: Sat, 23 Jan 2010 10:05:29 -0500

Matt Piskorz wrote:
> I'm having an issue with a query where I am getting back an extra
> record.

I doubt that. I don't know what you're seeing because you described it
rather than showing it, but the server surely returned only one row.
That's what the SQL max() function requires of it.

> I receive this warning when I run the query in the ms sql client tool -
> "Warning: Null value is eliminated by an aggregate or other SET
> operation."

That's a message from the server. If you run your query in tsql, you'll
see the same message. The unixODBC tool often doesn't print error/warning
messages. (Whether or not the server produces the message is controlled
by the ANSI_WARNINGS session variable. Is is on by default for ODBC
connections.)

> If I run the query as is in unixodbc isql, then I get the 4th row.

I suspect a problem with the tool. FreeTDS includes a variety of
utilities. If any of them produces a "4th row", please do show.

I ran the following tests. I got a variety of outcomes, some of which
need attention, but none of which evince an extra row. (fisql prints no
message; bsqlodbc is fussy about what's in a batch.)

$ cat row4.pr.sql; for A in tsql bsqldb fisql bsqlodbc; do printf "\n#\n#
$A\n#\n"; $A -S $S -U $U -P $P < row4.pr.sql; done

create table test(c1 int null, c2 int null)
insert into test values( 1,1 )
insert into test values( 1,2 )
insert into test values( 1,null )
go

set ANSI_WARNINGS ON
select max(case when c1 = 1 then c2 end) as 'case 1'
from test --this returns 2 rows
go
select max(case when c1 = 1 then c2 end) as 'case 2'
from test where c2 is not null -- this returns 1 row
go

drop table test
go

#
# tsql
#
locale is "C"
locale charset is "646"
Msg 8153, Level 0, State 1, Server TITAN, Line 3
Warning: Null value is eliminated by an aggregate or other SET operation.
locale is "C"
locale charset is "646"
1> 2> 3> 4> 5> 1> 2> 3> 4> 5> case 1
2
(1 row affected)
1> 2> 3> case 2
2
(1 row affected)
1> 2> 3> 1>
#
# bsqldb
#
@@rowcount not available
@@rowcount not available
case 1
-----------
Msg 8153, Level 0, State 1
Server 'TITAN', Line 3
Warning: Null value is eliminated by an aggregate or other SET
operation




Archive powered by MHonArc 2.6.24.

Top of Page