MySQL Bugs: #24764: MyOLEDB/excelVB/ADO: Count(*) and Year() Functions returning 0

Bug #24764	MyOLEDB/excelVB/ADO: Count(*) and Year() Functions returning 0 - BIGINT??
Submitted:	1 Dec 2006 22:01	Modified:	30 May 2013 6:52
Reporter:	alan weaver	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	3.5.12	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any
Tags:	MyOLEDB, VB(excel)/ADO

Description:
I hope somebody can help me.

I am experiencing a problem returning values back from mysql through the MyOLEDB Provider v3.0/ODBC 3.5.12 to Visual basic/ADO. 

When I run the same sql through the query browser it works fine. However when I run it through VB most columns return fine, however some functions e.g count(*) or Year() always return 0. 

When I check the returning ADO type it says 20 which I believe is bigint. I have read that ADO/Mysql doesnt work well with bigint, so I have selected "change bigint columns to int" on the ODBC administrator panel and used the Option in the connection clause but with no joy".

Any advice would be appreciated

Thanks

ALan

How to repeat:

For my setup, a simple select count(*) on a 2000 row table returns 0.
However a sum() works fine.

I have used a workaround to get round this. I have used the format(x,0) function to return the value as a string.

However it would be nice to know what I am doing wrong

Hi Alan and thanks for your problem report.
There is not enough data provided for me to tell what's happening. Please provide following:
  - MySQL server version
  - Small but complete test case showing this error
  - DDL + DML statements required to recreate your tables
  - General SQL log 

As for ADO type 20, you are right:
DataType Enum  	Value  	Access  	SQLServer
adBigInt 	20 	  	BigInt (SQL Server 2000 +) 	 

As you can see, Access (and for that part Excel) does not support this type...

Please make a test in mysql cl client:
  - start client with -T option: mysql -T -uUser -hHost DB
  - write the same query and observe the results
C:\mysql507\bin>mysql -T -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.27-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table bug24764 (ID BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, Ord
Int INT);
Query OK, 0 rows affected (0.06 sec)

mysql> insert into bug24764 (ID, OrdInt) values (NULL,1),(NULL,2);
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT SUM(ID) AS SBI, SUM(OrdInt) AS SOI FROM bug24764;
Field   1:  `SBI`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       NEWDECIMAL
Collation:  binary (63)
Length:     42
Max_length: 1
Decimals:   0
Flags:      BINARY

Field   2:  `SOI`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       NEWDECIMAL
Collation:  binary (63)
Length:     33
Max_length: 1
Decimals:   0
Flags:      BINARY

+------+------+
| SBI  | SOI  |
+------+------+
|    3 |    3 |
+------+------+
1 row in set (0.00 sec)

My appologies Alan, the problem was with COUNT.

mysql> SELECT COUNT(ID) AS CBI, COUNT(OrdInt) AS COI FROM bug24764;
Field   1:  `CBI`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     21
Max_length: 1
Decimals:   0
Flags:      NOT_NULL BINARY NUM

Field   2:  `COI`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     21
Max_length: 1
Decimals:   0
Flags:      NOT_NULL BINARY NUM

+-----+-----+
| CBI | COI |
+-----+-----+
|   2 |   2 |
+-----+-----+
1 row in set (0.00 sec)

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I'm closing this bug because I can not continue without feedback from the reporter. If you have new info, please reopen the report.