Snowflake, Postgres: Parse failure on concatention operator ||

[ixti]

Search before asking

• I searched the issues and found no similar issues.

What Happened

Concatenate operand || incorrectly parsed as two pipes. Both Snowflake and Postgres recognize || as concatenation operand, thus 'a' || 'b' -- => 'ab'. SQLFluff blames it's incorrect syntax.

Expected Behaviour

'a' || 'b' should pass linting

Observed Behaviour

'a' || 'b' fails linting with:

Expected single whitespace between pipe '|' and pipe '|'.

How to reproduce

echo "SELECT 'a' || 'b' AS c;" | sqlfluff lint --dialect postgres --disable-progress-bar -
echo "SELECT 'a' || 'b' AS c;" | sqlfluff lint --dialect snowflake --disable-progress-bar -

Dialect

Snowflake
Postgres

Version

sqlfluff, version 1.4.3
Python 3.10.9

Configuration

[sqlfluff]
templater = jinja

Are you willing to work on and submit a PR to address the issue?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[barrywhart]

"For no real reason"?

[ixti]

@barrywhart

"For no real reason"?

I meant it fails when it should not (on a valid syntax). Didn't mean to offend or being rude. :D Coming from my initial assumption that SQLs I've been linting were broken. :D

[ixti]

The bug is reproducable in v1.4.3; v1.4.2 is unaffected.

[dondupont]

I am experiencing this as well. The double pipe concatenation operator is being split into two and is causing the query to fail during execution.

[kyanscienceman]

Experiencing this as well in BigQuery. Looks like | and ||are both valid BigQuery operators, so makes sense why it's happening, though I'm not sure why this only started being an issue in v1.4.3. For now we disabled L006 in our repo as a workaround.

[tunetheweb]

Just go this too. Submitting a fix.

[tunetheweb]

FYI adding this to your config fixes it:

[sqlfluff:layout:type:binary_operator]
spacing_within = touch

We're adding it to the default config in #4168 and releasing another fix tonight, but if you can't wait then apply above addition to your config.

[tunetheweb]

SQLFluff 1.4.4 has been released with this fixed in it.

Apologies!

[ixti]

@tunetheweb thank you!