Ошибка в sql запросе select id from - ErrorsMaster.ru - большая энциклопедия ошибок и их решений

Когда пытаюсь вставить id в поле возникает уникальность.
Извлекал через resultset id и приболял в конце id+=1;,но не работает.
Если не трудно обьясните почему не извлекаются данные до последний записи в id.
И как сделать что бы id генерировался автоматически БД postgresql.

Код repository.

public void Add(String First_Name, String Last_Name, String Second_Name, String email, String password, String login) {
        Date date = new Date();
        SimpleDateFormat format1 = new SimpleDateFormat("dd.MM.yyyy");
        String sqlDate = format1.format(date);
        int id = 0;
        Connection c = getCurrentConnection();
        QueryRunner queryRunner = new QueryRunner();
        try {
            Statement statement = c.createStatement();
            ResultSet resultSet = statement.executeQuery("SELECT id FROM  account  ");
            while (resultSet.next()) {
                id = resultSet.getInt("id");
            }
            id += 1;
            String sql = "insert into account (id,login,password,first_name,last_name,second_name,email,active,created) values(" + "'" + id + "'" + "," +
                    "'" + login + "'" + "," + "'" + password + "'" + "," + "'" + First_Name + "'" + "," + "'" + Last_Name + "'" + "," + "'" +
                    Second_Name + "'" + "," + "'" + email + "'" + "," + "'" + "true" + "'" + "," + "'" + sqlDate + "'" + ")";
            queryRunner.update(c, sql);
            queryRunner.update(c, "insert into account_role (id,id_account,id_role) values " +
                    "(" + "'" + id + "'" + "," + "'" + id + "'" + "," + "'" + 1 + "'" + ")");
        } catch (SQLException e) {
            throw new WebtesterApplicationException(e);
        }
    }

Ошибка.

HTTP Status 500 - java.sql.SQLException: ОШИБКА: повторяющееся значение ключа нарушает ограничение уникальности "account_pkey"

type Exception report

message java.sql.SQLException: ОШИБКА: повторяющееся значение ключа нарушает ограничение уникальности "account_pkey"

description The server encountered an internal error that prevented it from fulfilling this request.

exception

exception.WebtesterApplicationException: java.sql.SQLException: ОШИБКА: повторяющееся значение ключа нарушает ограничение уникальности "account_pkey"
  Подробности: Ключ "(id)=(2)" уже существует. Query: insert into account (id,login,password,first_name,last_name,second_name,email,active,created) values('2','password','maximbogunwork@gmail.comw2','maksim','ascsav','','maksimbogunenko1z2','true','04.02.2017') Parameters: []
    repositoryimpl.AccountRegistrationImpl.Add(AccountRegistrationImpl.java:43)
    serviceimpl.CommonServiceImpl.registration(CommonServiceImpl.java:109)
    servlet.RegistrationServlet.doPost(RegistrationServlet.java:32)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
    org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    filter.CheckLoginFilter.doFilter(CheckLoginFilter.java:39)
    filter.AbstarctFilter.doFilter(AbstarctFilter.java:22)
    filter.AutoLoginFilter.doFilter(AutoLoginFilter.java:45)
    filter.AbstarctFilter.doFilter(AbstarctFilter.java:22)
root cause

java.sql.SQLException: ОШИБКА: повторяющееся значение ключа нарушает ограничение уникальности "account_pkey"
  Подробности: Ключ "(id)=(2)" уже существует. Query: insert into account (id,login,password,first_name,last_name,second_name,email,active,created) values('2','password','maximbogunwork@gmail.comw2','maksim','ascsav','','maksimbogunenko1z2','true','04.02.2017') Parameters: []
    org.apache.commons.dbutils.AbstractQueryRunner.rethrow(AbstractQueryRunner.java:392)
    org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:491)
    org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:377)
    repositoryimpl.AccountRegistrationImpl.Add(AccountRegistrationImpl.java:39)
    serviceimpl.CommonServiceImpl.registration(CommonServiceImpl.java:109)
    servlet.RegistrationServlet.doPost(RegistrationServlet.java:32)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
    org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    filter.CheckLoginFilter.doFilter(CheckLoginFilter.java:39)
    filter.AbstarctFilter.doFilter(AbstarctFilter.java:22)
    filter.AutoLoginFilter.doFilter(AutoLoginFilter.java:45)
    filter.AbstarctFilter.doFilter(AbstarctFilter.java:22)

Источник

Время на прочтение
6 мин

Количество просмотров 65K

Сегодня 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 ошибок — не единственные, которые часто встречаются среди новичков и даже профессионалов. У нас есть еще одна пачка тезисов по этому поводу — но это уже тема другой статьи.

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

Источник

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)!

Добавляйтесь ко мне в друзья ВКонтакте: http://vk.com/myrusakov.
Если Вы хотите дать оценку мне и моей работе, то напишите её в моей группе: http://vk.com/rusakovmy.

Если Вы не хотите пропустить новые материалы на сайте,
то Вы можете подписаться на обновления: Подписаться на обновления

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

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

Кнопка:

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

Она выглядит вот так: Как создать свой сайт
BB-код ссылки для форумов (например, можете поставить её в подписи):

Источник

I’m answering my own question because I have found the answer by myself.

Using EMS Sql Manager 2008 for SQL Server I executed select * from marcas and have no results, just errors. But If I recreated the table, voila, it just worked fine !!!

So the problem was the way I created the tables in the server. After a while, I realized the command that created the table in Foxpro using ODBC was:

oerr = sqlexec(oconn, "ALTER TABLE ["+xtabla+"] ADD ["+borrar.field_name+"] "+tipo_campo(borrar.field_type, borrar.field_len, borrar.field_dec),"")

so changed it to:

oerr = sqlexec(oconn, "ALTER TABLE ["+xtabla+"] ADD ["+alltrim(borrar.field_name)+"] "+tipo_campo(borrar.field_type, borrar.field_len, borrar.field_dec),"")

that is, I just deleted the extra spaces right after the table name.

Thats all, «codigo» is not equal to «codigo «.

Thanks to all of you who tried to help me.

I beleve

Источник

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

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

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

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

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

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

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 в запросах с агрегацией. Это неверно!

Например:

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

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

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

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

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

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

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

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

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

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

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

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

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

Отдел маркетинга решил сделать 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

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

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

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

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

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

Одна из таких вещей — поведение оконной функции 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

Получается, что запрос тупо продублировал значения из столбца 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

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

Эпилог

Источник