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

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Fixed
    • 10.0.9
    • 10.0.10
    • None
    • 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

            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.