Thread: BUG #8334: Merge Join drops records

BUG #8334: Merge Join drops records

From
euclid@yandex-team.ru
Date:
The following bug has been logged on the website:

Bug reference:      8334
Logged by:          Euclid Nikiforov
Email address:      euclid@yandex-team.ru
PostgreSQL version: 9.2.0
Operating system:   Linux 3.8.0-11-na
Description:

Hi all


Recently investigating problem in our backend query result, I have found
strange behaviour.


The exact query was:
SELECT r.*, state FROM revision.object_revision r JOIN revision.commit ON id
IN (32397,32403,32404) AND commit_id=id WHERE ((commit_id=32397 AND
(object_id=279638691 OR object_id=279638692)) OR (commit_id=32403 AND
object_id IN (279638118,279638128,279638970)) OR (commit_id=32404 AND
object_id=279638966));


It expected to return 6 rows, but returned only 3 when enable_mergejoin is
'on'
EXPLAIN ANALYZE shows Merge Join at final stage
while with Nested Loop (set enable_mergejoin=off;) everything is fine.


Since data is very huge, here is small synthetic way to reproduce the
behaviuor
In actual environment we don't set any restrictions on planner.
If you manipulate enable_mergejoin or enable_sort, you will see how results
differ.
Seems that merge join fails because of unsorted result from inner loops


Regards
Euclid
---------EXAMPLE--------
SET enable_mergejoin=on;
SET enable_nestloop=off;
SET enable_hashjoin=off;
SET enable_seqscan=off;
SET enable_sort=off;


CREATE TABLE testcase.c
(
  id bigint NOT NULL UNIQUE,
  s bigint,
  CONSTRAINT cpk PRIMARY KEY (id)
);


CREATE TABLE testcase.r
(
  c_id bigint NOT NULL,
  o_id bigint NOT NULL,
  CONSTRAINT rpk PRIMARY KEY (c_id, o_id)
);


CREATE INDEX rpkc ON testcase.r USING btree (c_id);

DELETE FROM testcase.c;
INSERT INTO testcase.c values(1,0);
INSERT INTO testcase.c values(2,0);
INSERT INTO testcase.c values(3,0);


DELETE FROM testcase.r;
INSERT INTO testcase.r values(1,1);
INSERT INTO testcase.r values(2,2);
INSERT INTO testcase.r values(2,3);
INSERT INTO testcase.r values(3,4);
INSERT INTO testcase.r values(3,5);
INSERT INTO testcase.r values(3,6);


SELECT s, r.* FROM testcase.r r JOIN testcase.c ON id IN (1, 2, 3) AND
id=r.c_id
WHERE ((c_id=2 AND (o_id=2 OR o_id=3)) OR
(c_id=3 AND o_id in (4,5,6)) OR (c_id=1 AND o_id=1))

Re: BUG #8334: Merge Join drops records

From
Pavan Deolasee
Date:
On Thu, Jul 25, 2013 at 9:46 PM, <euclid@yandex-team.ru> wrote:

> The following bug has been logged on the website:
>
> Bug reference:      8334
> Logged by:          Euclid Nikiforov
> Email address:      euclid@yandex-team.ru
> PostgreSQL version: 9.2.0
>

Did you try updating to the latest minor release ? I can not reproduce this
on the latest minor release of 9.2 branch.

There seem to be a fix which could be related to this:
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=f2366e584aea41b980f23fcf0a2e118efd7fb09b

Thanks,
Pavan

--
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee

Re: BUG #8334: Merge Join drops records

From
"Euclid Nikiforov"
Date:
Hi Pavan



Thanks for pointing out



Git commit looks like exact fix for our problem,  and I can't reproduce bug
in 9.2.4 on Windows platform



But need to update our linux servers with real data to be sure.



Will return in couple of days.



Regards,

Euclid





From: Pavan Deolasee [mailto:pavan.deolasee@gmail.com]
Sent: Thursday, July 25, 2013 8:53 PM
To: euclid@yandex-team.ru
Cc: Pg Bugs
Subject: Re: [BUGS] BUG #8334: Merge Join drops records



On Thu, Jul 25, 2013 at 9:46 PM, <euclid@yandex-team.ru> wrote:

The following bug has been logged on the website:

Bug reference:      8334
Logged by:          Euclid Nikiforov
Email address:      euclid@yandex-team.ru
PostgreSQL version: 9.2.0



Did you try updating to the latest minor release ? I can not reproduce this
on the latest minor release of 9.2 branch.



There seem to be a fix which could be related to this:

http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=f2366e584a
ea41b980f23fcf0a2e118efd7fb09b



Thanks,

Pavan



--
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee

Re: BUG #8334: Merge Join drops records

From
"Euclid Nikiforov"
Date:
Hello again



Bug can't be reproduced any more

Consider it closed.



Thanks,

Euclid





From: Pavan Deolasee [mailto:pavan.deolasee@gmail.com]
Sent: Thursday, July 25, 2013 8:53 PM
To: euclid@yandex-team.ru
Cc: Pg Bugs
Subject: Re: [BUGS] BUG #8334: Merge Join drops records



On Thu, Jul 25, 2013 at 9:46 PM, <euclid@yandex-team.ru> wrote:

The following bug has been logged on the website:

Bug reference:      8334
Logged by:          Euclid Nikiforov
Email address:      euclid@yandex-team.ru
PostgreSQL version: 9.2.0



Did you try updating to the latest minor release ? I can not reproduce this
on the latest minor release of 9.2 branch.



There seem to be a fix which could be related to this:

http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=f2366e584a
ea41b980f23fcf0a2e118efd7fb09b



Thanks,

Pavan



--
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee