Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-78787

DB data truncated to 255 chars when casting to char/concatenating in MSSQL

XMLWordPrintable

    • MOODLE_401_STABLE
    • MOODLE_401_STABLE, MOODLE_402_STABLE
    • MDL-78787-401
    • Hide

      Ensure testing is performed on SQL Server

      Feedback responses

      1. Log in as admin
      2. Create two new users
      3. Create course
      4. Enrol both users as students
      5. Add a Feedback activity to the course
      6. Press Edit questions > Add question > Longer text answer
        • Question: Q1
        • Label: Longer text
      7. Log out
      8. Log in as first user
      9. Navigate to Feedback activity in course
      10. Press Answer the questions and enter the following (1024 characters): 

        Suspendisse erat felis, ornare sit amet nunc ut, posuere ornare sapien. Aliquam fermentum ipsum mauris. Sed id maximus orci. Vivamus nisl nibh, fermentum a egestas vel, accumsan at nulla. Fusce sit amet leo nec felis consequat fermentum. Vestibulum finibus pharetra elementum. Nam ac tellus orci.
        		 
         
        		 
        Ut neque lacus, pellentesque quis malesuada ut, rhoncus eu neque. Vestibulum porttitor sem eget eros dignissim dapibus. Interdum et malesuada fames ac ante ipsum primis in faucibus. Quisque egestas est elementum vehicula facilisis. Fusce sagittis augue a mauris luctus, sed mollis elit fringilla. Curabitur sodales est sit amet sapien pulvinar ornare. Ut dignissim magna vel porta lacinia. Morbi nec dolor malesuada, ullamcorper ipsum sed, convallis turpis. Duis sit amet diam porttitor, rutrum leo nec, vestibulum lorem. Nunc pharetra libero at aliquet tristique. Vestibulum at tristique massa, a venenatis risus. Sed quis feugiat ipsum, et volutpat ante. In aliquam euismod sem, et tincidunt augue molestie porta. Praesent at.

      11. Submit, then log out
      12. Log in as second user
      13. Navigate to Feedback activity in course
      14. Press Answer the questions and enter the following (1024 characters): 

        Fusce ullamcorper efficitur neque vitae convallis. Proin consectetur tincidunt ipsum eget luctus. Proin nec ultrices erat, ut rhoncus elit. Proin efficitur elit quis dui vehicula fermentum. Duis eget turpis ac tortor interdum volutpat eget ut elit. Fusce rhoncus bibendum accumsan. Mauris nulla sapien, tincidunt eget orci vel, vulputate elementum sapien. Pellentesque quis risus mollis lectus iaculis ullamcorper.
        		 
         
        		 
        Nullam suscipit risus vitae metus fringilla bibendum. Vestibulum viverra felis elit, nec auctor risus ultrices condimentum. Nulla facilisi. Sed ornare sagittis sapien vitae egestas. Maecenas posuere ex eros, tristique bibendum sapien condimentum non. Praesent venenatis pretium erat at gravida. Aliquam ullamcorper iaculis mattis.
        		 
         
        		 
        Fusce sagittis laoreet metus, rutrum rhoncus turpis mollis sit amet. Praesent ornare diam in nunc tristique, porttitor egestas arcu euismod. Donec sed lectus at lorem interdum hendrerit ac eget quam. Vivamus tortor nulla, tincidunt eget est ut, varius imperdiet ligula. Nunc eros.

      15. Submit, then log out
      16. Log in as admin
      17. Navigate to Feedback activity in course
      18. Press Responses
      19. Confirm the full content of each response is shown in the table
      20. Confirm sorting of the Q1 table column works as expected

      Concatenation

      1. Create a new course, with the following description: 

        Suspendisse erat felis, ornare sit amet nunc ut, posuere ornare sapien. Aliquam fermentum ipsum mauris. Sed id maximus orci. Vivamus nisl nibh, fermentum a egestas vel, accumsan at nulla. Fusce sit amet leo nec felis consequat fermentum. Vestibulum finibus pharetra elementum. Nam ac tellus orci.
        		 
         
        		 
        Ut neque lacus, pellentesque quis malesuada ut, rhoncus eu neque. Vestibulum porttitor sem eget eros dignissim dapibus. Interdum et malesuada fames ac ante ipsum primis in faucibus. Quisque egestas est elementum vehicula facilisis. Fusce sagittis augue a mauris luctus, sed mollis elit fringilla. Curabitur sodales est sit amet sapien pulvinar ornare. Ut dignissim magna vel porta lacinia. Morbi nec dolor malesuada, ullamcorper ipsum sed, convallis turpis. Duis sit amet diam porttitor, rutrum leo nec, vestibulum lorem. Nunc pharetra libero at aliquet tristique. Vestibulum at tristique massa, a venenatis risus. Sed quis feugiat ipsum, et volutpat ante. In aliquam euismod sem, et tincidunt augue molestie porta. Praesent at.

      2. Create another new course, with the following description: 

        Fusce ullamcorper efficitur neque vitae convallis. Proin consectetur tincidunt ipsum eget luctus. Proin nec ultrices erat, ut rhoncus elit. Proin efficitur elit quis dui vehicula fermentum. Duis eget turpis ac tortor interdum volutpat eget ut elit. Fusce rhoncus bibendum accumsan. Mauris nulla sapien, tincidunt eget orci vel, vulputate elementum sapien. Pellentesque quis risus mollis lectus iaculis ullamcorper.
        		 
         
        		 
        Nullam suscipit risus vitae metus fringilla bibendum. Vestibulum viverra felis elit, nec auctor risus ultrices condimentum. Nulla facilisi. Sed ornare sagittis sapien vitae egestas. Maecenas posuere ex eros, tristique bibendum sapien condimentum non. Praesent venenatis pretium erat at gravida. Aliquam ullamcorper iaculis mattis.
        		 
         
        		 
        Fusce sagittis laoreet metus, rutrum rhoncus turpis mollis sit amet. Praesent ornare diam in nunc tristique, porttitor egestas arcu euismod. Donec sed lectus at lorem interdum hendrerit ac eget quam. Vivamus tortor nulla, tincidunt eget est ut, varius imperdiet ligula. Nunc eros.

      3. Navigate to Reports in user menu
      4. Create new report from Courses report source
        • Include default setup = No
      5. Add the following columns in the report editor:
        • Course category > Category name
        • Course > Course summary
      6. Set aggregation for Course summary column to "Comma seperated values"
      7. Confirm the full content of each course summary is shown in the table
      8. Add the following condition in the report editor:
        • Course > Course summary
      9. Set condition to Contains: Nunc eros and apply
      10. Confirm only the course summary containing "Nunc eros" is returned in the report
      Show
      Ensure testing is performed on SQL Server Feedback responses Log in as admin Create two new users Create course Enrol both users as students Add a Feedback activity to the course Press Edit questions > Add question > Longer text answer Question: Q1 Label: Longer text Log out Log in as first user Navigate to Feedback activity in course Press Answer the questions and enter the following (1024 characters): Suspendisse erat felis, ornare sit amet nunc ut, posuere ornare sapien. Aliquam fermentum ipsum mauris. Sed id maximus orci. Vivamus nisl nibh, fermentum a egestas vel, accumsan at nulla. Fusce sit amet leo nec felis consequat fermentum. Vestibulum finibus pharetra elementum. Nam ac tellus orci.   Ut neque lacus, pellentesque quis malesuada ut, rhoncus eu neque. Vestibulum porttitor sem eget eros dignissim dapibus. Interdum et malesuada fames ac ante ipsum primis in faucibus. Quisque egestas est elementum vehicula facilisis. Fusce sagittis augue a mauris luctus, sed mollis elit fringilla. Curabitur sodales est sit amet sapien pulvinar ornare. Ut dignissim magna vel porta lacinia. Morbi nec dolor malesuada, ullamcorper ipsum sed, convallis turpis. Duis sit amet diam porttitor, rutrum leo nec, vestibulum lorem. Nunc pharetra libero at aliquet tristique. Vestibulum at tristique massa, a venenatis risus. Sed quis feugiat ipsum, et volutpat ante. In aliquam euismod sem, et tincidunt augue molestie porta. Praesent at. Submit, then log out Log in as second user Navigate to Feedback activity in course Press Answer the questions and enter the following (1024 characters): Fusce ullamcorper efficitur neque vitae convallis. Proin consectetur tincidunt ipsum eget luctus. Proin nec ultrices erat, ut rhoncus elit. Proin efficitur elit quis dui vehicula fermentum. Duis eget turpis ac tortor interdum volutpat eget ut elit. Fusce rhoncus bibendum accumsan. Mauris nulla sapien, tincidunt eget orci vel, vulputate elementum sapien. Pellentesque quis risus mollis lectus iaculis ullamcorper.   Nullam suscipit risus vitae metus fringilla bibendum. Vestibulum viverra felis elit, nec auctor risus ultrices condimentum. Nulla facilisi. Sed ornare sagittis sapien vitae egestas. Maecenas posuere ex eros, tristique bibendum sapien condimentum non. Praesent venenatis pretium erat at gravida. Aliquam ullamcorper iaculis mattis.   Fusce sagittis laoreet metus, rutrum rhoncus turpis mollis sit amet. Praesent ornare diam in nunc tristique, porttitor egestas arcu euismod. Donec sed lectus at lorem interdum hendrerit ac eget quam. Vivamus tortor nulla, tincidunt eget est ut, varius imperdiet ligula. Nunc eros. Submit, then log out Log in as admin Navigate to Feedback activity in course Press Responses Confirm the full content of each response is shown in the table Confirm sorting of the Q1 table column works as expected Concatenation Create a new course, with the following description: Suspendisse erat felis, ornare sit amet nunc ut, posuere ornare sapien. Aliquam fermentum ipsum mauris. Sed id maximus orci. Vivamus nisl nibh, fermentum a egestas vel, accumsan at nulla. Fusce sit amet leo nec felis consequat fermentum. Vestibulum finibus pharetra elementum. Nam ac tellus orci.   Ut neque lacus, pellentesque quis malesuada ut, rhoncus eu neque. Vestibulum porttitor sem eget eros dignissim dapibus. Interdum et malesuada fames ac ante ipsum primis in faucibus. Quisque egestas est elementum vehicula facilisis. Fusce sagittis augue a mauris luctus, sed mollis elit fringilla. Curabitur sodales est sit amet sapien pulvinar ornare. Ut dignissim magna vel porta lacinia. Morbi nec dolor malesuada, ullamcorper ipsum sed, convallis turpis. Duis sit amet diam porttitor, rutrum leo nec, vestibulum lorem. Nunc pharetra libero at aliquet tristique. Vestibulum at tristique massa, a venenatis risus. Sed quis feugiat ipsum, et volutpat ante. In aliquam euismod sem, et tincidunt augue molestie porta. Praesent at. Create another new course, with the following description: Fusce ullamcorper efficitur neque vitae convallis. Proin consectetur tincidunt ipsum eget luctus. Proin nec ultrices erat, ut rhoncus elit. Proin efficitur elit quis dui vehicula fermentum. Duis eget turpis ac tortor interdum volutpat eget ut elit. Fusce rhoncus bibendum accumsan. Mauris nulla sapien, tincidunt eget orci vel, vulputate elementum sapien. Pellentesque quis risus mollis lectus iaculis ullamcorper.   Nullam suscipit risus vitae metus fringilla bibendum. Vestibulum viverra felis elit, nec auctor risus ultrices condimentum. Nulla facilisi. Sed ornare sagittis sapien vitae egestas. Maecenas posuere ex eros, tristique bibendum sapien condimentum non. Praesent venenatis pretium erat at gravida. Aliquam ullamcorper iaculis mattis.   Fusce sagittis laoreet metus, rutrum rhoncus turpis mollis sit amet. Praesent ornare diam in nunc tristique, porttitor egestas arcu euismod. Donec sed lectus at lorem interdum hendrerit ac eget quam. Vivamus tortor nulla, tincidunt eget est ut, varius imperdiet ligula. Nunc eros. Navigate to Reports in user menu Create new report from Courses report source Include default setup = No Add the following columns in the report editor: Course category > Category name Course > Course summary Set aggregation for Course summary column to "Comma seperated values" Confirm the full content of each course summary is shown in the table Add the following condition in the report editor: Course > Course summary Set condition to Contains: Nunc eros and apply Confirm only the course summary containing "Nunc eros" is returned in the report

      This is a general problem with the SQL Server DML driver, which truncates data at 255 characters when calling sql_cast_to_char and sql_concat

      Original report

      When viewing or exporting responses to Feedback Activities in 4.1.2 with MSSQL, the Longer Text Answer question response is truncated to 255 characters.

      This is caused by the introduction of sql_cast_to_char with MDL-70613 which in turn leads to  changes to sql_concat in the SQLSRV library with MDL-39028 (lib/dml/sqlsrv_native_moodle_database.php line 1444), where an arbitrary limit of 255 characters was placed on all character fields returned from the database, using CAST(example AS NVARCHAR(255)). This should be nvarchar(max) or nvarchar(4000), or perhaps just use the MSSQL CONCAT function.

        1. image-2023-08-23-11-31-08-948.png
          image-2023-08-23-11-31-08-948.png
          144 kB
        2. image-2023-08-23-11-31-44-554.png
          image-2023-08-23-11-31-44-554.png
          176 kB
        3. MDL-78787-concatination.png
          MDL-78787-concatination.png
          461 kB
        4. MDL-78787-feedback responses.png
          MDL-78787-feedback responses.png
          553 kB

            pholden Paul Holden
            mhenry79 Mike Henry
            Laurent David Laurent David
            Andrew Lyons Andrew Lyons
            Ron Carl Alfon Yu Ron Carl Alfon Yu
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Created:
              Updated:
              Resolved:

                Estimated:
                Original Estimate - 0 minutes
                0m
                Remaining:
                Remaining Estimate - 0 minutes
                0m
                Logged:
                Time Spent - 3 hours, 6 minutes
                3h 6m

                  Error rendering 'clockify-timesheets-time-tracking-reports:timer-sidebar'. Please contact your Jira administrators.