Find sql syntax error

In the SQL Server Management Studio, errors can be tracked down easily, using the built in Error List pane. This pane can be activated in the View menu, or by using shortcuts Ctrl+ and Ctrl+E

The Error List pane displays syntax and semantic errors found in the query editor. To navigate directly to the SQL syntax error in the script editor, double-click the corresponding error displayed in the Error List

SQL Keyword errors

SQL keyword errors occur when one of the words that the SQL query language reserves for its commands and clauses is misspelled. For example, writing “UPDTE” instead of “UPDATE” will produce this type of error

In this example, the keyword “TABLE” is misspelled:

As shown in the image above, not only the word “TBLE” is highlighted, but also the words around it. The image below shows that this simple mistake causes many highlighted words

In fact, there are total of 49 errors reported just because one keyword is misspelled

If the user wants to resolve all these reported errors, without finding the original one, what started as a simple typo, becomes a much bigger problem

It’s also possible that all SQL keywords are spelled correctly, but their arrangement is not in the correct order. For example, the statement “FROM Table_1 SELECT *” will report an SQL syntax error

Arrangement of commands

The wrong arrangement of keywords will certainly cause an error, but wrongly arranged commands may also be an issue

If the user, for example, is trying to create a new schema into an existing database, but first wants to check if there is already a schema with the same name, he would write the following command

However, even though each command is properly written, and is able to run separately without errors, in this form it results in an error

As the error message states, CREATE SCHEMA command has to be the first command that is given. The correct way of running this commands together looks like this

Using quotation marks

Another common error that occurs when writing SQL project is to use double quotation marks instead of single ones. Single quotation marks are used to delimit strings. For example, double quotation marks are used here instead of single ones, which cause an error

Replacing quotation marks with the proper ones, resolves the error

There are situations where double quotation marks need to be used, for writing some general quotes, for example

As shown in the previous example, this will cause an error. But, this doesn’t mean that double quotes can’t be used, they just have to be inside the single quotes. However, adding single quotes in this example won’t solve the problem, but it will cause another one

Since there is an apostrophe inside this quote, it is mistakenly used as the end of a string. Everything beyond is considered to be an error

To be able to use an apostrophe inside a string, it has to be “escaped”, so that it is not considered as a string delimiter. To “escape” an apostrophe, another apostrophe has to be used next to it, as it is shown below

Finding SQL syntax errors

Finding SQL syntax errors can be complicated, but there are some tips on how to make it a bit easier. Using the aforementioned Error List helps in a great way. It allows the user to check for errors while still writing the project, and avoid later searching through thousands lines of code

Another way to help, is to properly format the code

This can improve code readability, thus making the search for errors easier

SQLShack

Common SQL syntax errors and how to resolve them

In the SQL Server Management Studio, errors can be tracked down easily, using the built in Error List pane. This pane can be activated in the View menu, or by using shortcuts Ctrl+ and Ctrl+E

The Error List pane displays syntax and semantic errors found in the query editor. To navigate directly to the SQL syntax error in the script editor, double-click the corresponding error displayed in the Error List

SQL Keyword errors

SQL keyword errors occur when one of the words that the SQL query language reserves for its commands and clauses is misspelled. For example, writing “UPDTE” instead of “UPDATE” will produce this type of error

In this example, the keyword “TABLE” is misspelled:

As shown in the image above, not only the word “TBLE” is highlighted, but also the words around it. The image below shows that this simple mistake causes many highlighted words

In fact, there are total of 49 errors reported just because one keyword is misspelled

If the user wants to resolve all these reported errors, without finding the original one, what started as a simple typo, becomes a much bigger problem

It’s also possible that all SQL keywords are spelled correctly, but their arrangement is not in the correct order. For example, the statement “FROM Table_1 SELECT *” will report an SQL syntax error

Arrangement of commands

The wrong arrangement of keywords will certainly cause an error, but wrongly arranged commands may also be an issue

If the user, for example, is trying to create a new schema into an existing database, but first wants to check if there is already a schema with the same name, he would write the following command

However, even though each command is properly written, and is able to run separately without errors, in this form it results in an error

As the error message states, CREATE SCHEMA command has to be the first command that is given. The correct way of running this commands together looks like this

Using quotation marks

Another common error that occurs when writing SQL project is to use double quotation marks instead of single ones. Single quotation marks are used to delimit strings. For example, double quotation marks are used here instead of single ones, which cause an error

Replacing quotation marks with the proper ones, resolves the error

There are situations where double quotation marks need to be used, for writing some general quotes, for example

As shown in the previous example, this will cause an error. But, this doesn’t mean that double quotes can’t be used, they just have to be inside the single quotes. However, adding single quotes in this example won’t solve the problem, but it will cause another one

Since there is an apostrophe inside this quote, it is mistakenly used as the end of a string. Everything beyond is considered to be an error

To be able to use an apostrophe inside a string, it has to be “escaped”, so that it is not considered as a string delimiter. To “escape” an apostrophe, another apostrophe has to be used next to it, as it is shown below

Finding SQL syntax errors

Finding SQL syntax errors can be complicated, but there are some tips on how to make it a bit easier. Using the aforementioned Error List helps in a great way. It allows the user to check for errors while still writing the project, and avoid later searching through thousands lines of code

Another way to help, is to properly format the code

This can improve code readability, thus making the search for errors easier

Milena is a SQL Server professional with more than 20 years of experience in IT. She has started with computer programming in high school and continued at University.

She has been working with SQL Server since 2005 and has experience with SQL 2000 through SQL 2014.

Her favorite SQL Server topics are SQL Server disaster recovery, auditing, and performance monitoring.

• Using custom reports to improve performance reporting in SQL Server 2014 – running and modifying the reports — September 12, 2014
• Using custom reports to improve performance reporting in SQL Server 2014 – the basics — September 8, 2014
• Performance Dashboard Reports in SQL Server 2014 — July 29, 2014

About Milena Petrovic

Milena is a SQL Server professional with more than 20 years of experience in IT. She has started with computer programming in high school and continued at University. She has been working with SQL Server since 2005 and has experience with SQL 2000 through SQL 2014. Her favorite SQL Server topics are SQL Server disaster recovery, auditing, and performance monitoring. View all posts by Milena «Millie» Petrovic

Источник

SQL Errors: Five Common SQL Mistakes

As you learn SQL, watch out for these common coding mistakes

You’ve written some SQL code and you’re ready to query your database. You input the code and …. no data is returned. Instead, you get an error message.

Don’t despair! Coding errors are common in any programming language, and SQL is no exception. In this article, we’ll discuss five common mistakes people make when writing SQL.

The best way to prevent mistakes in SQL is practice. LearnSQL.com offers over 30 interactive SQL courses. Try out our SQL Practice track with 5 courses and over 600 hands-on exercises.

Watch Your Language (and Syntax)

The most common SQL error is a syntax error. What does syntax mean? Basically, it means a set arrangement of words and commands. If you use improper syntax, the database does not know what you’re trying to tell it.

To understand how syntax works, we can think of a spoken language. Imagine saying to a person “Nice dof” when you mean “Nice dog”. The person does not know what “dof” means. So when you tell your database to find a TABEL instead of a TABLE, the database does not know what it needs to do.

People tend to make the same kinds of syntax mistakes, so their errors are usually easy to spot and very much the same. After you read this article, you should be able to remember and avoid (or fix) these common mistakes. Knowing what errors to look for is very important for novice SQL coders, especially early on. New coders tend to make more mistakes and spend more time looking for them.

The types of SQL errors we will look at are:

1. Misspelling Commands
2. Forgetting Brackets and Quotes
3. Specifying an Invalid Statement Order
4. Omitting Table Aliases
5. Using Case-Sensitive Names

Ready? Let’s start.

SQL Errors:

1. Misspelling Commands

This is the most common type of SQL mistake among rookie and experienced developers alike. Let’s see what it looks like. Examine the simple SELECT statement below and see if you can spot a problem:

If you run this query, you’ll get an error which states:

Each database version will tell you the exact word or phrase it doesn’t understand, although the error message may be slightly different.

What is wrong here? You misspelled FROM as FORM. Misspellings are commonly found in keywords (like SELECT, FROM, and WHERE), or in table and column names.

Most common SQL spelling errors are due to:

• “Chubby fingers” where you hit a letter near the right one: SELEVT or FTOM or WJIRE
• “Reckless typing” where you type the right letters in the wrong order: SELETC or FORM or WHEER

Solution:

Use an SQL editor that has syntax highlighting: the SELECT and WHERE keywords will be highlighted, but the misspelled FORM will not get highlighted.

If you’re learning with interactive SQL courses in LearnSQL.com , the code editor puts every SELECT statement keyword in light purple. If the keyword is black, as it is with any other argument, you know there’s a problem. (In our example, FORM is black).

So if we correct our statement we get:

The keyword is now the right color and the statement executes without an error.

2. Forgetting Brackets and Quotes

Brackets group operations together and guide the execution order. In SQL (and in all of the programming languages I use), the following order of operations …

… is not the same as:

Can you figure out why?

A very common SQL mistake is to forget the closing bracket. So if we look at this erroneous statement :

We get an error code with the position of the error (the 102nd character from the beginning):

Remember: brackets always come in pairs.

The same is true with single quotes ( ‘ ‘ ) or double quotes ( ” ” ). There is no situation in SQL where we would find a quote (either a single quote or a double quote) without its mate. Column text values can contain one quote ( e.g. exp.last_name = «O’Reilly» ) and in these situations we must mix two types of quotes or use escape characters. ( In SQL, using escape characters simply means placing another quote near the character you want to deactivate – e.g. exp.last_name = ‘O’’Reilly. )

Solution:

Practice, practice, practice. Writing more SQL code will give you the experience you need to avoid these mistakes. And remember people usually forget the closing bracket or quotation mark. They rarely leave out the opening one. If you’re running into problems, take a close look at all your closing punctuation!

3. Invalid statement order

When writing SELECT statements, keep in mind that there is a predefined keyword order needed for the statement to execute properly. There is no leeway here.

Let’s look at an example of a correctly-ordered statement:

There’s no shortcut here; you simply have to remember the correct keyword order for the SELECT statement:

• SELECT identifies column names and functions
• FROM specifies table name or names (and JOIN conditions if you’re using multiple tables)
• WHERE defines filtering statements
• GROUP BY shows how to group columns
• HAVING filters the grouped values
• ORDER BY sets the order in which the results will be displayed

You cannot write a WHERE keyword before a FROM , and you can’t put a HAVING before a GROUP BY . The statement would be invalid.

Let’s look at what happens when you mix up the statement order. In this instance, we’ll use the common SQL error of placing ORDER BY before GROUP BY :

The error message we see is pretty intimidating!

Solution:

Don’t be discouraged! You can see that all of the keywords are highlighted correctly and all the quotations and brackets are closed. So now you should check the statement order. When you’re just beginning your SQL studies, I suggest using a SELECT order checklist. If you run into a problem, refer to your list for the correct order.

4. Omitting Table Aliases

When joining tables, creating table aliases is a popular practice. These aliases distinguish among columns with the same name across tables; thus the database will know which column values to return. This is not mandatory when we’re joining different tables, since we can use the full table names. But it is mandatory if we join a table to itself.

Suppose we’re writing an SQL statement to find an exhibition’s current location and the location from the previous year:

The database would return an error:

Note: Whenever you encounter “ambiguous column name” in your error message, you surely need table aliases.

The correct statement (with aliases) would be:

Solution:

Practice using table aliases for single-table SELECT statements. Use aliases often – they make your SQL more readable.

5. Using Case-Sensitive Names

This error only occurs when you need to write non-standard names for tables or database objects.

Let’s say that you need to have a table named LargeClient and for some reason you add another table called LARGECLIENT. As you already know, object names in databases are usually case-insensitive. So when you write a query for the LargeClient table, the database will actually query LARGECLIENT.

To avoid this, you must put double quotes around the table name. For example:

When creating a table, you will need to use double quotes if:

• The table will have a case-sensitive name.
• The table name will contain special characters. This includes using a blank space, like “Large Client”.

Solution:

Avoid using these names if you can. If not, remember your double quotes!

Everybody Makes SQL Mistakes

Those are the five most common errors in SQL code. You’ll probably make them many times as you learn this language. Remember, everybody makes mistakes writing code. In fact, making mistakes is a normal and predictable part of software development.

So don’t be discouraged. When you make mistakes in the future, try to analyze your code in a structured way. With a structured analysis, you can find and correct your errors quicker.

If you would like to learn about some other syntactic mistakes that I’ve not included here, please let me know. In an upcoming article, we’ll look at non-syntactic errors. These return or modify data and are therefore much more dangerous. Subscribe to our blog so you won’t miss it!

Источник

Designing Queries

Following a few conventions makes finding errors in queries a lot easier,
especially when you ask for help from people who might know SQL, but know
nothing about your particular schema. A query easy to read is a query easy to
debug. Use whitespace to group clauses within the query. Choose good table and
field aliases to add clarity, not confusion. Choose the syntax that supports
the query’s meaning.

Using Whitespace

A query hard to read is a query hard to debug. White space is free. New lines
and indentation make queries easy to read, particularly when constructing a
query inside a scripting language, where variables are interspersed throughout
the query.

There is a syntax error in the following. How fast can you find it?

SELECT u.id, u.name, alliance.ally FROM users u JOIN alliance ON
(u.id=alliance.userId) JOIN team ON (alliance.teamId=team.teamId
WHERE team.teamName='Legionnaires' AND u.online=1 AND ((u.subscription='paid'
AND u.paymentStatus='current') OR u.subscription='free') ORDER BY u.name;

Here’s the same query, with correct use of whitespace. Can you find the error faster?

SELECT
    u.id
    , u.name
    , alliance.ally
FROM
    users u
    JOIN alliance ON (u.id = alliance.userId)
    JOIN team ON (alliance.teamId = team.teamId
WHERE
    team.teamName = 'Legionnaires'
    AND u.online = 1
    AND (
        (u.subscription = 'paid' AND u.paymentStatus = 'current')
        OR
        u.subscription = 'free'
    )
ORDER BY
    u.name;

Even if you don’t know SQL, you might still have caught the missing ‘)’
following team.teamId.

The exact formatting style you use isn’t so important. You might like commas in
the select list to follow expressions, rather than precede them. You might
indent with tabs or with spaces. Adherence to some particular form is not
important. Legibility is the only goal.

Table and Field Aliases

Aliases allow you to rename tables and fields for use within a query. This can
be handy when the original names are very long, and is required for self joins
and certain subqueries. However, poorly chosen aliases can make a query harder
to debug, rather than easier. Aliases should reflect the original table name,
not an arbitrary string.

Bad:

SELECT *
FROM
    financial_reportQ_1 AS a
    JOIN sales_renderings AS b ON (a.salesGroup = b.groupId)
    JOIN sales_agents AS c ON (b.groupId = c.group)
WHERE
    b.totalSales > 10000
    AND c.id != a.clientId

As the list of joined tables and the WHERE clause grow, it becomes necessary to
repeatedly look back to the top of the query to see to which table any given
alias refers.

Better:

SELECT *
FROM
    financial_report_Q_1 AS frq1
    JOIN sales_renderings AS sr ON (frq1.salesGroup = sr.groupId)
    JOIN sales_agents AS sa ON (sr.groupId = sa.group)
WHERE
    sr.totalSales > 10000
    AND sa.id != frq1.clientId

Each alias is just a little longer, but the table initials give enough clues
that anyone familiar with the database only need see the full table name once,
and can generally remember which table goes with which alias while reading the
rest of the query.

Placing JOIN conditions

The manual warns against using the JOIN condition (that is, the ON
clause) for restricting rows. Some queries, particularly those using implicit
joins, take the opposite extreme — all join conditions are moved to the WHERE
clause. In consequence, the table relationships are mixed with the business
logic.

Bad:

SELECT *
FROM
    family,
    relationships
WHERE
    family.personId = relationships.personId
    AND relationships.relation = 'father'

Without digging through the WHERE clause, it is impossible to say what links
the two tables.

Better:

SELECT *
FROM
    family
    JOIN relationships ON (family.personId = relationships.personId)
WHERE
    relationships.relation = 'father'

The relation between the tables is immediately obvious. The WHERE clause is
left to limit rows in the result set.

Compliance with such a restriction negates the use of the comma operator to
join tables. It is a small price to pay. Queries should be written using the
explicit JOIN keyword anyway, and the two should never be mixed (unless you
like rewriting all your queries every time a new version changes operator
precedence).

Finding Syntax Errors

Syntax errors are among the easiest problems to solve. MariaDB provides an error
message showing the exact point where the parser became confused. Check the
query, including a few words before the phrase shown in the error message. Most
syntax and parsing errors are obvious after a second look, but some are more
elusive, especially when the error text seems empty, points to a valid keyword,
or seems to error on syntax that appears exactly correct.

Interpreting the Empty Error

Most syntax errors are easy to interpret. The error generally details the exact
source of the trouble. A careful look at the query, with the error message in
mind, often reveals an obvious mistake, such as mispelled field names, a
missing ‘AND’, or an extra closing parenthesis. Sometimes the error is a little
less helpful. A frequent, less-than-helpful message:

ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your
MariaDB server version for the right syntax to use near ' ' at line 1

The empty ‘ ‘ can be disheartening. Clearly there is an error, but where? A
good place to look is at the end of the query. The ‘ ‘ suggests that the parser
reached the end of the statement while still expecting some syntax token to
appear.

Check for missing closers, such as ‘ and ):

SELECT * FROM someTable WHERE field = 'value

Look for incomplete clauses, often indicated by an exposed comma:

SELECT * FROM someTable WHERE field = 1 GROUP BY id,

Checking for keywords

MariaDB allows table and field names and aliases that are also reserved words. To prevent ambiguity, such names must be enclosed in backticks (`):

SELECT * FROM actionTable WHERE `DELETE` = 1;

If the syntax error is shown near one of your identifiers, check if it appears
on the reserved word list.

A text editor with color highlighting for SQL syntax helps to find these
errors. When you enter a field name, and it shows up in the same color as the
SELECT keyword, you know something is amiss. Some common culprits:

• DESC is a common abbreviation for «description» fields. It means «descending»
  in a MariaDB ORDER clause.
• DATE, TIME, and TIMESTAMP are all common field names. They are
  also field types.
• ORDER appears in sales applications. MariaDB uses it to specify sorting for
  results.

Some keywords are so common that MariaDB makes a special allowance to use them
unquoted. My advice: don’t. If it’s a keyword, quote it.

Version specific syntax

As MariaDB adds new features, the syntax must change to support them. Most of the
time, old syntax will work in newer versions of MariaDB. One notable exception is
the change in precedence of the comma operator relative to the JOIN keyword in
version 5.0. A query that used to work, such as

SELECT * FROM a, b JOIN c ON a.x = c.x;

will now fail.

More common, however, is an attempt to use new syntax in an old version. Web
hosting companies are notoriously slow to upgrade MariaDB, and you may find
yourself using a version several years out of date. The result can be very
frustrating when a query that executes flawlessly on your own workstation,
running a recent installation, fails completely in your production environment.

This query fails in any version of MySQL prior to 4.1, when subqueries were
added to the server:

SELECT * FROM someTable WHERE someId IN (SELECT id FROM someLookupTable);

This query fails in some early versions of MySQL, because the JOIN syntax did
not originally allow an ON clause:

SELECT * FROM tableA JOIN tableB ON tableA.x = tableB.y;

Always check the installed version of MariaDB, and read the section of the manual
relevant for that version. The manual usually indicates exactly when particular
syntax became available for use.

SQL queries are easy to learn and reuse.

The difficulty of reading and understanding current SQL queries is known as query interpretation. It’s frequently as difficult as Query Composition, which entails writing a new query.

SQL Syntax Checker validates and marks any errors in your SQL queries.

Common Causes of Syntax Errors

• Mismatched number of open and close parentheses
• Improper query language used 
• The data required for the query is missing
• Typos
• Syntax errors such as misspelling
• Use of Reserved words
• An old version of the keyword is used
• Misspelling a keyword or function name

How to Validate Syntax and Disable Statement Execution

Before executing SQL on your production database server, you can run a SQL syntax check without connecting to the database server and look for syntax issues.
The following are supported by the database: Oracle (PLSQL), SQL Server, MySQL, DB2, and Access are all examples of database management systems.
When you’re debugging and come across any syntax that’s part of a long query and wants to validate it, all you have to do is use Syntax.

SQL is the Language used to Communicate with Databases

SQL, SQL Server, MySQL, PostgreSQL, Oracle, and so on. You want to learn SQL, but you’re intimidated by the number of keywords and don’t know where to begin. Let’s go over the definitions for each of these terms.

A database is a type of computer program that stores and processes vast volumes of information. Database vendors come in a variety of shapes and sizes. Database products from different vendors include PostgreSQL, MySQL, Oracle, and SQL Server. The programming language SQL is used to communicate with these databases, and each database product has its SQL variant. These variations are referred to as SQL dialects.

Using SQL Queries to Create Visualizations

Many individuals have tried but failed to create a successful parser due to the intricacy of the SQL grammar and dialicts. Our parser reduces the problems of deciphering SQL grammar. The parsing logic generates an abstract syntax tree (AST) containing «raw» or optionally qualified table and column IDs.

AST Tree Algorithm

The parsing stage entails breaking down the components of a SQL statement into a data structure that may be processed by subsequent algorithms. The database only parses a statement when the application tells it to. Therefore only the application, not the database, may reduce the number of parses. Various utility function analyze the AST tree to identify the components:

1. what tables appear in the query?
2. what columns appear in the query, per clause?
3. what is the table/column lineage of a query?
4. what sets of columns does a query compare for equality?

• Debugging a SQL query
• How does SQL debugging work?
• Debugging SQL syntax
• Common SQL reference guides
• Common SQL syntax errors
  • Column or table name is “not found” or “not recognized”
  • SQL function does not exist
• How to find the failing line in a SQL query
  • Reading your SQL error message
  • Reducing the size of a SQL query
• How to find out what SQL dialect to use
• Do you have a different problem?
• Are you still stuck?

Reading an error message shouldn’t feel like solving a riddle. This debugging guide explains what you can do about stubborn queries that refuse to run.

Debugging a SQL query

If your SQL query contains SQL variables that look like {{ variable }}, go to Troubleshooting SQL variables first.

1. Go to the line that is failing in your SQL query.
   • I don’t know where my SQL query is failing.
2. Check the SQL syntax on the line that is failing in your SQL query.
3. Check your query logic if the query uses joins, subqueries, or CTEs.
4. If you get an error message that isn’t specific to your SQL query, go to Troubleshooting error messages.

How does SQL debugging work?

• SQL error messages are displayed for each line in your query that fails to run. You’ll need to follow the steps above for each line that failed.
• If you make any changes to a line, run your query to check if the problem is fixed before moving on to the next step. You can add a LIMIT clause at the end of your query to speed up the process.
• Note that SQL queries are not run from top to bottom, so you won’t be debugging your query lines in the order that they are written. Follow the error messages to help you find the lines that need attention.

1. Review the spelling on the line that is failing in your SQL query.
2. Review for missing brackets or commas on the line that is failing in your SQL query.
3. Remove commented lines (lines that begin with -- or /*).
4. Review for common syntax errors that are specific to your SQL dialect.

Explanation

Your database needs to be able to “read” your query in order to execute it.

• Correct spelling tells your database exactly what to look for.
• Punctuation tells your database how (e.g. what order to use) to look for your data.
• Comments are not meant to be read or executed, but in certain edge cases, they can interfere with the reading and execution of neighboring lines.

Common SQL reference guides

Before you start, open up the SQL reference guide for the SQL dialect that you’re using. We’ve linked to some of the most common ones here:

• MySQL
• PostgreSQL
• Microsoft SQL Server
• Amazon Redshift
• Google BigQuery
• Snowflake
• I don’t know what SQL dialect to use.

Common SQL syntax errors

What does your error message say?

• My column or table name is “not found” or “not recognized”.
• My SQL “function does not exist”.

Column or table name is “not found” or “not recognized”

If your SQL query contains SQL variables that look like {{ variable }}, go to Troubleshooting SQL variables first.

Steps

1. Review the structure section of the reference guide for your SQL dialect.

   • Are you using the correct quotation marks? For example:

     • SELECT 'column_name'
     • SELECT "column_name"
     • SELECT `column_name`

   • Are you using the correct path to columns and tables? For example:

     • FROM table_name
     • FROM schema_name.table_name
     • FROM database_name.schema_name.table_name

   • Is your column name a reserved word? For example:

     In PostgresSQL, ‘users’ is a reserved key word.

     • SELECT users will throw an error.
     • SELECT "users" will run correctly.

   • Tip: Use Metabase to check for column and table name syntax

     1. Create a simple question in the notebook editor using the same columns and tables as your SQL question.
     2. Convert the question to SQL.
     3. Look at how the Metabase-generated SQL query refers to column and table names.

2. Review the data reference for the column and table names in your query.

   • If the column or table name doesn’t exist in the data reference:

     • Run SELECT * FROM your_table_name LIMIT 10; to look for the column or table name to use in your query.
     • If you’re a Metabase admin, check the Data model page for the original schema.

   • If the column name exists, but you can’t query the column from the SQL editor:

     • Ask your Metabase admin if the column was re-named or removed on the database side.
     • If you’re a Metabase admin, you may need to run a sync to refresh your data.

Explanation

You need to make sure that you’re using the correct syntax for the SQL dialect used by your database.

Your query also needs to use column and table names that match the original names in your database. Metabase uses display names that can be updated by your Metabase admin, so the data reference may not match your database schema. It’s also possible that a column or table was re-named on the database side, but Metabase hasn’t run a sync to grab the updates.

Further reading

• How Metabase executes SQL queries
• How Metabase syncs with your database
• SQL best practices

SQL function does not exist

If your SQL query contains SQL variables that look like {{ variable }}, go to Troubleshooting SQL variables first.

Steps

1. Review the data type of the column that you want your function to apply to.

   • You can use the Metabase data reference to review the column’s field type (as a proxy for data type).
   • You can also directly query the information schema in your database if you have permission to access it.

2. Review the function section of the reference guide for your SQL dialect.

   • Confirm that the function exists for your SQL dialect.
   • Review the data type(s) that are accepted by your function.

3. If the field type of your column does not match the expected data type of your function:

   • Cast your column to the correct data type in your SQL query.
   • If you’re a Metabase admin, you can also cast data types from the Data model page.

Explanation

SQL functions are designed to work on specific data types in your database. For example, the DATE_TRUNC function in PostgresSQL works on columns with date, timestamp, and time typed data in a Postgres database. If you try to use the DATE_TRUNC function on a column with a string data type in your database, it won’t work.

Note that Metabase field types are not one-to-one with the data types in your database. In this case, the field type gives you enough information about the column data type to troubleshoot the error.

Further reading

• How Metabase executes SQL queries
• Field types documentation
• SQL best practices

How to find the failing line in a SQL query

If your SQL query contains SQL variables that look like {{ variable }}, go to Troubleshooting SQL variables first.

Once you find the line that is failing in your SQL query, go to steps under Debugging a SQL query.

Reading your SQL error message

Does your error message:

• Tell you the line or character position?
• Include a table or column name? If the table or column name appears more than once in your query, reduce the size of your query.
• Mention a SQL clause?

Reducing the size of a SQL query

If your query uses:

• Subqueries (nested queries), run each subquery separately. Start with the inner subqueries and work your way out.
• CTEs, run each CTE separately. Start with your base CTE and work your way down the query.
• SQL variables that point to Metabase models, run each model separately. Go to the model by opening the variables panel, or enter the model ID number from the variable in the Metabase search bar.
• Remember to read the SQL error message as you try to isolate the problem. For more information, go to How does SQL debugging work?.

Tips for working in the SQL editor

Highlight lines of your SQL query to:

• Run the lines with Cmd + Return or Ctrl + Enter.
• Comment/uncomment the lines with Cmd + / or Ctrl + /.

How to find out what SQL dialect to use

The SQL dialect is based on the database that stores the tables you want to query. Once you find out what SQL dialect to use, you can follow the steps under Debugging a SQL query.

To find out which database you’re querying:

• If you’re a Metabase admin, go to Admin settings > Databases, and look under the Engine column.
• Otherwise, ask the person who set up your Metabase.

Do you have a different problem?

• My query results are wrong.
  • My query results have duplicated rows.
  • My query results have missing rows.
  • My aggregations (counts, sums, etc.) are wrong.
• My dates and times are wrong.
• My data isn’t up to date.
• I have an error message that isn’t specific to my SQL query or syntax.

Are you still stuck?

Search or ask the Metabase community.

Thanks for your feedback!

Get articles like this one in your inbox every month

SQL-запрос — это то, что либо работает хорошо, либо не работает вообще, частично он никак работать не может, в отличие, например, от того же PHP. Как следствие, найти ошибку в SQL-запросе, просто рассматривая его — трудно, особенно если этот запрос снабжён целой кучей JOIN и UNION. Однако, в этой статье я расскажу о методе поиска ошибок в SQL-запросе.

Поскольку обычно в SQL-запрос подставляются какие-то переменные в PHP, то необходимо его сначала вывести. Сделать это можно, например, так:

<?php
  $a = 5;
  $query = "SELECT FROM `table` WHERE `id` = '$a'";
  $result_set = $mysqli->query($query); // Не работает
  echo $query; // Выводим запрос, который отправляется
?>

В результате, скрипт выведет такой запрос: SELECT FROM `table` WHERE `id` = ‘5’. Теперь чтобы найти ошибку в нём, надо зайти в phpMyAdmin, открыть базу данных, с которой происходит работа, открыть вкладку «SQL» и попытаться выполнить запрос.

И вот здесь уже ошибка будет показана, не в самой понятной форме (иногда прямо точно описывает ошибку), но она будет. Вот что написал phpMyAdmin: «#1064 — You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘FROM `table` WHERE `id` = ‘5’ ORDER BY `table`.`id` ASC LIMIT 0, 30′ at line 1«. Это означает, что ошибка рядом с FROM. Присматриваемся к этому выделенному нами небольшому участку и обнаруживаем, что мы забыли поставить «*«. Исправляем сразу в phpMyAdmin эту ошибку, убеждаемся, что запрос сработал и после этого идём исправлять ошибку уже в коде.

С помощью этого метода я нахожу абсолютно все ошибки в SQL-запросе, которые мне не удаётся обнаружить непосредственно при осмотре в PHP-коде.

Надеюсь, теперь и Вы сможете найти ошибку в любом SQL-запросе.

• 

  Создано 01.05.2013 10:54:01

• 
  Михаил Русаков

Копирование материалов разрешается только с указанием автора (Михаил Русаков) и индексируемой прямой ссылкой на сайт (http://myrusakov.ru)!

Если у Вас остались какие-либо вопросы, либо у Вас есть желание высказаться по поводу этой статьи, то Вы можете оставить свой комментарий внизу страницы.

Если Вам понравился сайт, то разместите ссылку на него (у себя на сайте, на форуме, в контакте):

1. Кнопка:

   Она выглядит вот так:

2. Текстовая ссылка:

   Она выглядит вот так: Как создать свой сайт

3. BB-код ссылки для форумов (например, можете поставить её в подписи):