Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Fix a weird corner case in aggregate function processing that results from the recent addition of support for index expressions on aggregate queries. Forum post bad532820c. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
c34fd9fe1b76e0a5943f014f46141cbe |
| User & Date: | drh 2023-03-28 16:02:28 |
Context
|
2023-03-29
| ||
| 11:36 | Enhance PRAGMA integrity_check so that it can detect that a NOT NULL column contains a NaN value and report that as an error. dbsqlfuzz f144b642fe6f1a1c196f258ac6e60118a0cb59b2. (check-in: 7638d975 user: drh tags: trunk) | |
| 00:05 | Modify the OP_IsType opcode so that it does not need to distinguish between NaN and other floating point values. Later: This branch causes an unnecessary slowdown of PRAGMA integrity_check. Check-in [7638d9755dc90fd3] does a better job of detecting when a NaN value occurs in a NOT NULL column. (Closed-Leaf check-in: 242cb36c user: drh tags: istype-opcode-refactor) | |
|
2023-03-28
| ||
| 16:13 | Fix a weird corner case in aggregate function processing that results from the recent addition of support for index expressions on aggregate queries. (check-in: 36fd948e user: drh tags: branch-3.41) | |
| 16:02 | Fix a weird corner case in aggregate function processing that results from the recent addition of support for index expressions on aggregate queries. Forum post bad532820c. (check-in: c34fd9fe user: drh tags: trunk) | |
| 11:18 | Fix multiple problems with RETURNING on a DML statement against a view, all inspired by forum post dc3b92cfa0. (1) Do not allow a RETURNING clause to trick the code generator into thinking that the view being updated has an INSTEAD OF trigger. (2) Generate all result columns for a view in a DML statement. (3) The automatic covering index for a view should cover all result columns of the view. (check-in: c8bedef0 user: drh tags: trunk) | |
Changes
Changes to src/expr.c.
| ︙ | ︙ | |||
6282 6283 6284 6285 6286 6287 6288 6289 |
if( ALWAYS(!ExprHasProperty(pExpr, EP_TokenOnly|EP_Reduced))
&& pExpr->pAggInfo!=0
){
AggInfo *pAggInfo = pExpr->pAggInfo;
int iAgg = pExpr->iAgg;
Parse *pParse = pWalker->pParse;
sqlite3 *db = pParse->db;
if( pExpr->op!=TK_AGG_FUNCTION ){
| > | | > | | > | 6282 6283 6284 6285 6286 6287 6288 6289 6290 6291 6292 6293 6294 6295 6296 6297 6298 6299 6300 6301 6302 6303 6304 6305 6306 6307 6308 6309 6310 6311 |
if( ALWAYS(!ExprHasProperty(pExpr, EP_TokenOnly|EP_Reduced))
&& pExpr->pAggInfo!=0
){
AggInfo *pAggInfo = pExpr->pAggInfo;
int iAgg = pExpr->iAgg;
Parse *pParse = pWalker->pParse;
sqlite3 *db = pParse->db;
assert( iAgg>=0 );
if( pExpr->op!=TK_AGG_FUNCTION ){
if( iAgg<pAggInfo->nColumn
&& pAggInfo->aCol[iAgg].pCExpr==pExpr
){
pExpr = sqlite3ExprDup(db, pExpr, 0);
if( pExpr ){
pAggInfo->aCol[iAgg].pCExpr = pExpr;
sqlite3ExprDeferredDelete(pParse, pExpr);
}
}
}else{
assert( pExpr->op==TK_AGG_FUNCTION );
if( ALWAYS(iAgg<pAggInfo->nFunc)
&& pAggInfo->aFunc[iAgg].pFExpr==pExpr
){
pExpr = sqlite3ExprDup(db, pExpr, 0);
if( pExpr ){
pAggInfo->aFunc[iAgg].pFExpr = pExpr;
sqlite3ExprDeferredDelete(pParse, pExpr);
}
}
}
|
| ︙ | ︙ |
Changes to test/window1.test.
| ︙ | ︙ | |||
2272 2273 2274 2275 2276 2277 2278 2279 2280 |
do_catchsql_test 73.6 {
UPDATE t2 SET c=99 WHERE b=4 RETURNING *;
} {1 {cannot modify t2 because it is a view}}
do_catchsql_test 73.7 {
UPDATE t2 SET c=nth_value(15,2) OVER() FROM (SELECT * FROM t1) WHERE b=4 RETURNING *;
} {1 {cannot modify t2 because it is a view}}
finish_test
| > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 2272 2273 2274 2275 2276 2277 2278 2279 2280 2281 2282 2283 2284 2285 2286 2287 2288 2289 2290 2291 2292 2293 2294 2295 2296 2297 2298 2299 2300 2301 2302 2303 2304 2305 2306 2307 2308 2309 2310 2311 2312 2313 2314 2315 2316 2317 2318 2319 |
do_catchsql_test 73.6 {
UPDATE t2 SET c=99 WHERE b=4 RETURNING *;
} {1 {cannot modify t2 because it is a view}}
do_catchsql_test 73.7 {
UPDATE t2 SET c=nth_value(15,2) OVER() FROM (SELECT * FROM t1) WHERE b=4 RETURNING *;
} {1 {cannot modify t2 because it is a view}}
# 2023-03-28 https://sqlite.org/forum/forumpost/bad532820c
#
reset_db
do_execsql_test 74.0 {
CREATE TABLE t1 (a INT, b INT);
CREATE TABLE t2 (c INT, d INT);
CREATE INDEX idx ON t1(abs(a));
INSERT INTO t1 VALUES(1,2),(3,4);
INSERT INTO t2 VALUES(5,6),(7,8);
}
do_execsql_test 74.1 {
SELECT (
SELECT count( a ) FROM t2 LIMIT 1
)
FROM t1;
} {2} ;# Verified using PG 14.2
do_execsql_test 74.2 {
SELECT (
SELECT count( a+c ) FROM t2 LIMIT 1
)
FROM t1;
} {2 2} ;# verified on PG 14.2. Crashes PG 9.6!
do_execsql_test 74.3 {
SELECT (
SELECT count( ( SELECT(sum(0) OVER(ORDER BY c, abs(a))) ) )
FROM t2 GROUP BY c LIMIT 1
)
FROM t1;
} {1 1} ;# verified on PG 14.2
do_execsql_test 74.4 {
/* Original test case reported in https://sqlite.org/forum/forumpost/bad532820c
CREATE TABLE v0 (c1);
CREATE INDEX i ON v0 (c1, c1=1);
SELECT 0 FROM v0 AS a1
WHERE (SELECT count((SELECT(sum(0) OVER(PARTITION BY(c1), (a1.c1=1) ))))
FROM v0
GROUP BY hex(0))
AND a1.c1=0;
} {}
finish_test
|