SQLite

Check-in [ad06868807]
Login

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

Overview
Comment:Consider an index for queries like "SELECT count(DISTINCT col) FROM ...", even if the index records are not smaller than the table records.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: ad06868807a27f0c96fa5649ebd981e07909eb2c5e03781377c876fdec49abdc
User & Date: dan 2024-01-29 19:30:16.573
Context
2024-01-29
20:36
When generated text JSON from JSONB, do not continue descending into nested structures after an error is seen. This avoids long loops and wait times. (check-in: 97666ec052 user: drh tags: trunk)
19:30
Consider an index for queries like "SELECT count(DISTINCT col) FROM ...", even if the index records are not smaller than the table records. (check-in: ad06868807 user: dan tags: trunk)
15:30
Avoid a potential buffer overread when handling corrupt json blobs. (check-in: 738473dc0a user: dan tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/where.c.
︙︙
3358
3359
3360
3361
3362
3363
3364

3365

3366
3367
3368
3369
3370
3371
3372

  int ii, jj;

  if( pIndex->bUnordered ) return 0;
  if( (pOB = pBuilder->pWInfo->pOrderBy)==0 ) return 0;
  for(ii=0; ii<pOB->nExpr; ii++){
    Expr *pExpr = sqlite3ExprSkipCollateAndLikely(pOB->a[ii].pExpr);
    if( NEVER(pExpr==0) ) continue;

    if( pExpr->op==TK_COLUMN && pExpr->iTable==iCursor ){

      if( pExpr->iColumn<0 ) return 1;
      for(jj=0; jj<pIndex->nKeyCol; jj++){
        if( pExpr->iColumn==pIndex->aiColumn[jj] ) return 1;
      }
    }else if( (aColExpr = pIndex->aColExpr)!=0 ){
      for(jj=0; jj<pIndex->nKeyCol; jj++){
        if( pIndex->aiColumn[jj]!=XN_EXPR ) continue;








>
|
>








3358
3359
3360
3361
3362
3363
3364
3365
3366
3367
3368
3369
3370
3371
3372
3373
3374

  int ii, jj;

  if( pIndex->bUnordered ) return 0;
  if( (pOB = pBuilder->pWInfo->pOrderBy)==0 ) return 0;
  for(ii=0; ii<pOB->nExpr; ii++){
    Expr *pExpr = sqlite3ExprSkipCollateAndLikely(pOB->a[ii].pExpr);
    if( NEVER(pExpr==0) ) continue;
    if( (pExpr->op==TK_COLUMN || pExpr->op==TK_AGG_COLUMN) 
     && pExpr->iTable==iCursor 
    ){
      if( pExpr->iColumn<0 ) return 1;
      for(jj=0; jj<pIndex->nKeyCol; jj++){
        if( pExpr->iColumn==pIndex->aiColumn[jj] ) return 1;
      }
    }else if( (aColExpr = pIndex->aColExpr)!=0 ){
      for(jj=0; jj<pIndex->nKeyCol; jj++){
        if( pIndex->aiColumn[jj]!=XN_EXPR ) continue;
︙︙
Changes to test/distinctagg.test.
︙︙
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105

  3  1  "SELECT count(DISTINCT c) FROM t1"                4
  4  0  "SELECT count(DISTINCT c) FROM t1 WHERE b=3"      3
  5  0  "SELECT count(DISTINCT rowid) FROM t1"           10
  6  0  "SELECT count(DISTINCT a) FROM t1, t2"            5
  7  0  "SELECT count(DISTINCT a) FROM t2, t1"            5
  8  1  "SELECT count(DISTINCT a+b) FROM t1, t2, t2, t2"  6
  9  0  "SELECT count(DISTINCT c) FROM t1 WHERE c=2"      1
 10  1  "SELECT count(DISTINCT t1.rowid) FROM t1, t2"    10
} {
  do_test 3.$tn.1 {
    set prg [db eval "EXPLAIN $sql"]
    set idx [lsearch $prg OpenEphemeral]
    expr {$idx>=0}
  } $use_eph









|








91
92
93
94
95
96
97
98
99
100
101
102
103
104
105

  3  1  "SELECT count(DISTINCT c) FROM t1"                4
  4  0  "SELECT count(DISTINCT c) FROM t1 WHERE b=3"      3
  5  0  "SELECT count(DISTINCT rowid) FROM t1"           10
  6  0  "SELECT count(DISTINCT a) FROM t1, t2"            5
  7  0  "SELECT count(DISTINCT a) FROM t2, t1"            5
  8  1  "SELECT count(DISTINCT a+b) FROM t1, t2, t2, t2"  6
  9  0  "SELECT count(DISTINCT c) FROM t1 WHERE c=2"      1
 10  0  "SELECT count(DISTINCT t1.rowid) FROM t1, t2"    10
} {
  do_test 3.$tn.1 {
    set prg [db eval "EXPLAIN $sql"]
    set idx [lsearch $prg OpenEphemeral]
    expr {$idx>=0}
  } $use_eph
︙︙
144
145
146
147
148
149
150




151
152
153
154
155
156
157
158
159
160



161
162
163
164
165
166
167

  INSERT INTO t2 VALUES(2, 3, 'x');
  INSERT INTO t2 VALUES(2, 3, 'y');
  INSERT INTO t2 VALUES(2, 3, 'z');

  CREATE TABLE t3(x, y, z);
  INSERT INTO t3 VALUES(1,1,1);
  INSERT INTO t3 VALUES(2,2,2);




}

foreach {tn use_eph sql res} {
  1 0  "SELECT count(DISTINCT c) FROM t1 GROUP BY b"   {2 3 0 1}
  2 1  "SELECT count(DISTINCT a) FROM t1 GROUP BY b"   {2 3 0 1}
  3 1  "SELECT count(DISTINCT a) FROM t1 GROUP BY b+c" {0 1 1 1 1}

  4 0  "SELECT count(DISTINCT f) FROM t2 GROUP BY d, e" {1 2 2 3}
  5 1  "SELECT count(DISTINCT f) FROM t2 GROUP BY d" {2 3}
  6 0  "SELECT count(DISTINCT f) FROM t2 WHERE d IS 1 GROUP BY e" {1 2 2}



} {
  do_test 4.$tn.1 {
    set prg [db eval "EXPLAIN $sql"]
    set idx [lsearch $prg OpenEphemeral]
    expr {$idx>=0}
  } $use_eph









>
>
>
>










>
>
>








144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174

  INSERT INTO t2 VALUES(2, 3, 'x');
  INSERT INTO t2 VALUES(2, 3, 'y');
  INSERT INTO t2 VALUES(2, 3, 'z');

  CREATE TABLE t3(x, y, z);
  INSERT INTO t3 VALUES(1,1,1);
  INSERT INTO t3 VALUES(2,2,2);

  CREATE TABLE t4(a);
  CREATE INDEX t4a ON t4(a);
  INSERT INTO t4 VALUES(1), (2), (2), (3), (1);
}

foreach {tn use_eph sql res} {
  1 0  "SELECT count(DISTINCT c) FROM t1 GROUP BY b"   {2 3 0 1}
  2 1  "SELECT count(DISTINCT a) FROM t1 GROUP BY b"   {2 3 0 1}
  3 1  "SELECT count(DISTINCT a) FROM t1 GROUP BY b+c" {0 1 1 1 1}

  4 0  "SELECT count(DISTINCT f) FROM t2 GROUP BY d, e" {1 2 2 3}
  5 1  "SELECT count(DISTINCT f) FROM t2 GROUP BY d" {2 3}
  6 0  "SELECT count(DISTINCT f) FROM t2 WHERE d IS 1 GROUP BY e" {1 2 2}

  7 0  "SELECT count(DISTINCT a) FROM t1" {4}
  8 0  "SELECT count(DISTINCT a) FROM t4" {3}
} {
  do_test 4.$tn.1 {
    set prg [db eval "EXPLAIN $sql"]
    set idx [lsearch $prg OpenEphemeral]
    expr {$idx>=0}
  } $use_eph
︙︙