Submitted by: Alex Bekhtin (afgm)

Is related to QA419

CREATE TABLE TEST_TABLE (
ID INTEGER,
CAPTION VARCHAR(10)
);

CREATE OR ALTER VIEW VW_TEST_VIEW(
ID,
CAPTION)
AS
select ID
,CAPTION
from test_table
where ID > 0;

COMMIT;

select rdb$dependent_name from rdb$dependencies where RDB$DEPENDED_ON_NAME = 'VW_TEST_VIEW';

CREATE OR ALTER VIEW VW_TEST_VIEW(
ID,
CAPTION)
AS
select ID
,CAPTION
from test_table
where ID > 0
WITH CHECK OPTION;

COMMIT;

select rdb$dependent_name from rdb$dependencies where RDB$DEPENDED_ON_NAME = 'VW_TEST_VIEW';

RDB$DEPENDENT_NAME

CHECK_1
CHECK_1
CHECK_2

-- repeat 3 times
-- 1
CREATE OR ALTER VIEW VW_TEST_VIEW(
ID,
CAPTION)
AS
select ID
,CAPTION
from test_table
where ID > 0

-- 2
WITH CHECK OPTION;
CREATE OR ALTER VIEW VW_TEST_VIEW(
ID,
CAPTION)
AS
select ID
,CAPTION
from test_table
where ID > 0
WITH CHECK OPTION;

-- 3
CREATE OR ALTER VIEW VW_TEST_VIEW(
ID,
CAPTION)
AS
select ID
,CAPTION
from test_table
where ID > 0
WITH CHECK OPTION;

COMMIT;

-- more checks
select rdb$dependent_name from rdb$dependencies where RDB$DEPENDED_ON_NAME = 'VW_TEST_VIEW';

RDB$DEPENDENT_NAME

CHECK_1
CHECK_1
CHECK_3
CHECK_2
CHECK_3
CHECK_4
CHECK_5
CHECK_5
CHECK_6
CHECK_7
CHECK_7
CHECK_8

CREATE OR ALTER VIEW VW_TEST_VIEW(
ID,
CAPTION)
AS
select ID
,CAPTION
from test_table
where ID > 0
--WITH CHECK OPTION
;

-- ERROR vvv
CREATE OR ALTER VIEW VW_TEST_VIEW(
ID
--,CAPTION
)
AS
select ID
--,CAPTION
from test_table
where ID > 0;
Statement failed, SQLSTATE = 42000
unsuccessful metadata update
-cannot delete
-COLUMN VW_TEST_VIEW.CAPTION
-there are 4 dependencies

Commits: d5f1cac 69fe7de 6abdc46 e108800

====== Test Details ======

Updated code: made STDOUT independent of Firebird version
Note about SIMILAR TO: one need here to specify backslash TWICE inside escape clause,
e.g: ... escape '\\' (fbt_run feature ?)