•  
      request #42220 Adding a column in sorted table triggers an SQL error
    Infos
    #42220
    Manuel Vacelet (vaceletm)
    2025-03-17 15:14
    2025-03-13 15:02
    43896
    Details
    Adding a column in sorted table triggers an SQL error

    Given a tracker report table sorted on a column When user add a new column to the report Then the following error is generated, resulting an empty/broken column

    2025/03/13 13:53:37 [error] 2729659#2729659: *22022 FastCGI sent in stderr: "PHP message: PHP Notice:  Error encountered while retrieving data ==>  SELECT a.id AS id, c.id AS changeset_id , R2_522.value AS `user_defined__12_522` FROM tracker_artifact AS a INNER JOIN tracker_changeset AS c ON (c.artifact_id = a.id) LEFT JOIN ( tracker_changeset_value AS R1_522
                        INNER JOIN tracker_changeset_value_text AS R2_522 ON (R2_522.changeset_value_id = R1_522.id)
                    ) ON (R1_522.changeset_id = c.id AND R1_522.field_id = 522 ) WHERE c.id IN (2396,2770,2398,2359,2399,2360,2358,2357,2356,3139,2982,2393,3076,2397,2400,2986,2349,2530,2345,2949,3129,3210,3064,2926,3063,3116,3145,2369,2961,2904,2789,2805,2971,3132,3150,3134,2988,3030,3077,3205,3065,3148,3144,3119,3024,2844,3131,3206,3090,3209,3208,3157,3032,3083,3084,3089,3104,3216,3141,3147,3201,3203,3184,3204,3215,3183,3212,3211,3213,3214,3198)  ORDER BY `user_defined__12_356` DESC @@ /usr/share/tuleap/src/common/dao/include/DataAccessObject.php at line 119 in /usr/share/tuleap/src/common/dao/include/DataAcc" while reading response header from upstream, client: X.X.X.X, server:, request: "POST /plugins/tracker/?report=239&renderer=339 HTTP/2.0", upstream: "fastcgi://127.0.0.1:9000", referrer: "https://.../plugins/tracker/?tracker=12
    

    This happens because the code that generates the request add the ORDER BY in order (hu hu) to retrieve the data in the same order as the table report to merge easily the result.

    However the original code only retrieve the data with the added field so the ORDER BY will always generate an invalid query.

    Trackers
    Empty
    Empty
    • [ ] enhancement
    • [ ] internal improvement
    Empty
    Stage
    Manuel Vacelet (vaceletm)
    Closed
    2025-03-17
    Attachments
    Empty
    References

    Follow-ups

    User avatar
    Thomas Gerbet (tgerbet)2025-03-13 15:06
    • Original Submission
      Something went wrong, the follow up content couldn't be loaded
      Only formatting have been changed, you should switch to markup to see the changes