SQLite

Check-in [4ac81fac6c]
Login

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

Overview
Comment:Bring comments on the INSERT code generator up-to-date. Fix the INSERT code generator so that it correctly handles inserts from a SELECT into a virtual table with non-terminal hidden columns.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 4ac81fac6c6302c042be3df493a41630b733fff0
User & Date: drh 2015-04-19 18:32:43.531
Context
2015-04-19
19:21
Fix a potential NULL pointer deference on a corrupt database schema. (check-in: dc61b292d8 user: drh tags: trunk)
18:32
Bring comments on the INSERT code generator up-to-date. Fix the INSERT code generator so that it correctly handles inserts from a SELECT into a virtual table with non-terminal hidden columns. (check-in: 4ac81fac6c user: drh tags: trunk)
2015-04-18
19:20
The sqlite3_trace() callback does not try to expand parameters in statements that have no parameter. (check-in: 917e3c3629 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/insert.c.
338
339
340
341
342
343
344
345
346

347
348
349
350

351
352

353
354
355
356
357
358
359
360
361
362
363
364
365
  int onError,          /* How to handle constraint errors */
  int iDbDest           /* The database of pDest */
);

/*
** This routine is called to handle SQL of the following forms:
**
**    insert into TABLE (IDLIST) values(EXPRLIST)
**    insert into TABLE (IDLIST) select

**
** The IDLIST following the table name is always optional.  If omitted,
** then a list of all columns for the table is substituted.  The IDLIST
** appears in the pColumn parameter.  pColumn is NULL if IDLIST is omitted.

**
** The pList parameter holds EXPRLIST in the first form of the INSERT

** statement above, and pSelect is NULL.  For the second form, pList is
** NULL and pSelect is a pointer to the select statement used to generate
** data for the insert.
**
** The code generated follows one of four templates.  For a simple
** insert with data coming from a VALUES clause, the code executes
** once straight down through.  Pseudo-code follows (we call this
** the "1st template"):
**
**         open write cursor to <table> and its indices
**         put VALUES clause expressions into registers
**         write the resulting record into <table>
**         cleanup







|

>


|
|
>

|
>
|
|
|


|







338
339
340
341
342
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
  int onError,          /* How to handle constraint errors */
  int iDbDest           /* The database of pDest */
);

/*
** This routine is called to handle SQL of the following forms:
**
**    insert into TABLE (IDLIST) values(EXPRLIST),(EXPRLIST),...
**    insert into TABLE (IDLIST) select
**    insert into TABLE (IDLIST) default values
**
** The IDLIST following the table name is always optional.  If omitted,
** then a list of all (non-hidden) columns for the table is substituted.
** The IDLIST appears in the pColumn parameter.  pColumn is NULL if IDLIST
** is omitted.
**
** For the pSelect parameter holds the values to be inserted for the
** first two forms shown above.  A VALUES clause is really just short-hand
** for a SELECT statement that omits the FROM clause and everything else
** that follows.  If the pSelect parameter is NULL, that means that the
** DEFAULT VALUES form of the INSERT statement is intended.
**
** The code generated follows one of four templates.  For a simple
** insert with data coming from a single-row VALUES clause, the code executes
** once straight down through.  Pseudo-code follows (we call this
** the "1st template"):
**
**         open write cursor to <table> and its indices
**         put VALUES clause expressions into registers
**         write the resulting record into <table>
**         cleanup
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
  int addrCont = 0;     /* Top of insert loop. Label "C" in templates 3 and 4 */
  SelectDest dest;      /* Destination for SELECT on rhs of INSERT */
  int iDb;              /* Index of database holding TABLE */
  Db *pDb;              /* The database containing table being inserted into */
  u8 useTempTable = 0;  /* Store SELECT results in intermediate table */
  u8 appendFlag = 0;    /* True if the insert is likely to be an append */
  u8 withoutRowid;      /* 0 for normal table.  1 for WITHOUT ROWID table */
  u8 bIdListInOrder = 1; /* True if IDLIST is in table order */
  ExprList *pList = 0;  /* List of VALUES() to be inserted  */

  /* Register allocations */
  int regFromSelect = 0;/* Base register for data coming from SELECT */
  int regAutoinc = 0;   /* Register holding the AUTOINCREMENT counter */
  int regRowCount = 0;  /* Memory cell used for the row counter */
  int regIns;           /* Block of regs holding rowid+data being inserted */







|







461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
  int addrCont = 0;     /* Top of insert loop. Label "C" in templates 3 and 4 */
  SelectDest dest;      /* Destination for SELECT on rhs of INSERT */
  int iDb;              /* Index of database holding TABLE */
  Db *pDb;              /* The database containing table being inserted into */
  u8 useTempTable = 0;  /* Store SELECT results in intermediate table */
  u8 appendFlag = 0;    /* True if the insert is likely to be an append */
  u8 withoutRowid;      /* 0 for normal table.  1 for WITHOUT ROWID table */
  u8 bIdListInOrder;    /* True if IDLIST is in table order */
  ExprList *pList = 0;  /* List of VALUES() to be inserted  */

  /* Register allocations */
  int regFromSelect = 0;/* Base register for data coming from SELECT */
  int regAutoinc = 0;   /* Register holding the AUTOINCREMENT counter */
  int regRowCount = 0;  /* Memory cell used for the row counter */
  int regIns;           /* Block of regs holding rowid+data being inserted */
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
  db = pParse->db;
  memset(&dest, 0, sizeof(dest));
  if( pParse->nErr || db->mallocFailed ){
    goto insert_cleanup;
  }

  /* If the Select object is really just a simple VALUES() list with a
  ** single row values (the common case) then keep that one row of values
  ** and go ahead and discard the Select object
  */
  if( pSelect && (pSelect->selFlags & SF_Values)!=0 && pSelect->pPrior==0 ){
    pList = pSelect->pEList;
    pSelect->pEList = 0;
    sqlite3SelectDelete(db, pSelect);
    pSelect = 0;
  }







|
|







486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
  db = pParse->db;
  memset(&dest, 0, sizeof(dest));
  if( pParse->nErr || db->mallocFailed ){
    goto insert_cleanup;
  }

  /* If the Select object is really just a simple VALUES() list with a
  ** single row (the common case) then keep that one row of values
  ** and discard the other (unused) parts of the pSelect object
  */
  if( pSelect && (pSelect->selFlags & SF_Values)!=0 && pSelect->pPrior==0 ){
    pList = pSelect->pEList;
    pSelect->pEList = 0;
    sqlite3SelectDelete(db, pSelect);
    pSelect = 0;
  }
592
593
594
595
596
597
598

599
600
601
602
603
604
605
  ** If the table has an INTEGER PRIMARY KEY column and that column
  ** is named in the IDLIST, then record in the ipkColumn variable
  ** the index into IDLIST of the primary key column.  ipkColumn is
  ** the index of the primary key as it appears in IDLIST, not as
  ** is appears in the original table.  (The index of the INTEGER
  ** PRIMARY KEY in the original table is pTab->iPKey.)
  */

  if( pColumn ){
    for(i=0; i<pColumn->nId; i++){
      pColumn->a[i].idx = -1;
    }
    for(i=0; i<pColumn->nId; i++){
      for(j=0; j<pTab->nCol; j++){
        if( sqlite3StrICmp(pColumn->a[i].zName, pTab->aCol[j].zName)==0 ){







>







595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
  ** If the table has an INTEGER PRIMARY KEY column and that column
  ** is named in the IDLIST, then record in the ipkColumn variable
  ** the index into IDLIST of the primary key column.  ipkColumn is
  ** the index of the primary key as it appears in IDLIST, not as
  ** is appears in the original table.  (The index of the INTEGER
  ** PRIMARY KEY in the original table is pTab->iPKey.)
  */
  bIdListInOrder = (pTab->tabFlags & TF_OOOHidden)==0;
  if( pColumn ){
    for(i=0; i<pColumn->nId; i++){
      pColumn->a[i].idx = -1;
    }
    for(i=0; i<pColumn->nId; i++){
      for(j=0; j<pTab->nCol; j++){
        if( sqlite3StrICmp(pColumn->a[i].zName, pTab->aCol[j].zName)==0 ){
627
628
629
630
631
632
633

634
635
636
637
638
639
640
641

  /* Figure out how many columns of data are supplied.  If the data
  ** is coming from a SELECT statement, then generate a co-routine that
  ** produces a single row of the SELECT on each invocation.  The
  ** co-routine is the common header to the 3rd and 4th templates.
  */
  if( pSelect ){

    /* Data is coming from a SELECT.  Generate a co-routine to run the SELECT */
    int regYield;       /* Register holding co-routine entry-point */
    int addrTop;        /* Top of the co-routine */
    int rc;             /* Result code */

    regYield = ++pParse->nMem;
    addrTop = sqlite3VdbeCurrentAddr(v) + 1;
    sqlite3VdbeAddOp3(v, OP_InitCoroutine, regYield, 0, addrTop);







>
|







631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646

  /* Figure out how many columns of data are supplied.  If the data
  ** is coming from a SELECT statement, then generate a co-routine that
  ** produces a single row of the SELECT on each invocation.  The
  ** co-routine is the common header to the 3rd and 4th templates.
  */
  if( pSelect ){
    /* Data is coming from a SELECT or from a multi-row VALUES clause.
    ** Generate a co-routine to run the SELECT. */
    int regYield;       /* Register holding co-routine entry-point */
    int addrTop;        /* Top of the co-routine */
    int rc;             /* Result code */

    regYield = ++pParse->nMem;
    addrTop = sqlite3VdbeCurrentAddr(v) + 1;
    sqlite3VdbeAddOp3(v, OP_InitCoroutine, regYield, 0, addrTop);
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
      sqlite3VdbeAddOp3(v, OP_Insert, srcTab, regRec, regTempRowid);
      sqlite3VdbeAddOp2(v, OP_Goto, 0, addrL);
      sqlite3VdbeJumpHere(v, addrL);
      sqlite3ReleaseTempReg(pParse, regRec);
      sqlite3ReleaseTempReg(pParse, regTempRowid);
    }
  }else{
    /* This is the case if the data for the INSERT is coming from a VALUES
    ** clause
    */
    NameContext sNC;
    memset(&sNC, 0, sizeof(sNC));
    sNC.pParse = pParse;
    srcTab = -1;
    assert( useTempTable==0 );
    nColumn = pList ? pList->nExpr : 0;







|
|







694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
      sqlite3VdbeAddOp3(v, OP_Insert, srcTab, regRec, regTempRowid);
      sqlite3VdbeAddOp2(v, OP_Goto, 0, addrL);
      sqlite3VdbeJumpHere(v, addrL);
      sqlite3ReleaseTempReg(pParse, regRec);
      sqlite3ReleaseTempReg(pParse, regTempRowid);
    }
  }else{
    /* This is the case if the data for the INSERT is coming from a 
    ** single-row VALUES clause
    */
    NameContext sNC;
    memset(&sNC, 0, sizeof(sNC));
    sNC.pParse = pParse;
    srcTab = -1;
    assert( useTempTable==0 );
    nColumn = pList ? pList->nExpr : 0;
Changes to src/sqliteInt.h.
1618
1619
1620
1621
1622
1623
1624






1625
1626
1627
1628
1629
1630
1631

1632
1633
1634
1635
1636
1637
1638
  Trigger *pTrigger;   /* List of triggers stored in pSchema */
  Schema *pSchema;     /* Schema that contains this table */
  Table *pNextZombie;  /* Next on the Parse.pZombieTab list */
};

/*
** Allowed values for Table.tabFlags.






*/
#define TF_Readonly        0x01    /* Read-only system table */
#define TF_Ephemeral       0x02    /* An ephemeral table */
#define TF_HasPrimaryKey   0x04    /* Table has a primary key */
#define TF_Autoincrement   0x08    /* Integer primary key is autoincrement */
#define TF_Virtual         0x10    /* Is a virtual table */
#define TF_WithoutRowid    0x20    /* No rowid used. PRIMARY KEY is the key */



/*
** Test to see whether or not a table is a virtual table.  This is
** done as a macro so that it will be optimized out when virtual
** table support is omitted from the build.
*/







>
>
>
>
>
>







>







1618
1619
1620
1621
1622
1623
1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
1640
1641
1642
1643
1644
1645
  Trigger *pTrigger;   /* List of triggers stored in pSchema */
  Schema *pSchema;     /* Schema that contains this table */
  Table *pNextZombie;  /* Next on the Parse.pZombieTab list */
};

/*
** Allowed values for Table.tabFlags.
**
** TF_OOOHidden applies to virtual tables that have hidden columns that are
** followed by non-hidden columns.  Example:  "CREATE VIRTUAL TABLE x USING
** vtab1(a HIDDEN, b);".  Since "b" is a non-hidden column but "a" is hidden,
** the TF_OOOHidden attribute would apply in this case.  Such tables require
** special handling during INSERT processing.
*/
#define TF_Readonly        0x01    /* Read-only system table */
#define TF_Ephemeral       0x02    /* An ephemeral table */
#define TF_HasPrimaryKey   0x04    /* Table has a primary key */
#define TF_Autoincrement   0x08    /* Integer primary key is autoincrement */
#define TF_Virtual         0x10    /* Is a virtual table */
#define TF_WithoutRowid    0x20    /* No rowid used. PRIMARY KEY is the key */
#define TF_OOOHidden       0x40    /* Out-of-Order hidden columns */


/*
** Test to see whether or not a table is a virtual table.  This is
** done as a macro so that it will be optimized out when virtual
** table support is omitted from the build.
*/
Changes to src/vtab.c.
555
556
557
558
559
560
561

562
563
564
565
566
567
568
569
570
571
572
573
574



575
576
577
578
579
580
581
    if( sCtx.bDeclared==0 ){
      const char *zFormat = "vtable constructor did not declare schema: %s";
      *pzErr = sqlite3MPrintf(db, zFormat, pTab->zName);
      sqlite3VtabUnlock(pVTable);
      rc = SQLITE_ERROR;
    }else{
      int iCol;

      /* If everything went according to plan, link the new VTable structure
      ** into the linked list headed by pTab->pVTable. Then loop through the 
      ** columns of the table to see if any of them contain the token "hidden".
      ** If so, set the Column COLFLAG_HIDDEN flag and remove the token from
      ** the type string.  */
      pVTable->pNext = pTab->pVTable;
      pTab->pVTable = pVTable;

      for(iCol=0; iCol<pTab->nCol; iCol++){
        char *zType = pTab->aCol[iCol].zType;
        int nType;
        int i = 0;
        if( !zType ) continue;



        nType = sqlite3Strlen30(zType);
        if( sqlite3StrNICmp("hidden", zType, 6)||(zType[6] && zType[6]!=' ') ){
          for(i=0; i<nType; i++){
            if( (0==sqlite3StrNICmp(" hidden", &zType[i], 7))
             && (zType[i+7]=='\0' || zType[i+7]==' ')
            ){
              i++;







>












|
>
>
>







555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
    if( sCtx.bDeclared==0 ){
      const char *zFormat = "vtable constructor did not declare schema: %s";
      *pzErr = sqlite3MPrintf(db, zFormat, pTab->zName);
      sqlite3VtabUnlock(pVTable);
      rc = SQLITE_ERROR;
    }else{
      int iCol;
      u8 oooHidden = 0;
      /* If everything went according to plan, link the new VTable structure
      ** into the linked list headed by pTab->pVTable. Then loop through the 
      ** columns of the table to see if any of them contain the token "hidden".
      ** If so, set the Column COLFLAG_HIDDEN flag and remove the token from
      ** the type string.  */
      pVTable->pNext = pTab->pVTable;
      pTab->pVTable = pVTable;

      for(iCol=0; iCol<pTab->nCol; iCol++){
        char *zType = pTab->aCol[iCol].zType;
        int nType;
        int i = 0;
        if( !zType ){
          pTab->tabFlags |= oooHidden;
          continue;
        }
        nType = sqlite3Strlen30(zType);
        if( sqlite3StrNICmp("hidden", zType, 6)||(zType[6] && zType[6]!=' ') ){
          for(i=0; i<nType; i++){
            if( (0==sqlite3StrNICmp(" hidden", &zType[i], 7))
             && (zType[i+7]=='\0' || zType[i+7]==' ')
            ){
              i++;
590
591
592
593
594
595
596



597
598
599
600
601
602
603
            zType[j] = zType[j+nDel];
          }
          if( zType[i]=='\0' && i>0 ){
            assert(zType[i-1]==' ');
            zType[i-1] = '\0';
          }
          pTab->aCol[iCol].colFlags |= COLFLAG_HIDDEN;



        }
      }
    }
  }

  sqlite3DbFree(db, zModuleName);
  return rc;







>
>
>







594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
            zType[j] = zType[j+nDel];
          }
          if( zType[i]=='\0' && i>0 ){
            assert(zType[i-1]==' ');
            zType[i-1] = '\0';
          }
          pTab->aCol[iCol].colFlags |= COLFLAG_HIDDEN;
          oooHidden = TF_OOOHidden;
        }else{
          pTab->tabFlags |= oooHidden;
        }
      }
    }
  }

  sqlite3DbFree(db, zModuleName);
  return rc;
Changes to test/vtabA.test.
70
71
72
73
74
75
76











77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
} {{value a} {} {value c}}

do_test vtabA-1.6 {
  execsql {
    SELECT * FROM t1e;
  }
} {{value a} {value c}}












# Test that the expansion of a '*' expression in the result set of
# a SELECT does not include the hidden column.
#
do_test vtabA-1.7 {
  execsql {
    INSERT INTO t1e SELECT * FROM t1e;
  }
} {}
do_test vtabA-1.8 {
  execsql {
    SELECT * FROM t1e;
  }
} {{value a} {value c} {value a} {value c}}

# Test that the declaration type of the hidden column does not include
# the token "HIDDEN".
#
do_test vtabA-1.9 {
  get_decltype t1e b
} {VARCHAR}
do_test vtabA-1.10 {
  get_collist t1e
} {a c}

#----------------------------------------------------------------------
# These tests vtabA-2.* concentrate on testing that the HIDDEN token
# is detected and handled correctly in various declarations.
#







>
>
>
>
>
>
>
>
>
>
>




|




|

|

|




|


|







70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
} {{value a} {} {value c}}

do_test vtabA-1.6 {
  execsql {
    SELECT * FROM t1e;
  }
} {{value a} {value c}}
do_execsql_test vtabA-1.7 {
  DELETE FROM t1e;
  INSERT INTO t1e SELECT 'abc','def';
} {}
do_execsql_test vtabA-1.8 {
  INSERT INTO t1e VALUES('ghi','jkl'),('mno','pqr'),('stu','vwx');
} {}
do_execsql_test vtabA-1.9 {
  SELECT a,b,c, '|' FROM t1e ORDER BY 1;
} {abc {} def | ghi {} jkl | mno {} pqr | stu {} vwx |}


# Test that the expansion of a '*' expression in the result set of
# a SELECT does not include the hidden column.
#
do_test vtabA-1.20 {
  execsql {
    INSERT INTO t1e SELECT * FROM t1e;
  }
} {}
do_test vtabA-1.21 {
  execsql {
    SELECT * FROM t1e ORDER BY 1;
  }
} {abc def abc def ghi jkl ghi jkl mno pqr mno pqr stu vwx stu vwx}

# Test that the declaration type of the hidden column does not include
# the token "HIDDEN".
#
do_test vtabA-1.22 {
  get_decltype t1e b
} {VARCHAR}
do_test vtabA-1.23 {
  get_collist t1e
} {a c}

#----------------------------------------------------------------------
# These tests vtabA-2.* concentrate on testing that the HIDDEN token
# is detected and handled correctly in various declarations.
#