Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-14679 META: DB layer 2.0
  3. MDL-20734

insert/update/set_field expected behaviour is to cast empty string to 0 for numeric columns

    XMLWordPrintable

Details

    • Sub-task
    • Status: Closed
    • Minor
    • Resolution: Fixed
    • 2.0
    • 2.0
    • Database SQL/XMLDB
    • None
    • MySQL, PostgreSQL, Microsoft SQL, Oracle
    • MOODLE_20_STABLE
    • MOODLE_20_STABLE

    Description

      Expected behaviour to get cross-db support is to automatically cast empty strings to 0 in the following functions:

      • insert_record
      • update_record
      • set_field

      Rationale: DBs are idiot:

      • MSSQL allows empties on int fields and saves them as 0, but not in decimal fields no matter of the nullability of the field)
      • Oracle allows empties on int fields and saves them as NULL and only in NOT NULL fields. Same for decimal fields.
      • MySQL doesn't allow empties in int/decimal fields (strict mode). Error.
      • PG doesn't allow empties in int/decimal fields). Error.

      So I guess we only can move to the "perform casting" alternative. Is the only that will work cross-db. The other (not perform casting) won't work as far as MSSQL and Oracle sometimes accept those empty strings.

      Attachments

        Activity

          People

            stronk7 Eloy Lafuente (stronk7)
            stronk7 Eloy Lafuente (stronk7)
            Nobody Nobody
            David Woloszyn, Huong Nguyen, Jake Dallimore, Michael Hawkins, Stevani Andolo
            Votes:
            1 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:
              24/Nov/10