Bug #4456 I'm don't know bug that or not. But MySQL had returned result not such as Iwant
Submitted: 8 Jul 2004 9:02 Modified: 8 Jul 2004 10:54
Reporter: Yaroslav Svintsov Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: User-defined functions ( UDF ) Severity:S3 (Non-critical)
Version:4.1.1-alpha OS:FreeBSD (FreeBSD 4.9-RELEASE)
Assigned to: CPU Architecture:Any

[8 Jul 2004 9:02] Yaroslav Svintsov
Description:
I have that tables:

--- orders --- 
id int(5) NOT NULL auto_increment, 
price_id int(3) default NULL, 
manager_id int(3) default NULL, 
dealer_id int(5) default NULL, 
code char(8) default NULL, 
status int(1) default NULL, 
created datetime default NULL, 
modified timestamp NOT NULL, 
PRIMARY KEY (id) 

--- ordered --- 
id int(8) NOT NULL auto_increment, 
order_id int(5) default NULL, 
model_id int(7) default NULL, 
count int(10) default NULL, 
created datetime default NULL, 
modified timestamp NOT NULL, 
PRIMARY KEY (id) 

--- factor --- 
id int(8) NOT NULL auto_increment, 
dealer_id int(5) default NULL, 
price_id int(3) default NULL, 
coefficient float(10,5) default NULL, 
modified timestamp NOT NULL, 
PRIMARY KEY (id) 

--- price --- 
id int(7) NOT NULL auto_increment, 
price_id int(3) default NULL, 
group_id int(3) default NULL, 
model varchar(40) NOT NULL default '', 
description varchar(255) default NULL, 
price float(8,3) default NULL, 
monetary_id int(2) default NULL, 
formula varchar(255) default NULL, 
image varchar(100) default NULL, 
url varchar(255) default NULL, 
document blob, 
document_type enum('plain','html') default NULL, 
created datetime default NULL, 
created_by int(3) default NULL, 
modified timestamp NOT NULL, 
modified_by int(3) default NULL, 
PRIMARY KEY (id), 
UNIQUE KEY model (model) 

--- dealers --- 
id int(5) NOT NULL auto_increment, .....

My query is:
SELECT 
  price.model,
  orders.id,
  orders.code,
  orders.dealer_id,
  price.price_id, 
  (SELECT coefficient FROM factor WHERE dealer_id = 12 AND price_id = price.price_id) AS coefficient 
FROM ordered 
LEFT JOIN orders ON ordered.order_id = orders.id 
LEFT JOIN price ON ordered.model_id = price.id 
WHERE orders.code = 'oOjV8ooO';

I understant my query is not ture. And for my result query must be other. I know that .. now.
But MySQL with this query must return result such as I want too.

How to repeat:
model                    id      code      dealer_id price_id coefficient
ENW-9606            1  oOjV8ooO     1              1       1.06000
SW-501                 1 oOjV8ooO      1              1         NULL
CISCOSOHO76    1 oOjV8ooO      1              2         NULL
CISCOSOHO77H 1 oOjV8ooO       1              2         NULL
CISCOSOHO78    1 oOjV8ooO      1               2         NULL

Coefficient in first row is true. But in other rows coefficient must be not NULL!
Bug? I'm don't known.

Suggested fix:
model                    id      code      dealer_id price_id coefficient
ENW-9606            1  oOjV8ooO     1              1       1.06000
SW-501                 1 oOjV8ooO      1              1       1.06000
CISCOSOHO76    1 oOjV8ooO      1              2       1.00000
CISCOSOHO77H 1 oOjV8ooO       1              2       1.00000
CISCOSOHO78    1 oOjV8ooO      1               2       1.00000

I'm so sorry for my bad english.
[8 Jul 2004 10:54] Marko Mäkelä
Sorry, but the bug system is not the appropriate forum for asking
support questions. Your problem is not the result of a bug.
For a list of more appropriate places to ask for help using MySQL
products, please visit http://www.mysql.com/support/

Thank you for your interest in MySQL.

Additional info:

This is not a forum for asking support questions. We need a repeatable test case (CREATE TABLE, INSERT, SELECT).