provsql
provsql copied to clipboard
Multiplication by constant of aggregate leads to wrong query result
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.