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
 
 
 # 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.
     """
     """ 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
 
     name = 'PlacexGeometryReverseLookuppolygon'
     inherit_cache = True
 


@@ -51,8 +50,7 @@ def _sqlite_intersects(element: SaColumn,
             " AND placex.linked_place_id is null)")
 
 
             " 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
 
     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
 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'" + \
            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))
 
 
                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
 
     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"\
             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'"\
              " (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))
 
 
                 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
 
     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'")
 
 
                    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
 
     name = 'IsAddressPoint'
     inherit_cache = True
 


@@ -162,11 +158,10 @@ def sqlite_is_address_point(element: SaColumn,
                 compiler.process(name, **kw))
 
 
                 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.
     """
     """ 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
 
     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)
 
 
     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.
     """
     """ Check if the geometry is a line or multiline.
     """

-    type = sa.Boolean()

     name = 'Geometry_IsLineLike'
     inherit_cache = True
 
     name = 'Geometry_IsLineLike'
     inherit_cache = True
 


@@ -63,10 +62,9 @@ def _sqlite_is_line_like(element: SaColumn,
                compiler.process(element.clauses, **kw)
 
 
                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.
     """
     """ Check if the geometry is a polygon or multipolygon.
     """

-    type = sa.Boolean()

     name = 'Geometry_IsLineLike'
     inherit_cache = True
 
     name = 'Geometry_IsLineLike'
     inherit_cache = True
 


@@ -85,10 +83,9 @@ def _sqlite_is_area_like(element: SaColumn,
                compiler.process(element.clauses, **kw)
 
 
                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.
     """
     """ Check if the bounding boxes of the given geometries intersect.
     """

-    type = sa.Boolean()

     name = 'Geometry_IntersectsBbox'
     inherit_cache = True
 
     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:
 @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.
     """
     """ 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
 
     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)
 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)" %(
            "%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))
 
 
               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.
     """
     """ 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
 
     name = 'Geometry_ColumnDWithin'
     inherit_cache = True
 


@@ -320,8 +315,7 @@ for alias in SQLITE_FUNCTION_ALIAS:
     _add_function_alias(*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
 
     name = 'ST_DWithin'
     inherit_cache = True