[freetds] Using GROUP BY and COUNT

[Jeremy Livingston]

I am having trouble running a query that uses a left join, group by, and
count on the table that is left joined.

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. If I run this query in SQL Server itself,
it runs with no issues and returns 0 for the Membership that has no
OrderItems.

When I try to run this query from FreeTDS, I receive this error:

SQLSTATE[24000]: Invalid cursor state: 0 [FreeTDS][SQL Server]Invalid
cursor state (SQLFetchScroll[0] at
/builddir/build/BUILD/php-5.3.10/ext/pdo_odbc/odbc_stmt.c:537)

Does anyone know why I would receive this error? I remember encountering a
similar error when I was attempting to loop through two result sets at
once, but this is a single query.

This error only happens when I use the COUNT() function on the joined table
and there is a case where there aren't any records in the joined table.
This query works from FreeTDS when all Memberships have at least one record
in OrderItem.

Does anyone know what could be causing this issue or how I could resolve it?

Thank you!

[James K. Lowden]

On Wed, 19 Dec 2012 09:49:46 -0500

Post by Jeremy Livingston
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
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

[Jeremy Livingston]

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!

Post by James K. Lowden
On Wed, 19 Dec 2012 09:49:46 -0500

Post by Jeremy Livingston
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
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
$ 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

[Jeremy Livingston]

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 <

Post by Jeremy Livingston
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
"[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!

Post by James K. Lowden
On Wed, 19 Dec 2012 09:49:46 -0500

Post by Jeremy Livingston
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
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
$ 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

[Cade Roux]

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

Post by Jeremy Livingston
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 <

Post by Jeremy Livingston
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
"[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!

Post by James K. Lowden
On Wed, 19 Dec 2012 09:49:46 -0500

Post by Jeremy Livingston
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
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
$ 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

[James K. Lowden]

On Wed, 19 Dec 2012 13:38:06 -0500

Post by Jeremy Livingston
Pardon my ignorance, but I'm not sure how to specify that driver that
I'd like to use using bsqlodbc.

Look again at my example. :-) It's not a driver I'm using. bsqlodbc
is a FreeTDS command-line utility; it's found in src/apps and should
have been installed somewhere like /usr/local/bin when you installed
FreeTDS.

The point is to determine if the problem you're seeing is in the
FreeTDS ODBC driver (which the utility uses), or in the PHP module, or
in your application. I think you'll find bsqlodbc runs your query just
fine, which suggests the problem in not in the driver.

Post by Jeremy Livingston
For example: "SELECT COUNT(MiddleName) FROM Person"
where MiddleName contains all null values will throw the error.

You might try these, too:

$ echo 'select count(t) from (select cast(NULL as int) as t) as T' \
| bsqlodbc -U$U -P$P -S$S
----------
0
bsqlodbc: error 8153: 01003: [FreeTDS][SQL Server]Warning: Null value
is eliminated by an aggregate or other SET operation.

If I stack a guess on a few assumptions, the most likely problem is
that the error message isn't handled correctly. For example, if PHP
stops processing the resultset when the message arrives, and then
continues to use the connection without fetching or cancelling the
pending results, you would see an invalid-cursor-state error on the
next query.

HTH.

--jkl

[Jeremy Livingston]

The only reason that I asked about the driver is because that is the error
that I am receiving when I try to run my query with bsqlodbc:

bsqlodbc -S {server} -U {user} -P {pass} -D {db} <<< 'select count
(productCode) from Membership'

bsqlodbc: error -1: SQLConnect: SQL_ERROR: failed "[unixODBC][Driver
Manager]Data source name not found, and no default driver specified"

I'm not sure if there's configuration that I'm missing or what...but when I
connect with a DSN from the same server via the web, I don't have this
issue.

Post by James K. Lowden
On Wed, 19 Dec 2012 13:38:06 -0500

Post by Jeremy Livingston
Pardon my ignorance, but I'm not sure how to specify that driver that
I'd like to use using bsqlodbc.

Look again at my example. :-) It's not a driver I'm using. bsqlodbc
is a FreeTDS command-line utility; it's found in src/apps and should
have been installed somewhere like /usr/local/bin when you installed
FreeTDS.
The point is to determine if the problem you're seeing is in the
FreeTDS ODBC driver (which the utility uses), or in the PHP module, or
in your application. I think you'll find bsqlodbc runs your query just
fine, which suggests the problem in not in the driver.

Post by Jeremy Livingston
For example: "SELECT COUNT(MiddleName) FROM Person"
where MiddleName contains all null values will throw the error.

$ echo 'select count(t) from (select cast(NULL as int) as t) as T' \
| bsqlodbc -U$U -P$P -S$S
----------
0
bsqlodbc: error 8153: 01003: [FreeTDS][SQL Server]Warning: Null value
is eliminated by an aggregate or other SET operation.
If I stack a guess on a few assumptions, the most likely problem is
that the error message isn't handled correctly. For example, if PHP
stops processing the resultset when the message arrives, and then
continues to use the connection without fetching or cancelling the
pending results, you would see an invalid-cursor-state error on the
next query.
HTH.
--jkl
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds