MySQL Bugs: #17725: VIEW ignores WHERE clause when using MS Access 2003/ODBC

Bug #17725	VIEW ignores WHERE clause when using MS Access 2003/ODBC
Submitted:	26 Feb 2006 14:27	Modified:	7 Mar 2007 15:02
Reporter:	Charles Coverley	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	MySQL 5.0.18-nt/standard	OS:	Windows (Windows/Linux)
Assigned to:	Jess Balint	CPU Architecture:	Any
Tags:	ODBC5-RC

Description:
Created following VIEW to restrict rows in tbl_client to only those that are for the relevant group of offices (sites). This works perfectly from SQL query tools, but using Access and MyODBC to link VIEWs, the WHERE clause is completely ignored, in other words, all rows are returned to Access. This worked perfectly when evaluating MySQL 5.01 Beta, but GPL release doesn't! This is very serious to my application, and without a fix means that MySQL is not fit for purpose. I have seen other references to this bug, but it appears the bug is not being followed up, usually using the excuse "PROBLEM CANNOT BE REPRODUCED". Come on, this is easy to reproduce, so let's get some answers! Anyone with rudimentary SQL knowledge can understand what's needed to reproduce the VIEW below - simple tables with local and foreign keys, using a JOIN, and restricting the cursor with a WHERE clause. I can create two dummy tables with some test data, and reproduce this bug in minutes..

CREATE VIEW Clients_XXX AS 
SELECT tbl_client.*, tbl_office.site_id
FROM tbl_client INNER JOIN tbl_office ON tbl_client.office_id = tbl_office.office_id
WHERE (tbl_office.site_id=1);

Can I have some real, constructive answers to this problem please, it is an absolute killer to my original choice of MySQL 5.

How to repeat:
See above, or read any SQL manual on creating VIEWs.

Thank you for a problem report. 

Please, specify the exact versions of MySQL server and MySQL Connector/ODBC used. Provide the complete test case, with tables and some data, and the expected results (as described at http://bugs.mysql.com/how-to-report.php). It is absolutely needed for anybody with rudimentary SQL knowledge to be able to work on this bug report efficiently.

Have you tried to select data from that view using any other ODBC-based tools, not MS Access?

MyODBC Connector Version 5.01 is used for ODBC connection. I have used Access 2003, Excel 2003, and Crystal Reports Professional. It is not a problem with these products, it is a problem with either MySQL or more likely MyODBC.

I include the SQL to create the test tables with test data, and the VIEW that works correctly within MySQL, but when linked to using MyODBC, returns all rows. This must surely be enough information for you to work with, particularly as you and I know that this is not the first report of this type of bug recently...

#
# Table structure for table 'tbl_client_bugtest'
#

DROP TABLE IF EXISTS `tbl_client_bugtest`;
CREATE TABLE `tbl_client_bugtest` (
  `client_id` INT NOT NULL AUTO_INCREMENT,
  `office_id` INT DEFAULT 0,
  `client_name` VARCHAR(50),
  PRIMARY KEY (`client_id`)
) TYPE=MyISAM;

#
# Dumping data for table 'tbl_client_bugtest'
#

INSERT INTO `tbl_client_bugtest` VALUES(1, 1, 'Client 1');
INSERT INTO `tbl_client_bugtest` VALUES(2, 1, 'Client 2');
INSERT INTO `tbl_client_bugtest` VALUES(3, 1, 'Client 3');
INSERT INTO `tbl_client_bugtest` VALUES(4, 1, 'Client 4');
INSERT INTO `tbl_client_bugtest` VALUES(5, 2, 'Client 5');
INSERT INTO `tbl_client_bugtest` VALUES(6, 2, 'Client 6');
INSERT INTO `tbl_client_bugtest` VALUES(7, 2, 'Client 7');
INSERT INTO `tbl_client_bugtest` VALUES(8, 3, 'Client 8');
INSERT INTO `tbl_client_bugtest` VALUES(9, 3, 'Client 9');
INSERT INTO `tbl_client_bugtest` VALUES(10, 3, 'Client 10');
INSERT INTO `tbl_client_bugtest` VALUES(11, 4, 'Client 11');
INSERT INTO `tbl_client_bugtest` VALUES(12, 4, 'Client 12');
INSERT INTO `tbl_client_bugtest` VALUES(13, 4, 'Client 13');
INSERT INTO `tbl_client_bugtest` VALUES(14, 4, 'Client 14');
INSERT INTO `tbl_client_bugtest` VALUES(15, 4, 'Client 15');

#
# Table structure for table 'tbl_office_bugtest'
#

DROP TABLE IF EXISTS `tbl_office_bugtest`;
CREATE TABLE `tbl_office_bugtest` (
  `office_id` INT NOT NULL AUTO_INCREMENT,
  `office_desc` VARCHAR(50),
  `site_id` INT DEFAULT 0,
  PRIMARY KEY (`office_id`)
) TYPE=MyISAM;

#
# Dumping data for table 'tbl_office'
#

INSERT INTO `tbl_office_bugtest` VALUES(1, 'Office A', 1);
INSERT INTO `tbl_office_bugtest` VALUES(2, 'Office B', 1);
INSERT INTO `tbl_office_bugtest` VALUES(3, 'Office C', 1);
INSERT INTO `tbl_office_bugtest` VALUES(4, 'Office 2A', 2);
INSERT INTO `tbl_office_bugtest` VALUES(5, 'Office 2B', 2);
INSERT INTO `tbl_office_bugtest` VALUES(6, 'Office 2C', 2);

#  Create the VIEW that shows the problem when linked to Access, Excel, or Crystal Reports using MyODBC 3.51 Driver
#

CREATE VIEW view_Clients AS SELECT tbl_client_bugtest.client_name, tbl_office_bugtest.office_desc, tbl_office_bugtest.site_id
FROM tbl_client_bugtest INNER JOIN tbl_office_bugtest ON tbl_client_bugtest.office_id = tbl_office_bugtest.office_id
WHERE tbl_office_bugtest.site_id=1;

Thanks for your bug report.  I was able to reproduce this bug using the information provided in this bug report with 3.51.12 and MS Access 2003/w SP

I am still waiting for a bugfix, or at least a timescale on this one. Please can someone respond, or I will have to migrate to SQL Server (which works).

I have discovered today that JOINs are not necessary to reproduce this serious bug. When viewed from MS Access, any simple VIEW - "SELECT * FROM some_table WHERE some_table.some_id = <n>;" fails!  The WHERE clause is completely ignored!
PLEASE, PLEASE, PLEASE can we have some action on this. I note that this bug has STILL not been assigned to anyone....

CAN YOU PLEASE TELL ME WHY THIS BUG HAS NOT BEEN ASSIGNED TO ANYONE?
CAN YOU PLEASE TELL ME WHY THIS BUG HAS NOT BEEN ASSIGNED TO ANYONE?
CAN YOU PLEASE TELL ME WHY THIS BUG HAS NOT BEEN ASSIGNED TO ANYONE?

In my opinion, the total lack of follow-up and demonstrable commitment to solving a major bug with MySQL says a lot about the product and it's long term useability in the serious business marketplace. I will recommend to all my contacts within the developer community that they keep well clear of MySQL, and use a serious platform instead. Gentlemen, you have a very great deal to learn about support in general, and just how much damage this approach can do to a product.
I have moved to Oracle, what a difference!

Could you please inform us about this bug. We also have problems with MySQL 5.0 and Connector 3.51.12. We use Access 2003 SP2 as frontend so we have no option but to wait for this bug to be solved in order to migrate. Can you guide us about this subject. Thanks.

It is a problem in the client library.
See bug #19671. Its priority has been increased to high.

It was the server's issue as MySQL Server 5.0 haven returned the alias instead
of the name of appropriate VIEW. MySQL Server 5.0.26 has this bug fixed. I would
not recommend using patches of MyODBC such as:

Index: catalog.c
===================================================================
--- catalog.c	(revision 137)
+++ catalog.c	(working copy)
@@ -774,7 +774,7 @@
         row[1]= "";         /* No owner */
 
         /* TABLE_NAME */
-        row[2]= curField->table;
+        row[2]= curField->org_table;
 
         /* COLUMN_NAME */
         row[3]= curField->name;

This can lead to the security issues as using VIEWs is one of protective layers
that hide true table names from possible harmful actions.
MyODBC 3.51.12 can be used for linking VIEWs from Access without any patches on
the client side.
The bug report http://bugs.mysql.com/bug.php?id=19671 caused current bug has
been fixed.

(see Bug#14611)