Initial boilerplate for `DialectExtension`

Project: duckdb/duckdb | Language: C++ | Concepts: api-design, configuration-management | Difficulty: mid
Link: https://github.com/duckdb/duckdb/pull/24801

The problem

DuckDB needed a way to support multiple SQL dialects at the parser level. There was no mechanism to register, enumerate, or switch between different SQL dialects, nor a user-facing setting to control the current dialect. This limited extensibility and made it hard to add support for alternative SQL syntaxes.

Why it's non-trivial

A naive fix might just add a string setting for the dialect, but this would not:

Validate that the chosen dialect is actually registered/available.
Provide a way for extensions to register new dialects.
Expose a way for users to list available dialects.
Ensure that changing the dialect triggers necessary cache invalidations (e.g., parser cache).

The approach

1. Add a global current_dialect setting and its implementation.

This setting is defined in the settings JSON, with a default value and description:

+    {
+        "name": "current_dialect",
+        "description": "The SQL dialect used by the parser",
+        "type": "VARCHAR",
+        "scope": "global",
+        "default_value": "duckdb",
+        "on_callbacks": [
+            "set",
+            "reset"
+        ],
+        "custom_implementation": true
+    },

This ensures the setting is visible to users and can be changed at runtime.

The C++ implementation validates the input, checks for registration, and invalidates the parser cache:

void CurrentDialectSetting::OnSet(SettingCallbackInfo &info, Value &input) {
	if (input.IsNull()) {
		throw InvalidInputException("current_dialect setting cannot be NULL");
	}
	auto dialect_name = input.GetValue<string>();
	if (!info.config.GetCallbackManager().HasDialectExtension(dialect_name)) {
		throw InvalidInputException("Dialect \"%s\" is not installed", dialect_name);
	}
	if (info.db) {
		info.db->GetParserCache().Invalidate();
	}
}

Throws if the dialect is not registered.
Invalidates the parser cache when changed.

2. Introduce the DialectExtension class and registration mechanism.

Defines a new extension type:

+//! A named SQL dialect that can customize the PEG parser.
+class DialectExtension {
+public:
+	explicit DialectExtension(string name_p) : name(std::move(name_p)) {
+	}
+
+	string name;
+
+	static void Register(DBConfig &config, DialectExtension extension);
+};

And registers it in the extension callback manager:

+void ExtensionCallbackManager::Register(DialectExtension extension) {
+	if (extension.name.empty()) {
+		throw InvalidInputException("Dialect name cannot be empty");
+	}
+	lock_guard<mutex> guard(registry_lock);
+	auto new_registry = make_shared_ptr<ExtensionCallbackRegistry>(*callback_registry);
+	for (auto &existing : new_registry->dialect_extensions) {
+		if (StringUtil::CIEquals(existing.name, extension.name)) {
+			throw InvalidInputException("Dialect \"%s\" is already registered", extension.name);
+		}
+	}
+	new_registry->dialect_extensions.push_back(std::move(extension));
+	callback_registry.atomic_store(new_registry);
+}

Prevents duplicate or empty dialect names.
Stores registered dialects for later lookup.

3. Add a system table function to enumerate available dialects.

Defines a new table function:

+void DuckDBDialectsFun::RegisterFunction(BuiltinFunctions &set) {
+	set.AddFunction(
+	    TableFunction("duckdb_dialects", {}, DuckDBDialectsFunction, DuckDBDialectsBind, DuckDBDialectsInit));
+}

And the implementation:

+static unique_ptr<GlobalTableFunctionState> DuckDBDialectsInit(ClientContext &context, TableFunctionInitInput &input) {
+	auto result = make_uniq<DuckDBDialectsData>();
+	for (auto &dialect : ExtensionCallbackManager::Get(context).DialectExtensions()) {
+		result->dialects.push_back(dialect.name);
+	}
+	return std::move(result);
+}
+
+static void DuckDBDialectsFunction(ClientContext &context, TableFunctionInput &data_p, DataChunk &output) {
+	auto &data = data_p.global_state->Cast<DuckDBDialectsData>();
+	auto &dialect_name = output.data[0];
+	idx_t count = 0;
+	while (data.offset < data.dialects.size() && count < STANDARD_VECTOR_SIZE) {
+		dialect_name.Append(Value(data.dialects[data.offset++]));
+		count++;
+	}
+}

Allows users to run SELECT * FROM duckdb_dialects() to see all registered dialects.

4. Integrate with the configuration and function registration system.

The new setting and function are registered in the relevant places:

+    DUCKDB_SETTING_CALLBACK(CurrentDialectSetting),

and

+	DuckDBDialectsFun::RegisterFunction(*this);

ensuring they are available in the DuckDB runtime.

From the review thread

Mytherin: Thanks - should this be integrated with the setting introduced in https://github.com/duckdb/duckdb/pull/23251?

Tishj: I think eventually yes, but I'm focusing on the parser side for now.
We would also lose coverage seeing as there's no "spark" dialect added yet, so replacing test/sql/settings/dialect_compatibility_mode.test wouldn't be possible yet.

Mytherin: Alright - sounds good. Thanks!

Takeaways

When introducing extensibility points (like dialects), provide a registration API and validate user input against registered extensions.
Expose system introspection (e.g., available dialects) via a table function so users and tools can discover capabilities dynamically.
When changing configuration that affects caches or internal state, ensure appropriate invalidation or refresh logic is triggered to avoid stale behavior.

Initial boilerplate for DialectExtension