SQLite Forum

operator precedence docs missing, suggested clarifications below
Login
In https://sqlite.org/lang_expr.html section 2  BETWEEN and NOT are not placed on the list beside the other operators.

I still have some of these not quite right (see https://cgsql.dev/cql-guide/ch03/) but as far as I can tell this is correct:

```
OR
AND
NOT
BETWEEN
EQUALITY
INEQUALITY
BINARY // & | << >>
ADD // also sub
MUL // also div and mod
CONCAT
COLLATE TILDE COLLATE (order doesn't matter amongst these)
```

(presently I have CONCAT and COLLATE wrong in cgsql)

Docs: 

  The COLLATE operator has a higher precedence (binds more tightly)
  than any binary operator and any unary prefix operator except "~"

Proofs:

```
-----  NOT is weaker than BETWEEN 
select not 0 between -1 and 2; --> 0
select not (0 between -1 and 2); --> 0
select (not 0) between -1 and 2; --> 1

----- BETWEEN is weaker than equality 
select 1=2 between 2 and 2;  --> 0
select (1=2) between 2 and 2; --> 0
select 1=(2 between 2 and 2); --> 1

----- BETWEEN is left associative 
select 0 between 0 and 3 between 2 and 3; -->  0
select (0 between 0 and 3) between 2 and 3; --> 0
select 0 between 0 and (3 between 2 and 3); --> 1

---- TILDE is stronger than CONCAT 
select ~ 1||2;  --> -22
select (~ 1)||2; --> -22
select ~ (1||2); --> -13

--- NEGATION is stronger than CONCAT
select -0||1;  -> 01
select (-0)||1;  -> 01
select -(0||1); -> -1
```

NEGATION and TILDE have unambiguous order always or binding strength doesn't matter.  Collation is independent of value so binding strength there also doesn't matter.

Just in case negation was different than negative numbers:

```
select -(0)||1; -> also 01
```

more conclusively:

```
sqlite> select -5||'-5';
-5-5
sqlite> select (-5)||'-5';
-5-5
sqlite> select -(5||'-5');
-5
```
so negation definitely comes before || (with no parens)