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

    Details

    • Type: Sub-task Sub-task
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: 2.0
    • Fix Version/s: 2.0
    • Component/s: Database SQL/XMLDB
    • Labels:
      None
    • Database:
      MySQL, PostgreSQL, Microsoft SQL, Oracle
    • Affected Branches:
      MOODLE_20_STABLE
    • Fixed Branches:
      MOODLE_20_STABLE
    • Rank:
      35858

      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.

        Activity

        Eloy Lafuente (stronk7) created issue -
        Eloy Lafuente (stronk7) made changes -
        Field Original Value New Value
        Summary insert/update/set_field expected behaviour is to cast empty string to 0 insert/update/set_field expected behaviour is to cast empty string to 0 for numeric columns
        Eloy Lafuente (stronk7) made changes -
        Status Open [ 1 ] Resolved [ 5 ]
        Resolution Fixed [ 1 ]
        QA Assignee skodak
        Martin Dougiamas made changes -
        Status Resolved [ 5 ] Closed [ 6 ]
        QA Assignee skodak nobody
        Martin Dougiamas made changes -
        Workflow jira [ 33940 ] MDL Workflow [ 63338 ]
        Martin Dougiamas made changes -
        Workflow MDL Workflow [ 63338 ] MDL Full Workflow [ 92573 ]

          People

          • Votes:
            1 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: