SQLite

Check-in [710f75b9]
Login

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

Overview
Comment:Fix a problem handling ORDER BY terms of the form "ORDER BY likely(<integer>)" within window frames.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 710f75b98bb4ac5bfdfa745c1de832dd1e4d9fb387da52b2f0d1353613f5cdc3
User & Date: dan 2021-06-23 11:12:48
Context
2021-06-23
11:39
Show CROSS JOINs in the TreeView debuggingn output. (check-in: 72e09b6c user: drh tags: trunk)
11:12
Fix a problem handling ORDER BY terms of the form "ORDER BY likely(<integer>)" within window frames. (check-in: 710f75b9 user: dan tags: trunk)
2021-06-22
23:24
When an index is used by all branches of the WHERE_MULTI_OR optimization and becomes a covering index, make sure the index has been created prior to NULLing it in the OP_NullRow opcode of a LEFT JOIN. See forum post 0575376e07. The covering-index for WHERE_MULTI_OR optimization was added by [62678be3df35cdcb]. Test cases are in the orindex01.test module of TH3. (check-in: 787c76a8 user: drh tags: trunk)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/window.c.

︙︙
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920

      if( db->mallocFailed ){
        sqlite3ExprDelete(db, pDup);
        break;
      }
      if( bIntToNull ){
        int iDummy;
        Expr *pSub;
        for(pSub=pDup; ExprHasProperty(pSub, EP_Skip); pSub=pSub->pLeft){
          assert( pSub );
        }
        if( sqlite3ExprIsInteger(pSub, &iDummy) ){
          pSub->op = TK_NULL;
          pSub->flags &= ~(EP_IntValue|EP_IsTrue|EP_IsFalse);
          pSub->u.zToken = 0;
        }
      }
      pList = sqlite3ExprListAppend(pParse, pList, pDup);








<
|
<








904
905
906
907
908
909
910

911

912
913
914
915
916
917
918

      if( db->mallocFailed ){
        sqlite3ExprDelete(db, pDup);
        break;
      }
      if( bIntToNull ){
        int iDummy;
        Expr *pSub;

        pSub = sqlite3ExprSkipCollateAndLikely(pDup);

        if( sqlite3ExprIsInteger(pSub, &iDummy) ){
          pSub->op = TK_NULL;
          pSub->flags &= ~(EP_IntValue|EP_IsTrue|EP_IsFalse);
          pSub->u.zToken = 0;
        }
      }
      pList = sqlite3ExprListAppend(pParse, pList, pDup);
︙︙

Changes to test/window1.test.

︙︙
2170
2171
2172
2173
2174
2175
2176














2177
2178
2179

} {1 {misuse of aggregate: sum()}}
do_catchsql_test 69.1 {
  SELECT * FROM t1 WHERE b >= (SELECT b FROM t1 ORDER BY lead(b) OVER () AND sum(a));
} {1 {misuse of aggregate: sum()}}
do_catchsql_test 69.2 {
  SELECT * FROM t1 WHERE b <= (SELECT b FROM t1 ORDER BY lead(b) OVER () AND sum(a));
} {1 {misuse of aggregate: sum()}}
















finish_test








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




2170
2171
2172
2173
2174
2175
2176
2177
2178
2179
2180
2181
2182
2183
2184
2185
2186
2187
2188
2189
2190
2191
2192
2193

} {1 {misuse of aggregate: sum()}}
do_catchsql_test 69.1 {
  SELECT * FROM t1 WHERE b >= (SELECT b FROM t1 ORDER BY lead(b) OVER () AND sum(a));
} {1 {misuse of aggregate: sum()}}
do_catchsql_test 69.2 {
  SELECT * FROM t1 WHERE b <= (SELECT b FROM t1 ORDER BY lead(b) OVER () AND sum(a));
} {1 {misuse of aggregate: sum()}}

# 2021-06-23
# Forum https://sqlite.org/forum/forumpost/31e0432608
#
reset_db
do_execsql_test 70.0 {
  CREATE TABLE t1(a);
}
do_execsql_test 70.1 {
  SELECT substr(a,4,lag(a,7) OVER(PARTITION BY 'cf23' ORDER BY 2)) AS ca0 FROM t1 ORDER BY ca0;
}
do_execsql_test 70.2 {
  SELECT substr(a,4,lag(a,7) OVER(PARTITION BY 'cf23' ORDER BY likely(2))) AS ca0 FROM t1 ORDER BY ca0;
}


finish_test