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

NULL-ability problems with LEAST() in combination with NO_ZERO_DATE and NO_ZERO_IN_DATE

    XMLWordPrintable

Details

    Description

      I start ``mysql --column-type-info test'' and run the following script:

      SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
      SELECT
        LEAST('0000-00-00',DATE'2001-01-01') AS c1,
        LEAST('0001-00-01',DATE'2001-01-01') AS c2;
      

      It returns the following metadata and results::

      Field   1:  `c1`
      Catalog:    `def`
      Database:   ``
      Table:      ``
      Org_table:  ``
      Type:       DATE
      Collation:  binary (63)
      Length:     10
      Max_length: 0
      Decimals:   0
      Flags:      NOT_NULL BINARY 
       
      Field   2:  `c2`
      Catalog:    `def`
      Database:   ``
      Table:      ``
      Org_table:  ``
      Type:       DATE
      Collation:  binary (63)
      Length:     10
      Max_length: 0
      Decimals:   0
      Flags:      NOT_NULL BINARY 
      

      +----+----+
      | c1 | c2 |
      +----+----+
      | NULL | NULL |
      +----+----+
      1 row in set, 4 warnings (0.00 sec)
      

      Notice, metadata says the columns are NOT_NULL, but the result is NULL for both columns. Additionally, the result table is not well-formatted.

      Now if I run this script:

      SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 AS SELECT
        LEAST('0000-00-00',DATE'2001-01-01') AS c1,
        LEAST('0001-00-01',DATE'2001-01-01') AS c2;
      SELECT * FROM t1;
      

      +------------+------------+
      | c1         | c2         |
      +------------+------------+
      | 0000-00-00 | 0000-00-00 |
      +------------+------------+
      

      it wrote two zero dates into the table instead of two NULLs.

      SHOW tells that columns were created with the "NOT NULL" attribute:

      SHOW CREATE TABLE t1;
      

      +-------+-------------------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                                          |
      +-------+-------------------------------------------------------------------------------------------------------+
      | t1    | CREATE TABLE `t1` (
        `c1` date NOT NULL,
        `c2` date NOT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      +-------+-------------------------------------------------------------------------------------------------------+
      

      Looks wrong. Columns should be NULL-able.

      The same problem is repeatable with a mixture of integer and date:

      SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
      SELECT
        LEAST(0,DATE'2001-01-01') AS c1,
        LEAST(20010001,DATE'2001-01-01') AS c2;
      

      +----+----+
      | c1 | c2 |
      +----+----+
      | NULL | NULL |
      +----+----+
      

      SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 AS SELECT
        LEAST(0,DATE'2001-01-01') AS c1,
        LEAST(20010001,DATE'2001-01-01') AS c2;
      SELECT * FROM t1;
      

      +------------+------------+
      | c1         | c2         |
      +------------+------------+
      | 0000-00-00 | 0000-00-00 | <-- expect NULLs here
      +------------+------------+
      

      SHOW CREATE TABLE t1;
      

      +-------+-------------------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                                          |
      +-------+-------------------------------------------------------------------------------------------------------+
      | t1    | CREATE TABLE `t1` (
        `c1` date NOT NULL,
        `c2` date NOT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      +-------+-------------------------------------------------------------------------------------------------------+
      

      Expect NULL-able columns.

      The same problem is repeatable with a mixture of integer and time:

      SET sql_mode='';
      SELECT LEAST(999,TIME'10:20:30') AS c1;
      

      +----+
      | c1 |
      +----+
      | NULL |
      +----+
      

      SET sql_mode='';
      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 AS SELECT LEAST(999,TIME'10:20:30') AS c1;
      SELECT * FROM t1;
      

      +----------+
      | c1       |
      +----------+
      | 00:00:00 |
      +----------+
      

      Expect NULL.

      SHOW CREATE TABLE t1;
      

      +-------+---------------------------------------------------------------------------------+
      | Table | Create Table                                                                    |
      +-------+---------------------------------------------------------------------------------+
      | t1    | CREATE TABLE `t1` (
        `c1` time NOT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      +-------+---------------------------------------------------------------------------------+
      

      Expect a NULL-able column.

      Attachments

        Issue Links

          Activity

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.