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

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

      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.

            stronk7 Eloy Lafuente (stronk7)
            stronk7 Eloy Lafuente (stronk7)
            Nobody Nobody (Inactive)
            Votes:
            1 Vote for this issue
            Watchers:
            1 Start watching this issue

              Created:
              Updated:
              Resolved:

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