Tarantool development patches archive
 help / color / mirror / Atom feed
* [Tarantool-patches] [PATCH v1 0/7] Rework SCALAR and NUMBER types in SQL
@ 2021-08-13  3:12 Mergen Imeev via Tarantool-patches
  2021-08-13  3:12 ` [Tarantool-patches] [PATCH v1 1/7] sql: remove enum field_type from struct Mem Mergen Imeev via Tarantool-patches
                   ` (8 more replies)
  0 siblings, 9 replies; 14+ messages in thread
From: Mergen Imeev via Tarantool-patches @ 2021-08-13  3:12 UTC (permalink / raw)
  To: vdavydov; +Cc: tarantool-patches

This patch reworks SCALAR and NUMBER types. Afther these patches SCANAR and
NUMBER values will follow newly defined rules. However, only one SQL built-in
function is actually changed in this patch. All other functions that should be
changed will be reworked in issue #6105.

https://github.com/tarantool/tarantool/issues/6221
https://github.com/tarantool/tarantool/tree/imeevma/gh-6221-introduce-mem-type-number

Mergen Imeev (7):
  sql: remove enum field_type from struct Mem
  sql: re-introduce NUMBER and SCALAR meta-types
  sql: disallow implicit cast from NUMBER and SCALAR
  sql: disallow arithmetic for NUMBER and SCALAR
  sql: disallow bitwise for NUMBER and SCALAR
  sql: disallow concatination for SCALAR
  sql: fix comparison with SCALAR value

 .../gh-5956-remove-field-types-from-mem.md    |   3 +
 ...21-re-introduce-scalar-and-number-types.md |   8 +
 src/box/sql/func.c                            |  19 +-
 src/box/sql/mem.c                             | 388 ++++++++----------
 src/box/sql/mem.h                             |  17 +-
 src/box/sql/vdbe.c                            |  35 +-
 src/box/sql/vdbeInt.h                         |   3 -
 src/box/sql/vdbesort.c                        |   6 -
 test/sql-tap/cast.test.lua                    |  26 +-
 test/sql-tap/colname.test.lua                 |   4 +-
 test/sql-tap/e_select1.test.lua               |   6 +-
 test/sql-tap/engine.cfg                       |   3 +
 test/sql-tap/func.test.lua                    |  12 +-
 test/sql-tap/func5.test.lua                   |  75 +++-
 .../gh-5335-wrong-int-to-double-cast.test.lua |  40 --
 ...-5756-implicit-cast-in-arithmetic.test.lua |  32 +-
 test/sql-tap/in4.test.lua                     |   2 +-
 test/sql-tap/metatypes.test.lua               | 187 +++++++++
 test/sql-tap/minmax2.test.lua                 |   2 +-
 test/sql-tap/numcast.test.lua                 |  58 +--
 test/sql-tap/select7.test.lua                 |   8 +-
 test/sql-tap/sort.test.lua                    |  14 +-
 test/sql-tap/sql-errors.test.lua              |   8 +-
 test/sql-tap/table.test.lua                   |   4 +-
 test/sql-tap/tkt-7bbfb7d442.test.lua          |   4 +-
 test/sql-tap/tkt-91e2e8ba6f.test.lua          |  96 -----
 test/sql-tap/tkt-a8a0d2996a.test.lua          |  18 +-
 test/sql-tap/trigger9.test.lua                |   2 +-
 test/sql-tap/uuid.test.lua                    |  12 +-
 test/sql-tap/view.test.lua                    |   4 +-
 test/sql/boolean.result                       | 368 +++++++++--------
 test/sql/boolean.test.sql                     |   4 +-
 test/sql/gh-4697-scalar-bool-sort-cmp.result  |  16 +-
 test/sql/prepared.result                      |   2 +-
 test/sql/prepared.test.lua                    |   2 +-
 test/sql/types.result                         |  19 +-
 36 files changed, 761 insertions(+), 746 deletions(-)
 create mode 100644 changelogs/unreleased/gh-5956-remove-field-types-from-mem.md
 create mode 100644 changelogs/unreleased/gh-6221-re-introduce-scalar-and-number-types.md
 delete mode 100755 test/sql-tap/gh-5335-wrong-int-to-double-cast.test.lua
 create mode 100755 test/sql-tap/metatypes.test.lua
 delete mode 100755 test/sql-tap/tkt-91e2e8ba6f.test.lua

-- 
2.25.1


^ permalink raw reply	[flat|nested] 14+ messages in thread

* [Tarantool-patches] [PATCH v1 1/7] sql: remove enum field_type from struct Mem
  2021-08-13  3:12 [Tarantool-patches] [PATCH v1 0/7] Rework SCALAR and NUMBER types in SQL Mergen Imeev via Tarantool-patches
@ 2021-08-13  3:12 ` Mergen Imeev via Tarantool-patches
  2021-08-13  3:12 ` [Tarantool-patches] [PATCH v1 2/7] sql: re-introduce NUMBER and SCALAR meta-types Mergen Imeev via Tarantool-patches
                   ` (7 subsequent siblings)
  8 siblings, 0 replies; 14+ messages in thread
From: Mergen Imeev via Tarantool-patches @ 2021-08-13  3:12 UTC (permalink / raw)
  To: vdavydov; +Cc: tarantool-patches

This patch removes the enum field_type from the struct MEM since the
only place where this field was used is in the typeof() function. After
this patch, typeof() will return "NULL" if it receives NULL. Prior to
this patch, the field type was returned if it was set, otherwise it was
returned "boolean". Another reason for the removal was that field_type
was quite volatile and could change in unexpected places.

Also, this patch made another change: values of the field type NUMBER
will now be processed by the typeof() as INTEGER or DOUBLE, depending
on the storage type. However, this will be fixed in the next patch.

Part of #5956
Needed for #6221
---
 .../gh-5956-remove-field-types-from-mem.md    |   3 +
 src/box/sql/func.c                            |  15 +--
 src/box/sql/mem.c                             | 120 +++---------------
 src/box/sql/mem.h                             |   7 -
 src/box/sql/vdbe.c                            |  25 +---
 src/box/sql/vdbeInt.h                         |   3 -
 src/box/sql/vdbesort.c                        |   6 -
 test/sql-tap/cast.test.lua                    |  16 +--
 test/sql-tap/default.test.lua                 |   2 +-
 test/sql-tap/func.test.lua                    |  12 +-
 test/sql-tap/func5.test.lua                   |  75 ++++++++++-
 test/sql-tap/select7.test.lua                 |   4 +-
 test/sql-tap/table.test.lua                   |   4 +-
 test/sql-tap/tkt-3998683a16.test.lua          |   1 -
 test/sql/boolean.result                       |   4 +-
 test/sql/gh-4697-scalar-bool-sort-cmp.result  |   4 +-
 test/sql/types.result                         |   8 +-
 17 files changed, 128 insertions(+), 181 deletions(-)
 create mode 100644 changelogs/unreleased/gh-5956-remove-field-types-from-mem.md

diff --git a/changelogs/unreleased/gh-5956-remove-field-types-from-mem.md b/changelogs/unreleased/gh-5956-remove-field-types-from-mem.md
new file mode 100644
index 000000000..81fc21233
--- /dev/null
+++ b/changelogs/unreleased/gh-5956-remove-field-types-from-mem.md
@@ -0,0 +1,3 @@
+## feature/sql
+
+* The typeof() function with NULL as an argument now returns "NULL" (gh-5956).
diff --git a/src/box/sql/func.c b/src/box/sql/func.c
index 1622104d3..5ac5c5e56 100644
--- a/src/box/sql/func.c
+++ b/src/box/sql/func.c
@@ -162,17 +162,6 @@ typeofFunc(sql_context * context, int NotUsed, sql_value ** argv)
 {
 	const char *z = 0;
 	UNUSED_PARAMETER(NotUsed);
-	enum field_type f_t = argv[0]->field_type;
-	/*
-	 * SCALAR is not a basic type, but rather an aggregation of
-	 * types. Thus, ignore SCALAR field type and return msgpack
-	 * format type.
-	 */
-	if (f_t != field_type_MAX && f_t != FIELD_TYPE_SCALAR) {
-		sql_result_text(context, field_type_strs[argv[0]->field_type],
-				-1, SQL_STATIC);
-		return;
-	}
 	switch (argv[0]->type) {
 	case MEM_TYPE_INT:
 	case MEM_TYPE_UINT:
@@ -190,9 +179,11 @@ typeofFunc(sql_context * context, int NotUsed, sql_value ** argv)
 		z = "varbinary";
 		break;
 	case MEM_TYPE_BOOL:
-	case MEM_TYPE_NULL:
 		z = "boolean";
 		break;
+	case MEM_TYPE_NULL:
+		z = "NULL";
+		break;
 	case MEM_TYPE_UUID:
 		z = "uuid";
 		break;
diff --git a/src/box/sql/mem.c b/src/box/sql/mem.c
index d1ef92a86..773ef4d40 100644
--- a/src/box/sql/mem.c
+++ b/src/box/sql/mem.c
@@ -193,7 +193,6 @@ mem_create(struct Mem *mem)
 {
 	mem->type = MEM_TYPE_NULL;
 	mem->flags = 0;
-	mem->field_type = field_type_MAX;
 	mem->n = 0;
 	mem->z = NULL;
 	mem->zMalloc = NULL;
@@ -226,7 +225,6 @@ mem_clear(struct Mem *mem)
 	}
 	mem->type = MEM_TYPE_NULL;
 	mem->flags = 0;
-	mem->field_type = field_type_MAX;
 }
 
 void
@@ -255,7 +253,6 @@ mem_set_int(struct Mem *mem, int64_t value, bool is_neg)
 	mem->u.i = value;
 	mem->type = is_neg ? MEM_TYPE_INT : MEM_TYPE_UINT;
 	assert(mem->flags == 0);
-	mem->field_type = FIELD_TYPE_INTEGER;
 }
 
 void
@@ -265,7 +262,6 @@ mem_set_uint(struct Mem *mem, uint64_t value)
 	mem->u.u = value;
 	mem->type = MEM_TYPE_UINT;
 	assert(mem->flags == 0);
-	mem->field_type = FIELD_TYPE_UNSIGNED;
 }
 
 void
@@ -275,14 +271,12 @@ mem_set_bool(struct Mem *mem, bool value)
 	mem->u.b = value;
 	mem->type = MEM_TYPE_BOOL;
 	assert(mem->flags == 0);
-	mem->field_type = FIELD_TYPE_BOOLEAN;
 }
 
 void
 mem_set_double(struct Mem *mem, double value)
 {
 	mem_clear(mem);
-	mem->field_type = FIELD_TYPE_DOUBLE;
 	assert(mem->flags == 0);
 	if (sqlIsNaN(value))
 		return;
@@ -294,7 +288,6 @@ void
 mem_set_uuid(struct Mem *mem, const struct tt_uuid *uuid)
 {
 	mem_clear(mem);
-	mem->field_type = FIELD_TYPE_UUID;
 	mem->u.uuid = *uuid;
 	mem->type = MEM_TYPE_UUID;
 	assert(mem->flags == 0);
@@ -309,7 +302,6 @@ set_str_const(struct Mem *mem, char *value, uint32_t len, int alloc_type)
 	mem->n = len;
 	mem->type = MEM_TYPE_STR;
 	mem->flags = alloc_type;
-	mem->field_type = FIELD_TYPE_STRING;
 }
 
 static inline void
@@ -323,7 +315,6 @@ set_str_dynamic(struct Mem *mem, char *value, uint32_t len, int alloc_type)
 	mem->n = len;
 	mem->type = MEM_TYPE_STR;
 	mem->flags = alloc_type;
-	mem->field_type = FIELD_TYPE_STRING;
 	if (alloc_type == MEM_Dyn) {
 		mem->xDel = sql_free;
 	} else {
@@ -395,7 +386,6 @@ mem_copy_str(struct Mem *mem, const char *value, uint32_t len)
 			return -1;
 		mem->type = MEM_TYPE_STR;
 		mem->flags = 0;
-		mem->field_type = FIELD_TYPE_STRING;
 		return 0;
 	}
 	mem_clear(mem);
@@ -405,7 +395,6 @@ mem_copy_str(struct Mem *mem, const char *value, uint32_t len)
 	mem->n = len;
 	mem->type = MEM_TYPE_STR;
 	assert(mem->flags == 0);
-	mem->field_type = FIELD_TYPE_STRING;
 	return 0;
 }
 
@@ -429,7 +418,6 @@ set_bin_const(struct Mem *mem, char *value, uint32_t size, int alloc_type)
 	mem->n = size;
 	mem->type = MEM_TYPE_BIN;
 	mem->flags = alloc_type;
-	mem->field_type = FIELD_TYPE_VARBINARY;
 }
 
 static inline void
@@ -443,7 +431,6 @@ set_bin_dynamic(struct Mem *mem, char *value, uint32_t size, int alloc_type)
 	mem->n = size;
 	mem->type = MEM_TYPE_BIN;
 	mem->flags = alloc_type;
-	mem->field_type = FIELD_TYPE_VARBINARY;
 	if (alloc_type == MEM_Dyn) {
 		mem->xDel = sql_free;
 	} else {
@@ -487,7 +474,6 @@ mem_copy_bin(struct Mem *mem, const char *value, uint32_t size)
 			return -1;
 		mem->type = MEM_TYPE_BIN;
 		mem->flags = 0;
-		mem->field_type = FIELD_TYPE_VARBINARY;
 		return 0;
 	}
 	mem_clear(mem);
@@ -497,7 +483,6 @@ mem_copy_bin(struct Mem *mem, const char *value, uint32_t size)
 	mem->n = size;
 	mem->type = MEM_TYPE_BIN;
 	assert(mem->flags == 0);
-	mem->field_type = FIELD_TYPE_VARBINARY;
 	return 0;
 }
 
@@ -512,76 +497,73 @@ mem_set_zerobin(struct Mem *mem, int n)
 	mem->n = 0;
 	mem->type = MEM_TYPE_BIN;
 	mem->flags = MEM_Zero;
-	mem->field_type = FIELD_TYPE_VARBINARY;
 }
 
 static inline void
 set_msgpack_value(struct Mem *mem, char *value, uint32_t size, int alloc_type,
-		  enum field_type type)
+		  enum mem_type type)
 {
-	assert(type == FIELD_TYPE_MAP || type == FIELD_TYPE_ARRAY);
 	if (alloc_type == MEM_Ephem || alloc_type == MEM_Static)
 		set_bin_const(mem, value, size, alloc_type);
 	else
 		set_bin_dynamic(mem, value, size, alloc_type);
-	mem->type = type == FIELD_TYPE_MAP ? MEM_TYPE_MAP : MEM_TYPE_ARRAY;
-	mem->field_type = type;
+	mem->type = type;
 }
 
 void
 mem_set_map_ephemeral(struct Mem *mem, char *value, uint32_t size)
 {
 	assert(mp_typeof(*value) == MP_MAP);
-	set_msgpack_value(mem, value, size, MEM_Ephem, FIELD_TYPE_MAP);
+	set_msgpack_value(mem, value, size, MEM_Ephem, MEM_TYPE_MAP);
 }
 
 void
 mem_set_map_static(struct Mem *mem, char *value, uint32_t size)
 {
 	assert(mp_typeof(*value) == MP_MAP);
-	set_msgpack_value(mem, value, size, MEM_Static, FIELD_TYPE_MAP);
+	set_msgpack_value(mem, value, size, MEM_Static, MEM_TYPE_MAP);
 }
 
 void
 mem_set_map_dynamic(struct Mem *mem, char *value, uint32_t size)
 {
 	assert(mp_typeof(*value) == MP_MAP);
-	set_msgpack_value(mem, value, size, MEM_Dyn, FIELD_TYPE_MAP);
+	set_msgpack_value(mem, value, size, MEM_Dyn, MEM_TYPE_MAP);
 }
 
 void
 mem_set_map_allocated(struct Mem *mem, char *value, uint32_t size)
 {
 	assert(mp_typeof(*value) == MP_MAP);
-	set_msgpack_value(mem, value, size, 0, FIELD_TYPE_MAP);
+	set_msgpack_value(mem, value, size, 0, MEM_TYPE_MAP);
 }
 
 void
 mem_set_array_ephemeral(struct Mem *mem, char *value, uint32_t size)
 {
 	assert(mp_typeof(*value) == MP_ARRAY);
-	set_msgpack_value(mem, value, size, MEM_Ephem, FIELD_TYPE_ARRAY);
+	set_msgpack_value(mem, value, size, MEM_Ephem, MEM_TYPE_ARRAY);
 }
 
 void
 mem_set_array_static(struct Mem *mem, char *value, uint32_t size)
 {
 	assert(mp_typeof(*value) == MP_ARRAY);
-	set_msgpack_value(mem, value, size, MEM_Static, FIELD_TYPE_ARRAY);
+	set_msgpack_value(mem, value, size, MEM_Static, MEM_TYPE_ARRAY);
 }
 
 void
 mem_set_array_dynamic(struct Mem *mem, char *value, uint32_t size)
 {
 	assert(mp_typeof(*value) == MP_ARRAY);
-	set_msgpack_value(mem, value, size, MEM_Dyn, FIELD_TYPE_ARRAY);
+	set_msgpack_value(mem, value, size, MEM_Dyn, MEM_TYPE_ARRAY);
 }
 
 void
 mem_set_array_allocated(struct Mem *mem, char *value, uint32_t size)
 {
 	assert(mp_typeof(*value) == MP_ARRAY);
-	set_msgpack_value(mem, value, size, 0, FIELD_TYPE_ARRAY);
+	set_msgpack_value(mem, value, size, 0, MEM_TYPE_ARRAY);
 }
 
 void
@@ -623,7 +605,6 @@ mem_set_agg(struct Mem *mem, struct func *func, int size)
 	mem->type = MEM_TYPE_AGG;
 	assert(mem->flags == 0);
 	mem->u.func = func;
-	mem->field_type = field_type_MAX;
 	return 0;
 }
 
@@ -646,7 +627,6 @@ int_to_double(struct Mem *mem)
 	mem->u.r = d;
 	mem->type = MEM_TYPE_DOUBLE;
 	assert(mem->flags == 0);
-	mem->field_type = FIELD_TYPE_DOUBLE;
 	return 0;
 }
 
@@ -660,7 +640,6 @@ int_to_double_precise(struct Mem *mem)
 		return -1;
 	mem->u.r = d;
 	mem->type = MEM_TYPE_DOUBLE;
-	mem->field_type = FIELD_TYPE_DOUBLE;
 	return 0;
 }
 
@@ -677,7 +656,6 @@ int_to_double_forced(struct Mem *mem)
 	double d = (double)i;
 	mem->u.r = d;
 	mem->type = MEM_TYPE_DOUBLE;
-	mem->field_type = FIELD_TYPE_DOUBLE;
 	return CMP_OLD_NEW(i, d, int64_t);
 }
 
@@ -691,7 +669,6 @@ uint_to_double_precise(struct Mem *mem)
 		return -1;
 	mem->u.r = d;
 	mem->type = MEM_TYPE_DOUBLE;
-	mem->field_type = FIELD_TYPE_DOUBLE;
 	return 0;
 }
 
@@ -708,7 +685,6 @@ uint_to_double_forced(struct Mem *mem)
 	double d = (double)u;
 	mem->u.r = d;
 	mem->type = MEM_TYPE_DOUBLE;
-	mem->field_type = FIELD_TYPE_DOUBLE;
 	return CMP_OLD_NEW(u, d, uint64_t);
 }
 
@@ -732,7 +708,6 @@ str_to_str0(struct Mem *mem)
 		return -1;
 	mem->z[mem->n] = '\0';
 	mem->flags |= MEM_Term;
-	mem->field_type = FIELD_TYPE_STRING;
 	return 0;
 }
 
@@ -742,7 +717,6 @@ str_to_bin(struct Mem *mem)
 	assert(mem->type == MEM_TYPE_STR);
 	mem->type = MEM_TYPE_BIN;
 	mem->flags &= ~MEM_Term;
-	mem->field_type = FIELD_TYPE_VARBINARY;
 	return 0;
 }
 
@@ -791,7 +765,6 @@ bin_to_str(struct Mem *mem)
 	if (ExpandBlob(mem) != 0)
 		return -1;
 	mem->type = MEM_TYPE_STR;
-	mem->field_type = FIELD_TYPE_STRING;
 	return 0;
 }
 
@@ -806,7 +779,6 @@ bin_to_str0(struct Mem *mem)
 	mem->z[mem->n] = '\0';
 	mem->type = MEM_TYPE_STR;
 	mem->flags = MEM_Term;
-	mem->field_type = FIELD_TYPE_STRING;
 	return 0;
 }
 
@@ -869,14 +841,12 @@ double_to_int(struct Mem *mem)
 		mem->u.i = (int64_t)d;
 		mem->type = MEM_TYPE_INT;
 		assert(mem->flags == 0);
-		mem->field_type = FIELD_TYPE_INTEGER;
 		return 0;
 	}
 	if (d > -1.0 && d < (double)UINT64_MAX) {
 		mem->u.u = (uint64_t)d;
 		mem->type = MEM_TYPE_UINT;
 		assert(mem->flags == 0);
-		mem->field_type = FIELD_TYPE_UNSIGNED;
 		return 0;
 	}
 	return -1;
@@ -891,14 +861,12 @@ double_to_int_precise(struct Mem *mem)
 		mem->u.i = (int64_t)d;
 		mem->type = MEM_TYPE_INT;
 		assert(mem->flags == 0);
-		mem->field_type = FIELD_TYPE_INTEGER;
 		return 0;
 	}
 	if (d > -1.0 && d < (double)UINT64_MAX && (double)(uint64_t)d == d) {
 		mem->u.u = (uint64_t)d;
 		mem->type = MEM_TYPE_UINT;
 		assert(mem->flags == 0);
-		mem->field_type = FIELD_TYPE_UNSIGNED;
 		return 0;
 	}
 	return -1;
@@ -937,7 +905,6 @@ double_to_int_forced(struct Mem *mem)
 	}
 	mem->u.i = i;
 	mem->type = type;
-	mem->field_type = FIELD_TYPE_INTEGER;
 	return res;
 }
 
@@ -950,7 +917,6 @@ double_to_uint(struct Mem *mem)
 		mem->u.u = (uint64_t)d;
 		mem->type = MEM_TYPE_UINT;
 		assert(mem->flags == 0);
-		mem->field_type = FIELD_TYPE_UNSIGNED;
 		return 0;
 	}
 	return -1;
@@ -965,7 +931,6 @@ double_to_uint_precise(struct Mem *mem)
 		mem->u.u = (uint64_t)d;
 		mem->type = MEM_TYPE_UINT;
 		assert(mem->flags == 0);
-		mem->field_type = FIELD_TYPE_UNSIGNED;
 		return 0;
 	}
 	return -1;
@@ -995,7 +960,6 @@ double_to_uint_forced(struct Mem *mem)
 	}
 	mem->u.u = u;
 	mem->type = MEM_TYPE_UINT;
-	mem->field_type = FIELD_TYPE_UNSIGNED;
 	return res;
 }
 
@@ -1009,7 +973,6 @@ double_to_str0(struct Mem *mem)
 	mem->n = strlen(mem->z);
 	mem->type = MEM_TYPE_STR;
 	mem->flags = MEM_Term;
-	mem->field_type = FIELD_TYPE_STRING;
 	return 0;
 }
 
@@ -1165,10 +1128,8 @@ mem_to_str(struct Mem *mem)
 int
 mem_cast_explicit(struct Mem *mem, enum field_type type)
 {
-	if (mem->type == MEM_TYPE_NULL) {
-		mem->field_type = type;
+	if (mem->type == MEM_TYPE_NULL)
 		return 0;
-	}
 	switch (type) {
 	case FIELD_TYPE_UNSIGNED:
 		switch (mem->type) {
@@ -1227,10 +1188,8 @@ mem_cast_explicit(struct Mem *mem, enum field_type type)
 int
 mem_cast_implicit(struct Mem *mem, enum field_type type)
 {
-	if (mem->type == MEM_TYPE_NULL) {
-		mem->field_type = type;
+	if (mem->type == MEM_TYPE_NULL)
 		return 0;
-	}
 	switch (type) {
 	case FIELD_TYPE_UNSIGNED:
 		if (mem->type == MEM_TYPE_UINT)
@@ -1301,12 +1260,10 @@ mem_cast_implicit_number(struct Mem *mem, enum field_type type)
 	case FIELD_TYPE_UNSIGNED:
 		switch (mem->type) {
 		case MEM_TYPE_UINT:
-			mem->field_type = FIELD_TYPE_UNSIGNED;
 			return 0;
 		case MEM_TYPE_INT:
 			mem->u.u = 0;
 			mem->type = MEM_TYPE_UINT;
-			mem->field_type = FIELD_TYPE_UNSIGNED;
 			return -1;
 		case MEM_TYPE_DOUBLE:
 			return double_to_uint_forced(mem);
@@ -1330,7 +1287,6 @@ mem_cast_implicit_number(struct Mem *mem, enum field_type type)
 		switch (mem->type) {
 		case MEM_TYPE_UINT:
 		case MEM_TYPE_INT:
-			mem->field_type = FIELD_TYPE_INTEGER;
 			return 0;
 		case MEM_TYPE_DOUBLE:
 			return double_to_int_forced(mem);
@@ -1487,7 +1443,6 @@ mem_copy(struct Mem *to, const struct Mem *from)
 	to->u = from->u;
 	to->type = from->type;
 	to->flags = from->flags;
-	to->field_type = from->field_type;
 	to->n = from->n;
 	to->z = from->z;
 	if (!mem_is_bytes(to))
@@ -1514,7 +1469,6 @@ mem_copy_as_ephemeral(struct Mem *to, const struct Mem *from)
 	to->u = from->u;
 	to->type = from->type;
 	to->flags = from->flags;
-	to->field_type = from->field_type;
 	to->n = from->n;
 	to->z = from->z;
 	if (!mem_is_bytes(to))
@@ -1537,30 +1491,13 @@ mem_move(struct Mem *to, struct Mem *from)
 	from->zMalloc = NULL;
 }
 
-static bool
-try_return_null(const struct Mem *a, const struct Mem *b, struct Mem *result,
-		enum field_type type)
-{
-	mem_clear(result);
-	result->field_type = type;
-	return ((a->type | b->type) & MEM_TYPE_NULL) != 0;
-}
-
 int
 mem_concat(struct Mem *a, struct Mem *b, struct Mem *result)
 {
-	assert(result != b);
-	if (a != result) {
-		if (try_return_null(a, b, result, FIELD_TYPE_STRING))
-			return 0;
-	} else {
-		if (((a->type | b->type) & MEM_TYPE_NULL) != 0) {
-			mem_clear(a);
-			result->field_type = FIELD_TYPE_STRING;
-			return 0;
-		}
+	if (mem_is_any_null(a, b)) {
+		mem_set_null(result);
+		return 0;
 	}
-
 	/* Concatenation operation can be applied only to strings and blobs. */
 	if (((b->type & (MEM_TYPE_STR | MEM_TYPE_BIN)) == 0)) {
 		diag_set(ClientError, ER_INCONSISTENT_TYPES,
@@ -1593,8 +1530,6 @@ mem_concat(struct Mem *a, struct Mem *b, struct Mem *result)
 
 	result->type = a->type;
 	result->flags = 0;
-	if (result->type == MEM_TYPE_BIN)
-		result->field_type = FIELD_TYPE_VARBINARY;
 	if (result != a)
 		memcpy(result->z, a->z, a->n);
 	memcpy(&result->z[a->n], b->z, b->n);
@@ -1607,7 +1542,6 @@ mem_add(const struct Mem *left, const struct Mem *right, struct Mem *result)
 {
 	if (mem_is_any_null(left, right)) {
 		mem_set_null(result);
-		result->field_type = FIELD_TYPE_NUMBER;
 		return 0;
 	}
 	if (!mem_is_num(right)) {
@@ -1644,7 +1578,6 @@ mem_sub(const struct Mem *left, const struct Mem *right, struct Mem *result)
 {
 	if (mem_is_any_null(left, right)) {
 		mem_set_null(result);
-		result->field_type = FIELD_TYPE_NUMBER;
 		return 0;
 	}
 	if (!mem_is_num(right)) {
@@ -1681,7 +1614,6 @@ mem_mul(const struct Mem *left, const struct Mem *right, struct Mem *result)
 {
 	if (mem_is_any_null(left, right)) {
 		mem_set_null(result);
-		result->field_type = FIELD_TYPE_NUMBER;
 		return 0;
 	}
 	if (!mem_is_num(right)) {
@@ -1718,7 +1650,6 @@ mem_div(const struct Mem *left, const struct Mem *right, struct Mem *result)
 {
 	if (mem_is_any_null(left, right)) {
 		mem_set_null(result);
-		result->field_type = FIELD_TYPE_NUMBER;
 		return 0;
 	}
 	if (!mem_is_num(right)) {
@@ -1764,7 +1695,6 @@ mem_rem(const struct Mem *left, const struct Mem *right, struct Mem *result)
 {
 	if (mem_is_any_null(left, right)) {
 		mem_set_null(result);
-		result->field_type = FIELD_TYPE_INTEGER;
 		return 0;
 	}
 	if (!mem_is_int(right)) {
@@ -1797,7 +1727,6 @@ mem_bit_and(const struct Mem *left, const struct Mem *right, struct Mem *result)
 {
 	if (mem_is_any_null(left, right)) {
 		mem_set_null(result);
-		result->field_type = FIELD_TYPE_UNSIGNED;
 		return 0;
 	}
 	if (right->type != MEM_TYPE_UINT) {
@@ -1819,7 +1748,6 @@ mem_bit_or(const struct Mem *left, const struct Mem *right, struct Mem *result)
 {
 	if (mem_is_any_null(left, right)) {
 		mem_set_null(result);
-		result->field_type = FIELD_TYPE_UNSIGNED;
 		return 0;
 	}
 	if (right->type != MEM_TYPE_UINT) {
@@ -1842,7 +1770,6 @@ mem_shift_left(const struct Mem *left, const struct Mem *right,
 {
 	if (mem_is_any_null(left, right)) {
 		mem_set_null(result);
-		result->field_type = FIELD_TYPE_UNSIGNED;
 		return 0;
 	}
 	if (right->type != MEM_TYPE_UINT) {
@@ -1865,7 +1792,6 @@ mem_shift_right(const struct Mem *left, const struct Mem *right,
 {
 	if (mem_is_any_null(left, right)) {
 		mem_set_null(result);
-		result->field_type = FIELD_TYPE_UNSIGNED;
 		return 0;
 	}
 	if (right->type != MEM_TYPE_UINT) {
@@ -1887,7 +1813,6 @@ mem_bit_not(const struct Mem *mem, struct Mem *result)
 {
 	if (mem_is_null(mem)) {
 		mem_set_null(result);
-		result->field_type = FIELD_TYPE_UNSIGNED;
 		return 0;
 	}
 	if (mem->type != MEM_TYPE_UINT) {
@@ -2557,7 +2482,6 @@ sql_vdbemem_finalize(struct Mem *mem, struct func *func)
 	t.type = MEM_TYPE_NULL;
 	assert(t.flags == 0);
 	t.db = mem->db;
-	t.field_type = field_type_MAX;
 	ctx.pOut = &t;
 	ctx.pMem = mem;
 	ctx.func = func;
@@ -2604,7 +2528,6 @@ mem_from_mp_ephemeral(struct Mem *mem, const char *buf, uint32_t *len)
 		mem->n = buf - mem->z;
 		mem->type = MEM_TYPE_ARRAY;
 		mem->flags = MEM_Ephem;
-		mem->field_type = FIELD_TYPE_ARRAY;
 		break;
 	}
 	case MP_MAP: {
@@ -2613,7 +2536,6 @@ mem_from_mp_ephemeral(struct Mem *mem, const char *buf, uint32_t *len)
 		mem->n = buf - mem->z;
 		mem->type = MEM_TYPE_MAP;
 		mem->flags = MEM_Ephem;
-		mem->field_type = FIELD_TYPE_MAP;
 		break;
 	}
 	case MP_EXT: {
@@ -2630,7 +2552,6 @@ mem_from_mp_ephemeral(struct Mem *mem, const char *buf, uint32_t *len)
 			}
 			mem->type = MEM_TYPE_UUID;
 			mem->flags = 0;
-			mem->field_type = FIELD_TYPE_UUID;
 			break;
 		}
 		buf += size;
@@ -2638,21 +2559,18 @@ mem_from_mp_ephemeral(struct Mem *mem, const char *buf, uint32_t *len)
 		mem->n = buf - svp;
 		mem->type = MEM_TYPE_BIN;
 		mem->flags = MEM_Ephem;
-		mem->field_type = FIELD_TYPE_VARBINARY;
 		break;
 	}
 	case MP_NIL: {
 		mp_decode_nil(&buf);
 		mem->type = MEM_TYPE_NULL;
 		mem->flags = 0;
-		mem->field_type = field_type_MAX;
 		break;
 	}
 	case MP_BOOL: {
 		mem->u.b = mp_decode_bool(&buf);
 		mem->type = MEM_TYPE_BOOL;
 		mem->flags = 0;
-		mem->field_type = FIELD_TYPE_BOOLEAN;
 		break;
 	}
 	case MP_UINT: {
@@ -2660,14 +2578,12 @@ mem_from_mp_ephemeral(struct Mem *mem, const char *buf, uint32_t *len)
 		mem->u.u = v;
 		mem->type = MEM_TYPE_UINT;
 		mem->flags = 0;
-		mem->field_type = FIELD_TYPE_INTEGER;
 		break;
 	}
 	case MP_INT: {
 		mem->u.i = mp_decode_int(&buf);
 		mem->type = MEM_TYPE_INT;
 		mem->flags = 0;
-		mem->field_type = FIELD_TYPE_INTEGER;
 		break;
 	}
 	case MP_STR: {
@@ -2675,7 +2591,6 @@ mem_from_mp_ephemeral(struct Mem *mem, const char *buf, uint32_t *len)
 		mem->n = (int) mp_decode_strl(&buf);
 		mem->type = MEM_TYPE_STR;
 		mem->flags = MEM_Ephem;
-		mem->field_type = FIELD_TYPE_STRING;
 install_blob:
 		mem->z = (char *)buf;
 		buf += mem->n;
@@ -2686,7 +2601,6 @@ install_blob:
 		mem->n = (int) mp_decode_binl(&buf);
 		mem->type = MEM_TYPE_BIN;
 		mem->flags = MEM_Ephem;
-		mem->field_type = FIELD_TYPE_VARBINARY;
 		goto install_blob;
 	}
 	case MP_FLOAT: {
@@ -2694,11 +2608,9 @@ install_blob:
 		if (sqlIsNaN(mem->u.r)) {
 			mem->type = MEM_TYPE_NULL;
 			mem->flags = 0;
-			mem->field_type = FIELD_TYPE_DOUBLE;
 		} else {
 			mem->type = MEM_TYPE_DOUBLE;
 			mem->flags = 0;
-			mem->field_type = FIELD_TYPE_DOUBLE;
 		}
 		break;
 	}
@@ -2707,11 +2619,9 @@ install_blob:
 		if (sqlIsNaN(mem->u.r)) {
 			mem->type = MEM_TYPE_NULL;
 			mem->flags = 0;
-			mem->field_type = FIELD_TYPE_DOUBLE;
 		} else {
 			mem->type = MEM_TYPE_DOUBLE;
 			mem->flags = 0;
-			mem->field_type = FIELD_TYPE_DOUBLE;
 		}
 		break;
 	}
diff --git a/src/box/sql/mem.h b/src/box/sql/mem.h
index 9681fc736..958907aaf 100644
--- a/src/box/sql/mem.h
+++ b/src/box/sql/mem.h
@@ -79,13 +79,6 @@ struct Mem {
 	/** Type of the value this MEM contains. */
 	enum mem_type type;
 	u32 flags;		/* Some combination of MEM_Null, MEM_Str, MEM_Dyn, etc. */
-	/**
-	 * If value is fetched from tuple, then this property
-	 * contains type of corresponding space's field. If it's
-	 * value field_type_MAX then we can rely on on format
-	 * (msgpack) type which is represented by 'flags'.
-	 */
-	enum field_type field_type;
 	int n;			/* size (in bytes) of string value, excluding trailing '\0' */
 	char *z;		/* String or BLOB value */
 	/* ShallowCopy only needs to copy the information above */
diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
index 14caa6f82..db5f96b62 100644
--- a/src/box/sql/vdbe.c
+++ b/src/box/sql/vdbe.c
@@ -277,7 +277,6 @@ vdbe_prepare_null_out(struct Vdbe *v, int n)
 	struct Mem *out = &v->aMem[n];
 	memAboutToChange(v, out);
 	mem_set_null(out);
-	out->field_type = field_type_MAX;
 	return out;
 }
 
@@ -1354,7 +1353,7 @@ case OP_BitAnd: {               /* same as TK_BITAND, in1, in2, out3 */
 	pOut = &aMem[pOp->p3];
 	if (mem_bit_and(pIn2, pIn1, pOut) != 0)
 		goto abort_due_to_error;
-	assert(pOut->field_type == FIELD_TYPE_UNSIGNED);
+	assert(pOut->type == MEM_TYPE_UINT || pOut->type == MEM_TYPE_NULL);
 	break;
 }
 
@@ -1371,7 +1370,7 @@ case OP_BitOr: {                /* same as TK_BITOR, in1, in2, out3 */
 	pOut = &aMem[pOp->p3];
 	if (mem_bit_or(pIn2, pIn1, pOut) != 0)
 		goto abort_due_to_error;
-	assert(pOut->field_type == FIELD_TYPE_UNSIGNED);
+	assert(pOut->type == MEM_TYPE_UINT || pOut->type == MEM_TYPE_NULL);
 	break;
 }
 
@@ -1389,7 +1388,7 @@ case OP_ShiftLeft: {            /* same as TK_LSHIFT, in1, in2, out3 */
 	pOut = &aMem[pOp->p3];
 	if (mem_shift_left(pIn2, pIn1, pOut) != 0)
 		goto abort_due_to_error;
-	assert(pOut->field_type == FIELD_TYPE_UNSIGNED);
+	assert(pOut->type == MEM_TYPE_UINT || pOut->type == MEM_TYPE_NULL);
 	break;
 }
 
@@ -1407,7 +1406,7 @@ case OP_ShiftRight: {           /* same as TK_RSHIFT, in1, in2, out3 */
 	pOut = &aMem[pOp->p3];
 	if (mem_shift_right(pIn2, pIn1, pOut) != 0)
 		goto abort_due_to_error;
-	assert(pOut->field_type == FIELD_TYPE_UNSIGNED);
+	assert(pOut->type == MEM_TYPE_UINT || pOut->type == MEM_TYPE_NULL);
 	break;
 }
 
@@ -1465,13 +1464,6 @@ case OP_Cast: {                  /* in1 */
 	if (ExpandBlob(pIn1) != 0)
 		goto abort_due_to_error;
 	rc = mem_cast_explicit(pIn1, pOp->p2);
-	/*
-	 * SCALAR is not type itself, but rather an aggregation
-	 * of types. Hence, cast to this type shouldn't change
-	 * original type of argument.
-	 */
-	if (pOp->p2 != FIELD_TYPE_SCALAR)
-		pIn1->field_type = pOp->p2;
 	UPDATE_MAX_BLOBSIZE(pIn1);
 	if (rc == 0)
 		break;
@@ -1809,7 +1801,6 @@ case OP_Or: {             /* same as TK_OR, in1, in2, out3 */
 case OP_Not: {                /* same as TK_NOT, in1, out2 */
 	pIn1 = &aMem[pOp->p1];
 	pOut = vdbe_prepare_null_out(p, pOp->p2);
-	pOut->field_type = FIELD_TYPE_BOOLEAN;
 	if (!mem_is_null(pIn1)) {
 		if (!mem_is_bool(pIn1)) {
 			diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
@@ -1979,11 +1970,8 @@ case OP_Column: {
 		}
 		pC->cacheStatus = p->cacheCtr;
 	}
-	enum field_type field_type = field_type_MAX;
-	if (pC->eCurType == CURTYPE_TARANTOOL)
-		field_type = pC->uc.pCursor->space->def->fields[p2].type;
-	else if (pC->eCurType == CURTYPE_SORTER)
-		field_type = vdbe_sorter_get_field_type(pC->uc.pSorter, p2);
+	assert(pC->eCurType == CURTYPE_TARANTOOL ||
+	       pC->eCurType == CURTYPE_PSEUDO);
 	struct Mem *default_val_mem =
 		pOp->p4type == P4_MEM ? pOp->p4.pMem : NULL;
 	if (vdbe_field_ref_fetch(&pC->field_ref, p2, pDest) != 0)
@@ -1994,7 +1982,6 @@ case OP_Column: {
 	    default_val_mem != NULL) {
 		mem_copy_as_ephemeral(pDest, default_val_mem);
 	}
-	pDest->field_type = field_type;
 op_column_out:
 	REGISTER_TRACE(p, pOp->p3, pDest);
 	break;
diff --git a/src/box/sql/vdbeInt.h b/src/box/sql/vdbeInt.h
index b4ad8b774..cfe743b94 100644
--- a/src/box/sql/vdbeInt.h
+++ b/src/box/sql/vdbeInt.h
@@ -341,9 +341,6 @@ int sqlVdbeFrameRestore(VdbeFrame *);
 int sqlVdbeSorterInit(struct sql *db, struct VdbeCursor *cursor);
 void sqlVdbeSorterReset(sql *, VdbeSorter *);
 
-enum field_type
-vdbe_sorter_get_field_type(struct VdbeSorter *sorter, uint32_t field_no);
-
 void sqlVdbeSorterClose(sql *, VdbeCursor *);
 int sqlVdbeSorterRowkey(const VdbeCursor *, Mem *);
 int sqlVdbeSorterNext(sql *, const VdbeCursor *, int *);
diff --git a/src/box/sql/vdbesort.c b/src/box/sql/vdbesort.c
index 3da425be4..aadafeabd 100644
--- a/src/box/sql/vdbesort.c
+++ b/src/box/sql/vdbesort.c
@@ -969,12 +969,6 @@ sqlVdbeSorterReset(sql * db, VdbeSorter * pSorter)
 	pSorter->pUnpacked = 0;
 }
 
-enum field_type
-vdbe_sorter_get_field_type(struct VdbeSorter *sorter, uint32_t field_no)
-{
-	return sorter->key_def->parts[field_no].type;
-}
-
 /*
  * Free any cursor components allocated by sqlVdbeSorterXXX routines.
  */
diff --git a/test/sql-tap/cast.test.lua b/test/sql-tap/cast.test.lua
index 927114772..4f2bff93e 100755
--- a/test/sql-tap/cast.test.lua
+++ b/test/sql-tap/cast.test.lua
@@ -120,7 +120,7 @@ test:do_execsql_test(
         SELECT typeof(NULL)
     ]], {
         -- <cast-1.12>
-        "boolean"
+        "NULL"
         -- </cast-1.12>
     })
 
@@ -140,7 +140,7 @@ test:do_execsql_test(
         SELECT typeof(CAST(NULL AS text))
     ]], {
         -- <cast-1.14>
-        "string"
+        "NULL"
         -- </cast-1.14>
     })
 
@@ -160,7 +160,7 @@ test:do_execsql_test(
         SELECT typeof(CAST(NULL AS NUMBER))
     ]], {
         -- <cast-1.16>
-        "number"
+        "NULL"
         -- </cast-1.16>
     })
 
@@ -180,7 +180,7 @@ test:do_execsql_test(
         SELECT typeof(CAST(NULL AS SCALAR))
     ]], {
         -- <cast-1.18>
-        "boolean"
+        "NULL"
         -- </cast-1.18>
     })
 
@@ -200,7 +200,7 @@ test:do_execsql_test(
         SELECT typeof(CAST(NULL AS integer))
     ]], {
         -- <cast-1.20>
-        "integer"
+        "NULL"
         -- </cast-1.20>
     })
 
@@ -511,7 +511,7 @@ test:do_execsql_test(
         SELECT typeof(CAST(null AS NUMBER))
     ]], {
         -- <case-1.61>
-        "number"
+        "NULL"
         -- </case-1.61>
     })
 
@@ -531,7 +531,7 @@ test:do_execsql_test(
         SELECT typeof(CAST(1 AS NUMBER))
     ]], {
         -- <case-1.63>
-        "number"
+        "integer"
         -- </case-1.63>
     })
 
@@ -551,7 +551,7 @@ test:do_execsql_test(
         SELECT typeof(CAST('1' AS NUMBER))
     ]], {
         -- <case-1.65>
-        "number"
+        "integer"
         -- </case-1.65>
     })
 
diff --git a/test/sql-tap/default.test.lua b/test/sql-tap/default.test.lua
index 9c15220eb..59dc2a2a1 100755
--- a/test/sql-tap/default.test.lua
+++ b/test/sql-tap/default.test.lua
@@ -115,7 +115,7 @@ test:do_execsql_test(
 	g, typeof(g) FROM t3;
 	]], {
 	-- <default-3.1>
-	1, "integer", 5, "integer", "row1", "string", 5.25, "number", 8.67, "number", "321", "string", 432, "integer"
+	1, "integer", 5, "integer", "row1", "string", 5.25, "double", 8.67, "double", "321", "string", 432, "integer"
 	-- </default-3.1>
 })
 
diff --git a/test/sql-tap/func.test.lua b/test/sql-tap/func.test.lua
index df55ff9cf..4cc722d1e 100755
--- a/test/sql-tap/func.test.lua
+++ b/test/sql-tap/func.test.lua
@@ -1814,7 +1814,7 @@ test:do_execsql_test(
         SELECT typeof(replace('This is the main test string', NULL, 'ALT'));
     ]], {
         -- <func-21.3>
-        "boolean"
+        "NULL"
         -- </func-21.3>
     })
 
@@ -1824,7 +1824,7 @@ test:do_execsql_test(
         SELECT typeof(replace(NULL, 'main', 'ALT'));
     ]], {
         -- <func-21.4>
-        "boolean"
+        "NULL"
         -- </func-21.4>
     })
 
@@ -1834,7 +1834,7 @@ test:do_execsql_test(
         SELECT typeof(replace('This is the main test string', 'main', NULL));
     ]], {
         -- <func-21.5>
-        "boolean"
+        "NULL"
         -- </func-21.5>
     })
 
@@ -2055,7 +2055,7 @@ test:do_execsql_test(
         SELECT typeof(trim(NULL));
     ]], {
         -- <func-22.20>
-        "boolean"
+        "NULL"
         -- </func-22.20>
     })
 
@@ -2065,7 +2065,7 @@ test:do_execsql_test(
         SELECT typeof(TRIM('xyz' FROM NULL));
     ]], {
         -- <func-22.21>
-        "boolean"
+        "NULL"
         -- </func-22.21>
     })
 
@@ -2075,7 +2075,7 @@ test:do_execsql_test(
         SELECT typeof(TRIM(NULL FROM 'hello'));
     ]], {
         -- <func-22.22>
-        "boolean"
+        "NULL"
         -- </func-22.22>
     })
 
diff --git a/test/sql-tap/func5.test.lua b/test/sql-tap/func5.test.lua
index f07fc5fd9..44755b1f7 100755
--- a/test/sql-tap/func5.test.lua
+++ b/test/sql-tap/func5.test.lua
@@ -1,6 +1,6 @@
 #!/usr/bin/env tarantool
 local test = require("sqltester")
-test:plan(29)
+test:plan(38)
 
 --!./tcltestrunner.lua
 -- 2010 August 27
@@ -371,4 +371,77 @@ test:do_execsql_test(
         111
     })
 
+-- gh-5956: typeof(NULL) now returns 'NULL'.
+test:do_execsql_test(
+    "func-8.1",
+    [[
+        SELECT typeof(CAST(NULL AS UUID));
+    ]], {
+        'NULL'
+    })
+
+test:do_execsql_test(
+    "func-8.2",
+    [[
+        SELECT typeof(CAST(NULL AS SCALAR));
+    ]], {
+        'NULL'
+    })
+
+test:do_execsql_test(
+    "func-8.3",
+    [[
+        SELECT typeof(CAST(NULL AS BOOLEAN));
+    ]], {
+        'NULL'
+    })
+
+test:do_execsql_test(
+    "func-8.4",
+    [[
+        SELECT typeof(CAST(NULL AS INTEGER));
+    ]], {
+        'NULL'
+    })
+
+test:do_execsql_test(
+    "func-8.5",
+    [[
+        SELECT typeof(CAST(NULL AS UNSIGNED));
+    ]], {
+        'NULL'
+    })
+
+test:do_execsql_test(
+    "func-8.6",
+    [[
+        SELECT typeof(CAST(NULL AS DOUBLE));
+    ]], {
+        'NULL'
+    })
+
+test:do_execsql_test(
+    "func-8.7",
+    [[
+        SELECT typeof(CAST(NULL AS NUMBER));
+    ]], {
+        'NULL'
+    })
+
+test:do_execsql_test(
+    "func-8.8",
+    [[
+        SELECT typeof(CAST(NULL AS STRING));
+    ]], {
+        'NULL'
+    })
+
+test:do_execsql_test(
+    "func-8.9",
+    [[
+        SELECT typeof(CAST(NULL AS VARBINARY));
+    ]], {
+        'NULL'
+    })
+
 test:finish_test()
diff --git a/test/sql-tap/select7.test.lua b/test/sql-tap/select7.test.lua
index 815f9110b..47e7240ed 100755
--- a/test/sql-tap/select7.test.lua
+++ b/test/sql-tap/select7.test.lua
@@ -235,7 +235,7 @@ test:do_execsql_test(
         SELECT a=0, typeof(a) FROM t4
     ]], {
         -- <select7-7.5>
-        false, "number", false, "number"
+        false, "double", false, "double"
         -- </select7-7.5>
     })
 
@@ -245,7 +245,7 @@ test:do_execsql_test(
         SELECT a=0, typeof(a) FROM t4 GROUP BY a
     ]], {
         -- <select7-7.6>
-        false, "number", false, "number"
+        false, "double", false, "double"
         -- </select7-7.6>
     })
 
diff --git a/test/sql-tap/table.test.lua b/test/sql-tap/table.test.lua
index ecd9adcd8..4592909ff 100755
--- a/test/sql-tap/table.test.lua
+++ b/test/sql-tap/table.test.lua
@@ -906,7 +906,7 @@ test:do_execsql_test(
         FROM t7 LIMIT 1;
     ]], {
         -- <table-11.1>
-        "integer", "number", "string", "string", "boolean", "boolean", "string", "string"
+        "integer", "NULL", "NULL", "NULL", "NULL", "NULL", "NULL", "NULL"
         -- </table-11.1>
     })
 
@@ -917,7 +917,7 @@ test:do_execsql_test(
         FROM t7 LIMIT 1;
     ]], {
         -- <table-11.2>
-        "number", "string", "number", "string"
+        "NULL", "NULL", "NULL", "NULL"
         -- </table-11.2>
     })
 
diff --git a/test/sql-tap/tkt-3998683a16.test.lua b/test/sql-tap/tkt-3998683a16.test.lua
index 44ce7087a..81c7806ad 100755
--- a/test/sql-tap/tkt-3998683a16.test.lua
+++ b/test/sql-tap/tkt-3998683a16.test.lua
@@ -36,7 +36,6 @@ test:do_test(
         ]]
     end, {
         -- <tkt-3998683a16.1>
-        1, 2, 3, 4, 5, 6
         -- </tkt-3998683a16.1>
     })
 
diff --git a/test/sql/boolean.result b/test/sql/boolean.result
index 6e7ad179f..d027a56e3 100644
--- a/test/sql/boolean.result
+++ b/test/sql/boolean.result
@@ -331,8 +331,8 @@ SELECT typeof(a) FROM t0;
  |   rows:
  |   - ['boolean']
  |   - ['boolean']
- |   - ['boolean']
- |   - ['boolean']
+ |   - ['NULL']
+ |   - ['NULL']
  | ...
 
 -- Check BOOLEAN as argument of aggregate function.
diff --git a/test/sql/gh-4697-scalar-bool-sort-cmp.result b/test/sql/gh-4697-scalar-bool-sort-cmp.result
index 6f7fbd62b..e69c625bd 100644
--- a/test/sql/gh-4697-scalar-bool-sort-cmp.result
+++ b/test/sql/gh-4697-scalar-bool-sort-cmp.result
@@ -33,7 +33,7 @@ SELECT s2, typeof(s2) FROM test ORDER BY s2;
  |   - name: COLUMN_1
  |     type: string
  |   rows:
- |   - [null, 'boolean']
+ |   - [null, 'NULL']
  |   - [true, 'boolean']
  |   - [1, 'integer']
  |   - [1.1, 'double']
@@ -46,7 +46,7 @@ SELECT s3, typeof(s3) FROM test ORDER BY s3;
  |   - name: COLUMN_1
  |     type: string
  |   rows:
- |   - [null, 'boolean']
+ |   - [null, 'NULL']
  |   - [true, 'boolean']
  |   - [1, 'integer']
  |   - [1.1, 'double']
diff --git a/test/sql/types.result b/test/sql/types.result
index 07d5b46e4..e8ebdae84 100644
--- a/test/sql/types.result
+++ b/test/sql/types.result
@@ -1481,7 +1481,7 @@ box.execute("SELECT typeof(a), typeof(s) FROM t;")
     type: string
   rows:
   - ['integer', 'integer']
-  - ['integer', 'boolean']
+  - ['NULL', 'NULL']
 ...
 box.execute('CREATE TABLE t1 (id INTEGER PRIMARY KEY, a INTEGER, b INTEGER)')
 ---
@@ -1497,7 +1497,7 @@ box.execute('SELECT typeof(a & b) FROM t1;')
   - name: COLUMN_1
     type: string
   rows:
-  - ['unsigned']
+  - ['NULL']
 ...
 box.execute('SELECT typeof(a), typeof(b), typeof(a & b) FROM t1')
 ---
@@ -1509,7 +1509,7 @@ box.execute('SELECT typeof(a), typeof(b), typeof(a & b) FROM t1')
   - name: COLUMN_3
     type: string
   rows:
-  - ['integer', 'integer', 'unsigned']
+  - ['NULL', 'NULL', 'NULL']
 ...
 box.execute("SELECT typeof(CAST(0 AS UNSIGNED));")
 ---
@@ -1517,7 +1517,7 @@ box.execute("SELECT typeof(CAST(0 AS UNSIGNED));")
   - name: COLUMN_1
     type: string
   rows:
-  - ['unsigned']
+  - ['integer']
 ...
 box.space.T:drop()
 ---
-- 
2.25.1


^ permalink raw reply	[flat|nested] 14+ messages in thread

* [Tarantool-patches] [PATCH v1 2/7] sql: re-introduce NUMBER and SCALAR meta-types
  2021-08-13  3:12 [Tarantool-patches] [PATCH v1 0/7] Rework SCALAR and NUMBER types in SQL Mergen Imeev via Tarantool-patches
  2021-08-13  3:12 ` [Tarantool-patches] [PATCH v1 1/7] sql: remove enum field_type from struct Mem Mergen Imeev via Tarantool-patches
@ 2021-08-13  3:12 ` Mergen Imeev via Tarantool-patches
  2021-08-13  3:12 ` [Tarantool-patches] [PATCH v1 3/7] sql: disallow implicit cast from NUMBER and SCALAR Mergen Imeev via Tarantool-patches
                   ` (6 subsequent siblings)
  8 siblings, 0 replies; 14+ messages in thread
From: Mergen Imeev via Tarantool-patches @ 2021-08-13  3:12 UTC (permalink / raw)
  To: vdavydov; +Cc: tarantool-patches

This patch re-introduces the NUMBER and SCALAR meta-types. They were
introduced some time ago, however there were some problems with how they
worked and some properties of these meta-types were not implemented.

New properties of meta-type SCALAR in SQL:
1) typeof() with SCALAR argument returns "scalar";
2) Any value of any scalar type can be implicitly cast to SCALAR;
3) SCALAR values can be compared with any value of any other scalar
   type;
4) SCALAR values cannot be implicitly cast to any other scalar type;
5) SCALAR values cannot participate in arithmetic or bitwise
   operations;
6) SCALAR values cannot be concatenated with other values.

New properties of meta-type NUMBER in SQL:
1) typeof() with NUMBER argument returns "number";
2) Any value of a numeric type can be implicitly cast to NUMBER;
3) NUMBER values can be implicitly cast to SCALAR;
4) NUMBER cannot be implicitly cast to any other numeric type;
5) NUMBER values cannot participate in arithmetic or bitwise
   operations;

This patch only applies 1) and 2) for both meta-types. All other
properties will be presented in the next patches.

Part of #6221
---
 src/box/sql/func.c                           |   4 +
 src/box/sql/mem.c                            | 135 +++++++++++++------
 src/box/sql/mem.h                            |   4 +
 src/box/sql/vdbe.c                           |  10 ++
 test/sql-tap/cast.test.lua                   |  14 +-
 test/sql-tap/default.test.lua                |   2 +-
 test/sql-tap/engine.cfg                      |   3 +
 test/sql-tap/in4.test.lua                    |   2 +-
 test/sql-tap/metatypes.test.lua              |  51 +++++++
 test/sql-tap/select7.test.lua                |   4 +-
 test/sql-tap/tkt-3998683a16.test.lua         |   1 +
 test/sql-tap/uuid.test.lua                   |   2 +-
 test/sql/boolean.result                      |  84 ++++++------
 test/sql/gh-4697-scalar-bool-sort-cmp.result |  12 +-
 test/sql/types.result                        |   8 +-
 15 files changed, 230 insertions(+), 106 deletions(-)
 create mode 100755 test/sql-tap/metatypes.test.lua

diff --git a/src/box/sql/func.c b/src/box/sql/func.c
index 5ac5c5e56..1551d3ef2 100644
--- a/src/box/sql/func.c
+++ b/src/box/sql/func.c
@@ -162,6 +162,10 @@ typeofFunc(sql_context * context, int NotUsed, sql_value ** argv)
 {
 	const char *z = 0;
 	UNUSED_PARAMETER(NotUsed);
+	if ((argv[0]->flags & MEM_Number) != 0)
+		return mem_set_str0_static(context->pOut, "number");
+	if ((argv[0]->flags & MEM_Scalar) != 0)
+		return mem_set_str0_static(context->pOut, "scalar");
 	switch (argv[0]->type) {
 	case MEM_TYPE_INT:
 	case MEM_TYPE_UINT:
diff --git a/src/box/sql/mem.c b/src/box/sql/mem.c
index 773ef4d40..41aa40fdb 100644
--- a/src/box/sql/mem.c
+++ b/src/box/sql/mem.c
@@ -116,21 +116,22 @@ const char *
 mem_str(const struct Mem *mem)
 {
 	char buf[STR_VALUE_MAX_LEN];
+	const char *type = mem_type_to_str(mem);
 	switch (mem->type) {
 	case MEM_TYPE_NULL:
 		return "NULL";
 	case MEM_TYPE_STR:
 		if (mem->n <= STR_VALUE_MAX_LEN)
-			return tt_sprintf("string('%.*s')", mem->n, mem->z);
-		return tt_sprintf("string('%.*s...)", STR_VALUE_MAX_LEN,
+			return tt_sprintf("%s('%.*s')", type, mem->n, mem->z);
+		return tt_sprintf("%s('%.*s...)", type, STR_VALUE_MAX_LEN,
 				  mem->z);
 	case MEM_TYPE_INT:
-		return tt_sprintf("integer(%lld)", mem->u.i);
+		return tt_sprintf("%s(%lld)", type, mem->u.i);
 	case MEM_TYPE_UINT:
-		return tt_sprintf("integer(%llu)", mem->u.u);
+		return tt_sprintf("%s(%llu)", type, mem->u.u);
 	case MEM_TYPE_DOUBLE:
 		sql_snprintf(STR_VALUE_MAX_LEN, buf, "%!.15g", mem->u.r);
-		return tt_sprintf("double(%s)", buf);
+		return tt_sprintf("%s(%s)", type, buf);
 	case MEM_TYPE_BIN: {
 		int len = MIN(mem->n, STR_VALUE_MAX_LEN / 2);
 		for (int i = 0; i < len; ++i) {
@@ -140,13 +141,12 @@ mem_str(const struct Mem *mem)
 			buf[2 * i + 1] = n < 10 ? ('0' + n) : ('A' + n - 10);
 		}
 		if (mem->n > len)
-			return tt_sprintf("varbinary(x'%.*s...)", len * 2, buf);
-		return tt_sprintf("varbinary(x'%.*s')", len * 2, buf);
+			return tt_sprintf("%s(x'%.*s...)", type, len * 2, buf);
+		return tt_sprintf("%s(x'%.*s')", type, len * 2, buf);
 	}
 	case MEM_TYPE_MAP:
 	case MEM_TYPE_ARRAY: {
 		const char *str = mp_str(mem->z);
-		const char *type = mem_type_to_str(mem);
 		uint32_t len = strlen(str);
 		uint32_t minlen = MIN(STR_VALUE_MAX_LEN, len);
 		memcpy(buf, str, minlen);
@@ -156,9 +156,9 @@ mem_str(const struct Mem *mem)
 	}
 	case MEM_TYPE_UUID:
 		tt_uuid_to_string(&mem->u.uuid, buf);
-		return tt_sprintf("uuid('%s')", buf);
+		return tt_sprintf("%s('%s')", type, buf);
 	case MEM_TYPE_BOOL:
-		return mem->u.b ? "boolean(TRUE)" : "boolean(FALSE)";
+		return tt_sprintf("%s(%s)", type, mem->u.b ? "TRUE" : "FALSE");
 	default:
 		return "unknown";
 	}
@@ -626,7 +626,7 @@ int_to_double(struct Mem *mem)
 		d = (double)mem->u.i;
 	mem->u.r = d;
 	mem->type = MEM_TYPE_DOUBLE;
-	assert(mem->flags == 0);
+	mem->flags = 0;
 	return 0;
 }
 
@@ -640,6 +640,7 @@ int_to_double_precise(struct Mem *mem)
 		return -1;
 	mem->u.r = d;
 	mem->type = MEM_TYPE_DOUBLE;
+	mem->flags = 0;
 	return 0;
 }
 
@@ -656,6 +657,7 @@ int_to_double_forced(struct Mem *mem)
 	double d = (double)i;
 	mem->u.r = d;
 	mem->type = MEM_TYPE_DOUBLE;
+	mem->flags = 0;
 	return CMP_OLD_NEW(i, d, int64_t);
 }
 
@@ -668,6 +670,7 @@ uint_to_double_precise(struct Mem *mem)
 	if (mem->u.u != (uint64_t)d)
 		return -1;
 	mem->u.r = d;
+	mem->flags = 0;
 	mem->type = MEM_TYPE_DOUBLE;
 	return 0;
 }
@@ -684,6 +687,7 @@ uint_to_double_forced(struct Mem *mem)
 	uint64_t u = mem->u.u;
 	double d = (double)u;
 	mem->u.r = d;
+	mem->flags = 0;
 	mem->type = MEM_TYPE_DOUBLE;
 	return CMP_OLD_NEW(u, d, uint64_t);
 }
@@ -708,6 +712,7 @@ str_to_str0(struct Mem *mem)
 		return -1;
 	mem->z[mem->n] = '\0';
 	mem->flags |= MEM_Term;
+	mem->flags &= ~MEM_Scalar;
 	return 0;
 }
 
@@ -716,7 +721,7 @@ str_to_bin(struct Mem *mem)
 {
 	assert(mem->type == MEM_TYPE_STR);
 	mem->type = MEM_TYPE_BIN;
-	mem->flags &= ~MEM_Term;
+	mem->flags &= ~(MEM_Term | MEM_Scalar);
 	return 0;
 }
 
@@ -765,6 +770,7 @@ bin_to_str(struct Mem *mem)
 	if (ExpandBlob(mem) != 0)
 		return -1;
 	mem->type = MEM_TYPE_STR;
+	mem->flags &= ~MEM_Scalar;
 	return 0;
 }
 
@@ -840,13 +846,13 @@ double_to_int(struct Mem *mem)
 	if (d <= -1.0 && d >= (double)INT64_MIN) {
 		mem->u.i = (int64_t)d;
 		mem->type = MEM_TYPE_INT;
-		assert(mem->flags == 0);
+		mem->flags = 0;
 		return 0;
 	}
 	if (d > -1.0 && d < (double)UINT64_MAX) {
 		mem->u.u = (uint64_t)d;
 		mem->type = MEM_TYPE_UINT;
-		assert(mem->flags == 0);
+		mem->flags = 0;
 		return 0;
 	}
 	return -1;
@@ -860,13 +866,13 @@ double_to_int_precise(struct Mem *mem)
 	if (d <= -1.0 && d >= (double)INT64_MIN && (double)(int64_t)d == d) {
 		mem->u.i = (int64_t)d;
 		mem->type = MEM_TYPE_INT;
-		assert(mem->flags == 0);
+		mem->flags = 0;
 		return 0;
 	}
 	if (d > -1.0 && d < (double)UINT64_MAX && (double)(uint64_t)d == d) {
 		mem->u.u = (uint64_t)d;
 		mem->type = MEM_TYPE_UINT;
-		assert(mem->flags == 0);
+		mem->flags = 0;
 		return 0;
 	}
 	return -1;
@@ -905,6 +911,7 @@ double_to_int_forced(struct Mem *mem)
 	}
 	mem->u.i = i;
 	mem->type = type;
+	mem->flags = 0;
 	return res;
 }
 
@@ -916,7 +923,7 @@ double_to_uint(struct Mem *mem)
 	if (d > -1.0 && d < (double)UINT64_MAX) {
 		mem->u.u = (uint64_t)d;
 		mem->type = MEM_TYPE_UINT;
-		assert(mem->flags == 0);
+		mem->flags = 0;
 		return 0;
 	}
 	return -1;
@@ -930,7 +937,7 @@ double_to_uint_precise(struct Mem *mem)
 	if (d > -1.0 && d < (double)UINT64_MAX && (double)(uint64_t)d == d) {
 		mem->u.u = (uint64_t)d;
 		mem->type = MEM_TYPE_UINT;
-		assert(mem->flags == 0);
+		mem->flags = 0;
 		return 0;
 	}
 	return -1;
@@ -960,6 +967,7 @@ double_to_uint_forced(struct Mem *mem)
 	}
 	mem->u.u = u;
 	mem->type = MEM_TYPE_UINT;
+	mem->flags = 0;
 	return res;
 }
 
@@ -1020,8 +1028,10 @@ int
 mem_to_int(struct Mem *mem)
 {
 	assert(mem->type < MEM_TYPE_INVALID);
-	if ((mem->type & (MEM_TYPE_INT | MEM_TYPE_UINT)) != 0)
+	if ((mem->type & (MEM_TYPE_INT | MEM_TYPE_UINT)) != 0) {
+		mem->flags = 0;
 		return 0;
+	}
 	if (mem->type == MEM_TYPE_STR)
 		return str_to_int(mem);
 	if (mem->type == MEM_TYPE_DOUBLE)
@@ -1033,8 +1043,10 @@ int
 mem_to_int_precise(struct Mem *mem)
 {
 	assert(mem->type < MEM_TYPE_INVALID);
-	if ((mem->type & (MEM_TYPE_INT | MEM_TYPE_UINT)) != 0)
+	if ((mem->type & (MEM_TYPE_INT | MEM_TYPE_UINT)) != 0) {
+		mem->flags = 0;
 		return 0;
+	}
 	if (mem->type == MEM_TYPE_STR)
 		return str_to_int(mem);
 	if (mem->type == MEM_TYPE_DOUBLE)
@@ -1046,8 +1058,10 @@ int
 mem_to_double(struct Mem *mem)
 {
 	assert(mem->type < MEM_TYPE_INVALID);
-	if (mem->type == MEM_TYPE_DOUBLE)
+	if (mem->type == MEM_TYPE_DOUBLE) {
+		mem->flags = 0;
 		return 0;
+	}
 	if ((mem->type & (MEM_TYPE_INT | MEM_TYPE_UINT)) != 0)
 		return int_to_double(mem);
 	if (mem->type == MEM_TYPE_STR)
@@ -1059,12 +1073,15 @@ int
 mem_to_number(struct Mem *mem)
 {
 	assert(mem->type < MEM_TYPE_INVALID);
-	if (mem_is_num(mem))
+	if (mem_is_num(mem)) {
+		mem->flags = MEM_Number;
 		return 0;
+	}
 	if (mem->type == MEM_TYPE_STR) {
-		if (str_to_int(mem) == 0)
-			return 0;
-		return str_to_double(mem);
+		if (str_to_int(mem) != 0 && str_to_double(mem) != 0)
+			return -1;
+		mem->flags = MEM_Number;
+		return 0;
 	}
 	return -1;
 }
@@ -1075,8 +1092,10 @@ mem_to_str0(struct Mem *mem)
 	assert(mem->type < MEM_TYPE_INVALID);
 	switch (mem->type) {
 	case MEM_TYPE_STR:
-		if ((mem->flags & MEM_Term) != 0)
+		if ((mem->flags & MEM_Term) != 0) {
+			mem->flags &= ~MEM_Scalar;
 			return 0;
+		}
 		return str_to_str0(mem);
 	case MEM_TYPE_INT:
 	case MEM_TYPE_UINT:
@@ -1104,6 +1123,7 @@ mem_to_str(struct Mem *mem)
 	assert(mem->type < MEM_TYPE_INVALID);
 	switch (mem->type) {
 	case MEM_TYPE_STR:
+		mem->flags &= ~MEM_Scalar;
 		return 0;
 	case MEM_TYPE_INT:
 	case MEM_TYPE_UINT:
@@ -1134,6 +1154,7 @@ mem_cast_explicit(struct Mem *mem, enum field_type type)
 	case FIELD_TYPE_UNSIGNED:
 		switch (mem->type) {
 		case MEM_TYPE_UINT:
+			mem->flags = 0;
 			return 0;
 		case MEM_TYPE_STR:
 			return str_to_uint(mem);
@@ -1151,6 +1172,7 @@ mem_cast_explicit(struct Mem *mem, enum field_type type)
 	case FIELD_TYPE_BOOLEAN:
 		switch (mem->type) {
 		case MEM_TYPE_BOOL:
+			mem->flags = 0;
 			return 0;
 		case MEM_TYPE_STR:
 			return str_to_bool(mem);
@@ -1160,16 +1182,20 @@ mem_cast_explicit(struct Mem *mem, enum field_type type)
 	case FIELD_TYPE_VARBINARY:
 		if (mem->type == MEM_TYPE_STR)
 			return str_to_bin(mem);
-		if (mem_is_bytes(mem))
+		if (mem_is_bytes(mem)) {
+			mem->flags &= ~MEM_Scalar;
 			return 0;
+		}
 		if (mem->type == MEM_TYPE_UUID)
 			return uuid_to_bin(mem);
 		return -1;
 	case FIELD_TYPE_NUMBER:
 		return mem_to_number(mem);
 	case FIELD_TYPE_UUID:
-		if (mem->type == MEM_TYPE_UUID)
+		if (mem->type == MEM_TYPE_UUID) {
+			mem->flags = 0;
 			return 0;
+		}
 		if (mem->type == MEM_TYPE_STR)
 			return str_to_uuid(mem);
 		if (mem->type == MEM_TYPE_BIN)
@@ -1178,6 +1204,8 @@ mem_cast_explicit(struct Mem *mem, enum field_type type)
 	case FIELD_TYPE_SCALAR:
 		if ((mem->type & (MEM_TYPE_MAP | MEM_TYPE_ARRAY)) != 0)
 			return -1;
+		mem->flags |= MEM_Scalar;
+		mem->flags &= ~MEM_Number;
 		return 0;
 	default:
 		break;
@@ -1192,42 +1220,55 @@ mem_cast_implicit(struct Mem *mem, enum field_type type)
 		return 0;
 	switch (type) {
 	case FIELD_TYPE_UNSIGNED:
-		if (mem->type == MEM_TYPE_UINT)
+		if (mem->type == MEM_TYPE_UINT) {
+			mem->flags = 0;
 			return 0;
+		}
 		if (mem->type == MEM_TYPE_DOUBLE)
 			return double_to_uint_precise(mem);
 		return -1;
 	case FIELD_TYPE_STRING:
-		if (mem->type == MEM_TYPE_STR)
+		if (mem->type == MEM_TYPE_STR) {
+			mem->flags &= ~MEM_Scalar;
 			return 0;
+		}
 		return -1;
 	case FIELD_TYPE_DOUBLE:
-		if (mem->type == MEM_TYPE_DOUBLE)
+		if (mem->type == MEM_TYPE_DOUBLE) {
+			mem->flags = 0;
 			return 0;
+		}
 		if (mem->type == MEM_TYPE_INT)
 			return int_to_double_precise(mem);
 		if (mem->type == MEM_TYPE_UINT)
 			return uint_to_double_precise(mem);
 		return -1;
 	case FIELD_TYPE_INTEGER:
-		if ((mem->type & (MEM_TYPE_INT | MEM_TYPE_UINT)) != 0)
+		if ((mem->type & (MEM_TYPE_INT | MEM_TYPE_UINT)) != 0) {
+			mem->flags = 0;
 			return 0;
+		}
 		if (mem->type == MEM_TYPE_DOUBLE)
 			return double_to_int_precise(mem);
 		return -1;
 	case FIELD_TYPE_BOOLEAN:
-		if (mem->type == MEM_TYPE_BOOL)
+		if (mem->type == MEM_TYPE_BOOL) {
+			mem->flags = 0;
 			return 0;
+		}
 		return -1;
 	case FIELD_TYPE_VARBINARY:
 		if ((mem->type & (MEM_TYPE_BIN | MEM_TYPE_MAP |
-				  MEM_TYPE_ARRAY)) != 0)
+				  MEM_TYPE_ARRAY)) != 0) {
+			mem->flags &= ~MEM_Scalar;
 			return 0;
+		}
 		return -1;
 	case FIELD_TYPE_NUMBER:
-		if (mem_is_num(mem))
-			return 0;
-		return -1;
+		if (!mem_is_num(mem))
+			return -1;
+		mem->flags = MEM_Number;
+		return 0;
 	case FIELD_TYPE_MAP:
 		if (mem->type == MEM_TYPE_MAP)
 			return 0;
@@ -1239,11 +1280,14 @@ mem_cast_implicit(struct Mem *mem, enum field_type type)
 	case FIELD_TYPE_SCALAR:
 		if ((mem->type & (MEM_TYPE_MAP | MEM_TYPE_ARRAY)) != 0)
 			return -1;
+		mem->flags |= MEM_Scalar;
+		mem->flags &= ~MEM_Number;
 		return 0;
 	case FIELD_TYPE_UUID:
-		if (mem->type == MEM_TYPE_UUID)
-			return 0;
-		return -1;
+		if (mem->type != MEM_TYPE_UUID)
+			return -1;
+		mem->flags = 0;
+		return 0;
 	case FIELD_TYPE_ANY:
 		return 0;
 	default:
@@ -1260,9 +1304,11 @@ mem_cast_implicit_number(struct Mem *mem, enum field_type type)
 	case FIELD_TYPE_UNSIGNED:
 		switch (mem->type) {
 		case MEM_TYPE_UINT:
+			mem->flags = 0;
 			return 0;
 		case MEM_TYPE_INT:
 			mem->u.u = 0;
+			mem->flags = 0;
 			mem->type = MEM_TYPE_UINT;
 			return -1;
 		case MEM_TYPE_DOUBLE:
@@ -1278,6 +1324,7 @@ mem_cast_implicit_number(struct Mem *mem, enum field_type type)
 		case MEM_TYPE_UINT:
 			return uint_to_double_forced(mem);
 		case MEM_TYPE_DOUBLE:
+			mem->flags = 0;
 			return 0;
 		default:
 			unreachable();
@@ -1287,6 +1334,7 @@ mem_cast_implicit_number(struct Mem *mem, enum field_type type)
 		switch (mem->type) {
 		case MEM_TYPE_UINT:
 		case MEM_TYPE_INT:
+			mem->flags = 0;
 			return 0;
 		case MEM_TYPE_DOUBLE:
 			return double_to_int_forced(mem);
@@ -2070,15 +2118,18 @@ char *
 mem_type_to_str(const struct Mem *p)
 {
 	assert(p != NULL);
+	if ((p->flags & MEM_Scalar) != 0)
+		return "scalar";
+	if ((p->flags & MEM_Number) != 0)
+		return "number";
 	switch (p->type) {
 	case MEM_TYPE_NULL:
 		return "NULL";
 	case MEM_TYPE_STR:
 		return "string";
 	case MEM_TYPE_INT:
-		return "integer";
 	case MEM_TYPE_UINT:
-		return "unsigned";
+		return "integer";
 	case MEM_TYPE_DOUBLE:
 		return "double";
 	case MEM_TYPE_ARRAY:
diff --git a/src/box/sql/mem.h b/src/box/sql/mem.h
index 958907aaf..a5c9015cd 100644
--- a/src/box/sql/mem.h
+++ b/src/box/sql/mem.h
@@ -93,6 +93,10 @@ struct Mem {
 #endif
 };
 
+/** MEM is of NUMBER meta-type. */
+#define MEM_Number    0x0001
+/** MEM is of SCALAR meta-type. */
+#define MEM_Scalar    0x0002
 #define MEM_Cleared   0x0200	/* NULL set by OP_Null, not from data */
 
 /* Whenever Mem contains a valid string or blob representation, one of
diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
index db5f96b62..37a542fe8 100644
--- a/src/box/sql/vdbe.c
+++ b/src/box/sql/vdbe.c
@@ -1982,6 +1982,16 @@ case OP_Column: {
 	    default_val_mem != NULL) {
 		mem_copy_as_ephemeral(pDest, default_val_mem);
 	}
+	if (pDest->type == MEM_TYPE_NULL)
+		goto op_column_out;
+	enum field_type field_type = field_type_MAX;
+	/* Currently PSEUDO cursor does not have info about field types. */
+	if (pC->eCurType == CURTYPE_TARANTOOL)
+		field_type = pC->uc.pCursor->space->def->fields[p2].type;
+	if (field_type == FIELD_TYPE_SCALAR)
+		pDest->flags |= MEM_Scalar;
+	else if (field_type == FIELD_TYPE_NUMBER)
+		pDest->flags |= MEM_Number;
 op_column_out:
 	REGISTER_TRACE(p, pOp->p3, pDest);
 	break;
diff --git a/test/sql-tap/cast.test.lua b/test/sql-tap/cast.test.lua
index 4f2bff93e..e0295fd3b 100755
--- a/test/sql-tap/cast.test.lua
+++ b/test/sql-tap/cast.test.lua
@@ -90,7 +90,7 @@ test:do_execsql_test(
         SELECT typeof(CAST(x'616263' AS SCALAR))
     ]], {
         -- <cast-1.8>
-        "varbinary"
+        "scalar"
         -- </cast-1.8>
     })
 
@@ -280,7 +280,7 @@ test:do_execsql_test(
         SELECT typeof(CAST(123 AS SCALAR))
     ]], {
         -- <cast-1.28>
-        "integer"
+        "scalar"
         -- </cast-1.28>
     })
 
@@ -380,7 +380,7 @@ test:do_execsql_test(
         SELECT typeof(CAST(123.456 AS SCALAR))
     ]], {
         -- <cast-1.38>
-        "double"
+        "scalar"
         -- </cast-1.38>
     })
 
@@ -460,7 +460,7 @@ test:do_execsql_test(
         SELECT typeof(CAST('123abc' AS SCALAR))
     ]], {
         -- <cast-1.48>
-        "string"
+        "scalar"
         -- </cast-1.48>
     })
 
@@ -531,7 +531,7 @@ test:do_execsql_test(
         SELECT typeof(CAST(1 AS NUMBER))
     ]], {
         -- <case-1.63>
-        "integer"
+        "number"
         -- </case-1.63>
     })
 
@@ -551,7 +551,7 @@ test:do_execsql_test(
         SELECT typeof(CAST('1' AS NUMBER))
     ]], {
         -- <case-1.65>
-        "integer"
+        "number"
         -- </case-1.65>
     })
 
@@ -972,7 +972,7 @@ test:do_catchsql_test(
     [[
         SELECT CAST(CAST(1 AS NUMBER) AS BOOLEAN);
     ]], {
-        1, "Type mismatch: can not convert integer(1) to boolean"
+        1, "Type mismatch: can not convert number(1) to boolean"
     })
 
 -- Make sure that explicit cast from VARBINARY to numeric types throws an error.
diff --git a/test/sql-tap/default.test.lua b/test/sql-tap/default.test.lua
index 59dc2a2a1..9c15220eb 100755
--- a/test/sql-tap/default.test.lua
+++ b/test/sql-tap/default.test.lua
@@ -115,7 +115,7 @@ test:do_execsql_test(
 	g, typeof(g) FROM t3;
 	]], {
 	-- <default-3.1>
-	1, "integer", 5, "integer", "row1", "string", 5.25, "double", 8.67, "double", "321", "string", 432, "integer"
+	1, "integer", 5, "integer", "row1", "string", 5.25, "number", 8.67, "number", "321", "string", 432, "integer"
 	-- </default-3.1>
 })
 
diff --git a/test/sql-tap/engine.cfg b/test/sql-tap/engine.cfg
index 94b0bb1f6..820c72b00 100644
--- a/test/sql-tap/engine.cfg
+++ b/test/sql-tap/engine.cfg
@@ -23,6 +23,9 @@
     "gh-6164-uuid-follow-ups.test.lua": {
         "memtx": {"engine": "memtx"}
     },
+    "metatypes.test.lua": {
+        "memtx": {"engine": "memtx"}
+    },
     "gh-4077-iproto-execute-no-bind.test.lua": {},
     "*": {
         "memtx": {"engine": "memtx"},
diff --git a/test/sql-tap/in4.test.lua b/test/sql-tap/in4.test.lua
index aa6483697..9e11fac4f 100755
--- a/test/sql-tap/in4.test.lua
+++ b/test/sql-tap/in4.test.lua
@@ -153,7 +153,7 @@ test:do_catchsql_test(
         SELECT b FROM t2 WHERE a IN ('', '0.0.0', '2')
     ]], {
         -- <in4-2.8>
-        1, "Type mismatch: can not convert string('') to integer"
+        1, "Type mismatch: can not convert scalar('') to integer"
         -- </in4-2.8>
     })
 
diff --git a/test/sql-tap/metatypes.test.lua b/test/sql-tap/metatypes.test.lua
new file mode 100755
index 000000000..89802ec09
--- /dev/null
+++ b/test/sql-tap/metatypes.test.lua
@@ -0,0 +1,51 @@
+#!/usr/bin/env tarantool
+local test = require("sqltester")
+test:plan(3)
+
+-- Check that SCALAR and NUMBER meta-types works as intended.
+box.execute([[CREATE TABLE t (i INT PRIMARY KEY, s SCALAR, n NUMBER);]])
+
+--
+-- Check that implicit cast from numeric types to NUMBER and from scalar types
+-- to SCALAR works properly.
+--
+local uuid = [[CAST('11111111-1111-1111-1111-111111111111' AS UUID)]]
+test:do_execsql_test(
+    "metatypes-1.1",
+    [[
+        INSERT INTO t VALUES(1, 1, 1);
+        INSERT INTO t VALUES(2, 2.0, 2.0);
+        INSERT INTO t(i, s) VALUES(3, '3');
+        INSERT INTO t(i, s) VALUES(4, true);
+        INSERT INTO t(i, s) VALUES(5, x'35');
+        INSERT INTO t(i, s) VALUES(6, ]]..uuid..[[);
+        SELECT * FROM t;
+    ]], {
+        1,1,1,
+        2,2,2,
+        3,"3","",
+        4,true,"",
+        5,"5","",
+        6,require('uuid').fromstr('11111111-1111-1111-1111-111111111111'),""
+    })
+
+-- Check that typeof() returns right result.
+test:do_execsql_test(
+    "metatypes-1.2",
+    [[
+        SELECT typeof(s) FROM t;
+    ]], {
+        "scalar","scalar","scalar","scalar","scalar","scalar"
+    })
+
+test:do_execsql_test(
+    "metatypes-1.3",
+    [[
+        SELECT typeof(n) FROM t;
+    ]], {
+        "number","number","NULL","NULL","NULL","NULL"
+    })
+
+box.execute([[DROP TABLE t;]])
+
+test:finish_test()
diff --git a/test/sql-tap/select7.test.lua b/test/sql-tap/select7.test.lua
index 47e7240ed..815f9110b 100755
--- a/test/sql-tap/select7.test.lua
+++ b/test/sql-tap/select7.test.lua
@@ -235,7 +235,7 @@ test:do_execsql_test(
         SELECT a=0, typeof(a) FROM t4
     ]], {
         -- <select7-7.5>
-        false, "double", false, "double"
+        false, "number", false, "number"
         -- </select7-7.5>
     })
 
@@ -245,7 +245,7 @@ test:do_execsql_test(
         SELECT a=0, typeof(a) FROM t4 GROUP BY a
     ]], {
         -- <select7-7.6>
-        false, "double", false, "double"
+        false, "number", false, "number"
         -- </select7-7.6>
     })
 
diff --git a/test/sql-tap/tkt-3998683a16.test.lua b/test/sql-tap/tkt-3998683a16.test.lua
index 81c7806ad..44ce7087a 100755
--- a/test/sql-tap/tkt-3998683a16.test.lua
+++ b/test/sql-tap/tkt-3998683a16.test.lua
@@ -36,6 +36,7 @@ test:do_test(
         ]]
     end, {
         -- <tkt-3998683a16.1>
+        1, 2, 3, 4, 5, 6
         -- </tkt-3998683a16.1>
     })
 
diff --git a/test/sql-tap/uuid.test.lua b/test/sql-tap/uuid.test.lua
index bbe912c72..32203e9fb 100755
--- a/test/sql-tap/uuid.test.lua
+++ b/test/sql-tap/uuid.test.lua
@@ -1225,7 +1225,7 @@ test:do_execsql_test(
         INSERT INTO t14 VALUES ('11111111-1111-1111-1111-111111111111');
         SELECT typeof(s) FROM t14;
     ]], {
-        "boolean", "integer", "integer", "double", "string", "varbinary", "uuid"
+        "scalar", "scalar", "scalar", "scalar", "scalar", "scalar", "scalar"
     })
 
 local s = box.schema.space.create('T15', {format={{'I', 'integer'}, {'M', 'map'}, {'A', 'array'}}})
diff --git a/test/sql/boolean.result b/test/sql/boolean.result
index d027a56e3..b325ea458 100644
--- a/test/sql/boolean.result
+++ b/test/sql/boolean.result
@@ -4023,7 +4023,7 @@ SELECT a2, 2.3 OR a2 FROM t6
 SELECT c, true AND c FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert number(4.56) to boolean'
  | ...
 SELECT c, false AND c FROM t8;
  | ---
@@ -4038,17 +4038,17 @@ SELECT c, false AND c FROM t8;
 SELECT c, true OR c FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert number(4.56) to boolean'
  | ...
 SELECT c, false OR c FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert number(4.56) to boolean'
  | ...
 SELECT c, c AND true FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert number(4.56) to boolean'
  | ...
 SELECT c, c AND false FROM t8;
  | ---
@@ -4063,53 +4063,53 @@ SELECT c, c AND false FROM t8;
 SELECT c, c OR true FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert number(4.56) to boolean'
  | ...
 SELECT c, c OR false FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert number(4.56) to boolean'
  | ...
 
 SELECT a1, c, a1 AND c FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert number(4.56) to boolean'
  | ...
 SELECT a1, c, a1 OR c FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert number(4.56) to boolean'
  | ...
 SELECT a1, c, c AND a1 FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert number(4.56) to boolean'
  | ...
 SELECT a1, c, c OR a1 FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert number(4.56) to boolean'
  | ...
 SELECT a2, c, a2 AND c FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert number(4.56) to boolean'
  | ...
 SELECT a2, c, a2 OR c FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert number(4.56) to boolean'
  | ...
 SELECT a2, c, c AND a2 FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert number(4.56) to boolean'
  | ...
 SELECT a2, c, c OR a2 FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert number(4.56) to boolean'
  | ...
 
 SELECT true + 2.3;
@@ -4357,12 +4357,12 @@ SELECT c, false / c FROM t8;
 SELECT c, true % c FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert double(4.56) to integer'
+ | - 'Type mismatch: can not convert number(4.56) to integer'
  | ...
 SELECT c, false % c FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert double(4.56) to integer'
+ | - 'Type mismatch: can not convert number(4.56) to integer'
  | ...
 SELECT c, c + true FROM t8;
  | ---
@@ -4438,7 +4438,7 @@ SELECT a1, c, a1 / c FROM t6, t8;
 SELECT a1, c, a1 % c FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert double(4.56) to integer'
+ | - 'Type mismatch: can not convert number(4.56) to integer'
  | ...
 SELECT a1, c, c + a1 FROM t6, t8;
  | ---
@@ -4488,7 +4488,7 @@ SELECT a2, c, a2 / c FROM t6, t8;
 SELECT a2, c, a2 % c FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert double(4.56) to integer'
+ | - 'Type mismatch: can not convert number(4.56) to integer'
  | ...
 SELECT a2, c, c + a2 FROM t6, t8;
  | ---
@@ -4601,22 +4601,22 @@ SELECT a2, 2.3 < a2 FROM t6
 SELECT c, true > c FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert number(4.56) to boolean'
  | ...
 SELECT c, false > c FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert number(4.56) to boolean'
  | ...
 SELECT c, true < c FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert number(4.56) to boolean'
  | ...
 SELECT c, false < c FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert number(4.56) to boolean'
  | ...
 SELECT c, c > true FROM t8;
  | ---
@@ -4642,12 +4642,12 @@ SELECT c, c < false FROM t8;
 SELECT a1, c, a1 > c FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert number(4.56) to boolean'
  | ...
 SELECT a1, c, a1 < c FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert number(4.56) to boolean'
  | ...
 SELECT a1, c, c > a1 FROM t6, t8;
  | ---
@@ -4662,12 +4662,12 @@ SELECT a1, c, c < a1 FROM t6, t8;
 SELECT a2, c, a2 > c FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert number(4.56) to boolean'
  | ...
 SELECT a2, c, a2 < c FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert number(4.56) to boolean'
  | ...
 SELECT a2, c, c > a2 FROM t6, t8;
  | ---
@@ -4765,22 +4765,22 @@ SELECT a2, 2.3 <= a2 FROM t6
 SELECT c, true >= c FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert number(4.56) to boolean'
  | ...
 SELECT c, false >= c FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert number(4.56) to boolean'
  | ...
 SELECT c, true <= c FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert number(4.56) to boolean'
  | ...
 SELECT c, false <= c FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert number(4.56) to boolean'
  | ...
 SELECT c, c >= true FROM t8;
  | ---
@@ -4806,12 +4806,12 @@ SELECT c, c <= false FROM t8;
 SELECT a1, c, a1 >= c FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert number(4.56) to boolean'
  | ...
 SELECT a1, c, a1 <= c FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert number(4.56) to boolean'
  | ...
 SELECT a1, c, c >= a1 FROM t6, t8;
  | ---
@@ -4826,12 +4826,12 @@ SELECT a1, c, c <= a1 FROM t6, t8;
 SELECT a2, c, a2 >= c FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert number(4.56) to boolean'
  | ...
 SELECT a2, c, a2 <= c FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert number(4.56) to boolean'
  | ...
 SELECT a2, c, c >= a2 FROM t6, t8;
  | ---
@@ -4929,22 +4929,22 @@ SELECT a2, 2.3 != a2 FROM t6
 SELECT c, true == c FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert number(4.56) to boolean'
  | ...
 SELECT c, false == c FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert number(4.56) to boolean'
  | ...
 SELECT c, true != c FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert number(4.56) to boolean'
  | ...
 SELECT c, false != c FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert number(4.56) to boolean'
  | ...
 SELECT c, c == true FROM t8;
  | ---
@@ -4970,12 +4970,12 @@ SELECT c, c != false FROM t8;
 SELECT a1, c, a1 == c FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert number(4.56) to boolean'
  | ...
 SELECT a1, c, a1 != c FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert number(4.56) to boolean'
  | ...
 SELECT a1, c, c == a1 FROM t6, t8;
  | ---
@@ -4990,12 +4990,12 @@ SELECT a1, c, c != a1 FROM t6, t8;
 SELECT a2, c, a2 == c FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert number(4.56) to boolean'
  | ...
 SELECT a2, c, a2 != c FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert double(4.56) to boolean'
+ | - 'Type mismatch: can not convert number(4.56) to boolean'
  | ...
 SELECT a2, c, c == a2 FROM t6, t8;
  | ---
diff --git a/test/sql/gh-4697-scalar-bool-sort-cmp.result b/test/sql/gh-4697-scalar-bool-sort-cmp.result
index e69c625bd..7f054b438 100644
--- a/test/sql/gh-4697-scalar-bool-sort-cmp.result
+++ b/test/sql/gh-4697-scalar-bool-sort-cmp.result
@@ -34,9 +34,9 @@ SELECT s2, typeof(s2) FROM test ORDER BY s2;
  |     type: string
  |   rows:
  |   - [null, 'NULL']
- |   - [true, 'boolean']
- |   - [1, 'integer']
- |   - [1.1, 'double']
+ |   - [true, 'scalar']
+ |   - [1, 'scalar']
+ |   - [1.1, 'scalar']
  | ...
 SELECT s3, typeof(s3) FROM test ORDER BY s3;
  | ---
@@ -47,7 +47,7 @@ SELECT s3, typeof(s3) FROM test ORDER BY s3;
  |     type: string
  |   rows:
  |   - [null, 'NULL']
- |   - [true, 'boolean']
- |   - [1, 'integer']
- |   - [1.1, 'double']
+ |   - [true, 'scalar']
+ |   - [1, 'scalar']
+ |   - [1.1, 'scalar']
  | ...
diff --git a/test/sql/types.result b/test/sql/types.result
index e8ebdae84..592b1aa19 100644
--- a/test/sql/types.result
+++ b/test/sql/types.result
@@ -215,12 +215,12 @@ box.execute("INSERT INTO t1 VALUES (randomblob(5));")
 box.execute("SELECT * FROM t1 WHERE s LIKE 'blob';")
 ---
 - null
-- 'Inconsistent types: expected string got varbinary(x''91A0FEE366'')'
+- 'Inconsistent types: expected string got scalar(x''91A0FEE366'')'
 ...
 box.execute("SELECT * FROM t1 WHERE 'blob' LIKE s;")
 ---
 - null
-- 'Inconsistent types: expected string got varbinary(x''91A0FEE366'')'
+- 'Inconsistent types: expected string got scalar(x''91A0FEE366'')'
 ...
 box.execute("SELECT * FROM t1 WHERE 'blob' LIKE x'0000';")
 ---
@@ -246,7 +246,7 @@ box.execute("INSERT INTO t1 VALUES (1);")
 box.execute("SELECT * FROM t1 WHERE s LIKE 'int';")
 ---
 - null
-- 'Inconsistent types: expected string got integer(1)'
+- 'Inconsistent types: expected string got scalar(1)'
 ...
 box.execute("SELECT * FROM t1 WHERE 'int' LIKE 4;")
 ---
@@ -1480,7 +1480,7 @@ box.execute("SELECT typeof(a), typeof(s) FROM t;")
   - name: COLUMN_2
     type: string
   rows:
-  - ['integer', 'integer']
+  - ['integer', 'scalar']
   - ['NULL', 'NULL']
 ...
 box.execute('CREATE TABLE t1 (id INTEGER PRIMARY KEY, a INTEGER, b INTEGER)')
-- 
2.25.1


^ permalink raw reply	[flat|nested] 14+ messages in thread

* [Tarantool-patches] [PATCH v1 3/7] sql: disallow implicit cast from NUMBER and SCALAR
  2021-08-13  3:12 [Tarantool-patches] [PATCH v1 0/7] Rework SCALAR and NUMBER types in SQL Mergen Imeev via Tarantool-patches
  2021-08-13  3:12 ` [Tarantool-patches] [PATCH v1 1/7] sql: remove enum field_type from struct Mem Mergen Imeev via Tarantool-patches
  2021-08-13  3:12 ` [Tarantool-patches] [PATCH v1 2/7] sql: re-introduce NUMBER and SCALAR meta-types Mergen Imeev via Tarantool-patches
@ 2021-08-13  3:12 ` Mergen Imeev via Tarantool-patches
  2021-08-13  3:13 ` [Tarantool-patches] [PATCH v1 4/7] sql: disallow arithmetic for " Mergen Imeev via Tarantool-patches
                   ` (5 subsequent siblings)
  8 siblings, 0 replies; 14+ messages in thread
From: Mergen Imeev via Tarantool-patches @ 2021-08-13  3:12 UTC (permalink / raw)
  To: vdavydov; +Cc: tarantool-patches

This patch disallows implicit cast during INSERT and UPDATE from SCALAR
to any other scalar types and from NUMBER to any other numeric types.

Part of #6221
---
 src/box/sql/mem.c                    |  5 +++
 test/sql-tap/metatypes.test.lua      | 46 +++++++++++++++++++++++++++-
 test/sql-tap/tkt-7bbfb7d442.test.lua |  4 +--
 test/sql-tap/trigger9.test.lua       |  2 +-
 4 files changed, 53 insertions(+), 4 deletions(-)

diff --git a/src/box/sql/mem.c b/src/box/sql/mem.c
index 41aa40fdb..8468c0030 100644
--- a/src/box/sql/mem.c
+++ b/src/box/sql/mem.c
@@ -1218,6 +1218,11 @@ mem_cast_implicit(struct Mem *mem, enum field_type type)
 {
 	if (mem->type == MEM_TYPE_NULL)
 		return 0;
+	if ((mem->flags & MEM_Scalar) != 0 && type != FIELD_TYPE_SCALAR)
+		return -1;
+	if ((mem->flags & MEM_Number) != 0 && type != FIELD_TYPE_SCALAR &&
+	    type != FIELD_TYPE_NUMBER)
+		return -1;
 	switch (type) {
 	case FIELD_TYPE_UNSIGNED:
 		if (mem->type == MEM_TYPE_UINT) {
diff --git a/test/sql-tap/metatypes.test.lua b/test/sql-tap/metatypes.test.lua
index 89802ec09..2748ab781 100755
--- a/test/sql-tap/metatypes.test.lua
+++ b/test/sql-tap/metatypes.test.lua
@@ -1,6 +1,6 @@
 #!/usr/bin/env tarantool
 local test = require("sqltester")
-test:plan(3)
+test:plan(8)
 
 -- Check that SCALAR and NUMBER meta-types works as intended.
 box.execute([[CREATE TABLE t (i INT PRIMARY KEY, s SCALAR, n NUMBER);]])
@@ -46,6 +46,50 @@ test:do_execsql_test(
         "number","number","NULL","NULL","NULL","NULL"
     })
 
+--
+-- Check that implicit cast from NUMBER to numeric types and from SCALAR to
+-- scalar types is prohibited.
+--
+test:do_catchsql_test(
+    "metatypes-2.1",
+    [[
+        INSERT INTO t(i) VALUES(CAST(7 AS SCALAR));
+    ]], {
+        1, "Type mismatch: can not convert scalar(7) to integer"
+    })
+
+test:do_catchsql_test(
+    "metatypes-2.2",
+    [[
+        INSERT INTO t(i, n) VALUES(8, CAST(1.5 AS SCALAR));
+    ]], {
+        1, "Type mismatch: can not convert scalar(1.5) to number"
+    })
+
+test:do_catchsql_test(
+    "metatypes-2.3",
+    [[
+        INSERT INTO t(i) VALUES(CAST(9 AS NUMBER));
+    ]], {
+        1, "Type mismatch: can not convert number(9) to integer"
+    })
+
+test:do_catchsql_test(
+    "metatypes-2.4",
+    [[
+        UPDATE t SET i = CAST(10 AS SCALAR);
+    ]], {
+        1, "Type mismatch: can not convert scalar(10) to integer"
+    })
+
+test:do_catchsql_test(
+    "metatypes-2.5",
+    [[
+        UPDATE t SET i = CAST(11 AS NUMBER);
+    ]], {
+        1, "Type mismatch: can not convert number(11) to integer"
+    })
+
 box.execute([[DROP TABLE t;]])
 
 test:finish_test()
diff --git a/test/sql-tap/tkt-7bbfb7d442.test.lua b/test/sql-tap/tkt-7bbfb7d442.test.lua
index 448f884d8..71caad0ea 100755
--- a/test/sql-tap/tkt-7bbfb7d442.test.lua
+++ b/test/sql-tap/tkt-7bbfb7d442.test.lua
@@ -67,14 +67,14 @@ if (1 > 0)
             DELETE FROM t3
         ]])
 
-    test:do_execsql_test(
+    test:do_catchsql_test(
         1.4,
         [[
             INSERT INTO t3(t3_a) SELECT 1 UNION SELECT 2 UNION SELECT 3;
             SELECT * FROM t3;
         ]], {
             -- <1.4>
-            1, "I", 2, "II", 3, "III"
+            1, "Type mismatch: can not convert scalar(1) to integer"
             -- </1.4>
         })
 
diff --git a/test/sql-tap/trigger9.test.lua b/test/sql-tap/trigger9.test.lua
index 89b5b89ef..855557e4a 100755
--- a/test/sql-tap/trigger9.test.lua
+++ b/test/sql-tap/trigger9.test.lua
@@ -390,7 +390,7 @@ test:do_execsql_test(
     "trigger9-3.6",
     [[
         CREATE VIEW v1 AS
-          SELECT sum(a) AS a, max(b) AS b FROM t3 GROUP BY t3.a HAVING b>'two';
+          SELECT CAST(sum(a) AS INTEGER) AS a, max(b) AS b FROM t3 GROUP BY t3.a HAVING b>'two';
         DROP TRIGGER IF EXISTS trig1;
         CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 FOR EACH ROW BEGIN
           INSERT INTO t2 VALUES(old.a);
-- 
2.25.1


^ permalink raw reply	[flat|nested] 14+ messages in thread

* [Tarantool-patches] [PATCH v1 4/7] sql: disallow arithmetic for NUMBER and SCALAR
  2021-08-13  3:12 [Tarantool-patches] [PATCH v1 0/7] Rework SCALAR and NUMBER types in SQL Mergen Imeev via Tarantool-patches
                   ` (2 preceding siblings ...)
  2021-08-13  3:12 ` [Tarantool-patches] [PATCH v1 3/7] sql: disallow implicit cast from NUMBER and SCALAR Mergen Imeev via Tarantool-patches
@ 2021-08-13  3:13 ` Mergen Imeev via Tarantool-patches
  2021-08-13  3:13 ` [Tarantool-patches] [PATCH v1 5/7] sql: disallow bitwise " Mergen Imeev via Tarantool-patches
                   ` (4 subsequent siblings)
  8 siblings, 0 replies; 14+ messages in thread
From: Mergen Imeev via Tarantool-patches @ 2021-08-13  3:13 UTC (permalink / raw)
  To: vdavydov; +Cc: tarantool-patches

This patch disallows arithmetic operations for SCALAR and NUMBER values.
It also corrects the description of the error that is generated when an
inappropriate value participates in an arithmetic operation.

Part of #6221
---
 src/box/sql/mem.c                             |  60 +--
 src/box/sql/mem.h                             |   6 +
 test/sql-tap/colname.test.lua                 |   4 +-
 test/sql-tap/e_select1.test.lua               |   6 +-
 .../gh-5335-wrong-int-to-double-cast.test.lua |  40 --
 ...-5756-implicit-cast-in-arithmetic.test.lua |  32 +-
 test/sql-tap/metatypes.test.lua               |  19 +-
 test/sql-tap/minmax2.test.lua                 |   2 +-
 test/sql-tap/numcast.test.lua                 |  58 +--
 test/sql-tap/select7.test.lua                 |   8 +-
 test/sql-tap/sort.test.lua                    |  14 +-
 test/sql-tap/sql-errors.test.lua              |   8 +-
 test/sql-tap/tkt-91e2e8ba6f.test.lua          |  96 ----
 test/sql-tap/tkt-a8a0d2996a.test.lua          |  18 +-
 test/sql-tap/uuid.test.lua                    |  10 +-
 test/sql-tap/view.test.lua                    |   4 +-
 test/sql/boolean.result                       | 434 +++++++++---------
 test/sql/boolean.test.sql                     |   4 +-
 test/sql/prepared.result                      |   2 +-
 test/sql/prepared.test.lua                    |   2 +-
 test/sql/types.result                         |   3 +-
 21 files changed, 323 insertions(+), 507 deletions(-)
 delete mode 100755 test/sql-tap/gh-5335-wrong-int-to-double-cast.test.lua
 delete mode 100755 test/sql-tap/tkt-91e2e8ba6f.test.lua

diff --git a/src/box/sql/mem.c b/src/box/sql/mem.c
index 8468c0030..e2c85e2ea 100644
--- a/src/box/sql/mem.c
+++ b/src/box/sql/mem.c
@@ -1590,6 +1590,22 @@ mem_concat(struct Mem *a, struct Mem *b, struct Mem *result)
 	return 0;
 }
 
+static inline int
+check_types_numeric_arithmetic(const struct Mem *a, const struct Mem *b)
+{
+	if (!mem_is_num(a) || mem_is_metatype(a)) {
+		diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(a),
+			 "integer, unsigned or double");
+		return -1;
+	}
+	if (!mem_is_num(b) || mem_is_metatype(b)) {
+		diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(b),
+			 "integer, unsigned or double");
+		return -1;
+	}
+	return 0;
+}
+
 int
 mem_add(const struct Mem *left, const struct Mem *right, struct Mem *result)
 {
@@ -1597,16 +1613,8 @@ mem_add(const struct Mem *left, const struct Mem *right, struct Mem *result)
 		mem_set_null(result);
 		return 0;
 	}
-	if (!mem_is_num(right)) {
-		diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(right),
-			 "number");
+	if (check_types_numeric_arithmetic(right, left) != 0)
 		return -1;
-	}
-	if (!mem_is_num(left)) {
-		diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(left),
-			 "number");
-		return -1;
-	}
 	if (((left->type | right->type) & MEM_TYPE_DOUBLE) != 0) {
 		double a;
 		double b;
@@ -1633,16 +1641,8 @@ mem_sub(const struct Mem *left, const struct Mem *right, struct Mem *result)
 		mem_set_null(result);
 		return 0;
 	}
-	if (!mem_is_num(right)) {
-		diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(right),
-			 "number");
-		return -1;
-	}
-	if (!mem_is_num(left)) {
-		diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(left),
-			 "number");
+	if (check_types_numeric_arithmetic(right, left) != 0)
 		return -1;
-	}
 	if (((left->type | right->type) & MEM_TYPE_DOUBLE) != 0) {
 		double a;
 		double b;
@@ -1669,16 +1669,8 @@ mem_mul(const struct Mem *left, const struct Mem *right, struct Mem *result)
 		mem_set_null(result);
 		return 0;
 	}
-	if (!mem_is_num(right)) {
-		diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(right),
-			 "number");
+	if (check_types_numeric_arithmetic(right, left) != 0)
 		return -1;
-	}
-	if (!mem_is_num(left)) {
-		diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(left),
-			 "number");
-		return -1;
-	}
 	if (((left->type | right->type) & MEM_TYPE_DOUBLE) != 0) {
 		double a;
 		double b;
@@ -1705,16 +1697,8 @@ mem_div(const struct Mem *left, const struct Mem *right, struct Mem *result)
 		mem_set_null(result);
 		return 0;
 	}
-	if (!mem_is_num(right)) {
-		diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(right),
-			 "number");
+	if (check_types_numeric_arithmetic(right, left) != 0)
 		return -1;
-	}
-	if (!mem_is_num(left)) {
-		diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(left),
-			 "number");
-		return -1;
-	}
 	if (((left->type | right->type) & MEM_TYPE_DOUBLE) != 0) {
 		double a;
 		double b;
@@ -1750,12 +1734,12 @@ mem_rem(const struct Mem *left, const struct Mem *right, struct Mem *result)
 		mem_set_null(result);
 		return 0;
 	}
-	if (!mem_is_int(right)) {
+	if (!mem_is_int(right) || mem_is_metatype(right)) {
 		diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(right),
 			 "integer");
 		return -1;
 	}
-	if (!mem_is_int(left)) {
+	if (!mem_is_int(left) || mem_is_metatype(left)) {
 		diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(left),
 			 "integer");
 		return -1;
diff --git a/src/box/sql/mem.h b/src/box/sql/mem.h
index a5c9015cd..5f004646e 100644
--- a/src/box/sql/mem.h
+++ b/src/box/sql/mem.h
@@ -141,6 +141,12 @@ mem_is_num(const struct Mem *mem)
 	return (type & (MEM_TYPE_UINT | MEM_TYPE_INT | MEM_TYPE_DOUBLE)) != 0;
 }
 
+static inline bool
+mem_is_metatype(const struct Mem *mem)
+{
+	return (mem->flags & (MEM_Number | MEM_Scalar)) != 0;
+}
+
 static inline bool
 mem_is_double(const struct Mem *mem)
 {
diff --git a/test/sql-tap/colname.test.lua b/test/sql-tap/colname.test.lua
index 9ea0b2d4a..ff7585c7a 100755
--- a/test/sql-tap/colname.test.lua
+++ b/test/sql-tap/colname.test.lua
@@ -673,7 +673,7 @@ test:do_execsql2_test(
 test:do_execsql2_test(
     "colname-12.5",
     [[
-        CREATE TABLE j (s1 SCALAR PRIMARY KEY);
+        CREATE TABLE j (s1 INTEGER PRIMARY KEY);
         INSERT INTO j VALUES(1);
     ]], {})
 
@@ -750,7 +750,7 @@ test:do_execsql2_test(
 test:do_execsql2_test(
     "colname-12.14",
     [[
-        CREATE TABLE j_1 (column_1 SCALAR PRIMARY KEY, column_2 SCALAR);
+        CREATE TABLE j_1 (column_1 INTEGER PRIMARY KEY, column_2 SCALAR);
         INSERT INTO j_1 VALUES(1, 1);
     ]], {})
 
diff --git a/test/sql-tap/e_select1.test.lua b/test/sql-tap/e_select1.test.lua
index 8293965b8..27bd44ac4 100755
--- a/test/sql-tap/e_select1.test.lua
+++ b/test/sql-tap/e_select1.test.lua
@@ -756,9 +756,9 @@ test:drop_all_tables()
 test:do_execsql_test(
     "e_select-4.0",
     [[
-        CREATE TABLE z1(id  INT primary key, a NUMBER, b NUMBER, c TEXT);
-        CREATE TABLE z2(id  INT primary key, d NUMBER, e NUMBER);
-        CREATE TABLE z3(id  INT primary key, a NUMBER, b NUMBER);
+        CREATE TABLE z1(id  INT primary key, a DOUBLE, b DOUBLE, c TEXT);
+        CREATE TABLE z2(id  INT primary key, d DOUBLE, e DOUBLE);
+        CREATE TABLE z3(id  INT primary key, a DOUBLE, b DOUBLE);
 
         INSERT INTO z1 VALUES(1, 51.65, -59.58, 'belfries');
         INSERT INTO z1 VALUES(2, -5, NULL, '75');
diff --git a/test/sql-tap/gh-5335-wrong-int-to-double-cast.test.lua b/test/sql-tap/gh-5335-wrong-int-to-double-cast.test.lua
deleted file mode 100755
index 76daa45e9..000000000
--- a/test/sql-tap/gh-5335-wrong-int-to-double-cast.test.lua
+++ /dev/null
@@ -1,40 +0,0 @@
-#!/usr/bin/env tarantool
-local test = require("sqltester")
-test:plan(2)
-
-test:execsql([[
-    CREATE TABLE t1 (i NUMBER PRIMARY KEY, n NUMBER);
-    CREATE TABLE t2 (i NUMBER PRIMARY KEY, n NUMBER);
-    -- This trigger is only needed to reproduce the error.
-    CREATE TRIGGER r AFTER INSERT ON t1 FOR EACH ROW BEGIN UPDATE t1 SET n = new.n; END;
-    INSERT INTO t1 VALUES (1, 1);
-    INSERT INTO t2 VALUES (1, 1);
-]])
-
---
--- Make sure that there are no unnecesary INTEGER to DOUBLE implicit cast in
--- field of type NUMBER.
---
-test:do_execsql_test(
-    "gh-5335-1",
-    [[
-        SELECT i / 2, n / 2 FROM t1;
-    ]], {
-        0, 0
-    })
-
-test:do_execsql_test(
-    "gh-5335-2",
-    [[
-        SELECT i / 2, n / 2 FROM t2 GROUP BY n;
-    ]], {
-        0, 0
-    })
-
-test:execsql([[
-    DROP TRIGGER r;
-    DROP TABLE t1;
-    DROP TABLE t2;
-]])
-
-test:finish_test()
diff --git a/test/sql-tap/gh-5756-implicit-cast-in-arithmetic.test.lua b/test/sql-tap/gh-5756-implicit-cast-in-arithmetic.test.lua
index 9ce595fe0..3e4de6860 100755
--- a/test/sql-tap/gh-5756-implicit-cast-in-arithmetic.test.lua
+++ b/test/sql-tap/gh-5756-implicit-cast-in-arithmetic.test.lua
@@ -35,7 +35,7 @@ test:do_catchsql_test(
     [[
         SELECT 9 + '2';
     ]], {
-        1, "Type mismatch: can not convert string('2') to number"
+        1, "Type mismatch: can not convert string('2') to integer, unsigned or double"
     })
 
 test:do_catchsql_test(
@@ -43,7 +43,7 @@ test:do_catchsql_test(
     [[
         SELECT 9 + x'32';
     ]], {
-        1, "Type mismatch: can not convert varbinary(x'32') to number"
+        1, "Type mismatch: can not convert varbinary(x'32') to integer, unsigned or double"
     })
 
 test:do_catchsql_test(
@@ -51,7 +51,7 @@ test:do_catchsql_test(
     [[
         SELECT 9 + true;
     ]], {
-        1, "Type mismatch: can not convert boolean(TRUE) to number"
+        1, "Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double"
     })
 
 test:do_catchsql_test(
@@ -60,7 +60,7 @@ test:do_catchsql_test(
         SELECT 9 + CAST('11111111-1111-1111-1111-111111111111' AS UUID);
     ]], {
         1, "Type mismatch: can not convert "..
-           "uuid('11111111-1111-1111-1111-111111111111') to number"
+           "uuid('11111111-1111-1111-1111-111111111111') to integer, unsigned or double"
     })
 
 test:do_execsql_test(
@@ -92,7 +92,7 @@ test:do_catchsql_test(
     [[
         SELECT 9 - '2';
     ]], {
-        1, "Type mismatch: can not convert string('2') to number"
+        1, "Type mismatch: can not convert string('2') to integer, unsigned or double"
     })
 
 test:do_catchsql_test(
@@ -100,7 +100,7 @@ test:do_catchsql_test(
     [[
         SELECT 9 - x'32';
     ]], {
-        1, "Type mismatch: can not convert varbinary(x'32') to number"
+        1, "Type mismatch: can not convert varbinary(x'32') to integer, unsigned or double"
     })
 
 test:do_catchsql_test(
@@ -108,7 +108,7 @@ test:do_catchsql_test(
     [[
         SELECT 9 - true;
     ]], {
-        1, "Type mismatch: can not convert boolean(TRUE) to number"
+        1, "Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double"
     })
 
 test:do_catchsql_test(
@@ -117,7 +117,7 @@ test:do_catchsql_test(
         SELECT 9 - CAST('11111111-1111-1111-1111-111111111111' AS UUID);
     ]], {
         1, "Type mismatch: can not convert "..
-           "uuid('11111111-1111-1111-1111-111111111111') to number"
+           "uuid('11111111-1111-1111-1111-111111111111') to integer, unsigned or double"
     })
 
 test:do_execsql_test(
@@ -149,7 +149,7 @@ test:do_catchsql_test(
     [[
         SELECT 9 * '2';
     ]], {
-        1, "Type mismatch: can not convert string('2') to number"
+        1, "Type mismatch: can not convert string('2') to integer, unsigned or double"
     })
 
 test:do_catchsql_test(
@@ -157,7 +157,7 @@ test:do_catchsql_test(
     [[
         SELECT 9 * x'32';
     ]], {
-        1, "Type mismatch: can not convert varbinary(x'32') to number"
+        1, "Type mismatch: can not convert varbinary(x'32') to integer, unsigned or double"
     })
 
 test:do_catchsql_test(
@@ -165,7 +165,7 @@ test:do_catchsql_test(
     [[
         SELECT 9 * true;
     ]], {
-        1, "Type mismatch: can not convert boolean(TRUE) to number"
+        1, "Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double"
     })
 
 test:do_catchsql_test(
@@ -174,7 +174,7 @@ test:do_catchsql_test(
         SELECT 9 * CAST('11111111-1111-1111-1111-111111111111' AS UUID);
     ]], {
         1, "Type mismatch: can not convert "..
-           "uuid('11111111-1111-1111-1111-111111111111') to number"
+           "uuid('11111111-1111-1111-1111-111111111111') to integer, unsigned or double"
     })
 
 test:do_execsql_test(
@@ -206,7 +206,7 @@ test:do_catchsql_test(
     [[
         SELECT 9 / '2';
     ]], {
-        1, "Type mismatch: can not convert string('2') to number"
+        1, "Type mismatch: can not convert string('2') to integer, unsigned or double"
     })
 
 test:do_catchsql_test(
@@ -214,7 +214,7 @@ test:do_catchsql_test(
     [[
         SELECT 9 / x'32';
     ]], {
-        1, "Type mismatch: can not convert varbinary(x'32') to number"
+        1, "Type mismatch: can not convert varbinary(x'32') to integer, unsigned or double"
     })
 
 test:do_catchsql_test(
@@ -222,7 +222,7 @@ test:do_catchsql_test(
     [[
         SELECT 9 / true;
     ]], {
-        1, "Type mismatch: can not convert boolean(TRUE) to number"
+        1, "Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double"
     })
 
 test:do_catchsql_test(
@@ -231,7 +231,7 @@ test:do_catchsql_test(
         SELECT 9 / CAST('11111111-1111-1111-1111-111111111111' AS UUID);
     ]], {
         1, "Type mismatch: can not convert "..
-           "uuid('11111111-1111-1111-1111-111111111111') to number"
+           "uuid('11111111-1111-1111-1111-111111111111') to integer, unsigned or double"
     })
 
 test:do_execsql_test(
diff --git a/test/sql-tap/metatypes.test.lua b/test/sql-tap/metatypes.test.lua
index 2748ab781..c0b2a4d03 100755
--- a/test/sql-tap/metatypes.test.lua
+++ b/test/sql-tap/metatypes.test.lua
@@ -1,6 +1,6 @@
 #!/usr/bin/env tarantool
 local test = require("sqltester")
-test:plan(8)
+test:plan(10)
 
 -- Check that SCALAR and NUMBER meta-types works as intended.
 box.execute([[CREATE TABLE t (i INT PRIMARY KEY, s SCALAR, n NUMBER);]])
@@ -90,6 +90,23 @@ test:do_catchsql_test(
         1, "Type mismatch: can not convert number(11) to integer"
     })
 
+-- Check that arithmetic operations are prohibited for NUMBER and SCALAR values.
+test:do_catchsql_test(
+    "metatypes-3.1",
+    [[
+        SELECT 1 + CAST(1 AS NUMBER);
+    ]], {
+        1, "Type mismatch: can not convert number(1) to integer, unsigned or double"
+    })
+
+test:do_catchsql_test(
+    "metatypes-3.2",
+    [[
+        SELECT CAST(1 AS SCALAR) * 1;
+    ]], {
+        1, "Type mismatch: can not convert scalar(1) to integer, unsigned or double"
+    })
+
 box.execute([[DROP TABLE t;]])
 
 test:finish_test()
diff --git a/test/sql-tap/minmax2.test.lua b/test/sql-tap/minmax2.test.lua
index 31212bc18..42dbafea8 100755
--- a/test/sql-tap/minmax2.test.lua
+++ b/test/sql-tap/minmax2.test.lua
@@ -278,7 +278,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "minmax2-4.1",
     [[
-        SELECT coalesce(min(x+0),-1), coalesce(max(x+0),-1) FROM
+        SELECT coalesce(min(x),-1), coalesce(max(x),-1) FROM
           (SELECT x, y FROM t1 UNION SELECT NULL as "x", NULL as "y")
     ]], {
         -- <minmax2-4.1>
diff --git a/test/sql-tap/numcast.test.lua b/test/sql-tap/numcast.test.lua
index b172ca625..763708d76 100755
--- a/test/sql-tap/numcast.test.lua
+++ b/test/sql-tap/numcast.test.lua
@@ -1,6 +1,6 @@
 #!/usr/bin/env tarantool
 local test = require("sqltester")
-test:plan(31)
+test:plan(26)
 
 --!./tcltestrunner.lua
 -- 2013 March 20
@@ -144,62 +144,6 @@ test:do_catchsql_test(
         1, "Type mismatch: can not convert double(2.1) to integer"
     })
 
---
--- gh-4233: Make sure that NUMBER can contain UNSIGNED, INTEGER
--- and DOUBLE and is not automatically converted to DOUBLE.
---
-test:do_execsql_test(
-    "numcast-3.3",
-    [[
-        SELECT CAST('11111111111111111111' AS NUMBER);
-    ]], {
-        11111111111111111111ULL
-    })
-
-test:do_execsql_test(
-    "numcast-3.4",
-    [[
-        SELECT CAST('101' AS NUMBER) / 10, CAST('101.' AS NUMBER) / 10;
-    ]], {
-        10, 10.1
-    })
-
-test:do_execsql_test(
-    "numcast-3.5",
-    [[
-        SELECT CAST('101     ' AS NUMBER) / 10, CAST('      101' AS NUMBER) / 10;
-    ]], {
-        10, 10
-    })
-
-test:do_execsql_test(
-    "numcast-3.6",
-    [[
-        CREATE TABLE t1 (id INT PRIMARY KEY, n NUMBER);
-        INSERT INTO t1 VALUES (1, 9223372036854775807);
-        INSERT INTO t1 VALUES (2, -9223372036854775807);
-        INSERT INTO t1 VALUES (3, 9007199254740992.0);
-        SELECT n, n/100 FROM t1;
-    ]], {
-        9223372036854775807ULL, 92233720368547758ULL,
-        -9223372036854775807LL, -92233720368547758LL,
-        9007199254740992, 90071992547409.92
-    })
-
-test:do_execsql_test(
-    "numcast-3.7",
-    [[
-        CREATE TABLE t2(a NUMBER primary key);
-        INSERT INTO t2 VALUES(-56);
-        INSERT INTO t2 VALUES(44.0);
-        INSERT INTO t2 VALUES(46);
-        INSERT INTO t2 VALUES(56.0);
-        SELECT (a + 25) / 50 FROM t2;
-    ]], {
-        0,1.38,1,1.62
-})
-
-
 test:do_execsql_test(
     "numcast-3.8",
     [[
diff --git a/test/sql-tap/select7.test.lua b/test/sql-tap/select7.test.lua
index 815f9110b..b1cc32eee 100755
--- a/test/sql-tap/select7.test.lua
+++ b/test/sql-tap/select7.test.lua
@@ -187,7 +187,7 @@ test:do_catchsql_test(
 test:do_execsql_test(
     "select7-7.1",
     [[
-        CREATE TABLE t3(a NUMBER primary key);
+        CREATE TABLE t3(a DOUBLE primary key);
         INSERT INTO t3 VALUES(44.0);
         INSERT INTO t3 VALUES(56.0);
     ]], {
@@ -210,7 +210,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "select7-7.3",
     [[
-        CREATE TABLE t4(a NUMBER primary key);
+        CREATE TABLE t4(a DOUBLE primary key);
         INSERT INTO t4 VALUES( 2.0 );
         INSERT INTO t4 VALUES( 3.0 );
     ]], {
@@ -235,7 +235,7 @@ test:do_execsql_test(
         SELECT a=0, typeof(a) FROM t4
     ]], {
         -- <select7-7.5>
-        false, "number", false, "number"
+        false, "double", false, "double"
         -- </select7-7.5>
     })
 
@@ -245,7 +245,7 @@ test:do_execsql_test(
         SELECT a=0, typeof(a) FROM t4 GROUP BY a
     ]], {
         -- <select7-7.6>
-        false, "number", false, "number"
+        false, "double", false, "double"
         -- </select7-7.6>
     })
 
diff --git a/test/sql-tap/sort.test.lua b/test/sql-tap/sort.test.lua
index 0a1c2aabf..68a0ecbd2 100755
--- a/test/sql-tap/sort.test.lua
+++ b/test/sql-tap/sort.test.lua
@@ -30,7 +30,7 @@ test:do_test(
                v varchar(10),
                log int,
                roman varchar(10),
-               flt NUMBER
+               flt DOUBLE
             );
             INSERT INTO t1 VALUES(1,'one',0,'I',3.141592653);
             INSERT INTO t1 VALUES(2,'two',1,'II',2.15);
@@ -242,7 +242,7 @@ test:do_execsql_test(
         SELECT v FROM t1 ORDER BY v;
     ]], {
         -- <sort-2.1.1>
-        "x-123.0", "x-2.15", "x-2b", "x-3.141592653", "x-4221.0", "x0.0013442", "x1.6", "x11"
+        "x-123.0", "x-2.15", "x-2b", "x-3.141592653", "x-4221.0", "x0.0013442", "x1.6", "x11.0"
         -- </sort-2.1.1>
     })
 
@@ -252,7 +252,7 @@ test:do_execsql_test(
         SELECT v FROM t1 ORDER BY substr(v,2,999);
     ]], {
         -- <sort-2.1.2>
-        "x-123.0", "x-2.15", "x-2b", "x-3.141592653", "x-4221.0", "x0.0013442", "x1.6", "x11"
+        "x-123.0", "x-2.15", "x-2b", "x-3.141592653", "x-4221.0", "x0.0013442", "x1.6", "x11.0"
         -- </sort-2.1.2>
     })
 
@@ -262,7 +262,7 @@ test:do_execsql_test(
         SELECT v FROM t1 ORDER BY substr(v,2,999) DESC;
     ]], {
         -- <sort-2.1.4>
-        "x11", "x1.6", "x0.0013442", "x-4221.0", "x-3.141592653", "x-2b", "x-2.15", "x-123.0"
+        "x11.0", "x1.6", "x0.0013442", "x-4221.0", "x-3.141592653", "x-2b", "x-2.15", "x-123.0"
         -- </sort-2.1.4>
     })
 
@@ -380,7 +380,7 @@ test:do_execsql_test(
         SELECT v FROM t1 ORDER BY 1;
     ]], {
         -- <sort-4.6>
-        "x-123.0", "x-2.15", "x-2b", "x-3.141592653", "x-4.0e9", "x-4221.0", "x0.0013442", "x01234567890123456789", "x1.6", "x11", "x2.7", "x5.0e10"
+        "x-123.0", "x-2.15", "x-2b", "x-3.141592653", "x-4.0e9", "x-4221.0", "x0.0013442", "x01234567890123456789", "x1.6", "x11.0", "x2.7", "x5.0e10"
         -- </sort-4.6>
     })
 
@@ -390,7 +390,7 @@ test:do_execsql_test(
         SELECT v FROM t1 ORDER BY 1 DESC;
     ]], {
         -- <sort-4.7>
-        "x5.0e10", "x2.7", "x11", "x1.6", "x01234567890123456789", "x0.0013442", "x-4221.0", "x-4.0e9", "x-3.141592653", "x-2b", "x-2.15", "x-123.0"
+        "x5.0e10", "x2.7", "x11.0", "x1.6", "x01234567890123456789", "x0.0013442", "x-4221.0", "x-4.0e9", "x-3.141592653", "x-2b", "x-2.15", "x-123.0"
         -- </sort-4.7>
     })
 
@@ -400,7 +400,7 @@ test:do_execsql_test(
         SELECT substr(v,2,99) FROM t1 ORDER BY 1;
     ]], {
         -- <sort-4.8>
-    "-123.0","-2.15","-2b","-3.141592653","-4.0e9","-4221.0","0.0013442","01234567890123456789","1.6","11","2.7","5.0e10"
+    "-123.0","-2.15","-2b","-3.141592653","-4.0e9","-4221.0","0.0013442","01234567890123456789","1.6","11.0","2.7","5.0e10"
         -- </sort-4.8>
     })
 
diff --git a/test/sql-tap/sql-errors.test.lua b/test/sql-tap/sql-errors.test.lua
index 409c97257..daf0ee643 100755
--- a/test/sql-tap/sql-errors.test.lua
+++ b/test/sql-tap/sql-errors.test.lua
@@ -696,7 +696,7 @@ test:do_catchsql_test(
 		SELECT X'ff' + 1;
 	]], {
 		-- <sql-errors-2.1>
-		1, "Type mismatch: can not convert varbinary(x'FF') to number"
+		1, "Type mismatch: can not convert varbinary(x'FF') to integer, unsigned or double"
 		-- </sql-errors-2.1>
 	})
 
@@ -706,7 +706,7 @@ test:do_catchsql_test(
 		SELECT X'ff' - 1;
 	]], {
 		-- <sql-errors-2.2>
-		1, "Type mismatch: can not convert varbinary(x'FF') to number"
+		1, "Type mismatch: can not convert varbinary(x'FF') to integer, unsigned or double"
 		-- </sql-errors-2.2>
 	})
 
@@ -716,7 +716,7 @@ test:do_catchsql_test(
 		SELECT X'ff' * 1;
 	]], {
 		-- <sql-errors-2.3>
-		1, "Type mismatch: can not convert varbinary(x'FF') to number"
+		1, "Type mismatch: can not convert varbinary(x'FF') to integer, unsigned or double"
 		-- </sql-errors-2.3>
 	})
 
@@ -726,7 +726,7 @@ test:do_catchsql_test(
 		SELECT X'ff' / 1;
 	]], {
 		-- <sql-errors-2.4>
-		1, "Type mismatch: can not convert varbinary(x'FF') to number"
+		1, "Type mismatch: can not convert varbinary(x'FF') to integer, unsigned or double"
 		-- </sql-errors-2.4>
 	})
 
diff --git a/test/sql-tap/tkt-91e2e8ba6f.test.lua b/test/sql-tap/tkt-91e2e8ba6f.test.lua
deleted file mode 100755
index f575e213f..000000000
--- a/test/sql-tap/tkt-91e2e8ba6f.test.lua
+++ /dev/null
@@ -1,96 +0,0 @@
-#!/usr/bin/env tarantool
-local test = require("sqltester")
-test:plan(7)
-
---!./tcltestrunner.lua
--- 2011 June 23
---
---    May you do good and not evil.
---    May you find forgiveness for yourself and forgive others.
---    May you share freely, never taking more than you give.
---
--------------------------------------------------------------------------
---
--- This file contains tests for sql. Specifically, it tests that sql
--- does not crash and an error is returned if localhost() fails. This
--- is the problem reported by ticket 91e2e8ba6f.
---
--- ["set","testdir",[["file","dirname",["argv0"]]]]
--- ["source",[["testdir"],"\/tester.tcl"]]
-test:do_execsql_test(
-    1.1,
-    [[
-        CREATE TABLE t1(x INTEGER PRIMARY KEY, y NUMBER);
-        INSERT INTO t1 VALUES(11, 11);
-    ]], {
-        -- <1.1>
-
-        -- </1.1>
-    })
-
-test:do_execsql_test(
-    1.2,
-    [[
-        SELECT x/10, y/10 FROM t1;
-    ]], {
-        -- <1.2>
-        1, 1
-        -- </1.2>
-    })
-
-test:do_execsql_test(
-    1.3,
-    [[
-        SELECT x/10, y/10 FROM (SELECT * FROM t1);
-    ]], {
-        -- <1.3>
-        1, 1
-        -- </1.3>
-    })
-
-test:do_execsql_test(
-    1.4,
-    [[
-        SELECT x/10, y/10 FROM (SELECT * FROM t1 LIMIT 5 OFFSET 0);
-    ]], {
-        -- <1.4>
-        1, 1
-        -- </1.4>
-    })
-
-test:do_execsql_test(
-    1.5,
-    [[
-        SELECT x/10, y/10 FROM (SELECT * FROM t1 LIMIT 5 OFFSET 0) LIMIT 5 OFFSET 0;
-    ]], {
-        -- <1.5>
-        1, 1
-        -- </1.5>
-    })
-
-test:do_execsql_test(
-    1.6,
-    [[
-        SELECT a.x/10, a.y/10 FROM
-          (SELECT * FROM t1 LIMIT 5 OFFSET 0) AS a, t1 AS b WHERE a.x = b.x
-        LIMIT 5 OFFSET 0;
-    ]], {
-        -- <1.6>
-        1, 1
-        -- </1.6>
-    })
-
--- MUST_WORK_TEST
-test:do_execsql_test(
-    1.7,
-    [[
-        CREATE VIEW v1 AS SELECT * FROM t1 LIMIT 5;
-        SELECT a.x/10, a.y/10 FROM v1 AS a, t1 AS b WHERE a.x = b.x LIMIT 5 OFFSET 0;
-    ]], {
-        -- <1.7>
-        1, 1
-        -- </1.7>
-    })
-
-test:finish_test()
-
diff --git a/test/sql-tap/tkt-a8a0d2996a.test.lua b/test/sql-tap/tkt-a8a0d2996a.test.lua
index e97ff8c60..72f57ec65 100755
--- a/test/sql-tap/tkt-a8a0d2996a.test.lua
+++ b/test/sql-tap/tkt-a8a0d2996a.test.lua
@@ -27,7 +27,7 @@ test:do_catchsql_test(
         SELECT typeof(x), typeof(y) FROM t WHERE 1=x+0 AND y=='1';
     ]], {
         -- <1.0>
-        1, "Type mismatch: can not convert string('1') to number"
+        1, "Type mismatch: can not convert string('1') to integer, unsigned or double"
         -- </1.0>
     })
 
@@ -37,7 +37,7 @@ test:do_catchsql_test(
         SELECT typeof(x), typeof(y) FROM t WHERE 1=x-0 AND y=='1';
     ]], {
         -- <1.1>
-        1, "Type mismatch: can not convert string('1') to number"
+        1, "Type mismatch: can not convert string('1') to integer, unsigned or double"
         -- </1.1>
     })
 
@@ -47,7 +47,7 @@ test:do_catchsql_test(
         SELECT typeof(x), typeof(y) FROM t WHERE 1=x*1 AND y=='1';
     ]], {
         -- <1.2>
-        1, "Type mismatch: can not convert string('1') to number"
+        1, "Type mismatch: can not convert string('1') to integer, unsigned or double"
         -- </1.2>
     })
 
@@ -57,7 +57,7 @@ test:do_catchsql_test(
         SELECT typeof(x), typeof(y) FROM t WHERE 1=x/1 AND y=='1';
     ]], {
         -- <1.3>
-        1, "Type mismatch: can not convert string('1') to number"
+        1, "Type mismatch: can not convert string('1') to integer, unsigned or double"
         -- </1.3>
     })
 
@@ -78,7 +78,7 @@ test:do_catchsql_test(
         SELECT typeof(x), typeof(y) FROM t WHERE 1=x+0 AND y=='1';
     ]], {
         -- <3.0>
-        1, "Type mismatch: can not convert string('1.0') to number"
+        1, "Type mismatch: can not convert string('1.0') to integer, unsigned or double"
         -- </3.0>
     })
 
@@ -88,7 +88,7 @@ test:do_catchsql_test(
         SELECT typeof(x), typeof(y) FROM t WHERE 1=x-0 AND y=='1';
     ]], {
         -- <3.1>
-        1, "Type mismatch: can not convert string('1.0') to number"
+        1, "Type mismatch: can not convert string('1.0') to integer, unsigned or double"
         -- </3.1>
     })
 
@@ -98,7 +98,7 @@ test:do_catchsql_test(
         SELECT typeof(x), typeof(y) FROM t WHERE 1=x*1 AND y=='1';
     ]], {
         -- <3.2>
-        1, "Type mismatch: can not convert string('1.0') to number"
+        1, "Type mismatch: can not convert string('1.0') to integer, unsigned or double"
         -- </3.2>
     })
 
@@ -108,7 +108,7 @@ test:do_catchsql_test(
         SELECT typeof(x), typeof(y) FROM t WHERE 1=x/1 AND y=='1';
     ]], {
         -- <3.3>
-        1, "Type mismatch: can not convert string('1.0') to number"
+        1, "Type mismatch: can not convert string('1.0') to integer, unsigned or double"
         -- </3.3>
     })
 
@@ -138,7 +138,7 @@ test:do_catchsql_test(
         SELECT '1.23e64'/'1.0000e+62';
     ]], {
         -- <4.1>
-        1, "Type mismatch: can not convert string('1.0000e+62') to number"
+        1, "Type mismatch: can not convert string('1.0000e+62') to integer, unsigned or double"
         -- </4.1>
     })
 
diff --git a/test/sql-tap/uuid.test.lua b/test/sql-tap/uuid.test.lua
index 32203e9fb..57e638046 100755
--- a/test/sql-tap/uuid.test.lua
+++ b/test/sql-tap/uuid.test.lua
@@ -957,7 +957,7 @@ test:do_catchsql_test(
     [[
         SELECT -u FROM t2;
     ]], {
-        1, "Type mismatch: can not convert uuid('11111111-1111-1111-1111-111111111111') to number"
+        1, "Type mismatch: can not convert uuid('11111111-1111-1111-1111-111111111111') to integer, unsigned or double"
     })
 
 test:do_catchsql_test(
@@ -965,7 +965,7 @@ test:do_catchsql_test(
     [[
         SELECT u + 1 FROM t2;
     ]], {
-        1, "Type mismatch: can not convert uuid('11111111-1111-1111-1111-111111111111') to number"
+        1, "Type mismatch: can not convert uuid('11111111-1111-1111-1111-111111111111') to integer, unsigned or double"
     })
 
 test:do_catchsql_test(
@@ -973,7 +973,7 @@ test:do_catchsql_test(
     [[
         SELECT u - 1 FROM t2;
     ]], {
-        1, "Type mismatch: can not convert uuid('11111111-1111-1111-1111-111111111111') to number"
+        1, "Type mismatch: can not convert uuid('11111111-1111-1111-1111-111111111111') to integer, unsigned or double"
     })
 
 test:do_catchsql_test(
@@ -981,7 +981,7 @@ test:do_catchsql_test(
     [[
         SELECT u * 1 FROM t2;
     ]], {
-        1, "Type mismatch: can not convert uuid('11111111-1111-1111-1111-111111111111') to number"
+        1, "Type mismatch: can not convert uuid('11111111-1111-1111-1111-111111111111') to integer, unsigned or double"
     })
 
 test:do_catchsql_test(
@@ -989,7 +989,7 @@ test:do_catchsql_test(
     [[
         SELECT u / 1 FROM t2;
     ]], {
-        1, "Type mismatch: can not convert uuid('11111111-1111-1111-1111-111111111111') to number"
+        1, "Type mismatch: can not convert uuid('11111111-1111-1111-1111-111111111111') to integer, unsigned or double"
     })
 
 test:do_catchsql_test(
diff --git a/test/sql-tap/view.test.lua b/test/sql-tap/view.test.lua
index 1b60f485b..bea1f7db2 100755
--- a/test/sql-tap/view.test.lua
+++ b/test/sql-tap/view.test.lua
@@ -623,7 +623,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "view-8.6",
     [[
-        SELECT mx+10, pqr FROM v6, v8 WHERE xyz=2;
+        SELECT CAST(mx AS INTEGER) + 10, pqr FROM v6, v8 WHERE xyz = 2;
     ]], {
         -- <view-8.6>
         13, 7
@@ -633,7 +633,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "view-8.7",
     [[
-        SELECT mx+10, pqr FROM v6, v8 WHERE xyz>2;
+        SELECT CAST(mx AS INTEGER) + 10, pqr FROM v6, v8 WHERE xyz > 2;
     ]], {
         -- <view-8.7>
         13, 13, 13, 19, 13, 27
diff --git a/test/sql/boolean.result b/test/sql/boolean.result
index b325ea458..a8400ee49 100644
--- a/test/sql/boolean.result
+++ b/test/sql/boolean.result
@@ -1125,98 +1125,98 @@ SELECT a, a1, a OR a1 FROM t, t6;
 SELECT -true;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT -false;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT -a FROM t;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 
 SELECT true + true;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT true + false;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT false + true;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT false + false;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT true - true;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT true - false;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT false - true;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT false - false;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT true * true;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT true * false;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT false * true;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT false * false;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT true / true;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT true / false;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT false / true;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT false / false;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT true % true;
  | ---
@@ -1242,42 +1242,42 @@ SELECT false % false;
 SELECT a, true + a FROM t;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT a, false + a FROM t;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT a, true - a FROM t;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT a, false - a FROM t;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT a, true * a FROM t;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT a, false * a FROM t;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT a, true / a FROM t;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT a, false / a FROM t;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT a, true % a FROM t;
  | ---
@@ -1292,42 +1292,42 @@ SELECT a, false % a FROM t;
 SELECT a, a + true FROM t;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT a, a + false FROM t;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT a, a - true FROM t;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT a, a - false FROM t;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT a, a * true FROM t;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT a, a * false FROM t;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT a, a / true FROM t;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT a, a / false FROM t;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT a, a % true FROM t;
  | ---
@@ -1343,22 +1343,22 @@ SELECT a, a % false FROM t;
 SELECT a, a1, a + a1 FROM t, t6;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT a, a1, a - a1 FROM t, t6;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT a, a1, a * a1 FROM t, t6;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT a, a1, a / a1 FROM t, t6;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT a, a1, a % a1 FROM t, t6;
  | ---
@@ -2646,42 +2646,42 @@ SELECT a2, b, b OR a2 FROM t6, t7;
 SELECT true + 2;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT false + 2;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT true - 2;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT false - 2;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT true * 2;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT false * 2;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT true / 2;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT false / 2;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT true % 2;
  | ---
@@ -2696,42 +2696,42 @@ SELECT false % 2;
 SELECT 2 + true;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT 2 + false;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT 2 - true;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT 2 - false;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT 2 * true;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT 2 * false;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT 2 / true;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT 2 / false;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT 2 % true;
  | ---
@@ -2747,22 +2747,22 @@ SELECT 2 % false;
 SELECT a1, a1 + 2 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT a1, a1 - 2 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT a1, a1 * 2 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT a1, a1 / 2 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT a1, a1 % 2 FROM t6
  | ---
@@ -2772,22 +2772,22 @@ SELECT a1, a1 % 2 FROM t6
 SELECT a1, 2 + a1 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT a1, 2 - a1 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT a1, 2 * a1 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT a1, 2 / a1 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT a1, 2 % a1 FROM t6
  | ---
@@ -2797,22 +2797,22 @@ SELECT a1, 2 % a1 FROM t6
 SELECT a2, a2 + 2 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT a2, a2 - 2 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT a2, a2 * 2 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT a2, a2 / 2 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT a2, a2 % 2 FROM t6
  | ---
@@ -2822,22 +2822,22 @@ SELECT a2, a2 % 2 FROM t6
 SELECT a2, 2 + a2 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT a2, 2 - a2 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT a2, 2 * a2 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT a2, 2 / a2 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT a2, 2 % a2 FROM t6
  | ---
@@ -2848,42 +2848,42 @@ SELECT a2, 2 % a2 FROM t6
 SELECT b, true + b FROM t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT b, false + b FROM t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT b, true - b FROM t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT b, false - b FROM t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT b, true * b FROM t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT b, false * b FROM t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT b, true / b FROM t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT b, false / b FROM t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT b, true % b FROM t7;
  | ---
@@ -2898,42 +2898,42 @@ SELECT b, false % b FROM t7;
 SELECT b, b + true FROM t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT b, b + false FROM t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT b, b - true FROM t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT b, b - false FROM t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT b, b * true FROM t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT b, b * false FROM t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT b, b / true FROM t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT b, b / false FROM t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT b, b % true FROM t7;
  | ---
@@ -2949,22 +2949,22 @@ SELECT b, b % false FROM t7;
 SELECT a1, b, a1 + b FROM t6, t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT a1, b, a1 - b FROM t6, t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT a1, b, a1 * b FROM t6, t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT a1, b, a1 / b FROM t6, t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT a1, b, a1 % b FROM t6, t7;
  | ---
@@ -2974,22 +2974,22 @@ SELECT a1, b, a1 % b FROM t6, t7;
 SELECT a1, b, b + a1 FROM t6, t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT a1, b, b - a1 FROM t6, t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT a1, b, b * a1 FROM t6, t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT a1, b, b / a1 FROM t6, t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT a1, b, b % a1 FROM t6, t7;
  | ---
@@ -2999,22 +2999,22 @@ SELECT a1, b, b % a1 FROM t6, t7;
 SELECT a2, b, a2 + b FROM t6, t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT a2, b, a2 - b FROM t6, t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT a2, b, a2 * b FROM t6, t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT a2, b, a2 / b FROM t6, t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT a2, b, a2 % b FROM t6, t7;
  | ---
@@ -3024,22 +3024,22 @@ SELECT a2, b, a2 % b FROM t6, t7;
 SELECT a2, b, b + a2 FROM t6, t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT a2, b, b - a2 FROM t6, t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT a2, b, b * a2 FROM t6, t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT a2, b, b / a2 FROM t6, t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT a2, b, b % a2 FROM t6, t7;
  | ---
@@ -3922,8 +3922,8 @@ SELECT a2, a2 BETWEEN 0 and 10 FROM t6;
  | - 'Type mismatch: can not convert integer(0) to boolean'
  | ...
 
--- Check interaction of BOOLEAN and NUMBER.
-CREATE TABLE t8 (c NUMBER PRIMARY KEY);
+-- Check interaction of BOOLEAN and DOUBLE.
+CREATE TABLE t8 (c DOUBLE PRIMARY KEY);
  | ---
  | - row_count: 1
  | ...
@@ -4023,13 +4023,13 @@ SELECT a2, 2.3 OR a2 FROM t6
 SELECT c, true AND c FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert number(4.56) to boolean'
+ | - 'Type mismatch: can not convert double(4.56) to boolean'
  | ...
 SELECT c, false AND c FROM t8;
  | ---
  | - metadata:
  |   - name: C
- |     type: number
+ |     type: double
  |   - name: COLUMN_1
  |     type: boolean
  |   rows:
@@ -4038,23 +4038,23 @@ SELECT c, false AND c FROM t8;
 SELECT c, true OR c FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert number(4.56) to boolean'
+ | - 'Type mismatch: can not convert double(4.56) to boolean'
  | ...
 SELECT c, false OR c FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert number(4.56) to boolean'
+ | - 'Type mismatch: can not convert double(4.56) to boolean'
  | ...
 SELECT c, c AND true FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert number(4.56) to boolean'
+ | - 'Type mismatch: can not convert double(4.56) to boolean'
  | ...
 SELECT c, c AND false FROM t8;
  | ---
  | - metadata:
  |   - name: C
- |     type: number
+ |     type: double
  |   - name: COLUMN_1
  |     type: boolean
  |   rows:
@@ -4063,94 +4063,94 @@ SELECT c, c AND false FROM t8;
 SELECT c, c OR true FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert number(4.56) to boolean'
+ | - 'Type mismatch: can not convert double(4.56) to boolean'
  | ...
 SELECT c, c OR false FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert number(4.56) to boolean'
+ | - 'Type mismatch: can not convert double(4.56) to boolean'
  | ...
 
 SELECT a1, c, a1 AND c FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert number(4.56) to boolean'
+ | - 'Type mismatch: can not convert double(4.56) to boolean'
  | ...
 SELECT a1, c, a1 OR c FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert number(4.56) to boolean'
+ | - 'Type mismatch: can not convert double(4.56) to boolean'
  | ...
 SELECT a1, c, c AND a1 FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert number(4.56) to boolean'
+ | - 'Type mismatch: can not convert double(4.56) to boolean'
  | ...
 SELECT a1, c, c OR a1 FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert number(4.56) to boolean'
+ | - 'Type mismatch: can not convert double(4.56) to boolean'
  | ...
 SELECT a2, c, a2 AND c FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert number(4.56) to boolean'
+ | - 'Type mismatch: can not convert double(4.56) to boolean'
  | ...
 SELECT a2, c, a2 OR c FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert number(4.56) to boolean'
+ | - 'Type mismatch: can not convert double(4.56) to boolean'
  | ...
 SELECT a2, c, c AND a2 FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert number(4.56) to boolean'
+ | - 'Type mismatch: can not convert double(4.56) to boolean'
  | ...
 SELECT a2, c, c OR a2 FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert number(4.56) to boolean'
+ | - 'Type mismatch: can not convert double(4.56) to boolean'
  | ...
 
 SELECT true + 2.3;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT false + 2.3;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT true - 2.3;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT false - 2.3;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT true * 2.3;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT false * 2.3;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT true / 2.3;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT false / 2.3;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT true % 2.3;
  | ---
@@ -4165,42 +4165,42 @@ SELECT false % 2.3;
 SELECT 2.3 + true;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT 2.3 + false;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT 2.3 - true;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT 2.3 - false;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT 2.3 * true;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT 2.3 * false;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT 2.3 / true;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT 2.3 / false;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT 2.3 % true;
  | ---
@@ -4216,22 +4216,22 @@ SELECT 2.3 % false;
 SELECT a1, a1 + 2.3 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT a1, a1 - 2.3 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT a1, a1 * 2.3 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT a1, a1 / 2.3 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT a1, a1 % 2.3 FROM t6
  | ---
@@ -4241,22 +4241,22 @@ SELECT a1, a1 % 2.3 FROM t6
 SELECT a1, 2.3 + a1 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT a1, 2.3 - a1 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT a1, 2.3 * a1 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT a1, 2.3 / a1 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT a1, 2.3 % a1 FROM t6
  | ---
@@ -4266,22 +4266,22 @@ SELECT a1, 2.3 % a1 FROM t6
 SELECT a2, a2 + 2.3 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT a2, a2 - 2.3 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT a2, a2 * 2.3 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT a2, a2 / 2.3 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT a2, a2 % 2.3 FROM t6
  | ---
@@ -4291,22 +4291,22 @@ SELECT a2, a2 % 2.3 FROM t6
 SELECT a2, 2.3 + a2 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT a2, 2.3 - a2 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT a2, 2.3 * a2 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT a2, 2.3 / a2 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT a2, 2.3 % a2 FROM t6
  | ---
@@ -4317,92 +4317,92 @@ SELECT a2, 2.3 % a2 FROM t6
 SELECT c, true + c FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT c, false + c FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT c, true - c FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT c, false - c FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT c, true * c FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT c, false * c FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT c, true / c FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT c, false / c FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT c, true % c FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert number(4.56) to integer'
+ | - 'Type mismatch: can not convert double(4.56) to integer'
  | ...
 SELECT c, false % c FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert number(4.56) to integer'
+ | - 'Type mismatch: can not convert double(4.56) to integer'
  | ...
 SELECT c, c + true FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT c, c + false FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT c, c - true FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT c, c - false FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT c, c * true FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT c, c * false FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT c, c / true FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT c, c / false FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT c, c % true FROM t8;
  | ---
@@ -4418,47 +4418,47 @@ SELECT c, c % false FROM t8;
 SELECT a1, c, a1 + c FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT a1, c, a1 - c FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT a1, c, a1 * c FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT a1, c, a1 / c FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT a1, c, a1 % c FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert number(4.56) to integer'
+ | - 'Type mismatch: can not convert double(4.56) to integer'
  | ...
 SELECT a1, c, c + a1 FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT a1, c, c - a1 FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT a1, c, c * a1 FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT a1, c, c / a1 FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to integer, unsigned or double'
  | ...
 SELECT a1, c, c % a1 FROM t6, t8;
  | ---
@@ -4468,47 +4468,47 @@ SELECT a1, c, c % a1 FROM t6, t8;
 SELECT a2, c, a2 + c FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT a2, c, a2 - c FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT a2, c, a2 * c FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT a2, c, a2 / c FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT a2, c, a2 % c FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert number(4.56) to integer'
+ | - 'Type mismatch: can not convert double(4.56) to integer'
  | ...
 SELECT a2, c, c + a2 FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT a2, c, c - a2 FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT a2, c, c * a2 FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT a2, c, c / a2 FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to integer, unsigned or double'
  | ...
 SELECT a2, c, c % a2 FROM t6, t8;
  | ---
@@ -4601,22 +4601,22 @@ SELECT a2, 2.3 < a2 FROM t6
 SELECT c, true > c FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert number(4.56) to boolean'
+ | - 'Type mismatch: can not convert double(4.56) to boolean'
  | ...
 SELECT c, false > c FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert number(4.56) to boolean'
+ | - 'Type mismatch: can not convert double(4.56) to boolean'
  | ...
 SELECT c, true < c FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert number(4.56) to boolean'
+ | - 'Type mismatch: can not convert double(4.56) to boolean'
  | ...
 SELECT c, false < c FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert number(4.56) to boolean'
+ | - 'Type mismatch: can not convert double(4.56) to boolean'
  | ...
 SELECT c, c > true FROM t8;
  | ---
@@ -4642,12 +4642,12 @@ SELECT c, c < false FROM t8;
 SELECT a1, c, a1 > c FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert number(4.56) to boolean'
+ | - 'Type mismatch: can not convert double(4.56) to boolean'
  | ...
 SELECT a1, c, a1 < c FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert number(4.56) to boolean'
+ | - 'Type mismatch: can not convert double(4.56) to boolean'
  | ...
 SELECT a1, c, c > a1 FROM t6, t8;
  | ---
@@ -4662,12 +4662,12 @@ SELECT a1, c, c < a1 FROM t6, t8;
 SELECT a2, c, a2 > c FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert number(4.56) to boolean'
+ | - 'Type mismatch: can not convert double(4.56) to boolean'
  | ...
 SELECT a2, c, a2 < c FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert number(4.56) to boolean'
+ | - 'Type mismatch: can not convert double(4.56) to boolean'
  | ...
 SELECT a2, c, c > a2 FROM t6, t8;
  | ---
@@ -4765,22 +4765,22 @@ SELECT a2, 2.3 <= a2 FROM t6
 SELECT c, true >= c FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert number(4.56) to boolean'
+ | - 'Type mismatch: can not convert double(4.56) to boolean'
  | ...
 SELECT c, false >= c FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert number(4.56) to boolean'
+ | - 'Type mismatch: can not convert double(4.56) to boolean'
  | ...
 SELECT c, true <= c FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert number(4.56) to boolean'
+ | - 'Type mismatch: can not convert double(4.56) to boolean'
  | ...
 SELECT c, false <= c FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert number(4.56) to boolean'
+ | - 'Type mismatch: can not convert double(4.56) to boolean'
  | ...
 SELECT c, c >= true FROM t8;
  | ---
@@ -4806,12 +4806,12 @@ SELECT c, c <= false FROM t8;
 SELECT a1, c, a1 >= c FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert number(4.56) to boolean'
+ | - 'Type mismatch: can not convert double(4.56) to boolean'
  | ...
 SELECT a1, c, a1 <= c FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert number(4.56) to boolean'
+ | - 'Type mismatch: can not convert double(4.56) to boolean'
  | ...
 SELECT a1, c, c >= a1 FROM t6, t8;
  | ---
@@ -4826,12 +4826,12 @@ SELECT a1, c, c <= a1 FROM t6, t8;
 SELECT a2, c, a2 >= c FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert number(4.56) to boolean'
+ | - 'Type mismatch: can not convert double(4.56) to boolean'
  | ...
 SELECT a2, c, a2 <= c FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert number(4.56) to boolean'
+ | - 'Type mismatch: can not convert double(4.56) to boolean'
  | ...
 SELECT a2, c, c >= a2 FROM t6, t8;
  | ---
@@ -4929,22 +4929,22 @@ SELECT a2, 2.3 != a2 FROM t6
 SELECT c, true == c FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert number(4.56) to boolean'
+ | - 'Type mismatch: can not convert double(4.56) to boolean'
  | ...
 SELECT c, false == c FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert number(4.56) to boolean'
+ | - 'Type mismatch: can not convert double(4.56) to boolean'
  | ...
 SELECT c, true != c FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert number(4.56) to boolean'
+ | - 'Type mismatch: can not convert double(4.56) to boolean'
  | ...
 SELECT c, false != c FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert number(4.56) to boolean'
+ | - 'Type mismatch: can not convert double(4.56) to boolean'
  | ...
 SELECT c, c == true FROM t8;
  | ---
@@ -4970,12 +4970,12 @@ SELECT c, c != false FROM t8;
 SELECT a1, c, a1 == c FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert number(4.56) to boolean'
+ | - 'Type mismatch: can not convert double(4.56) to boolean'
  | ...
 SELECT a1, c, a1 != c FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert number(4.56) to boolean'
+ | - 'Type mismatch: can not convert double(4.56) to boolean'
  | ...
 SELECT a1, c, c == a1 FROM t6, t8;
  | ---
@@ -4990,12 +4990,12 @@ SELECT a1, c, c != a1 FROM t6, t8;
 SELECT a2, c, a2 == c FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert number(4.56) to boolean'
+ | - 'Type mismatch: can not convert double(4.56) to boolean'
  | ...
 SELECT a2, c, a2 != c FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert number(4.56) to boolean'
+ | - 'Type mismatch: can not convert double(4.56) to boolean'
  | ...
 SELECT a2, c, c == a2 FROM t6, t8;
  | ---
@@ -5043,22 +5043,22 @@ SELECT a2 IN (0.1, 1.2, 2.3, 3.4) FROM t6 LIMIT 1;
 SELECT true IN (SELECT c FROM t8);
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to double'
  | ...
 SELECT false IN (SELECT c FROM t8);
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to double'
  | ...
 SELECT a1 IN (SELECT c FROM t8) FROM t6 LIMIT 1;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Type mismatch: can not convert boolean(FALSE) to double'
  | ...
 SELECT a2 IN (SELECT c FROM t8) FROM t6 LIMIT 1;
  | ---
  | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to number'
+ | - 'Type mismatch: can not convert boolean(TRUE) to double'
  | ...
 
 SELECT true BETWEEN 0.1 and 9.9;
diff --git a/test/sql/boolean.test.sql b/test/sql/boolean.test.sql
index 13991ca0c..12e83a4bb 100644
--- a/test/sql/boolean.test.sql
+++ b/test/sql/boolean.test.sql
@@ -752,8 +752,8 @@ SELECT false BETWEEN 0 and 10;
 SELECT a1, a1 BETWEEN 0 and 10 FROM t6;
 SELECT a2, a2 BETWEEN 0 and 10 FROM t6;
 
--- Check interaction of BOOLEAN and NUMBER.
-CREATE TABLE t8 (c NUMBER PRIMARY KEY);
+-- Check interaction of BOOLEAN and DOUBLE.
+CREATE TABLE t8 (c DOUBLE PRIMARY KEY);
 INSERT INTO t8 VALUES (4.56);
 
 SELECT true AND 2.3;
diff --git a/test/sql/prepared.result b/test/sql/prepared.result
index 0db2cc03f..ecc16f597 100644
--- a/test/sql/prepared.result
+++ b/test/sql/prepared.result
@@ -419,7 +419,7 @@ unprepare(s.stmt_id)
  | - null
  | ...
 
-s = prepare("SELECT count(*), count(a - 3), max(b), abs(id) FROM test WHERE b = '3';")
+s = prepare("SELECT count(*), count(id - 3), max(b), abs(id) FROM test WHERE b = '3';")
  | ---
  | ...
 execute(s.stmt_id)
diff --git a/test/sql/prepared.test.lua b/test/sql/prepared.test.lua
index d8e8a44cb..0a1fdebae 100644
--- a/test/sql/prepared.test.lua
+++ b/test/sql/prepared.test.lua
@@ -155,7 +155,7 @@ end;
 test_run:cmd("setopt delimiter ''");
 unprepare(s.stmt_id)
 
-s = prepare("SELECT count(*), count(a - 3), max(b), abs(id) FROM test WHERE b = '3';")
+s = prepare("SELECT count(*), count(id - 3), max(b), abs(id) FROM test WHERE b = '3';")
 execute(s.stmt_id)
 execute(s.stmt_id)
 unprepare(s.stmt_id)
diff --git a/test/sql/types.result b/test/sql/types.result
index 592b1aa19..68bdcd62e 100644
--- a/test/sql/types.result
+++ b/test/sql/types.result
@@ -311,7 +311,8 @@ box.execute('SELECT 1 + 1.1;')
 box.execute('SELECT \'9223372036854\' + 1;')
 ---
 - null
-- 'Type mismatch: can not convert string(''9223372036854'') to number'
+- 'Type mismatch: can not convert string(''9223372036854'') to integer, unsigned or
+  double'
 ...
 -- Fix BOOLEAN bindings.
 box.execute('SELECT ?', {true})
-- 
2.25.1


^ permalink raw reply	[flat|nested] 14+ messages in thread

* [Tarantool-patches] [PATCH v1 5/7] sql: disallow bitwise for NUMBER and SCALAR
  2021-08-13  3:12 [Tarantool-patches] [PATCH v1 0/7] Rework SCALAR and NUMBER types in SQL Mergen Imeev via Tarantool-patches
                   ` (3 preceding siblings ...)
  2021-08-13  3:13 ` [Tarantool-patches] [PATCH v1 4/7] sql: disallow arithmetic for " Mergen Imeev via Tarantool-patches
@ 2021-08-13  3:13 ` Mergen Imeev via Tarantool-patches
  2021-08-13  3:13 ` [Tarantool-patches] [PATCH v1 6/7] sql: disallow concatination for SCALAR Mergen Imeev via Tarantool-patches
                   ` (3 subsequent siblings)
  8 siblings, 0 replies; 14+ messages in thread
From: Mergen Imeev via Tarantool-patches @ 2021-08-13  3:13 UTC (permalink / raw)
  To: vdavydov; +Cc: tarantool-patches

Part of #6221
---
 src/box/sql/mem.c               | 58 ++++++++++++---------------------
 test/sql-tap/metatypes.test.lua | 19 ++++++++++-
 2 files changed, 39 insertions(+), 38 deletions(-)

diff --git a/src/box/sql/mem.c b/src/box/sql/mem.c
index e2c85e2ea..e34f24c96 100644
--- a/src/box/sql/mem.c
+++ b/src/box/sql/mem.c
@@ -1759,6 +1759,22 @@ mem_rem(const struct Mem *left, const struct Mem *right, struct Mem *result)
 	return 0;
 }
 
+static inline int
+check_types_unsigned_bitwise(const struct Mem *a, const struct Mem *b)
+{
+	if (a->type != MEM_TYPE_UINT || mem_is_metatype(a)) {
+		diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(a),
+			 "unsigned");
+		return -1;
+	}
+	if (b->type != MEM_TYPE_UINT || mem_is_metatype(b)) {
+		diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(b),
+			 "unsigned");
+		return -1;
+	}
+	return 0;
+}
+
 int
 mem_bit_and(const struct Mem *left, const struct Mem *right, struct Mem *result)
 {
@@ -1766,16 +1782,8 @@ mem_bit_and(const struct Mem *left, const struct Mem *right, struct Mem *result)
 		mem_set_null(result);
 		return 0;
 	}
-	if (right->type != MEM_TYPE_UINT) {
-		diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(right),
-			 "unsigned");
-		return -1;
-	}
-	if (left->type != MEM_TYPE_UINT) {
-		diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(left),
-			 "unsigned");
+	if (check_types_unsigned_bitwise(right, left) != 0)
 		return -1;
-	}
 	mem_set_uint(result, left->u.u & right->u.u);
 	return 0;
 }
@@ -1787,16 +1795,8 @@ mem_bit_or(const struct Mem *left, const struct Mem *right, struct Mem *result)
 		mem_set_null(result);
 		return 0;
 	}
-	if (right->type != MEM_TYPE_UINT) {
-		diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(right),
-			 "unsigned");
-		return -1;
-	}
-	if (left->type != MEM_TYPE_UINT) {
-		diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(left),
-			 "unsigned");
+	if (check_types_unsigned_bitwise(right, left) != 0)
 		return -1;
-	}
 	mem_set_uint(result, left->u.u | right->u.u);
 	return 0;
 }
@@ -1809,16 +1809,8 @@ mem_shift_left(const struct Mem *left, const struct Mem *right,
 		mem_set_null(result);
 		return 0;
 	}
-	if (right->type != MEM_TYPE_UINT) {
-		diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(right),
-			 "unsigned");
-		return -1;
-	}
-	if (left->type != MEM_TYPE_UINT) {
-		diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(left),
-			 "unsigned");
+	if (check_types_unsigned_bitwise(right, left) != 0)
 		return -1;
-	}
 	mem_set_uint(result, right->u.u >= 64 ? 0 : left->u.u << right->u.u);
 	return 0;
 }
@@ -1831,16 +1823,8 @@ mem_shift_right(const struct Mem *left, const struct Mem *right,
 		mem_set_null(result);
 		return 0;
 	}
-	if (right->type != MEM_TYPE_UINT) {
-		diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(right),
-			 "unsigned");
-		return -1;
-	}
-	if (left->type != MEM_TYPE_UINT) {
-		diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(left),
-			 "unsigned");
+	if (check_types_unsigned_bitwise(right, left) != 0)
 		return -1;
-	}
 	mem_set_uint(result, right->u.u >= 64 ? 0 : left->u.u >> right->u.u);
 	return 0;
 }
@@ -1852,7 +1836,7 @@ mem_bit_not(const struct Mem *mem, struct Mem *result)
 		mem_set_null(result);
 		return 0;
 	}
-	if (mem->type != MEM_TYPE_UINT) {
+	if (mem->type != MEM_TYPE_UINT || mem_is_metatype(mem)) {
 		diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(mem),
 			 "unsigned");
 		return -1;
diff --git a/test/sql-tap/metatypes.test.lua b/test/sql-tap/metatypes.test.lua
index c0b2a4d03..50d700cc4 100755
--- a/test/sql-tap/metatypes.test.lua
+++ b/test/sql-tap/metatypes.test.lua
@@ -1,6 +1,6 @@
 #!/usr/bin/env tarantool
 local test = require("sqltester")
-test:plan(10)
+test:plan(12)
 
 -- Check that SCALAR and NUMBER meta-types works as intended.
 box.execute([[CREATE TABLE t (i INT PRIMARY KEY, s SCALAR, n NUMBER);]])
@@ -107,6 +107,23 @@ test:do_catchsql_test(
         1, "Type mismatch: can not convert scalar(1) to integer, unsigned or double"
     })
 
+-- Check that bitwise operations are prohibited for NUMBER and SCALAR values.
+test:do_catchsql_test(
+    "metatypes-4.1",
+    [[
+        SELECT 1 & CAST(1 AS NUMBER);
+    ]], {
+        1, "Type mismatch: can not convert number(1) to unsigned"
+    })
+
+test:do_catchsql_test(
+    "metatypes-4.2",
+    [[
+        SELECT CAST(1 AS SCALAR) >> 1;
+    ]], {
+        1, "Type mismatch: can not convert scalar(1) to unsigned"
+    })
+
 box.execute([[DROP TABLE t;]])
 
 test:finish_test()
-- 
2.25.1


^ permalink raw reply	[flat|nested] 14+ messages in thread

* [Tarantool-patches] [PATCH v1 6/7] sql: disallow concatination for SCALAR
  2021-08-13  3:12 [Tarantool-patches] [PATCH v1 0/7] Rework SCALAR and NUMBER types in SQL Mergen Imeev via Tarantool-patches
                   ` (4 preceding siblings ...)
  2021-08-13  3:13 ` [Tarantool-patches] [PATCH v1 5/7] sql: disallow bitwise " Mergen Imeev via Tarantool-patches
@ 2021-08-13  3:13 ` Mergen Imeev via Tarantool-patches
  2021-08-13  3:13 ` [Tarantool-patches] [PATCH v1 7/7] sql: fix comparison with SCALAR value Mergen Imeev via Tarantool-patches
                   ` (2 subsequent siblings)
  8 siblings, 0 replies; 14+ messages in thread
From: Mergen Imeev via Tarantool-patches @ 2021-08-13  3:13 UTC (permalink / raw)
  To: vdavydov; +Cc: tarantool-patches

Part of #6221
---
 src/box/sql/mem.c               |  6 ++++--
 test/sql-tap/metatypes.test.lua | 11 ++++++++++-
 2 files changed, 14 insertions(+), 3 deletions(-)

diff --git a/src/box/sql/mem.c b/src/box/sql/mem.c
index e34f24c96..732d1b012 100644
--- a/src/box/sql/mem.c
+++ b/src/box/sql/mem.c
@@ -1552,12 +1552,14 @@ mem_concat(struct Mem *a, struct Mem *b, struct Mem *result)
 		return 0;
 	}
 	/* Concatenation operation can be applied only to strings and blobs. */
-	if (((b->type & (MEM_TYPE_STR | MEM_TYPE_BIN)) == 0)) {
+	if (((b->type & (MEM_TYPE_STR | MEM_TYPE_BIN)) == 0) ||
+	    mem_is_metatype(b)) {
 		diag_set(ClientError, ER_INCONSISTENT_TYPES,
 			 "string or varbinary", mem_str(b));
 		return -1;
 	}
-	if (((a->type & (MEM_TYPE_STR | MEM_TYPE_BIN)) == 0)) {
+	if (((a->type & (MEM_TYPE_STR | MEM_TYPE_BIN)) == 0) ||
+	    mem_is_metatype(a)) {
 		diag_set(ClientError, ER_INCONSISTENT_TYPES,
 			 "string or varbinary", mem_str(a));
 		return -1;
diff --git a/test/sql-tap/metatypes.test.lua b/test/sql-tap/metatypes.test.lua
index 50d700cc4..b767e3f31 100755
--- a/test/sql-tap/metatypes.test.lua
+++ b/test/sql-tap/metatypes.test.lua
@@ -1,6 +1,6 @@
 #!/usr/bin/env tarantool
 local test = require("sqltester")
-test:plan(12)
+test:plan(13)
 
 -- Check that SCALAR and NUMBER meta-types works as intended.
 box.execute([[CREATE TABLE t (i INT PRIMARY KEY, s SCALAR, n NUMBER);]])
@@ -124,6 +124,15 @@ test:do_catchsql_test(
         1, "Type mismatch: can not convert scalar(1) to unsigned"
     })
 
+-- Check that concatination is prohibited for SCALAR values.
+test:do_catchsql_test(
+    "metatypes-5",
+    [[
+        SELECT CAST('asd' AS SCALAR) || 'dsa';
+    ]], {
+        1, "Inconsistent types: expected string or varbinary got scalar('asd')"
+    })
+
 box.execute([[DROP TABLE t;]])
 
 test:finish_test()
-- 
2.25.1


^ permalink raw reply	[flat|nested] 14+ messages in thread

* [Tarantool-patches] [PATCH v1 7/7] sql: fix comparison with SCALAR value
  2021-08-13  3:12 [Tarantool-patches] [PATCH v1 0/7] Rework SCALAR and NUMBER types in SQL Mergen Imeev via Tarantool-patches
                   ` (5 preceding siblings ...)
  2021-08-13  3:13 ` [Tarantool-patches] [PATCH v1 6/7] sql: disallow concatination for SCALAR Mergen Imeev via Tarantool-patches
@ 2021-08-13  3:13 ` Mergen Imeev via Tarantool-patches
  2021-08-13 16:08 ` [Tarantool-patches] [PATCH v1 0/7] Rework SCALAR and NUMBER types in SQL Vladimir Davydov via Tarantool-patches
  2021-08-18 12:29 ` Kirill Yukhin via Tarantool-patches
  8 siblings, 0 replies; 14+ messages in thread
From: Mergen Imeev via Tarantool-patches @ 2021-08-13  3:13 UTC (permalink / raw)
  To: vdavydov; +Cc: tarantool-patches

After this patch, SCALAR values will be able to be compared with values
of any other scalar type. The comparison will be done according to the
SCALAR rules, which means boolean values < numeric values < string
values < binary values < uuid values.

Closes #6221

@TarantoolBot document
Title: SCALAR and NUMBER values in SQL

SCALAR values can now be compared with values of any other scalar type,
but cannot be implicitly cast to any other scalar type. This means that
SCALAR values cannot participate in arithmetic, bitwise operations,
concatenation, or functions that, by definition, do not accept SCALAR
values.

NUMBER values now also cannot be implicitly cast to any other numeric
type, which means that NUMBER values cannot participate in arithmetic
and bitwise operations, or in functions that, by definition, do not
accept NUMBER values.
---
 ...21-re-introduce-scalar-and-number-types.md |  8 +++
 src/box/sql/mem.c                             |  4 ++
 test/sql-tap/cast.test.lua                    |  4 +-
 test/sql-tap/metatypes.test.lua               | 51 ++++++++++++++++++-
 test/sql/boolean.result                       | 14 +++--
 5 files changed, 74 insertions(+), 7 deletions(-)
 create mode 100644 changelogs/unreleased/gh-6221-re-introduce-scalar-and-number-types.md

diff --git a/changelogs/unreleased/gh-6221-re-introduce-scalar-and-number-types.md b/changelogs/unreleased/gh-6221-re-introduce-scalar-and-number-types.md
new file mode 100644
index 000000000..842e8a4e2
--- /dev/null
+++ b/changelogs/unreleased/gh-6221-re-introduce-scalar-and-number-types.md
@@ -0,0 +1,8 @@
+## feature/sql
+
+* The SCALAR and NUMBER types have been reworked in SQL. Now SCALAR values
+cannot be implicitly cast to any other scalar type, and NUMBER values cannot be
+implicitly cast to any other numeric type. This means that arithmetic and
+bitwise operations and concatenation are no longer allowed for SCALAR and NUMBER
+values. In addition, any SCALAR value can now be compared with values of any
+other scalar type using the SCALAR rules (gh-6221).
diff --git a/src/box/sql/mem.c b/src/box/sql/mem.c
index 732d1b012..066940fac 100644
--- a/src/box/sql/mem.c
+++ b/src/box/sql/mem.c
@@ -2062,6 +2062,10 @@ mem_cmp(const struct Mem *a, const struct Mem *b, int *result,
 			*result = 1;
 		return 0;
 	}
+	if (((a->flags | b->flags) & MEM_Scalar) != 0) {
+		*result = mem_cmp_scalar(a, b, coll);
+		return 0;
+	}
 	if (class_a != class_b) {
 		diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(b),
 			 mem_type_class_to_str(a));
diff --git a/test/sql-tap/cast.test.lua b/test/sql-tap/cast.test.lua
index e0295fd3b..68d2ae585 100755
--- a/test/sql-tap/cast.test.lua
+++ b/test/sql-tap/cast.test.lua
@@ -1157,12 +1157,12 @@ test:do_execsql_test(
     })
 
 -- Make sure that there is no unnecessary implicit casts in IN operator.
-test:do_catchsql_test(
+test:do_execsql_test(
     "cast-12",
     [[
         SELECT 1 IN (SELECT '1');
     ]], {
-        1, "Type mismatch: can not convert integer(1) to string"
+        false
     })
 
 test:finish_test()
diff --git a/test/sql-tap/metatypes.test.lua b/test/sql-tap/metatypes.test.lua
index b767e3f31..349c670ba 100755
--- a/test/sql-tap/metatypes.test.lua
+++ b/test/sql-tap/metatypes.test.lua
@@ -1,6 +1,6 @@
 #!/usr/bin/env tarantool
 local test = require("sqltester")
-test:plan(13)
+test:plan(19)
 
 -- Check that SCALAR and NUMBER meta-types works as intended.
 box.execute([[CREATE TABLE t (i INT PRIMARY KEY, s SCALAR, n NUMBER);]])
@@ -133,6 +133,55 @@ test:do_catchsql_test(
         1, "Inconsistent types: expected string or varbinary got scalar('asd')"
     })
 
+-- Check that SCALAR values can be compared to values of any other scalar type.
+test:do_execsql_test(
+    "metatypes-6.1",
+    [[
+        SELECT s > false FROM t;
+    ]], {
+        true, true, true, true, true, true
+    })
+
+test:do_execsql_test(
+    "metatypes-6.2",
+    [[
+        SELECT s = 1 FROM t;
+    ]], {
+        true, false, false, false, false, false
+    })
+
+test:do_execsql_test(
+    "metatypes-6.3",
+    [[
+        SELECT s != 1.5 FROM t;
+    ]], {
+        true, true, true, true, true, true
+    })
+
+test:do_execsql_test(
+    "metatypes-6.4",
+    [[
+        SELECT s <= 'abc' FROM t;
+    ]], {
+        true, true, true, true, false, false
+    })
+
+test:do_execsql_test(
+    "metatypes-6.5",
+    [[
+        SELECT s < x'10' FROM t;
+    ]], {
+        true, true, true, true, false, false
+    })
+
+test:do_execsql_test(
+    "metatypes-6.6",
+    [[
+        SELECT s > CAST('11111111-1111-1111-1111-111111111110' AS UUID) FROM t;
+    ]], {
+        false, false, false, false, false, true
+    })
+
 box.execute([[DROP TABLE t;]])
 
 test:finish_test()
diff --git a/test/sql/boolean.result b/test/sql/boolean.result
index a8400ee49..a9ce37e11 100644
--- a/test/sql/boolean.result
+++ b/test/sql/boolean.result
@@ -137,13 +137,19 @@ INSERT INTO ts(s) VALUES ('abc'), (12.5);
  | ...
 SELECT s FROM ts WHERE s = true;
  | ---
- | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to string'
+ | - metadata:
+ |   - name: S
+ |     type: scalar
+ |   rows:
+ |   - [true]
  | ...
 SELECT s FROM ts WHERE s < true;
  | ---
- | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to string'
+ | - metadata:
+ |   - name: S
+ |     type: scalar
+ |   rows:
+ |   - [false]
  | ...
 SELECT s FROM ts WHERE s IN (true, 1, 'abcd');
  | ---
-- 
2.25.1


^ permalink raw reply	[flat|nested] 14+ messages in thread

* Re: [Tarantool-patches] [PATCH v1 0/7] Rework SCALAR and NUMBER types in SQL
  2021-08-13  3:12 [Tarantool-patches] [PATCH v1 0/7] Rework SCALAR and NUMBER types in SQL Mergen Imeev via Tarantool-patches
                   ` (6 preceding siblings ...)
  2021-08-13  3:13 ` [Tarantool-patches] [PATCH v1 7/7] sql: fix comparison with SCALAR value Mergen Imeev via Tarantool-patches
@ 2021-08-13 16:08 ` Vladimir Davydov via Tarantool-patches
  2021-08-17 11:30   ` Vitaliia Ioffe via Tarantool-patches
  2021-08-18 12:29 ` Kirill Yukhin via Tarantool-patches
  8 siblings, 1 reply; 14+ messages in thread
From: Vladimir Davydov via Tarantool-patches @ 2021-08-13 16:08 UTC (permalink / raw)
  To: imeevma; +Cc: tarantool-patches

On Fri, Aug 13, 2021 at 06:12:52AM +0300, imeevma@tarantool.org wrote:
> This patch reworks SCALAR and NUMBER types. Afther these patches SCANAR and
> NUMBER values will follow newly defined rules. However, only one SQL built-in
> function is actually changed in this patch. All other functions that should be
> changed will be reworked in issue #6105.
> 
> https://github.com/tarantool/tarantool/issues/6221
> https://github.com/tarantool/tarantool/tree/imeevma/gh-6221-introduce-mem-type-number
> 
> Mergen Imeev (7):
>   sql: remove enum field_type from struct Mem
>   sql: re-introduce NUMBER and SCALAR meta-types
>   sql: disallow implicit cast from NUMBER and SCALAR
>   sql: disallow arithmetic for NUMBER and SCALAR
>   sql: disallow bitwise for NUMBER and SCALAR
>   sql: disallow concatination for SCALAR
>   sql: fix comparison with SCALAR value

LGTM

^ permalink raw reply	[flat|nested] 14+ messages in thread

* Re: [Tarantool-patches]  [PATCH v1 0/7] Rework SCALAR and NUMBER types in SQL
  2021-08-13 16:08 ` [Tarantool-patches] [PATCH v1 0/7] Rework SCALAR and NUMBER types in SQL Vladimir Davydov via Tarantool-patches
@ 2021-08-17 11:30   ` Vitaliia Ioffe via Tarantool-patches
  0 siblings, 0 replies; 14+ messages in thread
From: Vitaliia Ioffe via Tarantool-patches @ 2021-08-17 11:30 UTC (permalink / raw)
  To: Vladimir Davydov; +Cc: tarantool-patches

[-- Attachment #1: Type: text/plain, Size: 1051 bytes --]


QA lgtm
 
 
--
Vitaliia Ioffe
 
  
>Пятница, 13 августа 2021, 19:08 +03:00 от Vladimir Davydov via Tarantool-patches <tarantool-patches@dev.tarantool.org>:
> 
>On Fri, Aug 13, 2021 at 06:12:52AM +0300,  imeevma@tarantool.org wrote:
>> This patch reworks SCALAR and NUMBER types. Afther these patches SCANAR and
>> NUMBER values will follow newly defined rules. However, only one SQL built-in
>> function is actually changed in this patch. All other functions that should be
>> changed will be reworked in issue #6105.
>>
>>  https://github.com/tarantool/tarantool/issues/6221
>>  https://github.com/tarantool/tarantool/tree/imeevma/gh-6221-introduce-mem-type-number
>>
>> Mergen Imeev (7):
>> sql: remove enum field_type from struct Mem
>> sql: re-introduce NUMBER and SCALAR meta-types
>> sql: disallow implicit cast from NUMBER and SCALAR
>> sql: disallow arithmetic for NUMBER and SCALAR
>> sql: disallow bitwise for NUMBER and SCALAR
>> sql: disallow concatination for SCALAR
>> sql: fix comparison with SCALAR value
>LGTM
 

[-- Attachment #2: Type: text/html, Size: 1843 bytes --]

^ permalink raw reply	[flat|nested] 14+ messages in thread

* Re: [Tarantool-patches] [PATCH v1 0/7] Rework SCALAR and NUMBER types in SQL
  2021-08-13  3:12 [Tarantool-patches] [PATCH v1 0/7] Rework SCALAR and NUMBER types in SQL Mergen Imeev via Tarantool-patches
                   ` (7 preceding siblings ...)
  2021-08-13 16:08 ` [Tarantool-patches] [PATCH v1 0/7] Rework SCALAR and NUMBER types in SQL Vladimir Davydov via Tarantool-patches
@ 2021-08-18 12:29 ` Kirill Yukhin via Tarantool-patches
  8 siblings, 0 replies; 14+ messages in thread
From: Kirill Yukhin via Tarantool-patches @ 2021-08-18 12:29 UTC (permalink / raw)
  To: imeevma; +Cc: tarantool-patches

Hello,

On 13 авг 06:12, Mergen Imeev via Tarantool-patches wrote:
> This patch reworks SCALAR and NUMBER types. Afther these patches SCANAR and
> NUMBER values will follow newly defined rules. However, only one SQL built-in
> function is actually changed in this patch. All other functions that should be
> changed will be reworked in issue #6105.
> 
> https://github.com/tarantool/tarantool/issues/6221
> https://github.com/tarantool/tarantool/tree/imeevma/gh-6221-introduce-mem-type-number

LGTM.

I've checked your patchset into master.

--
Regards, Kirill Yukhin

^ permalink raw reply	[flat|nested] 14+ messages in thread

* Re: [Tarantool-patches] [PATCH v1 7/7] sql: fix comparison with SCALAR value
  2021-08-12 18:50   ` Vladislav Shpilevoy via Tarantool-patches
@ 2021-08-12 22:23     ` Mergen Imeev via Tarantool-patches
  0 siblings, 0 replies; 14+ messages in thread
From: Mergen Imeev via Tarantool-patches @ 2021-08-12 22:23 UTC (permalink / raw)
  To: Vladislav Shpilevoy; +Cc: tarantool-patches

On Thu, Aug 12, 2021 at 09:50:35PM +0300, Vladislav Shpilevoy wrote:
> Thanks for the patch!
> 
> On 11.08.2021 19:01, Mergen Imeev via Tarantool-patches wrote:
> > After this patch, SCALAR values will be able to be compared with values
> > of any other scalar type. The comparison will be done according to the
> > SCALAR rules, which means boolean values < numeric values < string
> > values < binary values < uuid values.
> > 
> > Closes #6221
> 
> Could you please add a docbot request?

Fixed:


commit 6179b16e422a1349ebe5b0ed340c81d8f5a4e6a2
Author: Mergen Imeev <imeevma@gmail.com>
Date:   Wed Aug 11 14:58:23 2021 +0300

    sql: fix comparison with SCALAR value
    
    After this patch, SCALAR values will be able to be compared with values
    of any other scalar type. The comparison will be done according to the
    SCALAR rules, which means boolean values < numeric values < string
    values < binary values < uuid values.
    
    Closes #6221
    
    @TarantoolBot document
    Title: SCALAR and NUMBER values in SQL
    
    SCALAR values can now be compared with values of any other scalar type,
    but cannot be implicitly cast to any other scalar type. This means that
    SCALAR values cannot participate in arithmetic, bitwise operations,
    concatenation, or functions that, by definition, do not accept SCALAR
    values.
    
    NUMBER values now also cannot be implicitly cast to any other numeric
    type, which means that NUMBER values cannot participate in arithmetic
    and bitwise operations, or in functions that, by definition, do not
    accept NUMBER values.

^ permalink raw reply	[flat|nested] 14+ messages in thread

* Re: [Tarantool-patches] [PATCH v1 7/7] sql: fix comparison with SCALAR value
  2021-08-11 16:01 ` [Tarantool-patches] [PATCH v1 7/7] sql: fix comparison with SCALAR value Mergen Imeev via Tarantool-patches
@ 2021-08-12 18:50   ` Vladislav Shpilevoy via Tarantool-patches
  2021-08-12 22:23     ` Mergen Imeev via Tarantool-patches
  0 siblings, 1 reply; 14+ messages in thread
From: Vladislav Shpilevoy via Tarantool-patches @ 2021-08-12 18:50 UTC (permalink / raw)
  To: imeevma; +Cc: tarantool-patches

Thanks for the patch!

On 11.08.2021 19:01, Mergen Imeev via Tarantool-patches wrote:
> After this patch, SCALAR values will be able to be compared with values
> of any other scalar type. The comparison will be done according to the
> SCALAR rules, which means boolean values < numeric values < string
> values < binary values < uuid values.
> 
> Closes #6221

Could you please add a docbot request?

^ permalink raw reply	[flat|nested] 14+ messages in thread

* [Tarantool-patches] [PATCH v1 7/7] sql: fix comparison with SCALAR value
  2021-08-11 16:01 Mergen Imeev via Tarantool-patches
@ 2021-08-11 16:01 ` Mergen Imeev via Tarantool-patches
  2021-08-12 18:50   ` Vladislav Shpilevoy via Tarantool-patches
  0 siblings, 1 reply; 14+ messages in thread
From: Mergen Imeev via Tarantool-patches @ 2021-08-11 16:01 UTC (permalink / raw)
  To: v.shpilevoy; +Cc: tarantool-patches

After this patch, SCALAR values will be able to be compared with values
of any other scalar type. The comparison will be done according to the
SCALAR rules, which means boolean values < numeric values < string
values < binary values < uuid values.

Closes #6221
---
 ...21-re-introduce-scalar-and-number-types.md |  8 +++
 src/box/sql/mem.c                             |  4 ++
 test/sql-tap/cast.test.lua                    |  4 +-
 test/sql-tap/metatypes.test.lua               | 51 ++++++++++++++++++-
 test/sql/boolean.result                       | 14 +++--
 5 files changed, 74 insertions(+), 7 deletions(-)
 create mode 100644 changelogs/unreleased/gh-6221-re-introduce-scalar-and-number-types.md

diff --git a/changelogs/unreleased/gh-6221-re-introduce-scalar-and-number-types.md b/changelogs/unreleased/gh-6221-re-introduce-scalar-and-number-types.md
new file mode 100644
index 000000000..842e8a4e2
--- /dev/null
+++ b/changelogs/unreleased/gh-6221-re-introduce-scalar-and-number-types.md
@@ -0,0 +1,8 @@
+## feature/sql
+
+* The SCALAR and NUMBER types have been reworked in SQL. Now SCALAR values
+cannot be implicitly cast to any other scalar type, and NUMBER values cannot be
+implicitly cast to any other numeric type. This means that arithmetic and
+bitwise operations and concatenation are no longer allowed for SCALAR and NUMBER
+values. In addition, any SCALAR value can now be compared with values of any
+other scalar type using the SCALAR rules (gh-6221).
diff --git a/src/box/sql/mem.c b/src/box/sql/mem.c
index 732d1b012..066940fac 100644
--- a/src/box/sql/mem.c
+++ b/src/box/sql/mem.c
@@ -2062,6 +2062,10 @@ mem_cmp(const struct Mem *a, const struct Mem *b, int *result,
 			*result = 1;
 		return 0;
 	}
+	if (((a->flags | b->flags) & MEM_Scalar) != 0) {
+		*result = mem_cmp_scalar(a, b, coll);
+		return 0;
+	}
 	if (class_a != class_b) {
 		diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(b),
 			 mem_type_class_to_str(a));
diff --git a/test/sql-tap/cast.test.lua b/test/sql-tap/cast.test.lua
index e0295fd3b..68d2ae585 100755
--- a/test/sql-tap/cast.test.lua
+++ b/test/sql-tap/cast.test.lua
@@ -1157,12 +1157,12 @@ test:do_execsql_test(
     })
 
 -- Make sure that there is no unnecessary implicit casts in IN operator.
-test:do_catchsql_test(
+test:do_execsql_test(
     "cast-12",
     [[
         SELECT 1 IN (SELECT '1');
     ]], {
-        1, "Type mismatch: can not convert integer(1) to string"
+        false
     })
 
 test:finish_test()
diff --git a/test/sql-tap/metatypes.test.lua b/test/sql-tap/metatypes.test.lua
index b767e3f31..349c670ba 100755
--- a/test/sql-tap/metatypes.test.lua
+++ b/test/sql-tap/metatypes.test.lua
@@ -1,6 +1,6 @@
 #!/usr/bin/env tarantool
 local test = require("sqltester")
-test:plan(13)
+test:plan(19)
 
 -- Check that SCALAR and NUMBER meta-types works as intended.
 box.execute([[CREATE TABLE t (i INT PRIMARY KEY, s SCALAR, n NUMBER);]])
@@ -133,6 +133,55 @@ test:do_catchsql_test(
         1, "Inconsistent types: expected string or varbinary got scalar('asd')"
     })
 
+-- Check that SCALAR values can be compared to values of any other scalar type.
+test:do_execsql_test(
+    "metatypes-6.1",
+    [[
+        SELECT s > false FROM t;
+    ]], {
+        true, true, true, true, true, true
+    })
+
+test:do_execsql_test(
+    "metatypes-6.2",
+    [[
+        SELECT s = 1 FROM t;
+    ]], {
+        true, false, false, false, false, false
+    })
+
+test:do_execsql_test(
+    "metatypes-6.3",
+    [[
+        SELECT s != 1.5 FROM t;
+    ]], {
+        true, true, true, true, true, true
+    })
+
+test:do_execsql_test(
+    "metatypes-6.4",
+    [[
+        SELECT s <= 'abc' FROM t;
+    ]], {
+        true, true, true, true, false, false
+    })
+
+test:do_execsql_test(
+    "metatypes-6.5",
+    [[
+        SELECT s < x'10' FROM t;
+    ]], {
+        true, true, true, true, false, false
+    })
+
+test:do_execsql_test(
+    "metatypes-6.6",
+    [[
+        SELECT s > CAST('11111111-1111-1111-1111-111111111110' AS UUID) FROM t;
+    ]], {
+        false, false, false, false, false, true
+    })
+
 box.execute([[DROP TABLE t;]])
 
 test:finish_test()
diff --git a/test/sql/boolean.result b/test/sql/boolean.result
index a8400ee49..a9ce37e11 100644
--- a/test/sql/boolean.result
+++ b/test/sql/boolean.result
@@ -137,13 +137,19 @@ INSERT INTO ts(s) VALUES ('abc'), (12.5);
  | ...
 SELECT s FROM ts WHERE s = true;
  | ---
- | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to string'
+ | - metadata:
+ |   - name: S
+ |     type: scalar
+ |   rows:
+ |   - [true]
  | ...
 SELECT s FROM ts WHERE s < true;
  | ---
- | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to string'
+ | - metadata:
+ |   - name: S
+ |     type: scalar
+ |   rows:
+ |   - [false]
  | ...
 SELECT s FROM ts WHERE s IN (true, 1, 'abcd');
  | ---
-- 
2.25.1


^ permalink raw reply	[flat|nested] 14+ messages in thread

end of thread, other threads:[~2021-08-18 12:29 UTC | newest]

Thread overview: 14+ messages (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
2021-08-13  3:12 [Tarantool-patches] [PATCH v1 0/7] Rework SCALAR and NUMBER types in SQL Mergen Imeev via Tarantool-patches
2021-08-13  3:12 ` [Tarantool-patches] [PATCH v1 1/7] sql: remove enum field_type from struct Mem Mergen Imeev via Tarantool-patches
2021-08-13  3:12 ` [Tarantool-patches] [PATCH v1 2/7] sql: re-introduce NUMBER and SCALAR meta-types Mergen Imeev via Tarantool-patches
2021-08-13  3:12 ` [Tarantool-patches] [PATCH v1 3/7] sql: disallow implicit cast from NUMBER and SCALAR Mergen Imeev via Tarantool-patches
2021-08-13  3:13 ` [Tarantool-patches] [PATCH v1 4/7] sql: disallow arithmetic for " Mergen Imeev via Tarantool-patches
2021-08-13  3:13 ` [Tarantool-patches] [PATCH v1 5/7] sql: disallow bitwise " Mergen Imeev via Tarantool-patches
2021-08-13  3:13 ` [Tarantool-patches] [PATCH v1 6/7] sql: disallow concatination for SCALAR Mergen Imeev via Tarantool-patches
2021-08-13  3:13 ` [Tarantool-patches] [PATCH v1 7/7] sql: fix comparison with SCALAR value Mergen Imeev via Tarantool-patches
2021-08-13 16:08 ` [Tarantool-patches] [PATCH v1 0/7] Rework SCALAR and NUMBER types in SQL Vladimir Davydov via Tarantool-patches
2021-08-17 11:30   ` Vitaliia Ioffe via Tarantool-patches
2021-08-18 12:29 ` Kirill Yukhin via Tarantool-patches
  -- strict thread matches above, loose matches on Subject: below --
2021-08-11 16:01 Mergen Imeev via Tarantool-patches
2021-08-11 16:01 ` [Tarantool-patches] [PATCH v1 7/7] sql: fix comparison with SCALAR value Mergen Imeev via Tarantool-patches
2021-08-12 18:50   ` Vladislav Shpilevoy via Tarantool-patches
2021-08-12 22:23     ` Mergen Imeev via Tarantool-patches

This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox