FreeTDS Development Group

[Cade Roux <cade AT roux.org>]

Just conjecture, but based on your symptoms, I expect this is due to
the "Warning: Null value is eliminated by an aggregate or other SET
operation" message that SQL Server emits which happens because every
aggregate operator (COUNT, SUM, MIN, MAX) ignores NULLs.  (COUNT(*) is
a special thing).

Not sure how to handle it in FreeTDS or what exactly the interactions
are in the tabular stream and messages, but just throwing that out
there.

If this is indeed the problem, I would expect a workaround in the SQL
using a CASE statement to eliminate the need for any warning will get
you past this for now:

SELECT SUM(CASE WHEN MiddleName IS NOT NULL THEN 1 ELSE 0 END) FROM Person

and

SELECT SUM((CASE WHEN o0_.orderItemId IS NOT NULL THEN 1 ELSE 0 END) AS sclr0
FROM Membership m1_
LEFT JOIN OrderItem o0_ ON m1_.membershipId = o0_.membershipId
GROUP BY m1_.membershipId

Cade
Cade Roux
cade AT roux.org
504-717-4887


On Wed, Dec 19, 2012 at 1:13 PM, Jeremy Livingston
<jeremyjlivingston AT gmail.com> wrote:
> Also, I have discovered that the LEFT JOIN and GROUP BY clauses aren't
> necessary to reproduce this result. I can trigger the error simply by
> running a COUNT() on a column that contains all null values.
>
> For example: "SELECT COUNT(MiddleName) FROM Person" where MiddleName
> contains all null values will throw the error.
>
> Thanks again for your help.
>
>
> On Wed, Dec 19, 2012 at 1:38 PM, Jeremy Livingston <
> jeremyjlivingston AT gmail.com> wrote:
>
>> Thank you for the reply.
>>
>> Pardon my ignorance, but I'm not sure how to specify that driver that I'd
>> like to use using bsqlodbc. I get the following error when I attempt my
>> query:
>>
>> "[unixODBC][Driver Manager]Data source name not found, and no default
>> driver specified"
>>
>> Do you have any idea how I can tell it to use a specific driver in
>> odbcinst.ini?
>>
>> Thanks for the help!
>>
>>
>> On Wed, Dec 19, 2012 at 1:21 PM, James K. Lowden 
>> <jklowden AT freetds.org>wrote:
>>
>>> On Wed, 19 Dec 2012 09:49:46 -0500
>>> Jeremy Livingston <jeremyjlivingston AT gmail.com> wrote:
>>>
>>> > I am using FreeTDS v0.91 with unixODBC, PHP 5.3.10, and SQL Server
>>> > 2008. I have my connection to use the 7.2 protocol. I am attempting
>>> > to run the following query:
>>> >
>>> > SELECT COUNT(o0_.orderItemId) AS sclr0
>>> > FROM Membership m1_
>>> > LEFT JOIN OrderItem o0_ ON m1_.membershipId = o0_.membershipId
>>> > GROUP BY m1_.membershipId
>>> >
>>> > This query only throws an error when there is a Membership that has no
>>> > records in the OrderItem table.
>>>
>>> Does your query work with bsqslodbc?  I tried something similar with no
>>> problem:
>>>
>>> $ bsqlodbc -S$S -U$U -P$P <<< 'select count
>>> (c.type) from systypes t left join syscolumns c on t.type = c.type
>>> group by t.type'
>>> ----------
>>>          0
>>>          6
>>>         16
>>>        122
>>>        640
>>>         12
>>>         54
>>>         57
>>>          5
>>>         28
>>>          2
>>>        196
>>>          3
>>>          8
>>>          2
>>>         26
>>>          2
>>>         72
>>>          2
>>>
>>> --jkl
>>> _______________________________________________
>>> FreeTDS mailing list
>>> FreeTDS AT lists.ibiblio.org
>>> http://lists.ibiblio.org/mailman/listinfo/freetds
>>>
>>
>>
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds