Oracle Provider: Different Count and Feature Queries in case a sql_manipulation class is used

[Moritz-Langer]

Description
Given that a sql_manipulation class is used within the oracle provider, the count query for "numberMatched" and the actual feature queries might be different queries. This can lead to wrong numberMatched and to wrong queries being executed.

Steps to Reproduce
Steps to reproduce the behavior: sql_manipulator that adds/removes something to the where clause

Expected behavior
Except the limit and offset params the where clauses should be the same for the count and the actual query.

pygeoapi/pygeoapi/provider/oracle.py

Lines 696 to 700 in 3bdeefe

	sql_query = f"SELECT COUNT(1) AS hits \
	FROM {self.table} \
	{where_dict['clause']}"
	try:
	cursor.execute(sql_query, where_dict["properties"])

even if this changes the query:

pygeoapi/pygeoapi/provider/oracle.py

Lines 799 to 870 in 3bdeefe

	if self.sql_manipulator:
	LOGGER.debug("sql_manipulator: " + self.sql_manipulator)
	manipulation_class = _class_factory(self.sql_manipulator)
	sql_query, bind_variables = manipulation_class.process_query(
	db,
	sql_query,
	bind_variables,
	self.sql_manipulator_options,
	offset,
	limit,
	resulttype,
	bbox,
	datetime_,
	properties,
	sortby,
	skip_geometry,
	select_properties,
	crs_transform_spec,
	q,
	language,
	filterq,
	extra_params=extra_params
	)
	# Clean up placeholders that aren't used by the
	# manipulation class.
	sql_query = sql_query.replace("#HINTS#", "")
	sql_query = sql_query.replace("#JOIN#", "")
	sql_query = sql_query.replace("#WHERE#", "")
	LOGGER.debug(f"SQL Query: {sql_query}")
	LOGGER.debug(f"Bind variables: {bind_variables}")
	try:
	cursor.execute(sql_query, bind_variables)
	except oracledb.Error as err:
	LOGGER.error(f"Error executing sql_query: {sql_query}")
	LOGGER.error(err)
	raise ProviderQueryError()
	# Convert row resultset to dictionary
	columns = [col[0] for col in cursor.description]
	cursor.rowfactory = lambda *args: dict(zip(columns, args))
	row_data = cursor.fetchall()
	# Generate feature JSON
	features = [self._response_feature(rd) for rd in row_data]
	feature_collection = {
	"numberMatched": hits,
	"type": "FeatureCollection",
	"features": features,
	}
	return feature_collection
	def _get_previous(self, cursor, identifier):
	"""
	Query previous ID given current ID
	:param identifier: feature id
	:returns: feature id
	"""
	sql = f"SELECT {self.id_field} AS id \
	FROM {self.table} \
	WHERE ROWNUM = 1 \
	AND {self.id_field} < :{self.id_field} \
	ORDER BY {self.id_field} DESC"
	bind_variables = {self.id_field: identifier}

Screenshots/Tracebacks
If applicable, add screenshots to help explain your problem.

Environment

• OS:
• Python version:
• pygeoapi version:

Additional context
Solution proposal is here:
https://github.com/totycro/pygeoapi/blob/e2ba2c79cdcbd1b23601fba9f7f555a8d6644353/pygeoapi/provider/oracle.py#L696-L736