9.0.167.6

manifold-9.0.167.6-x64.zip

SHA256: f35f45d5794d167b425780e77e40f5d2a4e9dc667157ad8e9153f2cc88ef94e6

manifold-viewer-9.0.167.6-x64.zip

SHA256: 6a3aeef458ad95b4f493998c4ca31daf87733c6c592ae01363afd8a409d02ce4

WARNING

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.

Overview

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.

Changes

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 @[john@sample.com]

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.

MAP files created by this build will not load on previous builds.

To anticipate the question, this build reads MAP files created by prior builds just fine. No worries.

More advanced queries might need some adjustments for the new syntax, but I think anybody who writes queries at that level will applaud the new changes and be happy to make a few tweaks.

---

This is a really huge build that is about as technical as it gets, and may seem to be for a limited audience of wizards. Not so. Having execution control over multithreaded action in queries means we can hang intense, multithreaded SQL onto a template in the Transform panel, and that's all about point-and-click convenience. That it opens many doors for people writing queries is also super.

Congratulations to both France and Croatia for a great game! Football at its finest.

I got to be French for a day. We were walking around Edinburgh's Royal Mile with the family, and came upon this nice man outside the French Embassy, putting up signs. Figured he was a janitor or something. We had a nice chat. He invited us to the Embassy the next evening for Bastille Day, and said we were welcome to come back on Sunday if France won the Cup.

Well, we went back the next day, and were greeting at the door by the same man, who gave us some wine. Turns out he wasn't the janitor, but rather the Consulate General of the French Embassy! What a wonderfully humble and kind man! That sold it for me - I rooted for France. Partly because of the Ambassador, but also we were looking to go to another free party.

Also went drinking in a Scottish pub with the locals to watch England / Croatia, all planning to root for England of course because we were in the UK. Boy, were we surprised!! No love for the English in a Scottish pub, that's for sure. Got to explain American baseball and how a New Yorker would never root for the Red Sox under any condition - there are commonalities all over.

The possibility of @variables holding VALUEs is very welcome.

Question about migration:

When 9.0.167.0 was released in May I suggested to client that they shall use regular builds and not Cutting Edge. With next regular release they should migrate. When it might happen? I'm ready (see below), but for planning it would be great to know.

Migrating a project to new syntax was not as hard as it first seemed. I had to find all FUNCTION definitions and prepend'@' to parameter names. I found 240 places from 9000+ lines of code. I used the Dump_Code addin to find all the occurences of function definitions.

We are planning to have a couple more cutting edge builds and then a public build in a few weeks - ideally before the end of July.

In addition to adding @ to the names of function parameters (both in the prototype and in the body) and query parameters if you are using those (both places again), look for EXECUTE <query> and replace with TABLE <query> (typically, we are talking about replacing EXECUTE CALL ... with TABLE CALL ...).

Example 1: computed fields and expressions that fail to load.

Let's create a table with a computed field that takes data from another table:

This is table 1:

--SQL9

CREATE TABLE countries (

  mfd_id INT64, INDEX mfd_id_x BTREE (mfd_id),

  name NVARCHAR, INDEX name_x BTREE (name),

  footballperf NVARCHAR);

INSERT INTO countries (name, footballperf) VALUES

  ('France', 'champion'),

  ('Croatia', '#2'),

  ('Belgium', '#3'),

  ('United Kingdom', '#4');

This is table 2:

--SQL9

CREATE TABLE places (

  mfd_id INT64, INDEX mfd_id_x BTREE (mfd_id),

  name NVARCHAR,

  country NVARCHAR,

  countryfootballperf NVARCHAR

    WITH [[

      FUNCTION countryperf(@country NVARCHAR) NVARCHAR AS (

        SELECT footballperf FROM countries WHERE name=@country

      ) END;

    ]]

    AS [[ countryperf(country) ]]

);

...for each record in 'places', we go into 'countries', look up the record with our 'country' and take the value of a field.

Let's insert some records just to see how it works:

--SQL9

INSERT INTO places (name, country) VALUES

  ('Paris', 'France'),

  ('London', 'United Kingdom'),

  ('Lisbon', 'Portugal');

Nothing out of the ordinary so far. If we save the MAP file, then close and reopen it, it opens just fine.

Now let's say we add a computed field that transports data in the reverse direction:

--SQL9

ALTER TABLE countries (

  ADD exampleplace NVARCHAR

    WITH [[

      FUNCTION anyplace(@country NVARCHAR) NVARCHAR AS (

        SELECT First(name) FROM places WHERE country=@country

      ) END;

    ]]

    AS [[ anyplace(name) ]]

);

...for each record in 'countries', we go into 'places', look up any record corresponding to our country and take the value of a field.

The second computed field creates a conundrum for the loader. If we start loading the 'countries' table, this will try to load the expression for 'exampleplace', this will try to load the 'places' table (to see if it has a field named 'place', etc, and what type it is), then the 'places' table will try to load the expression for 'countryfootballperf', and that expression will try to load the 'countries' table. This is a circular dependency, none of the two tables can load first, so in the previous builds we were aborting the process and the MAP file was refusing to open. This was pretty bad, because with a simple addition of a field you could render the MAP file useless. So, starting with this build, we are using a different loading technique which allows both fields to load successfully.

Let's continue the previous example.

Delete 'countries'.

We now have a single table 'places' with a computed field that tries to take data from 'countries', which no longer exists.

In the previous builds, if you saved the MAP file and then closed and reopened it, it would fail to load. In the current build, it loads and the computed field that references 'countries' marks itself as failed to load and just starts returning NULL values. You can open the 'places' table and you can see all of its data. You can insert new records, you can delete existing records, you can edit existing records (and editing 'country' which would normally recompute the value of the computed field will not try to recompute it), and finally, you can delete the computed field that failed to load and perhaps add a replacement field.

All of the above applies to constraints as well.

Let's try it again with a new MAP file:

This is table 1, same as before:

--SQL9

CREATE TABLE countries (

  mfd_id INT64, INDEX mfd_id_x BTREE (mfd_id),

  name NVARCHAR, INDEX name_x BTREE (name),

  footballperf NVARCHAR);

INSERT INTO countries (name, footballperf) VALUES

  ('France', 'champion'),

  ('Croatia', '#2'),

  ('Belgium', '#3'),

  ('United Kingdom', '#4');

This is table 2:

--SQL9

CREATE TABLE places (

  mfd_id INT64, INDEX mfd_id_x BTREE (mfd_id),

  place NVARCHAR,

  country NVARCHAR,

  CONSTRAINT countryvalid_c

    AS [[ country IN (SELECT name FROM countries) ]]

);

Let's try adding records:

--SQL9

INSERT INTO places (place, country)

  VALUES ('Paris', 'France');

INSERT INTO places (place, country)

  VALUES ('London', 'United Kingdom');

INSERT INTO places (place, country)

  VALUES ('Lisbon', 'Portugal'); -- fails

...the first two records go through, the third fails due to a constraint firing, as expected.

Let's now delete 'countries', save the MAP file, then close and reload it.

The constraint in 'places' can no longer find 'countries', but the table still loads. We cannot insert new records until the constraint is removed, because it keeps returning NULL values and that rejects all new records, but that's about the extent of the hassle.

Example 2: more on execution context.

Let's start with a simple table:

--SQL9

CREATE TABLE t (mfd_id INT64, INDEX mfd_id_x BTREE (mfd_id), a INT32);

INSERT INTO t (a) VALUES (10), (11), (12);

...open the table and Ctrl-click its tab to dock it below the command window, this way we will see what happens with it.

Add a computed field:

--SQL9

ALTER TABLE t (

  ADD b INT32 AS [[ a+1 ]]

);

Add a computed field that uses a function:

--SQL9

ALTER TABLE t (

  ADD c INT32

    WITH [[ FUNCTION f(@x INT32) INT32 AS @x+100 END; ]]

    AS [[ f(a) ]]

);

Add a computed field that uses a global value:

--SQL9

ALTER TABLE t (

  ADD d INT32

    WITH [[ VALUE @mul INT32 = 500; ]]

    AS [[ a * @mul ]]

);

Add a constraint that uses a global value:

--SQL9

ALTER TABLE t (

  ADD CONSTRAINT a_c

    WITH [[ VALUE @min INT32 = 0; ]]

    AS [[ a>=@min ]]

);

Add a computed field that uses an inline script to set insert date for each record:

--SQL9

ALTER TABLE t (

  ADD insertdate DATETIME

    WITH

[[

SCRIPT funcs ENGINE 'c#' [[

  class Script

  {

    static System.DateTime F() { return System.DateTime.Now; }

  }

]];

FUNCTION currentdate() DATETIME AS SCRIPT INLINE funcs ENTRY 'Script.F';

]]

    AS [[ currentdate() ]]

);

...after the above query, existing records will get the current date. Wait a minute or so, then insert a new record either manually or using INSERT, it should get the new now-current value.

Wow. An oblique question first...

Can an inline script do something procedural, such as showing a dialog, logging a message to the application log, finding a file in the filesystem? If so, will it fire (a) on compilation and/or (b) on each call? Will SQL execution always wait synchronously for the inline script to hit its return statement?

Don't know (get an answer soon)... but in the meantime, and completely off topic except for the mention in the Example, I cannot resist sharing an .mxb with Locations for the world cup stadiums. On a full screen display, Samara looks like the aliens have landed. The drawing's table has all the matches played at each to help remember where you were during the Cup. When it is all a blur that is one sign it was a good World Cup. :-)

Attachments:
2018_World_Cup_Stadiums.mxb

As long as you're running freely off topic, we adopted my daughter from Samara in 1999. For an aerospace engineer to visit the home of Russian space exploration and aviation was quite a treat. The sunburst design of the stadium is a nod to the space aspect of the town. Although, I'm a little surprised the stadium isn't shaped like Yuri Gagarin somehow.

Part of our tour of Moscow included a visit to the Moscow State University overlooking the stadium. As I recall the stadium was built for the Olympics in Moscow. It was all very interesting; although, I am happy to live in Texas.

An inline script can do anything. Part of the script will run during the set up when the expression is loaded (everything in WITH, you can first define an inline script and then call its functions using VALUE @... = <calling a script function> or, say, TABLE CALL <calling a script function that returns a table>) and parts will run synchronously when the table will need to compute the value of the computed field / perform the check on the constraint.

It is not a good idea to display any UI, in neither phase. It is way too easy to get too many windows popping up at what might sometimes seem to be random moments. Logging is perhaps fine as a debugging measure, but only as that, otherwise it also can quickly become way too chatty.

For now, we are not restricting what can happen in WITH, but we will perhaps have to, sooner or later.

Also, please use .NET languages for now. COM languages are currently not supported (they commonly use objects with thread affinity, we have to handle that specifically, otherwise evaluating the expression might fail).

Here is an example.

Create this table:

--SQL9

CREATE TABLE data (

  mfd_id INT64, INDEX mfd_id_x BTREE (mfd_id),

  value NVARCHAR,

  logger INT32

    WITH

[[

SCRIPT [inline] ENGINE 'c#'

[[

class Script

{

static Manifold.Context Manifold;

static int Log(string table, long id, string value)

{

  Manifold.Application.Log(string.Format("{0}: record {1} = {2}",

    table, id, value));

  return 0; // ignored

}

}

]]

FUNCTION logvalue(@table NVARCHAR, @id INT64, @value NVARCHAR) INT32

  AS SCRIPT INLINE [inline] ENTRY 'Script.Log';

]]

    AS [[ logvalue('[data]', mfd_id, value) ]]

);

The table has a computed field which logs changes to other fields (originally, 'value', but then I extended logging to log 'mfd_id' for the record, so changes to 'mfd_id' are also going to be logged).

Do some inserts / updates:

--SQL9

INSERT INTO data (value) VALUES ('abc');

INSERT INTO data (value) VALUES ('def');

UPDATE data SET value=value & 'x' WHERE value='def';

You should see something like this in the log:

#

++ [data]: record 1 = abc

++ [data]: record 2 = def

++ [data]: record 2 = defx

That's first INSERT, second INSERT and UPDATE, respectively.

But, again, this has to be used with care, because inserting a million records will just flood the log. The log will survive, but inserts will be noticeably slower than usual and, more importantly, you will not be able to see anything in the log apart from those automated messages. Maybe have a counter to log first ten messages or log no more than one message per minute (save date and time of last logged message, this needs a static variable and a critical section) or something like that.

Thanks for the advice Adam and for going to the effort with the example.

For now I think it would be worth using this sort of thing mainly for logging a warning in unusual cases (say, geometry unexpectedly NULL or with few vertices), flagged for checking later rather than being rejected outright with a constraint. In some cases where a constraint is used, it might be worth doing a manual check first, making a manual correction if possible along with a warning, or in a serious case logging a reason before falling through to fail the constraint.

Anyway your example is really informative. It's really cool that this is even possible--with the natural corollary that we can shoot ourselves in the foot.

Another thought. A useful alternative to

logging a warning in unusual cases

would be to insert a point into a separate drawing or, even better (and thinking of Dimitri's recent video), to create a new Location object, for each tricky case encountered.

These filter mechanisms will be useful not only for data entered via the GUI, but equally for data inserted by query, since it is the insertion of data by any means that will fire a computed-field filter.

So, an analytic query that records all cases, while defined unusual cases are automatically saved to Locations for easy inspection--that seems very useful.

There is a new VALUE statement which declares global variables.

... Type can be TABLE.

Many possibilities here.

Among other things, it provides a simpler alternative to the temporary database infrastructure.

A trivial example:

--SQL9

VALUE @temp TABLE = (SELECT * FROM [mfd_meta]);

SELECT * FROM @temp;

Compare that to the syntax for using a temporary child database (visible in the Project pane):

--SQL9

CREATE DATASOURCE [Scratch] (PROPERTY 'Type' 'manifold');

SELECT * INTO [Scratch]::[temp] FROM [mfd_meta];

SELECT * FROM [Scratch]::[temp];

DROP DATABASE [Scratch];

or to the syntax for using a temporary root database (not visible):

--SQL9

CREATE ROOT [Scratch];

USE ROOT [Scratch];

CREATE DATASOURCE [Main] AS ROOT;

SELECT * INTO [temp] FROM [Main]::[mfd_meta];

SELECT * FROM [temp];

DROP ROOT [Scratch];

(The examples are artificial, used just to show syntax.)

I think there will still be reasons to use a temporary child database (the second example), if we want to inspect the temporary result table, or use it from another query.

I'm not sure about the temporary root database (third example). It might always be easier to use one or more global table variables, with no loss of function.

Another thing. Sometimes we want to reuse the result of a function at several points in a query. The downside is that the function is evaluated once for each call. (As Adam has previously explained, it must be, in case a function uses a time-dependent or random function, or accesses a live database.

But now we use a variable instead. The result might be a simple scalar value, or a table. For example it might be a CoordinateConverter object, which is treated syntactically like a table.

VALUE @cc TABLE =

    CALL CoordConverterMake(

        ComponentCoordSystem(target_drawing),

        ComponentCoordSystem(source_drawing)

        )

    ;

Now we can use the coordinate converter several times, but it will only be built once, with a useful saving of overhead.

This will be useful in many other cases where we would otherwise have use repeat function calls.

Similarly, we sometimes want to SELECT an interim result, and use the result in two or more different ways. We might want to join a result table to itself, for example.

We could write two identical subqueries, but that is wasteful and hard to maintain. We can SELECT INTO a static table, use two or more instances of the result, then delete it. In the same way we can write a table to a temporary database (of either kind). Or we can make multiple calls to a function returning a table (with repeat evaluation).

Now with 9.0.167.6 we can assign the interim result to a global variable and use two instances. Very clean and readable. (Presumably we can also free resources by setting the variable to NULL after use.)

Does a table stored in a global variable include ORDER (if order is applied)? Yes, and it seems to be maintained between successive uses--nice!--though I expect the usual caveat will apply (order becoming arbitrary) when we use multiple threads and/or a large table.

VALUE @t TABLE = (SELECT * FROM mfd_meta ORDER BY [Name] DESC);

SELECT * FROM @t;

SELECT * FROM @t WHERE [Property] = 'Table';

Really enjoying the new syntax. It's fantastic.

Look how tidy and readable this is, to fetch and store image metadata in constants, ready for use throughout a query. Notice we can enter the names of the current image and table names just once, to be reused multiple times in further definitions and throughout the query. So easy to edit and reuse.

--SQL9

VALUE @table TABLE = [BA32_GRIDLESS_GeoTifv1-06 Tiles];

VALUE @image TABLE = [BA32_GRIDLESS_GeoTifv1-06]; -- NB image qua TABLE

--

VALUE @field NVARCHAR = ComponentProperty(@image, 'FieldTile');

VALUE @dimXY INT32X2 = CAST(ComponentProperty(@table, 'FieldTileSize.' + @field) AS INT32X2);

VALUE @dimX INT32 = VectorValue(@dimXY, 0);

VALUE @dimY INT32 = VectorValue(@dimXY, 1);

--

VALUE @rect INT32X4 = CAST(ComponentProperty(@image, 'Rect') AS INT32X4);

VALUE @minX INT32 = CAST(VectorValue(@rect, 0) AS INT32);

VALUE @maxX INT32 = CAST(VectorValue(@rect, 1) AS INT32);

VALUE @minY INT32 = CAST(VectorValue(@rect, 2) AS INT32);

VALUE @maxY INT32 = CAST(VectorValue(@rect, 3) AS INT32);

--

VALUE @cs NVARCHAR = ComponentProperty(@table, 'FieldCoordSystem.' + @field);

VALUE @scaleX FLOAT64 = StringJsonNumber(@cs, 'LocalScaleX', False);

VALUE @scaleY FLOAT64 = StringJsonNumber(@cs, 'LocalScaleY', False);

VALUE @offsetX FLOAT64 = StringJsonNumber(@cs, 'LocalOffsetX', False);

VALUE @offsetY FLOAT64 = StringJsonNumber(@cs, 'LocalOffsetY', False);

(Possibly a bit overdone.)

Oops. @maxX and @minY are switched. Too late for the edit.

The third section should read

VALUE @rect INT32X4 = CAST(ComponentProperty(@image, 'Rect') AS INT32X4);

VALUE @minX INT32 = CAST(VectorValue(@rect, 0) AS INT32);

VALUE @minY INT32 = CAST(VectorValue(@rect, 1) AS INT32);

VALUE @maxX INT32 = CAST(VectorValue(@rect, 2) AS INT32);

VALUE @maxY INT32 = CAST(VectorValue(@rect, 3) AS INT32);

I just noticed that we can use a CASE expression within a VALUE definition, e.g.

--SQL9

VALUE @bool BOOLEAN = TRUE;

VALUE @flag NVARCHAR = CASE WHEN @bool THEN 'yes' ELSE 'no' END;

Very nice! To quote:

We want to do it the right way and leave no holes.

Yup.

Temporary databases can do something that table VALUEs cannot do - they can describe what the table contains via properties. You can have a table with geoms, but you cannot pass it to a query function that takes a drawing just yet, because that function will need to know which of the potentially multiple geom fields to use and what the coordinate system of that field is. Same for images.

We have been discussing ways to bundle all relevant information of that type into a table value. Maybe we will do this in the future, but regardless, this will only be a subset of all properties. Temporary databases will likely always remain the most complete and straightforward way to model all relations between temporary data that we allow.

PS: Reprojection queries generated by the UI are also now storing coordinate converters in VALUEs, just like you show.

PPS: Yes, setting a variable to NULL frees resources. The only gotcha is that you have to switch the type from TABLE to something else, because the system will interpret VALUE @x TABLE = NULL as 'the table named NULL' - we should perhaps change that, but that's the way it is now. So, do VALUE @x TABLE = <something complex> and then later when you no longer need it, do something like VALUE @x INT32 = NULL.

Thanks Adam, really helpful.

Especially first para--I hadn't even thought of properties.

(I sort of like the idea of a table named NULL! Bit like a horse with no name.)