Bug #2887 elt return value not as expected after order by on field from left join
Submitted: 19 Feb 2004 3:00 Modified: 19 Feb 2004 10:35
Reporter: Marco Houtman Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.0.16 OS:Linux (Linux (suse) and Windows NT 4.0)
Assigned to: Dean Ellis CPU Architecture:Any

[19 Feb 2004 3:00] Marco Houtman
Description:
Hi,

we are experiencing a problem with the following example. We have two tables (platform and organisation). Within the platform table there's a field which contains an integer that represents the id of the organisation table. Also it contains an integer that represents a type (1=workgroup,2=thema, etc). When we query the database the results are unexpected (fields and content are in dutch):

SELECT platform.naam AS naam, elt(platform.type,'werkgroep','thema','algemeen') AS type, organisatie.regio AS regio FROM platform LEFT JOIN organisatie ON platform.org_id=organisatie.id ORDER BY naam,regio,type

the statement returns a complete list. All records are there, but the field type contains 'werkgroe' instead of 'werkgroep'. We've discovered that if we either leave out the regio (which is connected via the left join) in the order by statement or change the elt value of 'algemeen' to 'algemeen ' (note the space at the end!) , the results are as expected.

Our workaround is the latter (adding the extra space) and then trim it off in our website scripts. 

We've tried many sql-statements. Order of selecting the fields, not using the AS, different order in ORDER BY, it all generates the same wrong output.

Sincerly,

Marco Houtman

How to repeat:
create table platform(
id int(11) not null auto_increment primary key,
naam varchar(20) not null,
type int(11) not null,
org_id int(11))

insert into platform values (1,'test werkgroep',1,1)
insert into platform values (2,'test thema',2,1)
insert into platform values (3,'test algemeen',3,1)

create table organisatie(
id int(11) not null auto_increment primary key,
regio varchar(10) not null)

insert into organisatie values (1,'test regio')

SELECT platform.naam AS naam, elt(platform.type,'werkgroep','thema','algemeen') AS type, organisatie.regio AS regio FROM platform LEFT JOIN organisatie ON platform.org_id=organisatie.id ORDER BY naam,regio,type
[19 Feb 2004 10:35] Dean Ellis
I cannot repeat this with a current release.  It is possibly (probably) the same issue as bug 2290, which has already been corrected.  You might try your query with the current production release.