between's "and" split like logical operator

[WestComputing]

Describe the bug
When using between ___ and ____, the line is always split before the and

To Reproduce
Use the between operator in a where clause, such as the following:

select
    radio,
    mcc,
    net as mnc,
    area as lac,
    cell % 65536 as cid,
    cell / 65536 as rnc,
    cell as long_cid,
    lon,
    lat
from
    towershift
where
    radio != 'CDMA'
    and mcc between 200 and 799
    and net between 1 and 999
    and area between 0 and 65535
    and cell between 0 and 268435455
    and lon between -180 and 180
    and lat between -90 and 90

Expected behavior

select
    radio,
    mcc,
    net as mnc,
    area as lac,
    cell % 65536 as cid,
    cell / 65536 as rnc,
    cell as long_cid,
    lon,
    lat
from towershift
where
    radio != 'CDMA'
    and mcc between 200 and 799
    and net between 1 and 999
    and area between 0 and 65535
    and cell between 0 and 268435455
    and lon between -180 and 180
    and lat between -90 and 90

Actual behavior

select
    radio,
    mcc,
    net as mnc,
    area as lac,
    cell % 65536 as cid,
    cell / 65536 as rnc,
    cell as long_cid,
    lon,
    lat
from towershift
where
    radio != 'CDMA'
    and mcc between 200
    and 799
    and net between 1
    and 999
    and area between 0
    and 65535
    and cell between 0
    and 268435455
    and lon between -180
    and 180
    and lat between -90
    and 90

Additional context

% sqlfmt --version
sqlfmt, version 0.5.0

sqlfmt -l 80 test.sql
1 file formatted.
0 files left unchanged.
test.sql formatted.

[tconbeer]

Thanks for the report, @WestComputing!

I agree with you that a single between ... and ... should fit on a single line if possible. Right now, sqlfmt doesn't try to distinguish between the and after a between and your typical boolean and.

I was going to propose using parens as a workaround, but unfortunately that introduces its own problems.

To fix this, we're going to need to use more context when either parsing or deciding to split a line before the and. I am leaning toward the latter, since we have a (much simpler) precedent for *. I will try to work a fix for this into the next patch release, but I don't have a timeline for that, yet. In the future, sqlfmt will probably have to get smarter about parsing complex chains of boolean operators.

Until then, you can use -- fmt: off comments as a workaround:

select
    radio,
    mcc,
    net as mnc,
    area as lac,
    cell % 65536 as cid,
    cell / 65536 as rnc,
    cell as long_cid,
    lon,
    lat
from towershift
where  -- fmt: off
    radio != 'CDMA'
    and mcc between 200 and 799
    and net between 1 and 999
    and area between 0 and 65535
    and cell between 0 and 268435455
    and lon between -180 and 180
    and lat between -90 and 90  -- fmt: on

[WestComputing]

Thanks for your speedy response, Ted!

In looking at your code, I didn't think it was going to be a quick fix for the reason you mentioned.

I appreciate your efforts!
-Julian

[tconbeer]

@WestComputing Had some time this morning; this is now fixed and the patch has been released as v0.5.1. Thanks for using sqlfmt!