Bug #20617 | Syntax error pointed in some correct queries with joins | ||
---|---|---|---|
Submitted: | 22 Jun 2006 2:35 | Modified: | 22 Jun 2006 20:51 |
Reporter: | Roberto Barra | Email Updates: | |
Status: | Can't repeat | Impact on me: | |
Category: | MySQL Server | Severity: | S2 (Serious) |
Version: | 5.0.22-community-nt | OS: | Windows (Windows XP) |
Assigned to: | CPU Architecture: | Any |
[22 Jun 2006 2:35]
Roberto Barra
[22 Jun 2006 7:04]
Sveta Smirnova
Please, provide output of SHOW CREATE TABLE for each table in your queries.
[22 Jun 2006 15:06]
Roberto Barra
That queires were modified to use stantard tables from schema MySQL, so in every MySQL installation should have the same tables. Just in case, here are the create tables (hope it helps): 'db', 'CREATE TABLE `db` ( `Host` char(60) collate utf8_bin NOT NULL default '', `Db` char(64) collate utf8_bin NOT NULL default '', `User` char(16) collate utf8_bin NOT NULL default '', `Select_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Insert_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Update_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Delete_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Create_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Drop_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Grant_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `References_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Index_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Alter_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Create_tmp_table_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Lock_tables_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Create_view_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Show_view_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Create_routine_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Alter_routine_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Execute_priv` enum('N','Y') character set utf8 NOT NULL default 'N', PRIMARY KEY (`Host`,`Db`,`User`), KEY `User` (`User`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Database privileges'' 'user', 'CREATE TABLE `user` ( `Host` char(60) collate utf8_bin NOT NULL default '', `User` char(16) collate utf8_bin NOT NULL default '', `Password` char(41) collate utf8_bin NOT NULL default '', `Select_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Insert_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Update_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Delete_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Create_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Drop_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Reload_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Shutdown_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Process_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `File_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Grant_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `References_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Index_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Alter_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Show_db_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Super_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Create_tmp_table_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Lock_tables_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Execute_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Repl_slave_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Repl_client_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Create_view_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Show_view_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Create_routine_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Alter_routine_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Create_user_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `ssl_type` enum('','ANY','X509','SPECIFIED') character set utf8 NOT NULL default '', `ssl_cipher` blob NOT NULL, `x509_issuer` blob NOT NULL, `x509_subject` blob NOT NULL, `max_questions` int(11) unsigned NOT NULL default '0', `max_updates` int(11) unsigned NOT NULL default '0', `max_connections` int(11) unsigned NOT NULL default '0', `max_user_connections` int(11) unsigned NOT NULL default '0', PRIMARY KEY (`Host`,`User`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'' 'host', 'CREATE TABLE `host` ( `Host` char(60) collate utf8_bin NOT NULL default '', `Db` char(64) collate utf8_bin NOT NULL default '', `Select_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Insert_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Update_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Delete_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Create_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Drop_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Grant_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `References_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Index_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Alter_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Create_tmp_table_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Lock_tables_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Create_view_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Show_view_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Create_routine_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Alter_routine_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Execute_priv` enum('N','Y') character set utf8 NOT NULL default 'N', PRIMARY KEY (`Host`,`Db`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Host privileges; Merged with database privileges'' 'user_info', 'CREATE TABLE `user_info` ( `User` varchar(16) collate utf8_bin NOT NULL, `Full_name` varchar(60) collate utf8_bin default NULL, `Description` varchar(255) collate utf8_bin default NULL, `Email` varchar(80) collate utf8_bin default NULL, `Contact_information` text collate utf8_bin, `Icon` blob, PRIMARY KEY (`User`), KEY `user_info_Full_name` (`Full_name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Stores additional user information''
[22 Jun 2006 20:51]
Sveta Smirnova
I don't get any error using first "stripped" query (SELECT * FROM db db1 JOIN (user user1 JOIN host host1 ON user1.host = host1.host) ON db1.user = user1.user) and last 5.0.23 sources. Second "stripped" query (SELECT * FROM db db1 JOIN user user1 JOIN host host1 ON user1.Host = host1.Host ON db1.User = user1.User) really contains error. Read carefully about JOIN syntax here: http://dev.mysql.com/doc/refman/5.0/en/join.html