Server version 3.23.55-entropy.ch
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Apple PowerBook G3 running Mac OS X 10.2.4
I'm using the distribution provided at Marc Liyanage's site:
http://www.entropy.ch/software/macosx/mysql/
Make a small test table as follows:
mysql> create database bugTest;
mysql> use bugTest;
mysql> CREATE TABLE `products` (
-> `ProductID` smallint(4) unsigned NOT NULL auto_increment,
-> `ProductName` varchar(64) NOT NULL default '',
-> PRIMARY KEY (`ProductID`)
-> ) TYPE=MyISAM
-> ;
mysql> CREATE TABLE `units` (
-> `UnitID` smallint(4) unsigned NOT NULL auto_increment,
-> `ProductID` smallint(4) unsigned NOT NULL default '0',
-> `sSize` enum('','S','M','L','X','Z') NOT NULL default '',
-> PRIMARY KEY (`UnitID`),
-> KEY `ProductID` (`ProductID`),
-> KEY `sSize` (`sSize`)
-> ) TYPE=MyISAM
-> ;
mysql> insert into products values (1,"shirt"), (2,"pants");
mysql> insert into units values (1, 1, "S"),
(2, 1, "M"), (3, 2, "L");
Using the C API (my app is a Cocoa app linked to libmysql.a), connect to the test
database and execute this query:
select Products.ProductID, Products.ProductName from Products
left join Units using (ProductID)
order by Units.sSize
The BUG is that in the fields metadata returned with the results, the field
Products.ProductID is not marked as being the primary key field. That is, in the
information gathered with mysql_fetch_field, in field->flags, the test IS_PRI_KEY
returns false for this field. The relevant bit is 0. It should be 1.
If you change the query in its "order by" clause, as follows:
select Products.ProductID, Products.ProductName from Products
left join Units using (ProductID)
order by Products.ProductID
then the test *does* work (the metadata is returned correctly).
If you change the query to use an inner join, as follows:
select Products.ProductID, Products.ProductName
from Products, Units where Products.ProductID = Units.UnitID
order by Products.ProductID
then the BUG returns.
Apart from the fact that this bug occurs only when "group by" or "order by" clauses
are present, I have not been able to work out a pattern here.
This bug has been causing us trouble since July 2002 (when we started using MySQL).
matt neuburg, phd = [EMAIL PROTECTED], http://www.tidbits.com/matt
pantes anthropoi tou eidenai oregontai phusei
Subscribe to TidBITS! It's free and smart. http://www.tidbits.com/
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
