| Bug #819 | Querying for a field + timestamp | ||
|---|---|---|---|
| Submitted: | 9 Jul 2003 7:57 | Modified: | 30 Jul 2003 2:14 |
| Reporter: | Cory Twibell | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / J | Severity: | S1 (Critical) |
| Version: | 3.1 nightly 20030612 | OS: | SunOS 5.8 |
| Assigned to: | Venu Anuganti | CPU Architecture: | Any |
[12 Jul 2003 13:12]
Mark Matthews
Can you tell me what the value in the table should be, and the timestamp value you are pulling from your file? (I.E. I need a standalone testcase). I'm having problems reproducing this issue without this data.
[12 Jul 2003 19:14]
Mark Matthews
Venu, can you check this from the libmysql side? If I do this from the Java side, and create a simple table, and insert a row with a pre-created timestamp (using server-side prepared statements, to make sure the timestamp is being encoded properly), I can not retrieve the same row with an equality test, only a '>' test, which is incorrect... Example: (in pseudo-code): Create a table and insert a timestamp, say '2003-07-12 21:07:46', using a server-side prepared statement. Now do a 'SELECT * from TABLE where timestamp_col=?' using a server-side prepared statement, and substituting '2003-07-12 21:07:46' for ? (i.e. the 7-byte long encoding). I can't get any rows back. If I substitute a '>' instead of '=' I get rows back. I don't see anything odd on the wire at a protocol level from my end...The timestamps are being encoded correctly, as they end up being inserted correctly via another server-side prepared statement.
[17 Jul 2003 6:13]
Venu Anuganti
Hi !! I could able to reproduce this on the linux box from the latest 4.1 source. But the same works fine on Windows with the same source snapshot. I will investisgate this and post the patch once done. Thanks Venu
[17 Jul 2003 7:38]
Cory Twibell
Venu, Great. Did you also check on a Sun box? That's where I am having my problem.
[30 Jul 2003 2:14]
Venu Anuganti
Thank you for your bug report. This issue has been fixed in the latest
development tree for that product. You can find more information about
accessing our development trees at
http://www.mysql.com/doc/en/Installing_source_tree.html
Fix is now available from the BitKeeper tree (CSET #1.1590)
Description: Whenever I select my field + timestamp, it returns back 0 rows, even though I have a row that matches my query. How to repeat: Have the following table: ID bigint, unsigned, auto_increment, Name varchar(20), Update_time timestamp, Create_time timestamp Primary key( name, create_time ) key( id ) Whenever I create a prepared statement: PreparedStatement ps = Connection.prepareStatement( "Select * from table where Name = ? and Create_time = ?" ); ps.setString( 1, textField.getText().trim() ); String str = zipList.getTime(); // My TIMESTAMP comes from a zipped file // time, this is correct Calendar cal = Calendar.getInstance(); cal.clear; //This Also shows correct time cal.set( Integer.parseInt( str(0, 4) )....and the rest of the set fields); java.sql.Timestamp ts = new java.sql.Timestamp( cal.getTimeInMillis() ); ts.setNanos( Integer.parseInt( str.substring(20, 21) ) ); //Also correct, //currently 0 ps.setTimestamp(2, ts ); Resultset rs = ps.executeQuery(); This returns back NO results even though I have a row in my table corresponding to the name and timestamp. When I execute this query on the mysql command line, it works just fine. Suggested fix: It looks like the JDBC driver and the MySQL db aren't using the same timestamp structure for comparisons?