Bug #7716 Using IN () fails
Submitted: 6 Jan 2005 20:44 Modified: 5 Feb 2005 1:56
Reporter: [ name withheld ] Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.9 OS:
Assigned to: Sergey Petrunya CPU Architecture:Any

[6 Jan 2005 20:44] [ name withheld ] 
Description:
Simply, the first query fails, but the second succeeds.

mysql> SELECT CONCAT(userid,(UNIX_TIMESTAMP(CAST(20050106192601/10000 AS UNSIGNED)*10000) - UNIX_TIMESTAMP( CAST( inserted /10000 AS UNSIGNED ) *10000 ) ) /3600) AS 'KeyField', userid, total_score, teamid, (UNIX_TIMESTAMP(CAST(20050106192601/10000 AS UNSIGNED)*10000) - UNIX_TIMESTAMP( CAST( inserted /10000 AS UNSIGNED ) *10000 ) ) /3600 AS Difference FROM UserEntry WHERE LEFT( inserted+0, 10 ) IN (LEFT( DATE_SUB(20050106192601, INTERVAL 6 HOUR ) +0, 10 )) and userid = 'fadf1eb2-afb4-1027-9818-0020ed76a10e' ORDER BY userid ASC , Difference ASC limit 5;                                  Empty set (1 min 1.06 sec)                                                                                                                                                                           
mysql> SELECT CONCAT(userid,(UNIX_TIMESTAMP(CAST(20050106192601/10000 AS UNSIGNED)*10000) - UNIX_TIMESTAMP( CAST( inserted /10000 AS UNSIGNED ) *10000 ) ) /3600) AS 'KeyField', userid, total_score, teamid, (UNIX_TIMESTAMP(CAST(20050106192601/10000 AS UNSIGNED)*10000) - UNIX_TIMESTAMP( CAST( inserted /10000 AS UNSIGNED ) *10000 ) ) /3600 AS Difference FROM UserEntry WHERE LEFT( inserted+0, 10 ) = LEFT( DATE_SUB(20050106192601, INTERVAL 6 HOUR ) +0, 10 ) and userid = 'fadf1eb2-afb4-1027-9818-0020ed76a10e' ORDER BY userid ASC , Difference ASC limit 5;
+------------------------------------------+--------------------------------------+-------------+--------+------------+
| KeyField                                 | userid                               | total_score | teamid | Difference |
+------------------------------------------+--------------------------------------+-------------+--------+------------+
| fadf1eb2-afb4-1027-9818-0020ed76a10e6.00 | fadf1eb2-afb4-1027-9818-0020ed76a10e |       13039 |    758 |       6.00 |
+------------------------------------------+--------------------------------------+-------------+--------+------------+
1 row in set (59.97 sec)

The only difference in the queries is changing = to IN.

How to repeat:
CREATE TABLE `UserEntry` (
  `userid` char(36) NOT NULL default '',
  `teamid` int(11) NOT NULL default '0',
  `total_score` bigint(20) NOT NULL default '0',
  `total_wu` int(11) NOT NULL default '0',
  `inserted` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  KEY `inserted` (`inserted`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

INSERT INTO `UserEntry` VALUES ('fadf1eb2-afb4-1027-9818-0020ed76a10e', 758, 12830, 758, 0, 0x323030352d30312d30342031353a32353a3030);
INSERT INTO `UserEntry` VALUES ('fadf1eb2-afb4-1027-9818-0020ed76a10e', 758, 12830, 758, 0, 0x323030352d30312d30342031363a33373a3038);
INSERT INTO `UserEntry` VALUES ('fadf1eb2-afb4-1027-9818-0020ed76a10e', 758, 12830, 758, 0, 0x323030352d30312d30342031393a32363a3530);
INSERT INTO `UserEntry` VALUES ('fadf1eb2-afb4-1027-9818-0020ed76a10e', 758, 12830, 758, 0, 0x323030352d30312d30342032323a32353a3232);
INSERT INTO `UserEntry` VALUES ('fadf1eb2-afb4-1027-9818-0020ed76a10e', 758, 12830, 758, 0, 0x323030352d30312d30352030313a32353a3132);
INSERT INTO `UserEntry` VALUES ('fadf1eb2-afb4-1027-9818-0020ed76a10e', 758, 12908, 758, 0, 0x323030352d30312d30352030343a32353a3336);
INSERT INTO `UserEntry` VALUES ('fadf1eb2-afb4-1027-9818-0020ed76a10e', 758, 12908, 758, 0, 0x323030352d30312d30352030373a32363a3139);
INSERT INTO `UserEntry` VALUES ('fadf1eb2-afb4-1027-9818-0020ed76a10e', 758, 12986, 758, 0, 0x323030352d30312d30352031303a32363a3132);
INSERT INTO `UserEntry` VALUES ('fadf1eb2-afb4-1027-9818-0020ed76a10e', 758, 12986, 758, 0, 0x323030352d30312d30352031333a33303a3331);
INSERT INTO `UserEntry` VALUES ('fadf1eb2-afb4-1027-9818-0020ed76a10e', 758, 12986, 758, 0, 0x323030352d30312d30352031363a35343a3134);
INSERT INTO `UserEntry` VALUES ('fadf1eb2-afb4-1027-9818-0020ed76a10e', 758, 13039, 758, 0, 0x323030352d30312d30352031393a32363a3333);
INSERT INTO `UserEntry` VALUES ('fadf1eb2-afb4-1027-9818-0020ed76a10e', 758, 13039, 758, 0, 0x323030352d30312d30352032323a32373a3238);
INSERT INTO `UserEntry` VALUES ('fadf1eb2-afb4-1027-9818-0020ed76a10e', 758, 13039, 758, 0, 0x323030352d30312d30362030313a33323a3338);
INSERT INTO `UserEntry` VALUES ('fadf1eb2-afb4-1027-9818-0020ed76a10e', 758, 13039, 758, 0, 0x323030352d30312d30362030343a32373a3435);
INSERT INTO `UserEntry` VALUES ('fadf1eb2-afb4-1027-9818-0020ed76a10e', 758, 13039, 758, 0, 0x323030352d30312d30362030373a32373a3436);
INSERT INTO `UserEntry` VALUES ('fadf1eb2-afb4-1027-9818-0020ed76a10e', 758, 13039, 758, 0, 0x323030352d30312d30362031303a32383a3031);
INSERT INTO `UserEntry` VALUES ('fadf1eb2-afb4-1027-9818-0020ed76a10e', 758, 13039, 758, 0, 0x323030352d30312d30362031333a32363a3131);
INSERT INTO `UserEntry` VALUES ('fadf1eb2-afb4-1027-9818-0020ed76a10e', 758, 13039, 758, 0, 0x323030352d30312d30362031363a33353a3532);
INSERT INTO `UserEntry` VALUES ('fadf1eb2-afb4-1027-9818-0020ed76a10e', 758, 13098, 758, 0, 0x323030352d30312d30362031393a33363a3433);

SELECT CONCAT(userid,(UNIX_TIMESTAMP(CAST(20050106192601/10000 AS UNSIGNED)*10000) - UNIX_TIMESTAMP( CAST( inserted /10000 AS UNSIGNED ) *10000 ) ) /3600) AS 'KeyField', userid, total_score, teamid, (UNIX_TIMESTAMP(CAST(20050106192601/10000 AS UNSIGNED)*10000) - UNIX_TIMESTAMP( CAST( inserted /10000 AS UNSIGNED ) *10000 ) ) /3600 AS Difference FROM UserEntry WHERE LEFT( inserted+0, 10 ) IN (LEFT( DATE_SUB(20050106192601, INTERVAL 6 HOUR ) +0, 10 )) and userid = 'fadf1eb2-afb4-1027-9818-0020ed76a10e' ORDER BY userid ASC , Difference ASC limit 5;
(returns no records)

SELECT CONCAT(userid,(UNIX_TIMESTAMP(CAST(20050106192601/10000 AS UNSIGNED)*10000) - UNIX_TIMESTAMP( CAST( inserted /10000 AS UNSIGNED ) *10000 ) ) /3600) AS 'KeyField', userid, total_score, teamid, (UNIX_TIMESTAMP(CAST(20050106192601/10000 AS UNSIGNED)*10000) - UNIX_TIMESTAMP( CAST( inserted /10000 AS UNSIGNED ) *10000 ) ) /3600 AS Difference FROM UserEntry WHERE LEFT( inserted+0, 10 ) = LEFT( DATE_SUB(20050106192601, INTERVAL 6 HOUR ) +0, 10 ) and userid = 'fadf1eb2-afb4-1027-9818-0020ed76a10e' ORDER BY userid ASC , Difference ASC limit 5;
(returns 1 record)
[6 Jan 2005 20:48] [ name withheld ] 
The Create Table Syntax should be:

CREATE TABLE `UserEntry` (
  `userid` char(36) NOT NULL default '',
  `teamid` int(11) NOT NULL default '0',
  `total_score` bigint(20) NOT NULL default '0',
  `total_wu` int(11) NOT NULL default '0',
  `update_score` int(11) NOT NULL default '0',
  `inserted` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  KEY `inserted` (`inserted`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
[6 Jan 2005 20:52] Dean Ellis 
Verified against 4.1.9; thank you for the report.

Simplified test case:

CREATE TABLE t1 ( a TIMESTAMP );
INSERT INTO t1 VALUES ( '2004-01-06 12:34' );
SELECT a FROM t1 WHERE LEFT(a+0,6) IN ( LEFT(20040106,6) );
SELECT a FROM t1 WHERE LEFT(a+0,6) = ( LEFT(20040106,6) );
[4 Feb 2005 6:15] Sergey Petrunya 
Created a new fix based on review comments.
[4 Feb 2005 23:00] Sergey Petrunya 
Fixed in 4.1 repository and in 5.0 repository.
[5 Feb 2005 1:56] Paul DuBois 
Mentioned in 4.1.10 and 5.0.3 change notes.