/ Check-in [f5fc42e9]
Login

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

Overview
Comment:Do not attempt to omit unused columns of a view in an instead-of trigger since sometimes those columns can be used in ways that we do not expect. Ticket #3055. (CVS 5012)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:f5fc42e96d36b78797d7fa10b01d22b8501112b1
User & Date: drh 2008-04-15 14:36:42
Context
2008-04-15
14:37
Increment the version number. (CVS 5013) check-in: a12fa025 user: drh tags: trunk
14:36
Do not attempt to omit unused columns of a view in an instead-of trigger since sometimes those columns can be used in ways that we do not expect. Ticket #3055. (CVS 5012) check-in: f5fc42e9 user: drh tags: trunk
12:14
Fix a code generator bug caused by the new CSE optimization. Add test cases to prevent a recurrence. (CVS 5011) check-in: d04246a4 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/delete.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
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
...
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** in order to generate code for DELETE FROM statements.
**
** $Id: delete.c,v 1.167 2008/04/11 19:18:25 drh Exp $
*/
#include "sqliteInt.h"

/*
** Look up every table that is named in pSrc.  If any table is not found,
** add an error message to pParse->zErrMsg and return NULL.  If all tables
** are found, return a pointer to the last table.
................................................................................
** pWhere argument is an optional WHERE clause that restricts the
** set of rows in the view that are to be added to the ephemeral table.
*/
void sqlite3MaterializeView(
  Parse *pParse,       /* Parsing context */
  Select *pView,       /* View definition */
  Expr *pWhere,        /* Optional WHERE clause to be added */
  u32 col_mask,        /* Render only the columns in this mask. */
  int iCur             /* Cursor number for ephemerial table */
){
  SelectDest dest;
  Select *pDup;
  sqlite3 *db = pParse->db;

  pDup = sqlite3SelectDup(db, pView);
................................................................................
  if( pWhere ){
    SrcList *pFrom;
    
    pWhere = sqlite3ExprDup(db, pWhere);
    pFrom = sqlite3SrcListAppendFromTerm(pParse, 0, 0, 0, 0, pDup, 0, 0);
    pDup = sqlite3SelectNew(pParse, 0, pFrom, pWhere, 0, 0, 0, 0, 0, 0);
  }
  sqlite3SelectMask(pParse, pDup, col_mask);
  sqlite3SelectDestInit(&dest, SRT_EphemTab, iCur);
  sqlite3Select(pParse, pDup, &dest, 0, 0, 0, 0);
  sqlite3SelectDelete(pDup);
}
#endif /* !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER) */


................................................................................
    sqlite3VdbeJumpHere(v, iGoto);
  }

  /* If we are trying to delete from a view, realize that view into
  ** a ephemeral table.
  */
  if( isView ){
    sqlite3MaterializeView(pParse, pTab->pSelect, pWhere, old_col_mask, iCur);
  }

  /* Resolve the column names in the WHERE clause.
  */
  memset(&sNC, 0, sizeof(sNC));
  sNC.pParse = pParse;
  sNC.pSrcList = pTabList;







|







 







<







 







<







 







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
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
...
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** in order to generate code for DELETE FROM statements.
**
** $Id: delete.c,v 1.168 2008/04/15 14:36:42 drh Exp $
*/
#include "sqliteInt.h"

/*
** Look up every table that is named in pSrc.  If any table is not found,
** add an error message to pParse->zErrMsg and return NULL.  If all tables
** are found, return a pointer to the last table.
................................................................................
** pWhere argument is an optional WHERE clause that restricts the
** set of rows in the view that are to be added to the ephemeral table.
*/
void sqlite3MaterializeView(
  Parse *pParse,       /* Parsing context */
  Select *pView,       /* View definition */
  Expr *pWhere,        /* Optional WHERE clause to be added */

  int iCur             /* Cursor number for ephemerial table */
){
  SelectDest dest;
  Select *pDup;
  sqlite3 *db = pParse->db;

  pDup = sqlite3SelectDup(db, pView);
................................................................................
  if( pWhere ){
    SrcList *pFrom;
    
    pWhere = sqlite3ExprDup(db, pWhere);
    pFrom = sqlite3SrcListAppendFromTerm(pParse, 0, 0, 0, 0, pDup, 0, 0);
    pDup = sqlite3SelectNew(pParse, 0, pFrom, pWhere, 0, 0, 0, 0, 0, 0);
  }

  sqlite3SelectDestInit(&dest, SRT_EphemTab, iCur);
  sqlite3Select(pParse, pDup, &dest, 0, 0, 0, 0);
  sqlite3SelectDelete(pDup);
}
#endif /* !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER) */


................................................................................
    sqlite3VdbeJumpHere(v, iGoto);
  }

  /* If we are trying to delete from a view, realize that view into
  ** a ephemeral table.
  */
  if( isView ){
    sqlite3MaterializeView(pParse, pTab->pSelect, pWhere, iCur);
  }

  /* Resolve the column names in the WHERE clause.
  */
  memset(&sNC, 0, sizeof(sNC));
  sNC.pParse = pParse;
  sNC.pSrcList = pTabList;

Changes to src/sqliteInt.h.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
....
1931
1932
1933
1934
1935
1936
1937
1938
1939
1940
1941
1942
1943
1944
1945
....
1951
1952
1953
1954
1955
1956
1957
1958
1959
1960
1961
1962
1963
1964
1965
1966
1967
1968
1969
1970
1971
1972
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.692 2008/04/15 12:14:22 drh Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** Include the configuration header output by 'configure' if it was run
** (otherwise we get an empty default).
................................................................................
#else
# define sqlite3SafetyOn(A) 0
# define sqlite3SafetyOff(A) 0
#endif
int sqlite3SafetyCheckOk(sqlite3*);
int sqlite3SafetyCheckSickOrOk(sqlite3*);
void sqlite3ChangeCookie(Parse*, int);
void sqlite3MaterializeView(Parse*, Select*, Expr*, u32, int);

#ifndef SQLITE_OMIT_TRIGGER
  void sqlite3BeginTrigger(Parse*, Token*,Token*,int,int,IdList*,SrcList*,
                           Expr*,int, int);
  void sqlite3FinishTrigger(Parse*, TriggerStep*, Token*);
  void sqlite3DropTrigger(Parse*, SrcList*, int);
  void sqlite3DropTriggerPtr(Parse*, Trigger*);
................................................................................
  TriggerStep *sqlite3TriggerSelectStep(sqlite3*,Select*);
  TriggerStep *sqlite3TriggerInsertStep(sqlite3*,Token*, IdList*,
                                        ExprList*,Select*,int);
  TriggerStep *sqlite3TriggerUpdateStep(sqlite3*,Token*,ExprList*, Expr*, int);
  TriggerStep *sqlite3TriggerDeleteStep(sqlite3*,Token*, Expr*);
  void sqlite3DeleteTrigger(Trigger*);
  void sqlite3UnlinkAndDeleteTrigger(sqlite3*,int,const char*);
  void sqlite3SelectMask(Parse *, Select *, u32);
#else
# define sqlite3TriggersExist(A,B,C,D,E,F) 0
# define sqlite3DeleteTrigger(A)
# define sqlite3DropTriggerPtr(A,B)
# define sqlite3UnlinkAndDeleteTrigger(A,B,C)
# define sqlite3CodeRowTrigger(A,B,C,D,E,F,G,H,I,J,K) 0
# define sqlite3SelectMask(A, B, C)
#endif

int sqlite3JoinType(Parse*, Token*, Token*, Token*);
void sqlite3CreateForeignKey(Parse*, ExprList*, Token*, ExprList*, int);
void sqlite3DeferForeignKey(Parse*, int);
#ifndef SQLITE_OMIT_AUTHORIZATION
  void sqlite3AuthRead(Parse*,Expr*,Schema*,SrcList*);







|







 







|







 







<






<







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
....
1931
1932
1933
1934
1935
1936
1937
1938
1939
1940
1941
1942
1943
1944
1945
....
1951
1952
1953
1954
1955
1956
1957

1958
1959
1960
1961
1962
1963

1964
1965
1966
1967
1968
1969
1970
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.693 2008/04/15 14:36:42 drh Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** Include the configuration header output by 'configure' if it was run
** (otherwise we get an empty default).
................................................................................
#else
# define sqlite3SafetyOn(A) 0
# define sqlite3SafetyOff(A) 0
#endif
int sqlite3SafetyCheckOk(sqlite3*);
int sqlite3SafetyCheckSickOrOk(sqlite3*);
void sqlite3ChangeCookie(Parse*, int);
void sqlite3MaterializeView(Parse*, Select*, Expr*, int);

#ifndef SQLITE_OMIT_TRIGGER
  void sqlite3BeginTrigger(Parse*, Token*,Token*,int,int,IdList*,SrcList*,
                           Expr*,int, int);
  void sqlite3FinishTrigger(Parse*, TriggerStep*, Token*);
  void sqlite3DropTrigger(Parse*, SrcList*, int);
  void sqlite3DropTriggerPtr(Parse*, Trigger*);
................................................................................
  TriggerStep *sqlite3TriggerSelectStep(sqlite3*,Select*);
  TriggerStep *sqlite3TriggerInsertStep(sqlite3*,Token*, IdList*,
                                        ExprList*,Select*,int);
  TriggerStep *sqlite3TriggerUpdateStep(sqlite3*,Token*,ExprList*, Expr*, int);
  TriggerStep *sqlite3TriggerDeleteStep(sqlite3*,Token*, Expr*);
  void sqlite3DeleteTrigger(Trigger*);
  void sqlite3UnlinkAndDeleteTrigger(sqlite3*,int,const char*);

#else
# define sqlite3TriggersExist(A,B,C,D,E,F) 0
# define sqlite3DeleteTrigger(A)
# define sqlite3DropTriggerPtr(A,B)
# define sqlite3UnlinkAndDeleteTrigger(A,B,C)
# define sqlite3CodeRowTrigger(A,B,C,D,E,F,G,H,I,J,K) 0

#endif

int sqlite3JoinType(Parse*, Token*, Token*, Token*);
void sqlite3CreateForeignKey(Parse*, ExprList*, Token*, ExprList*, int);
void sqlite3DeferForeignKey(Parse*, int);
#ifndef SQLITE_OMIT_AUTHORIZATION
  void sqlite3AuthRead(Parse*,Expr*,Schema*,SrcList*);

Changes to src/update.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle UPDATE statements.
**
** $Id: update.c,v 1.176 2008/04/10 13:33:18 drh Exp $
*/
#include "sqliteInt.h"

#ifndef SQLITE_OMIT_VIRTUALTABLE
/* Forward declaration */
static void updateVirtualTable(
  Parse *pParse,       /* The parsing context */
................................................................................
    sqlite3VdbeJumpHere(v, iGoto);
  }

  /* If we are trying to update a view, realize that view into
  ** a ephemeral table.
  */
  if( isView ){
    sqlite3MaterializeView(pParse, pTab->pSelect, pWhere,
                           old_col_mask|new_col_mask, iCur);
  }

  /* Resolve the column names in all the expressions in the
  ** WHERE clause.
  */
  if( sqlite3ExprResolveNames(&sNC, pWhere) ){
    goto update_cleanup;







|







 







|
<







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
325
326
327
328
329
330
331
332

333
334
335
336
337
338
339
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle UPDATE statements.
**
** $Id: update.c,v 1.177 2008/04/15 14:36:42 drh Exp $
*/
#include "sqliteInt.h"

#ifndef SQLITE_OMIT_VIRTUALTABLE
/* Forward declaration */
static void updateVirtualTable(
  Parse *pParse,       /* The parsing context */
................................................................................
    sqlite3VdbeJumpHere(v, iGoto);
  }

  /* If we are trying to update a view, realize that view into
  ** a ephemeral table.
  */
  if( isView ){
    sqlite3MaterializeView(pParse, pTab->pSelect, pWhere, iCur);

  }

  /* Resolve the column names in all the expressions in the
  ** WHERE clause.
  */
  if( sqlite3ExprResolveNames(&sNC, pWhere) ){
    goto update_cleanup;

Changes to test/auth.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
2244
2245
2246
2247
2248
2249
2250

2251
2252
2253
2254
2255
2256
2257
2258
2259
2260
....
2268
2269
2270
2271
2272
2273
2274

2275
2276
2277
2278
2279
2280
2281
2282
2283
2284
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is testing the sqlite3_set_authorizer() API
# and related functionality.
#
# $Id: auth.test,v 1.41 2008/02/12 16:52:14 drh Exp $
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# disable this test if the SQLITE_OMIT_AUTHORIZATION macro is
# defined during compilation.
................................................................................
  }
  set authargs
} [list \
  SQLITE_UPDATE v1     x  main {} \
  SQLITE_INSERT v1chng {} main r2 \
  SQLITE_READ   v1     x  main r2 \
  SQLITE_READ   v1     x  main r2 \

  SQLITE_READ   t2     a  main v1 \
  SQLITE_READ   t2     b  main v1 \
  SQLITE_SELECT {}     {} {}   v1 \
  SQLITE_SELECT {}     {} {}   v1 \
  SQLITE_READ   v1     x  main v1 \
]
do_test auth-4.4 {
  execsql {
    CREATE TRIGGER r3 INSTEAD OF DELETE ON v1 BEGIN
      INSERT INTO v1chng VALUES(OLD.x,NULL);
................................................................................
    DELETE FROM v1 WHERE x=117
  }
  set authargs
} [list \
  SQLITE_DELETE v1     {} main {} \
  SQLITE_INSERT v1chng {} main r3 \
  SQLITE_READ   v1     x  main r3 \

  SQLITE_READ   t2     a  main v1 \
  SQLITE_READ   t2     b  main v1 \
  SQLITE_SELECT {}     {} {}   v1 \
  SQLITE_SELECT {}     {} {}   v1 \
  SQLITE_READ   v1     x  main v1 \
]

} ;# ifcapable view && trigger

# Ticket #1338:  Make sure authentication works in the presence of an AS







|







 







>


<







 







>


<







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
2244
2245
2246
2247
2248
2249
2250
2251
2252
2253

2254
2255
2256
2257
2258
2259
2260
....
2268
2269
2270
2271
2272
2273
2274
2275
2276
2277

2278
2279
2280
2281
2282
2283
2284
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is testing the sqlite3_set_authorizer() API
# and related functionality.
#
# $Id: auth.test,v 1.42 2008/04/15 14:36:42 drh Exp $
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# disable this test if the SQLITE_OMIT_AUTHORIZATION macro is
# defined during compilation.
................................................................................
  }
  set authargs
} [list \
  SQLITE_UPDATE v1     x  main {} \
  SQLITE_INSERT v1chng {} main r2 \
  SQLITE_READ   v1     x  main r2 \
  SQLITE_READ   v1     x  main r2 \
  SQLITE_SELECT {}     {} {}   v1 \
  SQLITE_READ   t2     a  main v1 \
  SQLITE_READ   t2     b  main v1 \

  SQLITE_SELECT {}     {} {}   v1 \
  SQLITE_READ   v1     x  main v1 \
]
do_test auth-4.4 {
  execsql {
    CREATE TRIGGER r3 INSTEAD OF DELETE ON v1 BEGIN
      INSERT INTO v1chng VALUES(OLD.x,NULL);
................................................................................
    DELETE FROM v1 WHERE x=117
  }
  set authargs
} [list \
  SQLITE_DELETE v1     {} main {} \
  SQLITE_INSERT v1chng {} main r3 \
  SQLITE_READ   v1     x  main r3 \
  SQLITE_SELECT {}     {} {}   v1 \
  SQLITE_READ   t2     a  main v1 \
  SQLITE_READ   t2     b  main v1 \

  SQLITE_SELECT {}     {} {}   v1 \
  SQLITE_READ   v1     x  main v1 \
]

} ;# ifcapable view && trigger

# Ticket #1338:  Make sure authentication works in the presence of an AS

Added test/triggerB.test.



























































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
# 2008 April 15
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library. Specifically,
# it tests updating tables with constraints within a trigger.  Ticket #3055.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
ifcapable {!trigger} {
  finish_test
  return
}

# Create test tables with constraints.
#
do_test triggerB-1.1 {
  execsql {
    CREATE TABLE x(x INTEGER PRIMARY KEY, y INT NOT NULL);
    INSERT INTO x(y) VALUES(1);
    INSERT INTO x(y) VALUES(1);
    CREATE TEMP VIEW vx AS SELECT x, y, 0 AS yy FROM x;
    CREATE TEMP TRIGGER tx INSTEAD OF UPDATE OF y ON vx
    BEGIN
      UPDATE x SET y = new.y WHERE x = new.x;
    END;
    SELECT * FROM vx;
  }
} {1 1 0 2 1 0}
do_test triggerB-1.2 {
breakpoint
  execsql {
    UPDATE vx SET y = yy;
    SELECT * FROM vx;
  }
} {1 0 0 2 0 0}

finish_test