Postgres bitwise xor (#) and JSON path (#>, #>>, and #-) operator formatting

[pauljz]

Describe the bug
The Postgres bitwise xor operator # appears to get recognized as a comment, rather than as an infix operator. This can produce some very strange results.

To Reproduce
Simplified input:

select
    a_useful_function(something_fancy::int # something_else_fancy::int) as bitwise_xor_result,
    thing1,
    thing2
from table

Expected behavior
I expect this to be treated like any other infix operator:

select
    a_useful_function(
        something_fancy::int # something_else_fancy::int
    ) as bitwise_xor_result,
    thing1,
    thing2
from table

Actual behavior
In this example, indentation gets pretty mangled for the rest of the file. It treats the closing paren as commented-out and so we get hanging indentation.

select
    a_useful_function(
        something_fancy::int  # something_else_fancy::int) as bitwise_xor_result,
        thing1,
        thing2
        from table

Workarounds
In some cases this can be worked around by pre-formatting things just right. For example, this works fine as an input (although, note the awkward double space: #).

select
    a_useful_function(
        something_fancy::int  # something_else_fancy::int
    ) as bitwise_xor_result,
    thing1,
    thing2
from table

If things still won't format properly, we can create a function as a workaround.

create or replace function bitwise_xor(a integer, b integer) returns integer
    as 'select a # b;'
    language sql
    immutable
    returns null on null input;

select
    bitwise_xor(something_fancy::int, something_else_fancy::int) as bitwise_xor_result,
    thing1,
    thing2
from table;

Additional context
sqlfmt, version 0.19.2 and reproducible on https://sqlfmt.com/ in current state.

[tconbeer]

ugh, yeah, I didn't know that symbol had meaning in postgres. We parse it as a comment for MySql compatibility.

I think we'll have to create a Postgres dialect that doesn't include that character in the comment rule (and includes it as an operator). That is a bit of a pain in the ass...

[pauljz]

Bitwise XOR isn't exactly a common one at least! Doesn't seem worth adding dialect differentiation just for this, which is also why I made a point to document those workarounds.

(I'm a bit sad Postgres itself doesn't seem to have a built-in function version of this. It'd be a lot less opaque than an operator to those reading the code.)

[tconbeer]

It was just brought to my attention that there are two other PG operators that start with #; JSON path accessors #> and #>>. sqlfmt is very broken for them, also. For completeness, there is also #-, which deletes an item from a JSONB at a specific path.

In all of these cases, --fmt: off comments will only work if they are on a previous line. They CANNOT be on the same line as the # token. For example, this works:

select
    --fmt: off
    foo #>> bar,
    --fmt: on
    baz

But this does NOT:

select
    foo #>> bar, -- fmt: off
    baz

as it becomes:

select
    foo  # >> bar, -- fmt: off
    baz

[TimDumol]

It was just brought to my attention that there are two other PG operators that start with #; JSON path accessors #> and #>>. sqlfmt is very broken for them, also. For completeness, there is also #-, which deletes an item from a JSONB at a specific path.

In all of these cases, --fmt: off comments will only work if they are on a previous line. They CANNOT be on the same line as the # token. For example, this works:

select
    --fmt: off
    foo #>> bar,
    --fmt: on
    baz

But this does NOT:

select
    foo #>> bar, -- fmt: off
    baz

as it becomes:

select
    foo  # >> bar, -- fmt: off
    baz

If it helps other people looking at this issue, I was able to workaround #>> by using json_extract_path_text() -- except for one case where I used it to convert a json string to TEXT (json_text #>> '{}'), which I had to express in a different way. (Although I see now that I also could have just used a --fmt: off comment)

[MattiasMTS]

I can add here that the -- fmt: off and -- fmt: on doesn't work with nested brackets. Take this sql query for example:

    select *,
        array[ 
            ( my_key || '_' || (blob #>> '{key,value}')
            )
        ] as new_blob
    from my_table

adding e.g. the formatting rules to this doesn't work as follows:

    select *,
        array[ 
            -- fmt: off
            ( my_key || '_' || (blob #>> '{key,value}')
            -- fmt: on
            )
        ] as new_blob
    from my_table

this still results in:

sqlfmt encountered an error: Closing bracket ']' found at 195 does not match last opened bracket '(' found at 102.

only if you add the format comment on this:

    select *,
        array[ 
            ( my_key || '_' || (
                    -- fmt: off
                    blob #>> '{key,value}'
                    -- fmt: on
                )
            )
        ] as new_blob
    from my_table

it works but that is just bizare.

So, there is no plan to support this yet?

[tconbeer]

The standalone XOR is harder, but we could fix the JSON operators with a tweak to the lexer easily enough.