I think if you make the column optional the validator will let you omit it from the INSERT. (It’s not really optional, but it has a default value, which is similar.)
And you’ll need to provide a different value for SqlToRelConverter.defaultValueFactory so that the column gets populated with the correct expression. See the changes I made to SqlToRelConverter to fix https://issues.apache.org/jira/browse/CALCITE-505 <https://issues.apache.org/jira/browse/CALCITE-505>. > On Feb 22, 2016, at 5:53 PM, Homer <logsplitter4...@gmail.com> wrote: > > Hi, > > I am trying to add a system column to my table, lets call it rowid for > argument sake and familiarity. > > I want to be able to do inserts without specifying rowid as it is added only > on the way out as a system column > > So if I do this > > Add a column called rowid to the metadata about table test > > then do: > > INSERT INTO test VALUES(7, 42, 101, 1001, 't', 1.1, 2.2, 'foo', 'real_foo', > '2014-12-13 22:23:15', '15:13:14', '1999-09-09', 9, 111.1); > > I get this > > org.apache.calcite.sql.validate.SqlValidatorException: Number of INSERT > target columns (15) does not equal number of source items (14) > > As it knows I don't have enough columns as I have added rowid to my metadata > about the table. > > If I don't add the rowid to the metadata I fail when attempting to do a > select from the table which includes rowid in the projection. > > ie: Select value1, rowid from test; > > So I am wondering what is the recommended way to deal with system columns in > this kind of circumstance. > > thanks
