MySQL Bugs: #24477: Strange behavior of mysql_field

Bug #24477	Strange behavior of mysql_field_flags()
Submitted:	21 Nov 2006 15:50	Modified:	27 Jan 2007 12:42
Reporter:	Luigi Suardi	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.27-community-nt	OS:	Windows (Win XP Pro)
Assigned to:		CPU Architecture:	Any
Tags:	mysql_field_flags, order by, primary key

Description:
When ORDER BY is added to a query the mysql_field_flags() on the primary key column does not show the primary_key flag.

How to repeat:
CREATE TABLE product
( id              INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
  name            VARCHAR(50) NOT NULL,
  package_size    SMALLINT NOT NULL,
  type            VARCHAR(50),
  brand           VARCHAR(50), 
  description     VARCHAR(255),
  supplier        VARCHAR(255),
  purchase_price  FLOAT NOT NULL,
  sale_price      FLOAT NOT NULL, 
  
  PRIMARY KEY (id),
  UNIQUE INDEX unique_product (name, package_size)

) ENGINE = INNODB;

CREATE TABLE product_price
( id              INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
  product_id      INT UNSIGNED NOT NULL,
  type            VARCHAR(50) NOT NULL,
  supplier        VARCHAR(255),
  location_id     INT UNSIGNED,
  price           FLOAT NOT NULL,
  unit_price      FLOAT, 
  
  PRIMARY KEY (id),
  FOREIGN KEY (product_id) REFERENCES product(id)

) ENGINE = INNODB;

# this query works, mysql_field_flags() returns
# not_null primary_key unique_key unsigned auto_increment

SELECT product_price.id, product_price.type, price, unit_price, 
       product.name, product.package_size 
FROM   product_price, product 
WHERE  product_price.product_id = product.id;

# this query does not work, mysql_field_flags() returns
# not_null unsigned
SELECT product_price.id, product_price.type, price, unit_price, 
       product.name, product.package_size 
FROM   product_price, product 
WHERE  product_price.product_id = product.id
ORDER BY product.name;

The issue with mysql_field_flags() is on the product.id column.

Discard previous comment. The problematic column is product_price.id

Thank you for a problem report, and sorry for a delay with it's processing. I do not see mysql_field_flags() function in C API (http://dev.mysql.com/doc/refman/5.0/en/c-api-function-overview.html), so, please, send complete test case that demonstrates the behaviour described. Send also EXPLAIN results for both SELECTs.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".