MySQL Bugs: #84399: SQLExecute fails with Date overflow error

Bug #84399	SQLExecute fails with Date overflow error
Submitted:	4 Jan 2017 5:56	Modified:	24 May 2017 18:16
Reporter:	kriti suwalka	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	5.03.07.00	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
SQLExecute() fails when query has comparison operators(>=,<=,<,>) on date field with "Date overflow" error. It used to work with driver version 3.51.27.

How to repeat:
Attaching a sample code to reproduce the issue.

Hi Kriti,

Thanks for your interest in MySQL ODBC driver and for providing the test case to help us with identifying the problem.

Unfortunately, there are few serious issues in the test case that could result in undefined behavior:

- The connection string requests the ANSI version of the driver to be used
"MySQL ODBC 5.3 ANSI Driver", but the L"" strings are used everywhere
and Wide-char non-ANSI functions (SQLDriverConnectW(), SQLPrepareW())
In most cases the ODBC driver manager should do the conversion from
the application Unicode strings to ANSI and back, but that
is not guaranteed. Your program should use the
"MySQL ODBC 5.3 Unicode Driver".

- The following line in your test case does not make any sense

SQLDATE c1 = 01-01-2013;

Since the program is C++ it is computed as a numeric expression
1 minus 1 minus 2013, which is -2013. This negative number cannot
represent any meaningful date. Also, SQLDATE is defined as
unsigned char type, which can take values from 0 to 255.
(You can check SQLDATE definition in the standard sqltypes.h
header file).
It is not supposed to be negative.

So, we have there the negative number assigned to an unsigned variable.
Even if the value was positive it would cause overflow because 2013
is bigger than the maximum 255 allowed for unsigned char.

If we ignore the sign problem and the overflow the resulting value
will be 35, which also cannot be considered a valid date.

The binding like this
rc = SQLBindParameter(stmt, 1, SQL_PARAM_INPUT, SQL_C_TIMESTAMP, SQL_DATE, 255, 0, &c1, 6, NULL);

would require defining c1 as SQL_TIMESTAMP_STRUCT and assigning its members accordingly:

SQL_TIMESTAMP_STRUCT c1;
ts.day = 01;
ts.month = 01;
ts.year = 2013;

Please let me know if you still have problems with the date overflow after making changes to your code.
Thanks.

Hi Bogdan,

I have modified my sample code. I have a date field and want to bind it to SQL_C_TIMESTAMP. I am providing date field as a SQL_DATE_STRUCT but I am still facing same issue. It used to work with driver version 3.51.27.

Thank you Kriti, the bug is verified.
We are working on the patch for this problem.

Just for the information: the version 5.3.4 was the first where this issue was introduced.

The issue has actually been fixed when dealing with Oracle Bug# 25386024--unfortunately, that Oracle Bug doesn't have a counterpart on the MySQL bug system. Nevertheless, the following is the changelog entry put in for that bug: 

"When the SQL_TIMESTAMP_STRUCT was used, if the date portion of a timestamp
was populated but the time portion was uninitialized, queries involving the
timestamp would fail with a Date overflow error. With this fix, the
uninitialized time value is simply ignored."

With driver version 8.0.29 we are seeing this error through the use of an ACCESS linked table from mysql to Access.  The table has two date time columns.  If both of those columns have no timestamp, the updates to the table through tabular editing work.  If either column has a time field, adding a time to the other or removing times from both has no effect.  The update fails for any data changes on the row with the following error.

[MYSQL][ODBC 8.0(a) Driver][mysqld-5.5.5-10.4.21-MariaDB-log]Date overflow (#0)

Hi Gregg,

The problem you described is not the same as in this bug even though the error message is similar.
If you believe this is a bug please report it with all relevant details.

Also, in your error message I noticed that MariaDB is used. Although MySQL Connector/ODBC can work with MariaDB, the compatibility is not guaranteed. MS Access is generally very sensitive to datetime/timestamp columns. The choice of the database server plays a significant role here.