COALESCE(NULL, integer_column) fails with type mismatch via simple protocol

[johannesdb]

When using simple protocol (raw SQL, no bind parameters), COALESCE(NULL, integer_column) fails with:

ASSIGNMENT_CAST: target is of type integer but expression is of type text: coalesce(NULL,table.column)

Standard PostgreSQL infers the type of NULL from context in COALESCE expressions. DoltgreSQL appears to default untyped NULL to text.

Reproduction

CREATE TABLE test_coalesce (id UUID PRIMARY KEY, val INTEGER NOT NULL DEFAULT 0);
INSERT INTO test_coalesce VALUES ('00000000-0000-0000-0000-000000000001', 42);
UPDATE test_coalesce SET val = COALESCE(NULL, val) WHERE id = '00000000-0000-0000-0000-000000000001';
-- Expected: no-op, val stays 42
-- Actual: ASSIGNMENT_CAST error

Workaround

Use CASE WHEN FALSE THEN NULL ELSE val END instead of COALESCE(NULL, val), or use explicit cast COALESCE(NULL::integer, val).

Notes

Same issue affects DATE columns: COALESCE(NULL, date_column) also fails.

[johannesdb]

Hi team — bumping this gently now that @zachmu is active this week (thanks for the turnaround on #2581!). Before anything else: this isn't blocking us — we have workarounds — so please treat this as low-priority.

Still reproduces on DoltgreSQL 0.56.1, for both INTEGER and DATE columns. The issue is that untyped NULL in COALESCE(NULL, col) is treated as text rather than resolved from the second argument's type, which standard PostgreSQL does.

Runnable Go reproducer

package doltgres_test

import (
	"context"
	"testing"

	"github.com/jackc/pgx/v5/pgxpool"
)

func TestCoalesceNullWithTypedColumn(t *testing.T) {
	ctx := context.Background()
	pool, err := pgxpool.New(ctx, "postgres://postgres:password@localhost:5432/testdb?sslmode=disable")
	if err != nil {
		t.Skip("DoltgreSQL not available")
	}
	defer pool.Close()

	pool.Exec(ctx, `DROP TABLE IF EXISTS test_coalesce`)
	pool.Exec(ctx, `CREATE TABLE test_coalesce (
		id UUID PRIMARY KEY,
		val INTEGER NOT NULL DEFAULT 0,
		d   DATE
	)`)
	pool.Exec(ctx, `INSERT INTO test_coalesce VALUES
		('00000000-0000-0000-0000-000000000001', 42, '2026-01-01')`)

	cases := []struct {
		name, sql string
	}{
		{"COALESCE(NULL, int_col)",
			`UPDATE test_coalesce SET val = COALESCE(NULL, val) WHERE id = '00000000-0000-0000-0000-000000000001'`},
		{"COALESCE(NULL, date_col)",
			`UPDATE test_coalesce SET d = COALESCE(NULL, d) WHERE id = '00000000-0000-0000-0000-000000000001'`},
		{"COALESCE(NULL::integer, int_col) — workaround",
			`UPDATE test_coalesce SET val = COALESCE(NULL::integer, val) WHERE id = '00000000-0000-0000-0000-000000000001'`},
	}
	for _, tc := range cases {
		_, err := pool.Exec(ctx, tc.sql)
		if err != nil {
			t.Logf("[%s] ERR: %v", tc.name, err)
			continue
		}
		t.Logf("[%s] OK", tc.name)
	}
}

Output on 0.56.1:

[COALESCE(NULL, int_col)] ERR: ERROR: ASSIGNMENT_CAST: target is of type integer but expression is of type text: coalesce(NULL,test_coalesce.val)
[COALESCE(NULL, date_col)] ERR: ERROR: ASSIGNMENT_CAST: target is of type date but expression is of type text: coalesce(NULL,test_coalesce.d)
[COALESCE(NULL::integer, int_col) — workaround] OK

Workarounds (so you know it's not blocking us)

• Explicit cast: COALESCE(NULL::integer, val) ✅
• CASE fallback: CASE WHEN FALSE THEN NULL ELSE val END ✅

Flagging it because it surprises people porting standard PostgreSQL code — most ORMs/query builders emit untyped NULL without thinking about it. But we're fine with the workarounds for now.

Happy to help however is useful — simplify the repro, test a fix branch, or investigate whether this is in the type-resolution layer vs. parser. Just let me know. Thanks again for the great work on DoltgreSQL.