SQLite Forum

Timeline
Login

50 most recent forum posts by user krking

2021-09-08
16:42 Reply: Segmentation fault in function isLikeOrGlob() (artifact: 10c784a193 user: krking)

We have reported several bugs before, and in the process, we continue to accumulate experience and think about how we can reduce the work of developers so that the problem can be fixed as soon as possible.

So my previous reply was just to sum up this experience, and I didn't feel bad.

Anyway, thanks for your encouragement.

14:17 Reply: Segmentation fault in function isLikeOrGlob() (artifact: b3cba5ebda user: krking)

Thanks for the praise.

Actually, we could do better by simplifying the test case as Dr. Hipp did.

We will continue to find potential bugs in SQLite to help improve the reliability of the software.

05:06 Reply: Segmentation fault in function isLikeOrGlob() (artifact: bf5e2c1e7d user: krking)

Well, it's really hard to estimate the cost of CPU-hours.

Our fuzzer is running on a single CPU core, and it took about 1-10 hours from start to when we first found the testcase (I didn't monitor the fuzzing progress during that time so I don't know the exact cost).

In fact, I think the initial seed matters a lot. When we use another input seed (looks completely different from this testcase, which triggers other crash, while we have not yet found a testcase suitable for reporting), within less than an hour a testcase was generated that could cause this bug.

2021-09-07
15:59 Reply: Segmentation fault in function isLikeOrGlob() (artifact: bfa736447e user: krking)

Thanks for the quick fix!

14:42 Edit: Segmentation fault in function isLikeOrGlob() (artifact: b8bc964a37 user: krking)

Hello developers,

We found a testcase causing a SQLite crash exception:

CREATE TABLE t0(a PRIMARY KEY,b CHAR(30) AS(1) UNIQUE) WITHOUT ROWID;
SELECT * FROM t0 JOIN t0 AS ra0 ON unlikely(ra0.a=t0.a) AND t0.b='iii' WHERE ra0.a=false OR t0.b LIKE '.' AND t0.a=ra0.b;

We simply analyzed the cause of the crash, locating it in the function isLikeOrGlob().

In this line:

|| IsVirtual(pLeft->y.pTab) /* Value might be numeric */

pLeft->y.pTab is a NULL pointer, and the use of macro:

# define IsVirtual(X) ((X)->eTabType==TABTYP_VTAB)

triggers the segmentation fault.

Bisecting shows the problem may be related to check-in b99d570131.

14:26 Post: Segmentation fault in function isLikeOrGlob() (artifact: 699b44b3ee user: krking)

Hello developers,

We found a testcase causing a SQLite crash exception:

CREATE TABLE t0(a PRIMARY KEY,b CHAR(30) AS(1) UNIQUE) WITHOUT ROWID;
SELECT * FROM t0 JOIN t0 AS ra0 ON unlikely(ra0.a=t0.a) AND t0.b='iii' WHERE ra0.a=false OR t0.b LIKE '.' AND t0.a=ra0.b;

We simply analyzed the cause of the crash, locating it in the function isLikeOrGlob().

In this line:

|| IsVirtual(pLeft->y.pTab) /* Value might be numeric */

pLeft->y.pTab is a NULL pointer, and the use of macro:

# define IsVirtual(X) ((X)->eTabType==TABTYP_VTAB)

triggers this problem.

2021-08-30
16:22 Edit: Unexpected result in a equivalence transfer query (artifact: 11c5e9ed19 user: krking)

Hello developers,

We found a testcase making SQLite outputs the wrong result as follows:

CREATE TABLE t1(a INT PRIMARY KEY);
INSERT INTO t1(a) VALUES(1),(2),(3);
CREATE TABLE t2(x INTEGER PRIMARY KEY,y INT);
INSERT INTO t2(y) VALUES(2),(3);
SELECT * FROM t2,t1 WHERE t2.y=t1.a AND t1.a=t2.x; -- expected: empty
SELECT * FROM t2,t1 WHERE likely(t2.y=t1.a) AND unlikely(t1.a=t2.x); -- unexpected: two lines

Presumably, this is a legacy issue related to the recent fix (check-in f1f9b5de) for the equivalence transfer problem, you probably forgot to consider the condition using PRIMARY KEY when dealing with equivalent transfer optimization.

Looking forward to your reply, further details will be appreciated!

06:44 Post: Unexpected result in a equivalence transfer query (artifact: 8d1b58f112 user: krking)

We found a testcase that outputs the wrong result as follows:

CREATE TABLE t1(a INT PRIMARY KEY);
INSERT INTO t1(a) VALUES(1),(2),(3);
CREATE TABLE t2(x INTEGER PRIMARY KEY,y INT);
INSERT INTO t2(y) VALUES(2),(3);
SELECT * FROM t2,t1 WHERE t2.y=t1.a AND t1.a=t2.x; -- expected: empty
SELECT * FROM t2,t1 WHERE likely(t2.y=t1.a) AND unlikely(t1.a=t2.x); -- unexpected: two lines

Presumably, this is a legacy issue related to the recent fix (check-in f1f9b5de) for the equivalence transfer problem, you probably forgot to consider the condition using PRIMARY KEY when dealing with equivalent transfer optimization.

2021-08-21
17:33 Reply: Assertion failure "pExpr->pAggInfo==pAggInfo" (artifact: 5e54d49628 user: krking)

Thanks for your explanation.

It's good to know that is a harmless coding error which not affects the normal execution process.

By the way, recently we're collecting more testcases, especially those triggering errors(crashes or logical errors) in old versions in order to find out more potential problems with our approach. We have noticed that TH3 is a useful tool for you developers to conduct testing works. So we are trying to find those TH3 testing modules like "orindex01.test". But I didn't find the file in the directory of SQLite. I wonder, how can we access these test data? Or are those data not public?

Looking forward to your reply.

11:31 Post: Assertion failure "pExpr->pAggInfo==pAggInfo" (artifact: cfcb4b461d user: krking)

Hello,

We found an assertion failure in the latest SQLite, and the test case is as follows:

CREATE TABLE t1(a);
SELECT MIN((SELECT NULL FROM t1 UNION SELECT 'X' FROM (SELECT NULL FROM (SELECT 1 WHERE t1.a=1) UNION ALL SELECT 'X' FROM (SELECT 2)))) FROM t1;

which triggers:

sqlite3.c:139665: sqlite3Select: Assertion `pExpr->pAggInfo==pAggInfo' failed.

Bisecting shows the issue may be introduced in check-in 7682d8a768.

2021-08-06
23:00 Reply: Bug: unexpected result from an empty table (artifact: 36222988c3 user: krking)

OK, thank you for your explanation and update to the documentation.

We have realized that it is a bare column, but it was not clear what the value of the column should be when the result set is empty. We used to think that "arbitrary" refers to "any one in the result set". When the result set is empty, it can only be NULL. Your update to the documentation has well corrected our thoughts and answered our doubts.

18:23 Reply: Bug: inconsistent result when an optimization is on and off (artifact: 6dc048f813 user: krking)

Long time no see, Richard.

I didn't realize that it's a compound query suiting that rule because I only remember it is a rule applicable to view.

After all, thanks for your patient explanation, and sorry for the false positive report.

I will read the documentation more carefully before reporting any issues.

07:43 Post: Bug: unexpected result from an empty table (artifact: 5b5c2a0e40 user: krking)

Hello,

For this case:

CREATE TABLE t0(c0);
SELECT COUNT(t1.c1), t1.c1 FROM (SELECT 1 AS c1 FROM t0) AS t1; -- 0|1

It says in the documentation:

If the SELECT statement is an aggregate query without a GROUP BY clause, then each aggregate expression in the result-set is evaluated once across the entire dataset. Each non-aggregate expression in the result-set is evaluated once for an arbitrarily selected row of the dataset. The same arbitrarily selected row is used for each non-aggregate expression. Or, if the dataset contains zero rows, then each non-aggregate expression is evaluated against a row consisting entirely of NULL values.

Since the table t0 is empty, interim result of subquery SELECT 1 AS c1 FROM t0 should be empty, and the expected result for the whole query should be 0| instead of 0|1.

I think the cause of the problem is still related to optimization SQLITE_QueryFlattener, when we turn off this optimization, we can get the desired result.

07:33 Post: Bug: inconsistent result when an optimization is on and off (artifact: 1821d30133 user: krking)

Hello developers,

For this case:

-- Version: latest(hash: check-in: 832ac4c1)
CREATE TABLE t0(c0 INT,c1 INT);
INSERT INTO t0 VALUES(10,10);

SELECT * FROM t0 JOIN (SELECT CAST(c0 AS TEXT) AS c2 FROM t0 UNION ALL SELECT c1 FROM t0) WHERE 10=c2; -- 2 lines
.testctrl optimizations 0x00000001;
SELECT '------';
SELECT * FROM t0 JOIN (SELECT CAST(c0 AS TEXT) AS c2 FROM t0 UNION ALL SELECT c1 FROM t0) WHERE 10=c2; -- 1 lines

When the optimization option SQLITE_QueryFlattener is off, we get incorrect result, which indicates that something could be going wrong here.

Hope it will be solved properly if there is indeed a problem here, and if there is no problem, your kind explanation would be appreciated.

Looking forward to your reply.

Thank you!

2021-06-23
09:36 Edit: An infinite recursive exception (artifact: 31e0432608 user: krking)

Hello developers,

We found a test case which causes an infinite recursion exception:

CREATE TABLE t1(a);
SELECT substr(a,4,lag(a,7) OVER(PARTITION BY 'cf23' ORDER BY 2)) AS ca0 FROM t1 ORDER BY ca0; -- expected: empty
SELECT substr(a,4,lag(a,7) OVER(PARTITION BY 'cf23' ORDER BY likely(2))) AS ca0 FROM t1 ORDER BY ca0; -- unexpected: Error: Expression tree is too large (maximum depth 1000)

The difference between the first and the second query is only a function likely().

The height of the first query is 4, but the second query results in infinite recursion, constantly calling the function sqlite3Select() until the exception is found by the function sqlite3ExprCheckHeight().

Besides, bisecting shows that the problem maybe first appear in check-in 57070c68bb.

09:31 Post: An infinite recursive exception (artifact: d0a2d95701 user: krking)

Hello developers,

We found a test case which causes an infinite recursive exception:

CREATE TABLE t1(a);
SELECT substr(a,4,lag(a,7) OVER(PARTITION BY 'cf23' ORDER BY 2)) AS ca0 FROM t1 ORDER BY ca0; -- expected: empty
SELECT substr(a,4,lag(a,7) OVER(PARTITION BY 'cf23' ORDER BY likely(2))) AS ca0 FROM t1 ORDER BY ca0; -- unexpected: Error: Expression tree is too large (maximum depth 1000)

The difference between the first and the second query is only a function likely(). The height of the first query is 4, but the second query results in infinite recursion, constantly calling the function sqlite3Select() until the exception is found by the function sqlite3ExprCheckHeight().

Besides, bisecting shows that the problem first appear in check-in 57070c68bb.

00:43 Reply: A crash bug in MULTI-INDEX OR (artifact: 7752257919 user: krking)

Thanks for the fix.

We will continue to produce similar test cases to verify whether the fix is perfect.

2021-06-22
17:39 Edit: A crash bug in MULTI-INDEX OR (artifact: 0575376e07 user: krking)

Hi,

We found a crash bug existing in SQLite 3.36.0, which may be triggered by:

CREATE TABLE IF NOT EXISTS t1(a int, b);
CREATE TABLE t2(e, d, f);
CREATE INDEX i1 ON t1(a);
INSERT INTO t2(f, d, e) VALUES (5, 2.0, 7);
SELECT e FROM t2 LEFT JOIN t1 INDEXED BY i1 ON likely(a=e) WHERE (b=5 AND e=12) OR (e=11 AND a=4) ORDER BY e; -- Segmentation fault (core dumped)

The query plan looks like this:

QUERY PLAN
|--SCAN t2
|--MULTI-INDEX OR
|  |--INDEX 1
|  |  `--SEARCH t1 USING INDEX i1 (a=?)
|  `--INDEX 2
|     `--SEARCH t1 USING INDEX i1 (a=?)
`--USE TEMP B-TREE FOR ORDER BY

Note that we use functions in order to adjust the cost so that the specific query solution is chosen, there may be other ways to do so.

Here are the bytecode for the query:

addr  opcode         p1    p2    p3    p4             p5  comment      
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     63    0                    0   Start at 63
1     SorterOpen     2     3     0     k(1,B)         0   
2     OpenRead       0     3     0     1              0   root=3 iDb=0; t2
3     OpenRead       1     2     0     2              0   root=2 iDb=0; t1
4     Rewind         0     56    0                    0   
5       Integer        0     1     0                    0   r[1]=0; init LEFT JOIN no-match flag
6       Null           0     3     0                    0   r[3]=NULL
7       Integer        34    2     0                    0   r[2]=34
8       Column         0     0     5                    0   r[5]=t2.e
9       Ne             6     21    5     BINARY-8       81  if r[5]!=r[6] goto 21
10      ReopenIdx      3     4     0     k(2,,)         2   root=4 iDb=0; i1
11      Column         0     0     7                    0   r[7]=t2.e
12      IsNull         7     21    0                    0   if r[7]==NULL goto 21
13      Affinity       7     1     0     C              0   affinity(r[7])
14      SeekGE         3     21    7     1              0   key=r[7]
15        IdxGT          3     21    7     1              0   key=r[7]
16        DeferredSeek   3     0     1     [1,0]          0   Move 1 to 3.rowid if needed
17        IdxRowid       3     4     0                    0   r[4]=rowid
18        RowSetTest     3     20    4     0              0   if r[4] in rowset(3) goto 20
19        Gosub          2     35    0                    0   
20      Next           3     15    1                    0   
21      Column         0     0     5                    0   r[5]=t2.e
22      Ne             8     34    5     BINARY-8       81  if r[5]!=r[8] goto 34
23      ReopenIdx      3     4     0     k(2,,)         2   root=4 iDb=0; i1
24      Column         0     0     9                    0   r[9]=t2.e
25      IsNull         9     34    0                    0   if r[9]==NULL goto 34
26      Affinity       9     1     0     C              0   affinity(r[9])
27      SeekGE         3     34    9     1              0   key=r[9]
28        IdxGT          3     34    9     1              0   key=r[9]
29        DeferredSeek   3     0     1     [1,0]          0   Move 1 to 3.rowid if needed
30        IdxRowid       3     4     0                    0   r[4]=rowid
31        RowSetTest     3     33    4     -1             0   if r[4] in rowset(3) goto 33
32        Gosub          2     35    0                    0   
33      Next           3     28    1                    0   
34      Goto           0     51    0                    0   
35      Column         3     0     5                    0   r[5]=t1.a
36      Column         0     0     10                   0   r[10]=t2.e
37      Ne             10    50    5     BINARY-8       83  if r[5]!=r[10] goto 50
38      Integer        1     1     0                    0   r[1]=1; record LEFT JOIN hit
39      Column         1     1     10                   0   r[10]=t1.b
40      Ne             11    43    10    BINARY-8       81  if r[10]!=r[11] goto 43
41      Column         0     0     10                   0   r[10]=t2.e
42      Eq             6     47    10    BINARY-8       65  if r[10]==r[6] goto 47
43      Column         0     0     10                   0   r[10]=t2.e
44      Ne             8     50    10    BINARY-8       81  if r[10]!=r[8] goto 50
45      Column         3     0     10                   0   r[10]=t1.a
46      Ne             12    50    10    BINARY-8       84  if r[10]!=r[12] goto 50
47      Column         0     0     13                   0   r[13]=t2.e
48      MakeRecord     13    1     15                   0   r[15]=mkrec(r[13])
49      SorterInsert   2     15    13    1              0   key=r[15]
50      Return         2     0     0                    0   
51      IfPos          1     55    0                    0   if r[1]>0 then r[1]-=0, goto 55
52      NullRow        1     0     0                    0   
53      NullRow        3     0     0                    0   
54      Gosub          2     38    0                    0   
55    Next           0     5     0                    1   
56    OpenPseudo     4     16    3                    0   3 columns in r[16]
57    SorterSort     2     62    0                    0   
58      SorterData     2     16    4                    0   r[16]=data
59      Column         4     0     14                   0   r[14]=e
60      ResultRow      14    1     0                    0   output=r[14]
61    SorterNext     2     58    0                    0   
62    Halt           0     0     0                    0   
63    Transaction    0     0     3     0              1   usesStmtJournal=0
64    Integer        12    6     0                    0   r[6]=12
65    Integer        11    8     0                    0   r[8]=11
66    Integer        5     11    0                    0   r[11]=5
67    Integer        4     12    0                    0   r[12]=4
68    Goto           0     1     0                    0   

It seems that it's caused by ReopenIdx an index without open an index first.

Besides, through bisecting, we find that the crash problem maybe first appear in check-in ce35e39c.

Hope these information will help you locate the cause of the bug more easily.

Looking forward to your reply :)

17:15 Post: A crash bug in MULTI-INDEX OR (artifact: db47d1277d user: krking)

Hi,

We found a crash bug existing in SQLite 3.36.0, which may be triggered by:

CREATE TABLE IF NOT EXISTS t1(a int, b);
CREATE TABLE t2(e, d, f);
CREATE INDEX i1 ON t1(a);
INSERT INTO t2(f, d, e) VALUES (5, 2.0, 7);
SELECT e FROM t2 LEFT JOIN t1 INDEXED BY i1 ON likely(a=e) WHERE (b=5 AND e=12) OR (e=11 AND a=4) ORDER BY e; -- Segmentation fault (core dumped)

The query plan looks like this:

QUERY PLAN
|--SCAN t2
|--MULTI-INDEX OR
|  |--INDEX 1
|  |  `--SEARCH t1 USING INDEX i1 (a=?)
|  `--INDEX 2
|     `--SEARCH t1 USING INDEX i1 (a=?)
`--USE TEMP B-TREE FOR ORDER BY

Note that we used functions in order to trigger this specific query solution, there may be other ways to do so.

Here are the bytecode for the query:

addr  opcode         p1    p2    p3    p4             p5  comment      
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     63    0                    0   Start at 63
1     SorterOpen     2     3     0     k(1,B)         0   
2     OpenRead       0     3     0     1              0   root=3 iDb=0; t2
3     OpenRead       1     2     0     2              0   root=2 iDb=0; t1
4     Rewind         0     56    0                    0   
5       Integer        0     1     0                    0   r[1]=0; init LEFT JOIN no-match flag
6       Null           0     3     0                    0   r[3]=NULL
7       Integer        34    2     0                    0   r[2]=34
8       Column         0     0     5                    0   r[5]=t2.e
9       Ne             6     21    5     BINARY-8       81  if r[5]!=r[6] goto 21
10      ReopenIdx      3     4     0     k(2,,)         2   root=4 iDb=0; i1
11      Column         0     0     7                    0   r[7]=t2.e
12      IsNull         7     21    0                    0   if r[7]==NULL goto 21
13      Affinity       7     1     0     C              0   affinity(r[7])
14      SeekGE         3     21    7     1              0   key=r[7]
15        IdxGT          3     21    7     1              0   key=r[7]
16        DeferredSeek   3     0     1     [1,0]          0   Move 1 to 3.rowid if needed
17        IdxRowid       3     4     0                    0   r[4]=rowid
18        RowSetTest     3     20    4     0              0   if r[4] in rowset(3) goto 20
19        Gosub          2     35    0                    0   
20      Next           3     15    1                    0   
21      Column         0     0     5                    0   r[5]=t2.e
22      Ne             8     34    5     BINARY-8       81  if r[5]!=r[8] goto 34
23      ReopenIdx      3     4     0     k(2,,)         2   root=4 iDb=0; i1
24      Column         0     0     9                    0   r[9]=t2.e
25      IsNull         9     34    0                    0   if r[9]==NULL goto 34
26      Affinity       9     1     0     C              0   affinity(r[9])
27      SeekGE         3     34    9     1              0   key=r[9]
28        IdxGT          3     34    9     1              0   key=r[9]
29        DeferredSeek   3     0     1     [1,0]          0   Move 1 to 3.rowid if needed
30        IdxRowid       3     4     0                    0   r[4]=rowid
31        RowSetTest     3     33    4     -1             0   if r[4] in rowset(3) goto 33
32        Gosub          2     35    0                    0   
33      Next           3     28    1                    0   
34      Goto           0     51    0                    0   
35      Column         3     0     5                    0   r[5]=t1.a
36      Column         0     0     10                   0   r[10]=t2.e
37      Ne             10    50    5     BINARY-8       83  if r[5]!=r[10] goto 50
38      Integer        1     1     0                    0   r[1]=1; record LEFT JOIN hit
39      Column         1     1     10                   0   r[10]=t1.b
40      Ne             11    43    10    BINARY-8       81  if r[10]!=r[11] goto 43
41      Column         0     0     10                   0   r[10]=t2.e
42      Eq             6     47    10    BINARY-8       65  if r[10]==r[6] goto 47
43      Column         0     0     10                   0   r[10]=t2.e
44      Ne             8     50    10    BINARY-8       81  if r[10]!=r[8] goto 50
45      Column         3     0     10                   0   r[10]=t1.a
46      Ne             12    50    10    BINARY-8       84  if r[10]!=r[12] goto 50
47      Column         0     0     13                   0   r[13]=t2.e
48      MakeRecord     13    1     15                   0   r[15]=mkrec(r[13])
49      SorterInsert   2     15    13    1              0   key=r[15]
50      Return         2     0     0                    0   
51      IfPos          1     55    0                    0   if r[1]>0 then r[1]-=0, goto 55
52      NullRow        1     0     0                    0   
53      NullRow        3     0     0                    0   
54      Gosub          2     38    0                    0   
55    Next           0     5     0                    1   
56    OpenPseudo     4     16    3                    0   3 columns in r[16]
57    SorterSort     2     62    0                    0   
58      SorterData     2     16    4                    0   r[16]=data
59      Column         4     0     14                   0   r[14]=e
60      ResultRow      14    1     0                    0   output=r[14]
61    SorterNext     2     58    0                    0   
62    Halt           0     0     0                    0   
63    Transaction    0     0     3     0              1   usesStmtJournal=0
64    Integer        12    6     0                    0   r[6]=12
65    Integer        11    8     0                    0   r[8]=11
66    Integer        5     11    0                    0   r[11]=5
67    Integer        4     12    0                    0   r[12]=4
68    Goto           0     1     0                    0   

It seems that it's caused by ReopenIdx an index without open an index first.

Through bisecting, we find that the crash problem may be first appear in check-in ce35e39c.

2021-06-14
14:10 Reply: Affinity problem existing in functions likely() (artifact: 1df339770c user: krking)

Thank you for the fix.

12:08 Edit: Affinity problem existing in functions likely() (artifact: 3b940c437a user: krking)

Hello all,

Consider the following behaviors:

CREATE TABLE t0 (c0 REAL);
INSERT INTO t0(c0) VALUES (0);
INSERT INTO t0(c0) VALUES (1);
CREATE UNIQUE INDEX idx ON t0(likely(c0));

SELECT count(*) FROM t0 WHERE c0 GLOB c0; --2
SELECT count(*) FROM t0 WHERE likely(c0) GLOB c0; --2
SELECT count(*) FROM t0 INDEXED BY idx WHERE c0 GLOB c0; --2
SELECT count(*) FROM t0 INDEXED BY idx WHERE likely(c0) GLOB c0; --0
SELECT count(*) FROM t0 INDEXED BY idx WHERE likely(c0) LIKE c0; --0
SELECT count(*) FROM t0 INDEXED BY idx WHERE CAST(likely(c0) AS TEXT) GLOB CAST(c0 AS TEXT); --0
SELECT count(*) FROM t0 INDEXED BY idx WHERE CAST(likely(c0) AS TEXT) LIKE CAST(c0 AS TEXT); --0

By specifying the index used and applying the likely() function, we can get unexpected results, indicating that there may be some problems concerning likely(). Besides, we also find opcode RealAffinity missing in this query.

Through bisecting, we found that the problem may be introduced in 44578865fa.

I'd like to know whether it's a bug or a feature.

Thank you!

12:02 Post: Affinity problem existing in functions likely() (artifact: 37dc1ebe44 user: krking)

Hello all,

Consider the following behaviors:

CREATE TABLE t0 (c0 REAL);
INSERT INTO t0(c0) VALUES (0);
INSERT INTO t0(c0) VALUES (1);
CREATE UNIQUE INDEX idx ON t0(likely(c0));

SELECT count(*) FROM t0 WHERE c0 GLOB c0; --2
SELECT count(*) FROM t0 WHERE likely(c0) GLOB c0; --2
SELECT count(*) FROM t0 INDEXED BY idx WHERE c0 GLOB c0; --2
SELECT count(*) FROM t0 INDEXED BY idx WHERE likely(c0) GLOB c0; --0
SELECT count(*) FROM t0 INDEXED BY idx WHERE likely(c0) LIKE c0; --0
SELECT count(*) FROM t0 INDEXED BY idx WHERE CAST(likely(c0) AS TEXT) GLOB CAST(c0 AS TEXT); --0
SELECT count(*) FROM t0 INDEXED BY idx WHERE CAST(likely(c0) AS TEXT) LIKE CAST(c0 AS TEXT); --0

By specifying the index used and applying the likely() function, we can get unexpected results, indicating that there may be some problems concerning likely(). Besides, we also find opcode RealAffinity missing in this query.

Through bisecting, we found that the problem may be introduced in 44578865fa.

I'd like to know whether it's a bug or a feature.

Thank you!

2021-06-02
14:53 Reply: Bug report: a bug in VIEW (artifact: ed2bd4cab7 user: krking)

Thanks for the explanation.

I have made changes to the type and tested again.

CREATE TABLE t0(c0, c1);

CREATE VIEW v0(c0) AS SELECT c1 FROM t0 EXCEPT SELECT c0 FROM t0;

INSERT INTO t0 VALUES (NULL, 1);

SELECT c0 FROM v0; -- 1

SELECT c1 FROM t0 EXCEPT SELECT c0 FROM t0; -- 1

SELECT (c0 NOT BETWEEN '-1' AND c0) FROM v0; -- 1

SELECT * FROM v0 WHERE (c0 NOT BETWEEN '-1' AND c0); -- 1

and

CREATE TABLE t0(c0 INT, c1 INT);

CREATE VIEW v0(c0) AS SELECT c1 FROM t0 EXCEPT SELECT c0 FROM t0;

INSERT INTO t0 VALUES (NULL, 1);

SELECT c0 FROM v0; -- 1

SELECT c1 FROM t0 EXCEPT SELECT c0 FROM t0; -- 1

SELECT (c0 NOT BETWEEN '-1' AND c0) FROM v0; -- 0

SELECT * FROM v0 WHERE (c0 NOT BETWEEN '-1' AND c0); -- empty

It is indeed the same as you say. So it's not a bug.

Thank you for all.

13:31 Edit: Bug report: a bug in VIEW (artifact: 02d7be94d7 user: krking)

Hi all,

For this example:

CREATE TABLE t0(c0 , c1 INT);
CREATE VIEW v0(c0) AS SELECT c1 FROM t0 EXCEPT SELECT c0 FROM t0;
INSERT INTO t0 VALUES (NULL, 1);

SELECT * FROM v0; -- 1
SELECT c1 FROM t0 EXCEPT SELECT c0 FROM t0; -- 1
SELECT (c0 NOT BETWEEN '-1' AND c0) FROM v0; -- 1
SELECT * FROM v0 WHERE (c0 NOT BETWEEN '-1' AND c0); -- empty

I have tried to find the commit which causes this problem through bisect, but I find that it seems like this problem has been existing for a long time. Since d794b34da6 and bed42116ad allowed us to use column names of a view, the problem already exists.

13:26 Edit reply: Bug report: a bug in VIEW (artifact: b946a51948 user: krking)

Thanks for the information.

When I am on bed42116ad, which is later than d794b34da6, SQLite prompts an error message:

./sqlite3 test.db
SQLite version 3.8.6 2014-08-16 19:01:00
Enter ".help" for usage hints.
sqlite> CREATE TABLE t0(a , b INT);
sqlite> INSERT INTO t0 VALUES (NULL, 1);
sqlite> CREATE VIEW v0(c) AS SELECT b FROM t0 EXCEPT SELECT a FROM t0;
Error: near "(": syntax error
sqlite> CREATE VIEW v1 AS SELECT b AS 'c' FROM t0 EXCEPT SELECT a FROM t0;
sqlite> SELECT (c NOT BETWEEN '-1' AND c) AS 'x' FROM v0; -- 1
Error: no such table: v0
sqlite> SELECT (c NOT BETWEEN '-1' AND c) AS 'x' FROM v1; -- 0
0

It seems that after introduced the ability to specify the column names of a view, the ability disappeared. So I can't figure out exactly which check-in introduced the problem.

Besides, there are some chech-in that can't compile, like 227bb8a181.

sqlite3.c: In function ‘pagerFreeBitvecs’:
sqlite3.c:44125:30: error: ‘Pager’ {aka ‘struct Pager’} has no member named ‘pAllRead’
make: *** [Makefile:631:sqlite3.lo] error 1
13:20 Edit reply: Bug report: a bug in VIEW (artifact: 519d7d4812 user: krking)

When I am on bed42116ad, which is later than d794b34da6, SQLite prompts an error message:

./sqlite3 test.db
SQLite version 3.8.6 2014-08-16 19:01:00
Enter ".help" for usage hints.
sqlite> CREATE TABLE t0(a , b INT);
sqlite> INSERT INTO t0 VALUES (NULL, 1);
sqlite> CREATE VIEW v0(c) AS SELECT b FROM t0 EXCEPT SELECT a FROM t0;
Error: near "(": syntax error
sqlite> CREATE VIEW v1 AS SELECT b AS 'c' FROM t0 EXCEPT SELECT a FROM t0;
sqlite> SELECT (c NOT BETWEEN '-1' AND c) AS 'x' FROM v0; -- 1
Error: no such table: v0
sqlite> SELECT (c NOT BETWEEN '-1' AND c) AS 'x' FROM v1; -- 0
0

So I can't figure out exactly which check-in introduced the problem.

After all, thanks for the information.

13:12 Reply: Bug report: a bug in VIEW (artifact: b8d719119e user: krking)

When I am on bed42116ad, which is later than d794b34da6, SQLite prompts an error message:

./sqlite3 test.db
SQLite version 3.8.6 2014-08-16 19:01:00
Enter ".help" for usage hints.
sqlite> CREATE TABLE t0(a , b INT);
sqlite> INSERT INTO t0 VALUES (NULL, 1);
sqlite> CREATE VIEW v0(c) AS SELECT b FROM t0 EXCEPT SELECT a FROM t0;
Error: near "(": syntax error
sqlite> CREATE VIEW v1 AS SELECT b AS 'c' FROM t0 EXCEPT SELECT a FROM t0;
sqlite> SELECT (c NOT BETWEEN '-1' AND c) AS 'x' FROM v0; -- 1
Error: no such table: v0
sqlite> SELECT (c NOT BETWEEN '-1' AND c) AS 'x' FROM v1; -- 0
0

So I can't figure out exactly which check-in introduced the problem.

08:46 Post: Bug report: a bug in VIEW (artifact: fcf2b46ab2 user: krking)

Hi all,

For this example:

CREATE TABLE t0(c0 , c1 INT);
CREATE VIEW v0(c0) AS SELECT c1 FROM t0 EXCEPT SELECT c0 FROM t0;
INSERT INTO t0 VALUES (NULL, 1);

SELECT * FROM v0; -- 1
SELECT c1 FROM t0 EXCEPT SELECT c0 FROM t0; -- 1
SELECT (c0 NOT BETWEEN '-1' AND c0) FROM v0; -- 1
SELECT * FROM v0 WHERE (c0 NOT BETWEEN '-1' AND c0); -- empty

I have tried to find the commit which causes this problem through bisect, but I find that it seems like this problem has been existing for a log time. Since d794b34da6 and bed42116ad allowed us to use column names in a view, the problem already exists.

2021-06-01
16:12 Post: No query solution when an optimization is off (artifact: 9edda56901 user: krking)

Hi everyone,

Consider the following test case:

CREATE TABLE t0 (c0 INT);
CREATE UNIQUE INDEX idx ON t0(c0) WHERE (1 in ());

SELECT count(*) FROM t0 INDEXED BY idx, (SELECT t0.c0 IN() AS c0 FROM t0) AS v0 WHERE (v0.c0); -- 0
.testctrl optimizations 0x00000001;
-- disable SQLITE_QueryFlattener
SELECT count(*) FROM t0 INDEXED BY idx, (SELECT t0.c0 IN() AS c0 FROM t0) AS v0 WHERE (v0.c0); -- Error: no query solution

When I disable the optimization SQLITE_QueryFlattener, the query fails.

07:57 Reply: Error when disable SQLITE_SimplifyJoin (artifact: 374c095081 user: krking)

Yes, I agree with you.

This is indeed a semantically problematic query, which is transformed into a semantically correct query by the optimization of SQLITE_SimplifyJoin.

I'm amazed at the fact that optimizations may affect whether a query can be executed to the end. It's kind of confusing.

2021-05-31
05:52 Post: Error when disable SQLITE_SimplifyJoin (artifact: bc98f6d1c5 user: krking)

Hello everyone,

I noticed that there is an annotation in the source code related to the function sqlite3_test_control, which I quoted here:

sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS, sqlite3 *db, int N)

The idea is that a test program (like the SQL Logic Test or SLT test module) can run the same SQL multiple times with various optimizations disabled to verify that the same answer is obtained in every case.

Then I found a test case, which may result in error when an optimization is disabled:

CREATE TABLE t0(c0, c1);
CREATE TABLE t1(c0, c1);
CREATE TABLE t2(c0, c1);

SELECT * FROM t0 LEFT JOIN t2 ON t0.c0=t1.c1 JOIN t1 ON t1.c0>t2.c0; -- empty
.testctrl optimizations 0x00002000;
-- disable SQLITE_SimplifyJoin
SELECT * FROM t0 LEFT JOIN t2 ON t0.c0=t1.c1 JOIN t1 ON t1.c0>t2.c0; -- Error: ON clause references tables to its right

I guess the reason for the error may be that, when processing an expression like "a JOIN b ON c.x JOIN c ON y", the columns of table "c" is allowed to be used by the previous "a JOIN b", but LEFT JOIN does not allow this. So if this optimization is on, LEFT JOIN is optimized to be JOIN, so it won't report an error message, but it will report an error message if this optimization is not on.

Although I can understand the reason for the error, I still have questions about it. Is there a description of similar cases in SQLite, such as whether syntax is specified, so that it is consistent in terms of whether the query can be done with or without error when optimization is on and off.

Looking forward to your early reply!

2021-05-29
06:56 Delete reply: meaning of "GROUP BY CONSTANT" (artifact: ef55eed3ab user: krking)
Deleted
06:55 Delete reply: meaning of "GROUP BY CONSTANT" (artifact: be644a639c user: krking)
Deleted
2021-05-04
19:50 Reply: Bug report: equivalence transfer results in incorrect output (artifact: ed250fd1bd user: krking)

Thank you for the confirmation!

09:01 Post: Bug report: equivalence transfer results in incorrect output (artifact: eb8613976a user: krking)

Hello,

Consider the following example:

CREATE TABLE t0(c0 INT, c1 INT UNIQUE);
CREATE TABLE t1(c0 INT);
INSERT INTO t0(c0, c1) VALUES (0, 1);
INSERT INTO t1(c0) VALUES (1);

SELECT ALL * FROM t1 NATURAL JOIN t0 WHERE (t1.c0=t0.c1);
SELECT ALL * FROM t1 NATURAL JOIN t0 WHERE (likely(t1.c0=t0.c1));
SELECT ALL * FROM t1,t0 WHERE (likely(t1.c0=t0.c1) AND t1.c0=t0.c0);

The resultset of the SELECT statements should be empty since there are no rows satisfing "t0.c0=t1.c0", but we got non-empty resultset from the second and the third statement.

It looks like a bug caused by some kind of equivalence transfer optimization described here.

I wonder whether it's an undiscovered bug.

Looking forward to your reply!

Thanks,

Wang Ke

08:59 Edit: (Deleted) (artifact: d88e16429f user: krking)
Deleted
08:57 Post: (Deleted) (artifact: 5d18451f0a user: krking)

Hello,

Consider the following example:

CREATE TABLE t0(c0 INT, c1 INT UNIQUE);
CREATE TABLE t1(c0 INT);
INSERT INTO t0(c0, c1) VALUES (0, 1);
INSERT INTO t1(c0) VALUES (1);

SELECT ALL * FROM t1 NATURAL JOIN t0 WHERE (t1.c0=t0.c1);
SELECT ALL * FROM t1 NATURAL JOIN t0 WHERE (likely(t1.c0=t0.c1));
SELECT ALL * FROM t1,t0 WHERE (likely(t1.c0=t0.c1) AND t1.c0=t0.c0);

The resultset of the SELECT statements should be empty since there are no rows satisfing "t0.c0=t1.c0", but we got non-empty resultset from the second and the third statement.

It looks like a bug caused by some kind of equivalence transfer optimization described here.

Thanks,

Wang Ke

2021-04-29
09:45 Reply: bug report: an assert() in zipfile.c that may fail (artifact: 7b371232fe user: krking)

Sorry I didn't see it, thanks for your reconfirming.

09:06 Post: bug report: an assert() in zipfile.c that may fail (artifact: 8061aa4d67 user: krking)

Hello,

This may cause SQLite fail to execute:

-- SQLite version 3.35.5
CREATE TABLE v0 ( v1 INT, v2 VARCHAR(200)) ;
INSERT INTO v0 ( v1) VALUES ( 9223372036854775807) ;
SELECT DISTINCT zipfile ( v1 , v1 , v1, v2 ) FROM v0 ;
sqlite3: zipfileMtimeToDos: Assertion `mUnixTime<315507600 mUnixTime==zipfileMtime(pCds) ((mUnixTime % 2) && mUnixTime-1==zipfileMtime(pCds))' failed.

The code that triggers this error is as follows:

  assert( mUnixTime<315507600 
       || mUnixTime==zipfileMtime(pCds) 
       || ((mUnixTime % 2) && mUnixTime-1==zipfileMtime(pCds)) 
       /* || (mUnixTime % 2) */
  );

Hope this issue will be handled properly :)

2021-04-26
15:53 Reply: bug report : adding constant to GROUP BY leads to different output (artifact: 091f0974ec user: krking)

This bug is indeed fixed, thanks!

15:49 Reply: bug report : adding constant to GROUP BY leads to different output (artifact: 3d4512b4ea user: krking)

Yeah, we are using their fuzzer to produce test cases, and using our new test oracle to check the output, which will be released later.

13:50 Edit reply: bug report : adding constant to GROUP BY leads to different output (artifact: 318de24f34 user: krking)

Sorry, but I didn't feel offended by his response. I am just talking about how I got this case. Sometimes, I think, "silly" cases may be more likely to find bugs because humans don't usually write like this.

I totally agree with you and that's exactly why I chose to report the potential bug and discuss my testing methods with you all in this forum.

Looking forward to Richard's patch to this bug :)

13:37 Reply: bug report : adding constant to GROUP BY leads to different output (artifact: 9c4aabd988 user: krking)

Sorry, but I didn't feel offended by his response. I am just talking about how I got this case. Sometimes, I think, "silly" cases may be more likely to find flaws because humans don't usually write like this.

13:20 Reply: bug report : adding constant to GROUP BY leads to different output (artifact: c4a6a3751d user: krking)

I am testing this DBMS with randomly generated test cases. It looks silly because it's just one of the billions of randomly generated test cases against my test oracle. No matter how silly they look like, they obey the syntax provided by the documentation and may cause incorrect results.

11:57 Reply: bug report : adding constant to GROUP BY leads to different output (artifact: ff96bf576c user: krking)

I see. But the method of how GROUP BY handles NULL does not affect its handling of other numbers, I still think that the two 1.0s should be grouped into the same group, and that adding NULL to the GROUP BY clause should not affect the result in this example.

So could you please tell me whether it's a bug or not?

2021-04-25
13:56 Reply: bug report : adding constant to GROUP BY leads to different output (artifact: f56f35b578 user: krking)

Can the developers explain this?

10:26 Edit: bug report : adding constant to GROUP BY leads to different output (artifact: 74330094d8 user: krking)

Hello everyone,

Consider the following example:

-- SQLite version 3.35.5
CREATE TABLE t0 (c0 REAL);
INSERT INTO t0(c0) VALUES (1), (NULL), (1);
CREATE UNIQUE INDEX idx ON t0(NULL DESC);

SELECT * FROM t0 GROUP BY c0;

Since the two "1" is equal, the expected output should contain two lines: one is null and the other is 1.0. And yes, the output is exactly what I expect.

But when we add a constant to the GROUP BY clause, just like:

SELECT * FROM t0 GROUP BY c0, NULL;

The "GROUP BY constant" means to group all the candidate records into a group by the constant provided. It's like adding a column to the candidate records, and values this constant. So, there should still be 2 lines.

But what we get from the output contains 3 lines. It looks like that SQLite takes the two 1.0 as not equal.

If we annotate the CREATE INDEX statement, the output turns to be two lines again.

I wonder whether it's a bug.

Looking forward to your early reply!

10:25 Edit reply: bug report : adding constant to GROUP BY leads to different output (artifact: 7f9aa36877 user: krking)

Okay, then, why it becomes 2 lines again when we annotate the CREATE INDEX statement?

10:03 Reply: bug report : adding constant to GROUP BY leads to different output (artifact: 64ee882945 user: krking)

Okay, then, why it becomes 2 lines again when we comment the CREATE INDEX statement?

09:35 Post: bug report : adding constant to GROUP BY leads to different output (artifact: 4baa34c2f2 user: krking)

Hello everyone,

Consider the following example:

-- SQLite version 3.35.5
CREATE TABLE t0 (c0 REAL);
INSERT INTO t0(c0) VALUES (1), (NULL), (1);
CREATE UNIQUE INDEX idx ON t0(NULL DESC);

SELECT * FROM t0 GROUP BY c0;

Since the two "1" is equal, the expected output should contain two lines: one is null and the other is 1.0. And yes, the output is exactly what I expect.

But when we add a constant to the GROUP BY clause, just like:

SELECT * FROM t0 GROUP BY c0, NULL;

The "GROUP BY constant" means to group all the candidate records into a group by the constant provided. It's like adding a column to the candidate records, and values this constant. So, there should still be 2 lines.

But what we get from the output contains 3 lines. It looks like that SQLite takes the two 1.0 as not equal.

If we comment the CREATE INDEX statement, the output turns to be two lines again.

I wonder whether it's a bug.

Looking forward to your early reply!

More ↓