SQLite

Check-in [c84d5602]
Login

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

Overview
Comment:Do not disable unused columns in a UNION ALL sub-query if any component of the sub-query is DISTINCT. Problem introduced by [7c2d3406000dc8ac] and reported by forum post aeae62275ebbf584.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: c84d5602ac9bfb4f12c3cf62033af616e51913c26877d1a0761363a625295f53
User & Date: dan 2023-08-31 18:00:10
Original Comment: Do not disable unused columns in a UNION ALL sub-query if any component of the sub-query is DISTINCT.
Context
2023-09-01
11:05
In the OPFS VFS' importDb() methods, overwrite the header bytes 18 and 19 with 1 instead of 0. Both seem to work, but 1 is correct. (check-in: 1c532e80 user: stephan tags: trunk)
2023-08-31
18:26
Do not disable unused columns in a UNION ALL sub-query if any component of the sub-query is DISTINCT. (check-in: 0d50d271 user: drh tags: branch-3.43)
18:00
Do not disable unused columns in a UNION ALL sub-query if any component of the sub-query is DISTINCT. Problem introduced by [7c2d3406000dc8ac] and reported by forum post aeae62275ebbf584. (check-in: c84d5602 user: dan tags: trunk)
2023-08-30
18:19
Fix a bug in json_array_length() introduced in version 3.43.0 by check-in [df099ad713011b67]. If the JSON input comes from json_remove(), the removed array entries are still counted as part of the array length. Bug report in forum post 0560d5e482. (check-in: 69a63595 user: drh tags: trunk)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/select.c.

︙︙
5297
5298
5299
5300
5301
5302
5303

5304
5305
5306
5307
5308
5309
5310
5311
5312
5313
5314
5315
5316

    return 0;
  }
  assert( pItem->pTab!=0 );
  pTab = pItem->pTab;
  assert( pItem->pSelect!=0 );
  pSub = pItem->pSelect;
  assert( pSub->pEList->nExpr==pTab->nCol );

  if( (pSub->selFlags & (SF_Distinct|SF_Aggregate))!=0 ){
    testcase( pSub->selFlags & SF_Distinct );
    testcase( pSub->selFlags & SF_Aggregate );
    return 0;
  }
  for(pX=pSub; pX; pX=pX->pPrior){
    if( pX->pPrior && pX->op!=TK_ALL ){
      /* This optimization does not work for compound subqueries that
      ** use UNION, INTERSECT, or EXCEPT.  Only UNION ALL is allowed. */
      return 0;
    }
#ifndef SQLITE_OMIT_WINDOWFUNC
    if( pX->pWin ){








>
|
|
|
|
|
<








5297
5298
5299
5300
5301
5302
5303
5304
5305
5306
5307
5308
5309

5310
5311
5312
5313
5314
5315
5316

    return 0;
  }
  assert( pItem->pTab!=0 );
  pTab = pItem->pTab;
  assert( pItem->pSelect!=0 );
  pSub = pItem->pSelect;
  assert( pSub->pEList->nExpr==pTab->nCol );
  for(pX=pSub; pX; pX=pX->pPrior){
    if( (pX->selFlags & (SF_Distinct|SF_Aggregate))!=0 ){
      testcase( pX->selFlags & SF_Distinct );
      testcase( pX->selFlags & SF_Aggregate );
      return 0;
    }

    if( pX->pPrior && pX->op!=TK_ALL ){
      /* This optimization does not work for compound subqueries that
      ** use UNION, INTERSECT, or EXCEPT.  Only UNION ALL is allowed. */
      return 0;
    }
#ifndef SQLITE_OMIT_WINDOWFUNC
    if( pX->pWin ){
︙︙

Changes to test/selectH.test.

︙︙
109
110
111
112
113
114
115




















116







117
118

# 2023-02-25 dbsqlfuzz bf1d3ed6e0e0dd8766027797d43db40c776d2b15
#
do_execsql_test 4.1 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT);
  SELECT 1 FROM (SELECT DISTINCT name COLLATE rtrim FROM sqlite_schema
                 UNION ALL SELECT a FROM t1);




















} 1








finish_test








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
>
>
>
>
>
>
>



109
110
111
112
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

# 2023-02-25 dbsqlfuzz bf1d3ed6e0e0dd8766027797d43db40c776d2b15
#
do_execsql_test 4.1 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT);
  SELECT 1 FROM (SELECT DISTINCT name COLLATE rtrim FROM sqlite_schema
                 UNION ALL SELECT a FROM t1);
} {1 1}

do_execsql_test 4.2 {
  SELECT DISTINCT name COLLATE rtrim FROM sqlite_schema 
    UNION ALL 
  SELECT a FROM t1
} {v1 t1}

#-------------------------------------------------------------------------
# forum post https://sqlite.org/forum/forumpost/b83c7b2168
#
reset_db
do_execsql_test 5.0 {
  CREATE TABLE t1 (val1);
  INSERT INTO t1 VALUES(4);
  INSERT INTO t1 VALUES(5);
  CREATE TABLE t2 (val2);
}
do_execsql_test 5.1 {
  SELECT DISTINCT val1 FROM t1 UNION ALL SELECT val2 FROM t2;
} {
  4 5
}
do_execsql_test 5.2 {
  SELECT count(1234) FROM (
    SELECT DISTINCT val1 FROM t1 UNION ALL SELECT val2 FROM t2
  )
} {2}

finish_test