provsql icon indicating copy to clipboard operation
provsql copied to clipboard

Multiplication by constant of aggregate leads to wrong query result

Open lamflokas opened this issue 1 month ago • 1 comments

The general phenomenon I am observing is that for a provenance mapped table b

`SELECT SOME_AGG(b.column) FROM b;' works correctly

but `SELECT SOME_AGG(b.column)*const FROM b;' returns a wrong result

First run provsql with docker exposing the 5432 to localhost, then set up the data

gdown --output downloaded-file.zip "1UJyA6I6pTmmhYpwdn8iT9QKrcJqSQAcX"
unzip downloaded-file.zip 'data_minidev/MINIDEV_mysql/BIRD_dev.sql' 'data_minidev/MINIDEV_postgresql/BIRD_dev.sql' -d extracted-files
sed -i 's/xiaolongli/test/g' extracted-files/data_minidev/MINIDEV_postgresql/BIRD_dev.sql
psql -h localhost -p 5432 -U test -d test < extracted-files/data_minidev/MINIDEV_postgresql/BIRD_dev.sql

Now I run:

psql -h localhost -p 5432 -U test -d test -c "SELECT add_provenance('budget');"
psql -h localhost -p 5432 -U test -d test -c "SELECT AVG(budget.amount) FROM budget" > out_works.txt
psql -h localhost -p 5432 -U test -d test -c "SELECT AVG(budget.amount) *10 FROM budget" > out_wrong.txt

The contents of out_wrong.txt and out_works.txt are attached. I see similar issues with multiplication/division and addition of constants.

lamflokas avatar Dec 01 '25 21:12 lamflokas