Thread: BUG #1913: select into a table missing a row part 2

The following bug has been logged online:

Bug reference:      1913
Logged by:          Keith Randall
Email address:      randallk@gmail.com
PostgreSQL version: 8.1beta2
Operating system:   Linux 2.4.21-15 Redhat enterprise
Description:        select into a table missing a row part 2
Details:

I'm missing a row when I generate a table off a view.


select count(*) from foo;

  20811

select * into temp from foo;
select count(*) from temp;

  20810


I have scripts that you can run to replicate at
https://www.oas.ca/~randallk/pgbug.tar.bz2

On Tue, Sep 27, 2005 at 09:17:18AM +0100, Keith Randall wrote:

> I'm missing a row when I generate a table off a view.
>
> select count(*) from foo;
>
>   20811
>
> select * into temp from foo;
> select count(*) from temp;
>
>   20810

Interesting.  I can duplicate this behavior here.  However, what's even
more interesting, is that 8.0 returns 20814 rows in both cases, so
something _is_ wrong.

It may be related to the latest outer join bug, which Tom Lane fixed
yesterday or the day before.  I'll recompile and retest.

--
Alvaro Herrera                  http://www.amazon.com/gp/registry/5ZYLFMCVHXC
"Uno combate cuando es necesario... ¡no cuando está de humor!
El humor es para el ganado, o para hacer el amor, o para tocar el
baliset.  No para combatir."  (Gurney Halleck)

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> It may be related to the latest outer join bug, which Tom Lane fixed
> yesterday or the day before.  I'll recompile and retest.

That was my first thought also.  Does the plan for the select-from-view
involve a hash join?  Have you finished testing with CVS tip yet?

            regards, tom lane

On Tue, Sep 27, 2005 at 10:37:06AM -0400, Tom Lane wrote:
> Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> > It may be related to the latest outer join bug, which Tom Lane fixed
> > yesterday or the day before.  I'll recompile and retest.
>
> That was my first thought also.  Does the plan for the select-from-view
> involve a hash join?  Have you finished testing with CVS tip yet?

Yes, it does involve hash joins, and yes I did test CVS tip, and the
problem doesn't happen there; or at least I can't reproduce it.

--
Alvaro Herrera                  http://www.amazon.com/gp/registry/DXLWNGRJD34
"Everybody understands Mickey Mouse. Few understand Hermann Hesse.
Hardly anybody understands Einstein. And nobody understands Emperor Norton."