Dear SQLDev team,
This faulty behavior happens only when formatting an editor content with an active database connection. I did not see this problem when formatting code in a worksheet without an open connection. Furthermore it works in SQLcl 21.2.2 and in my standalone testing environment which uses SQLcl as a library. And it worked as expected in SQLDev 21.2.0.
I'm sorry, I failed to produce a smaller test case based on the provided Arbori program or based on a minimalistic Arbori program. However, I don't think my Arbori program is responsible for this behavior, at least not directly.
Problem
These are the steps to reproduce the problem on macOS Big Sur or Windows 10 with JDK8 and SQLDev 21.2.1:
1. Start SQLDev with a console window
2. Configure the attached `custom_format.arbori.txt` in the preferences under (Code Editor -> Format -> Advanced Format -> Custom Format)
3. Connect to a schema containing an `EMP` table
4. Clear the console window (optional)
5. Paste this code into the worksheet
select
case job
when 'ANALYST'
then 3000
end
from emp;
6. Wait 1-2 seconds to give the background task time to format the expanded statement
7. Press Ctrl-F7 to format the code in the worksheet
The formatter result looks like this (might differ based on your `Advanced Format` settings, but this is not relevant to reproduce this problem):
select case jobwhen'analyst'
then 3000 end
from
emp ;
Based on my analysis the Arbori queries use the parse tree of the previous formatter call. The one issued by the background process for the expanded statement. This expanded statement has more lexer tokens.
One fatal difference is `"A1"."JOB"` instead of `job`. As a result the Arbori program finds a dot at lexer position 3. and registers zero spaces before and after this token via the HashMap `newlinePositions`. But the Formatter uses the correct parse tree and removes all whitespaces before and after its lexer token #3 `when`. This leads to an invalid SQL statement. And of course this explains the weird formatting result.
The attached `console_output.txt` file shows both statements and parse trees. It contains also various `IndexOutOfBoundsException` which are caused by the wrong results of the Arbori queries or better the wrong use of input.
Workaround
You can press Ctrl-Z or Command-Z to undo the changes by the formatter and then call the formatter via Ctrl-F7 again.
In this case the expanded statement is formatted afterwards and does not affect the formatting result.
Solution?
I believe this is a bug. Maybe a caching issue. I don't know.
I'd appreciate if that could be fixed in one of the coming releases.
Any hints about better or alternative workarounds are welcome.
Thank you!
console_output.txt (16.45 KB)custom_format.arbori.txt (132.44 KB)