BUG #14067: Wrong result of <select distinct geometry from XXX>

The following bug has been logged on the website:

Bug reference: 14067
Logged by: rui.yuan
Email address: yuan_rui1987(at)126(dot)com
PostgreSQL version: 9.5.0
Operating system: windows 7
Description:

Hi,

Sorry to bother.
I have encountered the following problem, please help me.

The attached file is a backup file of two geometries, the table name is
<temp_union_test>, the column of geometry is <the_geom>.
when I execute < select distinct the_geom from temp_union_test >, there is
only one row returned, but actually, the two geometries is totally
different.
I have tried this execution on PostgreSql 9.0 and PostgreSql 9.5, the
results is always the same.

Would you help me to solve this problem?
If you have any concern about it, I would be very appreciate it.
Thank you.

On 6 April 2016 at 18:10, <yuan_rui1987(at)126(dot)com> wrote:
> The following bug has been logged on the website:
>
> Bug reference: 14067
> Logged by: rui.yuan
> Email address: yuan_rui1987(at)126(dot)com
> PostgreSQL version: 9.5.0
> Operating system: windows 7
> Description:
>
> Hi,
>
> Sorry to bother.
> I have encountered the following problem, please help me.
>
> The attached file is a backup file of two geometries, the table name is
> <temp_union_test>, the column of geometry is <the_geom>.
> when I execute < select distinct the_geom from temp_union_test >, there is
> only one row returned, but actually, the two geometries is totally
> different.
> I have tried this execution on PostgreSql 9.0 and PostgreSql 9.5, the
> results is always the same.
>
> Would you help me to solve this problem?
> If you have any concern about it, I would be very appreciate it.

Hi,

Would you be able to tell us what data type "the_geom" is?
The most likely scenario would be that this type is from an extension
which has a buggy function which test for equality of the type, but we
need to determine if this is the case.

If you run;

SELECT
oprcode
FROM pg_amop amop
INNER JOIN pg_operator p
ON amop.amopopr = p.oid
AND amop.amopstrategy = 3
WHERE amoplefttype = (SELECT
oid
FROM pg_type
WHERE typname = 'int4')
AND amoprighttype = (SELECT
oid
FROM pg_type
WHERE typname = 'int4');

where instead of 'int4' have the type of your "the_geom" column. On
the function name returned by the results of the above query, execute
the following query passing in the two distinct values that you have
in the temp_union_test table.

Something like:
select geomqt(<value1>,<value2>);

Maybe also try swapping the parameters around too.

Let us know if this function returns true or false for your type.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services