[sqlite] 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|

[Dan Kennedy]

Post by Philip Warner
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.

Post by Philip Warner
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
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Philip Warner]

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

Post by Philip Warner
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
  https://www.sqlite.org/src/info/f44bc7a8b3fac82a
Dan.

Post by Philip Warner
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
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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