Default values like CURRENT_TIMESTAMP or DATETIME('NOW') cause problems

Issue #55 resolved
Former user created an issue

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)

  1. Former user 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

  2. Former user Account Deleted

    Reported by `george@digitalgaslight.com` on 2011-08-24 18:03:55 - Status changed: `Started`

  3. Former user Account Deleted

    ``` Issue 56 has been merged into this issue. ```

    Reported by `george@digitalgaslight.com` on 2011-08-24 18:05:34

  4. Former user 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

  5. Christopher Kramer
    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

  6. Christopher Kramer
    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

  7. Christopher Kramer
    This issue was closed by revision r428.
    

    Reported by crazy4chrissi on 2013-12-24 21:44:47 - Status changed: Fixed

  8. Log in to comment