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

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