MySQL Bugs: #12785: ODBC Only Shows First Table

Bug #12785	ODBC Only Shows First Table
Submitted:	24 Aug 2005 14:07	Modified:	8 Mar 2007 1:17
Reporter:	Aaron Edwards	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51	OS:	Windows (Windows)
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any
Tags:	ODBC5-RC

Description:
I built a view in server 5.0 that joins 2 tables, e.g. 

CREATE ALGORITHM=MERGE VIEW ProductsCategories AS 
select Categories.*, Products.* 
FROM Categories INNER JOIN Products 
On Categories.CategoryID = Products.CategoryID 

It works fine, is updatable, I can see and change all the fields in Query Browser. 

BUT once I link to it in my Access Client, using ODBC 3.51, only the fields in the first column show up. It's the same in 3.51.10 and 3.51.12 

How to repeat:
Create any view that joins 2 tables, then using myODBC, create a link to that view.  Only the fields on the left side of the JOIN will show up.

Suggested fix:
Not sure.  Probably pretty simple though.

Which release version of 5.0 are you using?

Thanks in advance.

5.0.11

I was unable to reproduce that behavior on my own.
Could you please provide the complete test case with
your tables schema and some data.

Thanks.

Wow.  You really couldn't reproduce the error?

And you pulled the data into Access using myODBC?  Because, as I wrote earlier, all the fields show up fine on the mySQL end.  It's only after linking them in Access using myODBC that the fields start turning up missing.

It would be very difficult for me to send you data.  I could pull out most of the data from my MDB file, compress it and send it to you.  Would that work?

Aaron

Yes using the latest MyODBC and Access 2003.
You can upload the file at:

ftp://ftp.mysql.com:/pub/mysql/upload

use a name identifying this bug report into a zip file.

Thanks.

The server is on an intranet, and is not exposed to a public IP.  Do you know of a server I can post the catalog to?  It would have to be running 5.0, and I would need login information.

Aaron

Really We need only descriptions of tables and some data:
Could you please write here results the follow queries:

show create table Categories
show create table Products

and insert some data:

insert into Categories values ......;
insert into Products values ......;

hello?  Is this thing working?

OK cool.  I added two comments earlier which didn't seem to register.  

I'm going to post the create statements and some data in my next two posts (I have to wait a few hours to get to a place where I can connect to the datasource again).  Thanks for your patience.

The tables are actually called tblOpportunities, tblProposals and tblProjects.  Here are the Create statements...

CREATE TABLE `tblopportunities` (
  `pkOpportunityID` int(10) NOT NULL auto_increment,
  `DateCreated` datetime default NULL,
  `ProjectName` varchar(100) default NULL,
  `Description` longtext,
  `fkClientID` int(10) default NULL,
  `fkClientLocationID` int(10) default NULL,
  `fkContactID` int(10) default NULL,
  `TypeOfSolicitation` smallint(5) default NULL,
  `SolicitationNumber` varchar(50) default NULL,
  `WhenDidWeHear` varchar(255) default NULL,
  `ProposalDueDate` varchar(50) default NULL,
  `HowDidWeHearAboutJob` varchar(255) default NULL,
  `RespondBy` varchar(255) default NULL,
  `TotalBudget` int(10) unsigned default NULL,
  `NNBudget` decimal(19,0) default NULL,
  `OpportunityDisposition` smallint(5) default NULL,
  `DateOpportunityDispositionChanged` datetime default NULL,
  `Comments` longtext,
  `ProjectNumber` varchar(255) default NULL,
  `fkOppPropManagerID` int(10) default NULL,
  `FiscalSponsor` varchar(100) default NULL,
  `NNCost` decimal(19,0) default NULL,
  PRIMARY KEY  (`pkOpportunityID`),
  KEY `ProjectNumber` (`ProjectNumber`),
  KEY `fkClientLocationID` (`fkClientLocationID`),
  CONSTRAINT `FK_tblopportunities_1` FOREIGN KEY (`fkClientLocationID`) REFERENCES `tblclientlocations` (`pkClientLocationID`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `tblproposals` (
  `pkProposalID` int(10) NOT NULL auto_increment,
  `fkOpportunityID` int(10) default NULL,
  `DateBecameProposal` datetime default NULL,
  `ProposedTeamingArrangements` varchar(255) default NULL,
  `InterviewDate` varchar(100) default NULL,
  `ProposalDisposition` tinyint(3) unsigned default NULL,
  `DateProposalDispositionChanged` datetime default NULL,
  `fkPrimeID` varchar(50) default NULL,
  `Subcontractors` varchar(50) default NULL,
  `ProposalURL` varchar(100) default NULL,
  PRIMARY KEY  (`pkProposalID`),
  KEY `fkPrimeID` (`fkPrimeID`),
  KEY `tblOpportunitiestblProposals` (`fkOpportunityID`),
  CONSTRAINT `tblproposals_ibfk_1` FOREIGN KEY (`fkOpportunityID`) REFERENCES `tblopportunities` (`pkOpportunityID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `tblprojects` (
  `pkProjectID` int(10) NOT NULL auto_increment,
  `fkProposalID` int(10) default NULL,
  `fkProjectManagerID` int(10) default NULL,
  `OKtoUseasReference` tinyint(1) unsigned NOT NULL,
  `RefYear` varchar(4) default NULL,
  `ReferenceText` longtext,
  `BeginningDate` datetime default NULL,
  `EndDate` datetime default NULL,
  `ReportsinLibrary` longtext,
  `WPCDArchive` varchar(50) default NULL,
  `GraphicsCDArchive` varchar(50) default NULL,
  `ProjectDisposition` tinyint(3) unsigned default NULL,
  `DateProjectDispositionChanged` datetime default NULL,
  PRIMARY KEY  (`pkProjectID`),
  KEY `fkProjectManagerID` (`fkProjectManagerID`),
  KEY `tblProposalstblProjects` (`fkProposalID`),
  CONSTRAINT `tblprojects_ibfk_1` FOREIGN KEY (`fkProposalID`) REFERENCES `tblproposals` (`pkProposalID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Data coming next...

Sample data too long (that's probably why it didn't show up yesterday.  I'm uploading it as a text file.

Bug 12785 Sample Data

Attachment: NNSampleData.txt (text/plain), 8.67 KiB.

Screenshot of view in Query Browser

Attachment: 1. vw_Proposals in Query Browser.jpg (image/pjpeg, text), 95.02 KiB.

Screenshot of view in Access/ODBC - Broken (Bug 12785)

Attachment: 2. vw_Proposals in Access-ODBC.jpg (image/pjpeg, text), 154.42 KiB.

Haven't heard back in a few days.  Is anyone working on this?  I'm really hating life until this gets resolved, as my client is waiting (not so patiently).  HELP!!!!

Why is no one responding to this?  Am I on my own then?

I'm working on bugfix

Awesome.  Thank you.

Aaron

I was unable to repeat the problem even with the table structure/data mentioned above. My Access asked to select a field as a primary index in the view and displayed all the fields from both tables. Then it displayed the data from the view.

Aaron, would you import dump12785.sql file (attached to this bug) and try again to create link to MySQL database from MS Access?

Another cause of your problems with Access can be msjet40.dll version 4.0.9025. This version of MS Jet Engine is distributed in one of MS Updates. We had many complains from our customers that MS Access with msjet40.dll version 4.0.9025 has problems with ODBC. Therefore I'd like to advise you to replace version 4.0.9025 by msjet40.dll version 4.0.80.15, 4.0.7328 or older.

DB dump creates a database db12785, tables and views

Attachment: dump12785.sql (text/plain), 15.43 KiB.

Do you know where I can get Jet 4.0.80.15 or 4.0.7328?

And do I need to install it, or just copy over the newer one?

Thanks.

Aaron

Oh and (sorry if this seems like a dumb questions) how do I do an import of that file?  do I just paste it into query browser?

Thanks.

Aaron

If msjet40.dll was replaced by automatic update it should make backup somewhere in Windows directory. Pls. try to search all msjet40.dll files there.

Current version is located in Windows/system32 directory. In addition System Files Protect stores a copy of it in Windows/system32/dllcache directory.
Check the version for every copy of msjet40.dll file (in file property dialog) and replace msjet40.dll v. 4.0.9025 by other version. But first of all you need to check the current version; possible it is different than 4.0.9025.

To import the database dump:
1. Download dump12785.sql
2. Copy it to MySQL/bin directory
3. Using mysql commandline tool:
    > mysql.exe --user=myuser --password=mypass < dump12785.sql

New database db12785 will be created.
Pls. try to make a DSN to it and import the view to MS Access.

The jet on my client machine is 4.0.8618.0.  That should be okay, right?

I'm on my way into the office now to create the new database with the SQL dump, so I'll let you know how that goes by tomorrow morning.

Thanks for your help on this.

Aaron

The IT guy is out until tomorrow, so I can't get into the server room until then.  Is it possible to create this database via mySQL Administrator or Query Browser?

If not, I'll do it tomorrow morning.

Thanks.

Aaron

ODBC info for a linked view. The last pair, TABLE=tblopportunities is what I think is causing the problem.

Attachment: Access showing ODBC info.jpg (image/pjpeg, text), 94.90 KiB.

Hi Bogdan,

I still haven't been able to create that database and try that DSN link, but I should be able to around noon today.

In the meantime, I was looking at my linked tables in Access and a funny thing happenned.  As I held my mouse over one of the linked tables, or linked view, that's been causing me problems, the little yellow box popped up showing the properties of that link.  The last item said "TABLE=tblopportunities" which is exactly the problem I'm having: it's only showing data for the tblopportunities part of the view.  

I made a screenshot and attached it to this bug.  Please tell me what you think.

And I should be able to install that DB and try creating that link by noon today.

Thanks.

Aaron

Hi Bogdan,

I did the sql dump, created the database, confirmed the view worked in QueryBrowser, created a DSN link to it in Access and was not able to see the columns from the right half of the join, just like before.

I took another screenshot of the link table (attached to the bug) and it shows the same thing, TABLE=tblopportunities.

I'm using MS Access 2002 SP1, if that makes any difference.

Aaron

ODBC info for productscategories view

Attachment: productscategoriesODBCInfo.jpg (image/pjpeg, text), 97.24 KiB.

I just updated my Access to SP3.  Same results.

Aaron

I'll try to find some workaround

Hi Bogdan,

Do you know anything about the status of Bug # 13199?  This one is a real show stopper.  It means myODBC can't create or use a File DSN, just a machine DSN, which means I have to manually set up 50 computers accross the country or try to walk people through it over the phone.  

Do you know if it will be addressed in the next release of 3.51, or will we need to wait for 5.0?  Thanks.

Aaron

Hi,

I have the same problem.
I began using views with 5.0.10b version and system worked fine.
After i upgraded to 5.0.11b, ...,  5.0.13.b and system stopped to work. 
Problem is the same: only the maintable is displayed.

My configuration:

Windows 2000 Pro
MySQL 5.0.13b
MySQL ODBC driver version: 3.51.11
MDAC 2.8 SP1
MsJet version 4.0.8015.0 (JE4SP8-Windows2000-kb829558-x86-ita.exe), same result using versions 4.0.6218.0 and 4.0.7328.0)
ACCESS 2002 (10.6501.6626) SP3

I tried to use ODBC version 5 but it doesnt work.

If usefull, i can send you the data subdirectory (10Mb)

best regards

I have two problems with Access using MySQL-views. One is the same already described. In Access I only see the the fields and data of the first table used for the select statement of the view. Also the order by statement is ignored in Access.

CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER VIEW test AS
select personal.PersonalID AS personalid,personal.VORNAME AS vorname,personal.NACHNAME AS nachname,anrede.TEXT_ANR AS text_anr
from (personal left join anrede on((personal.ANREDE_ID = anrede.ANREDESL))) order by personal.NACHNAME;

CREATE TABLE personal (
  PersonalID int(11) NOT NULL auto_increment,
  PNR int(11) default NULL,
  NACHNAME varchar(30) NOT NULL default '',
  ExNachname varchar(30) default NULL,
  VORNAME varchar(20) NOT NULL default '',
  ANREDE_ID int(11) default NULL,
  TitelExtern_ID int(11) default NULL,
  Funktion_ID int(11) default NULL,
  Stellenbezeichnung varchar(60) default NULL,
  M_FON varchar(25) default NULL,
  M_Handy varchar(50) default NULL,
  M_FAX varchar(20) default NULL,
  M_MAIL varchar(50) default NULL,
  TEAM_ID int(11) default NULL,
  Standort_ID int(11) default NULL,
  EDV_USER varchar(7) default NULL,
  GENO_USER varchar(7) default NULL,
  UDO_USER varchar(10) default NULL,
  BERATER_alt int(11) default NULL,
  KREBE_alt int(11) default NULL,
  EDV_USER_A varchar(7) default NULL,
  TitelIntern_ID int(11) default NULL,
  ArbeitCRM int(11) default NULL,
  Betriebsrat char(3) default NULL,
  FCMS_ID char(2) default NULL,
  Bild varchar(50) default NULL,
  DatErf datetime default NULL,
  ZeitErf datetime default NULL,
  DatAend datetime default NULL,
  ZeitAend datetime default NULL,
  Bearbeiter varchar(50) default NULL,
  PRIMARY KEY  (PersonalID),
  KEY PNR (PNR),
  KEY NACHNAME (NACHNAME),
  KEY VORNAME (VORNAME),
  KEY TEAM_ID (TEAM_ID),
  KEY Standort_ID (Standort_ID),
  KEY EDV_USER (EDV_USER),
  KEY GENO_USER (GENO_USER),
  KEY UDO_USER (UDO_USER)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1386 ;

CREATE TABLE anrede (
  ANREDESL int(11) NOT NULL default '0',
  ADRANR varchar(20) default NULL,
  ANRANR1 varchar(30) default NULL,
  ANRANR2 varchar(30) default NULL,
  TEXT_ANR varchar(30) NOT NULL default '',
  TEXT_ANR2 varchar(30) default NULL,
  BERATER_ANR varchar(50) default NULL,
  PRIMARY KEY  (ANREDESL)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

The second problem goes a bit further. This is a view with a more complex join as you can see below:

create or replace view persdata as
SELECT personal.PersonalID, personal.PNR, personal.VORNAME, personal.NACHNAME, CONCAT(personal.VORNAME,' ',personal.NACHNAME) as MName, CONCAT(personal.NACHNAME,', ',personal.VORNAME) as MName2, personal.Stellenbezeichnung AS Stelle, funktion.funktion_id, funktion.funktion, team.team_id, team.team, team.KuerzelTeam AS t_kurz, personal.M_FON, personal.M_Handy, personal.M_FAX, personal.M_Mail, Standort.Standort_ID, Standort.Standort, Standort.S_Strasse, Standort.S_PLZ, PLZ.ORT, CONCAT(S_PLZ, ' ', Ort) AS T_Ort, CONCAT(Standort, ' (', Standort.Standort_ID, ')') AS GS, personal.EDV_USER, personal.GENO_USER, personal.UDO_USER, berater.krebe, personal.ArbeitCRM, bereich.bereich_id, bereich.bereich, markt.markt_id, markt.marktbereich, markt.region, markt.mbl_pnr, resort.resort_id, resort.resort, anrede.anredesl, anrede.text_anr, titelextern.titelextern_id, titelextern.titelextern AS titel_ex, titelintern.titelintern_id, titelintern.titelintern AS titel_in
FROM (((((((((((personal LEFT JOIN Standort ON personal.Standort_ID = Standort.Standort_ID) LEFT JOIN PLZ ON Standort.S_PLZ = PLZ.PLZ) LEFT JOIN team ON personal.team_id = team.team_id) LEFT JOIN zubereich ON personal.team_id = zubereich.team_id AND personal.Standort_ID = zubereich.Standort_ID) LEFT JOIN bereich ON zubereich.bereich_id = bereich.bereich_id) LEFT JOIN markt ON zubereich.markt_id = markt.markt_id) LEFT JOIN resort ON bereich.resort_id = resort.resort_id) LEFT JOIN funktion ON personal.funktion_id = funktion.funktion_id) LEFT JOIN anrede ON personal.anrede_id = anrede.anredesl) LEFT JOIN titelextern on personal.titelextern_id = titelextern.titelextern_id) LEFT JOIN titelintern on personal.titelintern_id = titelintern.titelintern_id) LEFT JOIN berater ON personal.PNR = berater.pnr
ORDER BY personal.NACHNAME, personal.VORNAME;

In phpMyAdmin I see all the fields and data of the view as I expect them to be. In Access I only see the fields of the table "personal" and Access only counts 65 data sets while phpMyAdmin shows all 720. In addition, I don't see any data in Access. All the fields in all data sets read "#Name?".

I use MySQL 5.0.15-nt and Access 2003 (11.6566.6568) SP2 on Win XP SP2
msjet40.dll is 4.0.8618.0
MyODBC is 3.51.12.00

And one more general thing about the views in MySQL. I expected the views to be more static. Like a table that is build up when the view is created and updated when one of the corresponding tables is changed. This way I could use views to improve performance on complex selections. But it seems that the complete view is calculated each time I select one of the data sets of the view. This leads to a performance which is much slower then a direct selection of the data including all the needed joins. Is there a way to improve the performance of the views without building up a pseudo-view with a static table and update-triggers?

Thank you for reading this long comment. I hope you can help me.

Oli

I have the same problem. 
Is anyone looking at the bugs????

I investigated more the problem (see the forum: Regression using a VIEW with join in Windows environment) and i realized problem arises when the link to the view is created in ACCESS.

In fact I did two tests:
1- I created the link to a view using MySQL 15.0.11b and the view (as specified in forum article) worked well. After i switched to  MySQL 15.0.15 (without relinking) and the view worked well again. 
2- I created the link to a view using MyQL 15.0.15 and  the table reference ('TABLE=...' ODBC parameter) created by ACCESS wasn't the view name but the name of the main table referenced in the view.
 
Moreover I traced ODBC and I had these trivial results: 
1- MySQL 10.0.11b Trace is finite (in my test 45Kb)
2- MySQL 10.0.15 Trace never end .... (as if a loop exists! perhaps a wrong parameter caming from MySQL ??) (in my test >700kb before killing ACCESS)

I hope this note can help to find a solution.
best regards

PS:
A deeper analisys of the "PS" part of the forum article shown the same problem as above.

One more confirm on this issue.  

Environment:
WinXP SP2
Access 2003, msjet40.dll version: 4.0.6818.0
MySQL 5.0.16
MyODBC 3.51.12

Creating and querying the View works fine in the MySQL Query Browser & Administrator, but trying to link to it in a Microsoft Access 2003 database (using a standard .MDB database with a System DSN) makes Access pop up and ask for a Unique Record identifier.  In my case, only 1 column showed up, so I chose it, and got completely the wrong data.  Here are my tables and view code:

CREATE TABLE  `jason`.`test` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(45) NOT NULL default '',
  `TS` timestamp NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE  `jason`.`test_link` (
  `id` int(11) NOT NULL auto_increment,
  `test_id` int(11) NOT NULL default '0',
  `type_id` int(11) NOT NULL default '0',
  `TS` timestamp NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE  `jason`.`types` (
  `id` int(11) NOT NULL auto_increment,
  `type_name` varchar(45) NOT NULL default '',
  `TS` timestamp NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW  `jason`.`view_testtype` AS select `test`.`name` AS `name`,`types`.`type_name` AS `type_name` from ((`test` join `test_link` on((`test`.`id` = `test_link`.`test_id`))) join `types` on((`test_link`.`type_id` = `types`.`id`)));

Once again, the view works fine in the Query Browser, but linking from Access kills it.  This will prevent our company from going to MySQL 5 for the time being.  I hope someone can resolve this.  Thanks!

I'm using MySQL version 5.0.10a as production db and the views work fine on MsAccess.
Starting from 5.0.12 I was unable to work with views joining tables (on this subject I submitted a thread on forum).

While awaiting for a solution, I analyzed the problem making different traces (odbc standard, mysql trace, ...) which put in evidence two different behaviors in function SQLStatistics: 5.0.10 used view name while 5.0.16 used table name (I didn't investigate more but i thing the table name is the the name of the table of the first column instead of main table!)
So i debugged the driver (3.51.12) near the function SQLStatistcs, and 
looking on function SQLColumns (module catalog.c, line 773 (row[2]= curField->table;) I found different data loaded on strucure curField (MySQL regression??) and therefore on other fields reported to MsAccess:

a) 5.0.10a
  curField->Table  point to view name
  curField->org_Table point to an empty string
b) 5.0.16
  curField->Table point to the original table name from which field comes
  cur->org_Table same as curField

As I said version 5.0.10 works and so I did a patch forcing table/view name coming from the function parameter TableName insted of from MySQL:

<< . . 
/* TABLE_NAME */
        
// <<PATCH>> START
// row[2]= curField->table;
row[2] = strdup_root(alloc,TableName);  // << Force table/view name
// <<PATCH>> END

. .>>

Now views with join work again, and as soon as i'll upgrade to MySQL 5.0.16.

I'd like to know if this patch can be "the solution" or it's only a workaround, thanks.

Just a bump on this thread...  A patch has been offered - any word on whether or not this patch is the correct method to fix the issue, or if it has been applied to the main branch yet?

Hi all,

I've come to create an ODBC link to my 5.0.18 database to expose four views I've created and it would appear even the latest 3.51.12 ODBC still doesn't work.

However,
After reading the above I think the underlying problem is being missed. I don't understand why Access (or any other ODBC compliant app) ahould know ANYTHING about the underlying data structure to a view??

This issue also comes accross as I don't allow my users any access to the underlying data, they should only see the view on it. Therefore Access is now giving me an access denied error on my primary table.

The fix for this must be to just make the views appear as tables under the ODBC connector ?

I look forward to getting this fixed.

Matt Whiteley

I have another but similar problem with view in MS Access (2003):
I create a view to give a subset of a table as follows:
CREATE VIEW MainItemTypes as
select ItemTypes.*
from ItemTypes
where ItemTypes.MainType is NULL;

If I link the view as a table, MS Access gives back the entire table...

Looking into the DSN used for the link (Open link in design view, select properties), the strange thing is, that Access generates a DSN as follows:
ODBC;DATABASE=Coll1;DSN=Boek1;OPTION=542735;PORT=0;SERVER=192.168.0.1;UID=****;TABLE=ItemTypes

As you can see the DSN refers to the underlying table **and not to the view!**

I use ODBC 3.51.12 and server 5.0.18

For the people anxiously awaiting a fix I may have a workaround:

Remove the link
Create a pass-through query with the same name as the view where you just select * from the view ie in the problem I told about above: remove the link to mainitemtypes, create a pass-through query that says:
SELECT * FROM MainItemTypes;
Select as DSN the ODBC connection to your db
Save query with the name of the view.

I have the same problem. When I try to link a view in MS Access XP (or 2003) the field list in the local odbc linked table is not correct.

The mysql server version is 5.0.18-standard-log. I've even trid to install mysq 5.0.10 and everithing works fine!

Please, try to resolve this bug, or Access users can't keep on using mysqlserver for their odbc linked applications!

Thanks.

Sandro

Any progress on this bug?

Man, tell me about it.  The worst part is that it's been over a year and we STILL don't have a decent ODBC driver.  

Aaron

I'm still unable to reproduce this bug despite my numerous attempts. However, as a temporary workaround I'd suggest not to use table aliases instead of real table names in VIEWs because most probably it is caused by client library (see the bug report #19671).
If your query looks like:
SELECT t1.id, t2.category FROM table1 t1 LEFT JOIN table2 t2 ON ….
It is better to transform it into the following:
SELECT table1.id, table2.category FROM table1 LEFT JOIN table2 ON ….

finally a patch was made... go to
http://forums.mysql.com/read.php?65,52721,78517#msg-78517

and download the connector ODBC fixed =)
i hope u to enjoy it.

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

Hello, views don work correctly through ODBC with Windows XP either. When you link a view what you are really linking is the first table used in the view and all the WHERE clauses are lost.

As Bogdan noted, this was actually a bug in the MySQL server (Bug #19671), which was fixed in version 5.0.25.

Bug#19671 was fixed in 5.0.25 and 5.1.12.