Bug with large results

Bug with large results

[Jeffrey Melloy]

I downloaded RHDB-explain (CVS) and stumbled across a bug.

Small queries work fine, but a larger plan will fail with a null  
pointer exception.

Using PostgreSQL 7.4.5 on OS X.

Thanks,
Jeff

Error:
java.lang.NullPointerException
         at  
com.redhat.rhdb.explain.ExplainParserV73.addQual(ExplainParserV73.java: 
796)
         at  
com.redhat.rhdb.explain.ExplainParserV73.buildExplainTree(ExplainParserV 
73.java:343)
         at  
com.redhat.rhdb.explain.ExplainParserV73.explain(ExplainParserV73.java: 
187)
         at  
com.redhat.rhdb.explain.Explain$ExplainThread.run(Explain.java:298)


Query:
select * from message_v;

plan:
                                                 QUERY PLAN
------------------------------------------------------------------------ 
-----------------------------------
  Hash Join  (cost=34961.26..3454046.29 rows=155607 width=164)
    Hash Cond: ("outer".sender_id = "inner".user_id)
    Join Filter: (("inner".effdate <= "outer".message_date) AND (NOT  
(subplan)))
    ->  Hash Join  (cost=34923.29..2628055.36 rows=50978 width=115)
          Hash Cond: ("outer".recipient_id = "inner".user_id)
          Join Filter: (NOT (subplan))
          ->  Merge Join  (cost=34916.77..50555.99 rows=297841 width=105)
                Merge Cond: ("outer".user_id = "inner".recipient_id)
                Join Filter: ("outer".effdate <= "inner".message_date)
                ->  Sort  (cost=46.03..47.35 rows=526 width=39)
                      Sort Key: r_disp.user_id
                      ->  Seq Scan on user_display_name r_disp   
(cost=0.00..22.26 rows=526 width=39)
                ->  Sort  (cost=34870.74..35279.24 rows=163399 width=66)
                      Sort Key: m.recipient_id
                      ->  Seq Scan on messages m  (cost=0.00..6361.99  
rows=163399 width=66)
          ->  Hash  (cost=5.81..5.81 rows=281 width=26)
                ->  Seq Scan on users r  (cost=0.00..5.81 rows=281  
width=26)
          SubPlan
            ->  Index Scan using im_display_user on user_display_name  
udn  (cost=0.00..8.64 rows=1 width=0)
                  Index Cond: (user_id = $4)
                  Filter: ((effdate > $3) AND (effdate <= $2))
    ->  Hash  (cost=36.66..36.66 rows=526 width=65)
          ->  Hash Join  (cost=6.51..36.66 rows=526 width=65)
                Hash Cond: ("outer".user_id = "inner".user_id)
                ->  Seq Scan on user_display_name s_disp   
(cost=0.00..22.26 rows=526 width=39)
                ->  Hash  (cost=5.81..5.81 rows=281 width=26)
                      ->  Seq Scan on users s  (cost=0.00..5.81 rows=281  
width=26)
    SubPlan
      ->  Index Scan using im_display_user on user_display_name udn   
(cost=0.00..8.64 rows=1 width=0)
            Index Cond: (user_id = $1)
            Filter: ((effdate > $0) AND (effdate <= $2))
(31 rows)

Re: Bug with large results

[Fernando Nasser]

Hi Jeffrey,

It is not the size of the result.  It happens that a new type of node 
was added to the 7.4 version after we had released this version.

There is a patch, posted to this list, by Peter Eisentraut, that 
provides a workaround for this (it does not add the new nodes but 
prevents the NPE).

I did not incorporate it as I keep expecting someone will send a patch 
that includes code for all the new nodes, or that I myself find the time 
to do it.  In any case, it should not throw exception if a unknown type 
of node is found, just print a warning or something.

If you have problems obtaining it from the list archives or rebuilding 
rhdb-explain please send me a note and I will try and help.

Regards,
Fernando

Jeffrey Melloy wrote:
> I downloaded RHDB-explain (CVS) and stumbled across a bug.
> 
> Small queries work fine, but a larger plan will fail with a null  
> pointer exception.
> 
> Using PostgreSQL 7.4.5 on OS X.
> 
> Thanks,
> Jeff
> 
> Error:
> java.lang.NullPointerException
>         at  
> com.redhat.rhdb.explain.ExplainParserV73.addQual(ExplainParserV73.java: 
> 796)
>         at  
> com.redhat.rhdb.explain.ExplainParserV73.buildExplainTree(ExplainParserV 
> 73.java:343)
>         at  
> com.redhat.rhdb.explain.ExplainParserV73.explain(ExplainParserV73.java: 
> 187)
>         at  
> com.redhat.rhdb.explain.Explain$ExplainThread.run(Explain.java:298)
> 
> 
> Query:
> select * from message_v;
> 
> plan:
>                                                 QUERY PLAN
> ------------------------------------------------------------------------ 
> -----------------------------------
>  Hash Join  (cost=34961.26..3454046.29 rows=155607 width=164)
>    Hash Cond: ("outer".sender_id = "inner".user_id)
>    Join Filter: (("inner".effdate <= "outer".message_date) AND (NOT  
> (subplan)))
>    ->  Hash Join  (cost=34923.29..2628055.36 rows=50978 width=115)
>          Hash Cond: ("outer".recipient_id = "inner".user_id)
>          Join Filter: (NOT (subplan))
>          ->  Merge Join  (cost=34916.77..50555.99 rows=297841 width=105)
>                Merge Cond: ("outer".user_id = "inner".recipient_id)
>                Join Filter: ("outer".effdate <= "inner".message_date)
>                ->  Sort  (cost=46.03..47.35 rows=526 width=39)
>                      Sort Key: r_disp.user_id
>                      ->  Seq Scan on user_display_name r_disp   
> (cost=0.00..22.26 rows=526 width=39)
>                ->  Sort  (cost=34870.74..35279.24 rows=163399 width=66)
>                      Sort Key: m.recipient_id
>                      ->  Seq Scan on messages m  (cost=0.00..6361.99  
> rows=163399 width=66)
>          ->  Hash  (cost=5.81..5.81 rows=281 width=26)
>                ->  Seq Scan on users r  (cost=0.00..5.81 rows=281  
> width=26)
>          SubPlan
>            ->  Index Scan using im_display_user on user_display_name  
> udn  (cost=0.00..8.64 rows=1 width=0)
>                  Index Cond: (user_id = $4)
>                  Filter: ((effdate > $3) AND (effdate <= $2))
>    ->  Hash  (cost=36.66..36.66 rows=526 width=65)
>          ->  Hash Join  (cost=6.51..36.66 rows=526 width=65)
>                Hash Cond: ("outer".user_id = "inner".user_id)
>                ->  Seq Scan on user_display_name s_disp   
> (cost=0.00..22.26 rows=526 width=39)
>                ->  Hash  (cost=5.81..5.81 rows=281 width=26)
>                      ->  Seq Scan on users s  (cost=0.00..5.81 rows=281  
> width=26)
>    SubPlan
>      ->  Index Scan using im_display_user on user_display_name udn   
> (cost=0.00..8.64 rows=1 width=0)
>            Index Cond: (user_id = $1)
>            Filter: ((effdate > $0) AND (effdate <= $2))
> (31 rows)
> 
> 

-- 
Fernando Nasser
Red Hat Canada Ltd.                     E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9