If we create databases and tables with leading/trailing whitespaces (using backticks) the behavior is inconsistent and leads to multiple problems.
Creating database with spaces makes it part of the database name, from then on they must be used with backticks.
With leading spaces the database can be created, but it can't be referenced anymore:
For tables the spaces are trimmed - the tables are created without leading or trailing spaces in their names. However as the below example shows the space is kept within the table location.
Interestingly during table creation or other operations like "use" the database name's is trimmed.
One can validate with hdfs commands that the locations have even the trailing spaces. Keeping the space in the HDFS location is inconsistent with the table names and also confusing in multiple ways (like you cannot see the trailing space), and sounds a very bad pattern.
This is even more problematic because in the underlying HMS database the NOTIFICATION_LOG entries are created with the spaces as it has been passed in the SQL statement even if the table name is trimmed - which is providing incorrect information to the other components relying on the NOTIFICATION_LOG.
Hive should trim completely the database and table names in the SQL statements - without propagating that forward.