MySQL Bugs: #53601: JDBC gives error message on datetime value comparison

Bug #53601	JDBC gives error message on datetime value comparison
Submitted:	12 May 2010 14:07	Modified:	15 May 2010 22:50
Reporter:	Klaus Wolf	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.1.42	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	comparison, datetime, error

Description:
When comparing a datetime database field with the operator LIKE and an incomplete datetime value, JDBC delivers an unexpected error message.

How to repeat:
JDBC is initiated with
?zeroDateTimeBehavior=convertToNull&sessionVariables=sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ALLOW_INVALID_DATES'...

On a SQL statement:

SELECT benutzer, zeitraum, stufe, manuell, downline, kunden, gruppenpunkte, einzelpunkte, zeit FROM carpediem_gratifikationspunkt WHERE adminfreigabe=1 AND NOT direktorfreigabe AND zeit LIKE '2010-04%'

JDBC gives this error message:

Incorrect datetime value: '2010-04%' for column 'zeit' at row 1

Suggested fix:
This is not expected since MySQL sees no problem with this statement in any way. (Behavior gives lots of unnecessary error messages in the logs.)

The error message should be dropped.

Changing the category, as that is a server error message, it really has nothing to do with the behavior of the JDBC driver.

Plain server, given SQL statement manually, does not give any error message.

What exact version of MySQL server, x.y.z, do you use?

5.1.42 community on MS Windows

SELECT benutzer, zeitraum, stufe, manuell, downline, kunden, gruppenpunkte, einzelpunkte, zeit

FROM carpediem_gratifikationspunkt

WHERE adminfreigabe=1
AND NOT direktorfreigabe
AND zeit LIKE '2010-04%'

When I look into this statement I doubt that it works.

Looking into the WHERE clause:
You have:
adminfreigabe = 1
This is ok

AND NOT direktorfreigabe
here something is missing. What shall direktorfreigabe not be?
Shall it be not NULL or shall it be NULL?
Shall it be not 5 or not 23?
Shall it not be true or not be false?

System will be confused here because it doesn't know what direktorfreigabe not should be.

The next:
zeit LIKE '2010-04%'

I bet the column zeit has data type datetime or timestamp or date.

LIKE is for comparing char, varchar, text data types.

For comparing dates and times you can use same operators as for numbers.

You want all Zeiten of April ... that would be something like:

AND zeit between '2010-04-01' and '2010-04-30'

More elegant:
AND (YEAR(zeit)=2010 AND MONTH(zeit)=4)

You can find function for dates and times here:
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_month
http://dev.mysql.com/doc/refman/5.1/de/date-and-time-functions.html#function_month

You also can use the mathematical operators <, > and = also in combination <= >=.

More informations about operators you will find here:
http://dev.mysql.com/doc/refman/5.1/de/comparison-operators.html
http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html

And about logical operators:
http://dev.mysql.com/doc/refman/5.1/de/comparison-operators.html
http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html

When you look into the logical operators you will figure out:

direktorfreigabe AND zeit

The system will interpret the AND as logical operator between direktorfreigabe and zeit.

What shall direktorfreigabe && zeit be?

The result will be 0.

AND NOT direktorfreigabe AND zeit LIKE 'bla'

=> AND NOT boolean LIKE string

For me this looks like your statement is incomplete. It looks like that braces are missing. It looks like value for direktorfreigabe value is missing.

In any case there is no bug here.

I gave you all links in German and English.

After all, it works perfectly, it's legal SQL.

It's just irritating that you get an error message for nothing.

This is an duplicate of Bug #38915.

suzanne,

casting doubt on a bug-reporter's careful and thorough contributions to help mysql.com to improve the useability of mysql is insulting.  i trust that you will not do that ever again.

of course the query works.

let me give you another example (below) - in this case the query is using "%%" because it's part of a search system in a curses-based application.  as the user types (numbers or letters, it doesn't matter which), the text being searched for is inserted between the "%" characters.

so, for example, when searching for 2007 the query is made (across all fields) "%2%" then "%20%" then "%200%" then "%2007%" and on each keypress the results displayed narrow down more and more.

so when the field being queried is a text field, everything's fine - no warnings.

even when the field is a datetime field, everything's fine - no warnings.

then, all of a sudden, _just_ because of an upgrade to mysql 5.1, everything's fucked up.  users get crap on-screen (because this is a console-based "ncurses" application) and they complain about it to me to fix it.

you *can't* go breaking user expectations like this, just because someone feels like it, or worse, doesn't understand the implications of the change that they're making, which, by the use of a phrase that includes an accusation that the bug-reporter (klaus) must be a liar, would seem to be the case.

bottom line is: developers of mysql.com broke user expectations, interfered with working code, and is causing disruption for users - fixing it would be a good idea.

SELECT keybookings.keynum AS keybookings_keynum
        ,customers.session_cookie AS customers_session_cookie
        ,property.id AS property_id
        ,customers.active AS customers_active
        ,property.moddate AS property_moddate
        ,customers.language AS customers_language
        ,customers.accttype AS customers_accttype
        ,keybookings.keyinfo AS keybookings_keyinfo
        ,customers.id AS customers_id
        ,customers.email AS customers_email
        ,property.moduser AS property_moduser
        ,keybookings.id AS keybookings_id
        ,customers.accessed_date AS customers_accessed_date
        ,keybookings.property_id AS keybookings_property_id
        ,customers.moduser AS customers_moduser
        ,keybookings.cust_id AS keybookings_cust_id
        ,keybookings.returneddate AS keybookings_returneddate
        ,keybookings.keytype AS keybookings_keytype
        ,keybookings.bookeddate AS keybookings_bookeddate
        ,keybookings.notes AS keybookings_notes
        ,customers.password AS customers_password
        ,customers.moddate AS customers_moddate
        ,property.active AS property_active
        ,keybookings.moddate AS keybookings_moddate

FROM keybookings
        ,customers
        ,property

WHERE ((keybookings.property_id = 346) AND
        (keybookings.bookeddate LIKE '%%') AND
        customers.id = keybookings.cust_id AND
        (property.id = 346) AND
        property.id = keybookings.property_id AND
        keybookings.returneddate IS NULL)

> LIKE is for comparing char, varchar, text data types.

wrong.  a datetime field behaves exactly as a char, varchar and text data type
as far as the LIKE operator is concerned.

the entire application that i've written - the success and ease of use
of its search capability - is based around the fact - the FACT - that
LIKE operators work on datetime fields just as well as they do on
char, varchar and text data types.

> For comparing dates and times you can use same operators as for numbers.

 you must be joking, right?  ha ha, i am laughing.

> More elegant:
> AND (YEAR(zeit)=2010 AND MONTH(zeit)=4)

it's not "elegant" *at all*.

tell me how i am supposed to search for the number "2" by this stupid method
that you've "recommended", anywhere within the date field.

am i supposed to convert the year to a string and search for "2" in that, as well as convert the month to a string and also the day??

but first i need to _identify_ the field that is a datetime field, whereas
right now (with mysql 5.0) it is completely unnecessary to do that, because
a datetime field is treated exactly like all other fields.

right now it's perfectly possible to do what is needed to be done... except
there's this stupid, stupid and completely unnecessary warning comes up,
which massively disrupts the useability of the application.

best if you remove it, eh?