The NOT IN handling with NULLs is the part I found most interesting, since that is where rewrites often go wrong. Beyond the regression tests, how do you check that a rewritten query matches the original? For example, running both against random data with lots of NULLs and comparing the results?
That's exactly how we exercise it. We have special tables with different NULLs distribution over columns, and we use these tables to exercise the 3VL logic and compare against the upstream. Also, for contentious cases we consult ChatGpt/Claude to compare the original and the rewritten shapes. We also employ LLMs to also generate test cases for the rewrite layer.
This is one place where LLMs have truly helped out with testing.
The NOT IN handling with NULLs is the part I found most interesting, since that is where rewrites often go wrong. Beyond the regression tests, how do you check that a rewritten query matches the original? For example, running both against random data with lots of NULLs and comparing the results?
Thanks for the question.
That's exactly how we exercise it. We have special tables with different NULLs distribution over columns, and we use these tables to exercise the 3VL logic and compare against the upstream. Also, for contentious cases we consult ChatGpt/Claude to compare the original and the rewritten shapes. We also employ LLMs to also generate test cases for the rewrite layer.
This is one place where LLMs have truly helped out with testing.