A local temporary table can replace a persistent table when using RECREATE.

[AlexBekhtin]

T1 will be replaced. It would be logical to prohibit such a RECREATE between persistent and local temporary tables.

CREATE TABLE T1 (V VARCHAR(50));

RECREATE LOCAL TEMPORARY TABLE T1 (V VARCHAR(50))
ON COMMIT PRESERVE ROWS;

[sim1984]

A similar problem exists for GTT as well. It's just harder to spot, as there aren't many people willing to recreate GTT, as GTT metadata is randomly changed on the fly.

[asfernandes]

It's as designed as you have noted that GTT also allowed it.

[asfernandes]

RECREATE is DROP IF NOT EXISTS + CREATE, so it's conceptually correct.

[sim1984]

This may be correct, but it's a potential source of errors for the database developer. In their minds, recreating a LTT is safe; no one expects a persistent table to be accidentally deleted.
On the other hand, with dependencies and permissions, deleting a persistent table isn't so easy.

[dyemanov]

RECREATE is DROP IF NOT EXISTS + CREATE, so it's conceptually correct.

True, but if I recreate a temporary table, then I'd expect that the same temporary table to be dropped if exists.

[asfernandes]

True, but if I recreate a temporary table, then I'd expect that the same temporary table to be dropped if exists.

I would consider that valid if the command was DROP LOCAL TEMPORARY TABLE.

[sim1984]

DROP LOCAL TEMPORARY TABLE – I think this is a good option. The user should understand what exactly they're dropping.

[asfernandes]

Standard does not agree.

[sim1984]

The standard isn't always consistent. We can extend the DROP [<table-type>] TABLE standard. The table type is optional, but if specified, only tables of the correct type will be dropped, not any tables with the same name.

[aafemt]

Standard has no RECREATE query thus no requirement for it to use exactly standard DROP TABLE query.