MySQL Bugs: #32303: Sum has SQL error with any Where clause, and returns no records with no Where

Bug #32303	Sum has SQL error with any Where clause, and returns no records with no Where
Submitted:	13 Nov 2007 2:39	Modified:	29 May 2013 11:18
Reporter:	Guy Chamberlain	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.1	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	fail, ODBC, sum, where

Description:
Select sum(LocPremium) as LocTotal from buildersrisklocation Where AgencyCode = '2001001' And Quote = '100001' causes:Err#=0x80040E14 - [MySQL][ODBC 5.1 Driver][mysqld-5.0.45-community-nt]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2001001' And Quote = '100001' at line 1 on line=63.  
Works fine in Query Builder but not from ASP page with ODBC.  
Removing the Where clause then returns EOF when there are 3 existing records.  Should return Null if there were no records, but there are 3 for it to sum.

How to repeat:
CREATE TABLE  `mgalive`.`buildersrisklocation` (
  `AgencyCode` varchar(10) NOT NULL,
  `Quote` varchar(10) NOT NULL,
  `LocNum` tinyint(3) unsigned NOT NULL,
  `LocPremium` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`AgencyCode`,`Quote`,`LocNum`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

VB code SQL statement=Select sum(LocPremium) as LocTotal from buildersrisklocation Where AgencyCode = '2001001' And Quote = '100001'

Connection string="DRIVER={MySQL ODBC 5.1 Driver}; DATABASE=MGALive; Persist Security Info=False; OPTION=35"

Recordset opened with adOpenForwardOnly, adLockOptimistic

Hi Guy and thanks for your report. This does not seem to be the problem with MyODBC but rather server settings as I can not repeat it in MS generic ODBC client. Can you please check the SQL_MODE server is in regarding ANSI_QUOTES and such and repeat the test with other quotes than '?

Select @@global.sql_mode; gives: 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
Session is the same.  I am using the single quotes ' for all other SQL statements with no problem in all Select statements including using the function Count(*).  This is the first and only time that I've used Sum() so far.
Changing the quotes to Select sum(LocPremium) as LocTotal from buildersrisklocation Where AgencyCode = "2001001" And Quote = "100001" does not give me an SQL error now but does return eof when there is data to be retrieved.

Guy, we need to check two things now:
 1) General query log from server (start server with --log or add log to [mysqld] in my.ini) to see what reaches the server
and
 2) ODBC DM trace (Control panel/Administrative tools/Data Sources(ODBC)/ Tracing tab/Start tracing now) to see what was sent to MyODBC and how it was passed to server

Please attach both logs at the time of error.

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 am not able to reproduce this on my work machine but there is a problem in processing several aggregates in same call but that's posted as separate bug report (Bug#26097).