[Tarantool-patches] [PATCH v1 2/2] sql: introduce DOUBLE type
imeevma at tarantool.org
imeevma at tarantool.org
Sat Dec 21 19:03:11 MSK 2019
This patch introduces type DOUBLE in SQL.
Closes #3812
Needed for #4233
@TarantoolBot document
Title: Tarantool DOUBLE field type and DOUBLE type in SQL
The DOUBLE field type was added to Tarantool mainly for adding the
DOUBLE type to SQL.
In Lua, only non-integer numbers and CDATA of type DOUBLE can be
inserted in this field. You cannot insert integers of type Lua
NUMBER or CDATA of type int64 or uint64 in this field. The same
rules apply to key in get(), select(), update() and upsert()
methods.
It is important to note that you can use the ffi.cast() function
to cast numbers to CDATA of type DOUBLE. An example of this can be
seen below.
Another very important point is that CDATA of type DOUBLE in lua
can be used in arithmetic, but arithmetic for them does not work
correctly. This comes from LuaJIT and most likely will not be
fixed.
Example of usage in Lua:
s = box.schema.space.create('s', {format = {{'d', 'double'}}})
_ = s:create_index('ii')
s:insert({1.1})
ffi = require('ffi')
s:insert({ffi.cast('double', 1)})
s:insert({ffi.cast('double', tonumber('123'))})
s:select(1.1)
s:select({ffi.cast('double', 1)})
In SQL, DOUBLE type behavior is different due to implicit casting.
In a column of type DOUBLE, the number of any supported type can
be inserted. However, it is possible that the number that will be
inserted will be different from that which is inserted due to the
rules for casting to DOUBLE.
Example of usage in SQL:
box.execute('CREATE TABLE t (d DOUBLE PRIMARY KEY);')
box.execute('INSERT INTO t VALUES (10), (-2.0), (3.3);')
box.execute('SELECT * FROM t;')
box.execute('SELECT d / 100 FROM t;')
box.execute('SELECT * from t WHERE d < 15;')
box.execute('SELECT * from t WHERE d = 3.3;')
---
extra/mkkeywordhash.c | 2 +-
src/box/sql/expr.c | 6 +-
src/box/sql/parse.y | 3 +-
src/box/sql/sqlInt.h | 3 +-
src/box/sql/vdbe.c | 4 +
src/box/sql/vdbemem.c | 15 +-
test/sql/gh-3888-values-blob-assert.result | 4 +-
test/sql/misc.result | 4 +-
test/sql/types.result | 390 ++++++++++++++++++++++++++++-
test/sql/types.test.lua | 66 +++++
10 files changed, 473 insertions(+), 24 deletions(-)
diff --git a/extra/mkkeywordhash.c b/extra/mkkeywordhash.c
index 3c3de4c..0062856 100644
--- a/extra/mkkeywordhash.c
+++ b/extra/mkkeywordhash.c
@@ -202,7 +202,7 @@ static Keyword aKeywordTable[] = {
{ "DENSE_RANK", "TK_STANDARD", true },
{ "DESCRIBE", "TK_STANDARD", true },
{ "DETERMINISTIC", "TK_STANDARD", true },
- { "DOUBLE", "TK_STANDARD", true },
+ { "DOUBLE", "TK_DOUBLE", true },
{ "ELSEIF", "TK_STANDARD", true },
{ "ENABLE", "TK_ENABLE", false },
{ "FETCH", "TK_STANDARD", true },
diff --git a/src/box/sql/expr.c b/src/box/sql/expr.c
index 0bdcfe5..f76f822 100644
--- a/src/box/sql/expr.c
+++ b/src/box/sql/expr.c
@@ -388,9 +388,11 @@ sql_type_result(enum field_type lhs, enum field_type rhs)
if (sql_type_is_numeric(lhs) || sql_type_is_numeric(rhs)) {
if (lhs == FIELD_TYPE_NUMBER || rhs == FIELD_TYPE_NUMBER)
return FIELD_TYPE_NUMBER;
+ if (lhs == FIELD_TYPE_DOUBLE || rhs == FIELD_TYPE_DOUBLE)
+ return FIELD_TYPE_DOUBLE;
if (lhs == FIELD_TYPE_INTEGER || rhs == FIELD_TYPE_INTEGER)
return FIELD_TYPE_INTEGER;
- assert(lhs == FIELD_TYPE_UNSIGNED ||
+ assert(lhs == FIELD_TYPE_UNSIGNED &&
rhs == FIELD_TYPE_UNSIGNED);
return FIELD_TYPE_UNSIGNED;
}
@@ -2260,7 +2262,7 @@ sql_expr_needs_no_type_change(const struct Expr *p, enum field_type type)
case TK_INTEGER:
return type == FIELD_TYPE_INTEGER;
case TK_FLOAT:
- return type == FIELD_TYPE_NUMBER;
+ return type == FIELD_TYPE_DOUBLE;
case TK_STRING:
return type == FIELD_TYPE_STRING;
case TK_BLOB:
diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y
index 1d0c95f..c5f6e47 100644
--- a/src/box/sql/parse.y
+++ b/src/box/sql/parse.y
@@ -981,7 +981,7 @@ idlist(A) ::= nm(Y). {
p->type = FIELD_TYPE_INTEGER;
break;
case TK_FLOAT:
- p->type = FIELD_TYPE_NUMBER;
+ p->type = FIELD_TYPE_DOUBLE;
break;
case TK_TRUE:
case TK_FALSE:
@@ -1846,6 +1846,7 @@ typedef(A) ::= VARCHAR char_len(B) . {
%type number_typedef {struct type_def}
typedef(A) ::= number_typedef(A) .
number_typedef(A) ::= NUMBER . { A.type = FIELD_TYPE_NUMBER; }
+number_typedef(A) ::= DOUBLE . { A.type = FIELD_TYPE_DOUBLE; }
number_typedef(A) ::= INT|INTEGER_KW . { A.type = FIELD_TYPE_INTEGER; }
number_typedef(A) ::= UNSIGNED . { A.type = FIELD_TYPE_UNSIGNED; }
diff --git a/src/box/sql/sqlInt.h b/src/box/sql/sqlInt.h
index 2594b73..6c50572 100644
--- a/src/box/sql/sqlInt.h
+++ b/src/box/sql/sqlInt.h
@@ -1278,7 +1278,8 @@ enum trim_side_mask {
#define sql_type_is_numeric(X) ((X) == FIELD_TYPE_INTEGER || \
(X) == FIELD_TYPE_NUMBER || \
- (X) == FIELD_TYPE_UNSIGNED)
+ (X) == FIELD_TYPE_UNSIGNED || \
+ (X) == FIELD_TYPE_DOUBLE)
/*
* Additional bit values that can be ORed with an type without
diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
index 6ebc5f0..eccb265 100644
--- a/src/box/sql/vdbe.c
+++ b/src/box/sql/vdbe.c
@@ -347,6 +347,10 @@ mem_apply_type(struct Mem *record, enum field_type type)
if ((record->flags & (MEM_Real | MEM_Int | MEM_UInt)) != 0)
return 0;
return sqlVdbeMemRealify(record);
+ case FIELD_TYPE_DOUBLE:
+ if ((record->flags & MEM_Real) != 0)
+ return 0;
+ return sqlVdbeMemRealify(record);
case FIELD_TYPE_STRING:
/*
* Only attempt the conversion to TEXT if there is
diff --git a/src/box/sql/vdbemem.c b/src/box/sql/vdbemem.c
index 407b42e..df3f0d8 100644
--- a/src/box/sql/vdbemem.c
+++ b/src/box/sql/vdbemem.c
@@ -741,6 +741,7 @@ sqlVdbeMemCast(Mem * pMem, enum field_type type)
(pMem->flags & MEM_UInt) == 0)
return -1;
return 0;
+ case FIELD_TYPE_DOUBLE:
case FIELD_TYPE_NUMBER:
return sqlVdbeMemRealify(pMem);
case FIELD_TYPE_VARBINARY:
@@ -1820,26 +1821,12 @@ mpstream_encode_vdbe_mem(struct mpstream *stream, struct Mem *var)
if (var->flags & MEM_Null) {
mpstream_encode_nil(stream);
} else if (var->flags & MEM_Real) {
- /*
- * We can't pass to INT iterator float
- * value. Hence, if floating point value
- * lacks fractional component, we can
- * encode it as INT and successfully
- * pass to INT iterator.
- */
- i = var->u.r;
- if (i == var->u.r && i < 0)
- goto encode_int;
- if (i == var->u.r && i >= 0)
- goto encode_uint;
mpstream_encode_double(stream, var->u.r);
} else if (var->flags & MEM_Int) {
i = var->u.i;
-encode_int:
mpstream_encode_int(stream, i);
} else if (var->flags & MEM_UInt) {
i = var->u.u;
-encode_uint:
mpstream_encode_uint(stream, i);
} else if (var->flags & MEM_Str) {
mpstream_encode_strn(stream, var->z, var->n);
diff --git a/test/sql/gh-3888-values-blob-assert.result b/test/sql/gh-3888-values-blob-assert.result
index 4b8e7ed..edc5035 100644
--- a/test/sql/gh-3888-values-blob-assert.result
+++ b/test/sql/gh-3888-values-blob-assert.result
@@ -53,7 +53,7 @@ box.execute('VALUES(-0.5e-2)')
---
- metadata:
- name: column1
- type: number
+ type: double
rows:
- [-0.005]
...
@@ -70,7 +70,7 @@ box.execute('SELECT 3.14')
---
- metadata:
- name: '3.14'
- type: number
+ type: double
rows:
- [3.14]
...
diff --git a/test/sql/misc.result b/test/sql/misc.result
index a157ddb..1fc39a8 100644
--- a/test/sql/misc.result
+++ b/test/sql/misc.result
@@ -83,7 +83,7 @@ box.execute('SELECT 1.5;')
---
- metadata:
- name: '1.5'
- type: number
+ type: double
rows:
- [1.5]
...
@@ -91,7 +91,7 @@ box.execute('SELECT 1.0;')
---
- metadata:
- name: '1.0'
- type: number
+ type: double
rows:
- [1]
...
diff --git a/test/sql/types.result b/test/sql/types.result
index 1ad52e8..d2cc3aa 100644
--- a/test/sql/types.result
+++ b/test/sql/types.result
@@ -309,7 +309,7 @@ box.execute('SELECT 1 + 1.1;')
---
- metadata:
- name: 1 + 1.1
- type: number
+ type: double
rows:
- [2.1]
...
@@ -1691,3 +1691,391 @@ box.execute('DROP TABLE t1;')
---
- row_count: 1
...
+-- gh-3812: Make sure DOUBLE type works correctly.
+box.execute("SELECT 1.0;")
+---
+- metadata:
+ - name: '1.0'
+ type: double
+ rows:
+ - [1]
+...
+box.execute("SELECT .01;")
+---
+- metadata:
+ - name: '.01'
+ type: double
+ rows:
+ - [0.01]
+...
+box.execute("SELECT CAST(1 AS DOUBLE);")
+---
+- metadata:
+ - name: CAST(1 AS DOUBLE)
+ type: double
+ rows:
+ - [1]
+...
+box.execute("SELECT CAST(1.123 AS DOUBLE);")
+---
+- metadata:
+ - name: CAST(1.123 AS DOUBLE)
+ type: double
+ rows:
+ - [1.123]
+...
+box.execute("SELECT CAST(true AS DOUBLE);")
+---
+- null
+- 'Type mismatch: can not convert TRUE to double'
+...
+box.execute("SELECT CAST('asd' AS DOUBLE);")
+---
+- null
+- 'Type mismatch: can not convert asd to double'
+...
+box.execute("SELECT CAST('1' AS DOUBLE);")
+---
+- metadata:
+ - name: CAST('1' AS DOUBLE)
+ type: double
+ rows:
+ - [1]
+...
+box.execute("SELECT CAST('1.123' AS DOUBLE);")
+---
+- metadata:
+ - name: CAST('1.123' AS DOUBLE)
+ type: double
+ rows:
+ - [1.123]
+...
+box.execute("SELECT CAST(x'' AS DOUBLE);")
+---
+- null
+- 'Type mismatch: can not convert varbinary to double'
+...
+box.execute("SELECT CAST(x'35' AS DOUBLE);")
+---
+- null
+- 'Type mismatch: can not convert varbinary to double'
+...
+box.execute("SELECT CAST(CAST(x'35' AS STRING) AS DOUBLE);")
+---
+- metadata:
+ - name: CAST(CAST(x'35' AS STRING) AS DOUBLE)
+ type: double
+ rows:
+ - [5]
+...
+box.execute('CREATE TABLE t (i INT PRIMARY KEY AUTOINCREMENT, d DOUBLE);')
+---
+- row_count: 1
+...
+box.execute('INSERT INTO t(d) VALUES (10), (-2.0), (3.3), (18000000000000000000);')
+---
+- autoincrement_ids:
+ - 1
+ - 2
+ - 3
+ - 4
+ row_count: 4
+...
+box.execute('SELECT * FROM t;')
+---
+- metadata:
+ - name: I
+ type: integer
+ - name: D
+ type: double
+ rows:
+ - [1, 10]
+ - [2, -2]
+ - [3, 3.3]
+ - [4, 18000000000000000000]
+...
+box.execute('SELECT d / 100 FROM t;')
+---
+- metadata:
+ - name: d / 100
+ type: double
+ rows:
+ - [0.1]
+ - [-0.02]
+ - [0.033]
+ - [180000000000000000]
+...
+box.execute('SELECT * from t WHERE d < 15;')
+---
+- metadata:
+ - name: I
+ type: integer
+ - name: D
+ type: double
+ rows:
+ - [1, 10]
+ - [2, -2]
+ - [3, 3.3]
+...
+box.execute('SELECT * from t WHERE d = 3.3;')
+---
+- metadata:
+ - name: I
+ type: integer
+ - name: D
+ type: double
+ rows:
+ - [3, 3.3]
+...
+box.execute("SELECT sum(d) FROM t;")
+---
+- metadata:
+ - name: sum(d)
+ type: number
+ rows:
+ - [18000000000000000000]
+...
+box.execute("SELECT avg(d) FROM t;")
+---
+- metadata:
+ - name: avg(d)
+ type: number
+ rows:
+ - [4500000000000000000]
+...
+box.execute("SELECT total(d) FROM t;")
+---
+- metadata:
+ - name: total(d)
+ type: number
+ rows:
+ - [18000000000000000000]
+...
+box.execute("SELECT min(d) FROM t;")
+---
+- metadata:
+ - name: min(d)
+ type: scalar
+ rows:
+ - [-2]
+...
+box.execute("SELECT max(d) FROM t;")
+---
+- metadata:
+ - name: max(d)
+ type: scalar
+ rows:
+ - [18000000000000000000]
+...
+box.execute("SELECT count(d) FROM t;")
+---
+- metadata:
+ - name: count(d)
+ type: integer
+ rows:
+ - [4]
+...
+box.execute("SELECT group_concat(d) FROM t;")
+---
+- metadata:
+ - name: group_concat(d)
+ type: string
+ rows:
+ - ['10.0,-2.0,3.3,1.8e+19']
+...
+box.execute("SELECT lower(d) FROM t;")
+---
+- metadata:
+ - name: lower(d)
+ type: string
+ rows:
+ - ['10.0']
+ - ['-2.0']
+ - ['3.3']
+ - ['1.8e+19']
+...
+box.execute("SELECT upper(d) FROM t;")
+---
+- metadata:
+ - name: upper(d)
+ type: string
+ rows:
+ - ['10.0']
+ - ['-2.0']
+ - ['3.3']
+ - ['1.8E+19']
+...
+box.execute("SELECT abs(d) FROM t;")
+---
+- metadata:
+ - name: abs(d)
+ type: number
+ rows:
+ - [10]
+ - [2]
+ - [3.3]
+ - [18000000000000000000]
+...
+box.execute("SELECT typeof(d) FROM t;")
+---
+- metadata:
+ - name: typeof(d)
+ type: string
+ rows:
+ - ['double']
+ - ['double']
+ - ['double']
+ - ['double']
+...
+box.execute("SELECT quote(d) FROM t;")
+---
+- metadata:
+ - name: quote(d)
+ type: string
+ rows:
+ - ['10.0']
+ - ['-2.0']
+ - ['3.3']
+ - ['1.8e+19']
+...
+box.execute("SELECT LEAST(d, 0) FROM t;")
+---
+- metadata:
+ - name: LEAST(d, 0)
+ type: scalar
+ rows:
+ - [0]
+ - [-2]
+ - [0]
+ - [0]
+...
+box.execute("CREATE INDEX dd ON t(d);")
+---
+- row_count: 1
+...
+box.execute("SELECT d FROM t WHERE d < 0;")
+---
+- metadata:
+ - name: D
+ type: double
+ rows:
+ - [-2]
+...
+box.execute("SELECT d FROM t ORDER BY d;")
+---
+- metadata:
+ - name: D
+ type: double
+ rows:
+ - [-2]
+ - [3.3]
+ - [10]
+ - [18000000000000000000]
+...
+box.execute("UPDATE t SET d = 1 WHERE d = 10;")
+---
+- row_count: 1
+...
+box.execute("SELECT d FROM t;")
+---
+- metadata:
+ - name: D
+ type: double
+ rows:
+ - [1]
+ - [-2]
+ - [3.3]
+ - [18000000000000000000]
+...
+box.execute("DROP TABLE t;")
+---
+- row_count: 1
+...
+box.execute("CREATE TABLE t1 (d DOUBLE PRIMARY KEY);")
+---
+- row_count: 1
+...
+box.execute("INSERT INTO t1 VALUES (1), (2.2), (3.5);")
+---
+- row_count: 3
+...
+box.execute("INSERT INTO t1 VALUES (1);")
+---
+- null
+- Duplicate key exists in unique index 'pk_unnamed_T1_1' in space 'T1'
+...
+box.execute("CREATE TABLE t2 (i INT PRIMARY KEY, d DOUBLE REFERENCES t1);")
+---
+- row_count: 1
+...
+box.execute("INSERT INTO t2 VALUES (1,1), (2,2.2), (100, 3.5), (4, 1);")
+---
+- row_count: 4
+...
+box.execute("INSERT INTO t2 VALUES (5,10);")
+---
+- null
+- 'Failed to execute SQL statement: FOREIGN KEY constraint failed'
+...
+box.execute("DROP TABLE t2;")
+---
+- row_count: 1
+...
+box.execute("DROP TABLE t1;")
+---
+- row_count: 1
+...
+box.execute("CREATE TABLE t3 (i INT PRIMARY KEY, d DOUBLE CHECK (d < 10));")
+---
+- row_count: 1
+...
+box.execute("INSERT INTO t3 VALUES (1, 1);")
+---
+- row_count: 1
+...
+box.execute("INSERT INTO t3 VALUES (2, 9.999999);")
+---
+- row_count: 1
+...
+box.execute("INSERT INTO t3 VALUES (3, 10.0000001);")
+---
+- null
+- 'Check constraint failed ''ck_unnamed_T3_1'': d < 10'
+...
+box.execute("SELECT * FROM t3;")
+---
+- metadata:
+ - name: I
+ type: integer
+ - name: D
+ type: double
+ rows:
+ - [1, 1]
+ - [2, 9.999999]
+...
+box.execute("DROP TABLE t3;")
+---
+- row_count: 1
+...
+box.execute("CREATE TABLE t4 (i INT PRIMARY KEY, d DOUBLE DEFAULT 1.2345);")
+---
+- row_count: 1
+...
+box.execute("INSERT INTO t4(i) VALUES (1);")
+---
+- row_count: 1
+...
+box.execute("SELECT * FROM t4;")
+---
+- metadata:
+ - name: I
+ type: integer
+ - name: D
+ type: double
+ rows:
+ - [1, 1.2345]
+...
+box.execute("DROP TABLE t4;")
+---
+- row_count: 1
+...
diff --git a/test/sql/types.test.lua b/test/sql/types.test.lua
index f6fb64c..24bfa42 100644
--- a/test/sql/types.test.lua
+++ b/test/sql/types.test.lua
@@ -399,3 +399,69 @@ s:insert({1, {b = 1}})
box.execute('INSERT INTO t1(a) SELECT a FROM t2;')
s:drop()
box.execute('DROP TABLE t1;')
+
+-- gh-3812: Make sure DOUBLE type works correctly.
+box.execute("SELECT 1.0;")
+box.execute("SELECT .01;")
+
+box.execute("SELECT CAST(1 AS DOUBLE);")
+box.execute("SELECT CAST(1.123 AS DOUBLE);")
+box.execute("SELECT CAST(true AS DOUBLE);")
+box.execute("SELECT CAST('asd' AS DOUBLE);")
+box.execute("SELECT CAST('1' AS DOUBLE);")
+box.execute("SELECT CAST('1.123' AS DOUBLE);")
+box.execute("SELECT CAST(x'' AS DOUBLE);")
+box.execute("SELECT CAST(x'35' AS DOUBLE);")
+box.execute("SELECT CAST(CAST(x'35' AS STRING) AS DOUBLE);")
+
+box.execute('CREATE TABLE t (i INT PRIMARY KEY AUTOINCREMENT, d DOUBLE);')
+box.execute('INSERT INTO t(d) VALUES (10), (-2.0), (3.3), (18000000000000000000);')
+box.execute('SELECT * FROM t;')
+box.execute('SELECT d / 100 FROM t;')
+box.execute('SELECT * from t WHERE d < 15;')
+box.execute('SELECT * from t WHERE d = 3.3;')
+
+box.execute("SELECT sum(d) FROM t;")
+box.execute("SELECT avg(d) FROM t;")
+box.execute("SELECT total(d) FROM t;")
+box.execute("SELECT min(d) FROM t;")
+box.execute("SELECT max(d) FROM t;")
+box.execute("SELECT count(d) FROM t;")
+box.execute("SELECT group_concat(d) FROM t;")
+
+box.execute("SELECT lower(d) FROM t;")
+box.execute("SELECT upper(d) FROM t;")
+box.execute("SELECT abs(d) FROM t;")
+box.execute("SELECT typeof(d) FROM t;")
+box.execute("SELECT quote(d) FROM t;")
+box.execute("SELECT LEAST(d, 0) FROM t;")
+
+box.execute("CREATE INDEX dd ON t(d);")
+box.execute("SELECT d FROM t WHERE d < 0;")
+box.execute("SELECT d FROM t ORDER BY d;")
+
+box.execute("UPDATE t SET d = 1 WHERE d = 10;")
+box.execute("SELECT d FROM t;")
+box.execute("DROP TABLE t;")
+
+box.execute("CREATE TABLE t1 (d DOUBLE PRIMARY KEY);")
+box.execute("INSERT INTO t1 VALUES (1), (2.2), (3.5);")
+box.execute("INSERT INTO t1 VALUES (1);")
+
+box.execute("CREATE TABLE t2 (i INT PRIMARY KEY, d DOUBLE REFERENCES t1);")
+box.execute("INSERT INTO t2 VALUES (1,1), (2,2.2), (100, 3.5), (4, 1);")
+box.execute("INSERT INTO t2 VALUES (5,10);")
+box.execute("DROP TABLE t2;")
+box.execute("DROP TABLE t1;")
+
+box.execute("CREATE TABLE t3 (i INT PRIMARY KEY, d DOUBLE CHECK (d < 10));")
+box.execute("INSERT INTO t3 VALUES (1, 1);")
+box.execute("INSERT INTO t3 VALUES (2, 9.999999);")
+box.execute("INSERT INTO t3 VALUES (3, 10.0000001);")
+box.execute("SELECT * FROM t3;")
+box.execute("DROP TABLE t3;")
+
+box.execute("CREATE TABLE t4 (i INT PRIMARY KEY, d DOUBLE DEFAULT 1.2345);")
+box.execute("INSERT INTO t4(i) VALUES (1);")
+box.execute("SELECT * FROM t4;")
+box.execute("DROP TABLE t4;")
--
2.7.4
More information about the Tarantool-patches
mailing list