/ Check-in [bba975c7]
Login

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: bba975c7af3de9aeb5c62fb8b05d61b96e4ecd0b030008442bbdd345e1e5f134
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
Hide Diffs Unified Diffs Ignore Whitespace Patch

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]} {