Yu Xu created CALCITE-6997:
------------------------------
Summary: DuckDB 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
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)
