Compare filter pushdown for a remote SQL query

This example uses the same MatrixOne server as a remote MySQL-protocol source, with the local demo login root/111. ENGINE = SQL also supports PostgreSQL; see ESQL_TVF for the Elasticsearch configuration.

CREATE DATABASE cookbook_remote;
CREATE TABLE cookbook_remote.src(id INT PRIMARY KEY, name VARCHAR(50));
INSERT INTO cookbook_remote.src VALUES (1,'alice'),(2,'bob'),(3,'carol');
CREATE EXTERNAL TABLE cookbook_remote.plain(id INT, name VARCHAR(50))
ENGINE = SQL WITH ('config' = '{"driver":"mysql","dsn":"root:111@tcp(127.0.0.1:6001)/cookbook_remote"}',
                   'query' = 'SELECT id,name FROM src');
CREATE EXTERNAL TABLE cookbook_remote.pushed(id INT, name VARCHAR(50))
ENGINE = SQL WITH ('config' = '{"driver":"mysql","dsn":"root:111@tcp(127.0.0.1:6001)/cookbook_remote"}',
                   'query' = 'SELECT id,name FROM src', 'pushdown' = 'true');
SELECT id,name FROM cookbook_remote.plain WHERE id > 1 ORDER BY id;
SELECT id,name FROM cookbook_remote.pushed WHERE id > 1 ORDER BY id;

Both queries should return (2,bob) and (3,carol). With pushdown=true, remote result column names must match the declarations. Configuration can contain credentials; use session configuration or a protected connection method in production. Clean up with DROP DATABASE cookbook_remote;.

An ESQL external table needs Elasticsearch. The source repository provides a full local path: run make test-esql-tvf-e2e-local from its root. The test starts Elasticsearch and MatrixOne, seeds emp data, creates an ENGINE = ESQL external table, queries it, and checks the result. See the ESQL E2E implementation.