Hi

I have the following problem using PostgreSQL 6.4 on RedHat Linux 5.1 
on x86

using the following table

thplus=> \d envelope

Table    = envelope
+-------------------------+----------------------------------+-------+
|           Field         |              Type                | Length|
+-------------------------+----------------------------------+-------+
| envelope_id             | int4 not null default nextval (  |     4 |
| order_type_id           | int4 not null                    |     4 |
| envelope_name           | varchar() not null               |    32 |
| signed_string           | text                             |   var |
| envelope_comment        | text                             |   var |
| envelope_on_hold        | int2                             |     2 |
| envelope_order_count    | int4                             |     4 |
| envelope_total          | int4                             |     4 |
| envelope_currency       | text                             |   var |
| envelope_modify_time    | datetime                         |     8 |
| state_id                | char()                           |     1 |
+-------------------------+----------------------------------+-------+

thplus=> create index envelope_fk2 on envelope(state_id)

I try to use the following query

thplus=>
explain                                                          
thplus-> select count(*) from envelope where state_id='H' or
state_id='E';
NOTICE:  QUERY PLAN:

Aggregate  (cost=4.10 size=0 width=0)
  ->  Index Scan using envelope_fk2 on envelope  (cost=4.10 size=1
width=4)

EXPLAIN

when actually running it, I get the following:

thplus=> select count(*) from envelope where state_id='H' or
state_id='E';
pqReadData() -- backend closed the channel unexpectedly.
        This probably means the backend terminated abnormally before or
while processing the request.
We have lost the connection to the backend, so further processing is
impossible.  Terminating.


But the following query runs fine:

thplu=> select count(*) from envelope where envelope_id=1 or
envelope_id=3;
count
-----
    2
(1 row)

as well as this

thplus=> select count(*) from envelope where envelope_id=1 or
state_id='E';
count
-----
   12
(1 row)

and this

thplus=> select count(*) from envelope where state_id='H'
thplus-> union
thplus-> select count(*) from envelope where state_id='E';
count
-----
   11
 1140
(2 rows)


So it seems that there is a problem with using indexes in ORs that are
defined over text types

the same crash happened also when using varchar(1) as the type of
state_id

BTW, it does not happen when the state_id is first field 

--------------
Hannu

Wed, 18 Nov 1998 21:26:40 +0200
Hannu Krosing <hannu@trust.ee>
Bug in 6.4 release
> So it seems that there is a problem with using indexes in ORs that are
> defined over text types
> 
> the same crash happened also when using varchar(1) as the type of
> state_id
> 
> BTW, it does not happen when the state_id is first field 

Recreated.  I am on it.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 18 Nov 1998 14:56:22 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Bug in 6.4 release
> Hi
> 
> I have the following problem using PostgreSQL 6.4 on RedHat Linux 5.1 
> on x86
> 
> using the following table
> 
> thplus=> \d envelope
> 
> Table    = envelope
> +-------------------------+----------------------------------+-------+
> |           Field         |              Type                | Length|
> +-------------------------+----------------------------------+-------+
> | envelope_id             | int4 not null default nextval (  |     4 |
> | order_type_id           | int4 not null                    |     4 |
> | envelope_name           | varchar() not null               |    32 |
> | signed_string           | text                             |   var |
> | envelope_comment        | text                             |   var |
> | envelope_on_hold        | int2                             |     2 |
> | envelope_order_count    | int4                             |     4 |
> | envelope_total          | int4                             |     4 |
> | envelope_currency       | text                             |   var |
> | envelope_modify_time    | datetime                         |     8 |
> | state_id                | char()                           |     1 |
> +-------------------------+----------------------------------+-------+
> 
> thplus=> create index envelope_fk2 on envelope(state_id)
> 
> I try to use the following query
> 
> thplus=>
> explain                                                          
> thplus-> select count(*) from envelope where state_id='H' or
> state_id='E';
> NOTICE:  QUERY PLAN:
> 
> Aggregate  (cost=4.10 size=0 width=0)
>   ->  Index Scan using envelope_fk2 on envelope  (cost=4.10 size=1
> width=4)
> 
> EXPLAIN
> 
> when actually running it, I get the following:
> 
> thplus=> select count(*) from envelope where state_id='H' or
> state_id='E';
> pqReadData() -- backend closed the channel unexpectedly.
>         This probably means the backend terminated abnormally before or
> while processing the request.
> We have lost the connection to the backend, so further processing is
> impossible.  Terminating.
> 
> 
> But the following query runs fine:
> 
> thplu=> select count(*) from envelope where envelope_id=1 or
> envelope_id=3;
> count
> -----
>     2
> (1 row)
> 
> as well as this
> 
> thplus=> select count(*) from envelope where envelope_id=1 or
> state_id='E';
> count
> -----
>    12
> (1 row)
> 
> and this
> 
> thplus=> select count(*) from envelope where state_id='H'
> thplus-> union
> thplus-> select count(*) from envelope where state_id='E';
> count
> -----
>    11
>  1140
> (2 rows)
> 
> 
> So it seems that there is a problem with using indexes in ORs that are
> defined over text types
> 
> the same crash happened also when using varchar(1) as the type of
> state_id
> 
> BTW, it does not happen when the state_id is first field 
> 
> --------------
> Hannu
> 
> 

I need help with this one.  Attached is a patch that also fails, but it
looks closer than the original code.  The problem appears to be that I
can't get a slot that matches the items of the Var node I am trying to
evaluate.  If I used one that matches the heap tuple, that fails,
because if the index is on the second column of the tuple, the attnum is
1, while it is actually 2nd in the tuple slot.

Does anyone know the executor well enough to find me that slot that
matches the Var node?  I can't figure it out.


---------------------------------------------------------------------------


*** ./backend/executor/nodeIndexscan.c.orig	Fri Nov 20 11:38:27 1998
--- ./backend/executor/nodeIndexscan.c	Fri Nov 20 13:25:46 1998
***************
*** 153,161 ****
  				for (prev_index = 0; prev_index < indexstate->iss_IndexPtr;
  					 prev_index++)
  				{
- 					scanstate->cstate.cs_ExprContext->ecxt_scantuple = slot;
  					if (ExecQual(nth(prev_index, node->indxqual),
! 								 scanstate->cstate.cs_ExprContext))
  					{
  						prev_matches = true;
  						break;
--- 153,160 ----
  				for (prev_index = 0; prev_index < indexstate->iss_IndexPtr;
  					 prev_index++)
  				{
  					if (ExecQual(nth(prev_index, node->indxqual),
! 								node->scan.scanstate->cstate.cs_ExprContext))
  					{
  						prev_matches = true;
  						break;


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Fri, 20 Nov 1998 13:32:22 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Bug in 6.4 release