Subquery in from must have an alias ошибка

В первой части по подзапросам мы упоминали, что подзапросы можно использовать во фразе FROM. Но так ни разу этим не воспользовались.

Когда это может быть полезно?

Пример из жизни. Покупать товар по самой дешевой цене всегда приятно. Однако, не всегда удается купить именно по самой дешевой цене. То магазин закрыт, то добираться неудобно…

Допустим, что нас устроит покупка по цене от минимальной до средней на этот товар по всем магазинам.

Мы уже владеем коррелированными подзапросами. Воспользуемся ими для решения задачи:

SELECT *
  FROM product_price pp
 WHERE pp.price BETWEEN
           (SELECT min (ppi.price)
              FROM product_price ppi
             WHERE ppi.product_id = pp.product_id)
       AND (SELECT avg (ppi.price)
              FROM product_price ppi
             WHERE ppi.product_id = pp.product_id)
 ORDER BY pp.product_id,
          pp.price

Не ошиблись ли мы? Не знаю, давай проверим на товаре с product_id = 3:

SELECT pp.product_id,
       pp.price
  FROM product_price pp
 WHERE pp.product_id = 3
 ORDER BY pp.price

Вроде бы не ошиблись. А давай в исходном запросе выведем минимальную и среднюю стоимость. Мы же умеем писать коррелированные подзапросы:

SELECT pp.product_id,
       pp.store_id,
       pp.price,
       (SELECT round (min (ppi.price), 2)
          FROM product_price ppi
         WHERE ppi.product_id = pp.product_id
       ) AS price_min,
       (SELECT round (avg (ppi.price), 2)
          FROM product_price ppi
         WHERE ppi.product_id = pp.product_id
       ) AS price_avg
  FROM product_price pp
 WHERE pp.price BETWEEN
           (SELECT min (ppi.price)
              FROM product_price ppi
             WHERE ppi.product_id = pp.product_id)
       AND (SELECT avg (ppi.price)
              FROM product_price ppi
             WHERE ppi.product_id = pp.product_id)
 ORDER BY pp.product_id,
          pp.price
          

Работает, но читать такое сложновато.

Есть решение лучше. Давай сначала по каждому товару найдем минимальную и среднюю стоимость, а затем найдем товары с ценой в диапазоне от минимальной до средней.

1. Определим минимальную и среднюю стоимость по товару:

SELECT pp.product_id,
       min (pp.price) AS price_min,
       avg (pp.price) AS price_avg
  FROM product_price pp
 GROUP BY pp.product_id

1. Зная минимальную и среднюю стоимость по товару, возьмем товары с нужными ценами.

Можно использовать подзапросы во фразе FROM. В таком случае к результатам подзапроса можно обращаться как к обычной таблице:

SELECT *
  FROM (SELECT pp.product_id,
               min (pp.price) AS price_min,
               avg (pp.price) AS price_avg
          FROM product_price pp
         GROUP BY pp.product_id
       ) as ppma

Правда есть одно условие: результату подзапроса нужно назначить псевдоним. Если не задать псевдоним, то возникнет ошибка:

SELECT *
  FROM (SELECT pp.product_id,
               min (pp.price) AS price_min,
               avg (pp.price) AS price_avg
          FROM product_price pp
         GROUP BY pp.product_id
       ) 

error: subquery in FROM must have an alias

Воспользуемся новыми знаниями для поиска товаров в магазинах с ценой от минимальной до средней:

SELECT pp.product_id,
       pp.store_id,
       pp.price,
       round (ppma.price_min, 2) AS price_min,
       round (ppma.price_avg, 2) AS price_avg
  FROM (SELECT pp.product_id,
               min (pp.price) AS price_min,
               avg (pp.price) AS price_avg
          FROM product_price pp
         GROUP BY pp.product_id
       ) ppma,
       product_price pp
 WHERE pp.product_id = ppma.product_id
   AND pp.price BETWEEN ppma.price_min AND ppma.price_avg
 ORDER BY pp.product_id,
          pp.price

Syntax errors are quite common while coding.

But, things go for a toss when it results in website errors.

PostgreSQL error 42601 also occurs due to syntax errors in the database queries.

At Bobcares, we often get requests from PostgreSQL users to fix errors as part of our Server Management Services.

Today, let’s check PostgreSQL error in detail and see how our Support Engineers fix it for the customers.

What causes error 42601 in PostgreSQL?

PostgreSQL is an advanced database engine. It is popular for its extensive features and ability to handle complex database situations.

Applications like Instagram, Facebook, Apple, etc rely on the PostgreSQL database.

But what causes error 42601?

PostgreSQL error codes consist of five characters. The first two characters denote the class of errors. And the remaining three characters indicate a specific condition within that class.

Here, 42 in 42601 represent the class “Syntax Error or Access Rule Violation“.

In short, this error mainly occurs due to the syntax errors in the queries executed. A typical error shows up as:

Here, the syntax error has occurred in position 119 near the value “parents” in the query.

How we fix the error?

Now let’s see how our PostgreSQL engineers resolve this error efficiently.

Recently, one of our customers contacted us with this error. He tried to execute the following code,

CREATE OR REPLACE FUNCTION prc_tst_bulk(sql text)
RETURNS TABLE (name text, rowcount integer) AS
$$
BEGIN
WITH m_ty_person AS (return query execute sql)
select name, count(*) from m_ty_person where name like '%a%' group by name
union
select name, count(*) from m_ty_person where gender = 1 group by name;
END
$$ LANGUAGE plpgsql;

But, this ended up in PostgreSQL error 42601. And he got the following error message,

ERROR: syntax error at or near "return"
LINE 5: WITH m_ty_person AS (return query execute sql)

Our PostgreSQL Engineers checked the issue and found out the syntax error. The statement in Line 5 was a mix of plain and dynamic SQL. In general, the PostgreSQL query should be either fully dynamic or plain. Therefore, we changed the code as,

RETURN QUERY EXECUTE '
WITH m_ty_person AS (' || sql || $x$)
SELECT name, count(*)::int FROM m_ty_person WHERE name LIKE '%a%' GROUP BY name
UNION
SELECT name, count(*)::int FROM m_ty_person WHERE gender = 1 GROUP BY name$x$;

This resolved the error 42601, and the code worked fine.

[Need more assistance to solve PostgreSQL error 42601?- We’ll help you.]

Conclusion

In short, PostgreSQL error 42601 occurs due to the syntax errors in the code. Today, in this write-up, we have discussed how our Support Engineers fixed this error for our customers.

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

    SELECT "Branch", "Zip_5", "CountofStops", avg("EarlyTime") As 
    "Average_Arrival_Time"
    FROM
    (SELECT branch_id as "Branch", substring(stop_zip_postal_code, 1, 5) as 
    "Zip_5", count(stop_name) as "CountofStops", min(actual_arrival_time) as 
    "EarlyTime"

    FROM distribution_stop_information

    WHERE company_no = '001' AND route_date > '3/13/2017'

    GROUP BY branch_id, stop_zip_postal_code)
    inner

    GROUP BY "Branch", "Zip_5"

    ORDER BY Zip_5

********** Error **********

ERROR: subquery in FROM must have an alias
SQL state: 42601
Hint: For example, FROM (SELECT ...) [AS] foo.

2 ответа

Лучший ответ

SELECT Branch, Zip_5, CountofStops, avg(EarlyTime) As Average_Arrival_Time
FROM (SELECT branch_id as Branch, left(stop_zip_postal_code, 5) as Zip_5, 
             count(stop_name) as CountofStops,
             min(actual_arrival_time) as EarlyTime
      FROM distribution_stop_information
      WHERE company_no = '001' AND route_date > '2017-03-13'
      GROUP BY branch_id, stop_zip_postal_code
     ) b
GROUP BY Branch, Zip_5
ORDER BY Zip_5;

It’s a little tricky…there are a couple things going on.

• When peewee encounters a «bare» sub-select, it will only select the primary key. This is because commonly you use a sub-select to filter on the primary key. To work around this, you need to manually specify which columns to select in your subquery.
• When peewee builds the outer query, it uses the alias t1, so you need to specify that as the alias for the sub-select.

The following test-case is passing on both sqlite and postgres:

class FromMultiTableTestCase(ModelTestCase):
    requires = [Blog, Comment, User]

    def setUp(self):
        super(FromMultiTableTestCase, self).setUp()

        for u in range(2):
            user = User.create(username='u%s' % u)
            for i in range(3):
                b = Blog.create(user=user, title='b%s-%s' % (u, i))
                for j in range(i):
                    Comment.create(blog=b, comment='c%s-%s' % (i, j))

    def test_from_multi_table(self):
        q = (Blog
             .select(Blog, User)
             .from_(Blog, User)
             .where(
                 (Blog.user == User.id) &
                 (User.username == 'u0'))
             .order_by(Blog.pk)
             .naive())

        qc = len(self.queries())
        blogs = [b.title for b in q]
        self.assertEqual(blogs, ['b0-0', 'b0-1', 'b0-2'])

        usernames = [b.username for b in q]
        self.assertEqual(usernames, ['u0', 'u0', 'u0'])
        self.assertEqual(len(self.queries()) - qc, 1)

    def test_subselect(self):
        inner = User.select(User.username)
        self.assertEqual(
            [u.username for u in inner.order_by(User.username)], ['u0', 'u1'])

        # Have to manually specify the alias as "t1" because the outer query
        # will expect that.
        outer = (User
                 .select(User.username)
                 .from_(inner.alias('t1')))
        sql, params = compiler.generate_select(outer)
        self.assertEqual(sql, (
            'SELECT users."username" FROM '
            '(SELECT users."username" FROM "users" AS users) AS t1'))

        self.assertEqual(
            [u.username for u in outer.order_by(User.username)], ['u0', 'u1'])