Re: [Question] Working with defaults in calcite.

[stanilovsky evgeny]

Thanks for pointing a right test, seems defaults are broken:
such test is failed.
issue created : https://issues.apache.org/jira/browse/CALCITE-5950

# Create a basic table DEFAULT
create table tdef (i int not null, j int default 100);
(0 rows modified)

!update

insert into tdef values (1, DEFAULT);
(1 row modified)

!update

insert into tdef(i) values (2);
(1 row modified)

!update

select * from tdef order by i;
+---+-----+
| I | J   |
+---+-----+
| 1 | 100 |
| 2 | 100 |
+---+-----+
(2 rows)

!ok

but obtain from calcite:

+---+-----+
| I | J   |
+---+-----+
| 1 |     |
| 2 | 100 |
+---+-----+

If you way there are no execution tests, I wouldn’t be surprised. DDL was historically deemed out of scope, and what we didn’t implement, we couldn’t test. But now we have the ’server’ component, and there are tests such as table.iq [1].

Can you add some tests?

Julian

[1] https://github.com/apache/calcite/blob/main/server/src/test/resources/sql/table.iq

On Aug 14, 2023, at 7:40 AM, stanilovsky evgeny <estanilovs...@gridgain.com> wrote:

Hello community !
I just try a simple case, like :

create table foo (i int not null, j int default 100);

and obtain parser error: parse failed: Encountered "default" at line 1, column 41.

while
create table foo (i int not null, j int);
insert into foo values (1, DEFAULT);

throws no exceptions in quidem test framework

i also find SqlValidatorTest#testInsertShouldNotCheckForDefaultValue that contains newColumnDefaultValue:

// Now remove DEPTNO, which has a default value, from the target list. // Will generate an extra call to newColumnDefaultValue at sql-to-rel time,

   // just not yet.

final String sql4 = "insert into ^emp^ (empno, ename, job, mgr, hiredate,\n"

       + "  sal, comm, slacker)\n"
       + "values(1, 'nom', 'job', 0,\n"
       + "  timestamp '1970-01-01 00:00:00', 1, 1, false)";

org.apache.calcite.sql2rel.InitializerExpressionFactory#newColumnDefaultValue

so is it correct that:

1. calcite has no execution tests for CREATE TABLE with DEFAULT columns ? 2. seems can`t process correctly such kind of columns with insertions ? (i see no tests)

CREATE TABLE integers(i INTEGER PRIMARY KEY, col1 INTEGER DEFAULT 200, col2 INTEGER DEFAULT 100)

INSERT INTO integers VALUES (1, DEFAULT, DEFAULT)

INSERT INTO integers(i, col2) VALUES (2, DEFAULT), (3, 4), (4, DEFAULT)");

thanks !