[MDEV-27389] Error instead of warning if NULL is in first row during LOAD Created: 2021-12-30  Updated: 2022-01-07  Resolved: 2022-01-02

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - MyISAM
Affects Version/s: 10.6.5
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Vassilis Virvilis Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Debian unstable

Attachments: File mariadb-error-instead-of-warning-during-load.sh    

 Description   

Hi

Assuming STRICT_ALL_TABLES is not set and the column is specified as NOT NULL then MariaDB behaves differently depending in the order of the data during LOAD.

Here is how to reproduce (There is also a script attached)

DROP TABLE IF EXISTS bugtable;
 
CREATE TABLE bugtable (id INT NOT NULL) ENGINE=MyISAM;
 
 
 
-- this works with warning
 
LOAD DATA INFILE '/tmp/data-with-warning.txt' INTO TABLE bugtable;
 
-- this does not work - gives erros
 
LOAD DATA INFILE '/tmp/data-with-error.txt' INTO TABLE bugtable;

The file /tmp/data-with-warning.txt

1
 
\N

Warning (Code 1263): Column set to default value; NULL supplied to NOT NULL column 'id' at row 2

The file /tmp/data-with-error.txt

\N
 
1

ERROR 1263 (22004) at line 1: Column set to default value; NULL supplied to NOT NULL column 'id' at row 1

Expected result:

SELECT * FROM bugtable;
 
1
 
0
 
0
 
1

Note: Specifying DEFAULT 0 during table creation does not alter the inconsistent behavior.

 Comments   
Comment by Sergei Golubchik [ 2022-01-02 ]

This is intentional. "STRICT_ALL_TABLES" means that a warning will become an an error.

"STRICT_TRANS_TABLES" (which is not a very good name and might be changed) means that a warning will only be promoted to an error if the statement can be reverted completely, in this sql_mode the data cannot be left partially modified.

For a transactional table a DML statement can always be rolled back. For a non-transactional table like MyISAM a statement generally cannot be rolled back, but if the warning happens already in the very first row — at that point the statement still can be aborted cleanly leaving no modifications in the data. And it is.

Comment by Vassilis Virvilis [ 2022-01-07 ]

Hi Sergei,

I retracted my original comment.

Ok I reread your comment more carefully and wow!

What can I say? It sure is weird behavior that looks like it depends in the order of data presented.

Weird but if not a bug as you say then not a bug.

Thanks for the explanation.

Comment by Sergei Golubchik [ 2022-01-07 ]

I agree it could be rather unexpected, but it was like that for ~20 years, might be very difficult to change now. You'll get more predictable results if you use STRICT_ALL_TABLES (for always an error) or no strict at all.

Generated at Thu Feb 08 09:52:33 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.