Bug #21608 MySQL Query Browser causes error in valid query with subquery
Submitted: 13 Aug 2006 5:49 Modified: 13 Aug 2006 17:09
Reporter: [ name withheld ] Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Query Browser Severity:S1 (Critical)
Version:1.2.1 beta OS:Windows (Windows XP SP2)
Assigned to: CPU Architecture:Any
Tags: query browser, sub-query, subquery

[13 Aug 2006 5:49] [ name withheld ] 
Description:
When I try to execute this syntatically correct query with a subquery:
SELECT
	MAX(`Q`.`LastUpdated`)
FROM
	`Quote` AS `Q`
WHERE
	`Q`.`QuoteID` IN
		(
			SELECT
				`QQ`.`QuoteID`
			FROM
				`Quote` AS `QQ`
		);
I receive the following error message:
"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 '' at line 11"

However, executing this same query using mysql (the command line program), it works just fine.

Also, this query:
SELECT
  `TABLE_NAME`
FROM
  `TABLES`
WHERE
  `TABLE_NAME` IN
    (
      SELECT
        `TABLE_NAME`
      FROM
        `TABLES`
    );
against INFORMATION_SCHEMA, works just fine.

How to repeat:
Create this table:
CREATE TABLE `quote` (
  `QuoteID` smallint(5) unsigned NOT NULL auto_increment,
  `Text` text NOT NULL,
  `Dedication` varchar(127) NULL,
  `Author` varchar(127) NULL,
  `IsAnonymous` tinyint(1) NOT NULL default '0',
  `EditedBy` smallint(5) unsigned NOT NULL,
  `LastUpdated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY  (`QuoteID`,`LastUpdated`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

and try to execute the above query in MySQL Query Browser and mysql
[13 Aug 2006 15:54] MySQL Verification Team 
QB working fine

Attachment: bug21608.PNG (image/x-png, text), 31.00 KiB.
[13 Aug 2006 15:55] MySQL Verification Team 
Thank you for the bug report. I was unable to repeat the behavior reported,
please see picture attached.
[13 Aug 2006 17:09] [ name withheld ] 
I repeated the steps again on a fresh database and still got the error. I will try this on another machine and see what happens. Here is my configuration, in case that matters:

[client]
port=3306
socket=mysql

[mysql]
default-character-set=utf8

[mysqld]

port=3306
socket=mysql

basedir="C:/Program Files/MySQL/MySQL Server 5.0/"

datadir="C:/MySQL Datafiles/"

default-character-set=utf8

default-storage-engine=MYISAM

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

max_connections=15

query_cache_size=8M

table_cache=256

tmp_table_size=9M

thread_cache_size=6

myisam_max_sort_file_size=1G

myisam_max_extra_sort_file_size=1G

myisam_sort_buffer_size=16M

key_buffer_size=9M

read_buffer_size=64K
read_rnd_buffer_size=256K

sort_buffer_size=256K

innodb_data_home_dir="C:/MySQL Datafiles/"

#skip-innodb

innodb_additional_mem_pool_size=2M

innodb_flush_log_at_trx_commit=1

innodb_log_buffer_size=1M

innodb_buffer_pool_size=10M

innodb_log_file_size=7M

innodb_thread_concurrency=6