Mutli-statement allows partial application and ordering issues

[beanow-at-crabnebula]

Description

Referring to the feature from #620

Testing the multi-statement support with ClickHouse (e.g. no transactions) I've found a few practical issues.

Failing on a later statement will still mark the migration as applied.
And ordering of the rollback may fail as well.

• Version: ghcr.io/amacneil/dbmate:main@sha256:69758d55574b9daa3c3e213014f4c3cea99562f319a2fdb7f49ef797fa629d92
• Database: clickhouse/clickhouse-server:25.7.2
• Operating System: Linux amd64

Steps To Reproduce

-- migrate:up
CREATE TABLE IF NOT EXISTS partial_application (
	key String,
	value String,
) ENGINE=MergeTree
ORDER BY key;

-- migrate:down
DROP TABLE IF EXISTS partial_application;

-- migrate:up
INSERT INTO partial_application (key, value) VALUES ('hello', 'world');

-- migrate:down
DELETE FROM partial_application WHERE key='hello';

-- migrate:up
INSERT INTO partial_application (missing_field, value) VALUES ('BORKED', 'foobar');

-- migrate:down
DELETE FROM partial_application WHERE missing_field='BORKED';

Imagine a "settings table" like this with inserts as part of the migration.

When running dbmate up the 3rd query will fail.

Applying: 20250820102721_demo_partial_application.sql
Applied: 20250820102721_demo_partial_application.sql in 8.98645ms
Applied: 20250820102721_demo_partial_application.sql in 11.793321ms
Applied: 20250820102721_demo_partial_application.sql in 12.316361ms
Error: code: 16, message: No such column missing_field in table migrations.partial_application (514af1a8-460e-4778-90b3-23330e7a1f95)

dbmate status will claim it's applied.
SELECT * FROM partial_application will return the ('hello', 'world') row.

When running dbmate down it will fail on the 2nd query.

Rolling back: 20250820102721_demo_partial_application.sql
Rolled back: 20250820102721_demo_partial_application.sql in 2.469958ms
Rolled back: 20250820102721_demo_partial_application.sql in 3.294821ms
Error: code: 60, message: Table migrations.partial_application does not exist

Expected Behavior

Rollbacks should be executed in reverse order.
(Same as if they were split across multiple files. Running dbmate down several times.)

Partially applied migrations should be tracked in more detail (e.g. 2/3 applied rather than boolean).

Rollbacks, in case of a partial application should skip the statements that were not applied.

dbmate up should require partial application to be rolled back first.

[beanow-at-crabnebula]

I'm seeing part of what I proposed was brought up in the PR.
#620 (comment)