Error missing from clause entry for table rank

I’m trying to execute a join query for 4 tables on postgres.

Table names:

• scenarios_scenario
• payments_invoice
• payments_payment
• payments_action

(all those weird names are generated by django -)))

Relations:

• scenarios_scenario [has many] payments_actions
• payments_action [has one] payments_invoice
• payments_action [has one] payments_payment

Below one is a working query,

SELECT payments_invoice.*,
    (payments_payment.to_be_paid - payments_payment.paid) as remaining, 
    payments_action.identificator
FROM payments_invoice
JOIN payments_payment
  ON payments_invoice.action_id = payments_payment.action_id
  AND payments_payment.full_payment=2
JOIN payments_action
  ON payments_invoice.action_id = payments_action.id
  AND payments_action.identificator = %s

I just want to retrieve a related field from another table and wrote another query like

SELECT 
  scenarios_scenario.title, payments_invoice.*, 
  (payments_payment.to_be_paid - payments_payment.paid) as remaining, 
  payments_action.identificator, payments_action.scenario_id 
FROM payments_invoice 
JOIN scenarios_scenario 
  ON scenarios_scenario.id = payments_action.scenario_id
JOIN payments_payment 
  ON payments_invoice.action_id = payments_payment.action_id 
  AND payments_payment.full_payment=2 
JOIN payments_action 
  ON payments_invoice.action_id = payments_action.id 
  AND payments_action.identificator = 'EEE45667';

but facing with this error —

ERROR:  missing FROM-clause entry for table "payments_action"
LINE 2: ...IN  scenarios_scenario ON scenarios_scenario.id = payments_a...
                                                             ^

Looked across SO for similar questions like this (missing FROM-clause entry for table) but weren’t able to find a way. Any help would be appreciated.

Database.Guide

Beginners

Categories

• Azure SQL Edge (16)
• Database Concepts (48)
• Database Tools (70)
• DBMS (8)
• MariaDB (420)
• Microsoft Access (17)
• MongoDB (265)
• MySQL (375)
• NoSQL (7)
• Oracle (296)
• PostgreSQL (255)
• Redis (185)
• SQL (588)
• SQL Server (888)
• SQLite (235)

Fix “ERROR: missing FROM-clause entry for table” in PostgreSQL when using UNION, EXCEPT, or INTERSECT

If you’re getting “ERROR: missing FROM-clause entry for table” in PostgreSQL when using an operator such as UNION , INTERSECT , or EXCEPT , it could be because you’re qualifying a column name with its table name.

To fix this, either remove the table name or use a column alias.

Example of Error

Here’s an example of code that produces the error:

In this case I tried to order the results by the TeacherName column, but I qualified that column with the table name (I used Teachers.TeacherName to reference the column name).

Referencing tables like this doesn’t work when ordering the results of UNION , EXCEPT , or INTERSECT .

Solution 1

One way to fix this issue is to remove the table name from the ORDER BY clause:

Solution 2

Another way to fix it is to use an alias for the column:

With this option, we assign an alias to the column, and then reference that alias in the ORDER BY clause.

Источник

Missing FROM-clause entry for table « », что делать?

• Вопрос задан более двух лет назад
• 3618 просмотров

Простой 4 комментария

В FROM нужно указать таблицу teterika.users и условие связи с таблицей teterika.lessons.
если условие не указать, то свяжется каждая строка одной таблицы с каждой строкой другой таблицы, получится декартово произведение таблиц.

Кстати, ваш запрос не имеет смысла, потому что из teterika.users.role у вас извлечется только запись ‘tutor’ в соответствии с условием. Т.е. ваш запрос можно заменить на:

Разве что вам действительно нужно получить декартово произведение всех уроков со всеми учителями.

А как это называется,когда после запятой в select указывается условие ‘tutor’ ,этому есть название?

И кстати,мне и нужно извлечь только записи с ‘tutor’ , у меня их определённое количество

И ещё ,если моя команда находится долго в процессе обработки и не выводит мой запрос на экран,о чём это может говорить?

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

И ещё ,если моя команда находится долго в процессе обработки и не выводит мой запрос на экран, о чём это может говорить?

Это может говорить о том, что выборка получается очень большая.

Давайте мы с вами поступим иначе. Вы опишите здесь, структуру ваших таблиц. И объясните, какую конкретно выборку надо получить. А я попробую посоветовать вам правильный запрос.

Источник

Missing FROM-clause entry for table « », что делать?

• Вопрос задан более двух лет назад
• 3618 просмотров

Простой 4 комментария

В FROM нужно указать таблицу teterika.users и условие связи с таблицей teterika.lessons.
если условие не указать, то свяжется каждая строка одной таблицы с каждой строкой другой таблицы, получится декартово произведение таблиц.

Кстати, ваш запрос не имеет смысла, потому что из teterika.users.role у вас извлечется только запись ‘tutor’ в соответствии с условием. Т.е. ваш запрос можно заменить на:

Разве что вам действительно нужно получить декартово произведение всех уроков со всеми учителями.

А как это называется,когда после запятой в select указывается условие ‘tutor’ ,этому есть название?

И кстати,мне и нужно извлечь только записи с ‘tutor’ , у меня их определённое количество

И ещё ,если моя команда находится долго в процессе обработки и не выводит мой запрос на экран,о чём это может говорить?

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

И ещё ,если моя команда находится долго в процессе обработки и не выводит мой запрос на экран, о чём это может говорить?

Это может говорить о том, что выборка получается очень большая.

Давайте мы с вами поступим иначе. Вы опишите здесь, структуру ваших таблиц. И объясните, какую конкретно выборку надо получить. А я попробую посоветовать вам правильный запрос.

Источник

findAndCount, error: missing FROM-clause entry for table #7367

When I add limit and offset to this query I get error: missing FROM-clause entry for table «Instruments». When limit and offset are not in this query, it works perfectly.

I expected it to work the same with or without limit or offset. There is just an error:

Dialect: postgres
Database version: 9.5
Sequelize version: 3.30.2

The text was updated successfully, but these errors were encountered:

The issue occurs only when you append limit.I’am also waiting for the fix on this.

This issue will be fixed by appending duplicating:false on include

Appending duplicating: false breaks offset for me — anyone else seeing this? Limit works fine.

With duplicating: false, it fixes the error from happening but instead of limit 10, I get a random number, like 3 or 2 and 2 total pages of results instead of the full 6 or 4 results and 1 total page.

@Mikeysax; this may or may not help you all that much but the way we’ve «worked around» this is to perform the query twice. Once to collect the IDs of the parent entities and another perform the limit and offset. Somewhat thankfully we built an abstraction layer above sequelize that allowed us the flexibility. There’s the obvious and large downside of it requiring two SELECTs on any query that includes a where clause but for us the accuracy outweights the marginal performance concerns (it’s a pretty low percentage of the whole response time).

Here’s a non-generified started for 10:

Not stale. We’re still having to perform a double query and manual count in order to get this working.

This issue contains a code snippet that shows the problem but is not entirely self-contained (i.e. I can’t just copy-paste it and run it). Can someone please provide a SSCCE (also known as MCVE/reprex)?

Same here — adding limit breaks query containing aggregate function logic.

on main query will fix this

Same here — adding limit breaks query containing aggregate function logic.

hi i solved this problem, for you who need count and data match.

if you want to get duplicating data, you must add raw: true, and duplicating: false. and now total data and count must be same. like this :

users.findAndCountAll( <
<
«raw»: true,
«where» : <
«jobid»:req.params.jobid
>,
«attributes»:
Object.keys(this.db.model(«Run»).attributes).concat([
[sequelize.fn(‘COUNT’,sequelize.col(‘messages.id’)),»msg_count»]
]),
«include»:[
<
«model»: this.db.model(«RunMessage»),
«as» : «messages»,
«attributes»:[],
«duplicating: false
>
],
«order»:[[«createdAt»,»DESC»]],
«logging»: console.log,
«limit»: 10,
«offset»: 1
>
>)

Same here — adding limit breaks query containing aggregate function logic.

Источник

Custom Column «ERROR: missing FROM-clause entry for table» Postgres #12304

Describe the bug

Metabase can’t create a custom column even without any calculations on them for some tables.
What these tables have in common is that on the DB they are named e.g. reports__us but on the UI they are shown as «reports us». This seems to break the custom column creation

Logs

Server logs do not show anything about this error. It is like it never happens

To Reproduce
Steps to reproduce the behavior:
0. Try following these on a table with a name like «prices» and a table with a name like «prices__us_today»

1. From home page select your psql DB
2. Choose a schema
3. Choose a table
4. Click on «show editor»
5. Click on «Custom Column»
6. On «field formula» just choose any column and pick a name for the new column
7. Click on «Done»
8. Click on «Visualize»

Expected behavior

Metabase should create a new custom column without any issues. For some other tables it works without any issues but for some others it does not work

Screenshots

Information about your Metabase Installation:
<
«browser-info»: <
«language»: «en-GB»,
«platform»: «MacIntel»,
«userAgent»: «Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.132 Safari/537.36»,
«vendor»: «Google Inc.»
>,
«system-info»: <
«file.encoding»: «UTF-8»,
«java.runtime.name»: «OpenJDK Runtime Environment»,
«java.runtime.version»: «11.0.5+10»,
«java.vendor»: «AdoptOpenJDK»,
«java.vendor.url»: «https://adoptopenjdk.net/»,
«java.version»: «11.0.5»,
«java.vm.name»: «OpenJDK 64-Bit Server VM»,
«java.vm.version»: «11.0.5+10»,
«os.name»: «Linux»,
«os.version»: «4.14.171-136.231.amzn2.x86_64»,
«user.language»: «en»,
«user.timezone»: «GMT»
>,
«metabase-info»: <
«databases»: [
«postgres»
],
«hosting-env»: «unknown»,
«application-database»: «postgres»,
«application-database-details»: <
«database»: <
«name»: «PostgreSQL»,
«version»: «11.6»
>,
«jdbc-driver»: <
«name»: «PostgreSQL JDBC Driver»,
«version»: «42.2.8»
>
>,
«run-mode»: «prod»,
«version»: <
«date»: «2020-04-02»,
«tag»: «v0.35.1»,
«branch»: «release-0.35.x»,
«hash»: «e67f169»
>,
«settings»: <
«report-timezone»: «UTC»
>
>
>

Severity

It is severe since most analyst can do any transformations on this data and makes Metabase way less usable for them

Additional context
Add any other context about the problem here.

The text was updated successfully, but these errors were encountered:

Hi @rubenarevalo
I cannot reproduce your issue. Can you supply a sample table?

I noticed a new pattern. Those tables in which I receive this error have a large number of columns (30

100).
Is there a cap on the number of columns on Metabase that might be causing this issue?

@rubenarevalo No, I’ve had tables with 240 columns.

@flamber But have you tried adding an extra column from the UI for such a large size table
?

@rubenarevalo Can you please supply a sample table?

These are the DDL of two tables. The first one seems to work but the second one causes the issue that I have reported. They are both on the same schema

@rubenarevalo Which one of the tables are similar to prices ? Can you provide a full example with data as well? See #12248 (comment) for how an example could look like.
I’m pretty sure that you’re seeing errors during the sync+scan process, which is probably the root cause of this. Try doing a forced sync+scan via Admin > Databases > (db), and then check Admin > Troubleshooting > Logs for any warnings/errors during that process.

When running the async these are the errors I get this one multiple times:

Also this warn multiple times:

Also there is this info shown:

@rubenarevalo So where does the column type _text come from? I’m guessing it might have something to do with the failed sync. But it seems like the main problem relates to the failed sync, and not the double-underscore.

the double underscore is not the issue. That’s why I changed the title earlier. The only common pattern that I see it is the amount of columns. And the problem still is that when I check a table from my data source in Psql I can not create a custom column sometimes. @flamber

The fields that we complain are of unknown type _text , what are they in the actual schema? Both on the Postgres side and what you get in Admin > Data Model > Your DB > myschema.bad and check «Show original schema»

@sbelak I do not know which field Metabase complains about because it is not shown at all on the logs.
This is how the log looks like for this particular type of Warning.

I reproduced this issue on internal instance (almost 0.35.3), while working on #11519 (comment):

This is very likely fixed with #12328. Mind trying there

. it’s not 🙁 Seems to be a lazyness bug as it only crops up with big tables.

Источник

Hello Everyone.

@pleerock @Kononnable I think this could be possibly reopened.

I started facing the same issue today with TypeORM 0.2.24 using PostgreSQL driver when trying to fetch entities with @ManyToMany relation.

I don’t know why, but the generated query is missing the double quotes which seems to be the reason of missing FROM-clause entry for table errors.

Here is a simplified extract from my entities:

@Entity()
export class ProductOption {

    @ManyToMany(() => OptionValue, (value: OptionValue) => value.options)
    @JoinTable({ name: 'product_option_value' })
    public values: OptionValue[];
}

@Entity()
export class OptionValue {

    @Column({
        type: 'simple-json',
    })
    public value: { baseValue: string, alternativeValue: string }; // just an example

    @ManyToMany(() => ProductOption, (productOption: ProductOption) => productOption.values)
    public options: ProductOption[];
}

See I have both sides of ManyToMany relation defined with a custom-named @JoinTable.

Now, when I try to fetch Options with related OptionValues, e.g.

productOptionRepository.find({ relations: ["values"] });

I get this query generated and this FAILS:

query failed: SELECT ProductOption_values_rid.optionValueId AS «optionValueId», ProductOption_values_rid.productOptionId AS «productOptionId» FROM «option_value» «option_value» INNER JOIN «product_option_value» «ProductOption_values_rid» ON (ProductOption_values_rid.productOptionId = $1 AND ProductOption_values_rid.optionValueId = «option_value».»id») ORDER BY ProductOption_values_rid.optionValueId ASC, ProductOption_values_rid.productOptionId ASC — PARAMETERS: [123]

error: missing FROM-clause entry for table «productoption_values_rid»

Please note the bold parts — they are missing double quotes. I compared that to a different but similar relation in other part of my apps, and there the query includes double quotes! This is what I found in different sources can cause such missing FROM-clause entry for table errors.

@ishan123456789 provided me a hint to check if the simple-json may be causing this issue. So I removed the relation:

productOptionRepository.find();

and voliá! It magically works. There has to be something wrong with the query and/or pivot table being generated automatically by TypeORM for @ManyToMany relation when we have an entity containing simple-json column.

Any help kindly appreciated!

I don’t understand the reasoning here, as I thought I’d created the
temp table a_cte and could freely use it within the scope

You are facing with SQL Error [42P01]: ERROR: missing FROM-clause entry for table "a_cte" error because you never select from a_cte.

Use:

WITH a_cte AS (
     SELECT min(x) AS min_a
     FROM a
)
SELECT b.* 
FROM b  
inner join a_cte on b.y <= a_cte.min_a

https://dbfiddle.uk/Ec_MtFek

WITH Queries (Common Table Expressions)

Edit.

I don’t understand how this is fundamentally different to my usage
though, just has a join instead of a where. Can you write it so that
WHERE works please?

As I already mentioned, when using WITH a_cte AS () you need to use a_cte.

The where is redundant on your case , it is used on the join condition.

Maybe you are confusing the old join syntax which I do not recommend. This is still a join , but written differently

WITH a_cte AS (
     SELECT min(x) AS min_a
     FROM a
)
SELECT * FROM b, a_cte
WHERE b.y <= a_cte.min_a; 

https://dbfiddle.uk/uk_98YSX

I get an error while attempting to solve this query:

Table locales.countries:

Table locales.networks:

And table pricings.charges:

I have multiple prices per each locl_ntwrk_id, so what I am trying to do is to get only the last price per each locl_ntwrk_id.

This is my SQL:

SELECT
    locales.countries.locl_ctry_id,
    locales.countries.icc,

    locales.networks.locl_ntwrk_id,
    locales.networks.locl_ctry_id,

    pricings.charges.locl_ntwrk_id,
    pricings.charges.t_valid,
    pricings.charges.locl_ccy_id,
    pricings.charges.cost

FROM locales.countries

LEFT JOIN locales.networks
    ON locales.networks.locl_ctry_id = locales.countries.locl_ctry_id

LEFT OUTER JOIN (
    SELECT
           pricings.charges.locl_ntwrk_id,
           MAX(pricings.charges.t_valid) AS MaxDate
    FROM pricings.charges
    WHERE pricings.charges.t_valid <= CURRENT_TIMESTAMP(6)
    GROUP BY pricings.charges.locl_ntwrk_id
) AS temp ON temp.locl_ntwrk_id = locales.networks.locl_ntwrk_id

WHERE
    left(locales.countries.icc::text, 2) IN ('38')

Expected outcome if CURRENT_TIMESTAMP is 2019-04-02:

Expected outcome if CURRENT_TIMESTAMP is 2019-03-20: