The build contains changes incompatible with prior builds. Specifically:
Queries created in this build might not run on previous build and vice versa. (Queries created on previous builds will work in this build as long as they don't use function or query parameters.)
MAP files created by this build will not load on previous builds.
This build contains a number of changes to the query engine which help both current transforms and transforms that we are working to add right now or are planning to add in the future. Next builds are putting these changes to good use.
Background (skip if you just want to know what the changes are):
The sequence of changes started with us lacking syntax to control execution with multiple threads: we needed a way to tell the query engine that even though kriging should use multiple threads, building the interpolation model should happen only once, there was no way to express this. This was not the first time we had such a need, so we already had a couple of options which we were talking about before. After some time we settled on the option involving global variables: we'd first write a statement to build the interpolation model and put it into a global variable, then in the next statement we'd reference that global variable. With two statements opposite to two query constructs within a single statement, we would have means to control the number of threads separately for each. Plus we could use global variables for other things. So we settled on that.
Adding global variables, however, had a drawback in that each global variable added a name which could have clashed with names of fields and tables. We'd obviously resolve the clashes in that whenever we'd have a clash we'd let either a global variable or a field / table name win, however, these clashes were introducing big potential for errors. For example, if someone defined a global variable named 'pop' and then forgot about it and went to write SELECT ... pop ... from a table that should have 'pop' but does not (because, say, it contains 'population', 'pop' is a misspelling), then the query would compile and run, just return wrong results. There were many other scenarios in which this was occurring, plus we long wanted to fix several issues we had in naming anyway, so we decided to rework the way we refer to certain things and require some references to use @ before the name.
Now, we had computed fields to worry about. Expressions in computed fields do refer to other fields and since we absolutely did not want existing computed fields to suddenly stop working, we had to do something to make them work. So, we had one more set of variants. And then one more thing, and then another one, etc.
This is how it all proceeded until eventually we covered everything. :-)
End of background.
Query parameters use @ as a prefix.
Old: EXECUTE WITH (p INT32 = 1) [[ SELECT * FROM mfd_root WHERE mfd_id=p; ]]
New: EXECUTE WITH (@p INT32 = 1) [[ SELECT * FROM mfd_root WHERE mfd_id=@p; ]]
EXECUTE requires either query text in [[ ... ]] or the name of an existing query, forms that allow using EXECUTE without leaving the scope of the current query are removed.
(There are two reasons for the removal. First, forms of EXECUTE which run things like SELECT without leaving existing query were redundant: just use SELECT without EXECUTE. A notable exception was EXECUTE CALL ..., which is not exactly the same as SELECT * FROM CALL ..., but we added a replacement specifically for that, read below. Second and more important, SELECT and similar statements ran inside EXECUTE without [[ ... ]] boundaries could address functions and other objects defined in the containing query, this was just too different from the forms of EXECUTE which we actually did EXECUTE for - when you do EXECUTE [queryname], the referenced query cannot access functions from the outer query, and this is entirely by design and desirable - so we removed the extra options.)
Function parameters use @ as a prefix.
Old: FUNCTION f(p INT32) INT32 AS p+1 END; VALUES (f(10)), (f(11));
New: FUNCTION f(@p INT32) INT32 AS @p+1 END; VALUES (f(10)), (f(11));
All references to parameters inside a function body are resolved to local parameters. If a function wants to use a query parameter passed via EXECUTE, that parameter has to be transferred explicitly, under the same or different name.
Field values passed to computed fields and constraints continue to not use @ as a prefix. This allows existing computed fields and constraints to work without changes.
Parameter names can contain spaces and special characters: @[parameter name] or @[firstname.lastname@example.org]
The query parser allows whitespace between @ and parameter name and between @ and unescaped string literal: @ abc works, @ 'c:\windows\system32' works.
There is a new VALUE statement which declares global variables.
Example: VALUE @deg2rad FLOAT64 = Pi / 180;
Example: VALUE @angle FLOAT64 = 30;
Example: VALUE @anglerad FLOAT64 = @angle * @deg2rad;
Type is required. Type can be TABLE.
A global variable can be redefined and assigned a different type and value. When this happens, previous references to the global variable will continue to use its former type and value (similarly to overriding functions).
There is a short form of VALUE that omits the expression: VALUE @name NVARCHAR;
The short form of VALUE takes an existing value and converts it to the specified type. If there is no value with the specified name defined, the short form defines it and sets it to a NULL value.
(The short form is useful for handling query parameters. Say, we have a query that returns records from some table for a specific year and we want to specify the year as a parameter. We can do VALUE @year INT32; without specifying any value, and then if the caller passed a value, we will have it in @year and if it did not pass a value, we will have @year be a NULL. We can then do VALUE @year INT32 = COALESCE (@year, 2018); to provide a default value and proceed from there, with @year guaranteed to be defined and have the type we want for further statements.)
Names of query and function parameters are allowed to be reserved words (because @ removes the ambiguity).
There is a new TABLE statement which takes a query expression and returns a table.
Old: EXECUTE CALL ValueSequence(0, 10, 1);
New: TABLE CALL ValueSequence(0, 10, 1);
The [[ ... ]] embeddings can be nested. [[[ and longer sequences are ignored instead of being parsed as multiples of [[, same for ]].
(This allows having queries with nested EXECUTE statements and several other things that now use embeddings, read below.)
Loading a MAP file resolves expressions in computed fields and constraints in a way that allows successfully loading expressions from cross-referencing tables.
Loading a MAP file allows expressions in computed fields and constraints to fail to load. All existing data including data in computed fields with expressions that failed to load is fully and safely accessible. Computed fields and constraints with expressions that failed to load can be safely removed.
Computed fields allow specifying one or more statements to prepare execution context for the expression which computes values. This allows expressions for computed fields to use functions and scripts. Each computed field uses its own execution context, functions and values defined in the context of a computed field are inaccessible from other fields. The syntax for computed fields in ALTER TABLE / CREATE TABLE is changed to use embeddings and allow specifying statements to prepare execution context using WITH.
Constraints allow specifying one or more statements to prepare execution context for the expression which accepts or rejects records, similarly to computed fields.
There is a new SCRIPT statement which defines an inline script inside the query. Inline scripts can then be referenced by FUNCTION.
(See examples for many of the above things in posts following this one.)
Queries generated by all panes and dialogs are adjusted to use the new syntax (@ for parameter names, EXECUTE CALL -> TABLE CALL, a number of queries that were using parameter-less functions now use VALUE, etc).
The query builder lists all changes to syntax.
We are also working to update the API doc (and extend the object model a bit to cover the additions).
End of list.