pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17818: CTE is not returning the right data.
3+ messages / 3 participants
[nested] [flat]
* BUG #17818: CTE is not returning the right data.
@ 2023-03-02 08:33 PG Bug reporting form <noreply@postgresql.org>
2023-03-02 13:28 ` BUG #17818: CTE is not returning the right data. David G. Johnston <david.g.johnston@gmail.com>
2023-03-02 15:41 ` Re: BUG #17818: CTE is not returning the right data. Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 3+ messages in thread
From: PG Bug reporting form @ 2023-03-02 08:33 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: cute_hoay88@hotmail.com
The following bug has been logged on the website:
Bug reference: 17818
Logged by: Shiau Hoay Ong
Email address: cute_hoay88@hotmail.com
PostgreSQL version: 12.1
Operating system: window
Description:
Hi Postgresql Support Team, today i encounter a mystery case. The Stored
Procedure is already used for sometime, but today there are some issue in
this Stored Procedure which causing the data got deleted. The scenario is
like sample below :
WITH cte_primary_data AS
(
SELECT mr.id, f.feature, mr.item, mr.data_type_id
FROM public.primary_tables mr
INNER JOIN public.feature_tables f
ON f.feature_id = mr.feature_id
WHERE mr.id = 3163
AND mr.team_id = 23
AND mr.stage_id = 2
)
SELECT * FROM cte_primary_data l
INNER JOIN temp_flat_data f
ON l.feature = f.feature
AND l.item = f.item
Scenario: When i execute this above query, i am not able to find the feature
AA and Item AA EXT Inner-Lane- 1.1G.
But weird is i tried out with this (below example: Add WHERE inside the
cte), then i am able to get the item:
WITH cte_primary_data AS
(
SELECT mr.id, f.feature, mr.item, mr.data_type_id
FROM public.primary_tables mr
INNER JOIN public.feature_tables f
ON f.feature_id = mr.feature_id
WHERE mr.id = 3163
AND mr.team_id = 23
AND mr.stage_id = 2
AND mr.item = 'AA EXT Inner-Lane- 1.1G'
AND f.feature = 'AA'
)
SELECT * FROM cte_primary_data l
INNER JOIN temp_flat_data f
ON l.feature = f.feature
AND l.item = f.item
I do try out with Create Temp Table also work fine with the sample without
need filtering (as filtering will only reduce data record set).
Can you please help to investigate and explain what is the possible root
cause for this?
Thanks!
^ permalink raw reply [nested|flat] 3+ messages in thread
* BUG #17818: CTE is not returning the right data.
2023-03-02 08:33 BUG #17818: CTE is not returning the right data. PG Bug reporting form <noreply@postgresql.org>
@ 2023-03-02 13:28 ` David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: David G. Johnston @ 2023-03-02 13:28 UTC (permalink / raw)
To: cute_hoay88@hotmail.com <cute_hoay88@hotmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Thursday, March 2, 2023, PG Bug reporting form <noreply@postgresql.org>
wrote:
> The following bug has been logged on the website:
>
> Bug reference: 17818
> Logged by: Shiau Hoay Ong
> Email address: cute_hoay88@hotmail.com
> PostgreSQL version: 12.1
> Operating system: window
> Description:
>
> Hi Postgresql Support Team, today i encounter a mystery case. The Stored
> Procedure is already used for sometime, but today there are some issue in
> this Stored Procedure which causing the data got deleted. The scenario is
> like sample below :
>
>
> I do try out with Create Temp Table also work fine with the sample without
> need filtering (as filtering will only reduce data record set).
> Can you please help to investigate and explain what is the possible root
> cause for this?
> Thanks!
>
>
Upgrade to a supported minor release and probably rebuild all indexes
(though being windows I’m less sure about collation issues…)
David J.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #17818: CTE is not returning the right data.
2023-03-02 08:33 BUG #17818: CTE is not returning the right data. PG Bug reporting form <noreply@postgresql.org>
@ 2023-03-02 15:41 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2023-03-02 15:41 UTC (permalink / raw)
To: cute_hoay88@hotmail.com; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> PostgreSQL version: 12.1
> Scenario: When i execute this above query, i am not able to find the feature
> AA and Item AA EXT Inner-Lane- 1.1G.
> But weird is i tried out with this (below example: Add WHERE inside the
> cte), then i am able to get the item:
I agree that's strange, but ...
> Can you please help to investigate and explain what is the possible root
> cause for this?
Nobody is going to be able to help you from that amount of information.
I would suggest, if you really are running 12.1, that your first step
should be to update to 12.latest (currently 12.14), as you are missing
more than three years worth of bug fixes. Another useful step could be
to re-index these tables, just in case the problem is index corruption.
(Do that *after* updating.)
If you still see strange behavior, and can provide a self-contained test
case, we'd be happy to look at it.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2023-03-02 15:41 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-03-02 08:33 BUG #17818: CTE is not returning the right data. PG Bug reporting form <noreply@postgresql.org>
2023-03-02 13:28 ` David G. Johnston <david.g.johnston@gmail.com>
2023-03-02 15:41 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox