Using COUNT DISTINCT

[paulsk]

[PGNP32-1.2.8.1110 with .NET 2.0]

I am using the following SQL:

SELECT COUNT(DISTINCT GroupID) AS count FROM MyTable

but i am getting a System.NullReferenceException being thrown when the SQL is executed. I can't see what could be wrong with the SQL, especially since this same SQL is used successfully for Oracle and SqlServer.

[Moderator]

We were unable to reproduce the bug. We used the following code:

Code: Select all

dbcmd = new OleDbCommand("select count(distinct id) from testtbl", dbcon);
Console.WriteLine("Count: {0}", dbcmd.ExecuteScalar());

The returned value is Int64. Could you send the table definition and the fragment of code that executes the query?

[paulsk]

My code snippet is this.

Code: Select all

    System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection("db-connection-string")
    System.Data.OleDb.OleDbCommand stmt = con.CreateCommand();
    stmt.CommandText = "SELECT COUNT(DISTINCT GroupID) AS count FROM MyTable";
    System.Data.OleDb.OleDbDataReader rs = stmt.ExecuteReader();


I have also found the source of my problem , it is the

Code: Select all

AS count

part of the SQL. When this is removed I can use either ExecuteReader() or ExecuteScalar() okay.

It is worth noting that my SQL snippet is used okay for Oracle and Sqlserver with .NET, and also with Oracle, Sqlserver and Postgres with Java. Any thoughts as to why it will not work with your driver?

[Moderator]

There was a bug in <select_element> production rule definition. We have extended the rule to allow COUNT and some other words in the column alias.

We'll publish the fix in the next release of the provider. Please PM or e-mail us if you need the fix immediately.

Thank you for the good catch!

[paulsk]

I have worked around this issue so an immediate fix is not necessary. When will 1.3 be released?

[Moderator]

We are trying to determine the approximate release date. Most likely it will be middle of Summer 2009.