Purpose
Many dialects have native support for server side computed columns using syntaxes such as GENERATED ALWAYS AS
. These are useful for:
- Replacing triggers to produce identity values / sequence generated values or UUID primary key values
- Calculate some expression that can be used in an index. It's generally a cleaner approach than function based indexes, as the use site does not have to remember and repeat the expression in every query in order for the computed column's index to apply.
If a dialect doesn't support these features, or in some cases where the feature is not good enough for various reasons (e.g. Oracle doesn't support materialised computed columns, which would be useful if the computation is CPU intensive), it would be nice to have this feature in jOOQ, client side.
Comparison with RecordListener
Users can already implement it using RecordListener
, but this feature would affect also any explicitly constructed INSERT
, UPDATE
or MERGE
statement, including parsed ones, if meta data lookup is applied.
Unlike RecordListener
, which has access to the entire Record
being inserted, the computation expression cannot access any values from other columns, although accessing expressions based on other columns is possible.
Tasks
The following changes are needed:
- [x] Additional meta information on the
DataType
meta model
- [x] A new
GenerationLocation
enum with values CLIENT
and SERVER
(default)
- [x] A new
Generator<T> extends Function<GeneratorContext, Field<T>>, Serializable
type
- [x] A
GeneratorContext <: Scope
argument object
- [x] Providing access to a
Configuration
- [x] Providing access to statement information (
INSERT
, UPDATE
, SELECT
)
- [x] Providing access to the target
Field<T>
expression that is being generated
- [x] Or better:
Generator<R extends Record, T extends Table<R>, U> extends Function<T, Field<U>>, Serializable
, such that lambdas can reference aliased tables?
- [x] It doesn't seem to be necessary as a
Generator
can always access the aliased table via this
(lambda) or MyTable.this
(anonymous class). On the other hand, that would require a Generator
to capture the instance, which can cause other issues.
- [x] A
DataType.generatedAlwaysAs(Generator<T>)
overload, which works only with GenerationLocation.CLIENT
- [x] ~A
DataType.default(Generator<T>)
overload, which works only with GenerationLocation.CLIENT
~: Postponed: https://github.com/jOOQ/jOOQ/issues/13443
- [x] Code generation support to produce synthetic
GENERATED
expressions
- [x] A
forcedType
configuration element generator
, which can reference a class or contain inline code
- [x] It should be possible to have a forced type only with a
generator
. No name
, no userType
- [x]
generator
applies to
- [x]
TableField
(first draft)
- [x] ~
IN
parameters (maybe in a later release)~: Postponed https://github.com/jOOQ/jOOQ/issues/13444
- [x] ~
UDT
attributes (maybe in a later release)~: Postponed https://github.com/jOOQ/jOOQ/issues/13444
- [x] Tests in Java, Scala, Kotlin
- [x] ~Settings to enable / disable the feature at runtime~ (this is quite some work, let's wait for a customer requesting this. The workarounds are simple. Just create a plain SQL column expression)
- [x] Implementation logic
- [x] Tests for
VIRTUAL
semantics:
- [x] Simple query
- [x] Projecting virtual columns
- [x] Projecting expressions based on virtual columns
- [x] Function calls
- [x] Binary expressions
- [x] Aggregate functions
- [x] Window functions
- [x] Projecting aliased virtual columns
- [x] Projecting transitive computations (computed column depends on another computed column)
- [x] Implicit join
- [x] Computed implicit join referencing another computed column
- [x] Multisets
- [x] Uncorrelated
- [x] Correlated
- [x] Nested
- [x] ~MultisetAgg~ (this doesn't really make much sense as a client side computed virtual column)
- [x] Table aliases
- [x] Especially when used with implicit joins
- [x] These seem to work implicitly if the
Generator
captures a this
reference of the table, which may be aliased already!
- [x]
RETURNING
clauses
- [x] Tests for
STORED
semantics:
- [x]
INSERT
- [x]
INSERT .. VALUES
- [x] With alternative column order
- [x]
INSERT .. SELECT
- [x] Multi row
INSERT
- [x]
INSERT .. ON DUPLICATE KEY ..
- [x]
IGNORE
- [x] Native implementation
- [x] Merge emulation
- [x]
UPDATE
- [x] Native implementation (we might be able to leverage a peculiar MySQL "feature" where
SET
clauses aren't evaluated atomically, see https://twitter.com/lukaseder/status/1507019871569125381)
- [x] Reordering columns to 1) user
SET
clauses, 2) computed columns
- [x] Merge emulation with
SET ROW = ..
- [x] Merge emulation
- [x]
INSERT .. ON CONFLICT DO ..
- [x]
NOTHING
- [x] Native implementation
- [x] Merge emulation
- [x]
UPDATE
- [x] Native implementation (probably depends on https://github.com/jOOQ/jOOQ/issues/6769)
- [x]
SET ..
- [x]
SET ROW = ROW
- [x]
SET ROW = (SELECT)
- [x] Merge emulation with
SET ROW = ..
- [x]
SET ..
- [x]
SET ROW = ROW
- [x]
SET ROW = (SELECT)
- [x] Merge emulation
- [x]
INSERT .. RETURNING
- [x]
UPDATE
- [x]
UPDATE .. SET ..
- [x]
UPDATE .. SET ROW = ROW
- [x]
UPDATE .. SET ROW = SELECT
- [x] Computation column inclusion
- [x] Trivial solution would re-compute all columns
- [x]
UPDATE .. RETURNING
- [x]
UpdatableRecord
operations?
- [x] Combine with converters / bindings
- [x] The SQL transformation should have a simple and sophisticated version
- [x] Sophisticated is everything we've discussed so far, and it can handle arbitrary expressions from the
Generator
- [x] ~Synthetic
CREATE TABLE
and ALTER TABLE
DDL support for the interpreter~ (maybe later)
Related work / follow up work:
- https://github.com/jOOQ/jOOQ/issues/13411
- https://github.com/jOOQ/jOOQ/issues/13418
Caveats:
-
[x] MySQL UPDATE
doesn't run the SET
clause atomically, see https://twitter.com/lukaseder/status/1507019364800688134, https://stackoverflow.com/q/37649/521799. This has 2 implications:
- [x] Computed columns must be calculated lexically after all user-provided
SET
clauses
-
[x] It would be very helpful if we could guarantee that each Generator
is invoked exacly once per query rendering, in order to produce more predictable results, e.g. when a Generator
produces a timestamp.
More caveats and known issues here:
-
https://github.com/jOOQ/jOOQ/issues/13685
Examples:
Independent computed expressions
With a hypothetical synthetic table definition like this:
CREATE TABLE t (
i INT,
j INT CLIENT GENERATED ALWAYS AS 1,
k INT CLIENT GENERATED BY DEFAULT AS 2
);
The following statements...
INSERT INTO t (i, j) VALUES (0, 0);
INSERT INTO t (i, j, k) VALUES (0, 0, 0);
INSERT INTO t (i, j, k) SELECT 0, 0, 0;
UPDATE t SET i = 0, j = 0, k = 0;
... would be transformed to these ones
INSERT INTO t (i, j, k) VALUES (0, 1, 2);
INSERT INTO t (i, j, k) VALUES (0, 1, 0);
INSERT INTO t (i, j, k) SELECT i, 1, k FROM (SELECT 0, 0, 0) t (i, j, k);
UPDATE t SET i = 0, j = 1, k = 0;
Dependent computed expressions
Just like server side computed columns, it should be possible to form expressions based on other columns, e.g. in the aforementioned hypothetical syntax:
CREATE TABLE t (
i INT,
j INT CLIENT GENERATED ALWAYS AS i + 1,
k INT CLIENT GENERATED BY DEFAULT AS i + 2
);
The following statements...
INSERT INTO t (i, j) VALUES (0, 0);
INSERT INTO t (i, j, k) VALUES (0, 0, 0);
INSERT INTO t (i, j, k) SELECT 0, 0, 0;
UPDATE t SET i = 0, j = 0, k = 0;
... would be transformed to these ones
INSERT INTO t (i, j, k) SELECT i, i + 1, i + 2 FROM (VALUES (0)) t (i);
INSERT INTO t (i, j, k) SELECT i, i + 1, k FROM (VALUES (0, 0)) t (i, k);
INSERT INTO t (i, j, k) SELECT i, i + 1, k FROM (SELECT 0, 0, 0) t (i, j, k);
UPDATE t
SET (i, j, k) = (SELECT i, i + 1, k FROM (SELECT 0, 0, 0) t (i, j, k));
The UPDATE .. SET ROW = (SELECT ..)
emulation will be needed for this task, to be investigated: https://github.com/jOOQ/jOOQ/issues/10523
More tricky cases
The UPDATE .. SET ROW = (SELECT ..)
clause is being used to ensure atomic computation of all client side computed expressions. This means that existing SET ROW = (SELECT ..)
clauses might be a bit more tricky to handle:
Other
There will obviously be edge cases which are not properly handled in the above examples yet.
The following statements...
UPDATE t SET (i, j, k) = (0, 0, 0);
UPDATE t SET (i, j, k) = (SELECT 0, 0, 0);
UPDATE t SET
(i, j) = (SELECT 0, 0),
k = 0;
... would be transformed to these ones
-- SET ROW = ROW will just first flatten the ROW and then perform the usual emulation
UPDATE t SET (i, j, k) = (SELECT i, i + 1, k FROM (SELECT 0, 0, 0) t (i, j, k));
-- SET ROW = SELECT looks the same as the previous one, if there's only a single assignment
UPDATE t SET (i, j, k) = (SELECT i, i + 1, k FROM (SELECT 0, 0, 0) t (i, j, k));
-- But once we support multiple ROW assignments, some additional trickery will be needed
UPDATE t SET (i, j, k) = (
SELECT i, i + 1, k
FROM
(SELECT 0, 0) t (i, j), -- ROW assignment
(SELECT 0) u (k) -- k assignment
);
See also: https://github.com/jOOQ/jOOQ/issues/13330
GenerationOption
By default, the GenerationOption.STORED
flag is applied, meaning the value is generated upon INSERT
, UPDATE
, MERGE
directly in relevant SQL statements. However, GenerationOption.VIRTUAL
is also possible, in case of which the computation takes place in every SELECT
statement, as soon as the column is included.
- [x] Support
STORED
(when the forced type matches an actual column)
- [x] Support
VIRTUAL
(when the forced type matches a synthetic column). Prerequisites:
- [x] https://github.com/jOOQ/jOOQ/issues/13434
T: Enhancement C: Functionality P: Medium R: Fixed E: Professional Edition E: Enterprise Edition