SQLite

Check-in [5acc3ef8]
Login

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

Overview
Comment:Another fix to the indexed expressions in aggregate queries with GROUP BY enhancement of ticket [99378177930f87bd] and implemented by check-in [b9190d3da70c4171] to address a problem described by forum post f34e32d120,
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 5acc3ef83e16a9f2803981a6e61eca62fe177cac4eed1befe2ade3a5977a1eab
User & Date: drh 2023-04-18 14:13:54
Context
2023-04-18
15:21
Ensure that the VACUUM command is not confused by alternative encodings when it is the first command run on new database connection. Forum post 09503b4d33. Problem introduced by check-in [a02da71f3a80dd8e]. (check-in: 8b0fe63f user: drh tags: trunk)
14:17
Another fix to the indexed expressions in aggregate queries with GROUP BY enhancement of ticket 99378177930f87bd and implemented by check-in b9190d3da70c4171 to address a problem described by forum post f34e32d120. (check-in: c37e7610 user: drh tags: branch-3.41)
14:13
Another fix to the indexed expressions in aggregate queries with GROUP BY enhancement of ticket [99378177930f87bd] and implemented by check-in [b9190d3da70c4171] to address a problem described by forum post f34e32d120, (check-in: 5acc3ef8 user: drh tags: trunk)
11:35
Add the --fullsync option to speedtest1. (check-in: 960fe54c user: drh tags: trunk)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/select.c.

︙︙
6444
6445
6446
6447
6448
6449
6450
6451
6452
6453

6454
6455

6456

6457
6458
6459
6460
6461
6462
6463

  NameContext *pNC        /* Name context used to resolve agg-func args */
){
  assert( pAggInfo->iFirstReg==0 );
  assert( pSelect!=0 );
  assert( pSelect->pGroupBy!=0 );
  pAggInfo->nColumn = pAggInfo->nAccumulator;
  if( ALWAYS(pAggInfo->nSortingColumn>0) ){
    if( pAggInfo->nColumn==0 ){
      pAggInfo->nSortingColumn = pSelect->pGroupBy->nExpr;
    }else{

      pAggInfo->nSortingColumn =
        pAggInfo->aCol[pAggInfo->nColumn-1].iSorterColumn+1;

    }

  }
  analyzeAggFuncArgs(pAggInfo, pNC);
#if TREETRACE_ENABLED
  if( sqlite3TreeTrace & 0x20 ){
    IndexedExpr *pIEpr;
    TREETRACE(0x20, pParse, pSelect,
        ("AggInfo (possibly) adjusted for Indexed Exprs\n"));








<
|
<
>
|
|
>

>








6444
6445
6446
6447
6448
6449
6450

6451

6452
6453
6454
6455
6456
6457
6458
6459
6460
6461
6462
6463
6464

  NameContext *pNC        /* Name context used to resolve agg-func args */
){
  assert( pAggInfo->iFirstReg==0 );
  assert( pSelect!=0 );
  assert( pSelect->pGroupBy!=0 );
  pAggInfo->nColumn = pAggInfo->nAccumulator;
  if( ALWAYS(pAggInfo->nSortingColumn>0) ){

    int mx = pSelect->pGroupBy->nExpr - 1;

    int j, k;
    for(j=0; j<pAggInfo->nColumn; j++){
      k = pAggInfo->aCol[j].iSorterColumn;
      if( k>mx ) mx = k;
    }
    pAggInfo->nSortingColumn = mx+1;
  }
  analyzeAggFuncArgs(pAggInfo, pNC);
#if TREETRACE_ENABLED
  if( sqlite3TreeTrace & 0x20 ){
    IndexedExpr *pIEpr;
    TREETRACE(0x20, pParse, pSelect,
        ("AggInfo (possibly) adjusted for Indexed Exprs\n"));
︙︙

Changes to test/indexexpr1.test.

︙︙
587
588
589
590
591
592
593


594





















595
596

  SELECT b FROM t1;
} 300
do_execsql_test indexexpr1-2140 {
  UPDATE t1 SET b=400 WHERE (SELECT 'y') GLOB "y*";
  SELECT b FROM t1;
} 400


























finish_test








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



587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619

  SELECT b FROM t1;
} 300
do_execsql_test indexexpr1-2140 {
  UPDATE t1 SET b=400 WHERE (SELECT 'y') GLOB "y*";
  SELECT b FROM t1;
} 400

# 2023-04-18 Forum post https://sqlite.org/forum/forumpost/f34e32d120 from
# Alexis King.
#
# This problem originates at check-in b9190d3da70c4171 (2022-11-25).
# A similar problem arose on 2023-03-04 at
# https://sqlite.org/forum/forumpost/a68313d054 and was fixed at
# check-in e06973876993926f.  See the test case tkt-99378-400.
# 
reset_db
do_execsql_test indexexpr1-2200 {
  CREATE TABLE t1(id INTEGER PRIMARY KEY, tag INT);
  INSERT INTO t1 VALUES (0, 7), (1, 8);
  CREATE TABLE t2(type INT, t1_id  INT, value  INT);
  INSERT INTO t2 VALUES (0, 0, 100), (0, 1, 101);
  CREATE INDEX t1x ON t1(-tag);
  SELECT u.tag, v.max_value
    FROM (SELECT tag FROM t1 GROUP BY -tag) u
    JOIN (SELECT t1.tag AS "tag", t2.type AS "type",
                 MAX(t2.value) AS "max_value"
            FROM t1
                 JOIN t2 ON t2.t1_id = t1.id
           GROUP BY t2.type, t1.tag
         ) v ON v.type = 0 AND v.tag = u.tag;
} {7 100 8 101}

finish_test

Changes to test/tkt-99378177930f87bd.test.

︙︙
173
174
175
176
177
178
179


180
181
182
183
184
185
186

                (SELECT sum((SELECT 1 FROM t1 NATURAL RIGHT JOIN t1 WHERE a=a)))) AS xyz
           )
           AND a==2
         );
} {1 2}

# 2023-03-04 https://sqlite.org/forum/forumpost/a68313d054


#
do_execsql_test tkt-99378-400 {
  DROP TABLE t1;
  CREATE TABLE t0(w);
  INSERT INTO t0(w) VALUES(1);
  CREATE TABLE t1(x);
  INSERT INTO t1(x) VALUES(1);








>
>








173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188

                (SELECT sum((SELECT 1 FROM t1 NATURAL RIGHT JOIN t1 WHERE a=a)))) AS xyz
           )
           AND a==2
         );
} {1 2}

# 2023-03-04 https://sqlite.org/forum/forumpost/a68313d054
#
# See also indexexpr1-2200 added on 2023-03-18.
#
do_execsql_test tkt-99378-400 {
  DROP TABLE t1;
  CREATE TABLE t0(w);
  INSERT INTO t0(w) VALUES(1);
  CREATE TABLE t1(x);
  INSERT INTO t1(x) VALUES(1);
︙︙