Last tested: Mar 9, 2021
At the time the code for the MySQL dialect was written, it did not support WITH, so we had to write derived tables a bit differently. Instead of WITH we use CREATE temp TABLE, which requires database permissions. (This is still different than a PDT since we are not writing the table to the schema, just creating a temp table)
This error can occur when trying to use derived tables without setting up the database permissions:
temp derived_table sql_availability_audit creation failed: SQL Error in CREATE TABLE as SELECT: java.sql.SQLSyntaxErrorException: Access denied for user 'looker'@'%' to database 'looker_tmp'
Later versions of MySQL now support CTEs (WITH), but as of Mar. 2021 Looker still uses temporary tables for ephemeral derived tables.
@rachel.johnson did you find a solution for this?
I still believe we need to explicitly provide access to all the DB Users to create Temp tables according to this community forum. Isnt it so?