Failure to rename table in 3.25 and 3.26

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

Failure to rename table in 3.25 and 3.26

Philip Warner
Tables with complex triggers (possibly limited to "Insert...With....", though
that is not clear), fail with "no such table".

The following produces the error in 3.26; a much simpler trigger does not
produce the error.

|Create Table LOG_ENTRY(||
||    LOG_ENTRY_ID int primary key,||
||    TIME_START Long,||
||    TIME_FINISH Long||
||    );||
||
||Create Table SEGMENTS(START Long, FINISH Long);||
||
||Create View SEGMENTS_REBUILD_PV(START, FINISH) as Select Null, Null;||
||
||Drop Trigger if Exists SEGMENTS_REBUILD_PV_TG;||
||Create Trigger SEGMENTS_REBUILD_PV_TG ||
||    Instead of Insert on SEGMENTS_REBUILD_PV||
||-- Recreate the SEGMENTS entries in the specified range||
||Begin||
||    Delete from SEGMENTS Where ||
||        START <= New.FINISH ||
||        And FINISH >= New.START;||
||
||    Insert or Replace into SEGMENTS(START, FINISH) ||
||        With GAP(START, FINISH) as ||
||        (Select||
||            -- Nearest break before New.START (or New.START,||
||            -- if nothing before).||
||            Max(     Coalesce((Select Max(TIME_FINISH)||
||                From LOG_ENTRY E||
||                Where E.TIME_FINISH < New.START), New.START),||
||                Coalesce((Select Max(TIME_START)||
||                From LOG_ENTRY E||
||                Where E.TIME_START < New.START), New.START)||
||                ),||
||            -- Nearest break after New.FINISH (or New.FINISH ,||
||            -- if nothing after).||
||            Min(    Coalesce((Select Min(TIME_START)||
||                From LOG_ENTRY E||
||                Where E.TIME_START > New.FINISH), New.FINISH),||
||                Coalesce((Select Min(TIME_FINISH)||
||                From LOG_ENTRY E||
||                Where E.TIME_START > New.FINISH), New.FINISH))||
||        ),||
||        LOGS as||
||        (    Select * from LOG_ENTRY E, GAP||
||            Where E.TIME_START <= GAP.FINISH||
||                And E.TIME_FINISH >= GAP.START||
||        )||
||        Select Distinct B.START, B.FINISH From||
||        GAP,||
||        (    Select||
||                BREAK as START, ||
||                Lead(BREAK) Over (Order by BREAK) as FINISH||
||            From ||
||                ( ||
||                Select Distinct TIME_START as BREAK from LOGS||
||                UNION||
||                Select Distinct TIME_FINISH as BREAK from LOGS||
||                )||
||        ) B ||
||        Where B.FINISH is Not NULL ||
||            and B.START < GAP.FINISH||
||            and B.FINISH >= GAP.START||
||    ;||
||End;||
|

|Alter table LOG_ENTRY Rename To ZZZ;|

|Error: error in trigger SEGMENTS_REBUILD_PV_TG after rename: no such table:
main.LOG_ENTRY|


_______________________________________________
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: Failure to rename table in 3.25 and 3.26

Dan Kennedy-4

On 12/03/2018 10:37 AM, Philip Warner wrote:
> Tables with complex triggers (possibly limited to "Insert...With....",
> though that is not clear), fail with "no such table".

Thanks for reporting this. The bug was that table and column names
within WITH clauses in the schema were not being updated correctly. Now
fixed here:

   https://www.sqlite.org/src/info/f44bc7a8b3fac82a

Dan.



>
> The following produces the error in 3.26; a much simpler trigger does
> not produce the error.
>
> |Create Table LOG_ENTRY(||
> ||    LOG_ENTRY_ID int primary key,||
> ||    TIME_START Long,||
> ||    TIME_FINISH Long||
> ||    );||
> ||
> ||Create Table SEGMENTS(START Long, FINISH Long);||
> ||
> ||Create View SEGMENTS_REBUILD_PV(START, FINISH) as Select Null, Null;||
> ||
> ||Drop Trigger if Exists SEGMENTS_REBUILD_PV_TG;||
> ||Create Trigger SEGMENTS_REBUILD_PV_TG ||
> ||    Instead of Insert on SEGMENTS_REBUILD_PV||
> ||-- Recreate the SEGMENTS entries in the specified range||
> ||Begin||
> ||    Delete from SEGMENTS Where ||
> ||        START <= New.FINISH ||
> ||        And FINISH >= New.START;||
> ||
> ||    Insert or Replace into SEGMENTS(START, FINISH) ||
> ||        With GAP(START, FINISH) as ||
> ||        (Select||
> ||            -- Nearest break before New.START (or New.START,||
> ||            -- if nothing before).||
> ||            Max(     Coalesce((Select Max(TIME_FINISH)||
> ||                From LOG_ENTRY E||
> ||                Where E.TIME_FINISH < New.START), New.START),||
> ||                Coalesce((Select Max(TIME_START)||
> ||                From LOG_ENTRY E||
> ||                Where E.TIME_START < New.START), New.START)||
> ||                ),||
> ||            -- Nearest break after New.FINISH (or New.FINISH ,||
> ||            -- if nothing after).||
> ||            Min(    Coalesce((Select Min(TIME_START)||
> ||                From LOG_ENTRY E||
> ||                Where E.TIME_START > New.FINISH), New.FINISH),||
> ||                Coalesce((Select Min(TIME_FINISH)||
> ||                From LOG_ENTRY E||
> ||                Where E.TIME_START > New.FINISH), New.FINISH))||
> ||        ),||
> ||        LOGS as||
> ||        (    Select * from LOG_ENTRY E, GAP||
> ||            Where E.TIME_START <= GAP.FINISH||
> ||                And E.TIME_FINISH >= GAP.START||
> ||        )||
> ||        Select Distinct B.START, B.FINISH From||
> ||        GAP,||
> ||        (    Select||
> ||                BREAK as START, ||
> ||                Lead(BREAK) Over (Order by BREAK) as FINISH||
> ||            From ||
> ||                ( ||
> ||                Select Distinct TIME_START as BREAK from LOGS||
> ||                UNION||
> ||                Select Distinct TIME_FINISH as BREAK from LOGS||
> ||                )||
> ||        ) B ||
> ||        Where B.FINISH is Not NULL ||
> ||            and B.START < GAP.FINISH||
> ||            and B.FINISH >= GAP.START||
> ||    ;||
> ||End;||
> |
>
> |Alter table LOG_ENTRY Rename To ZZZ;|
>
> |Error: error in trigger SEGMENTS_REBUILD_PV_TG after rename: no such
> table: main.LOG_ENTRY|
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
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: Failure to rename table in 3.25 and 3.26

Philip Warner
Cool! Thanks, glad it was not my problem, and glad it was fixed so fast.


On 6/12/2018 12:50 AM, Dan Kennedy wrote:

>
> On 12/03/2018 10:37 AM, Philip Warner wrote:
>> Tables with complex triggers (possibly limited to "Insert...With....",
>> though that is not clear), fail with "no such table".
>
> Thanks for reporting this. The bug was that table and column names within WITH
> clauses in the schema were not being updated correctly. Now fixed here:
>
>   https://www.sqlite.org/src/info/f44bc7a8b3fac82a
>
> Dan.
>
>
>
>>
>> The following produces the error in 3.26; a much simpler trigger does
>> not produce the error.
>>
>> |Create Table LOG_ENTRY(||
>> ||    LOG_ENTRY_ID int primary key,||
>> ||    TIME_START Long,||
>> ||    TIME_FINISH Long||
>> ||    );||
>> ||
>> ||Create Table SEGMENTS(START Long, FINISH Long);||
>> ||
>> ||Create View SEGMENTS_REBUILD_PV(START, FINISH) as Select Null, Null;||
>> ||
>> ||Drop Trigger if Exists SEGMENTS_REBUILD_PV_TG;||
>> ||Create Trigger SEGMENTS_REBUILD_PV_TG ||
>> ||    Instead of Insert on SEGMENTS_REBUILD_PV||
>> ||-- Recreate the SEGMENTS entries in the specified range||
>> ||Begin||
>> ||    Delete from SEGMENTS Where ||
>> ||        START <= New.FINISH ||
>> ||        And FINISH >= New.START;||
>> ||
>> ||    Insert or Replace into SEGMENTS(START, FINISH) ||
>> ||        With GAP(START, FINISH) as ||
>> ||        (Select||
>> ||            -- Nearest break before New.START (or New.START,||
>> ||            -- if nothing before).||
>> ||            Max(     Coalesce((Select Max(TIME_FINISH)||
>> ||                From LOG_ENTRY E||
>> ||                Where E.TIME_FINISH < New.START), New.START),||
>> ||                Coalesce((Select Max(TIME_START)||
>> ||                From LOG_ENTRY E||
>> ||                Where E.TIME_START < New.START), New.START)||
>> ||                ),||
>> ||            -- Nearest break after New.FINISH (or New.FINISH ,||
>> ||            -- if nothing after).||
>> ||            Min(    Coalesce((Select Min(TIME_START)||
>> ||                From LOG_ENTRY E||
>> ||                Where E.TIME_START > New.FINISH), New.FINISH),||
>> ||                Coalesce((Select Min(TIME_FINISH)||
>> ||                From LOG_ENTRY E||
>> ||                Where E.TIME_START > New.FINISH), New.FINISH))||
>> ||        ),||
>> ||        LOGS as||
>> ||        (    Select * from LOG_ENTRY E, GAP||
>> ||            Where E.TIME_START <= GAP.FINISH||
>> ||                And E.TIME_FINISH >= GAP.START||
>> ||        )||
>> ||        Select Distinct B.START, B.FINISH From||
>> ||        GAP,||
>> ||        (    Select||
>> ||                BREAK as START, ||
>> ||                Lead(BREAK) Over (Order by BREAK) as FINISH||
>> ||            From ||
>> ||                ( ||
>> ||                Select Distinct TIME_START as BREAK from LOGS||
>> ||                UNION||
>> ||                Select Distinct TIME_FINISH as BREAK from LOGS||
>> ||                )||
>> ||        ) B ||
>> ||        Where B.FINISH is Not NULL ||
>> ||            and B.START < GAP.FINISH||
>> ||            and B.FINISH >= GAP.START||
>> ||    ;||
>> ||End;||
>> |
>>
>> |Alter table LOG_ENTRY Rename To ZZZ;|
>>
>> |Error: error in trigger SEGMENTS_REBUILD_PV_TG after rename: no such
>> table: main.LOG_ENTRY|
>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users