/ Check-in [ffc23409]
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:Clear the column cache before populating aggregate accumulator registers. Fix fo r [883034dcb5].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: ffc23409c7fb45dc5a8722fad26e26d207bb3213
User & Date: dan 2010-03-31 15:02:56
Context
2010-03-31
17:47
Clarification of the implementation of SQLITE_STMTSTATUS_FULLSCAN_STEP. No logical code changes - just commenting and layout changes to improve readability. check-in: 06b9ca32 user: drh tags: trunk
15:02
Clear the column cache before populating aggregate accumulator registers. Fix fo r [883034dcb5]. check-in: ffc23409 user: dan tags: trunk
13:57
Documentation enhancements for the sqlite3_log() interface. No functional changes to code. check-in: ba13a111 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/select.c.

3494
3495
3496
3497
3498
3499
3500












3501
3502
3503
3504
3505
3506
3507
    sqlite3ExprCacheAffinityChange(pParse, regAgg, nArg);
    sqlite3ReleaseTempRange(pParse, regAgg, nArg);
    if( addrNext ){
      sqlite3VdbeResolveLabel(v, addrNext);
      sqlite3ExprCacheClear(pParse);
    }
  }












  for(i=0, pC=pAggInfo->aCol; i<pAggInfo->nAccumulator; i++, pC++){
    sqlite3ExprCode(pParse, pC->pExpr, pC->iMem);
  }
  pAggInfo->directMode = 0;
  sqlite3ExprCacheClear(pParse);
}








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







3494
3495
3496
3497
3498
3499
3500
3501
3502
3503
3504
3505
3506
3507
3508
3509
3510
3511
3512
3513
3514
3515
3516
3517
3518
3519
    sqlite3ExprCacheAffinityChange(pParse, regAgg, nArg);
    sqlite3ReleaseTempRange(pParse, regAgg, nArg);
    if( addrNext ){
      sqlite3VdbeResolveLabel(v, addrNext);
      sqlite3ExprCacheClear(pParse);
    }
  }

  /* Before populating the accumulator registers, clear the column cache.
  ** Otherwise, if any of the required column values are already present 
  ** in registers, sqlite3ExprCode() may use OP_SCopy to copy the value
  ** to pC->iMem. But by the time the value is used, the original register
  ** may have been used, invalidating the underlying buffer holding the
  ** text or blob value. See ticket [883034dcb5].
  **
  ** Another solution would be to change the OP_SCopy used to copy cached
  ** values to an OP_Copy.
  */
  sqlite3ExprCacheClear(pParse);
  for(i=0, pC=pAggInfo->aCol; i<pAggInfo->nAccumulator; i++, pC++){
    sqlite3ExprCode(pParse, pC->pExpr, pC->iMem);
  }
  pAggInfo->directMode = 0;
  sqlite3ExprCacheClear(pParse);
}

Changes to test/selectC.test.

161
162
163
164
165
166
167
168












































169
        SELECT a FROM t21a WHERE a>new.x UNION ALL
        SELECT b FROM t21a WHERE b>new.x ORDER BY 1 LIMIT 2;
      END;
      INSERT INTO t21b VALUES(6);
    }
  } {1 {no such column: new.x}}
}













































finish_test








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

161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
        SELECT a FROM t21a WHERE a>new.x UNION ALL
        SELECT b FROM t21a WHERE b>new.x ORDER BY 1 LIMIT 2;
      END;
      INSERT INTO t21b VALUES(6);
    }
  } {1 {no such column: new.x}}
}

# Check that ticket [883034dcb5] is fixed.
#
do_test selectC-3.1 {
  execsql {
    CREATE TABLE person (
        org_id          TEXT NOT NULL,
        nickname        TEXT NOT NULL,
        license         TEXT,
        CONSTRAINT person_pk PRIMARY KEY (org_id, nickname),
        CONSTRAINT person_license_uk UNIQUE (license)
    );
    INSERT INTO person VALUES('meyers', 'jack', '2GAT123');
    INSERT INTO person VALUES('meyers', 'hill', 'V345FMP');
    INSERT INTO person VALUES('meyers', 'jim', '2GAT138');
    INSERT INTO person VALUES('smith', 'maggy', '');
    INSERT INTO person VALUES('smith', 'jose', 'JJZ109');
    INSERT INTO person VALUES('smith', 'jack', 'THX138');
    INSERT INTO person VALUES('lakeside', 'dave', '953OKG');
    INSERT INTO person VALUES('lakeside', 'amy', NULL);
    INSERT INTO person VALUES('lake-apts', 'tom', NULL);
    INSERT INTO person VALUES('acorn', 'hideo', 'CQB421');
    
    SELECT 
      org_id, 
      count((NOT (org_id IS NULL)) AND (NOT (nickname IS NULL)))
    FROM person 
    WHERE (CASE WHEN license != '' THEN 1 ELSE 0 END)
    GROUP BY 1;
  }
} {acorn 1 lakeside 1 meyers 3 smith 2}
do_test selectC-3.2 {
  execsql {
    CREATE TABLE t2(a PRIMARY KEY, b);
    INSERT INTO t2 VALUES('abc', 'xxx');
    INSERT INTO t2 VALUES('def', 'yyy');
    SELECT a, max(b || a) FROM t2 WHERE (b||b||b)!='value' GROUP BY a;
  }
} {abc xxxabc def yyydef}
do_test selectC-3.3 {
  execsql {
    SELECT b, max(a || b) FROM t2 WHERE (b||b||b)!='value' GROUP BY a;
  }
} {xxx abcxxx yyy defyyy}

finish_test