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: | |
Category: | MySQL Server: Data Types | Severity: | S3 (Non-critical) |
Version: | 5.1.42 | OS: | Windows |
Assigned to: | CPU Architecture: | Any | |
Tags: | comparison, datetime, error |
[12 May 2010 14:07]
Klaus Wolf
[12 May 2010 14:09]
Mark Matthews
Changing the category, as that is a server error message, it really has nothing to do with the behavior of the JDBC driver.
[12 May 2010 14:11]
Klaus Wolf
Plain server, given SQL statement manually, does not give any error message.
[12 May 2010 14:21]
Valeriy Kravchuk
What exact version of MySQL server, x.y.z, do you use?
[14 May 2010 6:05]
Klaus Wolf
5.1.42 community on MS Windows
[14 May 2010 11:07]
Susanne Ebrecht
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.
[14 May 2010 11:19]
Klaus Wolf
After all, it works perfectly, it's legal SQL. It's just irritating that you get an error message for nothing.
[15 May 2010 22:50]
Klaus Wolf
This is an duplicate of Bug #38915.
[8 Dec 2011 14:42]
luke leighton
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)
[8 Dec 2011 14:51]
luke leighton
> 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?