Common SQL error troubleshooting tips in Looker

  • 23 July 2021
  • 3 replies
  • 237 views

Userlevel 4

Overarching themes/questions:

 

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?

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

 

Specific/Common Errors:

 

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'

fqXiMon97I5Kmq_Met74SxB0AHBaDydSQz6gpnnVDNUmvpgRFi42wjtCQXfIe846GfKii5Mp8iahbD0MAWHxXuM64YDXG9zzWc694DvXobrI0xLn7XLaazDl0yD9Pm-z-oWdKUWsdndwKxXQq87e5DILl0FpS1R06e4rdW7dr90_sldK

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 [TABLE_NAME] LIMIT 5;` to view a list of columns in the table. Check if the column in question exists in your table.

Frequent causes of this error:

 

Error 2:

SQL Syntax Error: (conn=95369153) FUNCTION demo_db.getdate does not exist

iEFjjDsgTMm9eyx90383JDyp5Y7ggB7CcQrD-ENwB9DXQCZ718BQUQKpRpCDlOMKKHm9TUFPlKZyZrFaHF-_QMjxFiLp1gmohLZgx020B2m43jA_V4Q8Ftqxn0BBX2M05OHqhsOCp1D8yStHgJoff8Eg4nCrDlajHzxdx0jq5EmntliS
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! 

 

Error 3:
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)

We would want to create a measure for the field that we are using:

qonz0Oc69XN6sxq_dv5aNEa2AIUgR-w9zi9_cL2mlrkiVQNvWpmF1uWMGRlJ5KskT5IGsAYq9Q1KP1aadVUi39k-AFkDV2dDvNKs2mE6Oc4CZ-BwvXPAfHokyBB6GL9QUWISTrH5dxLDGcn1BbQbLgJmTWN4VoUFupbtOBt25RNGo6Qg

Rather than doing the sum function in the SQL of the dimension - we would want to create a type sum measure instead


Error 4:
invalidQuery: No matching signature for operator >= for argument types: TIMESTAMP, DATE. Supported signature: ANY >= ANY at [34:11]

  • 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 


 

More common SQL errors troubleshooting tips:

 

How to avoid dividing by zero

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


3 replies

Userlevel 2

Thanks @Sal and @shivanisankar for creating this article!

:clap::clap::clap::clap: Great article!

Well done putting this together folks!! :clap:

Reply