[MDEV-5447] CONNECT: no error on conversion 0 -> NULL with strict mode Created: 2013-12-14  Updated: 2013-12-23  Resolved: 2013-12-23

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.6
Fix Version/s: 10.0.5, 10.0.6, 10.0.7, 10.0.8

Type: Bug Priority: Minor
Reporter: Federico Razzoli Assignee: Olivier Bertrand
Resolution: Not a Bug Votes: 0
Labels: None


 Description   

I don't know if this is really a bug, but this behavior is different from the one we rely on, with this SQL_MODE:

MariaDB [test]> SELECT @@session.SQL_MODE;
 
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| @@session.SQL_MODE                                                                                                                                          |
 
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
 
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> CREATE TABLE t (c INT NULL DEFAULT 0) ENGINE=CONNECT TABLE_TYPE=DOS FILE_NAME='t.dos';
 
Query OK, 0 rows affected (0.09 sec)
 
 
 
MariaDB [test]> INSERT INTO t VALUES (0), (DEFAULT);
 
Query OK, 2 rows affected (0.00 sec)
 
Records: 2  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> SELECT * FROM t;
 
+------+
 
| c    |
 
+------+
 
| NULL |
 
| NULL |
 
+------+
 
2 rows in set (0.00 sec)

Both the DEFAULT 0 clause and the explicit 0 INSERT specify a value that is not supported for this data format. The strict mode is meant to prevent silent data changes. But even without strict mode, maybe a warnings is expected?

 Comments   
Comment by Olivier Bertrand [ 2013-12-23 ]

Indeed CONNECT handles NULL's in a specific way.
See the CONNECT documentation "NULL handling" chapter page 10.
Note: I am not sure this was added to the online documentation. You can contact me at:
bertrandop@gmail.com
to get the latest documentation in doc or pdf format.

Comment by Federico Razzoli [ 2013-12-23 ]

I understand that some text files can't contain NULLs. And this may be a personal opinion.

When @@sql_mode='STRICT_ALL_TABLES' and I try to insert a 0 in a NULL-able column, I expect an error, and if @@sql_mode='' I expect a warning (it happens with other engines). This seems to me useful, because if I try to explicitly insert a 0, or I specify a DEFAULT 0, it is probably a mistake.

Comment by Olivier Bertrand [ 2013-12-23 ]

It is not a mistake but just the fact that CONNECT does not handle nulls like other engines (at least for file based tables)

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