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 Quote 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(). Quote 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 Quote 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. Quote 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 Quote 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? Quote 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 Quote 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. Quote Link to comment https://forums.phpfreaks.com/topic/75677-data-not-showing/#findComment-383598 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.