Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Investigate possible SQLite savepoint bug #100

Closed
AnyhowStep opened this issue Jan 4, 2020 · 4 comments
Closed

Investigate possible SQLite savepoint bug #100

AnyhowStep opened this issue Jan 4, 2020 · 4 comments
Assignees
@AnyhowStep
Labels
sqlite

Comments

@AnyhowStep
Copy link
Owner

Schema (SQLite v3.26)

Query #1

CREATE TABLE appKey (
    appId INTEGER NOT NULL,
    appKeyId INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    appKeyTypeId INTEGER NOT NULL,
    key VARCHAR(255) NOT NULL,
    createdAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    disabledAt DATETIME
);

There are no results to be displayed.

Query #2

CREATE TABLE browserAppKey (
    appKeyId INTEGER NOT NULL,
    appKeyTypeId INTEGER NOT NULL DEFAULT (2),
    referer VARCHAR(2048),
    FOREIGN KEY (appKeyId, appKeyTypeId) REFERENCES appKey (appKeyId, appKeyTypeId)
);

There are no results to be displayed.

Query #3

CREATE TABLE serverAppKey (
    appKeyId INTEGER NOT NULL,
    appKeyTypeId INTEGER NOT NULL DEFAULT (1),
    ipAddress VARCHAR(2048),
    trustProxy BOOLEAN NOT NULL DEFAULT (FALSE),
    FOREIGN KEY (appKeyId, appKeyTypeId) REFERENCES appKey (appKeyId, appKeyTypeId)
);

There are no results to be displayed.

Query #4

CREATE TABLE specialServerAppKey (
	appKeyId INTEGER NOT NULL,
	FOREIGN KEY (appKeyId) REFERENCES serverAppKey (appKeyId)
);

There are no results to be displayed.

Query #5

BEGIN TRANSACTION;

There are no results to be displayed.

Query #6

SAVEPOINT tsql_savepoint_1;

There are no results to be displayed.

Query #7

INSERT INTO "appKey" ( "appId", "appKeyTypeId", "createdAt", "disabledAt", "key" ) VALUES ( 1 ,  1 ,  strftime('%Y-%m-%d %H:%M:%f', '1970-01-01 00:00:00.001') ,  strftime('%Y-%m-%d %H:%M:%f', '1970-01-01 00:00:00.002') ,  'server' );

There are no results to be displayed.

Query #8

SELECT LAST_INSERT_ROWID() AS "__aliased--value" LIMIT 2 OFFSET 0;
__aliased--value
1

Query #9

SELECT "appKey"."appId" AS "appKey--appId" , "appKey"."appKeyId" AS "appKey--appKeyId" , "appKey"."appKeyTypeId" AS "appKey--appKeyTypeId" , "appKey"."createdAt" AS "appKey--createdAt" , "appKey"."disabledAt" AS "appKey--disabledAt" , "appKey"."key" AS "appKey--key" FROM "appKey" WHERE "appKey"."appKeyId" IS 1 LIMIT 2 OFFSET 0;
appKey--appId appKey--appKeyId appKey--appKeyTypeId appKey--createdAt appKey--disabledAt appKey--key
1 1 1 1970-01-01 00:00:00.001 1970-01-01 00:00:00.002 server

Query #10

INSERT  INTO "serverAppKey" ( "appKeyId", "ipAddress", "trustProxy" ) VALUES ( 1 ,  'ip' ,  FALSE );

There are no results to be displayed.

Query #11

SELECT "serverAppKey"."appKeyId" AS "serverAppKey--appKeyId" , "serverAppKey"."appKeyTypeId" AS "serverAppKey--appKeyTypeId" , "serverAppKey"."ipAddress" AS "serverAppKey--ipAddress" , "serverAppKey"."trustProxy" AS "serverAppKey--trustProxy" FROM "serverAppKey" WHERE "serverAppKey"."appKeyId" IS 1 LIMIT 2 OFFSET 0;
serverAppKey--appKeyId serverAppKey--appKeyTypeId serverAppKey--ipAddress serverAppKey--trustProxy
1 1 ip 0

Query #12

INSERT  INTO "specialServerAppKey" ( "appKeyId" ) VALUES ( 1 );

There are no results to be displayed.

Query #13

SELECT "specialServerAppKey"."appKeyId" AS "specialServerAppKey--appKeyId" FROM "specialServerAppKey" WHERE "specialServerAppKey"."appKeyId" IS 1 LIMIT 2 OFFSET 0;
specialServerAppKey--appKeyId
1

Query #14

SELECT EXISTS(SELECT *FROM "specialServerAppKey" WHERE "specialServerAppKey"."appKeyId" = 1) AS "__aliased--value" LIMIT 2 OFFSET 0;
__aliased--value
1

Query #15

SAVEPOINT tsql_savepoint_2;

There are no results to be displayed.

Query #16

SELECT "specialServerAppKey"."appKeyId" AS "specialServerAppKey--appKeyId" FROM "specialServerAppKey" INNER JOIN "serverAppKey" ON "specialServerAppKey"."appKeyId" IS "serverAppKey"."appKeyId" INNER JOIN "appKey" ON "serverAppKey"."appKeyId" IS "appKey"."appKeyId" WHERE "specialServerAppKey"."appKeyId" = 1 LIMIT 2 OFFSET 0;
specialServerAppKey--appKeyId
1

Query #17

SAVEPOINT tsql_savepoint_3;

There are no results to be displayed.

Query #18

DELETE FROM "specialServerAppKey" WHERE ("specialServerAppKey"."appKeyId" = 1);

There are no results to be displayed.

Query #19

RELEASE SAVEPOINT tsql_savepoint_3;

There are no results to be displayed.

Query #20

SAVEPOINT tsql_savepoint_4;

There are no results to be displayed.

Query #21

DELETE FROM "serverAppKey" WHERE ("serverAppKey"."appKeyId" = 1);

There are no results to be displayed.

Query #22

RELEASE SAVEPOINT tsql_savepoint_4;

There are no results to be displayed.

Query #23

SAVEPOINT tsql_savepoint_5;

There are no results to be displayed.

Query #24

DELETE FROM "appKey" WHERE ("appKey"."appKeyId" = 1);

There are no results to be displayed.

Query #25

RELEASE SAVEPOINT tsql_savepoint_5;

There are no results to be displayed.

Query #26

RELEASE SAVEPOINT tsql_savepoint_2;

There are no results to be displayed.

Query #27

SELECT EXISTS(SELECT *FROM "specialServerAppKey" WHERE "specialServerAppKey"."appKeyId" = 1) AS "__aliased--value" LIMIT 2 OFFSET 0;
__aliased--value
0

Query #28

RELEASE SAVEPOINT tsql_savepoint_1;

There are no results to be displayed.

Query #29

ROLLBACK;

There are no results to be displayed.

Query #30

SELECT "specialServerAppKey"."appKeyId" AS "specialServerAppKey--appKeyId" FROM "specialServerAppKey";

There are no results to be displayed.

View on DB Fiddle
@AnyhowStep AnyhowStep self-assigned this Jan 4, 2020
@AnyhowStep
Copy link
Owner Author

The 30th query should return a row. We deleted stuff in a transaction, but rolled back.

@AnyhowStep
Copy link
Owner Author

Schema (SQLite v3.26)

Query #1

CREATE TABLE appKey (
    appId INTEGER NOT NULL,
    appKeyId INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    appKeyTypeId INTEGER NOT NULL,
    key VARCHAR(255) NOT NULL,
    createdAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    disabledAt DATETIME
);

There are no results to be displayed.

Query #2

CREATE TABLE browserAppKey (
    appKeyId INTEGER NOT NULL,
    appKeyTypeId INTEGER NOT NULL DEFAULT (2),
    referer VARCHAR(2048),
    FOREIGN KEY (appKeyId, appKeyTypeId) REFERENCES appKey (appKeyId, appKeyTypeId)
);

There are no results to be displayed.

Query #3

CREATE TABLE serverAppKey (
    appKeyId INTEGER NOT NULL,
    appKeyTypeId INTEGER NOT NULL DEFAULT (1),
    ipAddress VARCHAR(2048),
    trustProxy BOOLEAN NOT NULL DEFAULT (FALSE),
    FOREIGN KEY (appKeyId, appKeyTypeId) REFERENCES appKey (appKeyId, appKeyTypeId)
);

There are no results to be displayed.

Query #4

CREATE TABLE specialServerAppKey (
	appKeyId INTEGER NOT NULL,
	FOREIGN KEY (appKeyId) REFERENCES serverAppKey (appKeyId)
);

There are no results to be displayed.

Query #5

BEGIN TRANSACTION;

There are no results to be displayed.

Query #6

INSERT INTO "appKey" ( "appId", "appKeyTypeId", "createdAt", "disabledAt", "key" ) VALUES ( 1 ,  1 ,  strftime('%Y-%m-%d %H:%M:%f', '1970-01-01 00:00:00.001') ,  strftime('%Y-%m-%d %H:%M:%f', '1970-01-01 00:00:00.002') ,  'server' );

There are no results to be displayed.

Query #7

SELECT LAST_INSERT_ROWID() AS "__aliased--value" LIMIT 2 OFFSET 0;
__aliased--value
1

Query #8

SELECT "appKey"."appId" AS "appKey--appId" , "appKey"."appKeyId" AS "appKey--appKeyId" , "appKey"."appKeyTypeId" AS "appKey--appKeyTypeId" , "appKey"."createdAt" AS "appKey--createdAt" , "appKey"."disabledAt" AS "appKey--disabledAt" , "appKey"."key" AS "appKey--key" FROM "appKey" WHERE "appKey"."appKeyId" IS 1 LIMIT 2 OFFSET 0;
appKey--appId appKey--appKeyId appKey--appKeyTypeId appKey--createdAt appKey--disabledAt appKey--key
1 1 1 1970-01-01 00:00:00.001 1970-01-01 00:00:00.002 server

Query #9

INSERT  INTO "serverAppKey" ( "appKeyId", "ipAddress", "trustProxy" ) VALUES ( 1 ,  'ip' ,  FALSE );

There are no results to be displayed.

Query #10

SELECT "serverAppKey"."appKeyId" AS "serverAppKey--appKeyId" , "serverAppKey"."appKeyTypeId" AS "serverAppKey--appKeyTypeId" , "serverAppKey"."ipAddress" AS "serverAppKey--ipAddress" , "serverAppKey"."trustProxy" AS "serverAppKey--trustProxy" FROM "serverAppKey" WHERE "serverAppKey"."appKeyId" IS 1 LIMIT 2 OFFSET 0;
serverAppKey--appKeyId serverAppKey--appKeyTypeId serverAppKey--ipAddress serverAppKey--trustProxy
1 1 ip 0

Query #11

INSERT  INTO "specialServerAppKey" ( "appKeyId" ) VALUES ( 1 );

There are no results to be displayed.

Query #12

SELECT "specialServerAppKey"."appKeyId" AS "specialServerAppKey--appKeyId" FROM "specialServerAppKey" WHERE "specialServerAppKey"."appKeyId" IS 1 LIMIT 2 OFFSET 0;
specialServerAppKey--appKeyId
1

Query #13

SELECT EXISTS(SELECT *FROM "specialServerAppKey" WHERE "specialServerAppKey"."appKeyId" = 1) AS "__aliased--value" LIMIT 2 OFFSET 0;
__aliased--value
1

Query #14

SELECT "specialServerAppKey"."appKeyId" AS "specialServerAppKey--appKeyId" FROM "specialServerAppKey" INNER JOIN "serverAppKey" ON "specialServerAppKey"."appKeyId" IS "serverAppKey"."appKeyId" INNER JOIN "appKey" ON "serverAppKey"."appKeyId" IS "appKey"."appKeyId" WHERE "specialServerAppKey"."appKeyId" = 1 LIMIT 2 OFFSET 0;
specialServerAppKey--appKeyId
1

Query #15

DELETE FROM "specialServerAppKey" WHERE ("specialServerAppKey"."appKeyId" = 1);

There are no results to be displayed.

Query #16

DELETE FROM "serverAppKey" WHERE ("serverAppKey"."appKeyId" = 1);

There are no results to be displayed.

Query #17

DELETE FROM "appKey" WHERE ("appKey"."appKeyId" = 1);

There are no results to be displayed.

Query #18

SELECT EXISTS(SELECT *FROM "specialServerAppKey" WHERE "specialServerAppKey"."appKeyId" = 1) AS "__aliased--value" LIMIT 2 OFFSET 0;
__aliased--value
0

Query #19

ROLLBACK;

There are no results to be displayed.

Query #20

SELECT "specialServerAppKey"."appKeyId" AS "specialServerAppKey--appKeyId" FROM "specialServerAppKey";

There are no results to be displayed.

View on DB Fiddle

@AnyhowStep
Copy link
Owner Author

Even without savepoints, we still see this problem

@AnyhowStep
Copy link
Owner Author

Nope. Not a bug. We inserted those rows inside a transaction.
Obviously, they wouldn't exist outside.
Brain fart.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@AnyhowStep AnyhowStep

Labels
sqlite
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@AnyhowStep