Use sql syntax error

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.

Дата: 25.11.2013

Автор: Василий Лукьянчиков , vl (at) sqlinfo (dot) ru

Статья ориентирована на новичков. В ней объясняется, что означает ошибка сервера MySQL №1064, рассматриваются типичные ситуации и причины возникновения этой ошибки, а также даются рекомендации по исправлению.

Рассмотрим простейший пример.

SELECT mid, time, title, artist, download, view_count, rating, vote_num FROM dle_mservice WHERE category = ‘1’ AND approve = ‘1’ ORDER BY time DESC LIMIT -10,10;
ERROR 1064 (42000): 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 ‘-10,10’ at line 1

Сервер MySQL сообщает, что в первой строке нашего SQL запроса имеется синтаксическая ошибка, и в одинарных кавычках цитирует часть запроса с того места где начинается ошибка. Это очень полезное свойство, так как позволяет сразу определить место, которое сервер счел ошибочным. В данном случае это ‘-10,10’, ошибка возникает из-за того, что параметр LIMIT не может быть отрицательным числом.

Однако, бывает и так, что цитируемый кусок запроса не содержит синтаксической ошибки. Это означает, что данная часть запроса находится не на своем месте из-за чего весь запрос становится синтаксически неверным. Например, отсутствует разделитель между двумя запросами, пропущен кусок запроса, невидимый символ в дампе и т.д. Неудобством таких ситуаций является то, что сообщение об ошибке не содержит исходный запрос.
Действия по исправлению зависят от контекста возникновения ошибки. Таковых всего 3:

1. Запрос в редакторе.

Самый простейший случай — вы пишите свой запрос в редакторе. Если причина не опечатка, то:

• Смотреть в документации синтаксис команды для вашей версии сервера MySQL.
  

  Обратите внимание: речь идет о версии сервера MySQL, а не клиента (phpmyadmin, workbench и т.д.). Версию сервера можно узнать выполнив команду select version();

• В MySQL допускается использование ключевых слов в качестве имен столбцов/таблиц, но при этом их необходимо заключать в обратные кавычки (там где буква ё на клавиатуре).
  Пример:

  select order from test;
  ERROR 1064 (42000): 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 ‘order from test’ at line 1
  MariaDB [test]> select `order` from test;
  +——-+
  | order |
  +——-+
  |  NULL |
  +——-+

• По умолчанию ; разделяет команды. Если же нужно выполнить набор из нескольких инструкций как одну команду (например, при создании процедур, фунуций, триггеров), то в зависимости от используемого клиента может потребоваться переопределить разделитель с помощью DELIMITER, иначе интерпретация команды остановится на первой ; и будет ошибка синтаксиса. Пример:

  delimiter //
  create procedure test()
  begin
  set @a=1;
  select @a;
  end//

  Обратите внимание: DELIMITER это команда консольного клиента mysql, необходимость его использования зависит от того как вы передаете команду серверу. Например,:

  • mysql_query() выполняет содержимое как одну команду, добавление delimiter приведет к error 1064 с цитатой, начинающейся со слова delimiter
  • phpmyadmin удаляет слово delimiter из-за чего возникает error 1064 с цитатой, начинающейся с переопределенного разделителя
  • в MysqlQueryBrowser напротив необходимо использовать delimiter.

2. Перенос базы на другой сервер.

У вас есть дамп (т.е. файл с расширением .sql) и при попытке его импортировать вы получаете ошибку 1064. Причины:

• В различных версиях набор ключевых слов и синтаксис может немного отличаться. Наиболее распространенный случай: команда create table, в которой ключевое слово type было заменено на engine. Например, если вы получаете ошибку:

  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 ‘TYPE=MyISAM CHARACTER SET `utf8`’ at line 29

  Это означает, что вы переносите базу в пятую версию сервера MySQL, в котором ключевое слово TYPE не поддерживается и его нужно заменить на ENGINE.

  Редко бываю случаи, когда перенос идет на старый (~3.23) сервер, который кодировки не поддерживает. Тогда ошибка будет иметь вид:

  #1064 — You have an error in your SQL syntax near ‘DEFAULT CHARACTER SET cp1251 COLLATE cp1251_general_ci’ at line 1

  Такое может произойти, если вы переносите базу с хостинга на локальный комп, где стоит древняя версия MySQL. Лучшим решением в данном случае будет не править дамп, а обновить MySQL.

• Часто проблемы вызваны тем, что дамп делается неродными средствами MySQL (например, phpmyadmin) из-за чего в нем могут быть BOM-маркер, собственный синтаксис комментариев, завершения команды и т.д. Кроме того при использовании того же phpmyadmin возможна ситуация при которой из-за ограничения апача на размер передаваемого файла команда будет обрезана, что приведет к ошибке 1064.
  Например, если вы получаете ошибку:

  #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 ‘
  CREATE TABLE `jos_banner` (
    `bid` int(11) NOT NULL auto_increment,
    `ci‘ at line 1

  Значит ваш дамп содержит BOM-маркер. Это три байта в начале файла, помогающие программе определить что данный файл сохранен в кодировке UTF-8. Проблема в том, что MySQL пытается интерпретировать их как команду из-за чего возникает ошибка синтаксиса. Нужно открыть дамп в текстовом редакторе (например, Notepad++) и сохранить без BOM.

  Для избежания подобных проблем при создании дампа и его импорте лучше пользоваться родными средствами MySQL, см http://sqlinfo.ru/forum/viewtopic.php?id=583

3. Некорректная работа сайта.

Если во время работы сайта появляются ошибки синтаксиса, то, как правило, причина в установке вами сомнительных модулей к вашей cms. Лучшее решение — отказаться от их использования. Еще лучше предварительно проверять их работу на резервной копии.

Пример. Движок dle 7.2, поставили модуль ,вроде бы все Ок, но:

В данном примере мы видим, что причина ошибки в отсутствии значения после «id != «

Обратите внимание: из процитированного сервером MySQL куска запроса причина ошибки не ясна. Если ваша CMS не показывает весь запрос целиком, то нужно в скриптах найти место где выполняется данный запрос и вывести его на экран командой echo.

Кусок кода, который отвечает за данный запрос это

$db->query («SELECT id, title, date, category, alt_name, flag FROM « . PREFIX . «_post WHERE MATCH (title, short_story, full_story, xfields, title) AGAINST (‘$body’) AND id != «.$row[‘id’].» AND approve=’1′».$where_date.» LIMIT «.$config[‘related_number’]);

Далее можно искать откуда взялась переменная $row и почему в ней нет элемента ‘id’ и вносить исправления, но лучше отказаться от использования такого модуля (неизвестно сколько сюрпризов он еще принесет).

P.S. Если после прочтения статьи ваш вопрос с MySQL Error 1064 остался нерешенным, то задавайте его на форуме SQLinfo

Дата публикации: 25.11.2013

© Все права на данную статью принадлежат порталу SQLInfo.ru. Перепечатка в интернет-изданиях разрешается только с указанием автора и прямой ссылки на оригинальную статью. Перепечатка в бумажных изданиях допускается только с разрешения редакции.