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
