Details
-
New Feature
-
Status: Closed
-
Major
-
Resolution: Fixed
-
1.39.0
Description
SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. SQLite is the most used 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)
only the first 15 significant decimal digits of the number are preserved.
test(result with a 15 length decimal numbric):
sqlite> select cast('1.2300000000001234' as decimal(15,15)); 1.23000000000012
2. Not support character_length/char_length, need use length function to support it:
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
only support length function to get size of string
sqlite> select length('aa');
2
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:
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
from the test result, we can find that it is LOW mode for NULL.
4. Not support offset fetch rows sytnax
test:
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
but support limit offset syntax:
sqlite> select * from t5 limit 2 offset 0; 1|aa 2|bb
5. Not Support trim(TRAILING) syntax, test like:
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 ');
trim support trim/ltrim/rtrim functions, here need to adapt it
sqlite> select trim(' aa'); aa sqlite> select ltrim(' aa'); aa sqlite> select ltrim(' aa', 'a'); aa sqlite> select rtrim(' aa c', 'c'); aa
6. SQLite not support position function need use INSTR function to instead of it:
sqlite> select position('A' IN 'ABC'); Parse error: no such table: ABC sqlite> SELECT INSTR('ABC', 'A'); 1
7. SQLite not support nested type, such as ARRAY/MAP... we can refer from doc and test for it:
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
here need to convert correct right way to support it.
Attachments
Attachments
Issue Links
- links to