Lang Sql (1 of 2)
10 specifications, 143 requirements. All libraries
0001 SQL language core, lexical layer
| Requirement | Mechanism | Who | Note |
|---|---|---|---|
FR-001 | check | bun run verify | suite/layers.test.ts: every file of src imports only files of src by relative paths and @intellectual-frontiers/core by name, and no node: module |
FR-002 | check | bun run verify | suite/layers.test.ts: the core imports neither dialects nor extensions and names no dialect but the base; a dialect imports the core and its parent dialects; an extension imports the core alone |
FR-003 | check | bun run verify | suite/layers.test.ts: the manifest exports the core, every dialect folder and every extension folder, and no other library of commons imports a file of this one by a relative path |
FR-004 | check | bun run verify | suite/lexer.test.ts: for any text and any dialect the lexer does not throw and ends, and every character is in one token, one comment or white space, in order, so the text can be put back |
FR-005 | check | bun run verify | suite/lexer.test.ts: tokens are columns of a closed set of kinds that end with the end of the text, a keyword is found without regard to case, and a word with a character outside ASCII is never a keyword |
FR-006 | check | bun run verify | suite/lexer.test.ts: what the dialect cannot read is an error value with its span and the lexer goes on: an unclosed comment, string or quoted identifier ends at the end of the text, and an unreadable character is a token of the error kind |
FR-007 | check | bun run verify | suite/lexer.test.ts: strings and quoted identifiers are read as their dialect writes them: doubled quotes, backslashes, prefixes, long quotes and dollar quotes |
FR-008 | check | bun run verify | suite/lexer.test.ts: numbers have fractions, exponents, an initial point, radix prefixes and underscores where the dialect says; parameters come in the styles of the dialect; an operator is the longest at its place |
FR-009 | check | bun run verify | suite/comments.test.ts: every comment of every kind the dialect has is kept, with its kind, marker, span and text, and nesting follows the dialect |
FR-010 | check | bun run verify | suite/comments.test.ts: a block comment that begins /*+ is a hint where the dialect has hints, one that begins /*! is a conditional comment where it has those, and elsewhere both are plain block comments |
FR-011 | check | bun run verify | suite/comments.test.ts: a line comment whose marker needs a space after it is a comment only before white space, a control character or the end of the text |
FR-012 | check | bun run verify | suite/comments.test.ts: a comment's place is given: how many tokens precede it, whether it is on a line of its own, whether a blank line follows, and whether it is closed |
FR-013 | check | bun run verify | suite/comments.test.ts: what looks like a comment inside a string, a quoted identifier or another comment is not one |
FR-014 | check | bun run verify | suite/comments.test.ts: a position is found from an offset: lines end at \n, \r\n or a lone \r, line and column count from 1 in UTF-16 units, and an offset outside the text is placed at its nearest end |
FR-015 | check | bun run verify | suite/dialect.test.ts: a lexical compiles once into tables, and one that cannot be compiled is an error value that names the field and never a thrown error |
FR-016 | check | bun run verify | suite/dialect.test.ts: a dialect is made from a name, its parent and a function from the parent's lexical, and can add, remove and replace what it inherits; a chain is resolved once |
FR-017 | check | bun run verify | suite/dialect.test.ts: the base is ansi in the core, and SQLite, PostgreSQL and DuckDB each extend it with what their engines document |
FR-018 | check | bun run verify | suite/speed.test.ts: the lexer has no regular expression, a token is no object, and it lexes the corpus at a floor of ten megabytes of text a second |
0002 The syntax tree and the parser of the SQL language core
| Requirement | Mechanism | Who | Note |
|---|---|---|---|
FR-001 | check | bun run verify | suite/tree.test.ts: a tree is a closed union told apart by kind, a child field has the type of its sort so that a wrong sort or a missing case does not compile, and no node holds its parent or the text |
FR-002 | check | bun run verify | suite/tree.test.ts: every node has a range, the ranges of children are inside their parent's, and a parenthesis at the edge of a node is inside it |
FR-003 | check | bun run verify | suite/tree.test.ts: a name is an identifier with its quotes taken off or a qualified name, and a star keeps its qualifier |
FR-004 | check | bun run verify | suite/tree.test.ts: a number keeps its text, a string its value as the dialect reads it and its prefix, and booleans, null and parameters are nodes of their own |
FR-005 | check | bun run verify | suite/tree.test.ts: each kind of expression is a node of its own, with the fields the specification lists |
FR-006 | check | bun run verify | suite/tree.test.ts: queries, sources, data changes and definitions have the nodes and fields the specification lists |
FR-007 | check | bun run verify | suite/tree.test.ts: a script is a node of its statements and a statement the parser could not read is an unparsed node of its range |
FR-008 | check | bun run verify | suite/parser.test.ts: parse accepts any text in any dialect, does not throw and ends, and parseStrict gives ok where there is no error and the errors where there is any |
FR-009 | check | bun run verify | suite/parser.test.ts: expressions have the precedence of SQL and associate to the left |
FR-010 | check | bun run verify | suite/parser.test.ts: set operations bind INTERSECT tighter and to the left, trailing clauses belong to the whole set operation, and a query may stand in parentheses wherever a query goes |
FR-011 | check | bun run verify | suite/parser.test.ts: a word is an identifier where the dialect does not reserve it, a reserved word only after a dot or quoted, and an alias is read with or without AS |
FR-012 | check | bun run verify | suite/parser.test.ts: what cannot be read is an error value with a span and a sentence, the next statement is read, the lexer's errors are among the errors, and deep nesting is an error and not a stack overflow |
FR-013 | check | bun run verify | suite/parser.test.ts: a dialect's grammar adds its operators, cast and like-words, is checked against its lexical, and is inherited |
FR-014 | check | bun run verify | suite/attach.test.ts: each comment is attached by one rule: leading to the child after it on a line of its own, detached where a blank line follows, trailing to the child before it where it shares the line, and dangling where nothing is near |
FR-015 | check | bun run verify | suite/attach.test.ts: every comment of a text is in the table once, in order, for each node its own by placement, whether or not the statement around it was read |
FR-016 | check | bun run verify | suite/walk.test.ts: children gives every child of every kind in the order of the text, walk visits depth first and can skip, findAll is typed by its kind, and fold combines from the leaves |
FR-017 | check | bun run verify | suite/walk.test.ts: toSexp writes the same text for the same tree, and each golden file is what the parser makes of its SQL |
FR-018 | check | bun run verify | suite/speed.test.ts: the parser reads tokens by index with no regular expression, and parses the corpus, comments attached, at a floor of three megabytes of text a second |
FR-019 | check | bun run verify | suite/print.test.ts: FETCH FIRST with PERCENT or WITH TIES is read into the limit and its options, written with the offset first, read back as the same tree, and a FETCH FIRST n ROWS ONLY is still a LIMIT |
FR-020 | check | bun run verify | suite/print.test.ts: TABLESAMPLE after a table with no alias is read as what stands beside the table, not as its alias, and is written back |
0003 Rewriting, queries and unist for the SQL syntax tree
| Requirement | Mechanism | Who | Note |
|---|---|---|---|
FR-001 | check | bun run verify | suite/rewrite.test.ts: rules by sort are called from the leaves up for each node in a place of that sort, return a node of the same sort or nothing, and the result has the type of what was given |
FR-002 | check | bun run verify | suite/rewrite.test.ts: a rewrite changes nothing it is given, returns the same object for what no rule changed and for the whole tree where nothing changed, and makes new objects only for a changed node and those above it |
FR-003 | check | bun run verify | suite/rewrite.test.ts: a rewrite keeps the range of every node it does not replace, gives a replacement the range its rule gave it, and the builders take the range of what they stand for |
FR-004 | check | bun run verify | suite/rewrite.test.ts: every kind of node is handled, a rule for identifiers is called for every identifier wherever it stands, and no tree the parser makes throws |
FR-005 | check | bun run verify | suite/query.test.ts: the table of parents gives each node's parent once, the root has none, and ancestors come nearest first |
FR-006 | check | bun run verify | suite/query.test.ts: a selection is lazy and can be read again, its methods are typed by the kind asked for, and its nodes come in the order of the text |
FR-007 | check | bun run verify | suite/query.test.ts: each use of a table is given by name and access, in the order of the text, and a name of a common table expression in scope is not a table |
FR-008 | check | bun run verify | suite/query.test.ts: every call is given with its name and node, and a script that is not one query says why it is not read-only |
FR-009 | check | bun run verify | suite/unist.test.ts: each node has its kind as its type, children or a value, and a position of unist points |
FR-010 | check | bun run verify | suite/unist.test.ts: data holds the fields that are not nodes, and a child says which field of its parent it is and its place in a list |
FR-011 | check | bun run verify | suite/unist.test.ts: a comment is a node among the children of the node that encloses it, in the order of the text, with its data, and an unparsed statement keeps its comments and its source |
FR-012 | check | bun run verify | suite/unist.test.ts: the projection changes nothing, is the same for the same input, and is a tree in the unist sense: objects with a string type, parents with arrays of children, no node twice |
FR-013 | check | bun run verify | suite/unist.test.ts: unist-util-visit visits the projection by type and in order, unist-util-select selects with type, child, descendant and attribute selectors, and the nodes are assignable to the types of @types/unist |
0004 Documentation extracted from SQL comments
| Requirement | Mechanism | Who | Note |
|---|---|---|---|
FR-001 | check | bun run verify | suite/docs.test.ts: the text of a comment has its markers taken off, whatever kind of comment the dialect has, and consecutive comments are joined by a new line |
FR-002 | check | bun run verify | suite/docs.test.ts: a declaration is documented by its leading comments, its trailing comments and those that trail its name, and by no detached, dangling or other comment |
FR-003 | check | bun run verify | suite/docs.test.ts: documentation has a summary, a description and tags, read as TSDoc reads them |
FR-004 | check | bun run verify | suite/docs.test.ts: param, returns, example, deprecated, since and see are typed, every other tag is a tag with its name and text, and a project's reader is used in the place of the generic one |
FR-005 | check | bun run verify | suite/docs.test.ts: the code of an example is the text of the tag without its fence, parsed with the dialect of the script, with its tree and errors |
FR-006 | check | bun run verify | suite/docs.test.ts: a script gives, in order, its tables with columns and named constraints, views, indexes, common table expressions, added columns and other statements, each with its kind, name, node and documentation |
FR-007 | check | bun run verify | suite/docs.test.ts: declarations are written as Markdown, one section for each that has documentation or members that have it, with the same text for the same declarations |
FR-008 | check | bun run verify | suite/docs.test.ts: extracting documentation changes nothing it reads and gives the same result for the same input |
0005 The dialects of the SQL language core, and their conformance
| Requirement | Mechanism | Who | Note |
|---|---|---|---|
FR-001 | check | bun run verify | suite/dialects.test.ts: the thirty dialects are modules of their own that are exports of the package, in the families of the specification, and what is not SQL is not among them |
FR-002 | check | bun run verify | suite/dialects.test.ts: each dialect's lexical says its comments, identifiers, strings, prefixes, dollar quotes, parameters and numbers as its engine does |
FR-003 | check | bun run verify | suite/dialects.test.ts: the lexer reads number suffixes, Oracle's alternative quoting and words that begin with a character a dialect names, and a lexical that gets them wrong is an error value |
FR-004 | check | bun run verify | suite/dialects.test.ts: each dialect's grammar names its cast operator, like-words and operators as its engine documents them, and a dialect with nothing to add keeps its parent's |
FR-005 | check | bun run verify | suite/dialects.test.ts: DAX, PRQL and Tableau are not dialects of this library, and the reason is written where the dialects are described |
FR-006 | check | bun run verify | suite/conformance.test.ts: every corpus file has a row of provenance with its repository, commit, license and statements, the license is permissive and its text is kept, and what is included is in the register of third-party files |
FR-007 | check | bun run verify | suite/conformance.test.ts: for every statement of every corpus in its dialect the lexer and the parser do not throw, every character is in a token, a comment or white space, ranges nest, comments are attached once, the projection and a rewrite with no rules hold, and a second run gives the same |
FR-008 | check | bun run verify | suite/conformance.test.ts: the number of statements each file reads with no error does not fall below the baseline, the lexer's errors do not rise above it, and every file has a row |
FR-009 | check | bun run verify | suite/conformance.test.ts: every dialect is read in a corpus of its own, or is listed as an exception in the provenance |
FR-010 | check | bun run verify | suite/grammar-growth.test.ts: a statement that begins with a command word, and a CREATE, ALTER or DROP of an object the grammar does not read, is a command node with its keyword, object and text, and never an error |
FR-011 | check | bun run verify | suite/grammar-growth.test.ts: CREATE TABLE is read as a table made from a query, with no column list, and with the text after the columns or the name that the parser does not read kept as options |
FR-012 | check | bun run verify | suite/grammar-growth.test.ts: arrays are read as [a, b] and ARRAY[a, b], a subscript as x[i] that binds as tightly as ::, and [ is a quote where the dialect says so |
FR-013 | check | bun run verify | suite/grammar-growth.test.ts: VALUES is read as a source, with an alias and a column list |
FR-014 | check | bun run verify | suite/grammar-growth.test.ts: a dialect's grammar may name word operators with binding powers, which become operator nodes, and one whose word is not a keyword of its lexical is an error value |
FR-015 | check | bun run verify | suite/grammar-growth.test.ts: COLLATE is read after an expression as a node and after a data type as its collation |
FR-016 | check | bun run verify | suite/grammar-growth.test.ts: a select is read with DISTINCT ON, GROUP BY ALL, the empty grouping and GROUPING SETS, a WINDOW clause and QUALIFY, and a frame with its EXCLUDE; QUALIFY stays a name where nothing but the end of an item follows it |
FR-017 | check | bun run verify | suite/grammar-growth.test.ts: a call is read with an ORDER BY among its arguments, WITHIN GROUP, and a query as an argument |
FR-018 | check | bun run verify | suite/grammar-growth.test.ts: TRY_CAST, SAFE_CAST and CAST ... FORMAT are casts, and a parenthesized join keeps its alias and may begin with a query |
FR-019 | check | bun run verify | suite/grammar-growth.test.ts: expressions are read with fields of rows, slices, typed literals, interval ranges, keyword arguments, null treatment, SIMILAR TO, NOT NULL, IS, IN with no list or a table, DEFAULT and star modifiers |
FR-020 | check | bun run verify | suite/grammar-growth.test.ts: a dialect with operator runs reads the operators it does not list, before and between operands, and a run does not end in a sign |
FR-021 | check | bun run verify | suite/grammar-growth.test.ts: data changes are read with conflict clauses, aliases, paths for columns, USING, and data changes in WITH |
FR-022 | check | bun run verify | suite/grammar-growth.test.ts: definitions are read with no columns, LIKE, column options, constraints with options, index and view options, and alter actions with options |
FR-023 | check | bun run verify | suite/grammar-growth.test.ts: queries are read with LIMIT offset and count, table hints, WITH ORDINALITY, semi and anti joins, joins that nest on the right, a USING alias, and a select with no items |
FR-024 | check | bun run verify | suite/grammar-growth.test.ts: a dialect's grammar switches on a query that begins with FROM, lambdas, paths, indexes in CREATE TABLE, the tail of a query, parametric calls and TOP, and a dialect that does not switch them on reads as before |
FR-025 | check | bun run verify | suite/grammar-growth.test.ts: struct literals, types with fields, ORDER BY ALL, BY NAME, upserts of MySQL and Hive, APPLY, table operators and hints, and the arguments of calls are read |
FR-026 | check | bun run verify | suite/conformance.test.ts: no StarRocks corpus file holds the template variable `${...}` of its test runner, which is not SQL |
0006 Stored routines and procedural code
| Requirement | Mechanism | Who | Note |
|---|---|---|---|
FR-001 | check | bun run verify | suite/routines.test.ts: a procedural statement ends at the first ; outside every block it opens, and every other statement at its first ; |
FR-002 | check | bun run verify | suite/routines.test.ts: BEGIN begins a transaction where it is followed by ;, the end, or a word of a transaction, and a block elsewhere |
FR-003 | check | bun run verify | suite/routines.test.ts: CREATE FUNCTION and CREATE PROCEDURE are a create-routine node with the object, replace, name, parameters, result, language and characteristics, and a header that is not read is a command |
FR-004 | check | bun run verify | suite/routines.test.ts: a routine's body is a node with its style and text, and its offsets in the whole text where the text can be read in place |
FR-005 | check | bun run verify | suite/routines.test.ts: PL/pgSQL is read as a block of declarations, statements and handlers, with every statement the specification lists |
FR-006 | check | bun run verify | suite/routines.test.ts: the SQL a body holds is read with the core parser at its place in the whole text, and the INTO of a statement is taken out of it and kept |
FR-007 | check | bun run verify | suite/routines.test.ts: the comments of a body are kept and attached to its nodes by the rule of the core |
FR-008 | check | bun run verify | suite/routines.test.ts: what cannot be read is an error value with its span in the whole text, the statement is kept as unparsed or with a null in the place of the expression, and the statements after it are read |
FR-009 | check | bun run verify | suite/routines.test.ts: every routine of the corpus of PL/pgSQL from PostgreSQL's tests is read without a throw, and the number whose body is read with no error does not fall below the baseline |
0007 Quoting, placeholders and a registry of dialects
| Requirement | Mechanism | Who | Note |
|---|---|---|---|
FR-001 | check | bun run verify | suite/emit.test.ts: an identifier is quoted as the dialect's first syntax writes it, bare only where it is a plain lower-case word that is not a keyword, and a dialect with no quote gives an error value |
FR-002 | check | bun run verify | suite/emit.test.ts: a string is written as the dialect's first string syntax writes it, and a quote that cannot be written inside is an error value |
FR-003 | check | bun run verify | suite/emit.test.ts: what quoteIdent and quoteString give is read back as exactly one token of the same dialect's lexer, in every dialect |
FR-004 | check | bun run verify | suite/emit.test.ts: the styles of a dialect are listed, a name takes the colon, at or dollar style, a position takes $n or ?, and a dialect without a fitting style gives an error value |
FR-005 | check | bun run verify | suite/emit.test.ts: what placeholder gives is read back as exactly one parameter token of the same dialect's lexer, in every dialect that has a style that fits |
FR-006 | check | bun run verify | suite/registry.test.ts: a registry holds ansi, adds a dialect found by name without regard to case, refuses a name taken or a parent that is not registered, and takes the same dialect twice |
FR-007 | check | bun run verify | suite/registry.test.ts: functions are registered per dialect, found without regard to case in the dialect and then in those it extends, and a function that cannot be is an error value that changes nothing |
FR-008 | check | bun run verify | suite/registry.test.ts: no function of the registry or of quoting throws for an unusual value |
0008 Introspection, SQL over the syntax tree
| Requirement | Mechanism | Who | Note |
|---|---|---|---|
FR-001 | check | bun run verify | suite/introspect.test.ts: introspect gives an introspection for any text without throwing, and a query gives rows or an error value in one sentence |
FR-002 | check | bun run verify | suite/introspect.test.ts: the relations give the statements, the nodes in the order of the text with their fields, attributes, ancestors, tokens, comments, names, calls, parameters, common table expressions and diagnostics |
FR-003 | check | bun run verify | suite/introspect.test.ts: the keywords and operators of the dialect and the functions of the registry are relations, and none where none is given |
FR-004 | check | bun run verify | suite/introspect.test.ts: a relation is built only when a query names it, and tables() says every relation and its columns |
FR-005 | check | bun run verify | suite/introspect.test.ts: the query language has the clauses, operators, functions, subqueries, parameters and the logic of SQL that the specification lists |
FR-006 | check | bun run verify | suite/introspect.test.ts: what astsql does not have is an error value that names it |
FR-007 | check | bun run verify | suite/introspect.test.ts: the diagnostics are the errors of the parse and the rows of the rules, and a rule is a query that a consumer can add |
FR-008 | check | bun run verify | suite/introspect.test.ts: the fingerprint is the same for statements that differ in their literals and layout and different for another shape, and only a query is read only |
0009 The printer and unit literals
| Requirement | Mechanism | Who | Note |
|---|---|---|---|
FR-001 | check | bun run verify | suite/print.test.ts: any node is written as text of the dialect: precedence is kept by parentheses where it asks for them, names and strings are quoted as the dialect does, and a script ends each statement with a semicolon; suite/print.test.ts: TABLESAMPLE after a table with no alias is read as what stands beside the table, not as its alias, and is written back |
FR-002 | check | bun run verify | suite/print.test.ts: what the printer writes is read back as the same tree for the corpora of every dialect, the statements that are not the same are counted, and the count only shrinks |
FR-003 | check | bun run verify | suite/print.test.ts: the pretty form puts each clause on a line of its own, indents nested queries, and is read back as the same tree |
FR-004 | check | bun run verify | suite/print.test.ts: a tree is written for another dialect without throwing, as the printer's dialect has it |
FR-005 | check | bun run verify | suite/print.test.ts: a number followed by a unit is one number scaled by the unit, and a dialect without units reads the text as before |
FR-006 | check | bun run verify | suite/print.test.ts: FETCH FIRST with PERCENT or WITH TIES is read into the limit and its options, written with the offset first, read back as the same tree, and a FETCH FIRST n ROWS ONLY is still a LIMIT |
0010 Aide, the helpers of the SQL language
| Requirement | Mechanism | Who | Note |
|---|---|---|---|
FR-001 | check | bun run verify | suite/aide-layers.test.ts: src/aide imports only src/aide, the core and commons' core and no node: module, the core never imports aide or acceptance, and the manifest exports aide and not acceptance |
FR-002 | check | bun run verify | suite/aide.test.ts: a profile restricts statements, nodes, functions, tables and size, and every violation is an error value with its span |
FR-003 | check | bun run verify | suite/aide.test.ts: names are resolved against a catalog with aliases and subquery scopes, and unknown and ambiguous names are error values with a suggestion |
FR-004 | check | bun run verify | suite/aide.test.ts: an expression has a type, and a use that cannot be right is an error value |
FR-005 | check | bun run verify | suite/aide.test.ts: simplify folds constants, pushes NOT down, expands BETWEEN and a short IN, and flattens AND and OR |
FR-006 | check | bun run verify | suite/aide.test.ts: a query over one table is a plan of scan, filter, sort, project and limit that explain writes, and what is not planned is an error value |
FR-007 | check | bun run verify | suite/aide.test.ts: a plan is divided between a backend and the process, and the residual run on the pushed rows gives the rows of the whole plan |
FR-008 | check | bun run verify | suite/aide.test.ts: an expression compiles to a closure with the logic of SQL and the like-words, an unknown function is an error at compile time, and a plan runs over rows |
FR-009 | check | bun run verify | suite/aide.test.ts: transpile writes a text in another dialect and reports what it does not read, the template never puts a value into the text, and a diagnostic is rendered with its line and a caret |
FR-010 | check | bun run verify | suite/relational.test.ts: the engine answers a query over the relations of a provider: joins, grouping, a subquery and a common table expression, with the logic of SQL, and a name is found without regard to case; suite/relational.test.ts: what is not one query is an error value: a write, two statements, a table that is not there and a clause the engine does not have |
FR-011 | check | bun run verify | suite/highlight.test.ts: each token is what the lexer says: keywords, functions, names, quoted names, numbers, strings, parameters, operators, punctuation and comments, and a word in a string or a comment is none of the first; suite/highlight.test.ts: the dialect decides: a dollar quote is one string in PostgreSQL, a backtick name is quoted in MySQL, and a word that is a keyword in one dialect is a name in another; suite/highlight.test.ts: the stretches are in order and never overlap, the white space is left out, and no text makes it throw (a character the lexer cannot read is an error) |
FR-012 | check | bun run verify | suite/relational.test.ts: UNION, INTERSECT and EXCEPT give each row once and with ALL by their counts, a null equals a null, INTERSECT binds tighter, and the ORDER BY, LIMIT and OFFSET of the whole are applied to the answer |
FR-013 | check | bun run verify | suite/relational.test.ts: the ranking functions number each partition by its ORDER BY, with peers sharing a rank, and ntile, percent_rank and cume_dist follow the standard; suite/relational.test.ts: lag and lead read the row before and after in the partition (with an offset and a default), and first_value, last_value and nth_value read the frame; suite/relational.test.ts: aggregates run over the frame: the whole partition without an ORDER BY, the start to the last peer with one, a ROWS frame by its bounds, a RANGE frame by the current row's peers, with a named window and a FILTER; suite/relational.test.ts: a window function runs over the groups of a query that groups, may stand in the ORDER BY, and is refused in the WHERE, the HAVING and the GROUP BY, as are GROUPS and EXCLUDE frames, a RANGE with a distance, IGNORE NULLS and a name that is no window function |
FR-014 | check | bun run verify | suite/relational.test.ts: RIGHT and FULL outer joins keep the rows of a side that has no partner, with nulls for the other side, and an unmatched null key matches nothing; suite/relational.test.ts: USING and NATURAL joins compare the shared columns, show each once and first in *, read the side that has the row for an unqualified name in a RIGHT or FULL join, and keep a qualified name on its own side |
FR-015 | check | bun run verify | suite/relational.test.ts: NULLS FIRST and NULLS LAST order the nulls where they say (and as before where they do not), and OFFSET n ROWS and FETCH FIRST n ROWS ONLY page the answer |
FR-016 | check | bun run verify | suite/relational.test.ts: a recursive common table expression runs its anchor once and its step on the last round's rows until a round makes none, UNION stops where a round adds nothing new (a cycle), and one that does not end is an error value |
FR-017 | check | bun run verify | suite/relational.test.ts: the current date and time are the moment of the clock given, once for a statement, and dates, timestamps and intervals compare as the moments do |
FR-018 | check | bun run verify | suite/relational.test.ts: date and time literals, casts and arithmetic follow the calendar: months clamp to the end of a month, a date plus an interval with a time is a timestamp, differences are days or intervals, and what is not a date is an error value; suite/relational.test.ts: EXTRACT reads the parts of a date, a timestamp, a time and an interval, and a null gives a null |
FR-019 | check | bun run verify | suite/relational.test.ts: ROLLUP, CUBE and GROUPING SETS group each set on its own, with nulls for the columns a set leaves out, the empty set giving a row over no rows, GROUPING telling the rows apart, and plain expressions put with them |
FR-020 | check | bun run verify | suite/relational.test.ts: ANY, SOME and ALL compare with a subquery or an array by the logic of SQL, a null among the values giving null where nothing decides, and none giving false for ANY and true for ALL |
FR-021 | check | bun run verify | suite/relational.test.ts: the string functions follow the standard: SUBSTRING counts a part before the start in its length, POSITION and TRIM take their keywords, OVERLAY places, nulls give nulls (but for concat), and what is not a whole number is an error value |
FR-022 | check | bun run verify | suite/relational.test.ts: LISTAGG, STRING_AGG and GROUP_CONCAT join the values in the order given, with DISTINCT and a FILTER, a separator is required where the standard has none, and an ORDER BY on any other aggregate is an error value; suite/relational.test.ts: IGNORE NULLS counts only the rows whose value is not null for lag, lead, first_value, last_value and nth_value, RESPECT NULLS is as none, and any other function refuses it |
FR-023 | check | bun run verify | suite/relational.test.ts: FETCH FIRST n PERCENT takes the ceiling of the percent of the rows after the offset, WITH TIES takes the rows that equal the last on the keys of the ORDER BY, both work in a set operation, and what makes no sense is an error value |
FR-024 | check | bun run verify | suite/relational.test.ts: SIMILAR TO matches the whole text by the pattern of the standard: wildcards, alternation, repetition, classes (named too), an escape that is only where it is given, a dot that is a dot, and null for null |
FR-025 | check | bun run verify | suite/relational.test.ts: TABLESAMPLE keeps the rows a seeded generator lets through: all at 100 percent, none at 0, the same rows for the same seed, others for another seed, about the percent, with an alias or without, and a method or size that is none is an error value |
FR-026 | check | bun run verify | suite/relational.test.ts: a LATERAL subquery is run for each row before it and may name its columns: cross and inner joins keep the rows it gives, a left join keeps the row once with nulls, a comma in the FROM works the same, and a right or full join is an error value |
FR-027 | check | bun run verify | suite/relational.test.ts: round goes half away from zero on the decimal text (and to negative places), floor and ceil go to whole numbers, date_trunc cuts a date or a timestamp to the start of a part, a null gives a null, and what is not a number, a date or a field is an error value |