[MDEV-17800] STR_TO_DATE errors when used during an INSERT Created: 2018-11-22 Updated: 2020-03-03 Resolved: 2019-02-22 |
|
| Status: | Closed |
| Project: | MariaDB Server |
| Component/s: | Data Manipulation - Insert, Documentation, Variables |
| Affects Version/s: | N/A |
| Fix Version/s: | N/A |
| Type: | Bug | Priority: | Minor |
| Reporter: | Mat Bailie | Assignee: | Ian Gilfillan |
| Resolution: | Fixed | Votes: | 0 |
| Labels: | None | ||
| Environment: |
dbfiddle.com |
||
| Issue Links: |
|
||||||||
| Description |
|
This returns `NULL`...`SELECT STR_TO_DATE('xxx', '%Y-%M-%d')` This errors...`INSERT INTO anyTable SELECT STR_TO_DATE('xxx', '%Y-%M-%d')` https://dbfiddle.uk/?rdbms=mariadb_10.2&fiddle=26157cc83c57587beda704ab6f38cc37 |
| Comments |
| Comment by Elena Stepanova [ 2018-11-28 ] |
|
I assume you are running server with strict SQL_MODE (it is default starting from 10.2). In strict mode, if a statement with invalid values is to modifiy data, it produces an error, while SELECT still produce a warning. Same is true for MySQL. It should be documented in the KB, I can't find it on the corresponding page, where it belongs. |
| Comment by Sergei Golubchik [ 2018-11-29 ] |
|
There is no "strict" SQL_MODE. There are two modes — STRICT_TRANS_TABLES and STRICT_ALL_TABLES. They turn warnings into errors, when modifying tables (transactional or all, accordingly). There is no SQL_MODE that is called STRICT_NO_MATTER_WHAT_EVEN_IF_NO_TABLES_ARE_INVOLVED. |
| Comment by Ian Gilfillan [ 2019-02-22 ] |
|
Updated https://mariadb.com/kb/en/library/sql-mode/#strict-mode to clarify difference between modifying data or not. |