SQLite Forum

.selecttrace unknown and strange errors
Login

.selecttrace unknown and strange errors

(1) By anonymous on 2020-07-08 07:33:06 [link] [source]

Introduction to the problem
SQLite has a number of issues that challenges a programmer to control how the database processes data with triggers. To illustrate the behavior of the optimizer function I’ve created a script with a deliberate error in one of the triggers. The script creates two tables and two update triggers. Here is the script a2.sql:
-- Automatic ANALYZE
PRAGMA optimize;

CREATE TABLE IF NOT EXISTS A1 (
    A1_INDEX VARCHAR(20),
    A1_CAT VARCHAR(1) DEFAULT 'W',
    A1_STOCK_MIN INTEGER,
    A1_STOCK_LEVEL INTEGER,
    A1_PRODUCT TEXT,
    A1_STATUS NUMBER
        CHECK (A1_STATUS IN (1, 2, 4, 8, 16, 32, 64, 128)) DEFAULT 1,
    A1_REF VARCHAR(20) CHECK (A1_REF != A1_INDEX),
    A1_DTS_CREATED TEXT NOT NULL DEFAULT (DATETIME('NOW', 'UTC')),
    A1_DTS_UPDATED TEXT NOT NULL DEFAULT (DATETIME('NOW', 'UTC')),
    CONSTRAINT A1_PK PRIMARY KEY (
        A1_INDEX,
        A1_CAT
    )
--    UNIQUE (
--      A1_INDEX,
--        A1_CAT,
--    ) ON CONFLICT ROLLBACK
);

-- PRODUCTION STATUS:
-- 1 →  PREPARED FOR PRODUCTION
-- 2 →  IN PRODUCTIION
-- 4 →  PRODUCTION FINISHED
-- 128 → PRODUCTION BATCH REJECTED
--
-- STATUS
-- 1 →  PLANNING PROCESSING
-- 2 →  PROCESSING
-- 4 →  EVALUTION
-- 8 →  PROCESSED
CREATE TABLE IF NOT EXISTS C1 (
    C1_INDEX VARCHAR(20),
    C1_PRODUCTION_ORDER VARCHAR(20),
    C1_QTY_TO_PRODUCE NUMBER DEFAULT 0,
    C1_QTY_PRODUCED NUMBER DEFAULT 0,
    C1_PRODUCTION_STATUS NUMBER
        CHECK (C1_PRODUCTION_STATUS IN (1, 2, 4, 128)),
    C1_STATUS NUMBER
        CHECK (C1_STATUS IN (1, 2, 4, 8)),
    C1_DTS_CREATED TEXT NOT NULL DEFAULT (DATETIME('NOW', 'UTC')),
    C1_DTS_UPDATED TEXT NOT NULL DEFAULT (DATETIME('NOW', 'UTC')),
    CONSTRAINT C1_PK PRIMARY KEY (
        C1_INDEX
    ) ON CONFLICT ROLLBACK,
    CONSTRAINT C1_INDEX_FK FOREIGN KEY (
        C1_INDEX
    )
    REFERENCES A1 (
        A1_INDEX
    )
);

-- Create stock
INSERT INTO A1 (A1_INDEX, A1_STOCK_MIN, A1_STOCK_LEVEL, A1_PRODUCT, A1_REF)
VALUES
        (12, cast(abs((0.0661 * random()) % 997 +1) as integer), ABS(RANDOM() % 14731) + 1, 'Drei im Weggla', NULL),
        ( 7, cast(abs((0.0661 * random()) % 997 +1) as integer), ABS(RANDOM() % 14731) + 1, 'Blaue Zipfel mit Brezel', NULL),
        (17, cast(abs((0.0661 * random()) % 997 +1) as integer), ABS(RANDOM() % 14731) + 1, 'Nürnberger Rostbratwurst', NULL),
        (14, cast(abs((0.0661 * random()) % 997 +1) as integer), ABS(RANDOM() % 14731) + 1, 'Brätwurst', 17),
        (19, cast(abs((0.0661 * random()) % 997 +1) as integer), ABS(RANDOM() % 14731) + 1, 'Brühwürste', NULL);

CREATE TRIGGER IF NOT EXISTS A1_100 BEFORE UPDATE ON A1
WHEN (NEW.A1_STATUS = 2) AND (NEW.A1_REF ISNULL)
BEGIN
        SELECT CASE 1
                WHEN (NEW.A1_STATUS == 2)
                        THEN CASE 1
                                WHEN (NOT (OLD.A1_STATUS IN (1, 8, 32, 64))) OR (((OLD.A1_REF NOT NULL)) AND (NOT (OLD.A1_STATUS == 4)))
                                        THEN RAISE (ABORT, 'Error: someone goofed')
                                WHEN ((SELECT C1_STATUS FROM C1 WHERE C1_INDEX == NEW.A1_INDEX) == 1)
                                        THEN RAISE (ABORT, 'Error you are not allowed to change the status')
                                END
                        END
                END;

        UPDATE OR ROLLBACK A1 SET
                A1_STATUS = NEW.A1_STATUS
        WHERE A1_INDEX = NEW.A1_INDEX;

END;

-- Finished the production of sauceges
CREATE TRIGGER IF NOT EXISTS C1_100 BEFORE UPDATE ON A1
WHEN (NEW.C1_PRODUCTIONS_STATUS = 8)
BEGIN
        SELECT CASE 1
                WHEN (NEW.C1_QUANTITY_PRODUCED == 0)
                        THEN RAISE (ABORT, 'Error: someone goofed')
                WHEN (NEW.C1_QTY__TO_PRODUCE == 0)
                        THEN RAISE (ABORT, 'Error there no sausages are ordered!')
        END;

        UPDATE OR ROLLBACK A1 SET
                A1_STOCK_LEVEL = A1_STOCK_LEVEL + C1_QTY_PRODUCED
        WHERE A1_INDEX = NEW.C1_INDEX;

        UPDATE OR ROLLBACK C1 SET
                C1_QTY_TO_PRODUCE = 0,
                C1_QTY_PRODUCED = 0,
                C1_STATUS = 1,
                C1_PRODUCTION_STATUS = 1   -- CLEAN AND RESET THE PRODUCTION CHAIN
        WHERE C1_INDEX == NEW.C1_INDEX;
END;

-- Create a production order
INSERT INTO C1 (C1_INDEX, C1_PRODUCTION_ORDER, C1_QTY_TO_PRODUCE, C1_QTY_PRODUCED, C1_PRODUCTION_STATUS, C1_STATUS)
VALUES
        (12, '07072020_C', ABS(RANDOM() % 2137) + 1, 0, 1, 1),
        (19, '07072020_B', ABS(RANDOM() % 2137) + 1, 0, 1, 1);
The trigger  C1_100 has a syntax error. The variable NEW.C1_PRODUCTIONS_STATUS doesn’t exist. This deliberate error is part of the test. 
Build the database with the following command:
sqlite3 a2.db <  a2.sql                                                             
∎
Test a1.sql
This test updates the product status in table A1 to indicate that the production is being planned. The script for test_a1.sql looks like this:
.selecttrace 0xfffff
UPDATE A1 SET
A1_STATUS = 1 
WHERE A1_INDEX == 14;
Execute the script as follows:
sqlite3 a2.db < test_a1.sql | tee result_test_a1.txt
SQLite abends the execution of the test and shows the following freaky error messages:
Error: unknown command or invalid arguments:  "selecttrace". Enter ".help" for help
Error: near line 3: no such column: NEW.C1_PRODUCTIONS_STATUS
∎

Observations
I’ve made the following observations:
    • The syntax error in trigger C1_100 should have been detected while SQLite is building the database;
    • The .selecttrace was initially fixed but after update Ubuntu the command is no longer working;
    • Why is SQLite scanning also trigger C_100 when an update effects only table A1.

(2) By TripeHound on 2020-07-08 16:07:13 in reply to 1 [link] [source]

The syntax error in trigger C1_100 should have been detected while SQLite is building the database;

I would guess the SQL in a trigger only gets compiled/prepared when they come into play. Similar to how missing columns in views don't get complained about until you try to access the view.

The .selecttrace was initially fixed but after update Ubuntu the command is no longer working;

Presumably the Ubuntu update replaced your SQLite binary with a newer version. From the Debugging Hints page, since you need to compile with -DSQLITE_ENABLE_SELECTTRACE, and this isn't a documented (or officially supported option), the new version presumably doesn't have it enabled (my off-the-shelf Windows version doesn't).

Why is SQLite scanning also trigger C_100 when an update effects only table A1.

Trigger C1_100 (presumably C_100 is a typo) is defined as BEFORE UPDATE ON A1 ... why would you expect SQLite not to process this trigger?

I also fail to see anything "freaky" about the error message: it tells you that .selecttrace isn't available and that table A1 does not contain a column C1_PRODUCTIONS_STATUS. (Neither does it contain C1_PRODUCTION_STATUS ... I'm assuming you meant to create the trigger on C1).

(3) By anonymous on 2020-07-09 04:54:14 in reply to 2 [link] [source]

The syntax error in trigger C1_100 should have been detected while SQLite is building the database;

I would guess the SQL in a trigger only gets compiled/prepared when they come into play. Similar to how missing columns in views don't get complained about until you try to access the view.
—> The assumption that SQLite only scans the triggers if they come into play not correct. SQLite parses everything and stores the trigger metadata in a system table. Checking that the variables are valid names and existing column names is easy and would improve the trustworthiness of the SQLite build process.


    The .selecttrace was initially fixed but after update Ubuntu, the command is no longer working;

Presumably, the Ubuntu update replaced your SQLite binary with a newer version. From the Debugging Hints page, since you need to compile with -DSQLITE_ENABLE_SELECTTRACE, and this isn't a documented (or officially supported option), the new version presumably doesn't have it enabled (my off-the-shelf Windows version doesn't).

—> Nope. SQLite wasn't updated. I am running a debug version as was instructed by Mr. Hipp in the local directory. ./sqlite3 …
   

    Why is SQLite scanning also trigger C_100 when update affects only table A1.
  
Trigger C1_100 (presumably C_100 is a typo) is defined as BEFORE UPDATE ON A1 ... why would you expect SQLite not to process this trigger?

—> Oops, I goofed up. I am trying to build an example to reproduces a number of things related with the optimizer function and for that I need .selecttrace.

So I changed the A1 to C1 as it should have been and executed test_a2.sql.
Success! Nice! Let us repeat test_a2 setting the status to 2.
So the modified test is:
.selecttrace 0xfffff

UPDATE A1 SET
A1_STATUS = 2
WHERE A1_INDEX == 19;

This fails because OLD.A1_STATUS  and NEW.A1_STATUS  are the same. The Old status is 1 and the new status is 2.If I could execute .selecttrace you would have seen that old and new have the same value.

(4) By Keith Medcalf (kmedcalf) on 2020-07-09 05:59:39 in reply to 3 [link] [source]

Your A1_100 trigger is weird.

Do you not simply mean:

CREATE TRIGGER IF NOT EXISTS A1_100 BEFORE UPDATE ON A1
WHEN NEW.A1_STATUS == 2 AND NEW.A1_REF IS NULL
BEGIN
    SELECT RAISE (ABORT, 'Error: someone goofed')
      WHEN (NOT (OLD.A1_STATUS IN (1, 8, 32, 64))) 
        OR (((OLD.A1_REF IS NOT NULL)) AND (NOT (OLD.A1_STATUS == 4)));
    SELECT RAISE (ABORT, 'Error you are not allowed to change the status')
      FROM C1 
     WHERE C1_INDEX == NEW.A1_INDEX;
END;

or are you deliberately recursively recursing the recursive trigger for some recursive reason that is not obviously recursing?

(5) By anonymous on 2020-07-09 06:05:13 in reply to 4 [link] [source]

I fail to reproduce the error with OLD and NEW. The Ubuntu update has changed something.
Time to figure out what has changed.

(6) By Stephan Beal (stephan) on 2020-07-09 06:11:50 in reply to 5 updated by 6.1 [link] [source]

> I fail to reproduce the error with OLD and NEW. The Ubuntu update has changed something.

You wrote earlier:

> Nope. SQLite wasn't updated. I am running a debug version as was instructed by Mr. Hipp in the local directory. ./sqlite3

That's only the shell, not the library. If if's linked to the Ubuntu-installed `libsqlite3` then you are still using the one Ubuntu installed library. The easiest way to determine whether that's the case is to run:

```
ldd ./sqlite3
```

From the directory where you have your sqlite3, to check which libraries it's linking against.

Also don't forget that "." is *not* in the PATH on Linux by default like it is on Windows (and, generally speaking, should never be added to the PATH on Unix-style systems). What that means is that if you are running it like:

```
sqlite3 ...
```

instead of:

```
./sqlite3 ...
```

then you are almost certainly running the system-installed version instead of your local copy.

(6.1) By Stephan Beal (stephan) on 2020-07-09 06:13:08 edited from 6.0 in reply to 5 [link] [source]

I fail to reproduce the error with OLD and NEW. The Ubuntu update has changed something.

You wrote earlier:

Nope. SQLite wasn't updated. I am running a debug version as was instructed by Mr. Hipp in the local directory. ./sqlite3

That's only the shell, not the library. If if's linked to the Ubuntu-installed libsqlite3 then you are still using the library Ubuntu installed. The easiest way to determine whether that's the case is to run:

ldd ./sqlite3

From the directory where you have your sqlite3, to check which libraries it's linking against.

Also don't forget that "." is not in the PATH on Linux by default like it is on Windows (and, generally speaking, should never be added to the PATH on Unix-style systems). What that means is that if you are running it like:

sqlite3 ...

instead of:

./sqlite3 ...

then you are almost certainly running the system-installed version instead of your local copy.

(7) By anonymous on 2020-07-09 07:41:28 in reply to 6.1 [link] [source]

First step rebuild sqlite from snapshot 202006241145 using this script:
usage="Usage : '$0 ' [-ahbd]"

if [[ $1 =~ (-[abdh]) ]]; then
        echo "--------------"
else
        echo "Not a valid option is given!"
        echo $Usage
        exit 1
fi

while getopts "abdh" opt; do
  case ${opt} in
    h )
        echo $usage
        echo "Options:"
        echo "    -b            Compile without debug options"
        echo "    -d            Compile with debug options"
        echo "    -h            This menu for help"
        exit 0
        ;;
    a )
        echo "Building a debug version of SQLite3 with all options"
        ./configure --enable-debug --enable-all
        make
        exit 0
       ;; 
    b )
        echo "Building SQLite3 without debug options"
        export CFLAGS="-fPIC \
                -DSQLITE_ENABLE_FTS3  \
                -DSQLITE_ENABLE_FTS3_PARENTHESIS  \
                -DSQLITE_ENABLE_FTS4 \
                -DSQLITE_ENABLE_FTS5 \
                -DSQLITE_ENABLE_JSON1 \
                -DSQLITE_ENABLE_LOAD_EXTENSION \
                -DSQLITE_ENABLE_RTREE \
                -DSQLITE_ENABLE_STAT4 \
                -DSQLITE_ENABLE_UPDATE_DELETE_LIMIT \
                -DSQLITE_TEMP_STORE3 \
                -DSQLITE_USE_URI \
                -O2 "
        ;;
    d )
        echo "Bulding SQLite3 with debug options"
        export SQLITE3_CFLAGS="-fPIC \
                -DSQLITE_ENABLE_FTS3 \
                -DSQLITE_ENABLE_FTS3_PARENTHESIS  \
                -DSQLITE_ENABLE_FTS4 \
                -DSQLITE_ENABLE_FTS5 \
                -DSQLITE_ENABLE_JSON1 \
                -DSQLITE_ENABLE_LOAD_EXTENSION \
                -DSQLITE_ENABLE_RTREE \
                -DSQLITE_ENABLE_STAT4 \
                -DSQLITE_ENABLE_UPDATE_DELETE_LIMIT \
                -DSQLITE_TEMP_STORE3 \
                -DSQLITE_USE_URI \
                -DSQLITE_DEBUG \
                -DSQLITE_ENABLE_EXPLAIN_COMMENTS \
                -DSQLITE_ENABLE_SELECTTRACE \
                -DSQLITE_ENABLE_WHERETRACE \
                -O2"
        ;;
    * )
        echo "Invalid Option: -$OPTARG" 
        echo $usage >&2
        exit 1
      ;;
  esac
done

LIBS="-lm" ./configure --enable-shared --prefix="$PREFIX"

make

sudo make install

By the way the -DSQLITE options does not work.
Changed the name of SQLite3 to avoid confusing and …
$ ldd ./sqlite3_33_0 
        linux-vdso.so.1 (0x00007ffd32981000)
        libreadline.so.5 => /lib/x86_64-linux-gnu/libreadline.so.5 (0x00007efceddc4000)
        libz.so.1 => /lib/x86_64-linux-gnu/libz.so.1 (0x00007efcedda8000)
        libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007efcedc59000)
        libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007efcedc53000)
        libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007efcedc30000)
        libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007efceda3e000)
        libtinfo.so.6 => /lib/x86_64-linux-gnu/libtinfo.so.6 (0x00007efceda0c000)
        /lib64/ld-linux-x86-64.so.2 (0x00007efcee057000)

Presto: .selecttrace is back but I am still not able to reproduce the issue.
Here is the last part of the .selecttrace
                    |   '-- 1
                            '-- RAISE abort('Error you are not allowed to change the status')
2/0/55CC014018C8: WhereBegin
1/82/55CC01420BC8: begin processing:
'-- SELECT (1/55CC01420BC8) selFlags=0xc4 nSelectRow=0
    |-- result-set
    |   '-- {0:5} pTab=55CC0141C208 fg.af=40800000.n DDL
    |-- FROM
    |   '-- {0:*} C1 tab='C1' nCol=8 ptr=55CC0141C208 used=21 DDL
    |-- WHERE
    |   '-- EQ fg.af=40000000.n DDL
    |       |-- {0:0} pTab=55CC0141C208 fg.af=40800000.n DDL
    |       '-- NEW(0)
    '-- LIMIT
        '-- 1
1/82/55CC01420BC8: after name resolution:
'-- SELECT (1/55CC01420BC8) selFlags=0xc4 nSelectRow=0
    |-- result-set
    |   '-- {0:5} pTab=55CC0141C208 fg.af=40800000.n DDL
    |-- FROM
    |   '-- {0:*} C1 tab='C1' nCol=8 ptr=55CC0141C208 used=21 DDL
    |-- WHERE
    |   '-- EQ fg.af=40000000.n DDL
    |       |-- {0:0} pTab=55CC0141C208 fg.af=40800000.n DDL
    |       '-- NEW(0)
    '-- LIMIT
        '-- 1
1/82/55CC01420BC8: Constant propagation not helpful
1/82/55CC01420BC8: After all FROM-clause analysis:
'-- SELECT (1/55CC01420BC8) selFlags=0xc4 nSelectRow=0
    |-- result-set
    |   '-- {0:5} pTab=55CC0141C208 fg.af=40800000.n DDL
    |-- FROM
    |   '-- {0:*} C1 tab='C1' nCol=8 ptr=55CC0141C208 used=21 DDL
    |-- WHERE
    |   '-- EQ fg.af=40000000.n DDL
    |       |-- {0:0} pTab=55CC0141C208 fg.af=40800000.n DDL
    |       '-- NEW(0)
    '-- LIMIT
        '-- 1
1/82/55CC01420BC8: WhereBegin
1/82/55CC01420BC8: end processing
'-- SELECT (1/55CC01420BC8) selFlags=0x40c4 nSelectRow=0
    |-- result-set
    |   '-- {0:5} pTab=55CC0141C208 fg.af=40800000.n DDL
    |-- FROM
    |   '-- {0:*} C1 tab='C1' nCol=8 ptr=55CC0141C208 used=21 DDL
    |-- WHERE
    |   '-- EQ fg.af=40000000.n DDL
    |       |-- {0:0} pTab=55CC0141C208 fg.af=40800000.n DDL
    |       '-- NEW(0)
    '-- LIMIT
        '-- 1
2/0/55CC014018C8: end processing
'-- SELECT (2/55CC014018C8) selFlags=0x4400c4 nSelectRow=0
    '-- result-set
        '-- AS END
            '-- CASE
                |-- 1
                '-- LIST
                    |-- EQ fg.af=40000000.n DDL
                    |   |-- NEW(5)
                    |   '-- 2
                    '-- CASE
                        |-- 1
                        '-- LIST
                            |-- OR fg.af=40000000.n DDL
                            |   |-- IN flags=0x42000000
                            |   |   |-- OLD(5)
                            |   |   '-- LIST
                            |   |       |-- 1
                            |   |       |-- 8
                            |   |       |-- 32
                            |   |       '-- 64
                            |   '-- AND fg.af=40000000.n DDL
                            |       |-- NOTNULL fg.af=40000000.n DDL
                            |       |   '-- OLD(6)
                            |       '-- NOT fg.af=40000000.n DDL
                            |           '-- EQ fg.af=40000000.n DDL
                            |               |-- OLD(5)
                            |               '-- 4
                            |-- RAISE abort('Error: someone goofed')
                            |-- EQ fg.af=40200000.n DDL
                            |   |-- subquery-expr flags=0x42200800
                            |   |   '-- SELECT (1/55CC01420BC8) selFlags=0x40c4 nSelectRow=0
                            |   |       |-- result-set
                            |   |       |   '-- {0:5} pTab=55CC0141C208 fg.af=40800000.n DDL
                            |   |       |-- FROM
                            |   |       |   '-- {0:*} C1 tab='C1' nCol=8 ptr=55CC0141C208 used=21 DDL
                            |   |       |-- WHERE
                            |   |       |   '-- EQ fg.af=40000000.n DDL
                            |   |       |       |-- {0:0} pTab=55CC0141C208 fg.af=40800000.n DDL
                            |   |       |       '-- NEW(0)
                            |   |       '-- LIMIT
                            |   |           '-- 1
                            |   '-- 1
                            '-- RAISE abort('Error you are not allowed to change the status')

(8) By Adrian Ho (lexfiend) on 2020-07-09 08:31:23 in reply to 7 updated by 8.1 [link] [source]

> By the way the -DSQLITE options does not work.

If you're talking about your debug build, your build script defines `SQLITE3_CFLAGS`, which isn't recognized.

(9) By Keith Medcalf (kmedcalf) on 2020-07-09 08:56:32 in reply to 7 [link] [source]

Seems to work just fine if foreign_keys are disabled, recursive triggers are disabled, and the recursive recursiveness in your trigger is fixed (by removing the superfluous update).

One would presume that the reason you want to have all these things is so that you can have the database engine enforce integrity and do things for you. Thus you should concentrate on fixing your schema and triggers first since your script does not run at all if foreign key enforcement is enabled.

And of course it never updates anything because you are recursively updating the same table you are updating in the trigger that runs before the update in which you are performing and update which would ABORT but rather otherwise does a rollback because it is recursing recursively and you have overridden the CONFLICT resolution method in the update that runs within the before update trigger.

Updating the record which will be updated in a before update trigger causes either infinite recursion or "undefined behaviour".

(8.1) By Adrian Ho (lexfiend) on 2020-07-09 09:44:43 edited from 8.0 in reply to 7 [link] [source]

By the way the -DSQLITE options does not work.

If you're talking about your debug build, your build script defines them in SQLITE3_CFLAGS, which isn't recognized.

(10) By anonymous on 2020-07-09 11:44:17 in reply to 9 [link] [source]

Thx.For the comment.
That's precisely what I am doing.

Updating the record which will be updated in a before update trigger causes either infinite recursion or "undefined behaviour. 
—> valid argument. So here is the present version
-- character encoding
PRAGMA encoding = 'UTF-16le';

-- Journalling mode
PRAGMA journal_mode=WAL;

-- Automatic ANALYZE
PRAGMA optimize;

-- STATUS
-- 1 →  IN STOCK
-- 2 →  NEW STOCK IN PRODUCTION
-- 4 →  BLOCKED STOCK
-- 128 → PRODUCT NO LONGER PRODUCED
CREATE TABLE IF NOT EXISTS A1 (
    A1_INDEX VARCHAR(20),
    A1_CAT VARCHAR(1) DEFAULT 'W',
    A1_STOCK_MIN INTEGER,
    A1_STOCK_LEVEL INTEGER,
    A1_PRODUCT TEXT,
    A1_STATUS NUMBER
        CHECK (A1_STATUS IN (1, 2, 4, 8, 16, 32, 64, 128)) DEFAULT 1,
    A1_REF VARCHAR(20) CHECK (A1_REF != A1_INDEX),
    A1_DTS_CREATED TEXT NOT NULL DEFAULT (DATETIME('NOW', 'UTC')),
    A1_DTS_UPDATED TEXT NOT NULL DEFAULT (DATETIME('NOW', 'UTC')),
    CONSTRAINT A1_PK PRIMARY KEY (
        A1_INDEX,
        A1_CAT
    )
--    UNIQUE (
--      A1_INDEX,
--        A1_CAT,
--    ) ON CONFLICT ROLLBACK
);

-- PRODUCTION STATUS:
-- 1 →  PREPARED FOR PRODUCTION
-- 2 →  IN PRODUCTIION
-- 4 →  PRODUCTION FINISHED
-- 128 → PRODUCTION BATCH REJECTED
--
-- STATUS
-- 1 →  PLANNING PROCESSING
-- 2 →  PROCESSING
-- 4 →  EVALUTION
-- 8 →  PROCESSED
CREATE TABLE IF NOT EXISTS C1 (
    C1_INDEX VARCHAR(20),
    C1_PRODUCTION_ORDER VARCHAR(20),
    C1_QTY_TO_PRODUCE NUMBER DEFAULT 0,
    C1_QTY_PRODUCED NUMBER DEFAULT 0,
    C1_PRODUCTION_STATUS NUMBER
        CHECK (C1_PRODUCTION_STATUS IN (1, 2, 4, 128)),
    C1_STATUS NUMBER
        CHECK (C1_STATUS IN (1, 2, 4, 8)),
    C1_DTS_CREATED TEXT NOT NULL DEFAULT (DATETIME('NOW', 'UTC')),
    C1_DTS_UPDATED TEXT NOT NULL DEFAULT (DATETIME('NOW', 'UTC')),
    CONSTRAINT C1_PK PRIMARY KEY (
        C1_INDEX
    ) ON CONFLICT ROLLBACK,
    CONSTRAINT C1_INDEX_FK FOREIGN KEY (
        C1_INDEX
    )
    REFERENCES A1 (
        A1_INDEX
    )
);

-- Create stock
INSERT INTO A1 (A1_INDEX, A1_STOCK_MIN, A1_STOCK_LEVEL, A1_PRODUCT, A1_REF)
VALUES
        (12, cast(abs((0.0661 * random()) % 997 +1) as integer), ABS(RANDOM() % 14731) + 1, 'Drei im Weggla', NULL),
        ( 7, cast(abs((0.0661 * random()) % 997 +1) as integer), ABS(RANDOM() % 14731) + 1, 'Blaue Zipfel mit Brezel', NULL),
        (17, cast(abs((0.0661 * random()) % 997 +1) as integer), ABS(RANDOM() % 14731) + 1, 'Nürnberger Rostbratwurst', NULL),
        (14, cast(abs((0.0661 * random()) % 997 +1) as integer), ABS(RANDOM() % 14731) + 1, 'Brätwurst', 17),
        (19, cast(abs((0.0661 * random()) % 997 +1) as integer), ABS(RANDOM() % 14731) + 1, 'Brühwürste', NULL);

CREATE TRIGGER IF NOT EXISTS A1_100 BEFORE UPDATE ON A1
WHEN (NEW.A1_STATUS = 2) AND (NEW.A1_REF ISNULL)
BEGIN
        SELECT CASE 1
                WHEN (NEW.A1_STATUS == 2)
                        THEN CASE 1
                                WHEN (NOT (OLD.A1_STATUS IN (1, 128))) OR (((OLD.A1_REF NOT NULL)) AND (NOT (OLD.A1_STATUS == 4)))
                                        THEN RAISE (ABORT, 'Error: someone goofed')
                                WHEN ((SELECT C1_STATUS FROM C1 WHERE C1_INDEX == NEW.A1_INDEX) != 4)
                                        THEN RAISE (ABORT, 'Error you are not allowed to change the status')
                                END
                        END
                END;

        -- Signal processing
        UPDATE OR ROLLBACK C1 SET
                C1_STATUS = NEW.A1_STATUS
        WHERE C1_INDEX == NEW.A1_INDEX;

END;

-- Let us make sauceges
CREATE TRIGGER IF NOT EXISTS C1_101 BEFORE UPDATE ON C1
WHEN (NEW.C1_PRODUCTION_STATUS == 2)
BEGIN

        SELECT CASE 1
                WHEN (OLD.C1_STATUS != 1)
                        THEN RAISE (ABORT, 'Error, you need to plan the production first')
                WHEN (SELECT A1_STATUS FROM A1 WHERE A1_INDEX == NEW.C1_INDEX) IN (2, 4, 128)
                        THEN RAISE (ABORT, 'Error, production order is invalid')
                END;


        --Keep the status in sync
        -- Signal new stock in production
        UPDATE OR ROLLBACK A1 SET
                A1_STATUS = 2
        WHERE A1_INDEX == NEW.C1_INDEX;

END;

-- Finished the production of sauceges
CREATE TRIGGER IF NOT EXISTS C1_110 BEFORE UPDATE ON C1
WHEN (NEW.C1_PRODUCTIONS_STATUS == 8)
BEGIN
        SELECT CASE 1
                WHEN (NEW.C1_QUANTITY_PRODUCED == 0)
                        THEN RAISE (ABORT, 'Error: someone goofed')
                WHEN (NEW.C1_QTY__TO_PRODUCE == 0)
                        THEN RAISE (ABORT, 'Error there no sausages are ordered!')
        END;

        UPDATE OR ROLLBACK A1 SET
                A1_STOCK_LEVEL = A1_STOCK_LEVEL + C1_QTY_PRODUCED
        WHERE A1_INDEX = NEW.C1_INDEX;

        UPDATE OR ROLLBACK C1 SET
                C1_QTY_TO_PRODUCE = 0,
                C1_QTY_PRODUCED = 0,
                C1_STATUS = 1,
                C1_PRODUCTION_STATUS = 1   -- CLEAN AND RESET THE PRODUCTION CHAIN
        WHERE C1_INDEX == NEW.C1_INDEX;

        -- SYNC THE STATUS
        UPDATE OR ROLLBACK A1 SET
                A1_STATUS = (SELECT C1_STATUS FROM C1 WHERE C1_INDEX == NEW.C1_INDEX)
        WHERE A1_INDEX = NEW.C1_INDEX;

END;

-- Create a production order
INSERT INTO C1 (C1_INDEX, C1_PRODUCTION_ORDER, C1_QTY_TO_PRODUCE, C1_QTY_PRODUCED, C1_PRODUCTION_STATUS, C1_STATUS)
VALUES
        (12, '07072020_C', ABS(RANDOM() % 2137) + 1, 0, 1, 1),
        (19, '07072020_B', ABS(RANDOM() % 2137) + 1, 0, 1, 1);

(11) By anonymous on 2020-07-09 11:45:47 in reply to 8.1 [link] [source]

How would you define these settings? The C compiler expects CFLAGS.

(12) By anonymous on 2020-07-09 12:02:42 in reply to 4 [link] [source]

Your proposal is the way to go. However, I am trying to build a small sample to demonstrate that the SQLite optimizer can wreak havoc if one is not careful.

The test case name is "The freaky, the ugly, and the bad". I am trying to condense a problem in a script of 8000 source lines into a few lines so you can help me. Alas, it's back to the drawing board.

The foreign key mismatch is strange. A1_INDEX and C1_INDEX are varchars (20) no mismatch. Unless something else is meant.

(13) By anonymous on 2020-07-09 13:34:30 in reply to 9 [link] [source]

Foreign keys...do not work. Whatever I try INTEGER or VARCHAR. It just doesn't work. The documentation states use a unique index. Nope. Doesn't work.

(14) By Keith Medcalf (kmedcalf) on 2020-07-09 20:22:34 in reply to 13 [link] [source]

You do not have a UNIQUE index on the parent A1 (A1_INDEX). This is required.

You do not have an index on the child C1 where the leftmost column is C1_INDEX.

The UNIQUE index on the parent is required (not optional -- without it, the constraint is invalid and will throw errors). The index on the child is only necessary if you want foreign key enforcement to use index lookup (fast) rather than table scans (slow).

The relationship parent:child must be 1:N. This means that the parent key must be constrained UNIQUE (or be the PRIMARY KEY, which is the same thing) in order to enforce the 1: part of the relationship. You need an index on the child key to be able to find children related to the parent unless you want to always be doing a brute-force table scan when enforcing integrity. If the child key columns are a candidate key (and thus constrained UNIQUE) then the :N part of the relationship is constrained :1, otherwise it is :N.

These requirements apply to every Relational Database Management System ever created and are not unique to SQLite3.

On a second note, SQLite3 (and some other RDBMS) permit or default to having recursive triggers disabled. This often leads to unclear thinking about the implementation of trigger programs in particular by failing to consider all the conditions which may cause the condition to fire.

Also note the order in which triggers fire. This is very important. When you do an operation on a "row" (insert/update/delete) the order of operations is as follows:

fire BEFORE triggers (for tables only)
perform OPERATION or, for views only, an INSTEAD OF trigger
fire AFTER triggers (for tables only)

The purpose of the BEFORE trigger is to "put the database into the state at which the requested operation can be completed successfully or to abort the operation if it is not possible for this state to exist".

The purpose of the AFTER trigger is to "perform additional operations on the database which are required to implement collateral constraints only in the event we have successfully made it this far".

Also note that if you have multiple triggers of the same type, then all the applicable triggers will fire. There is not, to my knowledge, any guarantee of the order in which the triggers will fire. That is to say that if you have 15 BEFORE triggers of which 5 have applicable WHEN conditions, ALL 5 WILL RUN, although you have no control over the order in which they run. If order of operations is important to you for some reason then you must put those order dependent operations within the same trigger.

Be aware also that the NEW and OLD pseudo tables contain information relative to the particular context when the trigger was fired, and not the current condition of the processing or database. This is especially import to remember and to take into account in whether you require recursive triggers to be specifically enabled or disabled. If you do not explicitly state whether you want trigger recursion on or off you should have designed your triggers to work correctly in both cases.

(15) By Adrian Ho (lexfiend) on 2020-07-10 02:06:33 in reply to 11 [link] [source]

Your script does this for non-debug builds:

        export CFLAGS="-fPIC \
but does this for debug builds:
        export SQLITE3_CFLAGS="-fPIC \
hence my original comment.

The C compiler expects CFLAGS.

Most (all?) C compilers ignore CFLAGS, as they expect build flags on the command line. It's the configure script that captures the value of CFLAGS and modifies the various build control files accordingly.

(16) By anonymous on 2020-07-10 07:35:45 in reply to 15 [link] [source]

but does this for debug builds:

        export SQLITE3_CFLAGS="-fPIC \
—> Thx for spotting my mistake.

Most (all?) C compilers ignore CFLAGS, as they expect build flags on the command line. It's the configure script that captures the value of CFLAGS and modifies the various build control files accordingly.
—> You're correct that the configure script sets the command-line options to compile the code.

(17) By anonymous on 2020-07-10 07:43:13 in reply to 14 [link] [source]

A1_INDEX INTEGER,
    A1_CAT VARCHAR(1) DEFAULT 'W',
These columns are defined as the primary key like this:
   CONSTRAINT A1_PK PRIMARY KEY (
        A1_INDEX,
        A1_CAT
    ),
    UNIQUE (
        A1_INDEX,
        A1_CAT
    )
Please note that there shouldn't be a need for the unique statement.

Table C1 the foreign key column is defined as
    C1_INDEX INTEGER,

and the foreign key definition is:
  CONSTRAINT C1_INDEX_FK FOREIGN KEY (
        C1_INDEX
    )
    REFERENCES A1 (
        A1_INDEX
    ),
    UNIQUE (
        C1_INDEX
    )

Result: It fails to build (Error foreign key mismatch).

(18) By Keith Medcalf (kmedcalf) on 2020-07-10 09:45:22 in reply to 17 [link] [source]

The primary key of A1 has two fields, the reference from C1 only has one.

You need a UNIQUE index on A1_INDEX (not on A1_INDEX and something else besides).

That is, the UNIQUE constraint needs to have A1_CAT removed from it.

(19) By Ryan Smith (cuz) on 2020-07-10 11:18:42 in reply to 18 [link] [source]

That is, the UNIQUE constraint needs to have A1_CAT removed from it.

Or, (just adding the obvious), the Foreign Key reference need to refer to both fields (in the same order) to make the Unique index valid.

@OP: I'll try put in words what Keith assumed was understood - It's not about the Index mechanism, it's about the referential integrity - meaning:

If a table has 2 columns A and B and a unique/PK constraint UNIQUE(A,B), then it cannot have duplicates of A,B, but it CAN have duplicates of A, so that:

No. A | B
   ---|---
 1  1 | Z
 2  1 | Y
 3  2 | Z
etc. is possible.

If a foreign key was possible on the A of UNIQUE(A,B) for a child table, then a child record with value 1 for A would technically have 2 parents (Rows 1 and 2 where A=1) which violates the very core of a Foreign-Key relationship.

If the child (c) however contains 2 columns, say A' and B' which map directly to A and B in the parent (p), then the foreign-key can be created on c(A',B') REFERENCES p(A,B) - which conforms to the Unique index and so the integrity is restored and everything works.

What your definition should look like to work (though I don't know if it fulfills your layout desires):

CREATE TABLE A1(
  A1_INDEX INTEGER,
  A1_CAT VARCHAR(1) DEFAULT 'W',
  CONSTRAINT A1_PK PRIMARY KEY (
    A1_INDEX,
    A1_CAT
  )
);

CREATE TABLE C1(
  C1_INDEX INTEGER,
  C1_CAT VARCHAR(1)
  CONSTRAINT C1_INDEX_FK FOREIGN KEY (
    C1_INDEX,
    C1_CAT
  )REFERENCES A1(
    A1_INDEX
    A1_CAT
  )
);

If, on the other hand, you can only have 1 child in C1 for every single INDEX in A1, then you need this simpler layout (as Keith suggested):

CREATE TABLE A1(
  A1_INDEX INTEGER,
  A1_CAT VARCHAR(1) DEFAULT 'W',
  CONSTRAINT A1_PK PRIMARY KEY (
    A1_INDEX,
    A1_CAT
  ),
  CONSTRAINT UIdx UNIQUE(A1_INDEX)
);

CREATE TABLE C1(
  C1_INDEX INTEGER REFERENCES A1(A1_INDEX)
);

I'll further add here that this layout is silly as if A1_INDEX is already Unique... there is no point to the PK including A1_CAT... you'd probably be better off making A1_INDEX the PK and adding UNIQUE(A1_INDEX,A1_CAT).

I hope that removes some questions. Good luck!

(20) By anonymous on 2020-07-11 10:33:26 in reply to 19 [link] [source]

Executed the test and got this result:
2/0/563136D280B8: end processing
'-- SELECT (2/563136D280B8) selFlags=0x4400c4 nSelectRow=0
    '-- result-set
        '-- AS END 
            '-- CASE
                |-- 1
                '-- LIST
                    |-- EQ fg.af=40000000.n DDL
                    |   |-- NEW(5)
                    |   '-- 2
                    '-- CASE
                        |-- 1
                        '-- LIST
                            |-- OR fg.af=40000000.n DDL
                            |   |-- NOT fg.af=40000000.n DDL
                            |   |   '-- IN flags=0x40000000
                            |   |       |-- OLD(5)
                            |   |       '-- LIST
                            |   |           |-- 1
                            |   |           '-- 128
                            |   '-- AND fg.af=40000000.n DDL
                            |       |-- NOTNULL fg.af=40000000.n DDL
                            |       |   '-- OLD(6)
                            |       '-- NOT fg.af=40000000.n DDL
                            |           '-- EQ fg.af=40000000.n DDL
                            |               |-- OLD(5)
                            |               '-- 4
                            |-- RAISE abort('Error: someone goofed')
                            |-- NE fg.af=40200000.n DDL
                            |   |-- subquery-expr flags=0x42200800
                            |   |   '-- SELECT (1/563136D2AFF8) selFlags=0x40c4 nSelectRow=0
                            |   |       |-- result-set
                            |   |       |   '-- {0:5} pTab=563136D21208 fg.af=40800000.n DDL
                            |   |       |-- FROM
                            |   |       |   '-- {0:*} C1 tab='C1' nCol=8 ptr=563136D21208 used=22 DDL
                            |   |       |-- WHERE
                            |   |       |   '-- EQ fg.af=40000000.n DDL
                            |   |       |       |-- {0:1} pTab=563136D21208 fg.af=40800000.n DDL
                            |   |       |       '-- NEW(0)
                            |   |       '-- LIMIT
                            |   |           '-- 1
                            |   '-- 4
                            '-- RAISE abort('Error you are not allowed to change the status')
*** Optimizer Start *** (wctrlFlags: 0x1004)
---- Solution nRow=1
0 0.01.00           C1._2                1 f 01241 N 1 cost 0,39,1
*** Optimizer Finished ***
ONEPASS_SINGLE cursors: 3 4
Error: near line 6: no such column: C1_QTY_PRODUCED

Note that the .selecttrace crashes and the .wheretrace shows and error. What?!
Excuse me! The table C1 has a column C1_QTY_PRODUCED. Here is the table:
CREATE TABLE IF NOT EXISTS C1 (
    C1_PRODUCTION_ORDER VARCHAR(20),
    C1_INDEX INTEGER,
    C1_QTY_TO_PRODUCE NUMBER DEFAULT 0,
    C1_QTY_PRODUCED NUMBER DEFAULT 0,
    C1_PRODUCTION_STATUS NUMBER
        CHECK (C1_PRODUCTION_STATUS IN (1, 2, 4, 128)),
    C1_STATUS NUMBER
        CHECK (C1_STATUS IN (1, 2, 4, 8)),
    C1_DTS_CREATED TEXT NOT NULL DEFAULT (DATETIME('NOW', 'UTC')),
    C1_DTS_UPDATED TEXT NOT NULL DEFAULT (DATETIME('NOW', 'UTC')),
    CONSTRAINT C1_PK PRIMARY KEY (
        C1_PRODUCTION_ORDER
    ) ON CONFLICT ROLLBACK,
    CONSTRAINT C1_INDEX_FK FOREIGN KEY (
        C1_INDEX
    )
    REFERENCES A1 (
        A1_INDEX
    ),
    UNIQUE (
        C1_INDEX
    )
);
Am I overlooking something?

(21) By Ryan Smith (cuz) on 2020-07-11 11:56:16 in reply to 20 [link] [source]

Am I overlooking something?

It's so hard to tell...

Could you please post the entire schema? And then please say what "ran the test" means?

We have no insight into what your program(s) are doing, but we can test the SQL - just paste the Schema and SQL that fails and we will be able to also run it and get the same error.

This would make everything clear to everyone and then we'll be able to help you solve it fast and easily - now you're just prolonging your own agony. Help us to help you!

(22) By anonymous on 2020-07-11 13:11:43 in reply to 21 [link] [source]

Here is the complete schema:
-- character encoding
PRAGMA encoding = 'UTF-16le';

PRAGMA foreign_keys = TRUE;

-- Journalling mode
PRAGMA journal_mode=WAL;

-- Automatic ANALYZE
PRAGMA optimize;

-- STATUS
-- 1 →  IN STOCK
-- 2 →  NEW STOCK IN PRODUCTION
-- 4 →  BLOCKED STOCK
-- 128 → PRODUCT NO LONGER PRODUCED
CREATE TABLE IF NOT EXISTS A1 (
    A1_INDEX INTEGER,
    A1_CAT VARCHAR(1) DEFAULT 'W',
    A1_STOCK_MIN INTEGER,
    A1_STOCK_LEVEL INTEGER,
    A1_PRODUCT TEXT,
    A1_STATUS NUMBER
        CHECK (A1_STATUS IN (1, 2, 4, 8, 16, 32, 64, 128)) DEFAULT 1,
    A1_REF VARCHAR(20) CHECK (A1_REF != A1_INDEX),
    A1_DTS_CREATED TEXT NOT NULL DEFAULT (DATETIME('NOW', 'UTC')),
    A1_DTS_UPDATED TEXT NOT NULL DEFAULT (DATETIME('NOW', 'UTC')),
    CONSTRAINT A1_PK PRIMARY KEY (
        A1_INDEX,
        A1_CAT
    ),
    UNIQUE (
        A1_INDEX
    )
);

-- PRODUCTION STATUS:
-- 1 →  PREPARED FOR PRODUCTION
-- 2 →  IN PRODUCTIION
-- 4 →  PRODUCTION FINISHED
-- 128 → PRODUCTION BATCH REJECTED
--
-- STATUS
-- 1 →  PLANNING PROCESSING
-- 2 →  PROCESSING
-- 4 →  EVALUTION
-- 8 →  PROCESSED
CREATE TABLE IF NOT EXISTS C1 (
    C1_PRODUCTION_ORDER VARCHAR(20),
    C1_INDEX INTEGER,
    C1_QTY_TO_PRODUCE NUMBER DEFAULT 0,
    C1_QTY_PRODUCED NUMBER DEFAULT 0,
    C1_PRODUCTION_STATUS NUMBER
        CHECK (C1_PRODUCTION_STATUS IN (1, 2, 4, 128)),
    C1_STATUS NUMBER
        CHECK (C1_STATUS IN (1, 2, 4, 8)),
    C1_DTS_CREATED TEXT NOT NULL DEFAULT (DATETIME('NOW', 'UTC')),
    C1_DTS_UPDATED TEXT NOT NULL DEFAULT (DATETIME('NOW', 'UTC')),
    CONSTRAINT C1_PK PRIMARY KEY (
        C1_PRODUCTION_ORDER
    ) ON CONFLICT ROLLBACK,
    CONSTRAINT C1_INDEX_FK FOREIGN KEY (
        C1_INDEX
    )
    REFERENCES A1 (
        A1_INDEX
    ),
    UNIQUE (
        C1_INDEX
    )
);

-- Create stock
INSERT INTO A1 (A1_INDEX, A1_STOCK_MIN, A1_STOCK_LEVEL, A1_PRODUCT, A1_REF)
VALUES
        (12, cast(abs((0.0661 * random()) % 997 +1) as integer), ABS(RANDOM() % 14731) + 1, 'Drei im Weggla', NULL),
        ( 7, cast(abs((0.0661 * random()) % 997 +1) as integer), ABS(RANDOM() % 14731) + 1, 'Blaue Zipfel mit Brezel', NULL),
        (17, cast(abs((0.0661 * random()) % 997 +1) as integer), ABS(RANDOM() % 14731) + 1, 'Nürnberger Rostbratwurst', NULL),
        (14, cast(abs((0.0661 * random()) % 997 +1) as integer), ABS(RANDOM() % 14731) + 1, 'Brätwurst', 17),
        (19, cast(abs((0.0661 * random()) % 997 +1) as integer), ABS(RANDOM() % 14731) + 1, 'Brühwürste', NULL);

CREATE TRIGGER IF NOT EXISTS A1_100 BEFORE UPDATE ON A1
WHEN (NEW.A1_STATUS = 2) AND (NEW.A1_REF ISNULL)
BEGIN
        SELECT CASE 1
                WHEN (NEW.A1_STATUS == 2)
                        THEN CASE 1
                                WHEN (NOT (OLD.A1_STATUS IN (1, 128))) OR (((OLD.A1_REF NOT NULL)) AND (NOT (OLD.A1_STATUS == 4)))
                                        THEN RAISE (ABORT, 'Error: someone goofed')
                                WHEN ((SELECT C1_STATUS FROM C1 WHERE C1_INDEX == NEW.A1_INDEX) != 4)
                                        THEN RAISE (ABORT, 'Error you are not allowed to change the status')
                                END
                        END
                END;

        -- Signal processing
        UPDATE OR ROLLBACK C1 SET
                C1_STATUS = NEW.A1_STATUS
        WHERE C1_INDEX == NEW.A1_INDEX;

END;

-- Let us make sauceges
CREATE TRIGGER IF NOT EXISTS C1_101 BEFORE UPDATE ON C1
WHEN (NEW.C1_PRODUCTION_STATUS == 2)
BEGIN

        SELECT CASE 1
                WHEN (OLD.C1_STATUS != 1)
                        THEN RAISE (ABORT, 'Error, you need to plan the production first')
                WHEN (SELECT A1_STATUS FROM A1 WHERE A1_INDEX == NEW.C1_INDEX) IN (2, 4, 128)
                        THEN RAISE (ABORT, 'Error, production order is invalid')
                END;


        --Keep the status in sync
        -- Signal new stock in production
        UPDATE OR ROLLBACK A1 SET
                A1_STATUS = 2
        WHERE A1_INDEX == NEW.C1_INDEX;

END;

-- Finished the production of sauceges
CREATE TRIGGER IF NOT EXISTS C1_110 BEFORE UPDATE ON C1
WHEN (NEW.C1_PRODUCTION_STATUS == 8)
BEGIN
        SELECT CASE 1
                WHEN (NEW.C1_QTY_PRODUCED == 0)
                        THEN RAISE (ABORT, 'Error: someone goofed')
                WHEN (NEW.C1_QTY_TO_PRODUCE == 0)
                        THEN RAISE (ABORT, 'Error there no sausages are ordered!')
        END;

        UPDATE OR ROLLBACK A1 SET
                A1_STOCK_LEVEL = A1_STOCK_LEVEL + C1_QTY_PRODUCED
        WHERE A1_INDEX = NEW.C1_INDEX;

        UPDATE OR ROLLBACK C1 SET
                C1_QTY_TO_PRODUCE = 0,
                C1_QTY_PRODUCED = 0,
                C1_STATUS = 1,
                C1_PRODUCTION_STATUS = 1   -- CLEAN AND RESET THE PRODUCTION CHAIN
        WHERE C1_INDEX == NEW.C1_INDEX;

        -- SYNC THE STATUS
        UPDATE OR ROLLBACK A1 SET
                A1_STATUS = (SELECT C1_STATUS FROM C1 WHERE C1_INDEX == NEW.C1_INDEX)
        WHERE A1_INDEX = NEW.C1_INDEX;

END;
-- Create a production order
INSERT INTO C1 (C1_INDEX, C1_PRODUCTION_ORDER, C1_QTY_TO_PRODUCE, C1_QTY_PRODUCED, C1_PRODUCTION_STATUS, C1_STATUS)
VALUES
        (12, '07072020_C', ABS(RANDOM() % 2137) + 1, 0, 1, 1),
        (19, '07072020_B', ABS(RANDOM() % 2137) + 1, 0, 1, 1);
                                                                
And this is the test:
.selecttrace 0xfffff
.wheretrace

BEGIN TRANSACTION;

UPDATE A1 SET
A1_STATUS = 2
WHERE A1_INDEX == 19;

END;
~

(23) By TripeHound on 2020-07-11 14:05:44 in reply to 22 [link] [source]

I believe the problem is the line:

A1_STOCK_LEVEL = A1_STOCK_LEVEL + C1_QTY_PRODUCED

which doesn't prefix C1_QTY_PRODUCED with NEW. or OLD..

Please don't take this the wrong way – this comes from many years of experience from the days when the best diagnostic you could hope for was "HOW?", "WHAT?" or "SORRY" – but from the few threads you've posted ostensibly about problems with .selecttrace, I think you need to try and develop your "inner debugger".

I've done very little with triggers, but after cutting-and-pasting your code, and confirming that I get the same error, I simply searched for C1_QTY_PRODUCED within the block of code. First check is that it does appear, as typed, in the CREATE TABLE statement (so: not a simple typo).

Next, in trying to discover which reference SQLite was complaining about, it was fairly obvious (to me, after much general experience) that all other references both to it and other columns in C1 were preceded by NEW. or OLD.. Coupled with my basic knowledge of triggers, I tried adding NEW. to the above line and it "worked" (as the trigger threw up some messages).

I haven't followed the logic of the trigger to know whether it should be OLD. instead, but I'm fairly certain that it should be one or the other.

(24) By Keith Medcalf (kmedcalf) on 2020-07-11 15:25:22 in reply to 22 [source]

Your A1_100 trigger is ill_conceived.

It seems to assume that the value of A1_STATUS is being changed however there is no condition which requires this to be the case.

You are merely reacting to the value of NEW.A1_STATUS being 2. A change is being made to the status ONLY in the case where OLD.A1_STATUS IS NOT NEW.A1_STATUS.

Any update to A1 where the value of A1_STATUS happens to be 2 and not updated will also fire the trigger, but will "treat" the update as changing the value of A1_STATUS even though it is not, in fact, being changed.

This is particularly evident in the part of the code that:

                                WHEN ((SELECT C1_STATUS FROM C1 WHERE C1_INDEX == NEW.A1_INDEX) != 4)
                                        THEN RAISE (ABORT, 'Error you are not allowed to change the status')

which spits an error about changing the status being prohibited when in fact attempting to change the status is not a pre-condition to throwing that error. If the message said "cannot have status == 2" then it would be correct, but it is claiming conditions extant which do not in fact exist.

(25) By anonymous on 2020-07-12 05:49:56 in reply to 24 [link] [source]

The missing NEW was forcing an error to verify that the C1_110 trigger was fired. I was wrong. You're telling me that the optimizer scans all of the C1 update triggers because I execute a read on table C1 in de A1_100 trigger.

This explains why I was puzzled by the result. I will need some time to ponder about this.

(26) By Keith Medcalf (kmedcalf) on 2020-07-12 22:35:56 in reply to 25 [link] [source]

Not at all.

The C1 trigger was scanned as part of preparing the VDBE program because your trigger does an "UPDATE C1" which requires that all triggers which might fire on "UPDATE OF C1" be parsed to into the applicable parse tree.

However, this trigger could not be parsed correctly and therefore you were alerted to your error.

This is quite independent (I should think) of whether or not the trigger will actually be incorporated into the VBDE program.

(27) By Keith Medcalf (kmedcalf) on 2020-07-12 22:49:06 in reply to 25 [link] [source]

Also note that you have not yet reached the point at which you know the "trigger has fired" because you are still in the "getting ready to generate the code" phase of preparing the statement.

You have not yet managed to reach the "executing the statement" yet.

(28) By anonymous on 2020-07-14 11:09:36 in reply to 27 [link] [source]

That been said I have removed all of the references to table C1 in the A100 trigger.
Then I've pondered a while, about what you have said and I modified the script to demonstrate that the optimizer function is greedy and that the optimizer will also attempt to create code for triggers that will not be triggered because the status isn't complying to the condition.

So I executed my test:
.selecttrace 0xfffff
--.wheretrace

BEGIN TRANSACTION;

UPDATE A1 SET
A1_STATUS = 2
WHERE A1_INDEX == 19;

END;
Which resulted in an error:
Error: near line 6: no such column: C1_QTY_PRODUCED

I assume that if I get an error then the "trigger must have been triggered".

By the way: I have made the same error in the C110 trigger. Apparently, there is a reason in my madness…

If I update the status to 2 for table A1 then I think that following unfolds:
a) The condition is valid for trigger A100 → To proceed to generate the code;
b) The condition is invalid for trigger A101 → To do nothing and ignore the trigger.

-- character encoding
PRAGMA encoding = 'UTF-16le';

PRAGMA foreign_keys = TRUE;

-- Journalling mode
PRAGMA journal_mode=WAL;

-- Automatic ANALYZE
PRAGMA optimize;

CREATE TABLE IF NOT EXISTS _Variables (NAME TEXT PRIMARY KEY, VALUE TEXT)
WITHOUT ROWID;

-- STATUS
-- 1 →  IN STOCK
-- 2 →  NEW STOCK IN PRODUCTION
-- 4 →  BLOCKED STOCK
-- 128 → PRODUCT NO LONGER PRODUCED
CREATE TABLE IF NOT EXISTS A1 (
    A1_INDEX INTEGER,
    A1_CAT VARCHAR(1) DEFAULT 'W',
    A1_STOCK_MIN INTEGER,
    A1_STOCK_LEVEL INTEGER,
    A1_PRODUCT TEXT,
    A1_STATUS NUMBER
        CHECK (A1_STATUS IN (1, 2, 4, 8, 16, 32, 64, 128)) DEFAULT 1,
    A1_REF VARCHAR(20) CHECK (A1_REF != A1_INDEX),
    A1_DTS_CREATED TEXT NOT NULL DEFAULT (DATETIME('NOW', 'UTC')),
    A1_DTS_UPDATED TEXT NOT NULL DEFAULT (DATETIME('NOW', 'UTC')),
    CONSTRAINT A1_PK PRIMARY KEY (
        A1_INDEX,
        A1_CAT
    ),
    UNIQUE (
        A1_INDEX
    )
);

-- PRODUCTION STATUS:
-- 1 →  PREPARED FOR PRODUCTION
-- 2 →  IN PRODUCTIION
-- 4 →  PRODUCTION FINISHED
-- 128 → PRODUCTION BATCH REJECTED
--
-- STATUS
-- 1 →  PLANNING PROCESSING
-- 2 →  PROCESSING
-- 4 →  EVALUTION
-- 8 →  PROCESSED
CREATE TABLE IF NOT EXISTS C1 (
    C1_PRODUCTION_ORDER VARCHAR(20),
    C1_INDEX INTEGER,
    C1_QTY_TO_PRODUCE NUMBER DEFAULT 0,
    C1_QTY_PRODUCED NUMBER DEFAULT 0,
    C1_PRODUCTION_STATUS NUMBER
        CHECK (C1_PRODUCTION_STATUS IN (1, 2, 4, 128)),
    C1_STATUS NUMBER
        CHECK (C1_STATUS IN (1, 2, 4, 8)),
    C1_DTS_CREATED TEXT NOT NULL DEFAULT (DATETIME('NOW', 'UTC')),
    C1_DTS_UPDATED TEXT NOT NULL DEFAULT (DATETIME('NOW', 'UTC')),
    CONSTRAINT C1_PK PRIMARY KEY (
        C1_PRODUCTION_ORDER
    ) ON CONFLICT ROLLBACK,
    CONSTRAINT C1_INDEX_FK FOREIGN KEY (
        C1_INDEX
    )
    REFERENCES A1 (
        A1_INDEX
    ),
    UNIQUE (
        C1_INDEX
    )
);

-- Create stock
INSERT INTO A1 (A1_INDEX, A1_STOCK_MIN, A1_STOCK_LEVEL, A1_PRODUCT, A1_REF)
VALUES
        (12, cast(abs((0.0661 * random()) % 997 +1) as integer), ABS(RANDOM() % 14731) + 1, 'Drei im Weggla', NULL),
        ( 7, cast(abs((0.0661 * random()) % 997 +1) as integer), ABS(RANDOM() % 14731) + 1, 'Blaue Zipfel mit Brezel', NULL),
        (17, cast(abs((0.0661 * random()) % 997 +1) as integer), ABS(RANDOM() % 14731) + 1, 'Nürnberger Rostbratwurst', NULL),
        (14, cast(abs((0.0661 * random()) % 997 +1) as integer), ABS(RANDOM() % 14731) + 1, 'Brätwurst', 17),
        (19, cast(abs((0.0661 * random()) % 997 +1) as integer), ABS(RANDOM() % 14731) + 1, 'Brühwürste', NULL);

CREATE TRIGGER IF NOT EXISTS A1_100 BEFORE UPDATE ON A1
WHEN (NEW.A1_STATUS = 2) AND (NEW.A1_REF ISNULL)
BEGIN

   INSERT INTO _Variables VALUES
      ('NEW_A1_STATUS', NEW.A1_STATUS),
      ('OLD_A1_STATUS', OLD.A1_STATUS);

        SELECT CASE 1
                WHEN (NEW.A1_STATUS == 2)
                        THEN CASE 1
                                WHEN (NOT (OLD.A1_STATUS IN (1, 128))) OR (((OLD.A1_REF NOT NULL)) AND (NOT (OLD.A1_STATUS == 4)))
                                        THEN RAISE (ABORT, 'Error: someone goofed')
--                              WHEN ((SELECT C1_STATUS FROM C1 WHERE C1_INDEX == NEW.A1_INDEX) != 4)
--                                      THEN RAISE (ABORT, 'Error you are not allowed to change the status')
                                END
                        END
                END;
END;

CREATE TRIGGER IF NOT EXISTS A1_101 BEFORE UPDATE ON A1
WHEN (NEW.A1_STATUS == 4)
BEGIN

        -- Signal processing
        UPDATE OR ROLLBACK C1 SET
                C1_STATUS = NEW.A1_STATUS
        WHERE C1_INDEX == NEW.A1_INDEX;

END;

-- Let us make sausages
CREATE TRIGGER IF NOT EXISTS C1_101 BEFORE UPDATE ON C1
WHEN (NEW.C1_PRODUCTION_STATUS == 2)
BEGIN

        SELECT CASE 1
                WHEN (OLD.C1_STATUS != 1)
                        THEN RAISE (ABORT, 'Error, you need to plan the production first')
                WHEN (SELECT A1_STATUS FROM A1 WHERE A1_INDEX == NEW.C1_INDEX) IN (2, 4, 128)
                        THEN RAISE (ABORT, 'Error, production order is invalid')
                END;


        --Keep the status in sync
        -- Signal new stock in production
        UPDATE OR ROLLBACK A1 SET
                A1_STATUS = 2
        WHERE A1_INDEX == NEW.C1_INDEX;

END;

- Finished the production of sauceges
CREATE TRIGGER IF NOT EXISTS C1_110 BEFORE UPDATE ON C1
WHEN (NEW.C1_PRODUCTION_STATUS == 8)
BEGIN
        SELECT CASE 1
                WHEN (NEW.C1_QTY_PRODUCED == 0)
                        THEN RAISE (ABORT, 'Error: someone goofed')
                WHEN (NEW.C1_QTY_TO_PRODUCE == 0)
                        THEN RAISE (ABORT, 'Error there no sausages are ordered!')
        END;

        UPDATE OR ROLLBACK A1 SET
                A1_STOCK_LEVEL = A1_STOCK_LEVEL + C1_QTY_PRODUCED
        WHERE A1_INDEX = NEW.C1_INDEX;

        UPDATE OR ROLLBACK C1 SET
                C1_QTY_TO_PRODUCE = 0,
                C1_QTY_PRODUCED = 0,
                C1_STATUS = 1,
                C1_PRODUCTION_STATUS = 1   -- CLEAN AND RESET THE PRODUCTION CHAIN
        WHERE C1_INDEX == NEW.C1_INDEX;

        -- SYNC THE STATUS
        UPDATE OR ROLLBACK A1 SET
                A1_STATUS = (SELECT C1_STATUS FROM C1 WHERE C1_INDEX == NEW.C1_INDEX)
        WHERE A1_INDEX = NEW.C1_INDEX;

END;

-- Create a production order
INSERT INTO C1 (C1_INDEX, C1_PRODUCTION_ORDER, C1_QTY_TO_PRODUCE, C1_QTY_PRODUCED, C1_PRODUCTION_STATUS, C1_STATUS)
VALUES
        (12, '07072020_C', ABS(RANDOM() % 2137) + 1, 0, 1, 1),
        (19, '07072020_B', ABS(RANDOM() % 2137) + 1, 0, 1, 1);

(29) By Keith Medcalf (kmedcalf) on 2020-07-14 14:28:23 in reply to 28 updated by 29.1 [link] [source]

You are confusing the state "compile the code" with the state "run the code".

When you "prepare" a statement using the sqlite3_prepare* API you are "generating the code".  In order to "generate the code" **all** of the applicable source code must be parsed and "compiled".  This means that **ALL** the source code must be checked for errors (in this case all the applicable or could be applicable triggers).

Just like when you "compile a program" which consists of 47 source code files, an error in **ONE** of those 47 source code files is flagged by the compiler, and no "executable" is produced.  Instead you get an error message telling you about the error.

This is the error you are seeing.

Once you have managed to fix all the errors in your source code files, then the compiler is able to produce an "executable" (which in the case of SQLite3 is called a "statement" (VDBE program) and is the output of the compiling (prepare) process).

When you "compile" a bunch of source code files into an "executable", you cause that code to enter into the "executing" state by running the resulting executable.

In the case of SQLite3 when you "compile" (prepare) a valid "program" the output is a "statement" (VDBE program).  You cause this program to execute by calling the sqlite3_exec API on this statement.  In both cases you pass "parameters" into the program (for a computer program that is arguments on the command line, with SQLite3 that is by using the sqlite3_bind API).  Some variables may be set within the program itself.  Just because some file says "a2 = 2" does not mean anything with respect to compilation.  The value only has effect at execution time.

However, the result of "executing" a statement, sqlite3_exec, is very limited.  It may tell you SQLITE_DONE if it ran to completion, SQLITE_ROW if it has an intermediate row for you to do something with, or another applicable error message (such as SQLITE_BUSY, SQLITE_LOCKED, or a very small set of SQLITE_ERROR all dealing with **execution** problems (and never source code compilation problems).

Maybe perhaps the CLI should indicate whether the error is a "PREPARE ERROR" or an "EXEC ERROR" to make this clearer to you.  At the moment it does not because there is an assumption that you know that certain classes of errors can only be returned by the PREPARE processing and that other errors can only be returned by the EXEC processing (if it even matters at all), and there is almost no overlap (SQLITE_NOMEM, SQLITE_MISUSE, SQLITE_BUSY, SQLITE_LOCKED, and perhaps a few others are the only ones, and if you get one of those you are "dunfer" anyway).

Furthermore I would observe that you seem to presume that the WHEN clause in a trigger is a **compile** time directive.  It is not.

In the case of triggers:  
`{BEFORE|AFTER}` is a compile time directive controlling whether the code is to be inserted before or after the operation is performed.  
`[INSTEAD OF]{INSERT|UPDATE|DELETE}` are compile time directives controlling the statements in which to include the code.  
`[OF <column list>] ON <name>` is a compile time directive controlling when the statements are to be included in the code.  
`[WHEN <expression>]` is a runtime entry condition.

In this particular case, you are doing an UPDATE A1 so **ALL TRIGGERS** which should fire for this condition must be included, and since you did not specify an `OF <column_list>` no UPDATE TRIGGERS on A1 are excluded thereby (so both triggers specified for UPDATE ON A1 are included).

Of those triggers, Trigger A1_101 does an UPDATE C1 so **ALL TRIGGERS** which should fire for this condition must be included, and since you did not specify an `OF <column_list>` then no UPDATE TRIGGERS on C1 are excluded thereby (so both triggers specified for UPDATE ON C1 are included).

Whether or not a particular `WHEN <expression>` allows entry into the "trigger program code" is a **runtime** decision, not a **compile time** decision.

However, one of those triggers (C1_110) cannot be compiled because it contains an error.  The error is returned to you and the statement is **not compiled** and is not run (there is nothing to run because it failed to compile).

(29.1) By Keith Medcalf (kmedcalf) on 2020-07-14 14:43:03 edited from 29.0 in reply to 28 [link] [source]

You are confusing the state "compile the code" with the state "run the code".

When you "prepare" a statement using the sqlite3_prepare* API you are "generating the code". In order to "generate the code" all of the applicable source code must be parsed and "compiled". This means that ALL the source code must be checked for errors (in this case all the applicable or could be applicable triggers).

Just like when you "compile a program" which consists of 47 source code files, an error in ONE of those 47 source code files is flagged by the compiler, and no "executable" is produced. Instead you get an error message telling you about the error.

This is the error you are seeing.

Once you have managed to fix all the errors in your source code files, then the compiler is able to produce an "executable" (which in the case of SQLite3 is called a "statement" (VDBE program) and is the output of the compiling (prepare) process).

When you "compile" a bunch of source code files into an "executable", you cause that code to enter into the "executing" state by running the resulting executable.

In the case of SQLite3 when you "compile" (prepare) a valid "program" the output is a "statement" (VDBE program). You cause this program to execute by calling the sqlite3_exec API on this statement. In both cases you pass "parameters" into the program (for a computer program that is arguments on the command line, with SQLite3 that is by using the sqlite3_bind API). Some variables may be set within the program itself. Just because some file says "a2 = 2" does not mean anything with respect to compilation. The value only has effect at execution time.

However, the result of "executing" a statement, sqlite3_exec, is very limited. It may tell you SQLITE_DONE if it ran to completion, SQLITE_ROW if it has an intermediate row for you to do something with, or another applicable error message (such as SQLITE_BUSY, SQLITE_LOCKED, or a very small set of SQLITE_ERROR all dealing with execution problems (and never source code compilation problems).

Maybe perhaps the CLI should indicate whether the error is a "PREPARE ERROR" or an "EXEC ERROR" to make this clearer to you. At the moment it does not because there is an assumption that you know that certain classes of errors can only be returned by the PREPARE processing and that other errors can only be returned by the EXEC processing (if it even matters at all), and there is almost no overlap (SQLITE_NOMEM, SQLITE_MISUSE, SQLITE_BUSY, SQLITE_LOCKED, and perhaps a few others are the only ones, and if you get one of those you are "dunfer" anyway).

Furthermore I would observe that you seem to presume that the WHEN clause in a trigger is a compile time directive. It is not.

In the case of triggers:
{BEFORE|AFTER|INSTEAD OF} is a compile time directive controlling whether the code is to be inserted before, after, or instead of the operation chosen.
{INSERT|UPDATE|DELETE} are compile time directives controlling the statements to which the code is applicable.
[OF <column list>] ON <name> is a compile time directive controlling when the statements are to be included in the code.
[WHEN <expression>] is a runtime entry condition.

In this particular case, you are doing an UPDATE A1 so ALL TRIGGERS which should fire for this condition must be included, and since you did not specify an OF <column_list> no UPDATE TRIGGERS on A1 are excluded thereby (so both triggers specified for UPDATE ON A1 are included).

Of those triggers, Trigger A1_101 does an UPDATE C1 so ALL TRIGGERS which should fire for this condition must be included, and since you did not specify an OF <column_list> then no UPDATE TRIGGERS on C1 are excluded thereby (so both triggers specified for UPDATE ON C1 are included).

Whether or not a particular WHEN <expression> allows entry into the "trigger program code" is a runtime decision, not a compile time decision.

However, one of those triggers (C1_110) cannot be compiled because it contains an error. The error is returned to you and the statement is not compiled and is not run (there is nothing to run because it failed to compile).

(30) By doug (doug9forester) on 2020-07-14 15:49:35 in reply to 28 [link] [source]

Anonymous, I think your design has a major flaw: it is not provable to be correct. Your use of complex triggers which cannot be verified by reading them or running them seems to me to be dangerous to the integrity of your application.

My experience has led me to perform the trigger functionality at the application level where it is simple and verifiable. Get rid of the triggers.

If you generate different queries which test the conditions which your triggers are triggered on, then you can run the right insert or update query at the application level which handles the condition. The flow through the application is testable and not asynchronous and obtuse, as triggers are. And, of course, you can wrap the whole set of queries in a transaction for data integrity.

My two cents...