SQLite Forum

DROP TABLE failure - is it intentional or a bug?
Login

DROP TABLE failure - is it intentional or a bug?

(1) By tom (younique) on 2021-03-22 21:05:06 [link] [source]

Dear all,

assume the following schema:

CREATE TABLE IF NOT EXISTS "type" (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL CHECK (name <> '') UNIQUE COLLATE NOCASE, created DATETIME DEFAULT CURRENT_TIMESTAMP, modified DATETIME AS (datetime(julianday())) STORED);
CREATE TABLE IF NOT EXISTS "quantity" (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, unit TEXT NOT NULL, sumsqr BOOLEAN NULL DEFAULT NULL, logarithmical BOOLEAN NOT NULL, created DATETIME DEFAULT CURRENT_TIMESTAMP, modified DATETIME AS (datetime(julianday())) STORED, UNIQUE (name, unit, sumsqr, logarithmical) ON CONFLICT IGNORE);
CREATE TABLE IF NOT EXISTS "dataset" (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NULL DEFAULT NULL COLLATE NOCASE, parentid INTEGER NULL DEFAULT NULL REFERENCES dataset(id) ON UPDATE CASCADE ON DELETE SET NULL CHECK (parentid <> id), created DATETIME DEFAULT CURRENT_TIMESTAMP, modified DATETIME AS (datetime(julianday())) STORED);
CREATE TABLE IF NOT EXISTS "trace" (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NULL DEFAULT NULL COLLATE NOCASE, typeid INTEGER NULL DEFAULT NULL REFERENCES "type"(id) ON UPDATE CASCADE ON DELETE SET NULL, setupname TEXT COLLATE NOCASE NULL DEFAULT NULL CHECK (setupname <> ''), setuptype TEXT COLLATE NOCASE NULL DEFAULT NULL CHECK (setuptype <> ''), datasetid INTEGER NOT NULL REFERENCES dataset(id) ON UPDATE CASCADE ON DELETE CASCADE, quantityid INTEGER NOT NULL REFERENCES quantity(id) ON UPDATE CASCADE ON DELETE RESTRICT, stored DATETIME NULL DEFAULT NULL, created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, modified DATETIME AS (datetime(julianday())) STORED);
CREATE TABLE IF NOT EXISTS "item" (id INTEGER PRIMARY KEY AUTOINCREMENT, traceid INTEGER NOT NULL REFERENCES trace(id) ON UPDATE CASCADE ON DELETE CASCADE, freq BIGINT NOT NULL CHECK (freq >= 0), value REAL NOT NULL, noiseflag BOOLEAN NULL DEFAULT NULL, nameid INTEGER NULL REFERENCES meta2(id) ON UPDATE CASCADE ON DELETE SET NULL);
CREATE TABLE IF NOT EXISTS meta2 (id INTEGER PRIMARY KEY AUTOINCREMENT, parameter TEXT NOT NULL COLLATE NOCASE, value TEXT NOT NULL COLLATE NOCASE, UNIQUE (parameter, value) ON CONFLICT IGNORE);

Now try to delete these tables in the wrong order, e.g.:

DROP TABLE IF EXISTS "type"; -- statement A
DROP TABLE IF EXISTS dataset; -- statement B

Result:

Error: no such table: main.type

The problem is clear, the (already deleted) table "type" is still referenced by other tables. But imho, statement A should fail, not B.

(2) By Keith Medcalf (kmedcalf) on 2021-03-22 21:43:28 in reply to 1 [link] [source]

modified DATETIME AS (datetime(julianday())) STORED

Note that this will not work in versions of SQLite3 3.35.2 and later as the datetime functions that return 'now' (datetime(), julianday(), strftime(format), current date, current time, current_timestamp) are no longer permitted in computed columns.

You now must use triggers to achieve the same effect.

Also, there is no such datatype as DATETIME, BOOLEAN or BIGINT and that they will be NUMERIC, NUMERIC, and INTEGER respectively.

(3) By tom (younique) on 2021-03-23 00:03:49 in reply to 2 [link] [source]

Thanks, Keith, for your comments. I understand that julianday() cannot be used in GENERATED ALWAYS AS ... VIRTUAL columns, but I do not see why the use in STORED columns has to be forbidden. That's very sad news.

(4) By Ryan Smith (cuz) on 2021-03-23 16:29:53 in reply to 3 [link] [source]

but I do not see why the use in STORED columns has to be forbidden

There's been a very long thread recently where the poster lost a lot of valuable information precisely because of this NOT being forbidden in a previous version.

You seem to hold the same misconception as that poster did, in thinking that "STORED" is somehow more persistent than any other calculated column.

It isn't.

It's a lot to rehash and valuable reading, so I will just link to the discussion:
sqlite.org/forum/forumpost/28ab8d7150

(9) By tom (younique) on 2021-03-24 18:22:55 in reply to 4 [source]

Thanks for pointing me to that thread. It's very informative, indeed. I agree with the author of one of the bottom-most posts that CACHED would have been a much better wording. Then I wouldn't have fallen into this trap.

Is there any comment about my actual problem? Do others think that this is correct behavior?

(10.1) By Ryan Smith (cuz) on 2021-03-25 00:36:44 edited from 10.0 in reply to 9 [link] [source]

Is there any comment about my actual problem? Do others think that this is correct behavior?

The reason nobody is commenting is that it's hard to argue about "correct", all I can say is that it "is" the behavior.

[EDIT: Redacting all the rest of this message since it was based on an assumption that the reported error was for the reported problem, which wasn't the case, and not checking the Schema properly. Once checked properly, the real reason showed itself. See messages posted later in this thread.]

The schema has to parse. This is checked often, but not constantly (because there is a processing cost to it). One such check is right BEFORE it's being altered.

If you drop table "type", the schema parses just fine before that alteration.

Once it is dropped, the schema no longer parses, because you have now broken the schema, so any next attempt will be met by the given error.

Would it be more helpful if the schema error was evident directly after the remove table? Perhaps, but that would need another schema parse which, as I've mentioned, is expensive, so it's reasonable to choose not to do it.
Further to this, would such an error prevent the operation from completing? Should it? What if you have two tables with cross references, A cannot be dropped because B, and B cannot be dropped because A... that's essentially the same problem but now caused by the proposed rule in stead of the current rule. I'd argue hat it's not preferred either, especially since the current "problem" can easily and simply be fixed by dropping tables in the correct order.

Whether that is acceptable or "correct" in some sense of the word is debatable and you are welcome to take exception with it, but that is neither here nor there.

Maybe it can be improved, and you mentioning this may actually cause someone to look at it and rethink it, even if they come to the same conclusion as before. For now though, this is how it works and how it was designed to work, and therefore in that sense, is correct.

(11) By tom (younique) on 2021-03-24 21:57:08 in reply to 10.0 [link] [source]

Thanks Ryan for your explanation. However, I do not really understand it. The reason why I posted this long schema is that I was unable to create a minimal example triggering the same error.

For example:

CREATE TABLE a (id INTEGER PRIMARY KEY UNIQUE NOT NULL);
CREATE TABLE b (id INTEGER PRIMARY KEY UNIQUE NOT NULL, a_id REFERENCES a(id));
CREATE TABLE c (id INTEGER PRIMARY KEY UNIQUE NOT NULL, b_id REFERENCES b(id));
DROP TABLE a; 
DROP TABLE c; -- works fine despite missing reference in table "b"

(12) By Ryan Smith (cuz) on 2021-03-25 00:09:46 in reply to 11 [link] [source]

Ah, I see now what confused you.

It wasn't the base references that broke - that is very much allowed.
Your schema breaks because of what you specified it should DO when removing entries for those references.

In other words, your original script (unlike this new one you've posted) included REFERENCES to columns with ON DELETE CASCADE and ON DELETE RESTRICT directives.

When you say DROP the "dataset" table, the first thing that is needed is to see if there are any entries in a referred table that will be affected, and sure enough, there is a "REFERENCES dataset(id)" entry in the "trace" table with an "ON DELETE CASCADE" directive, so it knows that any deletes from this drop have to be also deleted (via the id) in that "traces" table, but wait, does deleting from the "traces" table affect any other table? Well YES, there is a reference to the another table that says "ON DELETE RESTRICT", as in prevent the deletes from happening if they exist in the target table, and when checking THAT table, it sees that it doesn't exist anymore, and thus fails with that message.

That's why the schema is broken and that is why you get the error.

There's actually more than one problematic reference, but I've reduced it and made a minimal example that still breaks using your original tables (with any unimportant fields removed):

  -- SQLite version 3.35.2  [ Release: 2021-03-17 ]  on  SQLitespeed version 2.1.3.11.
  -- ================================================================================================
CREATE TABLE IF NOT EXISTS "dataset" (
  id INTEGER PRIMARY KEY AUTOINCREMENT
);

CREATE TABLE IF NOT EXISTS "trace" (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  datasetid INTEGER NOT NULL REFERENCES dataset(id) ON UPDATE CASCADE ON DELETE CASCADE,
  quantityid INTEGER NOT NULL REFERENCES quantity(id) ON UPDATE CASCADE ON DELETE RESTRICT
);

DROP TABLE IF EXISTS dataset;

  -- -------  DB-Engine Logs (Contains logged information from all DB connections during run)  ------
  -- [2021-03-25 01:23:11.007] QUERY       : Failed Query: DROP TABLE IF EXISTS dataset;  
  -- [2021-03-25 01:23:11.007] INTERFACE   : DB Error: no such table: main.quantity
  -- ================================================================================================

Note 1: If you change any of the ON DELETE CASCADE/RESTRICT directives, it all will work because a non-existing reference will be ignored, but a RESTRICT directive cannot be ignored (and the RESTRICT directive won't be activated if the CASCADE directive wasn't there signalling that deletes may happen). Also, the Query engine cannot know that the table is empty or there will be no rows affected at the time of preparing the statement, it only sees that it "should" check for deletes and restrict it where needed, but putting that mechanism in place fails because that referenced table doesn't exist.

Note 2: Dropping the "type" table or not is irrelevant to the problem. Dropping the "trace" table first (with the RESTRICT directive) also fixes the problem.

(13) By tom (younique) on 2021-03-25 23:36:23 in reply to 12 [link] [source]

Thanks for your detailed explanation. It's more clear to me now.

(5) By Keith Medcalf (kmedcalf) on 2021-03-23 21:29:58 in reply to 3 [link] [source]

Effectively this is "underwear suspenders" to protect people who do not understand the implications of what they are doing.

I consider this a bad decision also however it is entirely possible to write a UDF that reports that it has "constant" output that bypasses this deficiency.

(6) By Larry Brasfield (larrybr) on 2021-03-23 22:06:07 in reply to 5 [link] [source]

I think some of the difficulty with non-deterministic functions used in virtual column values is that it can cause an index on such columns to become invalid without the DBMS being able to discern that it needs modification. It's a concern that is orthogonal to whether users know what they are doing.

(7.2) By Keith Medcalf (kmedcalf) on 2021-03-23 22:50:23 edited from 7.1 in reply to 3 [link] [source]

Here is sample extension code that reports itself as deterministic but returns a constant unix epoch floating point timestamp (for Windows) that attempts to be statement stable by caching its result using the auxdata capability.

/*
** TimeStamp function returns a floating point unix epoch time.
** It calls the Windows PreciseTime API directly and therefore has a
** theoretical precision of 100 nanoseconds for the source time
** reduced and bounded by the IEEE floating point conversion.
**
** This value will attempt to preserve its value across invocations
** by attaching the value as auxdata to the single required parameter
** that should be a statement compile time constant, there is no way
** to ensure that this requirement is being met.
*/

#include <windows.h>
#include "sqlite3ext.h"
SQLITE_EXTENSION_INIT1

static void _TimeStamp(sqlite3_context *context, int argc, sqlite3_value **argv)
{
    sqlite_int64 huns = 0;
    double *timestamp = sqlite3_get_auxdata(context, 0);

    if (!timestamp)
    {
        timestamp = sqlite3_malloc(sizeof(double));
        if (!timestamp)
        {
            sqlite3_result_error_nomem(context);
            return;
        }
        GetSystemTimePreciseAsFileTime((void*)&huns);
        *timestamp =  ((double)(huns - 116444736000000000ll)) / 1.0e7;
    }
    sqlite3_result_double(context, *timestamp);
    if (huns)
        sqlite3_set_auxdata(context, 0, timestamp, sqlite3_free);
}

#ifdef _WIN32
#ifndef SQLITE_CORE
__declspec(dllexport)
#endif
#endif
int sqlite3_timestamp_init(sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi)
{
    SQLITE_EXTENSION_INIT2(pApi);
    return sqlite3_create_function(db, "TimeStamp", 1, SQLITE_UTF8|SQLITE_DETERMINISTIC|SQLITE_INNOCUOUS, 0, _TimeStamp, 0, 0);
}

(8) By Keith Medcalf (kmedcalf) on 2021-03-24 02:00:43 in reply to 3 [link] [source]

Here is a better version that should work on both Linux (POSIX) and Windows and has two functions UnixStamp and JulianStamp which return the Unix Epoch Timestamp and Julian Day Epoch Timestamp. You can enable correct rounding if you wish, otherwise the results will be returned to the maximum precision supported.

#ifdef _WIN32
#include <windows.h>
#else
#include <time.h>
/* math.h is required is we want to round results to the millisecond */
#if 0
#include <math.h>
#endif
#endif
// #include <math.h>
#include "sqlite3ext.h"
SQLITE_EXTENSION_INIT1

/*
** UnixStamp function returns a floating point unix epoch time.
** JulianStamp function returns a floating point julian date.
**
** It calls the Windows PreciseTime API directly and therefore
** has a theoretical precision of 100 nanoseconds for the source
** time reduced and bounded by the IEEE floating point conversion.
**
** On non-Windows it uses the POSIX clock_gettime call and therefore
** has a theretical precision of 1 nanosecond, or as provided by the
** underlying Operating System, for the source time reduced and
** bounded by the IEEE floating point conversion.
**
** This function will attempt to preserve its value across invocations
** by attaching the value as auxdata to the single required parameter
** that should be a statement compile time constant, although there
** is no way to ensure that this requirement is being met.
**
** Using this function improperly may result in the heat death
** of the multiverse.  Consider yourself forewarned.
*/

static void _TimeStamp(sqlite3_context *context, int argc, sqlite3_value **argv)
{
    sqlite_int64 huns = 0;
    double *timestamp = sqlite3_get_auxdata(context, 0);

    if (!timestamp)
    {
        timestamp = sqlite3_malloc(sizeof(double));
        if (!timestamp)
        {
            sqlite3_result_error_nomem(context);
            return;
        }
#ifdef _WIN32
        GetSystemTimePreciseAsFileTime((void*)&huns);
        *timestamp =  ((double)(huns  - 116444736000000000ll)) / 1.0e7;
#else
        {
            struct timespec t;

            if (clock_gettime(CLOCK_REALTIME, &t) == -1)
            {
                sqlite3_result_error(context, "Cannot get time", -1);
                return;
            }
            huns = 1;
            *timestamp = (double)t.tv_sec + (double)t.tv_nsec / 1.0e9;
        }
#endif
        /* Optional to correctly round the result to the millisecond */
        #if 0
        *timestamp *= 1000.0;
        *timestamp -= remainder(*timestamp, 1.0);
        *timestamp /= 1000.0;
        #endif
        if ((intptr_t)sqlite3_user_data(context))
        {
            *timestamp = *timestamp / 86400.0 + 2440587.5;
        }
    }
    sqlite3_result_double(context, *timestamp);
    if (huns)
    {
        sqlite3_set_auxdata(context, 0, timestamp, sqlite3_free);
    }
}


#ifdef _WIN32
#ifndef SQLITE_CORE
__declspec(dllexport)
#endif
#endif
int sqlite3_timestamp_init(sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi)
{
    int nErr = 0;
    SQLITE_EXTENSION_INIT2(pApi);

    nErr += sqlite3_create_function(db, "UnixStamp",   1, SQLITE_UTF8|SQLITE_DETERMINISTIC|SQLITE_INNOCUOUS, (void*)0, _TimeStamp, 0, 0);
    nErr += sqlite3_create_function(db, "JulianStamp", 1, SQLITE_UTF8|SQLITE_DETERMINISTIC|SQLITE_INNOCUOUS, (void*)1, _TimeStamp, 0, 0);

    return nErr ? SQLITE_ERROR : SQLITE_OK;
}