User Parameters are not prompted in sub-sub query (Postgre)

Answered

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

 

 

3 comments
Comment actions Permalink

@Michal Vorel Could you specify your DataGrip version?

Since, we've got some issues with user parameters in some previous versions.

0
Comment actions Permalink

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

1
Comment actions Permalink

I created an issue based on your description https://youtrack.jetbrains.com/issue/DBE-6574

0

Please sign in to leave a comment.