Error subquery has too many columns - Исправление ошибок и поиск оптимальных решений проблем

Bug description

I’m working on a multi tenant data model, where every model has a tenantId field and a unique composite index ( tenantId, id ), and every foreign key involves the tenantId.

Example schema:

model Tenant {
  id   String @id @default(cuid())
  name String

  foos Foo[]
  bars Bar[]
}

model Foo {
  id       String @id @default(cuid())
  tenantId String

  bar    Bar?
  tenant Tenant @relation(fields: [tenantId], references: [id])

  @@unique([tenantId, id])
}

model Bar {
  id       String @id @default(cuid())
  fooId    String
  tenantId String

  foo    Foo    @relation(fields: [tenantId, fooId], references: [tenantId, id])
  tenant Tenant @relation(fields: [tenantId], references: [id])

  @@unique([tenantId, id])
}

The one-to-one relation between Foo and Bar is the source of the problem. A Foo can be created without a Bar, but a Bar needs a Foo, that’s why the relation field is on the Bar model. I didn’t find a way to make the relation bidirectional ( having a barId field on the Foo model as well ) without having Prisma yelling at me ( I guess Prisma doesn’t support bidirectional relations at all at the moment? ).

That said, if I want to query for all Foo rows that do not have a related Bar, this is the query according to Prisma docs:

const foos = await db.foo.findMany({
    where: {
      bar: {
        is: null,
      },
    },
  });

Which, unfortunately, translates to the following SQL query:

SELECT
  "public"."Foo"."id",
  "public"."Foo"."tenantId"
FROM
  "public"."Foo"
WHERE ("public"."Foo"."id")
NOT IN(
  SELECT
    "public"."Bar"."tenantId", "public"."Bar"."fooId" FROM "public"."Bar"
  WHERE ("public"."Bar"."tenantId" IS NOT NULL
    AND "public"."Bar"."fooId" IS NOT NULL))
OFFSET 0;

The subquery for the NOT IN clause SELECTs both the tenantId and fooId columns, which obviously causes a subquery has too many columns error.

How to reproduce

Here you can find a repo with an isolated reproduction case: https://github.com/darioielardi/prisma-subquery-has-too-many-columns.

Expected behavior

No response

Prisma information

I’ve included everything in the first section.

Environment & setup

OS: macOS
Database: PostgreSQL
Node.js version: v14.15.3
Prisma version:

prisma               : 2.22.1
@prisma/client       : 2.22.1
Current platform     : darwin
Query Engine         : query-engine 60cc71d884972ab4e897f0277c4b84383dddaf6c (at node_modules/@prisma/engines/query-engine-darwin)
Migration Engine     : migration-engine-cli 60cc71d884972ab4e897f0277c4b84383dddaf6c (at node_modules/@prisma/engines/migration-engine-darwin)
Introspection Engine : introspection-core 60cc71d884972ab4e897f0277c4b84383dddaf6c (at node_modules/@prisma/engines/introspection-engine-darwin)
Format Binary        : prisma-fmt 60cc71d884972ab4e897f0277c4b84383dddaf6c (at node_modules/@prisma/engines/prisma-fmt-darwin)
Default Engines Hash : 60cc71d884972ab4e897f0277c4b84383dddaf6c
Studio               : 0.379.0

Источник

Я пытаюсь сделать запрос с помощью postgresql.
База данных содержит два отношения:» Королевство», в которое входят некоторые английские короли, и» династия», в которой содержатся некоторые люди из династии Стюарта

отношение «королевство» включает в себя имя короля и когда его королевство началось и закончилось. Отношение «династия» включает в себя имя, пол, рождение и смерть.

С моим запросом я получение этой ошибки в строке 3 (не в):subquery has too many columns

Это запрос:

SELECT kingdom.king, dinasty.birth, dinasty.death
FROM kingdom, dinasty
WHERE kingdom.king = dinasty.name AND kingdom.king NOT IN
(
    SELECT DISTINCT R1.king, R1.birth, R1.death
    FROM
    (
        SELECT DISTINCT R1.king, D1.birth, D1.death
        FROM kingdom AS R1, dinasty AS D1, dinasty AS D2
        WHERE R1.king=D1.name
    ) AS R1, 
    (
        SELECT DISTINCT R1.king, D1.birth, D1.death
        FROM kingdom AS R1, dinasty AS D1, dinasty AS D2
        WHERE R1.king=D1.name
    ) AS R2
    WHERE R1.death-R1.birth < R2.death-R2.birth
);

то, что находится внутри не в правильно.

2 ответов

вы проецируете три столбцы в вашем подзапросе, но сравнение одного один их в IN предложения. Выберите только необходимый столбец (r1.king) для IN в подзапросе:

SELECT kingdom.king, dinasty.birth, dinasty.death
FROM kingdom, dinasty
WHERE kingdom.king = dinasty.name AND kingdom.king NOT IN
(
    SELECT DISTINCT R1.king
    FROM
    (
        SELECT DISTINCT R1.king, D1.birth, D1.death
        FROM kingdom AS R1, dinasty AS D1, dinasty AS D2
        WHERE R1.king=D1.name
    ) AS R1, 
    (
        SELECT DISTINCT R1.king, D1.birth, D1.death
        FROM kingdom AS R1, dinasty AS D1, dinasty AS D2
        WHERE R1.king=D1.name
    ) AS R2
    WHERE R1.death-R1.birth < R2.death-R2.birth
);

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

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

SELECT *, death-birth AS lived_for FROM dinasty

теперь, когда у вас есть это, вы можете использовать DISTINCT ON, чтобы найти самого долгоживущего короля для каждого королевства

SELECT DISTINCT ON( name ) name, birth, death, lived_for
  FROM (
      SELECT *, death-birth AS lived_for FROM dinasty
    ) a
  ORDER BY name, lived_for DESC
;

distinct on будет принимать первую строку для каждого отдельного значения, поэтому важно, чтобы вы связали его с правильный ORDER BY. Сначала мы заказываем по имени династии, затем, как долго жил король в порядке убывания. Это означает, что первый король, показанный для каждой династии, будет самым долгоживущим, и это запись, которая будет сохраняться для каждой династии.

обратите внимание, что я также удалил соединение с kindgom, но при необходимости вы можете добавить его обратно:

SELECT k.*, oldest.*
  FROM (
    SELECT DISTINCT ON( name ) name, birth, death, lived_for
      FROM (
          SELECT *, death-birth AS lived_for FROM dinasty
        ) a
      ORDER BY name, lived_for DESC
    ) oldest
    JOIN kingdom k ON k.king = oldest.name
;

наконец, если вам когда-либо нужно использовать несколько столбцов в под-выборе, вы можете использовать ROW () construct:

SELECT ...
  FROM table_a
  WHERE ROW(f1, f2, f3) NOT IN (SELECT f1a, f2a, f3a FROM ... )
;

Источник

Hi,

Does anyone know how to restrict the number of columns selected by the elements()» function? I can’t seem to figure it out, but I got an impression that Postgres only accepts a single column selects for ANY clauses.

Here is an example. The query below should get all rooms where the list of room codes contain codes with ‘BK’ and ‘DE’.

Code:

from EnumerationFieldValue as instanceValues
where instanceValues.field = ‘room_code’
and ‘BK’ = any elements(instanceValues.values)
and ‘DE’ = any elements(instanceValues.values)

It translates to the following SQL query:

Code:

select
enumeratio0_.field_value_id as field1_158_,
enumeratio0_1_.field as field158_,
enumeratio0_1_.listing_id as listing3_158_
from
EnumerationFieldValue enumeratio0_
inner join
FieldValue enumeratio0_1_
on enumeratio0_.field_value_id=enumeratio0_1_.field_value_id
where
enumeratio0_1_.field=’room_code’
and ‘BK’=any (
select
values1_.name,
values1_.shortName,
values1_.code,
values1_.parent
from
EnumerationFieldValue_values values1_
where
enumeratio0_.field_value_id=values1_.EnumerationFieldValue_field_value_id
)
and ‘DE’=any (
select
values2_.name,
values2_.shortName,
values2_.code,
values2_.parent
from
EnumerationFieldValue_values values2_
where
enumeratio0_.field_value_id=values2_.EnumerationFieldValue_field_value_id
)
and ‘SU’=any (
select
values3_.name,
values3_.shortName,
values3_.code,
values3_.parent
from
EnumerationFieldValue_values values3_
where
enumeratio0_.field_value_id=values3_.EnumerationFieldValue_field_value_id
)

Which gives

Code:

ERROR: subquery has too many columns

The modified query from above works like charm:

Code:

select
enumeratio0_.field_value_id as field1_158_,
enumeratio0_1_.field as field158_,
enumeratio0_1_.listing_id as listing3_158_
from
EnumerationFieldValue enumeratio0_
inner join
FieldValue enumeratio0_1_
on enumeratio0_.field_value_id=enumeratio0_1_.field_value_id
where
enumeratio0_1_.field=’room_code’
and ‘BK’=any (
select
values1_.code
from
EnumerationFieldValue_values values1_
where
enumeratio0_.field_value_id=values1_.EnumerationFieldValue_field_value_id
)
and ‘DE’=any (
select
values2_.code
from
EnumerationFieldValue_values values2_
where
enumeratio0_.field_value_id=values2_.EnumerationFieldValue_field_value_id
)
and ‘SU’=any (
select
values3_.code
from
EnumerationFieldValue_values values3_
where
enumeratio0_.field_value_id=values3_.EnumerationFieldValue_field_value_id
)

Can anyone please point out what I am doing wrong with this one? Thank you very much in advance.

Best regards,
Nick.

Hibernate version: 3.2.4

Mapping documents:

Code:

@Entity
public class EnumerationFieldValue {

@CollectionOfElements
@Embedded
private List<EnumerationOption> values =
new ArrayList<EnumerationOption>();

…
}

@Embeddable
public class EnumerationOption implements Serializable {

private static final long serialVersionUID = 1L;

private String name;

private String shortName;

private String code;

@Type(type=»persistence.FieldUserType»)
private EnumerationField parent;

…

Code between sessionFactory.openSession() and session.close(): N/A

Full stack trace of any exception that occurs: N/A

Name and version of the database you are using: PostgreSQL 8.2

The generated SQL (show_sql=true): See above

Debug level Hibernate log excerpt: N/A

Источник

Я использую postgreSQL 10 и пытаюсь создать довольно сложный запрос.

Вот

select clothes_id , true as iscon 
 from archive
 where 
 customer_id = 101 and 
 clothes_id <> 1 and 
 shoes_id is null  and 
 clothes_id is not null and 
clothes_id not in 
( select shoes_id, clothes_id from archive where customer_id in
  ( select customer_id from archive where customer_id <> 101 and clothes_id = 1 )  
)

Этот запрос дает ошибку

> ERROR:  subquery has too many columns LINE 5:                
> clothes_id not in ( select shoes_id, clo...
>                                         ^ SQL state: 42601 Character: 147

Как я могу это исправить?

Если вы хотите знать, что пытается сделать этот запрос, чтобы отладить, прочтите ниже

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

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

Итак, имея такой экземпляр архивной таблицы

clothes - customer - shoes
1          101       2
1          101       6
24         101       null
24         3         2

Запрос не вернет одежду 24, потому что покупатель 3 также ее заказал. Таким образом, он ничего не вернет.

Спасибо

Ответы
3

IN сопоставляет левую часть с правой, поэтому в результирующем наборе с правой стороны должен быть только один столбец. Вы можете сделать СОЮЗ, если хотите.

clothes_id not in 
  ( select shoes_id from archive ...
    union
    select clothes_id from archive ...

Или вы можете сделать два IN

clothes_id not in 
  ( select shoes_id from archive ...
and clothes_id not in
  ( select clothes_id from archive ...

В предложении IN отсутствует еще одно сравнение столбцов — shoes_id

select clothes_id, true as iscon 
 from archive
 where 
 customer_id = 101 and 
 clothes_id <> 1 and 
 shoes_id is null  and 
 clothes_id is not null and 
 (shoes_id, clothes_id) not in 
 (select shoes_id, clothes_id from archive where customer_id in
     (select customer_id from archive where customer_id <> 101 and clothes_id = 1)  
 )

Вы не в предложении не соответствует количеству и столбцу, вы должны уменьшить количество столбца в выборе

    select clothes_id , true as iscon 
     from archive
     where 
     customer_id = 101 and 
     clothes_id <> 1 and 
     shoes_id is null  and 
     clothes_id is not null and 
     clothes_id not in 
    ( select  clothes_id from archive where customer_id in
      ( select customer_id from archive where customer_id <> 101 and clothes_id = 1 )  
    )

Или вы можете попробовать использовать кортеж для сравнения результата предложения NOT IN

    select clothes_id , true as iscon 
     from archive
     where 
     customer_id = 101 and 
     clothes_id <> 1 and 
     shoes_id is null  and 
     clothes_id is not null and 
    (shoes_id, clothes_id) not in 
    ( select shoes_id, clothes_id from archive where customer_id in
      ( select customer_id from archive where customer_id <> 101 and clothes_id = 1 )  
    )

Другие вопросы по теме

Источник

Bug description

How to reproduce

Expected behavior

Prisma information

Environment & setup

2 ответов

Ответы 3

Другие вопросы по теме

Ответы
3