Subquery using SELECT MAX() on DATETIME field kills Mysql NT server on latest Connector/J
Posted by: Simon Haldon
Date: March 02, 2005 12:16PM

Hi,

This is a correction/distilled post of a problem I raised under the 'zero dates not working' thread below.

The following query (copied from the mysql log file) kills the MySql server 4.1 NT (mysqld-nt.exe), but ONLY when using Connector J/3.1.x. Under Connector J/3.0.x it works fine. It happens on both jdk1.4.2 and jdk1.5.

I'm using PreparedStatement setInt() to set the parameters represented by the ? in the query.

select threadtitle, a.threadid, postedby, posteddate, textpart1 from threadheader a, threaddetail b where a.forumid=b.forumid and a.threadid=b.threadid
and a.forumid=? and a.deleteddate='1900-01-01 00:00:00' and b.deleteddate='1900-01-01 00:00:00' and posteddate=
(select max(posteddate) from threaddetail where forumid=? and deleteddate='1900-01-01 00:00:00')

This is getting the latest post from a forum in case you're wondering. The posteddate column contains non-null datetime data.

Using MySql query browser or command line, the query runs with no problems in 0.015s.

So this appears to be an issue with Connector/J 3.1.x.

This issue is currently preventing me upgrading so any help would be appreciated. I don't want to start rewriting subqueries as two separate queries.

I tried qualifying even the unique column names (e.g. using b.posteddate instead of just postedate outside of the subquery) but that isn't the problem.

Options: ReplyQuote


Subject
Written By
Posted
Subquery using SELECT MAX() on DATETIME field kills Mysql NT server on latest Connector/J
March 02, 2005 12:16PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.