ALTER TABLE fails when there is an INSTEAD-OF trigger of a VIEW

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

ALTER TABLE fails when there is an INSTEAD-OF trigger of a VIEW

Mark Johnson
Based on ticket

https://www.sqlite.org/src/tktview?name=43ddc85a63

---
During work for spatialite, I have also run into this problem using version
3.25.3.

Today I updated to 3.26.0 and saw that changes were made that (possibly)
makes it easier to pinpoint the problem.

With 3.25.3 lookupName was always being called with pTriggerTab->aCol ==
NULL

-I-> lookupName -200b- looking_for[NEW.id_rowid]
pTriggerTab->zName[middle_earth_general]  *aCol[(nil)] nCol[0]
-I-> lookupName -250b- pTriggerTab->zName[middle_earth_general]  zTab[NEW]
op[116] aCol->zName[NULL] nCol[0]
-I-> lookupName -251b- pTriggerTab->zName[middle_earth_general]  zTab[NEW]
[op != 117] aCol->zName[NULL]
-I-> lookupName -299-  sqlite3SelectAddTypeInfo cnt[0] rc[1]  error[no such
column: NEW.id_rowid]
-I-> sqlite3SelectPrep -103-  sqlite3ResolveSelectNames rc[1]  error[no
such column: NEW.id_rowid]

With 3.26.0 lookupName is being called 2 times, first with NULL and
then again pTriggerTab->aCol
!= NULL.

-I-> lookupName -200b- looking_for[(null).id_rowid] pParse->eTriggerOp[116]
pTriggerTab->zName[middle_earth_general]  *aCol[(nil)] nCol[-1]
-I-> lookupName -200a- looking_for[NEW.id_rowid] pParse->eTriggerOp[116]
pTriggerTab->zName[middle_earth_general]  aCol->zName[id_rowid] nCol[17]

However, the column count is not correct.
In my case 2 columns are missing: which should be 19.

-I-> lookupName -201a- looking_for[NEW.id_belongs_to] op[116
!=TK_DELETE[117]] pTriggerTab->zName[middle_earth_general]
 aCol->zName[id_rowid] nCol[17]
-I-> lookupName -202a loop - iCol[0]
pCol->zName[id_belongs_to]==zCol[id_rowid]
-I-> lookupName -202a loop - iCol[1]
pCol->zName[id_belongs_to]==zCol[id_admin]
-I-> lookupName -202a loop - iCol[2] pCol->zName[id_belongs_to]==zCol[name]
-I-> lookupName -202a loop - iCol[3]
pCol->zName[id_belongs_to]==zCol[admin_type]
* missing id_belongs_to
-I-> lookupName -202a loop - iCol[4]
pCol->zName[id_belongs_to]==zCol[belongs_to_01]
* missing   id_belongs_to_02
-I-> lookupName -202a loop - iCol[5]
pCol->zName[id_belongs_to]==zCol[belongs_to_02]
-I-> lookupName -202a loop - iCol[6]
pCol->zName[id_belongs_to]==zCol[valid_since]
-I-> lookupName -202a loop - iCol[7]
pCol->zName[id_belongs_to]==zCol[valid_until]
-I-> lookupName -202a loop - iCol[8]
pCol->zName[id_belongs_to]==zCol[map_x]
-I-> lookupName -202a loop - iCol[9]
pCol->zName[id_belongs_to]==zCol[map_y]
-I-> lookupName -202a loop - iCol[10]
pCol->zName[id_belongs_to]==zCol[srid]
-I-> lookupName -202a loop - iCol[11]
pCol->zName[id_belongs_to]==zCol[order_selected]
-I-> lookupName -202a loop - iCol[12]
pCol->zName[id_belongs_to]==zCol[rule_type]
-I-> lookupName -202a loop - iCol[13]
pCol->zName[id_belongs_to]==zCol[rule_text]
-I-> lookupName -202a loop - iCol[14]
pCol->zName[id_belongs_to]==zCol[notes]
-I-> lookupName -202a loop - iCol[15]
pCol->zName[id_belongs_to]==zCol[text]
-I-> lookupName -202a loop - iCol[16]
pCol->zName[id_belongs_to]==zCol[eur_point]

resulting in the error: error in trigger vw_ins_middle_earth_general: no
such column: NEW.id_belongs_to.

Mark Johnson
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: ALTER TABLE fails when there is an INSTEAD-OF trigger of a VIEW

Mark Johnson
Am Do., 20. Dez. 2018 um 16:34 Uhr schrieb Mark Johnson <
[hidden email]>:

> Based on ticket
>
> https://www.sqlite.org/src/tktview?name=43ddc85a63
>
>
>
> However, the column count is not correct.
> In my case 2 columns are missing: which should be 19.
>
After a fresh look at this today, I realized that this error was caused by
a faulty VIEW, where the 2 columns were not defined in the VIEW but being
used in the TRIGGER.

After correcting the VIEW, the error is different:

ALTER TABLE "main"."middle_earth_admin" RENAME COLUMN "admin_type" TO
"admin_level";

The lookupName parameter 'zCol' contains the new column name 'admin_level',
but is searching for the old column name admin_type, which is not being
found.

-I-> lookupName -200a- looking_for[NEW.admin_type] pParse->eTriggerOp[116]
pTriggerTab->zName[middle_earth_admin_general] -I-> lookupName -201a-
looking_for[NEW.admin_type] op[116 !=TK_DELETE[117]]
pTriggerTab->zName[middle_earth_admin_general]

-I-> lookupName -202a loop - pCol->zName[admin_type]==zCol[admin_level]
 iCol[3]

Error: near line 14: error in trigger vw_ins_middle_earth_admin_general
after rename: no such column: NEW.admin_type


>
>
> Mark Johnson
> [hidden email]
>
>
>
>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: ALTER TABLE fails when there is an INSTEAD-OF trigger of a VIEW

Richard Hipp-3
On 12/26/18, Mark Johnson <[hidden email]> wrote:

> Am Do., 20. Dez. 2018 um 16:34 Uhr schrieb Mark Johnson <
> [hidden email]>:
>
>> Based on ticket
>>
>> https://www.sqlite.org/src/tktview?name=43ddc85a63
>>
>>
>>
>> However, the column count is not correct.
>> In my case 2 columns are missing: which should be 19.
>>
> After a fresh look at this today, I realized that this error was caused by
> a faulty VIEW, where the 2 columns were not defined in the VIEW but being
> used in the TRIGGER.
>
> After correcting the VIEW, the error is different:
>
> ALTER TABLE "main"."middle_earth_admin" RENAME COLUMN "admin_type" TO
> "admin_level";

I closed the original ticket (which I neglected to do originally) with
an explanation of why it is not a bug.

In order for us to investigate this new problem you are having, please
supply us with the VIEW, CREATE TRIGGER, and ALTER TABLE statements
that are giving you trouble.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: ALTER TABLE fails when there is an INSTEAD-OF trigger of a VIEW

Mark Johnson
In reply to this post by Mark Johnson
(summery of the last messages that were sent as email)

>> Please add the list of column names after the view name:
>> CREATE VIEW middle_earth_admin_general(a,b,c,e) AS ...
>> I have a note to improve the documentation about this point.

So would the following be true:

To insure that a constant, proper column resolvement, VIEWs should be
defined in a similar way as an INSERT command where a sub-set of columns
with values is done:

INSERT INTO gcp_master
(name, longitude,latitude)
 SELECT
  name, longitude,latitude
 FROM populated_places
 WHERE name LIKE "roma,%";


CREATE VIEW gcp_master_view
(name, longitude,latitude) AS
 SELECT
  name, longitude,latitude
 FROM populated_places
 WHERE name LIKE "roma,%";


--- Final note:

When creating a VIEW with a list of defined column names, a COLUMN rename
on the underlining TABLE:

ALTER TABLE "main"."gcp_master_view" RENAME COLUMN "longitude" TO
"position_x";
ALTER TABLE "main"."gcp_master_view" RENAME COLUMN "latitude" TO
"position_y";

will result will be:
- rename of the COLUMN of the TABLE
- rename of the referenced TABLE in the corresponding VIEWs and TRIGGERs
- will NOT rename the COLUMN definition of the VIEW

The final CREATE command will then look like this:

CREATE VIEW gcp_master_view
(name, longitude,latitude) AS
 SELECT
  name, "position_x","position_y"
 FROM populated_places
 WHERE name LIKE "roma,%";
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: ALTER TABLE fails when there is an INSTEAD-OF trigger of a VIEW

Mark Johnson
Am Do., 27. Dez. 2018 um 02:53 Uhr schrieb Mark Johnson <
[hidden email]>:

> (summery of the last messages that were sent as email)
>
> >> Please add the list of column names after the view name:
> >> CREATE VIEW middle_earth_admin_general(a,b,c,e) AS ...
> >> I have a note to improve the documentation about this point.
>
> So would the following be true:
>
> To insure that a constant, proper column resolvement, VIEWs should be
> defined in a similar way as an INSERT command where a sub-set of columns
> with values is done:
>
> INSERT INTO gcp_master
> (name, longitude,latitude)
>  SELECT
>   name, longitude,latitude
>  FROM populated_places
>  WHERE name LIKE "roma,%";
>
>
> CREATE VIEW gcp_master_view
> (name, longitude,latitude) AS
>  SELECT
>   name, longitude,latitude
>  FROM populated_places
>  WHERE name LIKE "roma,%";
>
>
> --- Final note:
>
> When creating a VIEW with a list of defined column names, a COLUMN rename
> on the underlining TABLE:
>
> ALTER TABLE "main"."gcp_master_view" RENAME COLUMN "longitude" TO
> "position_x";
> ALTER TABLE "main"."gcp_master_view" RENAME COLUMN "latitude" TO
> "position_y";
>
Correction:

ALTER TABLE "main"."populated_places" RENAME COLUMN "longitude" TO
"position_x";
ALTER TABLE "main"."populated_places" RENAME COLUMN "latitude" TO
"position_y";

>
> will result will be:
> - rename of the COLUMN of the TABLE
> - rename of the referenced TABLE in the corresponding VIEWs and TRIGGERs
> - will NOT rename the COLUMN definition of the VIEW
>
> The final CREATE command will then look like this:
>
> CREATE VIEW gcp_master_view
> (name, longitude,latitude) AS
>  SELECT
>   name, "position_x","position_y"
>  FROM populated_places
>  WHERE name LIKE "roma,%";
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users