Bug #55892 parser is too permissive for query - accepts invalid as valid

• Submitted: 10 Aug 2010 19:04
• Modified: 10 Aug 2010 20:39
• Reporter: Dave Dyer
• Status: Not a Bug
• Category: MySQL Server: General
• Severity: S3 (Non-critical)
• Version: Ver 14.14 Distrib 5.1.38, for Win32
• OS: Windows (windows 7 x64)
• CPU Architecture: Any

[10 Aug 2010 19:04] Dave Dyer

Description:

Consider this query, note the "tournament.uid=" phrase in the where clause

SELECT status,uid,description,count(pid),sum(if(pid='-1',1,0)),start,longdescription from tournament left join participant on uid=tid where (status='completed' or status='signup' or status='active') and tournament.uid='17 zz' group by uid order by start desc

this query executes correctly, and produces the same result as a well
formed query where tournament.uid='17 zz' is replaced by tournament.uid='17'

the "uid" field in the schema is an integer. 
# Host: localhost
# Database: boardspace
# Table: 'tournament'
# 
CREATE TABLE `tournament` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `status` enum('signup','active','finished','completed') NOT NULL DEFAULT 'signup',
  `longdescription` text,
  `start` date DEFAULT '0000-00-00',
  `end` date DEFAULT '0000-00-00',
  `variation` varchar(100) NOT NULL DEFAULT '0',
  `format` enum('manual','open-rr') DEFAULT 'manual',
  `description` varchar(100) NOT NULL DEFAULT '',
  PRIMARY KEY (`uid`)
) ENGINE=MyISAM AUTO_INCREMENT=19 DEFAULT CHARSET=latin1; 

How to repeat:

happens every time for me. I suspect something is ignoring the
extra text after the expected integer is found.

Suggested fix:

be strict.

[10 Aug 2010 20:39] Sveta Smirnova

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

Read at http://dev.mysql.com/doc/refman/5.1/en/type-conversion.html about type conversion in expression evaluation