My perception on EXPLAIN was wrong


My perception on EXPLAIN was wrong

Greetings experts,

We are in the testing environment, where we will not have select access to some of the tables.  When I need to run a complex query with multiple joins to other tables with many group by columns, I thought we could find out any syntax errors if any in the query by running it with explain.  If query is succcessful, I might see the message "user does not have access to table".  If there are any errors, "SYNTAX ERROR"

In short, when I don't have select access to table1 my perception on

explain sel col1, col2 from table1 group by col1  


1) If there are no syntax errors => output might be "user does not have select access"

2) If there are syntax errors => show the relative error;

But in reality the 2nd case is "user does not have select access" even though there is a hidden run time failure "selected non-aggregate must be part of the aggregate".

explain sel col1, col2, col_that_does_not_exist_in_table from table1 group by col1 

The above one gives the assumed syntax error as "column does not exist"

So, will the explain show syntax errors only with respect to the query at compile time, not at run time when we don't have access to the tables.

How can I know whether my query is free of syntax errors (without creation of any volatile tables) when I don't have select access over the tables.

Tags (1)
Junior Contributor

Re: My perception on EXPLAIN was wrong

Hi Cheeli,

this is not a runtime error like "numeric overflow", it's simply based on the internal processing of a query: syntaxer -> resolver -> security check -> query rewrite/optimization

#1: The syntaxer simply checks if the query is syntactically correct, like wrong order of keywords or missing commas/brackets.

#2: This initial parse tree is then passed to the resolver which checks for existing column/table names.

#3: Next stop is the security check which looks for existing access rights.

#4: None of previous steps checks if a query is logically correct, this is only done when query rewrite/optimization starts.

You example will not be found before #4, but already failed in #3 due to missing access rights.