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

LOAD DATA does not honor strict modes

    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

              • Assignee:
                Unassigned
                Reporter:
                nirbhay_c Nirbhay Choubey (Inactive)
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: