SQLite

Check-in [bcdd66c169]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Fix a crash that could occur if a sub-select that uses both DISTINCT and window functions also used an ORDER BY that is the same as its select list.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: bcdd66c1691955c697f3d756c2b035acfe98f6aad72e90b0021bab6e9023b3ba
User & Date: dan 2019-11-22 10:14:01.691
Context
2019-11-22
11:38
Add more test cases from Yongheng Chen and Rui Zhong. (check-in: f24e6ca4f2 user: drh tags: trunk)
10:14
Fix a crash that could occur if a sub-select that uses both DISTINCT and window functions also used an ORDER BY that is the same as its select list. (check-in: bcdd66c169 user: dan tags: trunk)
00:42
Revise the SQLITE_OPEN_NOFOLLOW so that it actually uses O_NOFOLLOW in the open() system call. This backs out the SQLITE_ACCESS_SYMLINK value but adds the new SQLITE_OK_SYMLINK return code from the xFullPathname method of sqlite3_vfs when that routine resolves symbolic links. O_NOFOLLOW is always included in open() system calls for journal files. (check-in: 6a64fb6a2d user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/select.c.
6060
6061
6062
6063
6064
6065
6066

6067
6068
6069
6070
6071
6072
6073
  ** The second form is preferred as a single index (or temp-table) may be 
  ** used for both the ORDER BY and DISTINCT processing. As originally 
  ** written the query must use a temp-table for at least one of the ORDER 
  ** BY and DISTINCT, and an index or separate temp-table for the other.
  */
  if( (p->selFlags & (SF_Distinct|SF_Aggregate))==SF_Distinct 
   && sqlite3ExprListCompare(sSort.pOrderBy, pEList, -1)==0

  ){
    p->selFlags &= ~SF_Distinct;
    pGroupBy = p->pGroupBy = sqlite3ExprListDup(db, pEList, 0);
    /* Notice that even thought SF_Distinct has been cleared from p->selFlags,
    ** the sDistinct.isTnct is still set.  Hence, isTnct represents the
    ** original setting of the SF_Distinct flag, not the current setting */
    assert( sDistinct.isTnct );







>







6060
6061
6062
6063
6064
6065
6066
6067
6068
6069
6070
6071
6072
6073
6074
  ** The second form is preferred as a single index (or temp-table) may be 
  ** used for both the ORDER BY and DISTINCT processing. As originally 
  ** written the query must use a temp-table for at least one of the ORDER 
  ** BY and DISTINCT, and an index or separate temp-table for the other.
  */
  if( (p->selFlags & (SF_Distinct|SF_Aggregate))==SF_Distinct 
   && sqlite3ExprListCompare(sSort.pOrderBy, pEList, -1)==0
   && p->pWin==0
  ){
    p->selFlags &= ~SF_Distinct;
    pGroupBy = p->pGroupBy = sqlite3ExprListDup(db, pEList, 0);
    /* Notice that even thought SF_Distinct has been cleared from p->selFlags,
    ** the sDistinct.isTnct is still set.  Hence, isTnct represents the
    ** original setting of the SF_Distinct flag, not the current setting */
    assert( sDistinct.isTnct );
Changes to test/window1.test.
1238
1239
1240
1241
1242
1243
1244













1245
1246
db close
sqlite3 db :memory:
do_catchsql_test 32.10 {
  CREATE VIEW a AS SELECT NULL INTERSECT SELECT NULL ORDER BY s() OVER R;
  CREATE TABLE a0 AS SELECT 0;
  ALTER TABLE a0 RENAME TO S;
} {1 {error in view a: 1st ORDER BY term does not match any column in the result set}}














finish_test







>
>
>
>
>
>
>
>
>
>
>
>
>


1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
db close
sqlite3 db :memory:
do_catchsql_test 32.10 {
  CREATE VIEW a AS SELECT NULL INTERSECT SELECT NULL ORDER BY s() OVER R;
  CREATE TABLE a0 AS SELECT 0;
  ALTER TABLE a0 RENAME TO S;
} {1 {error in view a: 1st ORDER BY term does not match any column in the result set}}

reset_db
do_execsql_test 33.1 {
  CREATE TABLE t1(aa, bb);
  INSERT INTO t1 VALUES(1, 2);
  INSERT INTO t1 VALUES(5, 6);
  CREATE TABLE t2(x);
  INSERT INTO t2 VALUES(1);
}
do_execsql_test 33.2 {
  SELECT (SELECT DISTINCT sum(aa) OVER() FROM t1 ORDER BY 1), x FROM t2 
  ORDER BY 1;
} {6 1}

finish_test