Data not showing

[maxz_pc]

Mysql version: 4.1.10

 

alerts.al_comment has the  data type TEXT

 

My SQL query

select alerts.al_id, alerts.al_comment, concat(users2.u_prenom,' ', users2.u_nom) as al_created_by, group_concat(users.U_NOM SEPARATOR ', ') as al_alerts_users, (SELECT count(*) from alerts as myTable where myTable.al_parent_id = alerts.al_parent_id group by alerts.al_parent_id) as nbRev 
from alerts 
	left join alerts_users on (alerts.al_parent_id = alerts_users.al_parent_id) 
	left join users on (alerts_users.u_id = users.U_ID) 
	join users as users2 on (alerts.u_id = users2.U_ID) 
WHERE 
	alerts.al_id = 429
GROUP BY alerts.al_id, alerts_users.al_parent_id ORDER BY al_date ASC LIMIT 100

 

Result obtained:

 

al_id   

 

al_comment   

 

al_created_by   

 

al_alerts_users   

 

nbRev   

 

429

 

--

 

John Smith

 

--

 

1

 

Result expected:

 

al_id   

 

al_comment   

 

al_created_by   

 

al_alerts_users   

 

nbRev   

 

429

 

It's weird

 

John Smith

 

--

 

1

 

 

I tried the query on mysql 5.0.27 and it works fine, i get the expected result

It seems that in Mysql 4.1.10 there is a problem with the TEXT data type and with groups by maybe...

 

Has anyone had a similar problem?

Thanks

[fenway]

Try wrapping the group_concat in CONCAT().

[maxz_pc]

Didn't work

[fenway]

Try wrapping the inside (not the separator) in CONCAT() -- I've had an issue like before, with NULLs, long time ago.

[maxz_pc]

I tried changing group concat in the original query with this:

 

Didn't work

concat(group_concat(concat(users.U_NOM) SEPARATOR ', ')) 

 

Didn't work

group_concat(concat(users.U_NOM) SEPARATOR ', ') 

 

Didn't work

concat(group_concat(users.U_NOM SEPARATOR ', ')) 

 

I also tried adding an id in the WHERE statements, it works but it's not logical(or at least I don't understand why this works...):

Worked

replace

alerts.al_id = 429

by

alerts.al_id in (429, 429)

 

I would like to have a better solution than having to put the alerts.al_id twice... but if it's the only way thats what i will do

[fenway]

Strange... did you try just using IN() with a single ID?

[maxz_pc]

yes i tried, it doesn't work,

 

maybe what I will do is add a null item in the IN() close

 

IN(429, null)

 

I tried that and it works... so at least I don't have to put a dummy Id and risk a weird result set

[fenway]

If you can reproduce this from the CLI with a simpler statement, report it as a bug.