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

SQL concat() function is not 100% cross-db

    XMLWordPrintable

Details

    Description

      While nowadays all the main databases have a CONCAT() function (some years ago that was not the case), they don't provide a full cross-db experience.

      • Some are not able to concatenate non-char types.
      • Others have a non-standard (SQL92 from memory) behaviour with NULLs.

      Because of that, the sql_concat() helper method was created. It provides custom code to make all databases to behave consistently.

      Right now there are a few (3 I've been able to find, though the search was not super-intensive) cases in core using the SQL native function:

      $ ag -i ' concat\(' --php | ag 'SELECT|FROM|AND|OR|WHERE|ORDER'
      lib/classes/task/manager.php:989:                  FROM (SELECT concat('s', ts.id) as uniqueid,
      lib/classes/task/manager.php:1000:                        SELECT concat('a', ta.id) as uniqueid,
      h5p/tests/h5p_file_storage_test.php:821:        $sql = "SELECT concat(filepath, filename)
      

      This issues is about to move them to use the helper function and verify that everything continues working ok (it should).

      Ciao

      Attachments

        Activity

          People

            pholden Paul Holden
            stronk7 Eloy Lafuente (stronk7)
            Carlos Escobedo Carlos Escobedo
            Sara Arjona (@sarjona) Sara Arjona (@sarjona)
            CiBoT CiBoT
            David Woloszyn, Huong Nguyen, Jake Dallimore, Michael Hawkins, Stevani Andolo, Amaia Anabitarte, Bas Brands, Carlos Escobedo, Laurent David, Raquel Ortega, Sabina Abellan, Sara Arjona (@sarjona), David Woloszyn, Huong Nguyen, Jake Dallimore, Michael Hawkins, Stevani Andolo
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:
              9/May/22

              Time Tracking

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - 0 minutes
                0m
                Logged:
                Time Spent - 1 hour, 5 minutes
                1h 5m