MySQL Bugs: #104206: `load data` with few specified fields doesn't result in error under STRICT

Bug #104206	`load data` with few specified fields doesn't result in error under STRICT_TRANS
Submitted:	5 Jul 2021 13:41	Modified:	6 Jul 2021 12:28
Reporter:	Brian Yue (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	8.0.25	OS:	Any (rhel-7.4)
Assigned to:		CPU Architecture:	Any (x86-64)

Description:
Hello, dear verification team,

  Here I tried `load data` command on a table which consists of 3 fields (a int, b varchar(20), c date not null) under sql mode "STRICT_TRANS_TABLES", but I specified data for only the front 2 fields and the last field (c date not null). As a result data is loaded successfully without any error or warning, which is probably a violation with refman.

  As descibed in the refman of MySQL8.0:
```
Restrictive data interpretation uses these rules:

Too many or too few fields results an error.

```

  Pelease reference to `How to repeat` for detail.

How to repeat:
set @@session.sql_mode = "STRICT_TRANS_TABLES";

create database test;

use test;

CREATE TABLE `test` (
     `a` int(11) NOT NULL,
     `b` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
     `c` date NOT NULL,
     PRIMARY KEY (`a`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

[shell]
echo "12345zhou" > ~/test.csv

// actually, why does this sql command succeed ?? I don't sepcify value for field `c` 
load data infile "~/test.csv" into table test character set binary fields escaped by '' (@a) set a=substring(@a,1,1),b=substring(@a,6,12);

mysql> select * from test;
+---+------+------------+
| a | b    | c          |
+---+------+------------+
| 1 | zhou | 0000-00-00 |
+---+------+------------+
1 row in set (0.00 sec)

Hi Mr. Yue,

Thank you for your bug report.

However, this is not a bug. Here is an excerpt from our Manual:

"
For STRICT_TRANS_TABLES, MySQL converts an invalid value to the closest valid value for the column and inserts the adjusted value. If a value is missing, MySQL inserts the implicit default value for the column data type. 
"

You should check whether you get a warning or not ......

Hello, 
  Thanks for your reply.

  Following the steps I mentioned, I get no warning. Is there a miss of warning ?

  Results of each sql command like this:

mysql>
mysql> create database test;

use test;

CREATE TABLE `test` (
     `a` int(11) NOT NULL,
     `b` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
     `c` date NOT NULL,
     PRIMARY KEY (`a`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;Query OK, 1 row affected (0.00 sec)

mysql>
mysql> use test;
Database changed
mysql>
mysql> CREATE TABLE `test` (
    ->      `a` int(11) NOT NULL,
    ->      `b` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
    ->      `c` date NOT NULL,
    ->      PRIMARY KEY (`a`)
    ->    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Query OK, 0 rows affected, 1 warning (0.61 sec)

mysql>
mysql> system cat ~/test.csv
12345zhou
mysql>
mysql> load data infile "~/test.csv" into table test character set binary fields escaped by '' (@a) set a=substring(@a,1,1),b=substring(@a,6,12);
Query OK, 1 row affected (0.01 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from test;
+---+------+------------+
| a | b    | c          |
+---+------+------------+
| 1 | zhou | 0000-00-00 |
+---+------+------------+
1 row in set (0.00 sec)

mysql>

Hi Mr. Yue,

Yes, there should be a warning.

Have you ran "show warnings" after LOAD DATA ????

Please, re-check.

In the case that there is no warning, this report will be verified , but with a very, very low priority.

Hello,
  I tried `show warnings` after `load data`, and I find no warning.
  And, here I tested InnoDB tables, and I find this excerpt:

```
  For transactional tables, an error occurs for invalid or missing values in a data-change statement when
either STRICT_ALL_TABLES or STRICT_TRANS_TABLES is enabled. The statement is aborted and
rolled back.
```
  According to this excerpt, I think an error should be reported.

  With `show warnings` after `load data`, results of sql commands are like this:

mysql> set @@session.sql_mode = "STRICT_TRANS_TABLES";

create database test;

use test;

CREATE TABLE `test` (
     `a` int(11) NOT NULL,
     `b` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
     `c` date NOT NULL,
     PRIMARY KEY (`a`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> use test;
Database changed
mysql>
mysql> CREATE TABLE `test` (
    ->      `a` int(11) NOT NULL,
    ->      `b` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
    ->      `c` date NOT NULL,
    ->      PRIMARY KEY (`a`)
    ->    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Query OK, 0 rows affected, 1 warning (0.62 sec)

mysql>
mysql> system cat ~/test.csv
12345zhou
mysql>
mysql> load data infile "~/test.csv" into table test character set binary fields escaped by '' (@a) set a=substring(@a,1,1),b=substring(@a,6,12);
Query OK, 1 row affected (0.01 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

mysql> show warnings;
Empty set (0.00 sec)

mysql> select * from test;
+---+------+------------+
| a | b    | c          |
+---+------+------------+
| 1 | zhou | 0000-00-00 |
+---+------+------------+
1 row in set (0.00 sec)

mysql>

Hi Mr. Yue,

This report is now verified.

This bug has two intentions. First one is to return the warning, as per the latest comment in our documentation. Second purpose of this verified bug is that documentation is fixed , so that warning only is returned.

Verified.