[MDEV-15094] Inserting empty value omit default value Created: 2018-01-26 Updated: 2018-01-26 Resolved: 2018-01-26 |
|
| Status: | Closed |
| Project: | MariaDB Server |
| Component/s: | Data Manipulation - Insert |
| Affects Version/s: | 10.2.12 |
| Fix Version/s: | N/A |
| Type: | Bug | Priority: | Major |
| Reporter: | Mathieu REHO | Assignee: | Unassigned |
| Resolution: | Not a Bug | Votes: | 0 |
| Labels: | None | ||
| Environment: |
Linux 4.4.0-112-generic Ubuntu 16.04.3 x86_64 |
||
| Description |
|
When trying to make an insert query with empty value into an integer field that have a default value, the server returns an error. This was not impacting MariaDB 10.1. On the previous version, the default value worked. Example :
Response : #1366 - Incorrect integer value: '' for column 'subuser' at row 1 Structure : Column=subuser Type=tinyint(1) CanBeNull=No DefaultValue=0 |
| Comments |
| Comment by Elena Stepanova [ 2018-01-26 ] |
|
A default value is used when the explicit value for the column is omitted, not when it's wrong; and an empty string is not an absence of a value, it's an explicit value (an incorrect one for the integer type). When an explicit value of a wrong type is provided, it can cause either an error or a warning + automatic conversion into something suitable for the column type. When the conversion occurs, for an integer type a string is converted into zero, thus you get the false impression that the default value is used. If you change the default value to something non-zero, you'll see that it has no effect for this case. The behavior – whether the error is thrown or the warning is issued and conversion is performed – depends on sql_mode: if it's set to STRICT, then it's the error, otherwise it's the warning. In 10.1 the mode is non-strict by default, and in 10.2 it's strict, hence the difference. |