DROP TABLE IF EXISTS does not work

[martinch]

When I try to run the command

spark.sql("DROP TABLE IF EXISTS table_to_drop")

and the table does not exist, I get the following error:

AnalysisException: "Table or view 'table_to_drop' not found in database 'null';;\nDropTableCommand `table_to_drop`, true, false, false\n"

The command works when the table does exist.

I am using Python3 in an Azure Databricks notebook, Databricks runtime 5.2 on a high concurrency cluster.

The table was created using the following command:

df.write.option("path", "adl://***.azuredatalakestore.net/delta/table_to_drop").saveAsTable(name="table_to_drop", format="delta")

I thought the whole point of using "IF EXISTS" was to avoid this error. Is this a bug, or is it something I don't understand?

[Eve]

According to this documentation, it is how it works... It throws an error, when the table doesn't exist:

Databricks DOCS

It's pretty annoying though...

[martinch]

But that is without the optional "IF EXISTS". With the "IF EXISTS" command the docs says: "If the table does not exist, nothing happens.".

[robert4os]

This is not the expected behavior for SQL users.

e.g. https://dev.mysql.com/doc/refman/8.0/en/drop-table.html ...'With

IF EXISTS

, no error occurs for nonexisting tables.'

It is a usability bug.

Best regards, Robert

[StevenWilliams]

I agree about this being a usability bug. Documentation clearly states that if the optional flag "IF EXISTS" is provided that the statement will do nothing.

https://docs.databricks.com/spark/latest/spark-sql/language-manual/drop-table.html

Drop Table copy

DROP TABLE [IF EXISTS] [db_name.]table_name

Drop a table and delete the directory associated with the table from the file system if this is not an

EXTERNAL

table. If the table to drop does not exist, an exception is thrown.

IF EXISTS

If the table does not exist, nothing happens.

I am getting the same error as the original post while running the just this command with the SQL API

DROP TABLE IF EXISTS testTable;

I would expect no error but get the following.

Error in SQL statement: AnalysisException: Table or view 'testTable' not found in database 'null';; DropTableCommand

testTable

, true, false, false