User Parameters are not prompted in sub-sub query (Postgre)
已回答
Hi there,
Please, I have very strange problem with parameters. I use Postgre language and some parameters (mostly in sub-sub query) are not prompted. I don't see the reason why it is so.
Here is my code, in the last lateral left join (allpp) the parameters are not prompted
select lap.*, allpp.id as all_prev_loan_id
FROM (SELECT app.*
FROM scoring.tmp_loan_applications_${product}${country_code} app
limit 100
) lap
-------------------------------------------------------------------------------------------------------------------
LEFT JOIN LATERAL (SELECT ll.*
FROM scoring.tmp_loan_${product}${country_code} ll
WHERE ll.id = lap.loan_id) l ON TRUE
-------------------------------------------------------------------------------------------------------------------
LEFT JOIN LATERAL (SELECT ARRAY (SELECT ll.id
FROM scoring.tmp_loan_${product}${country_code} ll --PARAMETERS ARE NOT PROMPTED !!
WHERE ll.id < lap.prev_loan_id
AND ll.client_id = lap.client_id)
AS all_prev_prev_loan_id) allpp ON TRUE
--[42601] ERROR: syntax error at or near "{" Position: 542
Even more strange behavior is if I leave the main table from subquery, then even second left lateral join will not prompt pamarameters
select lap.*, allpp.id as all_prev_loan_id
FROM scoring.tmp_loan_applications_${product}${country_code} lap --main table (PARAMETERS OK)
-------------------------------------------------------------------------------------------------------------------
LEFT JOIN LATERAL (SELECT ll.*
FROM scoring.tmp_loan_${product}${country_code} ll --PARAMETERS ARE NOT PROMPTED !
WHERE ll.id = lap.loan_id) l ON TRUE
-------------------------------------------------------------------------------------------------------------------
LEFT JOIN LATERAL (SELECT ARRAY (SELECT ll.id
FROM scoring.tmp_loan_${product}${country_code} ll --PARAMETERS ARE NOT PROMPTED !
WHERE ll.id < lap.prev_loan_id
AND ll.client_id = lap.client_id)
AS all_prev_prev_loan_id) allpp ON TRUE
limit 100
Setting of user parameters looks like: (I can't upload image due policy..) it is just plain text.
- Enable in console and SQL files (yes)
- Enable in string literals with SQL injection (yes)
- \$\{([^\{\}]*)\}
- In Scripts(yes) , In literals(yes), All languages
I tried combinations and was playing around but nothing worked (in a lot of scripts). Could you please help me out?
Best regards,
Michal
请先登录再写评论。
@Michal Vorel Could you specify your DataGrip version?
Since, we've got some issues with user parameters in some previous versions.
Hello @Vasily Chernov,
Today I got new laptop and provided datagrip (actual version). I checked few scripts a issue is still there. It is very unpleseant for me, parameters are one of big advantage why I am using datagrip.
DataGrip 2018.1.4
Build #DB-181.5087.33, built on May 22, 2018
JRE: 1.8.0_152-release-1136-b39 amd64
JVM: OpenJDK 64-Bit Server VM by JetBrains s.r.o
Windows 10 10.0
Thanks
I created an issue based on your description https://youtrack.jetbrains.com/issue/DBE-6574