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

Asymmetry between CAST(DATE'2001-00-00') to INT and TO CHAR in prepared statements

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 10.0.9
    • Fix Version/s: 10.0.10
    • Component/s: None
    • Labels:
      None

      Description

      SET sql_mode=DEFAULT;
      PREPARE stmt FROM "SELECT CAST(DATE'2001-00-00' AS SIGNED) AS c";
      SET sql_mode='no_zero_in_date';
      EXECUTE stmt;

      returns

      +----------+
      | c        |
      +----------+
      | 20010000 |
      +----------+

      If I change CAST type from SIGNED to CHAR, the behaviour changes:

      SET sql_mode=DEFAULT;
      PREPARE stmt FROM "SELECT CAST(DATE'2001-00-00' AS CHAR) AS c";
      SET sql_mode='no_zero_in_date';
      EXECUTE stmt;

      returns

      +---+
      | c |
      +---+
      | NULL |
      +---+
      1 row in set, 1 warning (1.50 sec)

      It should be fixed to follow the same rules:
      either NULL in both cases, or not-NULL in both cases.

      Also, notice the second problem: wrong table alignment in the last results.

      If I run "mysql --column-type-info" and run the last script that displays a wrong
      table, it reports:

      Field   1:  `c`
      Catalog:    `def`
      Database:   ``
      Table:      ``
      Org_table:  ``
      Type:       VAR_STRING
      Collation:  utf8_general_ci (33)
      Length:     30
      Max_length: 0
      Decimals:   31
      Flags:      NOT_NULL 

      The NOT_NULL flag is not correct.

      Alternatively, this script also demonstrates the problem with a wrong NOT_NULL flag:

      SET sql_mode=DEFAULT;
      DROP TABLE IF EXISTS t1;
      PREPARE stmt FROM "CREATE TABLE t1 AS SELECT CAST(DATE'2001-00-00' AS CHAR) AS c";
      SET sql_mode='no_zero_in_date';
      EXECUTE stmt;
      SHOW COLUMNS FROM t1;

      displays:

      +-------+-------------+------+-----+---------+-------+
      | Field | Type        | Null | Key | Default | Extra |
      +-------+-------------+------+-----+---------+-------+
      | c     | varchar(10) | NO   |     |         |       |
      +-------+-------------+------+-----+---------+-------+

      Null=NO is wrong.

        Attachments

          Activity

            People

            • Assignee:
              bar Alexander Barkov
              Reporter:
              bar Alexander Barkov
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: