Bug #5088 Result is not ordered when left join on = const

• Submitted: 18 Aug 2004 12:19
• Modified: 23 Aug 2004 10:31
• Reporter: Roger Nock
• Status: Closed
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.0.20d/4.1.3
• OS: Windows (Windows)
• Assigned to: Sergey Petrunya
• CPU Architecture: Any

[18 Aug 2004 12:19] Roger Nock

Description:
The results are not ordered when using 'ON ref = const'.

select 
if(m.home = s.match_1_h,5,0) 'points'
from (selection s
left join matches m on m.match_id = 1)
order by points desc

Looking through the bug search http://bugs.mysql.com/bug.php?id=4638 is very similiar. If I use the IN() function as in bug 4638 the result is correct.

select 
if(m.home = s.match_1_h,5,0) 'points'
from (selection s
left join matches m on m.match_id IN(1))
order by points desc 

How to repeat:
USE test;

CREATE TABLE matches (
  match_id tinyint(3) unsigned NOT NULL auto_increment,
  home tinyint(3) unsigned default '0',
  UNIQUE KEY match_id (match_id),
  KEY match_id_2 (match_id)
) TYPE=MyISAM;

INSERT INTO matches VALUES("1", "2");

CREATE TABLE selection (
  player_id tinyint(3) unsigned default '0',
  match_1_h tinyint(3) unsigned default '0',
  KEY player_id (player_id)
) TYPE=MyISAM;

INSERT INTO selection VALUES("1", "0");
INSERT INTO selection VALUES("2", "0");
INSERT INTO selection VALUES("3", "2");
INSERT INTO selection VALUES("4", "4");
INSERT INTO selection VALUES("5", "1");
INSERT INTO selection VALUES("6", "2");
INSERT INTO selection VALUES("7", "2");
INSERT INTO selection VALUES("8", "0");
INSERT INTO selection VALUES("9", "3");
INSERT INTO selection VALUES("10", "0");

when using:
select 
if(m.home = s.match_1_h,5,0) 'points'
from (selection s
left join matches m on m.match_id = 1)
order by points desc

gives:
+--------+
| points |
+--------+
|      0 |
|      0 |
|      5 |
|      0 |
|      0 |
|      5 |
|      5 |
|      0 |
|      0 |
|      0 |
+--------+
10 rows in set (0.00 sec)

should display:
+--------+
| points |
+--------+
|      5 |
|      5 |
|      5 |
|      0 |
|      0 |
|      0 |
|      0 |
|      0 |
|      0 |
|      0 |
+--------+
10 rows in set (0.00 sec)

[21 Aug 2004 9:13] Sergey Petrunya

ChangeSet@1.1990, 2004-08-21 11:10:59+04:00, [e-mail]

[23 Aug 2004 10:31] Sergey Petrunya

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html