[
https://issues.apache.org/jira/browse/CALCITE-6997?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Yu Xu updated CALCITE-6997:
---------------------------
Summary: Sqlite dialect implementation (was: DuckDB dialect implementation)
> Sqlite dialect implementation
> -----------------------------
>
> Key: CALCITE-6997
> URL: https://issues.apache.org/jira/browse/CALCITE-6997
> Project: Calcite
> Issue Type: New Feature
> Components: core
> Affects Versions: 1.39.0
> Reporter: Yu Xu
> Assignee: Yu Xu
> Priority: Major
> Fix For: 1.40.0
>
>
> SQLite is a C-language library that implements a
> [small|https://www.sqlite.org/footprint.html],
> [fast|https://www.sqlite.org/fasterthanfs.html],
> [self-contained|https://www.sqlite.org/selfcontained.html],
> [high-reliability|https://www.sqlite.org/hirely.html],
> [full-featured|https://www.sqlite.org/fullsql.html], SQL database engine.
> SQLite is the [most used|https://www.sqlite.org/mostdeployed.html] database
> engine in the world. ([https://www.sqlite.org/)]
> Currently Calcite not support dialect for it, maybe we need to support it.
> relevant materials about mainly syntax difference between default dialect and
> Sqlite(from document : [https://sqlite.org/docs.html)]
>
> 1. {*}max precision of decimal is 15{*}(we can refer to
> https://sqlite.org/datatype3.html[)|https://duckdb.org/docs/stable/sql/data_types/numeric#fixed-point-decimals)*]
> only the first 15 significant decimal digits of the number are preserved.
> test(result with a 15 length decimal numbric):
>
> {code:java}
> sqlite> select cast('1.2300000000001234' as decimal(15,15));
> 1.23000000000012{code}
>
> 2. *Not support character_length/char_length, need use length function to
> support it:*
>
> {code:java}
> sqlite> select char_length('aa');
> Parse error: no such function: char_length
> select char_length('aa');
> ^--- error here
> sqlite> select character_length('aa');
> Parse error: no such function: character_length
> select character_length('aa');
> ^--- error here {code}
> only support length function to get size of string
>
>
> {code:java}
> sqlite> select length('aa');
> 2 {code}
>
>
> 3. *NullCollation is LOW mode, Nulls last for DESC and first for ASC, test as
> follow：*
> according to doc [https://sqlite.org/datatype3.html,] can search as follow:
> A value with storage class NULL is considered less than any other value
> (including another value with storage class NULL).
> real env test:
>
> {code:java}
> sqlite> create table t5(id int, data varchar);
> sqlite> insert into t5 select 1, 'aa';
> sqlite> insert into t5 select 2, 'bb';
> sqlite> insert into t5 select 3, NULL;
> sqlite> insert into t5 select 4, NULL;
> sqlite> select * from t5 order by data;
> 3|
> 4|
> 1|aa
> 2|bb
> sqlite> select * from t5 order by data desc;
> 2|bb
> 1|aa
> 3|
> 4|
> sqlite> select * from t5 order by data asc;
> 3|
> 4|
> 1|aa
> 2|bb {code}
> from the test result, we can find that it is LOW mode for NULL.
>
>
> 4. *Not support offset fetch rows sytnax*
> test:
> {code:java}
> sqlite> select * from t5 OFFSET 1 ROWS FETCH NEXT 2 ROWS ONLY;
> Parse error: near "1": syntax error
> select * from t5 OFFSET 1 ROWS FETCH NEXT 2 ROWS ONLY;
> ^--- error here {code}
> but support limit offset syntax:
> {code:java}
> sqlite> select * from t5 limit 2 offset 0;
> 1|aa
> 2|bb {code}
> here need to convert correct right way to support it.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
