[
https://issues.apache.org/jira/browse/CALCITE-6997?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Caican Cai resolved CALCITE-6997.
---------------------------------
Resolution: Fixed
> 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
> Labels: pull-request-available
> Fix For: 1.40.0
>
> Attachments: sqlite.jpg, sqlite_decimal_test.jpg
>
>
> 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://sqlite.org/datatype3.html])
> 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}
>
> 5. *Not Support trim(TRAILING) syntax, test like:*
> {code:java}
> sqlite> SELECT TRIM(TRAILING ' ' from ' str ');
> Parse error: near "' '": syntax error
> SELECT TRIM(TRAILING ' ' from ' str ');
> sqlite> SELECT TRIM(BOTH ' ' from ' str ');
> Parse error: near "' '": syntax error
> SELECT TRIM(BOTH ' ' from ' str ');
> sqlite> SELECT TRIM(LEADING ' ' from ' str ');
> Parse error: near "' '": syntax error
> SELECT TRIM(LEADING ' ' from ' str ');{code}
> trim support trim/ltrim/rtrim functions, here need to adapt it
> {code:java}
> sqlite> select trim(' aa');
> aa
> sqlite> select ltrim(' aa');
> aa
> sqlite> select ltrim(' aa', 'a');
> aa
> sqlite> select rtrim(' aa c', 'c');
> aa
> {code}
>
> 6. *SQLite not support position function need use INSTR function to instead
> of it:*
> {code:java}
> sqlite> select position('A' IN 'ABC');
> Parse error: no such table: ABC
> sqlite> SELECT INSTR('ABC', 'A');
> 1{code}
>
> 7. {*}SQLite not support nested type, such as ARRAY/MAP{*}... we can refer
> from doc and test for it:
> {code:java}
> Parse error: no such column: 1,2,3
> select [1,2,3] ;
> ^--- error here
> sqlite> select ARRAY[1,2,3];
> Parse error: no such column: ARRAY
> select ARRAY[1,2,3];
> ^--- error here
> sqlite> select [1,2,3];
> Parse error: no such column: 1,2,3
> select [1,2,3];
> ^--- error here
> sqlite> select {1,2,3,4};
> Parse error: unrecognized token: "{"
> select {1,2,3,4};
> ^--- error here
> sqlite> select MAP{1,2,3,4};
> Parse error: unrecognized token: "{"
> select MAP{1,2,3,4};
> ^--- error here {code}
> here need to convert correct right way to support it.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
