MySQL Bugs: #13857: Comparison operator semantics changed, does not convert string date literals

Bug #13857	Comparison operator semantics changed, does not convert string date literals
Submitted:	7 Oct 2005 22:06	Modified:	21 Oct 2005 10:55
Reporter:	Christopher Brooks	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.13-rc-nt	OS:	Windows (Windows 2003)
Assigned to:		CPU Architecture:	Any

Description:
The MySQL manual says, about date/time comparison operations:
  "If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed."
 - ( http://dev.mysql.com/doc/mysql/en/comparison-operators.html )

In MySQL 4.1 I used this query, with no ill effects:

select posts.postingid, posts.userid as written_by, reads.userid as read_by, posts.reply_to  from (
  SELECT l.postingid, userid, reply_to FROM link_posting_category l , posting p where categoryid in ( 
    select childid from summary_category_structure where parentid='362' or childid='362'
  ) and p.rootid = l.postingid 
) as posts, posting_read reads where posts.postingid = reads.postingid and reads.time_read > '2004-01-01' and posts.userid in (
  SELECT userid FROM summary_category_structure cats , summary_user_category users where (cats.parentid='362' or cats.childid='362') and cats.childid = users.categoryid and users.post='Y' and read_category='Y' group by userid 
) and reads.userid in (
SELECT userid FROM summary_category_structure cats , summary_user_category users where (cats.parentid='362' or cats.childid='362') and cats.childid = users.categoryid and users.post='Y' and read_category='Y' group by userid 
)

In 5.0.13 the query causes an error, indicating that the sql syntax is wrong.  I believe it is because of a change in the semantics of the comparison operator.  Trying a smaller query though, like:

  SELECT * FROM posting_usage p where last_read > "2005-01-01"

Seems to work fine in both 4.1 and 5.0.13.  Perhaps it only has changed when used within an IN clause?

How to repeat:
See description.

Could you try to create a short reproducing example
indcluding a dump and failing query?

Sorry about the vagueness in the original post, I wanted to get it filed before our long weekend hit.  Here is some more info:

Create this database setup in both 4 and 5, it should create without problem:

-- MySQL Administrator dump 1.4
--
-- ------------------------------------------------------
-- Server version	4.1.12a-nt-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

--
-- Create schema bugtest
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ bugtest;
USE bugtest;

--
-- Table structure for table `bugtest`.`posting`
--

DROP TABLE IF EXISTS `posting`;
CREATE TABLE `posting` (
  `postingid` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`postingid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `bugtest`.`posting`
--

/*!40000 ALTER TABLE `posting` DISABLE KEYS */;
INSERT INTO `posting` (`postingid`) VALUES 
 (1),
 (2),
 (3);
/*!40000 ALTER TABLE `posting` ENABLE KEYS */;

--
-- Table structure for table `bugtest`.`posting_read`
--

DROP TABLE IF EXISTS `posting_read`;
CREATE TABLE `posting_read` (
  `postingid` int(10) unsigned NOT NULL auto_increment,
  `time_read` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`postingid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `bugtest`.`posting_read`
--

/*!40000 ALTER TABLE `posting_read` DISABLE KEYS */;
INSERT INTO `posting_read` (`postingid`,`time_read`) VALUES 
 (1,'2005-01-01 00:00:00');
/*!40000 ALTER TABLE `posting_read` ENABLE KEYS */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

Then try and run the following query, it throws an error in MySQL 5, and works fine in MySQL 4 (tried in query browser):

select *  from (
  SELECT * FROM posting p where p.postingid < 2000
) as posts, posting_read reads where reads.time_read > '2004-01-01'

It's verified on Windows 2003 on 5.0.14:

mysql> select *  from (SELECT * FROM posting p where p.postingid < 2000) as post
s, posting_read as reads where reads.time_read > '2004-01-01';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'reads
 where reads.time_read > '2004-01-01'' at line 1
mysql>

It works correct on 4.1.16.

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

You get an error "near 'reads where...'" so it's not comparison semantics.
'READS' is a reserved word in 5.0 - http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html