[
https://issues.apache.org/jira/browse/CALCITE-6997?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
xiong duan updated CALCITE-6997:
--------------------------------
Description:
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}
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.
was:
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}
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.
> 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
>
>
> 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}
>
> 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)
