SQLite Forum

Timeline
Login

50 most recent forum posts by user LY1598773890

2021-10-04
17:01 Reply: Unexpected output from the SELECT (artifact: 615f3eadba user: LY1598773890)

Thank you for the explanation and the reference to the Wang Ke's post.

So this is a bug related to the equivalence transfer optimization. This query is different from Wang Ke's one in the ways that it compares the two tables in its subquery, where the parent query only has one single table in the FROM clause so it uses the whereShortCut() path. The root problem is the same.

2021-10-02
21:27 Post: Unexpected output from the SELECT (artifact: a65cacbf5e user: LY1598773890)

Hi guys!

We encounter the following query, which gives us an unexpected output.

CREATE TABLE v0 ( c1, c2, c3,  c4, c5 );
INSERT INTO v0 VALUES ( 1, NULL, 0,  100, 200 );
INSERT INTO v0 VALUES ( 0, 0, 165, NULL, 300 );
/* v1 is a direct copy of v0 */
CREATE TABLE v1 ( c6 UNIQUE, c7, c8,  c9, c10 );
INSERT INTO v1 SELECT * FROM v0 AS a28;
 
SELECT a67.c7 > a67.c9  FROM v1 AS a67, v0 as a66 WHERE a66.c3 = a67.c10 AND a66.c3 IS a67.c6;
/* The subquery, returns NULL */
 
SELECT * FROM v0 AS a66 WHERE EXISTS ( SELECT a67.c7 > a67.c9  FROM v1 AS a67 WHERE a66.c3 = a67.c10 AND a66.c3 IS a67.c6 );
/* Returns '1| |0|100|200' */
/* Unexpected. Since the WHERE clause returns FALSE, we expect empty output. */

Interestingly, either removing the constraints UNIQUE from column c6, or removing the condition 'AND a66.c3 IS a67.c6' in the subquery, can fix the unexpected result. (The second SELECT returns NULL as expected then)

Bisecting shows that this unexpected output is introduced by Fossil commit: e038ce8955e785af

Detailed bisecting log:

  1 BAD     2021-10-02 18:22:24 5906a0152deded61
  2 BAD     2021-01-01 01:44:06 e5d7209e118a8453
  3 BAD     2019-12-31 23:17:35 eca7ec9cda4606c4
  4 BAD     2018-12-31 21:43:55 b57c545a384ab5d6
  5 BAD     2017-12-30 18:32:27 01d4e866fb7b01ae
  7 BAD     2016-04-11 14:49:39 60ad80e3af4bae9f
  8 BAD     2015-09-09 19:27:10 89bfdbfe943adce8
 10 BAD     2015-07-03 14:34:25 2b7567229e3ba7e2
 11 BAD     2015-06-12 13:04:51 2e8ad2ead9d146e3
 12 BAD     2015-05-26 00:28:08 c415bb7bbfd531b1
 13 BAD     2015-05-22 12:37:37 a6eb2a39357c35dc
 14 BAD     2015-05-16 18:31:44 ee4b74250ad7a406
 16 BAD     2015-05-15 20:14:00 1a4628c66c632d2a
 17 BAD     2015-05-15 19:59:23 e038ce8955e785af
 18 GOOD    2015-05-15 04:13:15 56ef98a04765c34c CURRENT
 15 GOOD    2015-05-14 15:39:18 be438d049dd9d7aa
  9 GOOD    2015-05-12 12:24:50 07c7d3925cbcf44c
  6 GOOD    2014-12-31 20:35:11 ec264bdee5ab8047

Any insights to this query is welcome. Thank you.

2021-10-01
04:08 Reply: Query group missing from the output (artifact: f4f57278c5 user: LY1598773890)

Thank you for providing the link. The linked thread has the similar question and has answered our doubts.

It is interesting to confirm that Postgres and MySQL also shares this behavior.

03:07 Edit: Query group missing from the output (artifact: f3b138693b user: LY1598773890)

Hi guys!

For query:


CREATE TABLE v0 ( c1, c2 INT ) ;
INSERT INTO v0 ( c1, c2 ) VALUES ( 100, 200 ), ( 127, 400 ) ;

/* First Select */
SELECT  a8.c1 IN ( SELECT a9.c1 FROM v0 AS a9 GROUP BY a9.c1 HAVING SUM ( a8.c2 ) > 100 ) FROM v0 AS a8;
/* Outputs '1' */
/* Expected '1 \n 1 \n ' */

/* Second Select */
SELECT a8.c1 IN (SELECT a9.c1 FROM v0 AS a9 WHERE a8.c2 > 100) FROM v0 AS a8;
/* Outputs '1 \n 1 \n '. Expected */

/* Third Select */
SELECT a8.c1 IN (SELECT a9.c1 FROM v0 AS a9 WHERE a9.c2 > 100) FROM v0 AS a8;
/* Outputs '1 \n 1 \n '. Expected */

Since the subquery from the first SELECT statement has 'GROUP BY a9.c1', we expect two groups being created, and thus the result from the First Select statement should have 2 rows, which should be '1 1 '. However, just one row being returned from SQLite3.

We notice that the alias a8 used in the HAVING clause plays a role in this query. We change the alias from a8 to a9 in the HAVING clause, the query returns '1 1 ' as expected.

Do you have some insight why the First Select only returns one row as results?

03:06 Post: Query group missing from the output (artifact: f4735eac16 user: LY1598773890)

Hi guys!

For query:


CREATE TABLE v0 ( c1, c2 INT ) ;
INSERT INTO v0 ( c1, c2 ) VALUES ( 100, 200 ), ( 127, 400 ) ;

/* First Select */
SELECT  a8.c1 IN ( SELECT a9.c1 FROM v0 AS a9 GROUP BY a9.c1 HAVING SUM ( a8.c2 ) > 100 ) FROM v0 AS a8;
/* Outputs '1' */
/* Expected '1 \n 1 \n ' */

/* Second Select */
SELECT a8.c1 IN (SELECT a9.c1 FROM v0 AS a9 WHERE a8.c2 > 100) FROM v0 AS a8;
/* Outputs '1 \n 1 \n '. Expected */

/* Third Select */
SELECT a8.c1 IN (SELECT a9.c1 FROM v0 AS a9 WHERE a9.c2 > 100) FROM v0 AS a8;
/* Outputs '1 \n 1 \n '. Expected */

Since the subquery from the first SELECT statement has 'GROUP BY a9.c1', we expect two groups being created, and thus the result from the First Select statement should have 2 rows, which should be '1 n 1 n'. However, just one row being returned from SQLite3.

We notice that the alias a8 used in the HAVING clause plays a role in this query. We change the alias from a8 to a9 in the HAVING clause, the query returns '1 n 1 n' as expected.

Do you have some insight why the First Select only returns one row as results?

2021-07-20
18:47 Reply: Unexpected return from the NULL column (artifact: be9f82b555 user: LY1598773890)

Thank you for the reference to the documentation, and thank you for the enhanced check in the ADD COLUMN. After the enhancement, column v3 is not being added to the table, and hence the unexpected outputs are prohibited in the original first query.

2021-07-19
23:26 Edit reply: Different comparison results in WHERE and SELECT clauses (artifact: cbe3e2104a user: LY1598773890)

Thank you Keith for providing further explanation.

So in my understanding, in the original query, if column v1 is NOT a PRIMARY KEY, then the floating point result 9223372036854775000.0 will not be converted back to an integer for table lookup, thus the comparison 9223372036854775807 >= 9223372036854775000.0 is accurate and always evaluates True. The loss of precision is only happening when v1 is PRIMARY KEY and the 9223372036854775000.0 would converted back to INTEGER.

Also, thank you for the fix and the fix comment.

22:17 Reply: Different comparison results in WHERE and SELECT clauses (artifact: c9eaeba734 user: LY1598773890)

Thank you Keith for providing further explanation.

So in my understanding, in the original query, if column v1 is NOT a PRIMARY KEY, then the floating point result 9223372036854775000.0 will not be converted back to an integer for table lookup, thus the comparison 9223372036854775807 >= 9223372036854775000.0 is accurate and always evaluates False. The loss of precision is only happening when v1 is PRIMARY KEY and the 9223372036854775000.0 would converted back to INTEGER.

Also, thank you for the fix and the fix comment.

22:04 Delete reply: Different comparison results in WHERE and SELECT clauses (artifact: e70dc4563d user: LY1598773890)
Deleted
22:00 Reply: Different comparison results in WHERE and SELECT clauses (artifact: e49edaa92a user: LY1598773890)

Thank you for the fix and the fix comment.

So according to the fix comment, in the comparison, column v1 is being interpreted as 64-bits INTEGER, and the numerical literal should be REAL. The patch fix the loss of the precision problem from the original query. This is different from our original understanding, as we originally believe that both v1 and the literal are being transformed into REAL and then compare.

19:21 Reply: Unexpected return from the NULL column (artifact: 57217d7f9d user: LY1598773890)

Thank you for the correction!

In this case, is the output row from the first query expected?

Assuming v3 is being calculated as NULL, we replaced v3 into NULL in the WHERE clause of the first query and observe this:

CREATE TABLE v0 ( v1);
INSERT INTO v0 VALUES ( 255);
ALTER TABLE v0 ADD COLUMN v3 AS ( NULL) NOT NULL;
SELECT "-----";
SELECT * FROM v0 WHERE NULL NOT IN ( SELECT count ( * ) FROM v0);
/* Return NULL */

This is expected but mismatched with the original outputs.

19:05 Post: Different comparison results in WHERE and SELECT clauses (artifact: 2bdb86a068 user: LY1598773890)

For query:

CREATE TABLE v0 ( v1 INTEGER PRIMARY KEY );
INSERT INTO v0 VALUES ( 9223372036854775807 );

SELECT * FROM v0 WHERE v1 >= ( 9223372036854775807 + 1 );
/* Returns 9223372036854775807 */

SELECT v1 >= ( 9223372036854775807 + 1 ) FROM v0;
/* Returns 0 */

We observe different comparison results on v1 >= ( 9223372036854775807 + 1 ), while v1 being the ROWID of the table. We are uncertain about the exact data type SQLite are using for v1 and the numerical literal, in both WHERE and SELECT clauses, so we are uncertain about the reason of the different comparison results.

The different comparison results are also related to the PRIMARY KEY. As we remove v1 as PRIMARY KEY, the comparison results are consistent:

CREATE TABLE v0 ( v1 INTEGER );
INSERT INTO v0 VALUES ( 9223372036854775807 );

SELECT * FROM v0 WHERE v1 >= ( 9223372036854775807 + 1 );
/* Returns NULL */

SELECT v1 >= ( 9223372036854775807 + 1 ) FROM v0;
/* Returns 0 */

Looking forward to replies. And thank you for any explanation that help us to better understand this query.

18:36 Post: Unexpected return from the NULL column (artifact: c04814903d user: LY1598773890)

For query:

CREATE TABLE v0 ( v1);
INSERT INTO v0 VALUES ( 255);
ALTER TABLE v0 ADD COLUMN v3 AS ( NULL) NOT NULL;
SELECT "-----";
SELECT * FROM v0 WHERE v3 NOT IN ( SELECT count ( * ) FROM v0);
/* Expected return: NULL. Actual return  255| */

In the ALTER TABLE statement, we add column v3 with default value NULL and constraints NOT NULL. We understand that this is not a conventional behavior, because document ALTER TABLE has noted that If a NOT NULL constraint is specified, then the column must have a default value other than NULL. However, the ALTER TABLE statement doesn't throw any errors, and the NULL column triggers unexpected output with unmatched condition in the following SELECT statement.

We do notice that SQLite apply NOT NULL constraints check when we INSERT data into the column, such as the following case:

CREATE TABLE v0 (v1, v2 AS (NULL) NOT NULL);
INSERT INTO v0 VALUES (255);
/* Error: NOT NULL constraint failed: v0.v2 */

However, if possible, we would also suggest to add a NOT NULL constraint check in the ALTER TABLE statement or in the SELECT statement, in order to prevent the unexpected behavior from the first query.

Looking forward to your reply.

2021-07-07
16:53 Reply: Assertion Failure "target>0 && target<=pParse->nMem" (artifact: 3b6e027451 user: LY1598773890)

Thanks for the prompt fix!

16:12 Post: Assertion Failure "target>0 && target<=pParse->nMem" (artifact: 79c9e4797d user: LY1598773890)

Query

CREATE TABLE v0 ( v2, v3, v4 AS (v4 <= 0), v1, FOREIGN KEY ( v4, v1, v3, v2, v3, v1, v3, v4 ) REFERENCES t0);
PRAGMA foreign_key_check;

Run on debug version

sqlite3.c:106136: sqlite3ExprCode: Assertion `target>0 && target<=pParse->nMem' failed.

Run on non-debug version

Error: near line 2: generated column loop on "v4"

Bisect

  1 BAD     2021-07-07 13:53:55 6b22f4e71dbc14c8
  2 BAD     2020-01-15 16:20:16 03b003c988d27f3a
  4 BAD     2019-12-14 19:55:31 a89b38605661e36d
  5 BAD     2019-11-16 23:47:40 51525f9c3235967b
  6 BAD     2019-10-30 18:50:08 2978b65ebe25eeab
  9 BAD     2019-10-29 01:26:24 591973217f1caf24
 10 BAD     2019-10-28 04:20:28 a1e1ba9145049491
 11 BAD     2019-10-27 22:22:24 6d1bbba9a004a249
 12 BAD     2019-10-26 23:51:44 5b4c0f2ddc6f324e CURRENT
  8 GOOD    2019-10-26 18:56:12 713fe86b8c9f3c9e
  7 GOOD    2019-10-23 21:00:40 1a6e009372cf9571
  3 GOOD    2019-10-11 14:21:48 bf875dc59909f9c2
2021-07-06
02:41 Reply: Bug: Sqlite Crash (artifact: 7578e0237c user: LY1598773890)

Thanks for your confirmation and the fix, Richard.

Also thanks for your detailed explanation on the triggering condition. This bug is indeed hard to trigger, and we got this query from our fuzzer which randomly mutates statements and clauses while trying to retain the query validity.

2021-07-04
19:02 Post: Bug: Sqlite Crash (artifact: 16ca0e9f32 user: LY1598773890)

Query

CREATE VIRTUAL TABLE v0 USING fts4 ( v1 );
WITH v0 ( v1 ) AS ( SELECT * FROM main.v0 AS a9 WHERE  a9.v1 IN ( 8, 16 ) UNION ALL SELECT * FROM main.v0) UPDATE v0 SET ( v1, v1 ) = ( SELECT 18446744073709551488, 0 ) FROM v0, v0;

Run on debug version

sqlite3: sqlite3.c:102866: sqlite3ExprListDup: Assertion `pNewExpr->iColumn==pItem[-1].pExpr->iColumn+1' failed.

Run on non-debug version

segmentation fault 

with ASAN:

AddressSanitizer:DEADLYSIGNAL
=================================================================
==24460==ERROR: AddressSanitizer: SEGV on unknown address 0x00000000002c (pc 0x00000078d451 bp 0x7ffc0cd908b0 sp 0x7ffc0cd90400 T0)
==24460==The signal is caused by a READ memory access.
==24460==Hint: address points to the zero page.
    #0 0x78d450 in sqlite3ExprCodeTarget /home/sqlite/sqlite-asan-build/sqlite3.c:105718:25
    #1 0x79e60c in sqlite3ExprCodeExprList /home/sqlite/sqlite-asan-build/sqlite3.c:106171:19
    #2 0x7fa432 in innerLoopLoadRow /home/sqlite/sqlite-asan-build/sqlite3.c:132750:3
    #3 0x7fa432 in selectInnerLoop /home/sqlite/sqlite-asan-build/sqlite3.c:133328
    #4 0x712502 in sqlite3Select /home/sqlite/sqlite-asan-build/sqlite3.c:138999:7
    #5 0x70ad21 in multiSelect /home/sqlite/sqlite-asan-build/sqlite3.c:134949:14
    #6 0x70ad21 in sqlite3Select /home/sqlite/sqlite-asan-build/sqlite3.c:138618
    #7 0x70ad21 in multiSelect /home/sqlite/sqlite-asan-build/sqlite3.c:134949:14
    #8 0x70ad21 in sqlite3Select /home/sqlite/sqlite-asan-build/sqlite3.c:138618
    #9 0x895fdc in updateFromSelect /home/sqlite/sqlite-asan-build/sqlite3.c:141486:3
    #10 0x7433dd in sqlite3Update /home/sqlite/sqlite-asan-build/sqlite3.c:142453:5
    #11 0x6c3d2f in yy_reduce /home/sqlite/sqlite-asan-build/sqlite3.c:162281:3
    #12 0x5a0a41 in sqlite3Parser /home/sqlite/sqlite-asan-build/sqlite3.c:163325:15
    #13 0x5a0a41 in sqlite3RunParser /home/sqlite/sqlite-asan-build/sqlite3.c:164621
    #14 0x699616 in sqlite3Prepare /home/sqlite/sqlite-asan-build/sqlite3.c:131903:5
    #15 0x59d18e in sqlite3LockAndPrepare /home/sqlite/sqlite-asan-build/sqlite3.c:131978:10
    #16 0x570b77 in sqlite3_prepare_v2 /home/sqlite/sqlite-asan-build/sqlite3.c:132063:8
    #17 0x51d972 in shell_exec /home/sqlite/sqlite-asan-build/shell.c:14379:10
    #18 0x55c009 in runOneSqlLine /home/sqlite/sqlite-asan-build/shell.c:21449:8
    #19 0x52235a in process_input /home/sqlite/sqlite-asan-build/shell.c:21549:17
    #20 0x4feee5 in main /home/sqlite/sqlite-asan-build/shell.c
    #21 0x7f10c4c9683f in __libc_start_main /build/glibc-S7Ft5T/glibc-2.23/csu/../csu/libc-start.c:291
    #22 0x41b278 in _start (/home/sqlite/sqlite-asan-build/sqlite3+0x41b278)

AddressSanitizer can not provide additional info.
SUMMARY: AddressSanitizer: SEGV /home/sqlite/sqlite-asan-build/sqlite3.c:105718:25 in sqlite3ExprCodeTarget
==24460==ABORTING

Bisect

  2 BAD     2021-07-03 18:57:40 d2b9b8daa3b87c3d
  5 BAD     2020-07-30 17:29:39 166e82dd20efbfd3
 11 BAD     2020-07-20 23:33:11 6d258c3c7ecafa11
 12 BAD     2020-07-18 15:52:15 88baf1eb07065032
 14 GOOD    2020-07-16 18:55:58 c1ea064948ba08c4 CURRENT
 13 GOOD    2020-07-15 02:15:03 73d62f82f94347c6
 10 GOOD    2020-07-14 12:40:53 f25a56c26e28abd4
  9 GOOD    2020-06-30 15:32:12 4d0cfb1236884349
  8 GOOD    2020-06-11 15:53:54 32a88bdd4be5acdc
  7 GOOD    2020-05-07 01:56:57 99749d4fd4930ccf
  1 GOOD    2020-02-04 20:22:32 76668b55894a9c75
  6 GOOD    2020-01-15 16:20:16 03b003c988d27f3a
  4 GOOD    2019-10-11 14:21:48 bf875dc59909f9c2
  3 GOOD    2018-04-19 13:52:39 b6d5ea59fe83716f

An issue with fossil bisect

When performing bisect, I first set the version at "2020-02-04 20:22:32" as good, and then set version at "2021-07-03 18:57:40" as bad. I expect fossil will find a commit in the middle. However, fossil goes back to a version at "2018-04-19 13:52:39" (the 3rd step). Is this expected? I guess it could be related to commit merge, but not sure.

2021-07-03
15:30 Reply: Bug: Heap Buffer Overflow (artifact: 6aa23f9dfc user: LY1598773890)

Thank you for the very good advice. :-)

We do use the -DSQLITE_DEBUG version for fuzzing. Actually, we observe the assertion failure first in our fuzzing tool, and then use ASAN to generate the heap buffer report.

We are very happy to provide more information if needed, and produce more reports in the future to make the software better!

01:58 Post: Bug: Heap Buffer Overflow (artifact: a6b0c05277 user: LY1598773890)

Query

CREATE TABLE v0 ( v1, v2, v3, FOREIGN KEY ( v1, v1, v1, v1, v1, v1, v1, v1, v1, v1, v1, v1, v1 ) REFERENCES t0 );
INSERT INTO v0 VALUES ( 18446744073709551615, 'x', 'four' );
PRAGMA foreign_key_check;

Bug

The query above triggers a heap based buffer overflow when testing with the latest commit of sqlite (55e2fbebb0a2c999). The ASAN output is as follows:

=================================================================
==26238==ERROR: AddressSanitizer: heap-buffer-overflow on address 0x61d0000013c8 at pc 0x00000064ee66 bp 0x7ffc6245fd70 sp 0x7ffc6245fd68
READ of size 2 at 0x61d0000013c8 thread T0
    #0 0x64ee65 in sqlite3VdbeExec /home/sqlite/sqlite-asan-build/sqlite3.c:89413:7
    #1 0x5712c9 in sqlite3Step /home/sqlite/sqlite-asan-build/sqlite3.c:84974:10
    #2 0x5712c9 in sqlite3_step /home/sqlite/sqlite-asan-build/sqlite3.c:85031
    #3 0x557643 in exec_prepared_stmt /home/sqlite/sqlite-asan-build/shell.c:14164:8
    #4 0x51e40e in shell_exec /home/sqlite/sqlite-asan-build/shell.c:14473:7
    #5 0x55c009 in runOneSqlLine /home/sqlite/sqlite-asan-build/shell.c:21449:8
    #6 0x52235a in process_input /home/sqlite/sqlite-asan-build/shell.c:21549:17
    #7 0x4feee5 in main /home/sqlite/sqlite-asan-build/shell.c
    #8 0x7f1e6bcb083f in __libc_start_main /build/glibc-S7Ft5T/glibc-2.23/csu/../csu/libc-start.c:291
    #9 0x41b278 in _start (/home/sqlite/sqlite-asan-build/sqlite3+0x41b278)

0x61d0000013c8 is located 8 bytes to the right of 2368-byte region [0x61d000000a80,0x61d0000013c0)
allocated by thread T0 here:
    #0 0x4bb9a3 in malloc /home/sqlite/llvm_source/llvm/projects/compiler-rt/lib/asan/asan_malloc_linux.cc:88:3
    #1 0x8e429c in sqlite3MemMalloc /home/sqlite/sqlite-asan-build/sqlite3.c:24178:7

SUMMARY: AddressSanitizer: heap-buffer-overflow /home/sqlite/sqlite-asan-build/sqlite3.c:89413:7 in sqlite3VdbeExec
Shadow bytes around the buggy address:
  0x0c3a7fff8220: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
  0x0c3a7fff8230: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
  0x0c3a7fff8240: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
  0x0c3a7fff8250: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
  0x0c3a7fff8260: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
=>0x0c3a7fff8270: 00 00 00 00 00 00 00 00 fa[fa]fa fa fa fa fa fa
  0x0c3a7fff8280: fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa
  0x0c3a7fff8290: fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa
  0x0c3a7fff82a0: fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa
  0x0c3a7fff82b0: fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa
  0x0c3a7fff82c0: fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa
Shadow byte legend (one shadow byte represents 8 application bytes):
  Addressable:           00
  Partially addressable: 01 02 03 04 05 06 07
  Heap left redzone:       fa
  Freed heap region:       fd
  Stack left redzone:      f1
  Stack mid redzone:       f2
  Stack right redzone:     f3
  Stack after return:      f5
  Stack use after scope:   f8
  Global redzone:          f9
  Global init order:       f6
  Poisoned by user:        f7
  Container overflow:      fc
  Array cookie:            ac
  Intra object redzone:    bb
  ASan internal:           fe
  Left alloca redzone:     ca
  Right alloca redzone:    cb
==26238==ABORTING

Running the debug version

leads to the following assertion failure:

sqlite3: sqlite3.c:89245: sqlite3VdbeExec: Assertion `pOp->p3>0 && pOp->p3<=(p->nMem+1 - p->nCursor)' failed.

Bisecting

It seems this bug has been in the source code for a while. Here is an incomplete bisect result:

  6 BAD     2021-07-02 12:25:30 55e2fbebb0a2c999 CURRENT
  2 BAD     2020-11-25 20:29:45 f4b7c10057a50c5b
  3 BAD     2020-02-04 20:22:32 76668b55894a9c75
  1 BAD     2018-07-27 20:01:00 865249de683e6971
  5 BAD     2016-02-05 21:09:26 22589018ac3321f7
2021-05-29
23:39 Reply: Unexpected results with IS NOT NULL (artifact: 3ed31aaa0d user: LY1598773890)

Seems like the problem has been fixed in: 8cc23931d61b7d78. Thank you for the updates and the patch information provided.

19:09 Post: Unexpected results with IS NOT NULL (artifact: 49dfab594d user: LY1598773890)

Hi all.

For query:

CREATE TABLE v0 ( v2 INT, v1 INT);
INSERT INTO v0 VALUES ( 10, 10 );
CREATE UNIQUE INDEX v4 ON v0 ( v1 ) WHERE v2 > NULL;

SELECT * FROM v0;
/* 10 | 10 */
SELECT * FROM v0 WHERE v0.v2 IS NOT NULL;
/* EMPTY */

The second SELECT statement failed to output the row. Fossil bisecting results show the bug introduced commit is: fc98218cf69e63bdb9e5f154521a341508502cd8cfe04cb870cabee2d99e0cb3.

Looking forward to your reply.

18:25 Edit reply: Inconsistant output when likely/unlikely and join keyword is applied (artifact: 456f3ceaaf user: LY1598773890)

It appears that another query form also triggers this bug.

CREATE TABLE v0 ( v1, v2 PRIMARY KEY);
CREATE INDEX v3 ON v0 ( v2, v2 );
INSERT INTO v0 ( v1, v2 ) VALUES ( 10, 'x' );

SELECT COUNT ( * ) FROM v0 AS a13, v0 AS a14, v0 AS a15, v0 AS a16, v0 AS a17 WHERE a13.v1 = a13.v2 AND a13.v1 = 'x';
/* 1 */
SELECT COUNT ( * ) FROM v0 WHERE v0.v1 = v0.v2 AND v0.v1 = 'x';
/* 0 */

Since v0.v1 is not equals to v0.v2, both SELECT statements are expected to output 0 or empty results. Fix https://sqlite.org/src/info/2363a14ca723c034 also fix the inconsistent outputs from this query.

18:24 Reply: Inconsistant output when likely/unlikely and join keyword is applied (artifact: e33d26a5c9 user: LY1598773890)

It appears that another query form has also triggers this bug.

CREATE TABLE v0 ( v1, v2 PRIMARY KEY);
CREATE INDEX v3 ON v0 ( v2, v2 );
INSERT INTO v0 ( v1, v2 ) VALUES ( 10, 'x' );

SELECT COUNT ( * ) FROM v0 AS a13, v0 AS a14, v0 AS a15, v0 AS a16, v0 AS a17 WHERE a13.v1 = a13.v2 AND a13.v1 = 'x';
/* 1 */
SELECT COUNT ( * ) FROM v0 WHERE v0.v1 = v0.v2 AND v0.v1 = 'x';
/* 0 */

Since v0.v1 is not equals to v0.v2, both SELECT statements are expected to output 0 or empty results. Fix https://sqlite.org/src/info/2363a14ca723c034 also fix the inconsistent outputs from this query.

2021-05-28
15:38 Reply: Assertion Failure on query (artifact: e11965ebd8 user: LY1598773890)

Thank you for the fix information.

01:02 Post: Assertion Failure on query (artifact: 28216b36ac user: LY1598773890)

Hi all

For query:

CREATE TABLE v0 ( v1 TEXT );
CREATE INDEX v4 ON v0 ( v1, v1 );
SELECT * FROM v0 WHERE ( v1 IS ( SELECT v1 ) AND v1 = 'AAA' );

Assertion failed with enable-debug compile flags applied. SQLite3 provides the following outputs:

sqlite3: sqlite3.c:87288: sqlite3VdbeExec: Assertion `memIsValid(&aMem[pOp->p3])' failed.

Here is the bisecting result:

bisect complete
  2 BAD     2021-05-27 16:31:04 f958ffbc61c693b7
  3 BAD     2021-05-26 23:10:19 9be208a6d70582c6
  4 BAD     2021-05-26 18:46:51 f30fb19ff763a7cb
  5 GOOD    2021-05-26 14:32:33 3e2c36a8272ab3c1 CURRENT
  1 GOOD    2021-05-25 16:10:12 708ce7ad8acee702

Looking forward to your reply.

2021-05-27
18:02 Reply: Inconsistent output possibly due to affinity issue (artifact: 559dc7159e user: LY1598773890)

Thank you for the patch, and the detail explanation.

2021-05-25
22:07 Reply: Inconsistent output possibly due to affinity issue (artifact: 3b0dc1e1f4 user: LY1598773890)

Thanks for the explanation, Keith. Now I understand the cause of this problem. Whether to propagate constant in the SELECT expression really depends on the design of SQLite. However, I guess the following query, which is slightly modified from the original one, may be useful for us to think about this problem again.

CREATE TABLE v0 ( v2 REAL, v1 );
INSERT INTO v0 VALUES ( 10, 11 );
UPDATE v0 SET v1 = v2;

SELECT * FROM v0 WHERE v1 LIKE 10 AND v1 = 10;
/* 10 | 10 */
SELECT * FROM v0 WHERE v1 LIKE 10;
/* (EMPTY) */

In this updated query, the condition is only being placed in the WHERE clause, so we don't need to worry about the different propagation rules between different clause. However, the inconsistent behavior still persist with just the addition of AND statement. Here, the propagation rule is overwriting the result of a 'FALSE AND TRUE' condition to TRUE, which is unexpected.

Looking forward to your reply.

19:26 Post: Inconsistent output possibly due to affinity issue (artifact: 6a06202608 user: LY1598773890)

Hi all.

For query:

CREATE TABLE v0 ( v2 REAL, v1 );
INSERT INTO v0 VALUES ( 10, 11 );
UPDATE v0 SET v1 = v2;

SELECT * FROM v0 WHERE v1 = 10 and v1 LIKE 10;
/* 10.0 | 10.0 */
SELECT v1 = 10 and v1 LIKE 10 FROM v0;
/* 0 */

The two SELECT statements return inconsistent results. We do not know whether this is a potential bug or an expected behavior due to affinity issue.

The behavior is reproducible with Fossil: 708ce7ad8acee702d08d1987aa253b0bfc3fd97255d6e4153122b03eba337570. But it does not appear on stable release SQLite 3.31.1.

Looking forward to your reply.

2021-05-23
01:00 Reply: Query assertion not satisfied (artifact: bfa03cfaf0 user: LY1598773890)

It seems an update has been applied to fix this issue, as shown in: https://sqlite.org/src/info/b986600520696b0c

Thank you for the patch and the information provided in the update.

2021-05-22
03:42 Edit: Query assertion not satisfied (artifact: 7e484e225c user: LY1598773890)

Hi all

For query:

CREATE TABLE v0 ( v1 CHAR(30), v2 CHAR(30) );
CREATE INDEX v19 ON v0 ( v2, v1 );
SELECT * FROM v0 WHERE ( v2 = ( SELECT v2 FROM v0 ORDER BY lead ( v2 ) OVER ( ) = '0' AND SUM ( v1 * ( 9223372036854775807 - v2 ) ) > v2 AND EXISTS ( SELECT * FROM v0 x WHERE v1 = v2 AND v1 != v1 ) AND NOT EXISTS ( SELECT * FROM v0 x WHERE v2 = v2 AND v2 != v2 AND v1 > v2 ) AND v2 = v1 AND v1 = 'av1 c' ) );

When testing the above query with DEBUG flag on the latest development branch, Fossil: d42dbd9dfeb9233dcf9bb7af617289bc51a12379, assertion failed and program exit. The error information is shown below:

sqlite3: sqlite3.c:101215: sqlite3TableColumnAffinity: Assertion `iCol<pTab->nCol' failed.
Aborted (core dumped)

Looking forward to your reply.

03:41 Post: Query assertion not satisfied (artifact: 43895fcf10 user: LY1598773890)

Hi all

For query:

CREATE TABLE v0 ( v1 CHAR(30), v2 CHAR(30) );
CREATE INDEX v19 ON v0 ( v2, v1 );
SELECT * FROM v0 WHERE ( v2 = ( SELECT v2 FROM v0 ORDER BY lead ( v2 ) OVER ( ) = '0' AND SUM ( v1 * ( 9223372036854775807 - v2 ) ) > v2 AND EXISTS ( SELECT * FROM v0 x WHERE v1 = v2 AND v1 != v1 ) AND NOT EXISTS ( SELECT * FROM v0 x WHERE v2 = v2 AND v2 != v2 AND v1 > v2 ) AND v2 = v1 AND v1 = 'av1 c' ) );

When testing the above query with DEBUG flag on the latest development branch, Fossil: d42dbd9dfeb9233dcf9bb7af617289bc51a12379, assertion failed and program exit. The error information is shown below:

sqlite3: sqlite3.c:101215: sqlite3TableColumnAffinity: Assertion `iCol<pTab->nCol' failed.
Aborted (core dumped)

Looking forward to your reply.

2021-05-21
19:18 Post: Query triggers Segmentation Fault (artifact: aa4a7a3980 user: LY1598773890)

Hi all

For query:

CREATE TABLE v0 ( c0 );
CREATE VIEW v12 ( c1 ) AS WITH x AS ( WITH y AS ( WITH z AS ( SELECT * FROM v0 ) SELECT * FROM v12 ) SELECT * ) SELECT * from v0 ;
ALTER TABLE v0 RENAME COLUMN c0 TO c2;

The query above triggers Segmentation Fault with the latest development build. Tested with Fossil: c18dbe2f389f4ba7b219b7995d4f7009d1bc249ef8f93a30b262c6d2c008319d. AddressSanitizer outputs the following information:

=================================================================
==97474==ERROR: AddressSanitizer: heap-use-after-free on address 0x6060000020c0 at pc 0x0000007a77ba bp 0x7fff1dbd5e70 sp 0x7fff1dbd5e68
READ of size 4 at 0x6060000020c0 thread T0
    #0 0x7a77b9 in searchWith /home/hong/sqlite-asan-build/sqlite3.c:136978:19
    #1 0x7a77b9 in resolveFromTermToCte /home/hong/sqlite-asan-build/sqlite3.c:137052:10
    #2 0x7a77b9 in selectExpander /home/hong/sqlite-asan-build/sqlite3.c:137322:21
    #3 0x77dee5 in sqlite3WalkSelect /home/hong/sqlite-asan-build/sqlite3.c:99114:10
    #4 0x79e3e1 in sqlite3SelectExpand /home/hong/sqlite-asan-build/sqlite3.c:137598:3
    #5 0x79e3e1 in sqlite3SelectPrep /home/hong/sqlite-asan-build/sqlite3.c:137683:3
    #6 0x7ad353 in renameWalkWith /home/hong/sqlite-asan-build/sqlite3.c:108158:7
    #7 0x8a32c5 in renameColumnSelectCb /home/hong/sqlite-asan-build/sqlite3.c:108296:3
    #8 0x77dee5 in sqlite3WalkSelect /home/hong/sqlite-asan-build/sqlite3.c:99114:10
    #9 0x7ad35e in renameWalkWith /home/hong/sqlite-asan-build/sqlite3.c:108159:7
    #10 0x8a32c5 in renameColumnSelectCb /home/hong/sqlite-asan-build/sqlite3.c:108296:3
    #11 0x77dee5 in sqlite3WalkSelect /home/hong/sqlite-asan-build/sqlite3.c:99114:10
    #12 0x7ad35e in renameWalkWith /home/hong/sqlite-asan-build/sqlite3.c:108159:7
    #13 0x8a32c5 in renameColumnSelectCb /home/hong/sqlite-asan-build/sqlite3.c:108296:3
    #14 0x77dee5 in sqlite3WalkSelect /home/hong/sqlite-asan-build/sqlite3.c:99114:10
    #15 0x8a20bd in renameQuotefixFunc /home/hong/sqlite-asan-build/sqlite3.c:109175:13
    #16 0x5e686c in sqlite3VdbeExec /home/hong/sqlite-asan-build/sqlite3.c:94421:3
    #17 0x53f0ea in sqlite3Step /home/hong/sqlite-asan-build/sqlite3.c:84813:10
    #18 0x53f0ea in sqlite3_step /home/hong/sqlite-asan-build/sqlite3.c:84870:16
    #19 0x5261fa in exec_prepared_stmt /home/hong/sqlite-asan-build/shell.c:13387:8
    #20 0x4f5f47 in shell_exec /home/hong/sqlite-asan-build/shell.c:13696:7
    #21 0x52a90f in runOneSqlLine /home/hong/sqlite-asan-build/shell.c:20626:8
    #22 0x4f9e8b in process_input /home/hong/sqlite-asan-build/shell.c:20726:17
    #23 0x4d6e10 in main /home/hong/sqlite-asan-build/shell.c
    #24 0x7f30d23030b2 in __libc_start_main /build/glibc-eX1tMB/glibc-2.31/csu/../csu/libc-start.c:308:16
    #25 0x41c63d in _start (/home/hong/sqlite-asan-build/sqlite3+0x41c63d)

0x6060000020c0 is located 0 bytes inside of 64-byte region [0x6060000020c0,0x606000002100)
freed by thread T0 here:
    #0 0x494afd in free (/home/hong/sqlite-asan-build/sqlite3+0x494afd)
    #1 0x52dec5 in sqlite3_free /home/hong/sqlite-asan-build/sqlite3.c:28141:5
    #2 0x52dec5 in sqlite3DbFreeNN /home/hong/sqlite-asan-build/sqlite3.c:28196:3
    #3 0x52dec5 in sqlite3DbFree /home/hong/sqlite-asan-build/sqlite3.c:28200:11

previously allocated by thread T0 here:
    #0 0x494d7d in malloc (/home/hong/sqlite-asan-build/sqlite3+0x494d7d)
    #1 0x8b3ebc in sqlite3MemMalloc /home/hong/sqlite-asan-build/sqlite3.c:24120:7

SUMMARY: AddressSanitizer: heap-use-after-free /home/hong/sqlite-asan-build/sqlite3.c:136978:19 in searchWith
Shadow bytes around the buggy address:
  0x0c0c7fff83c0: fa fa fa fa fd fd fd fd fd fd fd fd fa fa fa fa
  0x0c0c7fff83d0: fd fd fd fd fd fd fd fa fa fa fa fa fd fd fd fd
  0x0c0c7fff83e0: fd fd fd fa fa fa fa fa 00 00 00 00 00 00 00 00
  0x0c0c7fff83f0: fa fa fa fa 00 00 00 00 00 00 00 00 fa fa fa fa
  0x0c0c7fff8400: 00 00 00 00 00 00 00 00 fa fa fa fa fd fd fd fd
=>0x0c0c7fff8410: fd fd fd fa fa fa fa fa[fd]fd fd fd fd fd fd fd
  0x0c0c7fff8420: fa fa fa fa fd fd fd fd fd fd fd fd fa fa fa fa
  0x0c0c7fff8430: fd fd fd fd fd fd fd fd fa fa fa fa fa fa fa fa
  0x0c0c7fff8440: fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa
  0x0c0c7fff8450: fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa
  0x0c0c7fff8460: fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa
Shadow byte legend (one shadow byte represents 8 application bytes):
  Addressable:           00
  Partially addressable: 01 02 03 04 05 06 07
  Heap left redzone:       fa
  Freed heap region:       fd
  Stack left redzone:      f1
  Stack mid redzone:       f2
  Stack right redzone:     f3
  Stack after return:      f5
  Stack use after scope:   f8
  Global redzone:          f9
  Global init order:       f6
  Poisoned by user:        f7
  Container overflow:      fc
  Array cookie:            ac
  Intra object redzone:    bb
  ASan internal:           fe
  Left alloca redzone:     ca
  Right alloca redzone:    cb
  Shadow gap:              cc
==97474==ABORTING

Looking forward to your reply.

2021-05-20
19:09 Post: Query assertion not satisfied with DEBUG build (artifact: 339f487de5 user: LY1598773890)

Hi all

For query:

CREATE TABLE v0 ( v4 );
CREATE VIEW v8 AS SELECT v4 FROM v0 UNION ALL SELECT v4 FROM v0;
CREATE TABLE v25 ( v30, v32 );
UPDATE v25 SET ( v32, v30 ) = ( SELECT 10, 10 ) FROM v0, v8;

Assertion failed when tested with --enable-debug flag and the latest development build: Fossil: 67bde01614edd944b769ab28488bac1dbbf17d88beee62ca7f1e45681aa3d402. The execution outputs the following error message:

sqlite3: sqlite3.c:102701: sqlite3ExprListDup: Assertion `pOldExpr->pLeft==pOldExpr->pRight' failed.
Aborted (core dumped)

Looking forward to your reply.

00:35 Reply: Query triggers Segmentation Fault (artifact: c9159b57b6 user: LY1598773890)

Thank you for patch and the explanation.

2021-05-19
19:13 Post: Query triggers Segmentation Fault (artifact: a274248080 user: LY1598773890)

Hi all.

For query:

CREATE TABLE v0 ( v2 INTEGER );
CREATE TABLE v3 ( v5 INTEGER );
WITH v0 AS ( SELECT * FROM v3 ) UPDATE v0 SET v2 = 'x' FROM v3;

Segmentation Fault triggered. Address Sanitizer outputs the following information:

AddressSanitizer:DEADLYSIGNAL
=================================================================
==3922846==ERROR: AddressSanitizer: SEGV on unknown address 0x000000000000 (pc 0x0000007d7f8c bp 0x7fff139364d0 sp 0x7fff13936380 T0)
==3922846==The signal is caused by a READ memory access.
==3922846==Hint: address points to the zero page.
    #0 0x7d7f8c in sqlite3ExprVectorSize /home/hong/sqlite-asan-build/sqlite3.c:101589:18
    #1 0x7d7f8c in sqlite3ExprIsVector /home/hong/sqlite-asan-build/sqlite3.c:101579:10
    #2 0x7d7f8c in substExpr /home/hong/sqlite-asan-build/sqlite3.c:135568:11
    #3 0x7d7310 in substExprList /home/hong/sqlite-asan-build/sqlite3.c:135635:25
    #4 0x7d7310 in substSelect /home/hong/sqlite-asan-build/sqlite3.c:135648:5
    #5 0x6d726d in flattenSubquery /home/hong/sqlite-asan-build/sqlite3.c:136313:7
    #6 0x6d726d in sqlite3Select /home/hong/sqlite-asan-build/sqlite3.c:138246:9
    #7 0x862613 in updateFromSelect /home/hong/sqlite-asan-build/sqlite3.c:141142:3
    #8 0x7136e0 in sqlite3Update /home/hong/sqlite-asan-build/sqlite3.c:141555:9
    #9 0x6902b8 in yy_reduce /home/hong/sqlite-asan-build/sqlite3.c:161919:3
    #10 0x56e010 in sqlite3Parser /home/hong/sqlite-asan-build/sqlite3.c:162963:15
    #11 0x56e010 in sqlite3RunParser /home/hong/sqlite-asan-build/sqlite3.c:164259:5
    #12 0x663e3d in sqlite3Prepare /home/hong/sqlite-asan-build/sqlite3.c:131652:5
    #13 0x56a6af in sqlite3LockAndPrepare /home/hong/sqlite-asan-build/sqlite3.c:131727:10
    #14 0x53e804 in sqlite3_prepare_v2 /home/hong/sqlite-asan-build/sqlite3.c:131812:8
    #15 0x4f5482 in shell_exec /home/hong/sqlite-asan-build/shell.c:13602:10
    #16 0x52a90f in runOneSqlLine /home/hong/sqlite-asan-build/shell.c:20626:8
    #17 0x4f9e8b in process_input /home/hong/sqlite-asan-build/shell.c:20726:17
    #18 0x4d6e10 in main /home/hong/sqlite-asan-build/shell.c
    #19 0x7fc161e9e0b2 in __libc_start_main /build/glibc-eX1tMB/glibc-2.31/csu/../csu/libc-start.c:308:16
    #20 0x41c63d in _start (/home/hong/sqlite-asan-build/sqlite3+0x41c63d)

AddressSanitizer can not provide additional info.
SUMMARY: AddressSanitizer: SEGV /home/hong/sqlite-asan-build/sqlite3.c:101589:18 in sqlite3ExprVectorSize
==3922846==ABORTING

This behavior can be reproduced by one of the latest development version, fossil: b480aacb3430a789d98ffd81a1886bbbc3cda5b0e736ec9a3e2b463db2a3b3ad, but seems not affecting the stable release: tested with SQLite version 3.32.3 with correct run through.

Look forward to your reply.

2021-05-13
14:16 Reply: Unexpected output from the SELECT statement (artifact: 2cc2bc1821 user: LY1598773890)

Thank you for the explanation and the fix.

2021-05-12
23:06 Post: Unexpected output from the SELECT statement (artifact: 6c8960f545 user: LY1598773890)

Hi all:

For query:


CREATE TABLE v0 ( v1 INTEGER PRIMARY KEY ) WITHOUT ROWID;
INSERT INTO v0 VALUES (10) ;
ALTER TABLE v0 ADD v2 INT;

SELECT * FROM v0 WHERE ( v1 = 20 ) OR ( v1 = 10 AND v2 = 10 );
/* outputs '10| '*/


Since column v2 is empty, it is expected that the SELECT statement would output empty result. Unfortunately, the actual output returns the row.

Looking forward to your reply.

22:55 Reply: Inconsistent results in the WHERE clause (artifact: 2aa3f5495c user: LY1598773890)

Thank you for the information.

18:40 Post: Inconsistent results in the WHERE clause (artifact: 8988341615 user: LY1598773890)

Hi all:

For query:

CREATE TABLE v0 ( v1 INT PRIMARY KEY DESC, v2 INT ) WITHOUT ROWID;
INSERT INTO v0 VALUES ( 10, 10 );
CREATE INDEX v3 ON v0 ( v2 );

/* STMT 1 */
SELECT * FROM v0 WHERE v2 = 10 AND v1 = 10;
/* Outputs 10|10 */

/* STMT 2 */
SELECT * FROM v0 WHERE v2 = 10 AND v1 < 11;
/* Outputs (empty) */

The results are inconsistent between STMT 1 and STMT 2. Additionally, we observe that:

  1. Remove DESC from v1 can fix the inconsistency.

  2. Remove the WITHOUT ROWID from v0 can fix the inconsistency.

  3. Remove the INDEX v3 can fix the inconsistency.

Looking forward to your reply.

2021-05-08
18:41 Reply: Inconsistent behavior when using nth_value and COUNT (artifact: 4b48b381c4 user: LY1598773890)

Thank you for the information.

2021-05-07
21:45 Edit: (Deleted) (artifact: 47fd432578 user: LY1598773890)
Deleted
21:45 Edit: (Deleted) (artifact: 55dcbf7b03 user: LY1598773890)
Deleted
21:43 Edit reply: Inconsistent behavior when using nth_value and COUNT (artifact: cc037ef242 user: LY1598773890)

It seems that an update of code is being proposed https://sqlite.org/src/info/0d11d777c8d368f0, that block the aggregation function such as COUNT in the ORDER BY clause. The latest code clear some of the confusions we asked in the original thread https://sqlite.org/forum/forumpost/5d3d400d49. However, when testing the latest commit with the query being questioned in this thread, we still see the inconsistency that 4 matches being returned from STMT 2. We wonder whether this output is an expected behavior.

21:41 Post: Inconsistent Error messages between SELECT and SELECT COUNT statements (artifact: 504a0910b0 user: LY1598773890)

Hi all:

For query:


CREATE TABLE v0 ( v1 INT );

/* STMT 1 */
SELECT * FROM v0 JOIN v0 ON v1 = 0 WHERE 0;
/* Error: ambiguous column name: v1 */

/* STMT 2 */
SELECT COUNT ( * ) FROM v0 JOIN v0 ON v1 = 0 WHERE 0;
/* Outputs  '0' */


Apparently both STMT 1 result and STMT 2 result are correct and easy to understand. I just wonder whether we want to make it consistent, like both return error messages, or STMT 1 returns empty set and STMT 2 returns 0.

We appreciate any feedback available. Thank you.

21:40 Reply: Inconsistent behavior when using nth_value and COUNT (artifact: 21f8d0453e user: LY1598773890)

It seems that an update of code is being proposed https://sqlite.org/src/info/0d11d777c8d368f0, that block the aggregation function such as COUNT in the ORDER BY clause, which clear some of the confusion we asked in the original thread https://sqlite.org/forum/forumpost/5d3d400d49. However, when testing the latest commit with the query being questioned in this thread, we still see the inconsistency that 4 matches being returned from STMT 2. We wonder whether this output is an expected behavior.

21:33 Reply: Question about Aggregate Function COUNT and Window Function nth_value (artifact: 4da6783ff6 user: LY1598773890)

It seems that the question is being answered in commit: https://sqlite.org/src/info/0d11d777c8d368f0. This COUNT() aggregate function will be blocked in the ORDER BY clause in the later version of SQLite.

Thank you for the fix and the information being posted in commit: https://sqlite.org/src/info/0d11d777c8d368f0.

20:55 Post: (Deleted) (artifact: 87c09adc9b user: LY1598773890)

Hi all:

For query:



CREATE TABLE v0 ( v1 INT );

/* STMT 1 */
SELECT * FROM v0 JOIN v0 ON v1 = 0 WHERE 0;
/* Error: ambiguous column name: v1 */

/* STMT 2 */
SELECT COUNT ( * ) FROM v0 JOIN v0 ON v1 = 0 WHERE 0;
/* Outputs  '0' */


STMT 2 is just a slight rewritten version of STMT 1, replaced SELECT * with SELECT COUNT(*). Since STMT 1 throw a query error when executing, we expect the same behavior from STMT 2. But the actual output shows otherwise. We wonder whether this is an expected behavior from SQLite.

We appreciate any explanation available. Thank you.

02:23 Reply: Question about Aggregate Function COUNT and Window Function nth_value (artifact: 3a0b6b4901 user: LY1598773890)

The questioned query being posted in this thread is related and being further extended in thread: https://sqlite.org/forum/forumpost/99d452c4a8. We thank for any explanation available for helping us better understand this first SELECT statement.

02:19 Post: Inconsistent behavior when using nth_value and COUNT (artifact: 99d452c4a8 user: LY1598773890)

For query:



CREATE TABLE v0 ( v2 INT, v1 INT );
INSERT INTO v0 VALUES ( 1, 10 );
INSERT INTO v0 VALUES ( 1, 0 );
INSERT INTO v0 VALUES ( 10, 10 );
INSERT INTO v0 VALUES ( 10, 0 );

/* INDEX STMT */
CREATE INDEX v3 ON v0 ( v2, v1 );

/* STMT 1 */
SELECT * FROM v0 WHERE ( v2 IN ( SELECT v2 FROM v0 ORDER BY max ( nth_value ( v1, 10 ) OVER( ), COUNT () ) ) );
/* Outputs " 1|0   1|10 ". 2 matches.*/

/* STMT 2 */
SELECT (v2 IN ( SELECT v2 FROM v0 ORDER BY max ( nth_value ( v1, 10 ) OVER( ), COUNT ( ) ) )) FROM v0;
/* Output " 1  1  1  1 ". 4 matches. */

/* STMT 3 */
SELECT v2 FROM v0 ORDER BY max ( nth_value ( v1, 10 ) OVER( ), COUNT () );
/* Output 1 */


STMT 1 has an inconsistent output compared to STMT 2, which is just a rewritten version of the former. Because we can see from STMT 3 that the IN subquery from STMT 1, STMT 2 returns value 1, and column v2 only has 2 rows equal to 1, we suspect that there might be some errors from STMT 2.

Additionally, we notice by removing the INDEX STMT, the inconsistency is gone and STMT 2 outputs 2 matches as expected.

This behavior is reproducible in SQLite version 3.32.3.

This is a further discovery and an extension from post: https://sqlite.org/forum/forumpost/5d3d400d49. We still appreciate any explanation available that could help us better understand the STMT 3 used in this query sets. Thank you.

2021-05-06
19:13 Reply: Question about Aggregate Function COUNT and Window Function nth_value (artifact: 5d3d400d49 user: LY1598773890)

After double checking with the documentations of Window Function and Aggregate Function, we still do not understand the full picture of this query.

We sincerely appreciate if any explanation could be provided to help us further understand this first SELECT statement. Thank you.

More ↓