maxz_pc Posted November 1, 2007 Share Posted November 1, 2007 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 Link to comment https://forums.phpfreaks.com/topic/75677-data-not-showing/ Share on other sites More sharing options...
fenway Posted November 1, 2007 Share Posted November 1, 2007 Try wrapping the group_concat in CONCAT(). Link to comment https://forums.phpfreaks.com/topic/75677-data-not-showing/#findComment-382966 Share on other sites More sharing options...
maxz_pc Posted November 1, 2007 Author Share Posted November 1, 2007 Didn't work Link to comment https://forums.phpfreaks.com/topic/75677-data-not-showing/#findComment-382978 Share on other sites More sharing options...
fenway Posted November 2, 2007 Share Posted November 2, 2007 Try wrapping the inside (not the separator) in CONCAT() -- I've had an issue like before, with NULLs, long time ago. Link to comment https://forums.phpfreaks.com/topic/75677-data-not-showing/#findComment-383552 Share on other sites More sharing options...
maxz_pc Posted November 2, 2007 Author Share Posted November 2, 2007 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 Link to comment https://forums.phpfreaks.com/topic/75677-data-not-showing/#findComment-383570 Share on other sites More sharing options...
fenway Posted November 2, 2007 Share Posted November 2, 2007 Strange... did you try just using IN() with a single ID? Link to comment https://forums.phpfreaks.com/topic/75677-data-not-showing/#findComment-383589 Share on other sites More sharing options...
maxz_pc Posted November 2, 2007 Author Share Posted November 2, 2007 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 Link to comment https://forums.phpfreaks.com/topic/75677-data-not-showing/#findComment-383592 Share on other sites More sharing options...
fenway Posted November 2, 2007 Share Posted November 2, 2007 If you can reproduce this from the CLI with a simpler statement, report it as a bug. Link to comment https://forums.phpfreaks.com/topic/75677-data-not-showing/#findComment-383598 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.