SQLite Forum

Timeline
Login

19 forum posts by user LY1598773890 occurring on or before 2021-05-06 19:13:41.

More ↑
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.

01:52 Post: Question about Aggregate Function COUNT and Window Function nth_value (artifact: 540fdfef77 user: LY1598773890)

Hi all:

For query:


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

select rowid, v2, v1, nth_value(v1, 1) over () FROM v0 ORDER BY (COUNT());
/* This SELECT stmt outputs '1|1|10|10'  */

select rowid, v2, v1 FROM v0 GROUP BY v1 ORDER BY (COUNT());
/* This SELECT stmt outputs '3|1|3    1|1|10'   */

select rowid, v2, v1 FROM v0 ORDER BY (COUNT());
/* This SELECT stmt outputs 'Error: misuse of aggregate: COUNT()' */


We cannot fully understand the first SELECT statement. Although this output is highly possible to be expected, we do not understand why the Window Function nth_value is able to rectify the error proposed by COUNT() shown in the third SELECT statement.

We appreciate any explanation available to help us understand this first SELECT statement. Thank you.

2021-05-04
16:45 Reply: Possible inconsistent result when use EXISTS ( SELECT...) expression in a WHERE clause (artifact: bd8de0d188 user: LY1598773890)

Thank you for the explanation.

04:06 Edit: Possible inconsistent result when use EXISTS ( SELECT...) expression in a WHERE clause (artifact: 8692d94725 user: LY1598773890)

For query:


CREATE TABLE v0 ( v1 INTEGER);
INSERT INTO v0 VALUES ( 10 ),  ( 7 );
CREATE TABLE v4 ( v5 INTEGER PRIMARY KEY );
INSERT INTO v4 VALUES ( 10 );

/* First SELECT stmt */
SELECT cast ( (SELECT v5 FROM v4 WHERE EXISTS ( SELECT v5 WHERE v5 = v1 )) as BOOL) != 0   FROM  v0 ;
/* Output: 1 */

/* Second SELECT stmt */
SELECT TOTAL(cast ( (SELECT v5 FROM v4 WHERE EXISTS ( SELECT v5 WHERE v5 = v1 )) as BOOL) != 0)   FROM  v0 ;
/* Output: 2.0 */

/* Third SELECT stmt, replace v1 with 10, matching the first component in v1. */
SELECT cast ( (SELECT v5 FROM v4 WHERE EXISTS ( SELECT v5 WHERE v5 = 10 )) as BOOL) != 0   FROM  v0 ; 
/* Output: 1 1 */

/* Fourth SELECT stmt, as validation for the third SELECT stmt */
SELECT TOTAL(cast ( (SELECT v5 FROM v4 WHERE EXISTS ( SELECT v5 WHERE v5 = 10 )) as BOOL) != 0)   FROM  v0 ;
/* Output: 2.0 */

/* Fifth SELECT stmt, replace v1 with 7, matching the second component in v1. */
SELECT cast ( (SELECT v5 FROM v4 WHERE EXISTS ( SELECT v5 WHERE v5 = 7 )) as BOOL) != 0   FROM  v0 ;
/* Output EMPTY */

/* Sixth SELECT stmt, as validation for the fifth SELECT stmt */
SELECT TOTAL(cast ( (SELECT v5 FROM v4 WHERE EXISTS ( SELECT v5 WHERE v5 = 7 )) as BOOL) != 0)   FROM  v0 ;
/* Output 0.0 */


The first and the second SELECT stmts contains a correlated subquery in the select clause. We expect the subquery would be evaluated each time when iterating through column v1. These steps are being shown in the third and the fifth SELECT stmts.

However, the second SELECT stmt result is mismatched with the result of the first SELECT stmt. Because they have a similar correlated subquery inside the select clause, we expect the second result to be 1.0, rather than 2.0 in the reality. We wonder whether it is a mistake in the code or an expected behavior.

This inconsistent behavior from the second SELECT stmt, seems to be introduced in Fossil: 9f90a88221d0694951c353e58efce342eb0b868b8ca6a4469c8205e5c7855b24.

Looking forward to your reply.

04:01 Post: Possible inconsistent result when use EXISTS ( SELECT...) expression in a WHERE clause (artifact: 98c67a446c user: LY1598773890)

For query:


CREATE TABLE v0 ( v1 INTEGER);
INSERT INTO v0 VALUES ( 10 ),  ( 7 );
CREATE TABLE v4 ( v5 INTEGER PRIMARY KEY );
INSERT INTO v4 VALUES ( 10 );

/* First SELECT stmt */
SELECT cast ( (SELECT v5 FROM v4 WHERE EXISTS ( SELECT v5 WHERE v5 = v1 )) as BOOL) != 0   FROM  v0 ;
/* Output: 1 */

/* Second SELECT stmt */
SELECT TOTAL(cast ( (SELECT v5 FROM v4 WHERE EXISTS ( SELECT v5 WHERE v5 = v1 )) as BOOL) != 0)   FROM  v0 ;
/* Output: 2.0 */

/* Third SELECT stmt, replace v1 with 10, matching the first component in v1. */
SELECT cast ( (SELECT v5 FROM v4 WHERE EXISTS ( SELECT v5 WHERE v5 = 10 )) as BOOL) != 0   FROM  v0 ; 
/* Output: 1 1 */

/* Fourth SELECT stmt, as validation for the third SELECT stmt */
SELECT TOTAL(cast ( (SELECT v5 FROM v4 WHERE EXISTS ( SELECT v5 WHERE v5 = 10 )) as BOOL) != 0)   FROM  v0 ;
/* Output: 2.0 */

/* Fifth SELECT stmt, replace v1 with 7, matching the second component in v1. */
SELECT cast ( (SELECT v5 FROM v4 WHERE EXISTS ( SELECT v5 WHERE v5 = 7 )) as BOOL) != 0   FROM  v0 ;
/* Output EMPTY */

/* Sixth SELECT stmt, as validation for the fifth SELECT stmt */
SELECT TOTAL(cast ( (SELECT v5 FROM v4 WHERE EXISTS ( SELECT v5 WHERE v5 = 7 )) as BOOL) != 0)   FROM  v0 ;
/* Output 0.0 */


The first and the second SELECT stmts contains a correlated subquery in the select clause. We expect the subquery would be evaluated each time when iterating through column v1 from v0. These steps are being shown in the third and the fifth SELECT stmt. However, the second SELECT stmt result is mismatched with the result of the first SELECT stmt. We expect the second result to be 1.0, rather than 2.0 in the reality. We wonder whether it is a mistake in the code or an expected behavior.

This inconsistent behavior from the second SELECT stmt, seems to be introduced in Fossil: 9f90a88221d0694951c353e58efce342eb0b868b8ca6a4469c8205e5c7855b24.

Looking forward to your reply.

2021-04-29
14:35 Reply: Confusion of some subquery results (artifact: 5809a32028 user: LY1598773890)

Thank you for the explanation and the confirming.

04:35 Edit: Confusion of some subquery results (artifact: 6a3ec138e9 user: LY1598773890)

For query:

CREATE TABLE v0 ( v2 CHAR(30), v1 CHAR(30) );
INSERT INTO v0 ( v1 ) VALUES ( 10 ),( 0 );
CREATE INDEX v19 ON v0 ( v2, v1 );
INSERT INTO v0 VALUES ( 10, 10 );
INSERT INTO v0 VALUES ( 0, 10 );
SELECT * FROM v0;
SELECT '----------------------------------------';
SELECT * FROM v0 WHERE ( ( v1 IN ( SELECT v2 FROM v0 ORDER BY v2 ) ));
SELECT '----------------------------------------';
SELECT * FROM v0 WHERE ( v2 = 0 );
SELECT '----------------------------------------';
SELECT * FROM v0 WHERE ( ( v1 IN ( SELECT v2 FROM v0 ORDER BY v2 ) ) AND v2 = 0);

We got the result:

|10
|0
10|10
0|10
------------
|10
|0
10|10
0|10
------------
0|10
------------
(EMPTY RESULT)

Since the second and the third SELECT statements return some results, we expect some results being outputted from the last SELECT statement too. However, there is no outputs from the last SELECT statement.

A few more interesting behaviors that we observe is that:

-If we delete the CREATE INDEX statement, the inconsistency is gone and the last SELECT successfully output 0|10 just like the third SELECT statement.

-If we change the data type of v1, v2 to be INT, the inconsistency is also gone and the last SELECT successfully output 0|10 just like the third SELECT statement.

We are not sure whether this is a bug or this is just an affinity behavior that is known and expected. We are really appreciated for any explanation provided. Thank you.

04:31 Edit: Confusion of some subquery results (artifact: 9ec1361d32 user: LY1598773890)

For query:

CREATE TABLE v0 ( v2 CHAR(30), v1 CHAR(30) );
INSERT INTO v0 ( v1 ) VALUES ( 10 ),( 0 );
CREATE INDEX v19 ON v0 ( v2, v1 );
INSERT INTO v0 VALUES ( 10, 10 );
INSERT INTO v0 VALUES ( 0, 10 );
SELECT * FROM v0;
SELECT '----------------------------------------';
SELECT * FROM v0 WHERE ( ( v1 IN ( SELECT v2 FROM v0 ORDER BY v2 ) ));
SELECT '----------------------------------------';
SELECT * FROM v0 WHERE ( v2 = 0 );
SELECT '----------------------------------------';
SELECT * FROM v0 WHERE ( ( v1 IN ( SELECT v2 FROM v0 ORDER BY v2 ) ) AND v2 = 0);

We got the result:

|10
|0
10|10
0|10
------------
|10
|0
10|10
0|10
------------
0|10
------------
(EMPTY RESULT)

Since the first two SELECT statements return some results, we expect some results being outputted from the third SELECT statement too. However, there is no outputs from the third SELECT statement.

A few more interesting behaviors that we observe is that:

-If we delete the CREATE INDEX statement, the inconsistency is gone and the third SELECT successfully output 0|10 just like the second SELECT statement.

-If we change the data type of v1, v2 to be INT, the inconsistency is also gone and the third SELECT successfully output 0|10 just like the second SELECT statement.

We are not sure whether this is a bug or this is just an affinity behavior that is known and expected. We are really appreciated for any explanation provided. Thank you.

04:29 Post: Confusion of some subquery results (artifact: 2dc279d40b user: LY1598773890)

For query:

CREATE TABLE v0 ( v2 CHAR(30), v1 CHAR(30) );
INSERT INTO v0 ( v1 ) VALUES ( 10 ),( 0 );
CREATE INDEX v19 ON v0 ( v2, v1 );
INSERT INTO v0 VALUES ( 10, 10 );
INSERT INTO v0 VALUES ( 0, 10 );
SELECT * FROM v0;
SELECT '----------------------------------------';
SELECT * FROM v0 WHERE ( ( v1 IN ( SELECT v2 FROM v0 ORDER BY v2 ) ));
SELECT '----------------------------------------';
SELECT * FROM v0 WHERE ( v2 = 0 );
SELECT '----------------------------------------';
SELECT * FROM v0 WHERE ( ( v1 IN ( SELECT v2 FROM v0 ORDER BY v2 ) ) AND v2 = 0);

We got the result:

|10
|0
10|10
0|10
------------
|10
|0
10|10
0|10
------------
0|10
------------
(EMPTY RESULT)

Since the first two SELECT statements return some results, we expect some results being outputted from the third SELECT statement too. However, there is no outputs from the third SELECT statement.

A few more interesting behaviors that we observe is that:

-If we delete the CREATE INDEX statement, the inconsistency is gone and the third SELECT successfully output 0|10 just like the second SELECT statement.

-If we change the data type of v1, v2 to be INT, the inconsistency is also gone and the third SELECT successfully output 0|10 just like the second SELECT statement.

We are not sure whether this is a bug or this is just an affinity behavior that is known and expected. We are really appreciated for explanation provided. Thank you.

2021-04-17
21:02 Reply: Inconsistant output when likely/unlikely and join keyword is applied (artifact: 8115ae02c6 user: LY1598773890)

It appears that the bug has been fixed by: https://sqlite.org/src/info/2363a14ca723c034.

Appreciate with the fix.

2021-04-15
17:08 Post: Inconsistant output when likely/unlikely and join keyword is applied (artifact: c38462ab5e user: LY1598773890)

For query:

CREATE TABLE v0 ( v1 PRIMARY KEY , v2 , v3 ) ;
INSERT INTO v0 VALUES ( '111' , '222' , '333' ) ;
CREATE TABLE v4 ( v5 PRIMARY KEY ) ;
INSERT INTO v4 VALUES ( '0' ) ;
SELECT * FROM v4 JOIN v0 ON v0.v3 = v0.v1 AND v0.v3 = '111';
SELECT * FROM v4 JOIN v0 ON likely( v0.v3 = v0.v1 ) AND v0.v3 = '111';
SELECT * FROM v4 JOIN v0 ON unlikely( v0.v3 = v0.v1 ) AND v0.v3 = '111';

The expected result should be:

(EMPTY)

(EMPTY)

(EMPTY)

However, the actual output is:

(EMPTY)

0|111|222|333

0|111|222|333

This inconsistent behavior is reproducible with the latest development version: FossilOrigin-Name: b5dc7aba036cfd6d09c68dd17608328063634ca99ff341f97bab2dc2a1f59b11; and one of the latest released version: SQLite version 3.31.1

This inconsistent behavior seems to be introduced in: FossilOrigin-Name: 82c67efb723dba387964f690cd459b420e59e3367d9589016597a76531596391

2021-04-08
01:12 Edit reply: Query triggers Segmentation Fault (artifact: ba2189d29e user: LY1598773890)

Thank you for the information.

We are indeed fuzzing the latest development version of SQLite. Hope we can provide more useful reports in the future.

01:10 Reply: Query triggers Segmentation Fault (artifact: 5412f98c86 user: LY1598773890)

Thank you for the information.

We are indeed testing the latest development version of SQLite using our own fuzzing tool. Hope we can provide more useful reports in the future.

2021-04-07
21:20 Post: Query triggers Segmentation Fault (artifact: 174ffb6948 user: LY1598773890)

For query:

CREATE TABLE v1 ( v2 UNIQUE, v3 AS( TYPEOF ( NULL ) ) UNIQUE );
SELECT COUNT ( DISTINCT TRUE ) FROM v1 GROUP BY likelihood ( v3 , 0.100000 );

When testing with FossilOrigin-Name: 3039bcaff95bb5d096c80b5eefdaeda6abd1d1337e829f32fd28a968f663f481, triggers Segmentation Fault.

ASAN log:

AddressSanitizer:DEADLYSIGNAL

==2099735==ERROR: AddressSanitizer: SEGV on unknown address 0x000000000000 (pc 0x56223fdde8fb bp 0x7fffd65a0aa0 sp 0x7fffd65a0a30 T0) ==2099735==The signal is caused by a READ memory access. ==2099735==Hint: address points to the zero page. #0 0x56223fdde8fa in sqlite3DbMallocRawNN /home/luy70/Squirrel_DBMS_Project/sqlite3_source/sqlite/bld/0d23f678b1d26a469a801c1e80f46003c55cf2e1_AFL_ASAN/sqlite3.c:28241 #1 0x56223fdded88 in strAccumFinishRealloc /home/luy70/Squirrel_DBMS_Project/sqlite3_source/sqlite/bld/0d23f678b1d26a469a801c1e80f46003c55cf2e1_AFL_ASAN/sqlite3.c:29489 #2 0x56223fe0b8bf in sqlite3VMPrintf /home/luy70/Squirrel_DBMS_Project/sqlite3_source/sqlite/bld/0d23f678b1d26a469a801c1e80f46003c55cf2e1_AFL_ASAN/sqlite3.c:29611 #3 0x56223fe0bb23 in vdbeVComment /home/luy70/Squirrel_DBMS_Project/sqlite3_source/sqlite/bld/0d23f678b1d26a469a801c1e80f46003c55cf2e1_AFL_ASAN/sqlite3.c:79791 #4 0x56223fe0bb23 in vdbeVComment /home/luy70/Squirrel_DBMS_Project/sqlite3_source/sqlite/bld/0d23f678b1d26a469a801c1e80f46003c55cf2e1_AFL_ASAN/sqlite3.c:79784 #5 0x56223fe0bca5 in sqlite3VdbeComment /home/luy70/Squirrel_DBMS_Project/sqlite3_source/sqlite/bld/0d23f678b1d26a469a801c1e80f46003c55cf2e1_AFL_ASAN/sqlite3.c:79798 #6 0x56223fee4e06 in sqlite3Select /home/luy70/Squirrel_DBMS_Project/sqlite3_source/sqlite/bld/0d23f678b1d26a469a801c1e80f46003c55cf2e1_AFL_ASAN/sqlite3.c:138406 #7 0x56223ff7b92c in yy_reduce /home/luy70/Squirrel_DBMS_Project/sqlite3_source/sqlite/bld/0d23f678b1d26a469a801c1e80f46003c55cf2e1_AFL_ASAN/sqlite3.c:161094 #8 0x56223ff7b92c in sqlite3Parser /home/luy70/Squirrel_DBMS_Project/sqlite3_source/sqlite/bld/0d23f678b1d26a469a801c1e80f46003c55cf2e1_AFL_ASAN/sqlite3.c:162458 #9 0x56223ff7b92c in sqlite3RunParser /home/luy70/Squirrel_DBMS_Project/sqlite3_source/sqlite/bld/0d23f678b1d26a469a801c1e80f46003c55cf2e1_AFL_ASAN/sqlite3.c:163745 #10 0x56223ff89d4b in sqlite3Prepare /home/luy70/Squirrel_DBMS_Project/sqlite3_source/sqlite/bld/0d23f678b1d26a469a801c1e80f46003c55cf2e1_AFL_ASAN/sqlite3.c:131039 #11 0x56223ff8a84e in sqlite3LockAndPrepare /home/luy70/Squirrel_DBMS_Project/sqlite3_source/sqlite/bld/0d23f678b1d26a469a801c1e80f46003c55cf2e1_AFL_ASAN/sqlite3.c:131113 #12 0x56223ff8aa99 in sqlite3_prepare_v2 /home/luy70/Squirrel_DBMS_Project/sqlite3_source/sqlite/bld/0d23f678b1d26a469a801c1e80f46003c55cf2e1_AFL_ASAN/sqlite3.c:131198 #13 0x56223fd7e9be in shell_exec /home/luy70/Squirrel_DBMS_Project/sqlite3_source/sqlite/bld/0d23f678b1d26a469a801c1e80f46003c55cf2e1_AFL_ASAN/shell.c:13591 #14 0x56223fd82459 in runOneSqlLine /home/luy70/Squirrel_DBMS_Project/sqlite3_source/sqlite/bld/0d23f678b1d26a469a801c1e80f46003c55cf2e1_AFL_ASAN/shell.c:20615 #15 0x56223fd9b52d in process_input /home/luy70/Squirrel_DBMS_Project/sqlite3_source/sqlite/bld/0d23f678b1d26a469a801c1e80f46003c55cf2e1_AFL_ASAN/shell.c:20715 #16 0x56223fd45e16 in main /home/luy70/Squirrel_DBMS_Project/sqlite3_source/sqlite/bld/0d23f678b1d26a469a801c1e80f46003c55cf2e1_AFL_ASAN/shell.c:21516 #17 0x7efc26a220b2 in __libc_start_main (/lib/x86_64-linux-gnu/libc.so.6+0x270b2) #18 0x56223fd47a9d in _start (/home/luy70/Squirrel_DBMS_Project/sqlite3_source/sqlite/bld/0d23f678b1d26a469a801c1e80f46003c55cf2e1_AFL_ASAN/sqlite3+0x4ea9d)

AddressSanitizer can not provide additional info. SUMMARY: AddressSanitizer: SEGV /home/luy70/Squirrel_DBMS_Project/sqlite3_source/sqlite/bld/0d23f678b1d26a469a801c1e80f46003c55cf2e1_AFL_ASAN/sqlite3.c:28241 in sqlite3DbMallocRawNN ==2099735==ABORTING

02:10 Reply: Extra values outputted, with Partial Index and DISTINCT constraints applied (artifact: 3f93e95e08 user: LY1598773890)

Thank you for the details and the patched information.

2021-04-06
22:56 Post: Extra values outputted, with Partial Index and DISTINCT constraints applied (artifact: 66954e9ece user: LY1598773890)

For query:

CREATE TABLE person ( pid INT) ;
CREATE UNIQUE INDEX idx ON person ( pid ) WHERE pid == 1;
INSERT INTO person VALUES (1), (10), (10);
SELECT DISTINCT pid FROM person;
SELECT DISTINCT pid FROM person where pid = 10;

The expected answer should be: 1 10 (from the first SELECT) 10 (from the second SELECT)

However, the actual output is: 1 10 (from the first SELECT) 10 10 (from the second SELECT)

An extra 10 is outputted from the second SELECT stmt, with the constraint DISTINCT being applied.

Removing the CREATE UNIQUE INDEX statement seems to fix the problem.

2021-04-05
16:17 Reply: COUNT statement returns inconsistent values when used with UNION ALL (artifact: 691f0047be user: LY1598773890)

Thank you for the explanation.

05:08 Reply: COUNT statement returns inconsistent values when used with UNION ALL (artifact: 19bee66ece user: LY1598773890)

Sorry. "de9ed6293de53e89b7c37e7de9a8697d86d7f619" is the github commit ID.

The Fossil hash should be: FossilOrigin-Name: 00e4bf74d3dfb87666a2266905f7d1a2afc6eb088d22cfd4f38f048733d6b936

04:55 Post: COUNT statement returns inconsistent values when used with UNION ALL (artifact: 8ea1937ce2 user: LY1598773890)

For query:

CREATE VIEW v0 ( v1 ) AS SELECT CAST ( 0 AS VARCHAR(1) );
CREATE VIEW v2 ( v3 ) AS SELECT * FROM v0 UNION ALL SELECT ( v1 IN ( 10) ) from v0;
SELECT * FROM v2 NATURAL JOIN v2;
SELECT COUNT(*) FROM v2 NATURAL JOIN v2;

The expected answer should be: 00 2. However, the actual output is: 0000 2.

This problem seems to be introduced by de9ed6293de53e89b7c37e7de9a8697d86d7f619