Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 - Mailing list pgsql-bugs

From Rajni Bobal
Subject Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4
Date
Msg-id CA+UiKXY60DYXsQZcqNUeQ-V6DRa8T4nS6Db3qYbpsW51iWPveA@mail.gmail.com
Whole thread Raw
In response to Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4  ("David G. Johnston" <david.g.johnston@gmail.com>)
Responses Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4
List pgsql-bugs
Hello SIr,
Please clarify about why $1 is not been replaced by 'colname' in delete_from_table2 function.

regards,

On Wed, Jan 22, 2025 at 10:13 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
You seem to have reversed which query you deem correct...

On Wed, Jan 22, 2025 at 8:59 AM Rajni Bobal <rajnibobal@gmail.com> wrote:

        || quote_ident(colname)
        || ' IN (SELECT '
        || quote_ident(colname)
        || ' FROM '
        || quote_ident(sel_tblname)

This deletes rows from the table depending upon finding the same value in colname in the subquery.


        ' where $1 in (select $1 from ' || quote_ident(sel_tblname) ||

This deletes rows from the table depending only upon finding at least one row in the subquery - the limit is basically pointless.  Since all rows return the value provided in $1, and $1 = $1.

So these indeed produce different results, as they should.  The "error" one is behaving exactly as it should.  If it doesn't do what you want - which is likely as it is a very unusual query - don't use it.

David J.

pgsql-bugs by date:

Previous
From: PG Bug reporting form
Date:
Subject: BUG #18783: 2025-01-23 03:55:06.243 GMT [22929] LOG: postmaster became multithreaded 2025-01-23 03:55:06.243 GM
Next
From: Richard Guo
Date:
Subject: ERROR: tuple to be updated was already modified by an operation triggered by the current command