Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Enhancements to SQL query normalization for UPDATE statements. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
bba975c7af3de9aeb5c62fb8b05d61b9 |
| User & Date: | mistachkin 2019-10-16 17:46:22 |
Context
|
2019-10-17
| ||
| 15:41 | Avoid an infinite loop in fts3/4 incremental-merge in the case where the lowest level in the database contains segments but no data (because there is a delete-marker for each valid entry). Fix for [bf1aab89]. (check-in: 35beaee0 user: dan tags: trunk) | |
|
2019-10-16
| ||
| 17:46 | Enhancements to SQL query normalization for UPDATE statements. (check-in: bba975c7 user: mistachkin tags: trunk) | |
| 14:56 | If an AFTER DELETE trigger fires when a conflict row is deleted by REPLACE conflict resolution, make sure the conflict really has been resolved and that the trigger did not recreate the row before continuing. Ticket [a8a4847a2d96f5de] (check-in: eea1e7aa user: drh tags: trunk) | |
Changes
Changes to src/tokenize.c.
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
...
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
|
int i; /* Next unread byte of zSql[] */ int n; /* length of current token */ int tokenType; /* type of current token */ int prevType = 0; /* Previous non-whitespace token */ int nParen; /* Number of nested levels of parentheses */ int iStartIN; /* Start of RHS of IN operator in z[] */ int nParenAtIN; /* Value of nParent at start of RHS of IN operator */ int j; /* Bytes of normalized SQL generated so far */ sqlite3_str *pStr; /* The normalized SQL string under construction */ db = sqlite3VdbeDb(pVdbe); tokenType = -1; nParen = iStartIN = nParenAtIN = 0; pStr = sqlite3_str_new(db); assert( pStr!=0 ); /* sqlite3_str_new() never returns NULL */ ................................................................................ nParenAtIN = nParen; } sqlite3_str_append(pStr, "(", 1); break; } case TK_RP: { if( iStartIN>0 && nParen==nParenAtIN ){ assert( pStr->nChar>=iStartIN ); pStr->nChar = iStartIN+1; sqlite3_str_append(pStr, "?,?,?", 5); iStartIN = 0; } nParen--; sqlite3_str_append(pStr, ")", 1); break; |
|
|
|
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
...
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
|
int i; /* Next unread byte of zSql[] */ int n; /* length of current token */ int tokenType; /* type of current token */ int prevType = 0; /* Previous non-whitespace token */ int nParen; /* Number of nested levels of parentheses */ int iStartIN; /* Start of RHS of IN operator in z[] */ int nParenAtIN; /* Value of nParent at start of RHS of IN operator */ u32 j; /* Bytes of normalized SQL generated so far */ sqlite3_str *pStr; /* The normalized SQL string under construction */ db = sqlite3VdbeDb(pVdbe); tokenType = -1; nParen = iStartIN = nParenAtIN = 0; pStr = sqlite3_str_new(db); assert( pStr!=0 ); /* sqlite3_str_new() never returns NULL */ ................................................................................ nParenAtIN = nParen; } sqlite3_str_append(pStr, "(", 1); break; } case TK_RP: { if( iStartIN>0 && nParen==nParenAtIN ){ assert( pStr->nChar>=(u32)iStartIN ); pStr->nChar = iStartIN+1; sqlite3_str_append(pStr, "?,?,?", 5); iStartIN = 0; } nParen--; sqlite3_str_append(pStr, ")", 1); break; |
Changes to src/update.c.
285
286
287
288
289
290
291
292
293
294
295
296
297
298
...
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
|
/* Initialize the name-context */ memset(&sNC, 0, sizeof(sNC)); sNC.pParse = pParse; sNC.pSrcList = pTabList; sNC.uNC.pUpsert = pUpsert; sNC.ncFlags = NC_UUpsert; /* Resolve the column names in all the expressions of the ** of the UPDATE statement. Also find the column index ** for each column to be updated in the pChanges array. For each ** column to be updated, make sure we have authorization to change ** that column. */ chngRowid = chngPk = 0; ................................................................................ aRegIdx[nAllIdx] = ++pParse->nMem; /* Register storing the table record */ if( bReplace ){ /* If REPLACE conflict resolution might be invoked, open cursors on all ** indexes in case they are needed to delete records. */ memset(aToOpen, 1, nIdx+1); } /* Begin generating code. */ v = sqlite3GetVdbe(pParse); if( v==0 ) goto update_cleanup; if( pParse->nested==0 ) sqlite3VdbeCountChanges(v); sqlite3BeginWriteOperation(pParse, pTrigger || hasFK, iDb); /* Allocate required registers. */ if( !IsVirtual(pTab) ){ /* For now, regRowSet and aRegIdx[nAllIdx] share the same register. ** If regRowSet turns out to be needed, then aRegIdx[nAllIdx] will be |
>
>
>
>
<
<
<
|
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
...
386
387
388
389
390
391
392
393
394
395
396
397
398
399
|
/* Initialize the name-context */ memset(&sNC, 0, sizeof(sNC)); sNC.pParse = pParse; sNC.pSrcList = pTabList; sNC.uNC.pUpsert = pUpsert; sNC.ncFlags = NC_UUpsert; /* Begin generating code. */ v = sqlite3GetVdbe(pParse); if( v==0 ) goto update_cleanup; /* Resolve the column names in all the expressions of the ** of the UPDATE statement. Also find the column index ** for each column to be updated in the pChanges array. For each ** column to be updated, make sure we have authorization to change ** that column. */ chngRowid = chngPk = 0; ................................................................................ aRegIdx[nAllIdx] = ++pParse->nMem; /* Register storing the table record */ if( bReplace ){ /* If REPLACE conflict resolution might be invoked, open cursors on all ** indexes in case they are needed to delete records. */ memset(aToOpen, 1, nIdx+1); } if( pParse->nested==0 ) sqlite3VdbeCountChanges(v); sqlite3BeginWriteOperation(pParse, pTrigger || hasFK, iDb); /* Allocate required registers. */ if( !IsVirtual(pTab) ){ /* For now, regRowSet and aRegIdx[nAllIdx] share the same register. ** If regRowSet turns out to be needed, then aRegIdx[nAllIdx] will be |
Changes to test/normalize.test.
343 344 345 346 347 348 349 350 351 352 353 354 355 356 |
0x2
{0 {SELECT x FROM t1 WHERE x=?;}}
760
{SELECT x FROM t1 WHERE x IN ([x] IS NOT NULL, NULL, 1, 'a', "b", x'00');}
0x2
{0 {SELECT x FROM t1 WHERE x IN(x IS NOT NULL,?,?,?,b,?);}}
} {
do_test $tnum {
set code [catch {
set STMT [sqlite3_prepare_v3 $DB $sql -1 $flags TAIL]
sqlite3_normalized_sql $STMT
} res]
if {[info exists STMT]} {
|
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 |
0x2
{0 {SELECT x FROM t1 WHERE x=?;}}
760
{SELECT x FROM t1 WHERE x IN ([x] IS NOT NULL, NULL, 1, 'a', "b", x'00');}
0x2
{0 {SELECT x FROM t1 WHERE x IN(x IS NOT NULL,?,?,?,b,?);}}
800
{ATTACH "normalize800.db" AS somefile;}
0x2
{0 {ATTACH"normalize800.db"AS somefile;}}
810
{ATTACH DATABASE "normalize810.db" AS somefile;}
0x2
{0 {ATTACH DATABASE"normalize810.db"AS somefile;}}
900
{INSERT INTO t1 (x) VALUES("sl1"), (1), ("sl2"), ('i');}
0x2
{0 {INSERT INTO t1(x)VALUES(?),(?),(?),(?);}}
910
{UPDATE t1 SET x = "sl1" WHERE x IN (1, "sl2", 'i');}
0x2
{0 {UPDATE t1 SET x=?WHERE x IN(?,?,?);}}
920
{UPDATE t1 SET x = "y" WHERE x IN (1, "sl1", 'i');}
0x2
{0 {UPDATE t1 SET x=y WHERE x IN(?,?,?);}}
930
{DELETE FROM t1 WHERE x IN (1, "sl1", 'i');}
0x2
{0 {DELETE FROM t1 WHERE x IN(?,?,?);}}
} {
do_test $tnum {
set code [catch {
set STMT [sqlite3_prepare_v3 $DB $sql -1 $flags TAIL]
sqlite3_normalized_sql $STMT
} res]
if {[info exists STMT]} {
|