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

LOAD DATA does not honor strict modes

    XMLWordPrintable

Details

    Description

      Zero date could be inserted using LOAD DATA even under 'NO_ZERO_DATE' mode.

      MariaDB [test]> set sql_mode='';
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> create table t1(c1 date default '0000-00-00');
      Query OK, 0 rows affected (0.08 sec)
       
      MariaDB [test]> \! echo "" > /tmp/data
       
      MariaDB [test]> insert into t1 values ();
      Query OK, 1 row affected (0.00 sec)
       
      MariaDB [test]> load data local infile '/tmp/data' into table t1;
      Query OK, 1 row affected, 1 warning (0.01 sec)
      Records: 1  Deleted: 0  Skipped: 0  Warnings: 1
       
      MariaDB [test]> select * from t1;
      +------------+
      | c1         |
      +------------+
      | 0000-00-00 |
      | 0000-00-00 |
      +------------+
      2 rows in set (0.01 sec)
       
      MariaDB [test]> set sql_mode='STRICT_TRANS_TABLES,NO_ZERO_DATE';
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> insert into t1 values ();
      ERROR 1978 (22007): Incorrect default value '0000-00-00' for column 'c1'
       
      MariaDB [test]> load data local infile '/tmp/data' into table t1;
      Query OK, 1 row affected, 1 warning (0.04 sec)
      Records: 1  Deleted: 0  Skipped: 0  Warnings: 1
       
      MariaDB [test]> show warnings;
      +---------+------+-----------------------------------------+
      | Level   | Code | Message                                 |
      +---------+------+-----------------------------------------+
      | Warning | 1265 | Data truncated for column 'c1' at row 1 |
      +---------+------+-----------------------------------------+
      1 row in set (0.00 sec)
       
      MariaDB [test]> select * from t1;
      +------------+
      | c1         |
      +------------+
      | 0000-00-00 |
      | 0000-00-00 |
      | 0000-00-00 |
      +------------+
      3 rows in set (0.00 sec)
       
      MariaDB [test]> show create table t1;
      +-------+---------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                                |
      +-------+---------------------------------------------------------------------------------------------+
      | t1    | CREATE TABLE `t1` (
        `c1` date DEFAULT '0000-00-00'
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      +-------+---------------------------------------------------------------------------------------------+
      1 row in set (0.00 sec)
      

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              nirbhay_c Nirbhay Choubey (Inactive)
              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.