TPC-Council / HammerDB

HammerDB Database Load Testing and Benchmarking Tool
http://www.hammerdb.com
GNU General Public License v3.0
545 stars 115 forks source link

Fix for TPROC-H calculates o_totalprice incorrectly #610

Closed sm-shaw closed 9 months ago

sm-shaw commented 9 months ago

Fixes o_totalprice calculation for all databases for TPROC-H schema load.

Also does the same for refresh function, which is defined as follows:

LOOP (SF * 1500) TIMES
INSERT a new row into the ORDERS table
LOOP RANDOM(1, 7) TIMES
INSERT a new row into the LINEITEM table
END LOOP
END LOOP

However, o_totalprice is calculated from the sum of the rows in LINEITEM and therefore we need to insert it after LINEITEM otherwise o_totalprice is 0 (current behaviour) This in turn causes the issue that we have a foreign key on LINEITEM L_ORDERKEY referencing ORDERS O_ORDERKEY, so we cannot insert in ORDERS before as we don't know o_totalprice and cannot insert after as it violates the FK. Therefore, have added deferrable constraints for Oracle and PostgreSQL and temporary disabling of FK checks for SQL Server, MySQL/MariaDB and Db2 so that the refresh function will work by inserting into LINEITEM first.

abondvt89 commented 9 months ago

Merging after reviews and approval of the three members of the code maintenance team.