DROP TABLE with CASCADE appears not to account for CHECK constraint with subquery and fails

[deliciouslytyped]

I'm using a self-compiled version 2.2.214 of H2.

I'm not sure if this is my error or if this is a bug. I have a schema with a check constraint containing a subquery (with H2 being one of the few databases that supports such at thing :) <3 ). When Hibernate tries to drop a table referred to by the constraint, even with CASCADE, it fails.

https://www.h2database.com/html/commands.html#drop_table states the following, which suggests this should work.

Drops an existing table, or a list of tables. The command will fail if dependent objects exist and the RESTRICT clause is used (the default). All dependent views and constraints are dropped as well if the CASCADE clause is used. This command commits an open transaction in this connection.

2023-12-30 08:54:28.742 DEBUG 3600 --- [ionShutdownHook] org.hibernate.SQL                        : 
    
    drop table if exists movie CASCADE 
Hibernate: 
    
    drop table if exists movie CASCADE 
2023-12-30 08:54:28.748 ERROR 3600 --- [ionShutdownHook] .SchemaDropperImpl$DelayedDropActionImpl : HHH000478: Unsuccessful: 
    drop table if exists movie CASCADE 
2023-12-30 08:54:28.748 DEBUG 3600 --- [ionShutdownHook] .SchemaDropperImpl$DelayedDropActionImpl : Error performing delayed DROP command [
    drop table if exists movie CASCADE ]

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Cannot drop "PUBLIC.MOVIE" because "PUBLIC.SCREENING" depends on it; SQL statement:

    drop table if exists movie C [90107-214]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:632) ~[classes/:na]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:477) ~[classes/:na]
	at org.h2.message.DbException.get(DbException.java:223) ~[classes/:na]
	at org.h2.engine.Database.removeSchemaObject(Database.java:1655) ~[classes/:na]
	at org.h2.command.ddl.DropTable.executeDrop(DropTable.java:114) ~[classes/:na]
	at org.h2.command.ddl.DropTable.update(DropTable.java:122) ~[classes/:na]
	at org.h2.command.CommandContainer.update(CommandContainer.java:169) ~[classes/:na]
	at org.h2.command.Command.executeUpdate(Command.java:252) ~[classes/:na]
	at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:252) ~[classes/:na]
	at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:223) ~[classes/:na]
	at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94) ~[HikariCP-4.0.3.jar:na]
	at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java) ~[HikariCP-4.0.3.jar:na]
	at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:54) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
	... 21 common frames omitted

I may have time to minimize a repro later but until then, the non-minimized part of my schema dumped with IntelliJ:

create table PUBLIC.MOVIE
(
    TITLE   CHARACTER VARYING(255) not null primary key,
    GENRE   CHARACTER VARYING(255) not null,
    RUNTIME INTEGER not null,
    constraint CHECK_RUN_TIME check ("RUNTIME" > 0)
);

create table PUBLIC.ROOM
(
    NAME      CHARACTER VARYING(255) not null
        primary key,
    COL_COUNT INTEGER                not null,
    ROW_COUNT INTEGER                not null,
    constraint CHECK_COL_COUNT
        check ("COL_COUNT" > 0),
    constraint CHECK_ROW_COUNT
        check ("ROW_COUNT" > 0)
);

create table PUBLIC.SCREENING
(
    SCREENING_ID BIGINT                 not null
        primary key,
    START_TIME   TIMESTAMP              not null,
    MOVIE_TITLE  CHARACTER VARYING(255) not null,
    ROOM_NAME    CHARACTER VARYING(255) not null,
    constraint ALTERNATE_PK
        unique (MOVIE_TITLE, START_TIME, ROOM_NAME),
    constraint FK5ABO25OKVA5HYDVTS73F6H7VA
        foreign key (MOVIE_TITLE) references PUBLIC.MOVIE,
    constraint FKQ1QJO09HO2CPVQEE0XV1MCR6I
        foreign key (ROOM_NAME) references PUBLIC.ROOM,
    constraint FUTURE_ONLY
        check ("PUBLIC"."CURRENT_TIMESTAMP"() < "START_TIME"),
    constraint NO_OVERLAP
        check (NOT EXISTS(SELECT 1
                          FROM "PUBLIC"."SCREENING" "S"
                                   INNER JOIN "PUBLIC"."MOVIE" "M"
                                              ON 1 = 1
                                   INNER JOIN "PUBLIC"."MOVIE" "MY"
                                              ON 1 = 1
                          WHERE (("SCREENING"."MOVIE_TITLE" = "MY"."TITLE")
                              AND ("S"."MOVIE_TITLE" = "M"."TITLE"))
                            AND (("SCREENING"."ROOM_NAME" = "S"."ROOM_NAME")
                              AND ("SCREENING"."SCREENING_ID" <> "S"."SCREENING_ID"))
                            AND (DATEADD(MINUTE, "MY"."RUNTIME", "SCREENING"."START_TIME") > "S"."START_TIME")
                            AND (DATEADD(MINUTE, "M"."RUNTIME", "S"."START_TIME") > "SCREENING"."START_TIME"))),
    constraint NO_OVERLAP_BREAK
        check (NOT EXISTS(SELECT 1
                          FROM "PUBLIC"."MOVIE" "M"
                                   INNER JOIN "PUBLIC"."MOVIE" "MY"
                                   INNER JOIN "PUBLIC"."SCREENING" "S"
                          WHERE (("SCREENING"."MOVIE_TITLE" = "MY"."TITLE")
                              AND ("S"."MOVIE_TITLE" = "M"."TITLE"))
                            AND (("ROOM_NAME" = "S"."ROOM_NAME")
                              AND ("SCREENING"."SCREENING_ID" <> "S"."SCREENING_ID"))
                            AND (DATEADD(MINUTE, "MY"."RUNTIME" + 10, "SCREENING"."START_TIME") > "S"."START_TIME")
                            AND (DATEADD(MINUTE, "M"."RUNTIME" + 10, "S"."START_TIME") > "SCREENING"."START_TIME")))
);

[deliciouslytyped]

Using the debugger took me to (these links are on master, not my version, but they appear to match)

h2database/h2/src/main/org/h2/command/ddl/DropTable.java

Lines 117 to 137 in 3525a20

	private void executeDrop() {
	for (SchemaAndTable schemaAndTable : tables) {
	// need to get the table again, because it may be dropped already
	// meanwhile (dependent object, or same object)
	Table table = schemaAndTable.schema.findTableOrView(session, schemaAndTable.tableName);
	if (table != null) {
	table.setModified();
	Database db = getDatabase();
	db.lockMeta(session);
	db.removeSchemaObject(session, table);
	}
	}
	}
	@Override
	public long update() {
	if (prepareDrop()) {
	executeDrop();
	}
	return 0;
	}

and

h2database/h2/src/main/org/h2/engine/Database.java

Line 1684 in 3525a20

Table t = getDependentTable(obj, null);

prepareDrop does it's checks and whatnot, this seems to be where "is not RESTRICT" seems to be checked.
This section passes, then in executeDrop removeSchemaObject is called on the MOVIE table, where in:

            if (!starting) {
                Table t = getDependentTable(obj, null);
                if (t != null) {
                    obj.getSchema().add(obj);
                    throw DbException.get(ErrorCode.CANNOT_DROP_2, obj.getTraceSQL(), t.getTraceSQL());
                }
                obj.removeChildrenAndResources(session);

getDependentTable returns the SCREENING table and so aborts.

removeChildrenAndResources I think removes (from browsing the source) the indexes on the MVTable and via the superclass all the resources belonging to the table.
I.e. the CASCADE behavior simple just seems not to be implemented (neither in the code preceding the paste)?
It just doesn't fail the statement earlier in the process.

[grandinj]

Just to be clear - are you expecting the SCREENING table to the dropped?

Because if so, that is not what CASCADE is designed to do. The intention is that it will drop associated VIEWs, TRIGGERs, and CONSTRAINTs, but not anything else.

In which case, the documentation should be clarified.

But possibly I am misreading your (very nice) bug report?

[katzyn]

@grandinj
General rules of <drop table statement> in the SQL Standard say that only constraint must be dropped with CASCADE clause.

I'll take a look on it later, most likely we have other problems in this area.

[deliciouslytyped]

grandinj;
I think I would expect the NO_OVERLAP and NO_OVERLAP_BREAK constraints to be dropped, not the SCREENING table.

This would fit what the doc says, and would do what I superficially expect. I haven't used SQL much in production but the purpose of CASCADE appears to me to be to remove things that are preventing the desired deletion operation. To do that it should be sufficient for the constraint, i.e. the thing holding a live reference to the table, to be removed. Afterwards, if something wants to delete the other table later it can.

I understand if this wasn't clear from the big SQL blob and unelaborated log I pasted.

I.e. no I do not expect SCREENING to be dropped. It shouldn't need to be, it doesn't stop MOVIE from being dropped, only it's constraint stops it.

katzyn;
Thanks for taking a look.

[katzyn]

Such constraints currently don't register themselves in a queried table, but registration can't help by itself because the main problem is bigger.

H2 collects dependencies during database object removal in the wrong way, it probably should collect actual dependencies instead of tables with these dependencies. And this collection is performed too late for transient removal. A whole database object deletion logic needs to be adjusted first.