You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Homer <lo...@gmail.com> on 2016/02/23 02:53:55 UTC

System Columns

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

Re: System Columns

Posted by Julian Hyde <jh...@apache.org>.
RelDataType.isNullable() returns true if and only if the type is optional.

> On Feb 22, 2016, at 7:28 PM, Homer <lo...@gmail.com> wrote:
> 
> Julian,
> 
> Thanks for the quick response.
> 
> Sorry but I don't seem to see how I make the column optional.  I am looking at RelDataType and don't see anything that springs out at me.  Am I way off the mark?
> 
> thanks
> 
> On 2016-02-22 06:31 PM, Julian Hyde wrote:
>> 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 <lo...@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
>> 
>

Re: System Columns

Posted by Homer <lo...@gmail.com>.
Julian,

Thanks for the quick response.

Sorry but I don't seem to see how I make the column optional.  I am 
looking at RelDataType and don't see anything that springs out at me.  
Am I way off the mark?

thanks

On 2016-02-22 06:31 PM, Julian Hyde wrote:
> 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 <lo...@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
>

Re: System Columns

Posted by Julian Hyde <jh...@apache.org>.
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 <lo...@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