Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

bi_view_editor: wrong generation of ID column #881

Open
gurneyalex opened this issue May 21, 2024 · 3 comments
Open

bi_view_editor: wrong generation of ID column #881

gurneyalex opened this issue May 21, 2024 · 3 comments
Labels

Comments

@gurneyalex
Copy link
Member

gurneyalex commented May 21, 2024

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

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 gurneyalex added the bug label May 21, 2024
@gurneyalex gurneyalex changed the title [15.0] BI View Editor: wrong generation of ID column BI View Editor: wrong generation of ID column May 21, 2024
@gurneyalex gurneyalex changed the title BI View Editor: wrong generation of ID column bi_view_editor: wrong generation of ID column May 21, 2024
@gurneyalex
Copy link
Member Author

for your information @astirpe

Copy link

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.

@github-actions github-actions bot added the stale PR/Issue without recent activity, it'll be soon closed automatically. label Nov 24, 2024
@dhx
Copy link

dhx commented Dec 10, 2024

@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 github-actions bot removed the stale PR/Issue without recent activity, it'll be soon closed automatically. label Dec 15, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
Development

No branches or pull requests

2 participants