Description:
Linking a MySQL 5.0.x view to MS Access as a linked or imported table returns unexpected results.
For instance, selection criteria specified in view by WHERE statement are not applied to the resultset and so more records are returned than expected. In certain cases this applies also to columns, that is, more columns are shown in Access than defined by the view.
If a view is used to restrict access to certain rows this can be bad thing to happen.
This applies to several different server, MyODBC and MS Access versions, currently using 5.0.15, 3.51.12 and 2003.
If I prepare `pass-through`SELECT query to query the view, correct results are returned.
When using OpenOffice.org Base as the client, connecting by the same ODBC data source and linking the view as table works just fine.
MySQL Query Browser and phpMyAdmin also return correct results.
I don't know if this has something to do with how MyODBC asks data from the server or how the server responds to certain requests, but it seems to me that such behavior might be considered as a bug.
How to repeat:
CREATE TABLE `table1` (
`id` int(10) unsigned NOT NULL auto_increment,
`message` varchar(45) NOT NULL default '',
`criteria` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `table1` (`id`,`message`,`criteria`) VALUES
(1,'public message',1),
(2,'private message',2),
(3,'very private message',3),
(4,'another private message',2);
CREATE VIEW view1 AS SELECT id, message FROM table1 WHERE criteria=1;
Create ODBC system data source;
In MS Access, connect to this ODBC data source and link view1 as table. There will be 4 rows in that table instead of 1.
