/ 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 Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

929
930
931
932
933
934
935

936
937
938
939
940
941
942
  */
  nOrderBy = 0;
  if( pOrderBy ){
    int n = pOrderBy->nExpr;
    for(i=0; i<n; i++){
      Expr *pExpr = pOrderBy->a[i].pExpr;
      if( pExpr->op!=TK_COLUMN || pExpr->iTable!=pSrc->iCursor ) break;

    }
    if( i==n){
      nOrderBy = n;
    }
  }

  /* Allocate the sqlite3_index_info structure







>







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

  /* Allocate the sqlite3_index_info structure

Changes to test/nulls1.test.

113
114
115
116
117
118
119
120





























121
  INSERT INTO first(last, nulls) VALUES(100,200), (300,400), (200,300);
  SELECT * FROM first ORDER BY nulls;
} {
  200 100
  300 200
  400 300
}






























finish_test








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

113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
  INSERT INTO first(last, nulls) VALUES(100,200), (300,400), (200,300);
  SELECT * FROM first ORDER BY nulls;
} {
  200 100
  300 200
  400 300
}

#-------------------------------------------------------------------------
ifcapable vtab {
  register_echo_module db
  do_execsql_test 4.0 {
    CREATE TABLE tx(a INTEGER PRIMARY KEY, b, c);
    CREATE INDEX i1 ON tx(b);
    INSERT INTO tx VALUES(1, 1, 1);
    INSERT INTO tx VALUES(2, NULL, 2);
    INSERT INTO tx VALUES(3, 3, 3);
    INSERT INTO tx VALUES(4, NULL, 4);
    INSERT INTO tx VALUES(5, 5, 5);
    CREATE VIRTUAL TABLE te USING echo(tx);
  }

  do_execsql_test 4.1 {
    SELECT * FROM tx ORDER BY b NULLS FIRST;
  } {2 {} 2  4 {} 4  1 1 1  3 3 3  5 5 5}
  do_execsql_test 4.2 {
    SELECT * FROM te ORDER BY b NULLS FIRST;
  } {2 {} 2  4 {} 4  1 1 1  3 3 3  5 5 5}

  do_execsql_test 4.3 {
    SELECT * FROM tx ORDER BY b NULLS LAST;
  } {1 1 1  3 3 3  5 5 5  2 {} 2  4 {} 4}
  do_execsql_test 4.4 {
    SELECT * FROM te ORDER BY b NULLS LAST;
  } {1 1 1  3 3 3  5 5 5  2 {} 2  4 {} 4}
}

finish_test