[MDEV-24046] Invalid SQL state for error 1366 Created: 2020-10-28  Updated: 2020-11-07  Resolved: 2020-11-07

Status: Closed
Project: MariaDB Server
Component/s: Data types
Affects Version/s: 10.4.13
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Kamil Tekiela Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Windows 10


 Description   

When trying to insert an incorrect value into for example integer field an error is throw with code 1366. According to https://mariadb.com/kb/en/mariadb-error-codes/ and MySQL 8 the SQL status should be HY000 (General issue), however for MariaDB it is showing 22007 which is slightly misleading and not compatible with MySQL. 

CREATE TABLE IF NOT EXISTS `testnum` (
 
  `col1` integer NOT NULL
 
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;
 
 
 
INSERT INTO `testnum` (`col1`) VALUES ('DEU');

In MySQL this produces:

ERROR 1366 (HY000): Incorrect integer value: 'DEU' for column 'col1' at row 1

and in MariaDB:

ERROR 1366 (22007): Incorrect integer value: 'DEU' for column `test`.`testnum`.`col1` at row 1

 Comments   
Comment by Sergei Golubchik [ 2020-11-07 ]

Error ER_TRUNCATED_WRONG_VALUE — 1292 — has SQLSTATE 22007 both in MariaDB and MySQL.

It looks correct that ER_TRUNCATED_WRONG_VALUE_FOR_FIELD — 1366 — has the same SQLSTATE.

In fact, 22007 isn't the best SQLSTATE for them, 22007 is "invalid datetime format", some more generic value, like 22005 "error in assignment" would be more appropriate. But both errors surely should have the same SQLSTATE and in MySQL they don't

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