Type casting and indexes: bug?

We ran into a problem which seems to point to a defect relating to
performing explicit type casts on columns that have alternate indexes on
them. We distilled down into the following simplest case:

# select version()
# ;
version
-------------------------------------------------------------
PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96

#
# SELECT DISTINCT
# EXO.add_timestamp
# FROM
# obc_device OBC,
# sy_external_object EXO
# WHERE
# OBC.station_id::bpchar = EXO.reference_data_1 AND
# EXO.add_timestamp BETWEEN '2003-5-1' AND '2003-5-2';

add_timestamp
---------------
(0 rows)

#
# SELECT DISTINCT
# EXO.add_timestamp
# FROM
# obc_device OBC,
# sy_external_object EXO
# WHERE
# TRIM(OBC.station_id) = TRIM(EXO.reference_data_1) AND
# EXO.add_timestamp BETWEEN '2003-5-1' AND '2003-5-2';

add_timestamp
---------------------
2003-05-01 10:53:22
....
....
....
2003-05-01 15:24:53
2003-05-01 15:28:19
(189 rows)

We originally thought that something was going wrong with the BETWEEN as
the bigger SQL that originally was the source of the issue would return
rows when using "add_timestamp > '2003-5-1'" and not return rows when using
"add_timestamp BETWEEN '2003-5-1' AND '2003-5-2'". However, what we found
seemed to tie the problem to the use of an index on the type casted
"station_id" column. When doing the "greater than" operation instead of
the BETWEEN, the query plan changed (and rightly so) such that it did not
use the alternate index on station_id. The same effect is achieved by
performing the TRIM() function on the column. Consider the explains of the
sqls:

# explain
# SELECT DISTINCT
# EXO.add_timestamp
# FROM
# obc_device OBC,
# sy_external_object EXO
# WHERE
# OBC.station_id::bpchar = EXO.reference_data_1 AND
# EXO.add_timestamp BETWEEN '2003-5-1' AND '2003-5-2';

NOTICE: QUERY PLAN:
Unique (cost=0.00..6.94 rows=1 width=128)
-> Nested Loop (cost=0.00..6.94 rows=1 width=128)
-> Index Scan using exo_add_timestamp on sy_external_object exo
-> Index Scan using obc_station_id on obc_device obc

# explain
# SELECT DISTINCT
# EXO.add_timestamp
# FROM
# obc_device OBC,
# sy_external_object EXO
# WHERE
# TRIM(OBC.station_id) = TRIM(EXO.reference_data_1) AND
# EXO.add_timestamp BETWEEN '2003-5-1' AND '2003-5-2';

NOTICE: QUERY PLAN:
Unique (cost=100000000.00..100000015.07 rows=1 width=128)
-> Nested Loop (cost=100000000.00..100000015.07 rows=1 width=128)
-> Index Scan using exo_add_timestamp on sy_external_object exo
-> Seq Scan on obc_device obc (cost=100000000.00..100000008.20

This would seem to indicate some trouble with type casting indexed columns.
Right now we can use the TRIM() work around as even though the index isn't
used, the query performs acceptably fast. However, something seems not
right. Sorry if this is simply a known issue that has been corrected in
7.3. With no other known issues forcing us to upgrade, we are hoping to
hold out for a stable 7.4.

Any input?

Marc Mitchell - Senior Application Architect
Enterprise Information Solutions, Inc.
Downers Grove, IL 60515
marcm(at)eisolution(dot)com