bi_view_editor: wrong generation of ID column

[gurneyalex]

BI View Editor

The id column generated by BI View Editor using CAST(row_number() OVER () as integer) AS id is broken and should be replaced. It generates non stable IDs so when you group or filter records, Odoo will display results not matching the filter or the group in its results.

This issue existed on bi_sql_editor and was fixed by using a subselect to get the rows and then inject the ids.

See method _prepare_request_for_execution on model bi.sql.view for a correct implementation

reporting-engine/bi_sql_editor/models/bi_sql_view.py

Lines 618 to 638 in e8df660

	def _prepare_request_for_execution(self):
	self.ensure_one()
	query = (
	"""
	SELECT
	CAST(row_number() OVER () as integer) AS id,
	CAST(Null as timestamp without time zone) as create_date,
	CAST(Null as integer) as create_uid,
	CAST(Null as timestamp without time zone) as write_date,
	CAST(Null as integer) as write_uid,
	my_query.*
	FROM
	(%s) as my_query
	"""
	% self.query
	)
	return "CREATE {} VIEW {} AS ({});".format(
	self.materialized_text,
	self.view_name,
	query,
	)

Affected versions: all versions

[gurneyalex]

for your information @astirpe

[github-actions]

There hasn't been any activity on this issue in the past 6 months, so it has been marked as stale and it will be closed automatically if no further activity occurs in the next 30 days.
If you want this issue to never become stale, please ask a PSC member to apply the "no stale" label.

[dhx]

@gurneyalex @astirpe our workaround here is to sort the query by an id field to make the order deterministic - that leads to consistent results for our use case but might not be possible in every scenario.

[github-actions]

There hasn't been any activity on this issue in the past 6 months, so it has been marked as stale and it will be closed automatically if no further activity occurs in the next 30 days.
If you want this issue to never become stale, please ask a PSC member to apply the "no stale" label.