RQG: Virtual persistent columns not computed properly for default NULL values

[Philip Stoev]

In the example below, the virtual persistent column does not agree that the result from INTERVAL ( NULL , NULL ) is -1 . If the computation is executed without the aid of a virtual column, or if the virtual column is not persisted, the result is correct.

CREATE TABLE t1 ( f1 CHAR(32), f2 CHAR(32), v1 INT AS ( INTERVAL( f2 , NULL ) ) PERSISTENT );
INSERT INTO t1 ( f1 ) VALUES ( NULL );

SELECT INTERVAL(NULL , NULL), v1, ( INTERVAL( f2, NULL ) ) FROM t1;

[Philip Stoev (pstoev-askmonty)]

Here is another example:

--disable_abort_on_error
DROP TABLE t1;
DROP TABLE t2;

CREATE TABLE t1 ( f1 CHAR(32) NOT NULL, v1 INT AS ( f1 ) PERSISTENT );
INSERT INTO t1 () VALUES ();

CREATE TABLE t2 ( f1 CHAR(32) NOT NULL, v1 INT AS ( f1 ) VIRTUAL );
INSERT INTO t2 () VALUES ();

SELECT f1, v1 FROM t1;
SELECT f1, v1 FROM t2;

[Philip Stoev (philip-stoev)]

What happens here is that if you use an INSERT that does not explicitly list all the columns in the table, some of the columns will get the default value. If such default values are then used in a virtual column computation, the result from the computation will be incorrect.

If you use an INSERT form where all fields are specified and thus no defaults kick in, virtual columns are computed correctly.