Default values like CURRENT_TIMESTAMP or DATETIME('NOW') cause problems
Originally reported on Google Code with ID 55
What steps will reproduce the problem?
1. Create a table with some fields NOT NULL DEFAULT 1
2. Try to insert records while leaving those fields empty
3. Record is not inserted: "An error occured"
What is the expected output? What do you see instead?
The records should be inserted with default values
What version of the product are you using? On what operating system?
v1.8.6 on Linux
Please provide any additional information below.
Fields for which no value was specified are inserted with the NULL value.
Instead these field should not be mentioned at all in the statement, falling back to
the default value.
Reported by jos.grootlipman
on 2011-08-14 17:45:26
Comments (9)
-
-
Account Deleted ``` Thank you, it has improved with 1.8.7 but still has some issues: CURRENT_TIMESTAMP is a special default value for SQLite. Now the *text* 'CURRENT_TIMESTAMP' gets inserted. ```
Reported by `jos.grootlipman` on 2011-08-23 20:12:12
-
Account Deleted Reported by `george@digitalgaslight.com` on 2011-08-24 18:03:55 - Status changed: `Started`
-
Account Deleted Reported by `george@digitalgaslight.com` on 2011-08-24 18:05:08
-
Account Deleted ``` Issue 56 has been merged into this issue. ```
Reported by `george@digitalgaslight.com` on 2011-08-24 18:05:34
-
Account Deleted ``` Also, if you set as default value DATETIME('NOW') then when you insert a new record into the table, then in the value column, the default value is truncated to DATETIME(
```
Reported by `jeroen.walter` on 2012-01-12 21:32:10
-
In 1.9.4: - DEFAULT 1 is no problem anylonger (as written in comment 2) - DEFAULT CURRENT_TIMESTAMP is still a problem as described above. - DEFAULT DATETIME('NOW') still gets truncated like described in comment 6. I will fix those issues.
Reported by
crazy4chrissi
on 2013-03-19 22:35:29 - Labels added: Target-1.9.5 -
I just committed a change to svn that encodes default values properly so stuff like DATETIME('NOW') won't get truncated any longer. But values like this still get inserted as text. To solve this, we need to: 1. detect if the submitted value is the default value. Easy. 2a. If it is the default value, we could skip it in the INSERT statement. This way, SQLite would insert the correct value. 2b. Alternatively, we could not quote() the value if it is the default value. This seems problematic, as SQLiteDatabase (v2) seems to return default values like 'text' without the quotes, which would not work. So this solution seems hard to implement for SQlite2. So I think 2a is the best solution. But it requires that we also skip the column-name in the column-list of the INSERT statement. We just need to implement it in a way that does not introduce unnecessary redundancy.
Reported by
crazy4chrissi
on 2013-03-19 23:29:03 -
This issue was closed by revision r428.
Reported by
crazy4chrissi
on 2013-12-24 21:44:47 - Status changed:Fixed
- Log in to comment
Reported by `diracleo` on 2011-08-23 18:37:06 - Status changed: `Fixed`