What a complete adapter would have, sized and reasoned, so that the next session picks up an argument rather than a list.
Sizes. Small is a translator case and a test. Medium is a node, a rule, or an SDK surface.
Large needs a design decision recorded in DESIGN.md before any code.
On finishing. When an item is done, remove it — the entry, its rationale, and any done marker
elsewhere in this file. This file holds only work still to be done; what was decided belongs in
DESIGN.md, what was built is visible in the code and its tests, and history lives in git. A done
paragraph kept here is a second copy of one of those, aging independently.
On testing a change. Before believing a test covers what it claims, check that it fails without the change. This has repeatedly told both stories: fixes whose tests genuinely depended on them, and guards that turned out to be dead code — the case they guarded already unreachable. Neither is visible from a green suite.
On evidence. Where a claim about the service is unverified it says so. The emulator has disagreed
with Azure in both directions — accepting an ORDER BY over an unnest alias that Azure rejects, and
rejecting the full text search Azure runs — so "the reference says" is not a measurement. Point
COSMOS_TEST_ENDPOINT and COSMOS_TEST_KEY at a real account and the suite runs against one.
534 tests: 528 passing, 6 skipped, on net8.0 and net10.0, against Apache.Calcite 2.0.0-pre.7.
The skips are things only a real account can answer; the suite runs against one when
COSMOS_TEST_ENDPOINT and COSMOS_TEST_KEY name it, and reports inconclusive rather than passing
where the emulator cannot — and each of them detects the gap it is skipping for, so an environment
that closes one asserts rather than going quiet. Several facts in this file and in DESIGN.md were
settled by measurement, each time with an Azure account, used and deleted.
No PRs are open and nothing is parked; main is where the work is and a new branch starts from it.
Reading, writing (INSERT, DELETE including the whole-partition form, and UPDATE of the map
column as a whole-document replace), the lookup join, partial aggregates, DISTINCT, the scalar
functions and the diagnostics surface are complete and covered. What remains below is not started.
Declared columns were built and then dropped, and the shape of the hole they left is worth
knowing before anyone rebuilds them. A caller-declared, typed document path promoted to a real
column — through a columns operand — would have given four things a type to work with: a
patchable UPDATE target, an argument the nullable-aggregate rewrite could fire on, a declared
temporal representation, and a non-nullable sort key for DISTINCT with ORDER BY. Every one of
those items below still names that dependency, because the dependency is real; what is gone is one
answer to it, not the question.
dotnet run --project samples/Apache.Cosmos.Sample/Apache.Cosmos.Sample.csproj
It needs the Cosmos emulator on localhost:8081 and prints the docker command if it is missing. It
seeds both sources and is safe to re-run. What it demonstrates is the lookup join across two adapters:
the CSV side's three product ids are pushed into Cosmos, so the container is filtered at the service
rather than read whole.
Two things trip a host and fail with messages that name nothing useful: the calc rules must run as a
pass after the planner (Programs.CALC_PROGRAM's shape — given to Volcano they do nothing), and a
model must name CosmosSchemaFactory assembly-qualified with the assembly already loaded. The README
carries both with the reasoning.
- A metadata cache on the schema (section 1) — the design is settled and the cost is real:
every connection re-reads a container's definition, and any connection planning a
whole-partition
DELETEre-probes the account. One thing to verify before building it — whetherApache.Calcite.Dataoffers a supported way to hand back the same schema instance. - An explicit statistics refresh (section 1) — the time to live is in; what is missing is a way for a caller to say now, which after a bulk load is the only moment that matters.
- Typed columns, if they are wanted at all (section 6) — four items name this dependency, and
nothing satisfies it. Whether the answer is a
columnsoperand, computed properties, or something else is open again.
getStatistic reports keys from declared metadata and a row count read from the service; the cost
model is still inference over constants. Everything here is a read of something the service already
knows, or the model those reads deserve.
Average document size is already derived from the container's resource usage and not yet used. It is what a row costs to move, which for a map row model carrying whole documents dominates.
ReadThroughputAsync. Not needed to compare two plans, but it is the denominator that turns an RU
estimate into a latency estimate, and it distinguishes a container that can absorb a scan from one
that cannot.
The measured charge is on the cosmos.request_charge histogram and the cosmos.query span. A
measured charge for a query shape is worth more than the estimate that was used to choose it, but a
cost model that learns needs somewhere to keep what it learnt — the statistics-refresh question
below wearing a different hat.
Per-partition storage is an Azure Monitor metric, not data plane. The count is reachable and the distribution is not, so a hot-partition estimate would have to come from outside the adapter.
CosmosSchemaFactory.create runs per model read, which in the ADO.NET path is per connection, so
every connection builds fresh CosmosContainerMetadata and with it fresh lazy cells. Within a
connection each fact is computed once; across connections nothing is shared, though the client can
be. That was two round trips per container for statistics; the whole-partition delete capability
adds a third for any connection that plans one, and a short-lived-connection application pays them
all again each time.
The cache hangs off the schema, which is where the lookup cache already hangs and for the same
reason: no global static, no leak between accounts, and the lifetime is the caller's to choose. It
does not help a host that rebuilds its schema per connection — but that is the honest shape, because
the alternative is a process-wide cache keyed by CosmosClient.Endpoint that outlives every
decision anyone made about it. ADO.NET pushes callers to recreate connections freely and pool them
underneath; reusing the schema across those connections is the documented way to keep what it
learnt, and the README should say so beside the client-factory guidance.
Three facts, three lifetimes, and they are not the same:
- The container definition — partition key paths, indexing policy. Changes only by a control plane operation; cache for the life of the schema.
- The whole-partition delete capability — a property of the account, changed only by a support request. Same treatment.
- Statistics — genuinely mutable, and sharing them across connections is what makes Statistics refresh below load-bearing rather than theoretical: without a time to live, one connection's stale row count would outlive the connection that fetched it.
Worth verifying first: how a host reuses a schema through Apache.Calcite.Data, since the model
path builds one per connection and the guidance is only actionable if there is a supported way to
hand the same instance back.
A row count now expires and is read again — five minutes by default, statisticsExpireSeconds to
say otherwise — so a long-lived schema no longer plans for ever against the first number it saw.
What a time to live cannot do is let a caller say now: after a bulk load, the useful moment to
re-read is the one the caller knows about and the clock does not. Drill's answer is a metastore that
an explicit ANALYZE TABLE COMPUTE STATISTICS populates, which decouples the fetch from the query
altogether and is the shape worth copying.
Flink collects connector statistics after partition pruning and filter pushdown, so the number the
planner sees describes the scan it will actually do rather than the whole table. Here that would mean
a row count for a partition-pinned scan rather than for the container — which is the difference
between costing a single-partition read and costing everything. It needs a statistic attached to a
RelNode rather than to a table, which is a larger change than it sounds.
The above are inputs; this is the model. Cosmos charges in RUs and the current model multiplies Calcite's abstract cost by constants. A model in RUs — a point read is 1, a query is 2.3 plus scanned size, a cross-partition query is that times the fan-out — would make pushdown decisions comparable with in-process alternatives on a real scale rather than a notional one.
Queries execute through GetItemQueryStreamIterator, with a pinned id and complete partition key
recovered as a point read. The SDK's other cheap routes are unused.
Apache.Calcite 2.0.0-pre.3 fixed the ADO.NET adapter against SQL Server (calcite-dotnet#24), so the sample's CSV side can become the SQL Server it was meant to be — a one-line change, plus the SQL Server the sample would then need running beside the emulator, which is the actual decision. Nothing in CI runs the sample either way; it was last verified by hand against pre.3.
GetChangeFeedIterator is a fundamentally different read: ordered by _ts within a partition,
resumable, and the basis of every incremental pipeline built on Cosmos. It is not a table in the
relational sense — it has no end — so exposing it means deciding what it is to Calcite: a table
function taking a start time, a streaming source, or something a caller drives and the adapter only
materializes.
A query's continuation token makes a result resumable, and the adapter reads every page eagerly within
one enumeration. GROUP BY and DISTINCT results are documented as not resumable, which is a
constraint on where this can apply rather than a reason not to.
Writes are item CRUD behind a TableModify — Cosmos SQL has no DML, and does not need to for the
adapter to write. What each statement does and refuses is recorded in DESIGN.md under Writing.
SET "_MAP" = … executes as a whole-document replace. What remains is the cheap tier: a targeted
SET of a plain document property as PatchItemAsync, sending changed properties rather than the
document. It has no targets: SET names a column, the map column is the whole document, and the
promoted columns are id, the partition keys and the system properties — every one of them either
immutable or not worth patching. So the tier is one rule-and-writer step behind something that
gives a document path a column of its own; see section 6. The execution ladder above it (static
decomposition via a mutation operator, the diff and blind-patch optimizations) is recorded in
DESIGN.md under Updating.
A predicate pinning exactly the complete partition key plans as
DeleteAllItemsByPartitionKeyStreamAsync — one request, no query — with a probed account
capability deciding which way the rule goes, and COUNT(*) first for the affected count. The
design is in DESIGN.md under Deleting a whole partition; the fallback is exercised, and the
fast path is not, because no account this repository can reach will run it.
The gate is a support request, not a switch. The capability is an account capability —
az cosmosdb update --capabilities DeleteAllItemsByPartitionKey — not a subscription preview
registration, which is what made it look portal-only the first time it was measured. Set on a fresh
account and reported back by az cosmosdb show, the operation still answers 400:
Partition key delete feature is disabled for this account. Please contact Azure Support to enable it.
What remains, therefore, is a measurement on an account somebody has had enabled: that the fast
path fires, what it costs against the per-document loop, and one documented hazard worth confirming
— an index-using COUNT issued during an ongoing delete may still count the documents being
removed, which decides whether the reported count can be trusted. Hierarchical partition keys are
documented as unsupported, which the recovery condition already required.
TransactionalBatch is atomic within a single partition key. That is a real transactional guarantee
Calcite has no way to ask for, so exposing it means a session-level or hint-level surface rather than
SQL.
CosmosClientOptions.AllowBulkExecution changes the throughput profile of many small writes
dramatically. A client factory can already set it; whether the adapter should is a question about who
owns the client.
- Spatial — medium.
ST_DISTANCE,ST_WITHIN,ST_INTERSECTS,ST_ISVALID. Calcite has a spatial operator library to map from, so the mapping is mechanical; the geometry representation and what a spatial index makes cheap are not.
EXISTSover an item-scoped subquery — large.EXISTS (SELECT VALUE t FROM t IN c.tags WHERE …)is a semi-join over a nested array. Today the only route to a nested array isUnnest, which cross-products the document with it and de-duplicates above — the wrong shape and the wrong cost for an existence test.- Scalar and multi-value subqueries — medium. Item-scoped only; there are no derived tables. The
correlated forms are what
ARRAY(SELECT …)andIN (SELECT …)need.
- Temporal is what is left, and its blocker is a declared representation rather than a translation — see below.
- A host must chain a library operator table to name
LEFT,RIGHT,REVERSEorREPEATat all: Calcite's standard table carries none of them, and the adapter translates whatever arrives rather than deciding which library a caller uses. Worth a line in the README beside theCosmosOperatorschaining it already documents — small. - Currently declined, admissible with work —
SUBSTRINGwithout a length (LENGTH(s)supplies it);LIKEwithESCAPE, and a bracket-escaping rewrite that would lift the bracket-pattern decline (CosmosLIKEreads[…]as a character range where SQL does not — measured, and why bracket and computed patterns are refused);TRIMof a non-space character andTRUNCATEto decimal places, both needing Cosmos's two-argument arity verified first;IS TRUE/IS FALSE/IS DISTINCT FROM, expressible with the??operator once the null-versus-undefined semantics are measured.
Cosmos has DateTimeAdd, DateTimeDiff, DateTimePart, DateTimeBin and tick conversions; Calcite
has EXTRACT, TIMESTAMPADD, TIMESTAMPDIFF. The mapping is mechanical and the representation is
not: a date is an ISO string or an epoch number by application convention, and _ts is the only value
whose encoding the service defines. Pushing a temporal function down means knowing what the column
is, and nothing in the row model says. _ts alone is reachable without answering that; everything
else waits on section 6.
- Native
INandBETWEEN— closed by measurement, not built.expandSearchturns both into comparison chains, and the question was whether the native spelling is priced differently. Measured on a real account over five hundred documents:INand its OR-chain cost identically at three, ten and fifty values — 6.06, 7.62 and 16.52 RU, matching to the hundredth — andBETWEENcosts exactly what its two comparisons do (7.90 RU). Neither form used an index on an unindexed path, so the reference's "index-friendly" is a property of the path rather than of the spelling. Emitting the native form would be a change with no effect. DISTINCTwithORDER BYreaches only non-nullable keys — small, and it waits on a column that can be non-nullable. The combination pushes as one statement now, but the null-placement rule refuses any nullable sort key — Calcite's ascending means nulls last and Cosmos sorts them first — and everything reachable within the map column is nullable, so today it reaches_ts,idand_etagalone. A user path stated non-nullable would extend it; the rule itself is correct and should not move.TOP— closed by the same measurement. Emitted for a rank clause and nowhere else.TOP 10andOFFSET 0 LIMIT 10cost the same 2.37 RU on a real account, so the spelling the adapter already emits is the cheaper of nothing.
The null-semantics refusals are the biggest source of declined aggregates: SUM(c.v) over a nullable
column is undefined at the service where SQL skips the null. The fix is rewriting the rendered
argument so Cosmos skips it too — aggregates skip undefined, and arithmetic on a JSON null yields
it, so SUM(c.v * 1) is the candidate for a column known to be numeric. The rewrite is type-directed
and cannot be applied blindly (* 1 over a string silently drops it from MIN/MAX), and a path
inside the map column is ANY — so there is nothing to fire on until section 6 has an answer.
Measure on the emulator before building: that the null is skipped, that an all-null group comes back
as SQL's null does, and that * 1 does not disturb a large integer.
- Unique key policy — small. Declared unique keys are keys
getStatisticdoes not report. - Computed properties — medium. A container can declare named, queryable, indexable computed paths. Declared metadata is the one kind this adapter trusts, so they should promote to real columns with real index awareness rather than living in the map column.
SELECT VALUEfor a single column —DESIGN.mdchose the uniform object form deliberately, "whatever the arity", and the materializer depends on it. A single-column projection could be bare scalars. Reversing a recorded decision is the work; the code is trivial.SELECT *sends promoted columns twice —_MAPis the whole document and every promoted column is a path within it. Reading them out of the map value client-side would avoid it; the saving is a few short scalars against a whole document, so smaller than it first looks.
- A typed column over a document path — large, and it is a question before it is work. Four
items converge here and none of them can move without it: the
UPDATEpatch tier (section 3), a temporal basis (section 4), the nullable-aggregate rewrite andDISTINCTwithORDER BY(sections 5 and 4). Each needs the same thing — a document path the planner can see the type of, and in one case the nullability of — and the map column gives itANY. Acolumnsoperand taking caller-declared paths was built for this and dropped; it is not the only shape. Computed properties (section 5) are the other candidate and a materially different one: the container declares them, so the adapter would be reading metadata it already trusts rather than taking a caller's word, and they are indexable — but the caller must create them on the container first, and their type still is not declared anywhere the adapter can read. Whichever way, this is a new public surface and wants a decision recorded inDESIGN.mdbefore any code. - Binary — small.
BINARY/VARBINARYread base64 from a JSON string. Unverified against the service, because nothing in the test data is binary. - Temporal representation — see Temporal above. The reading side handles ISO strings and epoch numbers; what is missing is any declared basis for deciding which a column holds.
- Schema functions — medium, and the trade is the decision.
CosmosOperators.Instanceis aSqlOperatorTablea caller must chain, which is why full text is unreachable from a bareCalciteConnection. Registering the functions on the schema instead meansScalarFunctionImplover real CLR methods, and these functions cannot execute outside Cosmos, so those methods would throw: a non-pushed query becomes a run-time failure mid-enumeration rather than a plan-time refusal. That downside is measured and pinned byCosmosFunctionResolutionTests; there is also nofun=cosmosroute,SqlLibrarybeing a closed enum. - Connection options as operands — small. Consistency level, preferred regions, application name, for callers who do not want to write a factory.
- Lazy subschemas — small. Container definitions are read eagerly when an account-level schema
is built, so an account with many databases pays a read per container to reach one. Statistics are
already lazy; the definitions want a lazy
Map. - Client disposal — small. The schema owns a client for the life of the process because Calcite
offers no disposal hook. Worth revisiting against
SchemaPlusrather than left as a comment. - Server-side functions — medium. Cosmos has stored procedures and JavaScript UDFs. A UDF is nameable in a query, so it could be exposed as a Calcite operator the way the built-ins are.
CosmosDiagnostics— small. The one signal not surfaced: a large JSON blob per response, so it wants a switch of its own rather than to ride on thecosmos.queryspan.- RU regression tracking — medium. The charge is on the histogram; assert that a query shape does not get more expensive.
- A real account in CI — medium. The emulator accepts statements Azure rejects and rejects
features Azure implements; both have been found by hand. A nightly job against a real account is
what stops the next one being found by a user — and it is where
CosmosDifferentialTestsand the routing measurement rerun their evidence against the real service. - Growing the differential corpus — small, forever. The harness is done (
DESIGN.mdunder Differential testing); every new pushdown should bring its statements to the corpus, and every translator addition is a candidate. Probed and in: filters, sorts, the aggregate forms,LIKE, and the array traversal — the guess that the oracle could not evaluate an in-process unnest was wrong, and the corpus says so. - Emulator gaps, asserted — done for the two that were wrong; keep the shape. A skip must be
earned by detecting the gap, never by asking which endpoint answered: the flat request charge and
the discarded composite index were both hard-coded to
IsEmulator, so an emulator that fixed either would have gone on skipping for ever. Both now measure the gap and report it, and the index-metrics pair already did. Any future gap belongs in that shape. (Retained here as the rule rather than as a task.) - A malformed response's failure mode — small. A lookup-join stub returning raw documents instead of the statement's projection once produced a null reference inside the join's result selector, and which access produced it was never established. Worth knowing whether a malformed service response fails loudly or quietly.
These are not features. They are things believed but not measured, and each one is a defect waiting for the right query.
- Two-argument
TRIMandTRUNCATE. Left out for want of a measurement.
Flink is the most complete Calcite-based connector framework in the open, and its source and sink ability interfaces are a catalogue of what a pushdown-capable connector can offer. Each row below is an interface a Flink connector implements and what it would mean for Cosmos; abilities already covered here are not listed. Every Cosmos operation named was compile-checked against the SDK this project references.
| Flink | Here |
|---|---|
SupportsPartitionPushDown |
worth taking. Hands the planner the list of partitions. GetFeedRangesAsync gives the physical ones. |
SupportsDynamicFiltering, SupportsLookupCustomShuffle |
closed by measurement — the service's query router already prunes an IN over the partition key to the partitions owning the values, cross-partition execution already fans out per feed range, and per-key routing costs the per-query floor times the key count. See The lookup restriction is already routed in DESIGN.md; CosmosLookupRoutingMeasurementTests reruns the evidence against any real account. |
SupportsReadingMetadata |
small. Metadata columns declared rather than always promoted: _rid, _self, _attachments, and the per-item ttl. Would also let _ts/_etag stop occupying ordinary column ordinals. |
SupportsRowLevelModificationScan |
worth taking. The scan is told it is feeding an UPDATE/DELETE, so it can read only what the modification needs. Both are implemented and read whole documents to use two paths out of them — id and the partition key — which for a map row model is the whole cost of the statement. |
SupportsWatermarkPushDown, SupportsSourceWatermark |
only with the change feed. Streaming concepts; the change feed is the analogue, and _ts the natural watermark. See change feed. |
| Flink | Here |
|---|---|
FullCachingLookupProvider |
worth considering for small containers: load the whole thing once and never call the service on a miss, with a reload strategy. A lookup table of a few thousand documents is exactly this. The partial cache — per execution and, by declared policy, across them — is done; see DESIGN.md under The lookup join's caches. |
| Lookup retry (FLIP-234) | probably not. Flink retries a lookup that comes back empty, for late-arriving reference data. The SDK already retries throttling, which is the failure that actually happens here. |
| Flink | Here |
|---|---|
SupportsTargetColumnWriting, SupportsRowLevelUpdate |
Patch — the UPDATE tier, waiting on a column a SET could target; see sections 3 and 6. |
SupportsDeletePushDown |
Done, and unverifiable — the whole-partition delete plans and is gated on a probed account capability; see section 3. |
SupportsTruncate |
TRUNCATE TABLE — per-partition deletes, or recreating the container, which is cheaper and has different semantics. Worth deciding deliberately rather than by default. |
SupportsOverwrite |
Upsert, which is native (UpsertItemStreamAsync). |
SupportsPartitioning |
Writes routed by partition key. Bulk mode already groups by partition, so this is mostly about telling the planner. |
SupportsWritingMetadata |
Writing the per-item ttl, which is a real Cosmos feature with no column to put it in today. |
SupportsStaging, SupportsBucketing |
Not applicable — two-phase commit for CTAS and bucketed layouts have no Cosmos counterpart; recorded so nobody looks again. |