Opened 2 years ago

Closed 14 months ago

#5175 closed defect (fixed)

<> and != on geometry yield operator is not unique

Reported by: robe Owned by: pramsey
Priority: medium Milestone: PostGIS 3.5.0
Component: postgis Version: 3.0.x
Keywords: Cc:

Description (last modified by robe)

Reported on IRC someone trying to create a trigger with construct:

 (NEW.geom != OLD.geom) 

yielded != is not unique for geometry. had to resort to

 (NEW.geom::text != OLD.geom::text) 

I verified it is an issue with

SELECT ST_Point(1,2) != ST_Point(3,4);
SELECT ST_Point(1,2) <> ST_Point(3,4);

However this works fine:

SELECT ST_Point(1,2)::geography != ST_Point(3,4)::geography;
SELECT ST_Point(1,2)::geography <> ST_Point(3,4)::geography;

I haven't verified where the extra operator indirection is coming from that is not effecting geography as well.

Note that:

SELECT ST_Point(1,2) = ST_Point(3,4);
SELECT NOT (ST_Point(1,2) = ST_Point(3,4)) ;

work without error

Change History (23)

comment:1 by robe, 2 years ago

Description: modified (diff)

comment:2 by robe, 2 years ago

Milestone: PostGIS 3.2.2PostGIS 3.2.3

comment:3 by pramsey, 2 years ago

I don't see EVEN ONE definition of <> or !=. so... crazY?

comment:4 by robe, 2 years ago

Milestone: PostGIS 3.2.3PostGIS 2.5.9

comment:5 by robe, 2 years ago

It just dawned on me where the indirection could be coming from. Remember we have a cast for PostgreSQL geometry types (polygon and point) to postgis types and I think we might have the other way around too.

Could it be because it can cast to text or point and it's getting those extra operators from there. I don't think we have such a cast for geography types.

comment:6 by robe, 2 years ago

Milestone: PostGIS 2.5.9PostGIS 3.2.4

comment:7 by robe, 2 years ago

Milestone: PostGIS 3.2.4PostGIS 3.4.0

comment:8 by robe, 17 months ago

Milestone: PostGIS 3.4.0PostGIS 3.5.0

comment:9 by pramsey, 16 months ago

I tried dropping the casts into point, and that didn't change anything.

drop CAST (point AS geometry);
drop CAST (geometry AS point);

comment:10 by pramsey, 16 months ago

I feel kind of stupid this has been open for so long because the solution seems to be just to add an explicit <> operator for geometry.

in reply to:  10 comment:11 by robe, 16 months ago

Replying to pramsey:

I feel kind of stupid this has been open for so long because the solution seems to be just to add an explicit <> operator for geometry.

Really? I would never have figured that out

comment:12 by Paul Ramsey <pramsey@…>, 16 months ago

Resolution: fixed
Status: newclosed

In 5235846e/git:

Add an explicit <> operator, closes #5175

comment:13 by Paul Ramsey <pramsey@…>, 16 months ago

In 71fefd6/git:

Add an explicit <> operator, closes #5175

comment:14 by robe, 16 months ago

Resolution: fixed
Status: closedreopened

Please remove from 3.3. We can't be adding signatures in stable releases.

comment:15 by Paul Ramsey <pramsey@…>, 16 months ago

Resolution: fixed
Status: reopenedclosed

In 3b12c65/git:

Revert "Add an explicit <> operator, closes #5175"

This reverts commit 71fefd690fbc887a7399518ea56f17797faf1c11.

comment:16 by robe, 15 months ago

Resolution: fixed
Status: closedreopened

gitlab is not happy, registering below https://gitlab.com/postgis/postgis/-/jobs/4870592289#L4164 on the dump restore test

COMMENT
ERROR:  operator <> already exists
-----------------------------------------------------------------------------
make: *** [/builds/postgis/postgis/regress/runtest.mk:24: check-regress] Error 3
Cleaning up project directory and file based variables

perhaps because you are missing an availability notice on geometry_neq (although that shouldn't cause a comment error).

@strk what's missing here?

comment:17 by robe, 15 months ago

Also news item is missing

comment:18 by Regina Obe <lr@…>, 15 months ago

Resolution: fixed
Status: reopenedclosed

In fe51bf7b/git:

Fix loose-ends for <> and != geometry operators

  • Add NEWS
  • Fix dump reload issue

Closes #5175 for PostGIS 3.5.0

comment:19 by strk, 14 months ago

Resolution: fixed
Status: closedreopened

Still missing:

  • testcase showing the problem being fixed (should fail when that new operator is removed)
  • uogradec support (if you add a test you'll see it fail on the woodie upgrade test)
Version 0, edited 14 months ago by strk (next)

comment:20 by Sandro Santilli <strk@…>, 14 months ago

In 3df8cad/git:

Add test for inequality operator

References #5175

comment:21 by strk, 14 months ago

Given the side-effect of turning an inequality operator against TopoGeometry ambiguous (see #5546) I'd like to understand what exactly made those operators ambiguous for the core geometry type. I guess ambiguity derives from the presence of implicit casts from the type to some other type. What explicit casts does geometry have ? Did that change any recently ?

comment:22 by strk, 14 months ago

This shows 3.0.9dev is also affected by the ambiguity:

regress/run_test.pl --extension --upgrade --upgrade-path unpackaged3.0--3.0.9dev -v regress/core/operators

 regress/core/operators .. failed (diff expected obtained: /tmp/pgis_reg/test_1_diff)
-----------------------------------------------------------------------------
--- regress/core/operators_expected     2023-09-22 10:22:12.097326401 +0200
+++ /tmp/pgis_reg/test_1_out    2023-09-22 10:56:43.854885462 +0200
@@ -44,8 +44,8 @@
 eq1|t
 eq2|f
 eq3|f
-neq1|f
-neq2|t
+ERROR:  operator is not unique: geometry <> geometry at character 40
+ERROR:  operator is not unique: geometry <> geometry at character 40
 cd1|4
 bd1|0
 bd2|1

So the reported problem isn't anything new. But the fix moves the ambiguity where previously there wasn't one (TopoGeometry, see #5546)

comment:23 by strk, 14 months ago

Resolution: fixed
Status: reopenedclosed
Version: 3.2.x3.0.x

Given CI is green we're obviously not testing the structure of casts so I'll give up on chasing that, especially since I'm sure there are a few of these cases to deal with...

Note: See TracTickets for help on using tickets.