Skip to content
Merged
Show file tree
Hide file tree
Changes from all commits
Commits
File filter

Filter by extension

Filter by extension

Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
14 changes: 7 additions & 7 deletions internal/stackql/sqlengine/internal/sqlfuncs/DIVERGENCES.md
Original file line number Diff line number Diff line change
Expand Up @@ -84,13 +84,13 @@ Preserved C behaviors (parity):
- Numbers compare as IEEE 754 doubles using cJSON's `DBL_EPSILON` rule
(`|a-b| <= max(|a|,|b|) * DBL_EPSILON`), so `1` equals `1.0` and one-ULP
differences are equal (vector `cref_number_epsilon`).
- `json_equal`: NULL arguments and parse failures are **errors**, not NULL
results. Object comparison is unordered with case-*sensitive* keys; array
comparison is ordered.
- `aws_policy_equal`: NULL arguments and parse failures are errors, except
that byte-identical inputs return 1 *before* parsing (the C `strcmp`
shortcut, vector `cref_strcmp_shortcut` - even non-JSON identical strings
compare equal).
- `json_equal`: NULL arguments yield SQL NULL (stackql issue #914); parse
failures are errors. Object comparison is unordered with case-*sensitive*
keys; array comparison is ordered.
- `aws_policy_equal`: NULL arguments yield SQL NULL (stackql issue #914);
parse failures are errors, except that byte-identical inputs return 1
*before* parsing (the C `strcmp` shortcut, vector `cref_strcmp_shortcut`
- even non-JSON identical strings compare equal).

## aws_policy_equal comparison rules

Expand Down
Original file line number Diff line number Diff line change
Expand Up @@ -34,9 +34,12 @@ var awsUnorderedFields = map[string]struct{}{

// AWSPolicyEqual implements aws_policy_equal(a, b): 1 if the two IAM policy
// documents are equivalent under the retired C normalization rules (see
// awsPolicyCompare and DIVERGENCES.md), else 0. NULL or unparseable
// arguments are errors.
// awsPolicyCompare and DIVERGENCES.md), else 0. NULL arguments yield SQL
// NULL; unparseable arguments are errors.
func AWSPolicyEqual(a, b any) (any, error) {
if a == nil || b == nil {
return nil, nil //nolint:nilnil // A SQL NULL is a valid scalar result.
}
sa, okA := valueText(a)
sb, okB := valueText(b)
if !okA || !okB {
Expand Down
6 changes: 5 additions & 1 deletion internal/stackql/sqlengine/internal/sqlfuncs/jsonequal.go
Original file line number Diff line number Diff line change
Expand Up @@ -14,8 +14,12 @@ var errJSONParse = errors.New("Error parsing JSON strings") //nolint:staticcheck

// JSONEqual implements json_equal(a, b): 1 if the documents are deeply equal,
// else 0. Objects compare unordered, arrays ordered, numbers as doubles with
// the epsilon rule (1 == 1.0). NULL or unparseable arguments are errors.
// the epsilon rule (1 == 1.0). NULL arguments yield SQL NULL; unparseable
// arguments are errors.
func JSONEqual(a, b any) (any, error) {
if a == nil || b == nil {
return nil, nil //nolint:nilnil // A SQL NULL is a valid scalar result.
Comment on lines +20 to +21
}
sa, okA := valueText(a)
sb, okB := valueText(b)
if !okA || !okB {
Expand Down
Original file line number Diff line number Diff line change
Expand Up @@ -138,8 +138,8 @@
null,
"{}"
],
"error": true,
"note": "NULL arguments raise an error in the C implementation, not NULL"
"note": "NULL arguments yield SQL NULL (stackql issue #914), not an error",
"result": null
},
{
"id": "cref_null_rhs",
Expand All @@ -148,7 +148,18 @@
"{}",
null
],
"error": true
"result": null,
"note": "NULL arguments yield SQL NULL (stackql issue #914), not an error"
},
{
"id": "cref_null_both",
"source": "C reference implementation behavior (aws_policy_equal.c)",
"args": [
null,
null
],
"note": "NULL arguments yield SQL NULL (stackql issue #914), not an error",
"result": null
},
{
"id": "cref_strcmp_shortcut",
Expand Down
Original file line number Diff line number Diff line change
Expand Up @@ -153,8 +153,8 @@
null,
"{}"
],
"error": true,
"note": "NULL arguments raise an error in the C implementation, not NULL"
"note": "NULL arguments yield SQL NULL (stackql issue #914), not an error",
"result": null
},
{
"id": "cref_null_rhs",
Expand All @@ -163,7 +163,18 @@
"{}",
null
],
"error": true
"result": null,
"note": "NULL arguments yield SQL NULL (stackql issue #914), not an error"
},
{
"id": "cref_null_both",
"source": "C reference implementation behavior (json_equal.c)",
"args": [
null,
null
],
"note": "NULL arguments yield SQL NULL (stackql issue #914), not an error",
"result": null
},
{
"id": "cref_invalid_json",
Expand Down
81 changes: 81 additions & 0 deletions internal/stackql/sqlengine/sqlite_embedded_test.go
Original file line number Diff line number Diff line change
Expand Up @@ -489,3 +489,84 @@ func TestCallerDirectoryForeignKeysOnEveryConnection(t *testing.T) {
t.Fatal("second connection accepted an orphan membership")
}
}

// TestSQLiteEmbeddedNullOptionalPropertyComparison pins the issue #914
// contract: a SQL NULL optional property must not abort the whole result set,
// and WHERE comparison = 1 must exclude the resulting SQL NULL rows. SQL NULL
// stays distinct from the JSON text 'null', which remains comparable.
func TestSQLiteEmbeddedNullOptionalPropertyComparison(t *testing.T) {
eng := newTestEngine(t, "")
if _, err := eng.Exec(`CREATE TABLE opt_cmp (id INTEGER PRIMARY KEY, lhs TEXT, rhs TEXT)`); err != nil {
t.Fatalf("create table: %v", err)
}
inserts := []string{
`INSERT INTO opt_cmp VALUES (1, '{"a":1}', '{"a":1}')`,
`INSERT INTO opt_cmp VALUES (2, NULL, '{"a":1}')`,
`INSERT INTO opt_cmp VALUES (3, '{"a":null}', '{"a":null}')`,
}
for _, q := range inserts {
if _, err := eng.Exec(q); err != nil {
t.Fatalf("insert: %v", err)
}
}

for _, fn := range []string{"json_equal", "aws_policy_equal"} {
t.Run(fn, func(t *testing.T) {
assertOptionalComparisonRows(t, eng, fn)
assertOptionalComparisonFilteredRows(t, eng, fn)
})
}
}

func assertOptionalComparisonRows(t *testing.T, eng *sqLiteEmbeddedEngine, fn string) {
t.Helper()
rows, err := eng.Query(`SELECT id, ` + fn + `(lhs, rhs) FROM opt_cmp ORDER BY id`)
if err != nil {
t.Fatalf("read all rows: %v", err)
}
defer rows.Close()
got := map[int64]any{}
for rows.Next() {
var id int64
var cmp any
if err := rows.Scan(&id, &cmp); err != nil {
t.Fatalf("scan row %d: %v", id, err)
}
got[id] = cmp
}
if err := rows.Err(); err != nil {
t.Fatalf("rows: %v", err)
}
if got[1] != int64(1) {
t.Fatalf("row 1 (%s) = %#v, want 1", fn, got[1])
}
if got[2] != nil {
t.Fatalf("row 2 (%s) = %#v, want SQL NULL", fn, got[2])
}
if got[3] != int64(1) {
t.Fatalf("row 3 (%s) = %#v, want 1 (JSON null stays comparable)", fn, got[3])
}
}

func assertOptionalComparisonFilteredRows(t *testing.T, eng *sqLiteEmbeddedEngine, fn string) {
t.Helper()
filtered, err := eng.Query(`SELECT id FROM opt_cmp WHERE ` + fn + `(lhs, rhs) = 1 ORDER BY id`)
if err != nil {
t.Fatalf("filter rows: %v", err)
}
defer filtered.Close()
var ids []int64
for filtered.Next() {
var id int64
if err := filtered.Scan(&id); err != nil {
t.Fatalf("scan filtered row: %v", err)
}
ids = append(ids, id)
}
if err := filtered.Err(); err != nil {
t.Fatalf("filtered rows: %v", err)
}
if len(ids) != 2 || ids[0] != 1 || ids[1] != 3 {
t.Fatalf("filtered ids = %v, want [1 3]", ids)
}
}
Loading