| Bug #17395 | Syntacs error in update statement using virtual table in set clause | ||
|---|---|---|---|
| Submitted: | 14 Feb 2006 19:58 | Modified: | 21 Feb 2006 13:09 |
| Reporter: | Andre Timmer | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.0.18 | OS: | Any (All) |
| Assigned to: | CPU Architecture: | Any | |
[21 Feb 2006 13:09]
Valeriy Kravchuk
Thank you for a problem report. I was able to repeat the problem:
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.19 |
+-----------+
1 row in set (0.00 sec)
mysql> CREATE TABLE `adres` (
-> `dummy_pk` int(10) unsigned NOT NULL auto_increment,
-> `bkwi_id` int(11) NOT NULL default '0',
-> `bkwi_volgnr` smallint(6) NOT NULL default '0',
-> `type` char(2) character set latin1 NOT NULL default '',
-> `table_type` char(1) character set latin1 NOT NULL default '',
-> `postcode` varchar(9) default NULL,
-> `woonplaats` varchar(24) NOT NULL default '',
-> `code_gemeente` smallint(4) unsigned zerofill NOT NULL default '0000',
-> `code_land_kvk` smallint(4) unsigned zerofill NOT NULL default '0000',
-> `straat` varchar(26) default NULL,
-> `huisnr` varchar(9) default NULL,
-> `huisnrtoevoeging` varchar(20) default NULL,
-> `antwoordnr` int(5) default NULL,
-> `postbus` varchar(7) default NULL,
-> `created` datetime NOT NULL default '0000-00-00 00:00:00',
-> `created_by` char(1) character set latin1 NOT NULL default '',
-> `updated` datetime default NULL,
-> `updated_by` char(1) character set latin1 default NULL,
-> PRIMARY KEY (`dummy_pk`),
-> UNIQUE KEY `adres_uk` (`bkwi_id`,`bkwi_volgnr`,`type`,`table_type`),
-> KEY `adres_ref_gemeente_fk` (`code_gemeente`),
-> KEY `adres_ref_land_kvk_fk` (`code_land_kvk`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.04 sec)
mysql> CREATE TABLE `adres_googliaans` (
-> `bkwi_id` int(11) NOT NULL default '0',
-> `bkwi_volgnr` smallint(6) NOT NULL default '0',
-> `type` char(2) character set latin1 NOT NULL default '',
-> `table_type` char(1) character set latin1 NOT NULL default '',
-> `postcode` varchar(9) character set utf8 default NULL,
-> `woonplaats` varchar(24) character set utf8 NOT NULL default '',
-> `code_gemeente` smallint(4) unsigned zerofill NOT NULL default '0000',
-> `code_land_kvk` smallint(4) unsigned zerofill NOT NULL default '0000',
-> `straat` varchar(26) character set utf8 default NULL,
-> `huisnr` varchar(9) character set utf8 default NULL,
-> `huisnrtoevoeging` varchar(20) character set utf8 default NULL,
-> `postbus` varchar(7) character set utf8 default NULL,
-> KEY `adres_googliaans_i1` (`bkwi_id`,`bkwi_volgnr`),
-> KEY `adres_googliaans_i2` (`postcode`,`huisnr`),
-> KEY `adres_googliaans_i3` (`straat`),
-> KEY `adres_googliaans_i4` (`woonplaats`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
Query OK, 0 rows affected (0.01 sec)
mysql> update adres aa
-> set aa.code_gemeente = (
-> select case when max(code_gemeente) =
-> min(code_gemeente) then max(code_gemeente) else '0000' end
-> from (
-> select code_gemeente
-> from adres_googliaans bb use index
-> (adres_googliaans_i4)
-> where aa.woonplaats = bb.woonplaats
-> and aa.code_land_kvk = bb.code_land_kvk
-> and bb.code_gemeente != '0000'
-> limit 10 -- to speed up query
-> ) xx
-> )
-> where aa.code_gemeente = '0000'
-> and length(aa.woonplaats) > 0
-> and aa.code_land_kvk = '0001';
ERROR 1054 (42S22): Unknown column 'aa.woonplaats' in 'where clause'
Moreover, the following works:
mysql> update adres aa
-> set aa.code_gemeente = (select max(code_gemeente)
-> from adres_googliaans bb use index(adres_g
oogliaans_i4)
-> where aa.woonplaats = bb.woonplaats
-> and aa.code_land_kvk = bb.code_land_kvk
-> and bb.code_gemeente != '0000'
-> limit 10 -- to speed up query
-> )
-> where aa.code_gemeente = '0000'
-> and length(aa.woonplaats) > 0
-> and aa.code_land_kvk = '0001';
Query OK, 0 rows affected (0.01 sec)
Rows matched: 0 Changed: 0 Warnings: 0
So, the problem is CORRELATED subquery in FROM. Please, read the manual (http://dev.mysql.com/doc/refman/5.0/en/unnamed-views.html):
"Subqueries in the FROM clause cannot be correlated subqueries."
Exactly your case. So, it is not a bug, but documented limitation of current MySQL server versions.
Description: update adres aa set aa.code_gemeente = ( select case when max(code_gemeente) = min(code_gemeente) then max(code_gemeente) else '0000' end from ( select code_gemeente from adres_googliaans bb use index (adres_googliaans_i4) where aa.woonplaats = bb.woonplaats and aa.code_land_kvk = bb.code_land_kvk and bb.code_gemeente != '0000' limit 10 -- to speed up query ) xx ) where aa.code_gemeente = '0000' and length(aa.woonplaats) > 0 and aa.code_land_kvk = '0001'; Statement above produces error: - Unknown column 'aa.woonplaats' in 'where clause' How to repeat: Create the 2 tables below and try statement above. CREATE TABLE `adres` ( `dummy_pk` int(10) unsigned NOT NULL auto_increment, `bkwi_id` int(11) NOT NULL default '0', `bkwi_volgnr` smallint(6) NOT NULL default '0', `type` char(2) character set latin1 NOT NULL default '', `table_type` char(1) character set latin1 NOT NULL default '', `postcode` varchar(9) default NULL, `woonplaats` varchar(24) NOT NULL default '', `code_gemeente` smallint(4) unsigned zerofill NOT NULL default '0000', `code_land_kvk` smallint(4) unsigned zerofill NOT NULL default '0000', `straat` varchar(26) default NULL, `huisnr` varchar(9) default NULL, `huisnrtoevoeging` varchar(20) default NULL, `antwoordnr` int(5) default NULL, `postbus` varchar(7) default NULL, `created` datetime NOT NULL default '0000-00-00 00:00:00', `created_by` char(1) character set latin1 NOT NULL default '', `updated` datetime default NULL, `updated_by` char(1) character set latin1 default NULL, PRIMARY KEY (`dummy_pk`), UNIQUE KEY `adres_uk` (`bkwi_id`,`bkwi_volgnr`,`type`,`table_type`), KEY `adres_ref_gemeente_fk` (`code_gemeente`), KEY `adres_ref_land_kvk_fk` (`code_land_kvk`), CONSTRAINT `adres_ref_gemeente_fk` FOREIGN KEY (`code_gemeente`) REFERENCES `ref_gemeente` (`code`), CONSTRAINT `adres_ref_land_kvk_fk` FOREIGN KEY (`code_land_kvk`) REFERENCES `ref_land_kvk` (`code`), CONSTRAINT `adres_vestiging_fk` FOREIGN KEY (`bkwi_id`, `bkwi_volgnr`) REFERENCES `vestiging` (`bkwi_id`, `bkwi_volgnr`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `adres_googliaans` ( `bkwi_id` int(11) NOT NULL default '0', `bkwi_volgnr` smallint(6) NOT NULL default '0', `type` char(2) character set latin1 NOT NULL default '', `table_type` char(1) character set latin1 NOT NULL default '', `postcode` varchar(9) character set utf8 default NULL, `woonplaats` varchar(24) character set utf8 NOT NULL default '', `code_gemeente` smallint(4) unsigned zerofill NOT NULL default '0000', `code_land_kvk` smallint(4) unsigned zerofill NOT NULL default '0000', `straat` varchar(26) character set utf8 default NULL, `huisnr` varchar(9) character set utf8 default NULL, `huisnrtoevoeging` varchar(20) character set utf8 default NULL, `postbus` varchar(7) character set utf8 default NULL, KEY `adres_googliaans_i1` (`bkwi_id`,`bkwi_volgnr`), KEY `adres_googliaans_i2` (`postcode`,`huisnr`), KEY `adres_googliaans_i3` (`straat`), KEY `adres_googliaans_i4` (`woonplaats`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin; Suggested fix: If this is a bug then fix it. In the statement to tables are used: adres and adres_googliaans. I use adres_googliaans because it's still not allowed to use table adres in the set clause as well :-(.