> > therefore, a user supplied query like this: > > ``` > > select where $5 in ($1, $2, $3) and $6 = $4 and 1 = 2 > > ``` > > > > will be normalized to: > > ``` > > select where $1 in ($2 /*...*/) and $3 = $4 and $5 = $6 > > ``` > > Hmm, interesting. > > I think this renumbering should not be a problem in practice; users with > unordered parameters have little room to complain if the param numbers > change on query normalization. At least that's how it seems to me. > > If renumbering everything in physical order makes the code simpler, then > I don't disagree. >
It does make it simpler, otherwise we have to introduce O(n) behavior
to find eligible parameter numbers.
I've spent a bit of time looking at this, and I want to
propose the following patchset.
* 0001:
This is a normalization issue discovered when adding new
tests for squashing. This is also an issue that exists in
v17 and likely earlier versions and should probably be
backpatched.
The crux of the problem is if a constant location is
recorded multiple times, the values for $n don't take
into account the duplicate constant locations and end up
incorrectly incrementing the next value fro $n.
So, a query like
SELECT WHERE '1' IN ('2'::int, '3'::int::text)
ends up normalizing to
SELECT WHERE $1 IN ($3::int, $4::int::text)
I also added a few test cases as part of
this patch.
This does also feel like it should be backpatched.
* 0002:
Added some more tests to the ones initially proposed
by Dmitri in v3-0001 [0] including the "edge cases" which
led to the findings for 0001.
* 0003:
This fixes the normalization anomalies introduced by
62d712ec ( squashing feature ) mentioned here [1]
This patch therefore implements the fixes to track
the boundaries of an IN-list, Array expression.
* 0004: implements external parameter squashing.
While I think we should get all patches in for v18, I definitely
think we need to get the first 3 because they fix existing
bugs.
What do you think?
[0]
https://www.postgresql.org/message-id/i635eozw2yjpzqxi5vgm4ceccqq3gv7ul4xj2xni2v6pfgtqlr%40vc5otquxmgjg
[1]
https://www.postgresql.org/message-id/CAA5RZ0ts6zb-efiJ%2BK31Z_YDU%3DM7tHE43vv6ZBCqQxiABr3Yaw%40mail.gmail.com
--
Sami
v5-0002-Enhanced-query-jumbling-squashing-tests.patch
Description: Binary data
v5-0003-Fix-Normalization-for-squashed-query-texts.patch
Description: Binary data
v5-0001-Fix-off-by-one-error-in-query-normalization.patch
Description: Binary data
v5-0004-Support-Squashing-of-External-Parameters.patch
Description: Binary data
