Here is a list of common questions our users often need support with, along with troubleshooting steps to try out:
1. Why is my SQL query slow even when there are no queries queued on Looker?
Run against database (or SQL Runner): check if that runs fast to see if it is a database problem or looker problem
If the query runs slow in both SQL Runner and database, that means that the query itself is slow and that it is not specifically a Looker issue, but instead a database issue or query complexity issue
Explain in SQL can help to optimize the query
Check join logic, so we can make sure to avoid fanouts
Open up the generated SQL in SQL Runner to test your query
2. I have a part of a query, and I see there’s an error from this query. How can I find out which field is causing the error?
Start by making the query less complex:
Remove all filters and extra fields to isolate the field causing the error
If the query is pivoted, unpivot the field and then take a look at the generated SQL
Open up the generated SQL in SQL Runner to test your query/see a more detailed error message
Search the SQL database dialect docs for the functions we are using to ensure that we are using correct syntax
Check to see if the database name is included in the error surfaced on the explore.
If it is, we are looking at a SQL error and need to check our SQL
If it is not, we are looking at a LookML issue and need to check our LookML
Are we able to run the query in the database without error?
3. Why is Looker’s generated SQL different from the one I usually write?
Symmetric aggregates: They help to prevent miscalculating aggregates, but also add in a lot of complex looking code in the generated SQL
4. What does a particular SQL error mean? (function missing, data type mismatch, etc)
Is there a data type mismatch between what we have written versus the data types that are required for the function, per the database dialect documentation?
Are we using the correct function for the database dialect?
Google the error: there are lots of online forums that can provide context
Open up the generated SQL in SQL Runner to test your query
So we just went over the overarching themes that we can run into when working with complex SQL. However, there are times when we might run into errors that are very specific to the query we are working with. Here are the top few errors we may run into, we use MySQL here as an example, the error messages from other SQL dialects can be very similar:
Error 1:
SQL Syntax Error: The MySQL database encountered an error while running this query.
(conn=95374054) Unknown column 'users.city' in 'field list'
So what does this error mean?
The database is throwing an error stating that the column we’re referencing in the `sql` parameter of a field isn’t being found. Therefore, there are a few things we can check for here:
Steps to troubleshoot:
Narrow down the field(s) the error is coming from and identify the table in the database that the error is coming from
Run a `SELECT * FROM Frequent causes of this error: Typos: When manually typing table names into the `sql` parameter, we may make a typo Data changed in the database: If column names change in a table in the database, that won’t automatically update the Looker view file that’s based on that table. We’ll need to manually change that in the Looker view file or generate a new view file with “create view from table” Resource for if your data is changing frequently: https://community.looker.com/lookml-5/automatically-refresh-of-view-definition-based-on-updated-data... Error 2: SQL Syntax Error: (conn=95369153) FUNCTION demo_db.getdate does not exist Error 3: We would want to create a measure for the field that we are using: Rather than doing the sum function in the SQL of the dimension - we would want to create a type sum measure instead Here we want to make sure that the two date functions that we are comparing are the same DATATYPE (timestamp >= timestamp or date >= date or datetime >= datetime) Oftentimes we can use the cast() to cast one of the fields to match the other fields data type It is often helpful to take a look at the query to see which fields are type date or time field from which the error could be occurring and working up from there A similar error can happen when we write “case when .. then” statement if the data type for all the “then” statements are not consistent Dealing with Non-Unique Primary Keys Error: Column <name> must appear in the GROUP BY clause or be used in an aggregate function SQL Error: "Syntax error at or near:" SQL Error: relation "schema.table" does not exist" SQL Error: "X is not a valid group by expression" No Matching Signature for operator >= for argument types: TYPE, OTHER TYPE LIMIT 5;` to view a list of columns in the table. Check if the column in question exists in your table.
Explanation: This error is due to using the wrong function. As we can see in the SQL here - the `getdate()` function is not a recognized function in the MySQL database. The correct function to use in this case would be curdate()
`sql: curdate() ;;`
Because there are nuances in which SQL dialect is based on your database - it is a good idea to double check the functions and their properties when writing custom SQL!
SQL Error: "X is not a valid group by expression"
Explanation: What does error mean? It is likely that the fields we are added to our query is not aggregating (measure) across a group (dimension)
Error 4:
invalidQuery: No matching signature for operator >= for argument types: TIMESTAMP, DATE. Supported signature: ANY >= ANY at [34:11]
More common SQL errors troubleshooting tips:
Thanks @Sal and @shivanisankar for creating this article!
Great article!
Well done putting this together folks!!
One of the SQL System Functions that returns the severity of an error is SQL ERROR SEVERITY (if occurred). Within the context of a CATCH block, the ERROR SEVERITY function is used. This method will return NULL if called outside of the CATCH block logo animation online