Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-14053

Unquoted NULL default values in information schema with views

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • 10.2.7, 10.2.9
    • N/A
    • Admin statements
    • None
    • Ubuntu 16.04x64

    Description

      I'm currently working on doctrine support for mariadb 10.2 and information_schema changes introduced in 10.2.7 (MDEV-13132).

      I faced an unexpected case in information schema concerning default values. I understand the new behavior of 'NULL' being quoted. And it works well, except that after an upgrade from 10.2.6 to 10.2.7, not all the fields have been converted to the new spec.

      As an example:

      SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS where COLUMN_DEFAULT IS NULL AND IS_NULLABLE = "YES" 
      

      Should not return any row, but returns 4 rows on my database (amongst 4206 fields matching default is null). The column is nullable and the column_default is NULL (should be 'NULL'). See attached screenshot 086.

      For info, the normal behaviour was working:

      SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT
      FROM INFORMATION_SCHEMA.COLUMNS where COLUMN_DEFAULT IS NULL AND IS_NULLABLE <> "YES" 
      

      For this one, 4202 records worked. See screenshot 087.

      It's only happening after upgrade to 10.2.7. So I guess the upgrade of information_schema failed somewhere. If I recreate the database the information_schema is correct.

      Attachments

        1. Selection_086.png
          50 kB
          Sébastien Vanvelthem
        2. Selection_087.png
          326 kB
          Sébastien Vanvelthem

        Issue Links

          Activity

            People

              Unassigned Unassigned
              belgattitude Sébastien Vanvelthem
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.