IS NOT NULL parsing errors

[icio]

The following query:

f IS NOT NULL AND v > 10

is being parsed as:

("f") IS NOT ((NULL) AND (("v") > (10)))

but it should be being parsed as:

("f" IS NOT NULL) AND ("v" > 10)

We can see this with the following snippet: https://go.dev/play/p/RI86BpGBz5s

package main

import (
	"fmt"
	"log"
	"strings"

	"github.com/rqlite/sql"
)

func main() {
	for _, q := range []string{
		`f IS NULL`,
		`f IS NULL AND v > 10`,
		`f IS NOT NULL`,
		`f IS NOT NULL AND v > 10`,
	} {
		expr, err := sql.NewParser(strings.NewReader(q)).ParseExpr()
		if err != nil {
			log.Fatal(err)
		}
		fmt.Printf("%s %T\n\t%s\n", q, expr, brackets(expr))
	}
}

func brackets(e sql.Expr) sql.Expr {
	switch e := e.(type) {
	case *sql.UnaryExpr:
		e.X = &sql.ParenExpr{X: brackets(e.X)}
	case *sql.BinaryExpr:
		e.X = &sql.ParenExpr{X: brackets(e.X)}
		e.Y = &sql.ParenExpr{X: brackets(e.Y)}
	}
	return e
}

Produces the following output:

f IS NULL *sql.Null
	"f" IS NULL
f IS NULL AND v > 10 *sql.BinaryExpr
	("f" IS NULL) AND (("v") > (10))
f IS NOT NULL *sql.BinaryExpr
	("f") IS NOT (NULL)
f IS NOT NULL AND v > 10 *sql.BinaryExpr
	("f") IS NOT ((NULL) AND (("v") > (10)))

The IS NULL examples shown there are correct and are parsed as *sql.Null.

Confirming this behaviour with SQLite:

sqlite> CREATE TABLE fv (f, v);
sqlite> INSERT INTO TABLE fv VALUES (NULL, 0), (NULL, 20), (true, 0), (true, 20);
sqlite> SELECT * FROM fv;
+---+----+
| f | v  |
+---+----+
|   | 0  |
|   | 20 |
| 1 | 0  |
| 1 | 20 |
+---+----+

sqlite> SELECT * FROM fv WHERE f IS NOT NULL AND v > 10;
+---+----+
| f | v  |
+---+----+
| 1 | 20 |
+---+----+
sqlite> SELECT * FROM fv WHERE ("f") IS NOT ((NULL) AND (("v") > (10)));
+---+----+
| f | v  |
+---+----+
|   | 0  |
| 1 | 0  |
| 1 | 20 |
+---+----+

Thanks for your continued support with these issues @otoolep 🙏

[otoolep]

Thanks for the report. Do you know if this is called out specifically in the syntax docs for SQLite?

[icio]

Perhaps this operator precedence table? https://www.sqlite.org/lang_expr.html#operators_and_parse_affecting_attributes