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

[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

[pramsey]

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

[robe]

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.

[pramsey]

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

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

[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.

[robe]

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

[Paul Ramsey <pramsey@…>]

In 5235846e/git:

Add an explicit <> operator, closes #5175

[Paul Ramsey <pramsey@…>]

In 71fefd6/git:

Add an explicit <> operator, closes #5175

[robe]

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

[Paul Ramsey <pramsey@…>]

In 3b12c65/git:

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

This reverts commit 71fefd690fbc887a7399518ea56f17797faf1c11.

[robe]

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?

[robe]

Also news item is missing

[Regina Obe <lr@…>]

In fe51bf7b/git:

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

• Add NEWS
• Fix dump reload issue

Closes #5175 for PostGIS 3.5.0

[strk]

Still missing:

• testcase showing the problem being fixed (should fail when that new operator is removed)
• upgrade support (if you add a test you'll see it fail on the woodie upgrade test)

[Sandro Santilli <strk@…>]

In 3df8cad/git:

Add test for inequality operator

References #5175

[strk]

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 ?

[strk]

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)

[strk]

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...