remove type info from SQLALchemy condition functions

A boolean type makes the SQLite dialect produce a costruct like
'func() = 1' in WHERE condition. While syntactically correct, it tends
to confuse the query planer.

diff --git a/nominatim/db/sqlalchemy_functions.py b/nominatim/db/sqlalchemy_functions.py
index 65b813cb387cff265e311d6ce47cb7305f894a08..cb04f7626f08b97f2ee602900849e132f65f6272 100644 (file)
--- a/nominatim/db/sqlalchemy_functions.py
+++ b/nominatim/db/sqlalchemy_functions.py
@@ -17,14 +17,13 @@ from nominatim.typing import SaColumn
 
 # pylint: disable=all
 
-class PlacexGeometryReverseLookuppolygon(sa.sql.functions.GenericFunction[bool]):
+class PlacexGeometryReverseLookuppolygon(sa.sql.functions.GenericFunction[Any]):
     """ Check for conditions that allow partial index use on
         'idx_placex_geometry_reverse_lookupPolygon'.
 
         Needs to be constant, so that the query planner picks them up correctly
         in prepared statements.
     """
-    type = sa.Boolean()
     name = 'PlacexGeometryReverseLookuppolygon'
     inherit_cache = True
 
@@ -51,8 +50,7 @@ def _sqlite_intersects(element: SaColumn,
             " AND placex.linked_place_id is null)")
 
 
-class IntersectsReverseDistance(sa.sql.functions.GenericFunction[bool]):
-    type = sa.Boolean()
+class IntersectsReverseDistance(sa.sql.functions.GenericFunction[Any]):
     name = 'IntersectsReverseDistance'
     inherit_cache = True
 
@@ -66,12 +64,12 @@ class IntersectsReverseDistance(sa.sql.functions.GenericFunction[bool]):
 def default_reverse_place_diameter(element: SaColumn,
                                    compiler: 'sa.Compiled', **kw: Any) -> str:
     table = element.tablename
-    return f"{table}.rank_address between 4 and 25"\
+    return f"({table}.rank_address between 4 and 25"\
            f" AND {table}.type != 'postcode'"\
            f" AND {table}.name is not null"\
            f" AND {table}.linked_place_id is null"\
            f" AND {table}.osm_type = 'N'" + \
-           " AND ST_Buffer(%s, reverse_place_diameter(%s)) && %s" % \
+           " AND ST_Buffer(%s, reverse_place_diameter(%s)) && %s)" % \
                tuple(map(lambda c: compiler.process(c, **kw), element.clauses))
 
 
@@ -81,7 +79,7 @@ def sqlite_reverse_place_diameter(element: SaColumn,
     geom1, rank, geom2 = list(element.clauses)
     table = element.tablename
 
-    return (f"{table}.rank_address between 4 and 25"\
+    return (f"({table}.rank_address between 4 and 25"\
             f" AND {table}.type != 'postcode'"\
             f" AND {table}.name is not null"\
             f" AND {table}.linked_place_id is null"\
@@ -91,15 +89,14 @@ def sqlite_reverse_place_diameter(element: SaColumn,
              " (SELECT place_id FROM placex_place_node_areas"\
              "  WHERE ROWID IN (SELECT ROWID FROM SpatialIndex"\
              "  WHERE f_table_name = 'placex_place_node_areas'"\
-             "  AND search_frame = %s))") % (
+             "  AND search_frame = %s)))") % (
                 compiler.process(geom1, **kw),
                 compiler.process(geom2, **kw),
                 compiler.process(rank, **kw),
                 compiler.process(geom2, **kw))
 
 
-class IsBelowReverseDistance(sa.sql.functions.GenericFunction[bool]):
-    type = sa.Boolean()
+class IsBelowReverseDistance(sa.sql.functions.GenericFunction[Any]):
     name = 'IsBelowReverseDistance'
     inherit_cache = True
 
@@ -132,8 +129,7 @@ def select_index_placex_geometry_reverse_lookupplacenode(table: str) -> 'sa.Text
                    f" AND {table}.osm_type = 'N'")
 
 
-class IsAddressPoint(sa.sql.functions.GenericFunction[bool]):
-    type = sa.Boolean()
+class IsAddressPoint(sa.sql.functions.GenericFunction[Any]):
     name = 'IsAddressPoint'
     inherit_cache = True
 
@@ -162,11 +158,10 @@ def sqlite_is_address_point(element: SaColumn,
                 compiler.process(name, **kw))
 
 
-class CrosscheckNames(sa.sql.functions.GenericFunction[bool]):
+class CrosscheckNames(sa.sql.functions.GenericFunction[Any]):
     """ Check if in the given list of names in parameters 1 any of the names
         from the JSON array in parameter 2 are contained.
     """
-    type = sa.Boolean()
     name = 'CrosscheckNames'
     inherit_cache = True
 

diff --git a/nominatim/db/sqlalchemy_types.py b/nominatim/db/sqlalchemy_types.py
index 036b25dd9ac48e2500b4f31e8d7dcc6221bd38bf..a36e8c462acfce3b4cc5e730b2eb5c008f1dfa14 100644 (file)
--- a/nominatim/db/sqlalchemy_types.py
+++ b/nominatim/db/sqlalchemy_types.py
@@ -41,10 +41,9 @@ def _spatialite_distance_spheroid(element: SaColumn,
     return "COALESCE(Distance(%s, true), 0.0)" % compiler.process(element.clauses, **kw)
 
 
-class Geometry_IsLineLike(sa.sql.expression.FunctionElement[bool]):
+class Geometry_IsLineLike(sa.sql.expression.FunctionElement[Any]):
     """ Check if the geometry is a line or multiline.
     """
-    type = sa.Boolean()
     name = 'Geometry_IsLineLike'
     inherit_cache = True
 
@@ -63,10 +62,9 @@ def _sqlite_is_line_like(element: SaColumn,
                compiler.process(element.clauses, **kw)
 
 
-class Geometry_IsAreaLike(sa.sql.expression.FunctionElement[bool]):
+class Geometry_IsAreaLike(sa.sql.expression.FunctionElement[Any]):
     """ Check if the geometry is a polygon or multipolygon.
     """
-    type = sa.Boolean()
     name = 'Geometry_IsLineLike'
     inherit_cache = True
 
@@ -85,10 +83,9 @@ def _sqlite_is_area_like(element: SaColumn,
                compiler.process(element.clauses, **kw)
 
 
-class Geometry_IntersectsBbox(sa.sql.expression.FunctionElement[bool]):
+class Geometry_IntersectsBbox(sa.sql.expression.FunctionElement[Any]):
     """ Check if the bounding boxes of the given geometries intersect.
     """
-    type = sa.Boolean()
     name = 'Geometry_IntersectsBbox'
     inherit_cache = True
 
@@ -103,16 +100,15 @@ def _default_intersects(element: SaColumn,
 @compiles(Geometry_IntersectsBbox, 'sqlite') # type: ignore[no-untyped-call, misc]
 def _sqlite_intersects(element: SaColumn,
                        compiler: 'sa.Compiled', **kw: Any) -> str:
-    return "MbrIntersects(%s)" % compiler.process(element.clauses, **kw)
+    return "MbrIntersects(%s) = 1" % compiler.process(element.clauses, **kw)
 
 
-class Geometry_ColumnIntersectsBbox(sa.sql.expression.FunctionElement[bool]):
+class Geometry_ColumnIntersectsBbox(sa.sql.expression.FunctionElement[Any]):
     """ Check if the bounding box of the geometry intersects with the
         given table column, using the spatial index for the column.
 
         The index must exist or the query may return nothing.
     """
-    type = sa.Boolean()
     name = 'Geometry_ColumnIntersectsBbox'
     inherit_cache = True
 
@@ -128,7 +124,7 @@ def default_intersects_column(element: SaColumn,
 def spatialite_intersects_column(element: SaColumn,
                                  compiler: 'sa.Compiled', **kw: Any) -> str:
     arg1, arg2 = list(element.clauses)
-    return "MbrIntersects(%s, %s) and "\
+    return "MbrIntersects(%s, %s) = 1 and "\
            "%s.ROWID IN (SELECT ROWID FROM SpatialIndex "\
                         "WHERE f_table_name = '%s' AND f_geometry_column = '%s' "\
                         "AND search_frame = %s)" %(
@@ -138,13 +134,12 @@ def spatialite_intersects_column(element: SaColumn,
               compiler.process(arg2, **kw))
 
 
-class Geometry_ColumnDWithin(sa.sql.expression.FunctionElement[bool]):
+class Geometry_ColumnDWithin(sa.sql.expression.FunctionElement[Any]):
     """ Check if the geometry is within the distance of the
         given table column, using the spatial index for the column.
 
         The index must exist or the query may return nothing.
     """
-    type = sa.Boolean()
     name = 'Geometry_ColumnDWithin'
     inherit_cache = True
 
@@ -320,8 +315,7 @@ for alias in SQLITE_FUNCTION_ALIAS:
     _add_function_alias(*alias)
 
 
-class ST_DWithin(sa.sql.functions.GenericFunction[bool]):
-    type = sa.Boolean()
+class ST_DWithin(sa.sql.functions.GenericFunction[Any]):
     name = 'ST_DWithin'
     inherit_cache = True