/ Check-in [a9a82ee8]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Do not pass ORDER BY clauses with non-standard NULL handling to virtual table implementations.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | nulls-last
Files: files | file ages | folders
SHA3-256: a9a82ee88d2d12209a674bd19167277aa91a6912e7a8bbf3714f90d559307fee
User & Date: dan 2019-08-20 15:47:28
Context
2019-08-20
17:51
Add tests for sort-by-index cases that use IN() and non-default NULL handling. check-in: 09d660ec user: dan tags: nulls-last
15:47
Do not pass ORDER BY clauses with non-standard NULL handling to virtual table implementations. check-in: a9a82ee8 user: dan tags: nulls-last
2019-08-19
19:59
Fix problems with window frames that use ORDER BY ... NULLS LAST etc. check-in: 75d665a4 user: dan tags: nulls-last
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

   929    929     */
   930    930     nOrderBy = 0;
   931    931     if( pOrderBy ){
   932    932       int n = pOrderBy->nExpr;
   933    933       for(i=0; i<n; i++){
   934    934         Expr *pExpr = pOrderBy->a[i].pExpr;
   935    935         if( pExpr->op!=TK_COLUMN || pExpr->iTable!=pSrc->iCursor ) break;
          936  +      if( pOrderBy->a[i].sortFlags & KEYINFO_ORDER_BIGNULL ) break;
   936    937       }
   937    938       if( i==n){
   938    939         nOrderBy = n;
   939    940       }
   940    941     }
   941    942   
   942    943     /* Allocate the sqlite3_index_info structure

Changes to test/nulls1.test.

   113    113     INSERT INTO first(last, nulls) VALUES(100,200), (300,400), (200,300);
   114    114     SELECT * FROM first ORDER BY nulls;
   115    115   } {
   116    116     200 100
   117    117     300 200
   118    118     400 300
   119    119   }
          120  +
          121  +#-------------------------------------------------------------------------
          122  +ifcapable vtab {
          123  +  register_echo_module db
          124  +  do_execsql_test 4.0 {
          125  +    CREATE TABLE tx(a INTEGER PRIMARY KEY, b, c);
          126  +    CREATE INDEX i1 ON tx(b);
          127  +    INSERT INTO tx VALUES(1, 1, 1);
          128  +    INSERT INTO tx VALUES(2, NULL, 2);
          129  +    INSERT INTO tx VALUES(3, 3, 3);
          130  +    INSERT INTO tx VALUES(4, NULL, 4);
          131  +    INSERT INTO tx VALUES(5, 5, 5);
          132  +    CREATE VIRTUAL TABLE te USING echo(tx);
          133  +  }
          134  +
          135  +  do_execsql_test 4.1 {
          136  +    SELECT * FROM tx ORDER BY b NULLS FIRST;
          137  +  } {2 {} 2  4 {} 4  1 1 1  3 3 3  5 5 5}
          138  +  do_execsql_test 4.2 {
          139  +    SELECT * FROM te ORDER BY b NULLS FIRST;
          140  +  } {2 {} 2  4 {} 4  1 1 1  3 3 3  5 5 5}
          141  +
          142  +  do_execsql_test 4.3 {
          143  +    SELECT * FROM tx ORDER BY b NULLS LAST;
          144  +  } {1 1 1  3 3 3  5 5 5  2 {} 2  4 {} 4}
          145  +  do_execsql_test 4.4 {
          146  +    SELECT * FROM te ORDER BY b NULLS LAST;
          147  +  } {1 1 1  3 3 3  5 5 5  2 {} 2  4 {} 4}
          148  +}
   120    149   
   121    150   finish_test