SQL support

What the engine accepts, what it refuses, and how closely its answers match SQL's. This is a reference, not a pitch: the entries that say refused are the reason the rest can be trusted.

#How to read this page

Every row is one of three states.

There is no fourth state for silently approximated, and that is the design. A clause the engine cannot consume never becomes an empty filter, and an unsupported function is never treated as a field name — a query that returns everything because your WHERE was dropped is the failure this rule exists to prevent.

The engine also checks that it read all of your query. Each step of the translation records the characters it consumed, and anything significant left over at the end is refused, naming the text and its position rather than answering as though you had not written it. String literals and comments are exempt, because they are not code — a WHERE inside quotation marks is data.

#When a result may not match SQL

A refusal is easy to notice: nothing runs, and the reason is on screen. The dangerous case is the opposite one — a query that answers, and answers slightly differently from the SQL you wrote, so the rows look like every other correct result you have seen.

TableCore says so on the same screen as the rows. When the engine knowingly decided something SQL decides differently, the result carries a note: a sentence above the grid saying what was decided, which part of the generated pipeline decided it, and how to write the query if you meant the other thing. The footer counts them beside the row count and the duration. A query with nothing to report shows nothing, which is what keeps the notes worth reading.

Three shapes produce one today:

Everything else under Deliberate differences from SQL is documented rather than announced per query. The list grows in that direction: a difference the engine can recognise in your particular query belongs in the notes, and one that depends on what is in your data can only be written down here.

#Statements

SELECT only. Every other statement is refused by the parser with Expected SELECT or a MongoDB command document.

This is a product decision. The engine translates between two languages whose semantics do not line up perfectly. A defect in a translated SELECT shows you the wrong rows: bad, visible, and nothing on the server moved. The same defect in a translated UPDATE writes wrong data to a production database, and no version of that is recoverable. So INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, MERGE and TRUNCATE are refused, and will stay refused until there is a much better answer than "we tested it".

You can still write to MongoDB from TableCore — through the grid, the explorer, import, restore, reshape and migration, or a raw MongoDB command document. Those are direct operations, not translations. See What this release covers.

#The dialect

PostgreSQL is the reference dialect. The supported subset is a subset of PostgreSQL's SELECT, and where the SQL dialects disagree — identifier quoting, string against numeric comparison, where NULL sorts in ORDER BY, how strict GROUP BY is, integer division, whether LIKE cares about case, || against + for concatenation, booleans, date and time functions — TableCore does what PostgreSQL does. Where it cannot, it refuses rather than picking a third answer; where it knowingly differs, that difference is listed under Deliberate differences from SQL below, and the ones the engine can recognise in your particular query also appear as a note above your rows.

The tests below compare answers against SQLite, because SQLite is the engine that can be embedded in a test run. That makes SQLite the yardstick for did this change break something, not for what should this construct do — a difference from SQLite that PostgreSQL would call correct is recorded as intentional rather than counted against the engine.

Extensions carried over from the product's history:

• TOP n as well as LIMIT,
• [bracketed identifiers] for names that collide with keywords,
• OBJECTID('507f…') and OBJECTID(@id) to build an ObjectId explicitly.

Paging has two spellings of the same pair of numbers, and both plan to the same command:

SELECT * FROM users ORDER BY Name LIMIT 10 OFFSET 5
SELECT * FROM users ORDER BY Name OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY

LIMIT accepts a number or ALL, OFFSET may stand alone, and a FETCH with no count means one row. Using LIMIT and FETCH in one query is refused — they set the same limit. WITH TIES is not supported.

#Clauses

#ORDER BY

ORDER BY 2 means the second item in your SELECT list — a position, not a value. With SELECT * a column number is refused rather than guessed: SQL counts a table's declared columns, and a collection declares nothing, so the position would resolve to whichever document happened to be read first.

Sorting by an expression works; the key is computed in the pipeline and removed again afterwards. A key that does not depend on the row — ORDER BY -1, ORDER BY UPPER('x') — is refused, because a constant gives every row the same key and orders nothing.

A column number that lands on a computed expression is a known defect. SELECT a+b*2 FROM t ORDER BY 1 returns the right rows in an unspecified order: the position is not resolved to the expression it names. A column number pointing at a plain column — SELECT a, b FROM t ORDER BY 2, 1 — is correct, and so is naming the expression itself, ORDER BY a+b*2, which is the way to write it until this is fixed. It is documented here rather than left to be discovered because a silently mis-ordered result is the kind of wrong answer a user has no way to notice.

NULLS FIRST and NULLS LAST are supported.

#Joins

INNER JOIN and LEFT JOIN are translated to $lookup with $unwind.

• The ON condition accepts one or more dotted parts on each side, so joining on a key inside a document (u.Ref.Id = o.Owner.Id) works.
• The whole ON condition must be consumed, or the query is refused. A join condition partly ignored is a silently wrong answer.
• GROUP BY over a join groups the joined rows, as SQL does. COUNT(*) therefore counts join pairs: a group with two users and three orders answers 3, not 2.
• An unqualified column belonging to a joined side is resolved there, but only when exactly one joined side carries that name. A name the base collection itself carries is never moved. A name two joined sides carry and the base does not is ambiguous in SQL, and the query is refused rather than answered with an empty column — SQLite refuses it too, so no answer is being withheld.
• That resolution applies everywhere the query names a column: the SELECT list, WHERE, ORDER BY, GROUP BY and the aggregates. SELECT title FROM inventory i JOIN film f ON i.film_id = f.film_id reads title from film.
• RIGHT JOIN, FULL OUTER JOIN and CROSS JOIN are not supported.

Resolution of unqualified joined columns depends on a sample of each collection's shape, so the offline planner — the one behind the browser converter, which has no database — does not do it.

#Aggregates and grouping

COUNT, SUM, AVG, MIN and MAX are supported, with COUNT(*) and COUNT(column) distinguished the way SQL distinguishes them, and COUNT(DISTINCT column) supported on its own, with WHERE, with GROUP BY and across a join.

SUM over nothing answers null, not 0. MongoDB's $sum returns zero for an empty set; SQL says the value is unknown. Those are different answers — zero says "the values added up to nothing", null says "there were no values" — so the engine carries a hidden witness through the group stage and converts a zero count back to null.

#A column the GROUP BY does not name

SELECT team, name, COUNT(*) FROM players GROUP BY team

name is not grouped and is not aggregated, so it has one value per row and the query asks for one value per group. PostgreSQL and SQL Server reject this. SQLite accepts it, and so does MySQL outside ONLY_FULL_GROUP_BY — both answer with the value from an arbitrary row of the group.

TableCore accepts it too, for the same reason: a great deal of working SQL is written this way, and refusing it would turn away queries that run fine where they came from. It is one of the few places TableCore knowingly parts company with its reference dialect, and it is here rather than in silence for exactly that reason. The result says so. A note above the grid names every column whose value was picked rather than computed, because a value chosen out of several is not a fact about the group and a tool that presents it as one is not worth trusting.

If the arbitrary pick is not what you meant, either add the column to GROUP BY or wrap it — MIN(name), MAX(name) — to say which row you want.

An aggregate with no GROUP BY at all beside a plain column (SELECT name, COUNT(*) FROM players) is still refused. There are no groups there to choose within, and it is a different question.

#Subqueries

Each refusal is refused because the answer would be different, not because the spelling is unfamiliar. Correlated NOT IN is the clearest case: SQL answers nothing for a row whose subquery yields null, while "no matching document" is NOT EXISTS — a different question with the same shape.

#Set operators

UNION, UNION ALL, INTERSECT and EXCEPT are supported. MongoDB has no operator that compares whole rows across two pipelines, so each branch runs separately and the engine merges the results by row key.

• ORDER BY after a set operator belongs to the merged result and is applied to it, before LIMIT/OFFSET. The comparator reproduces MongoDB's own $sort ordering, so the same column sorts the same way whether or not the query contains a set operator.
• ORDER BY an expression after a set operator is refused: the computed key is removed before rows are mapped, so it is not in the merged result. Alias the expression in the SELECT list instead.
• Branches are compared by column name; when the two sides name their columns differently, INTERSECT/EXCEPT is refused rather than answering silently empty or silently returning the left side.
• INTERSECT ALL and EXCEPT ALL are refused — multiset operations are a different answer, not a different spelling.
• INTERSECT/EXCEPT inside a subquery is refused; merging happens for the whole statement only.

#Common table expressions

WITH t AS (SELECT …) SELECT … FROM t works by folding the CTE into the statement before anything reads it: it becomes a derived table in FROM, which the engine already executes.

A name used twice is inlined twice and its query therefore runs twice — the same cost you would pay writing the subquery out by hand.

Refused: WITH RECURSIVE, a column list (WITH t(a, b) AS …, which renames the inner query's output), and a CTE that reads another CTE — the engine flattens one level of derived table, not two.

#Window functions

Translated to $setWindowFields, which requires MongoDB 5.0 or newer.

Supported: ROW_NUMBER, RANK, DENSE_RANK, and COUNT, SUM, AVG, MIN, MAX, each with optional PARTITION BY and ORDER BY.

The frame is written explicitly, because this is where the two dialects differ by default: SQL's window with an ORDER BY accumulates up to the current row — a running total — while MongoDB without an explicit window would sum the whole partition.

Refused: any function with no MongoDB equivalent (LAG, LEAD, NTILE, FIRST_VALUE and friends — named individually, not dismissed as a syntax error); an explicit frame (ROWS/RANGE/GROUPS BETWEEN …), because ignoring it would change which rows are read; a ranking function with no ORDER BY in its window; and a window together with GROUP BY or SELECT DISTINCT, since both replace the rows the window would walk.

#Expressions and functions

Arithmetic, comparison, AND/OR/NOT, LIKE with ESCAPE, ILIKE, IN, BETWEEN, IS [NOT] NULL, CASE WHEN … THEN … ELSE … END and CAST(… AS type) are supported in the SELECT list, in WHERE and in ORDER BY — they are read by one expression parser, so ROUND(Price * (1 + Rate), 2) costs nothing that ROUND(Price) does not. ORDER BY takes ASC and DESC.

The operators are = <> != < <= > >=, + - * / %, and || for concatenation. A single |, &, #, ~ and PostgreSQL's :: cast are refused by name rather than read as something else.

CAST accepts these type names, and a width on any of them changes nothing in a schemaless store:

INT / INTEGER / BIGINT / SMALLINT        → a whole number
REAL / FLOAT / DOUBLE / NUMERIC / DECIMAL → a real number
TEXT / VARCHAR / CHAR / NVARCHAR / STRING → text
BOOL / BOOLEAN                            → a boolean
DATE / DATETIME / TIMESTAMP               → a date

Four function spellings build a BSON value that SQL has no literal for, and they are written where a value goes in WHERE: OBJECTID('507f…'), DATETIME('2020-01-01'), TIMESTAMP('1700000000') and BINARY('…base64…'), plus JSON('…') for a value spelled the way MongoDB spells it.

Literal forms SQL has and TableCore does not read are refused by name rather than half-read: E'…', X'ff', B'1010', N'…', and the typed spellings DATE '2020-01-01', TIMESTAMP '…' and INTERVAL '1 day'. Write the value as ordinary text in single quotes, or as a number.

Functions the engine implements:

ABS      CEIL/CEILING   COALESCE   CONCAT    DATE      DATETIME
FLOOR    IF/IIF         JULIANDAY  LCASE     LEN       LENGTH
LOWER    LTRIM          NULLIF     ROUND     RTRIM     SQRT
STRFTIME SUBSTR         SUBSTRING  TIME      TRIM      UCASE      UPPER

CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP and SYSDATE are values rather than fields, and compile to the server's clock in UTC. CURRENT_TIME, LOCALTIME, CURRENT_USER and SESSION_USER are refused by name — BSON has no time-without-date type, and the server does not hand session identity to an expression. NOW(), with parentheses, is an unsupported function.

Any other function is refused as Unsupported function NAME, with its position.

#Nothing outside this page runs

The list above is not a description of the engine written beside it. It is the list: one table of constructs inside the engine, checked against this page by a test, and consulted before a query is translated. A keyword, function, operator or literal form that is not on it is refused, and the refusal names it.

This is worth stating because the alternative is not an error — it is a wrong answer. Until this gate existed, a construct nobody had thought about was not rejected, it was ignored, and the query ran as if the text were not there. Two real examples, both of which used to answer:

SELECT Name FROM ONLY people

ONLY is PostgreSQL's "do not read the inheritance children". TableCore read it as the collection and people as its alias, so the query asked a collection nobody has and came back successful with no rows.

SELECT Name FROM people LATERAL JOIN orders ON people.Id = orders.OwnerId

LATERAL was taken for the alias of people and the join ran anyway — a complete, confident answer to a query nobody wrote.

Both are refused now, by name. The rule that catches them is PostgreSQL's own: a word the reference dialect reserves cannot be a bare name here either, so ONLY, LATERAL, USER, TABLE, CHECK, END and the rest of that class have to be quoted to name a field — [end] or "end", exactly as the callout above says for FROM. Words PostgreSQL does not reserve are ordinary names and always were: value, year, name, type, level, count and several hundred others need no quoting.

The refusal says what is not supported rather than "syntax error", because the first tells you which four characters to delete and the second does not.

#Deliberate differences from SQL

These are pinned by tests, not accidents:

• Non-numeric text in arithmetic yields null, not 0. Zero is SQLite's type affinity rule, not SQL's, and it invents a number that is not in your data. Text that is a number ('20') still computes normally.

• Division is always real. 5 / 2 is 2.5, where PostgreSQL gives 2 for two integers. In a schemaless database the operand type is not known when the query is compiled, so choosing integer division would be a blind guess. A query that divides says so in its result notes; wrap the division in FLOOR or CAST to ask for the whole number.

• Division by zero yields null, per SQL, rather than aborting the query.

• A quoted number matches both forms in identifier fields. In _id, id, *_id and camelCase *Id, _id = '1' matches the string "1" and the number 1, because a collection holds whatever was written into it and SQL has no way to say which. The number is added beside the text, never instead of it, and only when the round trip is exact — '007', '1.0', '+1' and ' 1' stay text. This applies to =, <>, IN and NOT IN; ordered comparisons are deliberately excluded.

• A 24-character hex string matches both forms in identifier fields. In the same _id, id, *_id and *Id fields, external_id = '507f…' matches the text "507f…" and the ObjectId of that value, because 24 hex characters are equally how a git hash, an MD5 digest or an id copied out of another system is written. The ObjectId is added beside the text, never instead of it, and the text goes in exactly as it was typed. This applies to =, <>, IN and NOT IN; in ordered comparisons and BETWEEN, where only one value fits, the hex still means the ObjectId. For any other field, say so explicitly with OBJECTID('…').

• Every comparison is guarded. When either side is null or the field is missing, the answer is false. Without that, BSON's ordering — null below every number — would make Score * 2 < 20 true for every row with no Score.

• A column that holds more than one type is answered MongoDB's way, and the result says so. MongoDB compares within a type and orders types before values, so on a column holding both 10 and '10', > 5 answers about the numbers, the two never sort together, and 1, 1.0 and '1' fall into three groups where SQL's type affinity gives one. PostgreSQL cannot have such a column at all — there a column has one type — so there is no SQL answer to follow here, which is why this is a note rather than a defect. TableCore reads 25 documents of the collection to spell your field names, and reports what those documents disagreed about; a query that only carries such a column through the SELECT list gets no note, because nothing about its types decided anything. The absence of the note is not a promise: 25 documents cannot speak for a collection. CAST the column to read every row the same way.

• NOT (a > b) is not a <= b on a column that mixes types. MongoDB compares within a type, so if Size holds both 40 and 'large', neither Size > 'm' nor Size <= 'm' matches the number. NOT therefore means "has a value, and this comparison does not hold" rather than the opposite comparison, which puts the number in NOT (Size > 'm') — where SQL says it belongs, since every row with a value is in the comparison or its negation. NOT BETWEEN works the same way. On a column of one type the two readings agree, and the ordinary comparison is unchanged.

• Nested fields are paths, not table.column. Profile.Address.Zip is a path unless Profile is a declared qualifier: an alias from FROM, the base collection's name, or the alias or name of a joined collection.

• Double quotes make an identifier, and a comparison against one that names nothing is refused. "Name" is the column Name, exactly as SQL and PostgreSQL say — a text value is written in single quotes. So WHERE rating = "PG" compares two columns, and if no document has a field called PG the query is turned away naming it, rather than quietly matching no rows. This is the shape MySQL-dialect SQL runs into most often; if you meant the text, write WHERE rating = 'PG'.

  A field that simply is not there is normal in MongoDB and still answers: WHERE optional IS NULL, COUNT(optional) and COALESCE(optional, 0) all run. Only a comparison between a name that resolves and a name that does not is refused, because that is the only case where the second name cannot have been meant as a column.

#Which shapes are not one command

Some SELECTs never become a single round trip: subqueries, set operators, and a global aggregate that the engine finishes in memory.

For those, Explain and Query as code refuse by name — naming the reason and listing the steps the engine will run, and saying for each whether it happens on the server or in TableCore — rather than describing one of the commands as if it were the whole query. A global aggregate is not a refusal: the command is real, it is just not the last thing that happens, so you get the plan and the disclosure.

JOIN, GROUP BY, DISTINCT and computed columns are one command and are never described as multi-command work.

#Measured agreement with SQL

The engine is evaluated against public corpora nobody here wrote. No competitor publishes anything comparable, and there is a reason to: a user who finds a wrong answer having been warned files a bug, and one who was promised exactness writes a review instead.

Most of that evaluation asks the same question of SQLite and of MongoDB and compares the rows. One corpus does not, and it is the newest: sqllogictest declares the answer it expects inside the test file, so the engine is graded against a written-down answer rather than against one implementation's idea of what the question means.

Undecided counts queries this run could not decide either way: ties in the ordering leave more than one correct answer, and on BIRD Mini-Dev a query can exceed the 30-second budget. A row need not add up to the number of questions — an outcome with no published column is left out rather than folded into one of the others.

• Spider — 1,034 questions over twenty unrelated databases (concerts, aircraft, dog breeding, world demographics, tennis), written by people who had never seen this engine. It measures breadth of schemas.
• BIRD Mini-Dev — 348 questions over databases taken from working systems: uncleaned nulls, codes whose meaning lives in a separate file, dates kept as text, eighty-column tables, column names with spaces. It measures what real data does to a query. It is simply harder, and about something else.
• sqllogictest — 8,884 queries from the SQLite project's own test suite, each carrying the result it expects. It is the only one here that does not grade the engine against another engine, and the only one that checks the order of an answer rather than just its rows. The score is the lowest of the three because two of its five scripts are built almost entirely from shapes the engine refuses by name — four-way comma joins, and chains of EXCEPT, INTERSECT and UNION — which is what makes it worth running.

A fourth corpus, Sakila — 282 queries over a sixteen-table schema — is a pass/fail gate rather than a score: every one of those must agree.

These numbers are written into the test suite as ratchets. The count that agrees may not fall and the count that differs may not rise; the floor is raised when the engine earns it, and never lowered to make a run go green.

#What the numbers do not say

• "Differs" is not always the engine being wrong. Spider declares columns as text and stores numbers in them, so WHERE Year = 2014 matches in SQLite, which converts the literal, and does not match in MongoDB, which compares "2014" with 2014. That is a property of the data, and the honest fix is not to lie about types.
• A query with ties in its ordering has more than one correct answer. Those are counted separately and never as agreement.
• A low score can mean the corpus asks for shapes the engine refuses, not that its answers are unreliable. Two of sqllogictest's five scripts are built almost entirely from four-way comma joins and long EXCEPT/INTERSECT/UNION chains, and the engine declines all 3,564 of them by name. Refusing is the designed behaviour: the alternative is an approximate answer presented as an exact one.
• BIRD's numbers move a little between runs, because a few queries sit near the 30-second per-query budget and land in timed out or in differs depending on what else the machine is doing. The ratchets are set outside that band on purpose; pinning an exact figure would make the suite flake and teach everyone to re-run it.
• The corpora are not in the repository and the suites are opt-in: they need a MongoDB and a downloaded data set. The per-query lists are written to artifacts/spider-report.txt, artifacts/bird-minidev-report.txt and artifacts/sqllogictest-report.txt by the run.

#Next

• SQL to MongoDB — how each shape is translated.
• Explain plans — what the server says about the command.
• The browser converter — the same engine, compiled to WebAssembly, with no installation and no database.