Tarantool development patches archive
 help / color / mirror / Atom feed
From: Mergen Imeev <imeevma@tarantool.org>
To: Vladislav Shpilevoy <v.shpilevoy@tarantool.org>
Cc: tarantool-patches@dev.tarantool.org
Subject: Re: [Tarantool-patches] [PATCH v1 1/1] sql: make NUMBER to be union of SQL numeric types
Date: Thu, 7 Nov 2019 12:30:17 +0300	[thread overview]
Message-ID: <20191107093017.GA13486@tarantool.org> (raw)
In-Reply-To: <0a281b27-407a-9ab6-6d75-1564142a9bf9@tarantool.org>

Thank you for review! My answers, diff between versions and
new patch below.

On Sat, Nov 02, 2019 at 06:49:58PM +0100, Vladislav Shpilevoy wrote:
> Thanks for the patch!
> 
> See 5 comments below.
> 
> On 02/11/2019 12:30, imeevma@tarantool.org wrote:
> > This patch makes number to be union of UNSIGNED, INTEGER and
> > DOUBLE numeric types. The first two types represented by Tarantool
> > UNSIGNED and INTEGER types. Currently there is not DOUBLE type in
> > Tarantool.
> > 
> > Closes #4233
> > Closes #4463
> > ---
> > https://github.com/tarantool/tarantool/branches
> 
> https://github.com/tarantool/tarantool/tree/imeevma/gh-4233-fix-number-field-type-in-sql
> 
> > https://github.com/tarantool/tarantool/issues/4233
> > https://github.com/tarantool/tarantool/issues/4463
> > 
> > diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
> > index 03c63d8..61e6487 100644
> > --- a/src/box/sql/vdbe.c
> > +++ b/src/box/sql/vdbe.c
> > @@ -1660,6 +1657,7 @@ case OP_Remainder: {           /* same as TK_REM, in1, in2, out3 */
> >  				 sql_value_to_diag_str(pIn2), "numeric");
> >  			goto abort_due_to_error;
> >  		}
> > +		assert(((type1 | type2) & MEM_Real) !=0);
> 
> 1. Please, put a whitespace after '!='.
> 
Fixed.

> >  		switch( pOp->opcode) {
> >  		case OP_Add:         rB += rA;       break;
> >  		case OP_Subtract:    rB -= rA;       break;
> > diff --git a/src/box/sql/vdbemem.c b/src/box/sql/vdbemem.c
> > index 2d37b62..6bfd4c0 100644
> > --- a/src/box/sql/vdbemem.c
> > +++ b/src/box/sql/vdbemem.c
> > @@ -596,6 +596,34 @@ sqlVdbeMemRealify(Mem * pMem)
> > +static int
> > +sql_mem_to_number(struct Mem *mem)
> > +{
> > +	assert((mem->flags & (MEM_Int | MEM_UInt | MEM_Real | MEM_Null)) == 0);
> > +	if ((mem->flags & (MEM_Blob | MEM_Str)) == 0)
> > +		return -1;
> > +	assert(mem->z[mem->n] == '\0');
> > +	char *tail = NULL;
> > +	long long unsigned int value_u = strtoull(mem->z, &tail, 10);
> > +	if (tail[0] == '\0' && value_u <= UINT64_MAX) {
> 
> 2. I think you can safely assume, that long long unsigned == uint64_t.
> As well as long long int == int64_t.
> 
Thanks, was fixed due to changes.

> > +		mem->u.u = value_u;
> > +		MemSetTypeFlag(mem, MEM_UInt);
> > +		return 0;
> > +	}
> > +	long long int value_i = strtoll(mem->z, &tail, 10);
> > +	if (tail[0] == '\0' && value_i <= INT64_MAX && value_i >= INT64_MIN) {
> > +		mem->u.i = value_i;
> > +		MemSetTypeFlag(mem, MEM_Int);
> > +		return 0;
> > +	}
> 
> 3. strtoull and strtoll are very mean functions. It is easy to
> screw their results:
> 
> tarantool> box.execute("SELECT CAST('  9223372036854774800 ' AS NUMBER);")
> ---
> - metadata:
>   - name: CAST('  9223372036854774800 ' AS NUMBER)
>     type: number
>   rows:
>   - [9223372036854774784]
> ...
> 
> tarantool> box.execute("SELECT CAST('  9223372036854774800' AS NUMBER);")
> ---
> - metadata:
>   - name: CAST('  9223372036854774800' AS NUMBER)
>     type: number
>   rows:
>   - [9223372036854774800]
> ...
> 
> Here presence of whitespaces in the end of a number string affects the
> cast result. So you can't actually treat tail[0] == 0 as a sign of
> success.
> 
> One another example:
> 
> tarantool> box.execute("SELECT CAST('' AS NUMBER);")
> ---
> - metadata:
>   - name: CAST('' AS NUMBER)
>     type: number
>   rows:
>   - [0]
> ...
> 
> You can find how we work with them in expr_code_int(), sql_atoi64(),
> lbox_tonumber64().
> 
Thanks. I understood that sql_atoi64() is actually capable
of all I need in this issue. Replaced sql_mem_to_number() by
sqlVdbeMemNumerify().

> > +	double value_d;
> > +	if (sqlAtoF(mem->z, &value_d, mem->n) == 0)
> > +		return -1;
> 
> 4. Why not 'strtod'?
> 
Some tests fail if the DOUBLE is cast to STRING and then that
STRING casts back to DOUBLE. It looks like the SQL functions that
casts STRING to DOUBLE and DOUBLE to STRING are somehow
synchronized.

> > +	mem->u.r = value_d;
> > +	MemSetTypeFlag(mem, MEM_Real);
> > +	return 0;
> > +}
> > +
> > diff --git a/test/sql-tap/numcast.test.lua b/test/sql-tap/numcast.test.lua
> > index 9f72485..1355bad 100755
> > --- a/test/sql-tap/numcast.test.lua
> > +++ b/test/sql-tap/numcast.test.lua
> > @@ -65,5 +65,107 @@ for _, enc in ipairs({"utf8"}) do
> > +test:do_execsql_test(
> > +    "numcast-2.7",
> > +    [[
> > +        SELECT CAST('101     ' AS NUMBER) / 10, CAST('      101' AS NUMBER) / 10;
> > +    ]], {
> > +        10.1, 10
> 
> 5. So this is actually incorrect. Why a number of whitespaces in the end
> affects the result type?
Fixed.


Diff:

From 920653b9649503ff0dafe5587247db02115a013e Mon Sep 17 00:00:00 2001
From: Mergen Imeev <imeevma@gmail.com>
Date: Thu, 7 Nov 2019 12:00:46 +0300
Subject: [PATCH] Review fix


diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
index 61e6487..2ceee1a 100644
--- a/src/box/sql/vdbe.c
+++ b/src/box/sql/vdbe.c
@@ -1657,7 +1657,7 @@ case OP_Remainder: {           /* same as TK_REM, in1, in2, out3 */
 				 sql_value_to_diag_str(pIn2), "numeric");
 			goto abort_due_to_error;
 		}
-		assert(((type1 | type2) & MEM_Real) !=0);
+		assert(((type1 | type2) & MEM_Real) != 0);
 		switch( pOp->opcode) {
 		case OP_Add:         rB += rA;       break;
 		case OP_Subtract:    rB -= rA;       break;
diff --git a/src/box/sql/vdbeInt.h b/src/box/sql/vdbeInt.h
index 0f32b4c..3c5b9cc 100644
--- a/src/box/sql/vdbeInt.h
+++ b/src/box/sql/vdbeInt.h
@@ -512,7 +512,6 @@ int sqlVdbeRealValue(Mem *, double *);
 int
 mem_value_bool(const struct Mem *mem, bool *b);
 
-int mem_apply_integer_type(Mem *);
 int sqlVdbeMemRealify(Mem *);
 int sqlVdbeMemNumerify(Mem *);
 int sqlVdbeMemCast(Mem *, enum field_type type);
diff --git a/src/box/sql/vdbemem.c b/src/box/sql/vdbemem.c
index 6bfd4c0..6d99efe 100644
--- a/src/box/sql/vdbemem.c
+++ b/src/box/sql/vdbemem.c
@@ -534,23 +534,6 @@ mem_value_bool(const struct Mem *mem, bool *b)
 }
 
 /*
- * The MEM structure is already a MEM_Real.  Try to also make it a
- * MEM_Int if we can.
- */
-int
-mem_apply_integer_type(Mem *pMem)
-{
-	int rc;
-	i64 ix;
-	assert(pMem->flags & MEM_Real);
-	assert(EIGHT_BYTE_ALIGNMENT(pMem));
-
-	if ((rc = doubleToInt64(pMem->u.r, (int64_t *) &ix)) == 0)
-		mem_set_int(pMem, ix, pMem->u.r <= -1);
-	return rc;
-}
-
-/*
  * Convert pMem to type integer.  Invalidate any prior representations.
  */
 int
@@ -596,34 +579,6 @@ sqlVdbeMemRealify(Mem * pMem)
 	return 0;
 }
 
-static int
-sql_mem_to_number(struct Mem *mem)
-{
-	assert((mem->flags & (MEM_Int | MEM_UInt | MEM_Real | MEM_Null)) == 0);
-	if ((mem->flags & (MEM_Blob | MEM_Str)) == 0)
-		return -1;
-	assert(mem->z[mem->n] == '\0');
-	char *tail = NULL;
-	long long unsigned int value_u = strtoull(mem->z, &tail, 10);
-	if (tail[0] == '\0' && value_u <= UINT64_MAX) {
-		mem->u.u = value_u;
-		MemSetTypeFlag(mem, MEM_UInt);
-		return 0;
-	}
-	long long int value_i = strtoll(mem->z, &tail, 10);
-	if (tail[0] == '\0' && value_i <= INT64_MAX && value_i >= INT64_MIN) {
-		mem->u.i = value_i;
-		MemSetTypeFlag(mem, MEM_Int);
-		return 0;
-	}
-	double value_d;
-	if (sqlAtoF(mem->z, &value_d, mem->n) == 0)
-		return -1;
-	mem->u.r = value_d;
-	MemSetTypeFlag(mem, MEM_Real);
-	return 0;
-}
-
 /*
  * Convert pMem so that it has types MEM_Real or MEM_Int or both.
  * Invalidate any prior representations.
@@ -636,18 +591,16 @@ int
 sqlVdbeMemNumerify(Mem * pMem)
 {
 	if ((pMem->flags & (MEM_Int | MEM_UInt | MEM_Real | MEM_Null)) == 0) {
-		assert((pMem->flags & (MEM_Blob | MEM_Str)) != 0);
+		if ((pMem->flags & (MEM_Blob | MEM_Str)) == 0)
+			return -1;
 		bool is_neg;
 		int64_t i;
 		if (sql_atoi64(pMem->z, &i, &is_neg, pMem->n) == 0) {
 			mem_set_int(pMem, i, is_neg);
 		} else {
-			double v;
-			if (sqlVdbeRealValue(pMem, &v))
+			if (sqlAtoF(pMem->z, &pMem->u.r, pMem->n) == 0)
 				return -1;
-			pMem->u.r = v;
 			MemSetTypeFlag(pMem, MEM_Real);
-			mem_apply_integer_type(pMem);
 		}
 	}
 	assert((pMem->flags & (MEM_Int | MEM_UInt | MEM_Real | MEM_Null)) != 0);
@@ -754,10 +707,7 @@ sqlVdbeMemCast(Mem * pMem, enum field_type type)
 			return -1;
 		return 0;
 	case FIELD_TYPE_NUMBER:
-		if ((pMem->flags &
-		    (MEM_Real | MEM_Int | MEM_UInt | MEM_Null)) != 0)
-			return 0;
-		return sql_mem_to_number(pMem);
+		return sqlVdbeMemNumerify(pMem);
 	case FIELD_TYPE_VARBINARY:
 		if ((pMem->flags & MEM_Blob) != 0)
 			return 0;
diff --git a/test/sql-tap/numcast.test.lua b/test/sql-tap/numcast.test.lua
index 1355bad..582344e 100755
--- a/test/sql-tap/numcast.test.lua
+++ b/test/sql-tap/numcast.test.lua
@@ -133,7 +133,7 @@ test:do_execsql_test(
     [[
         SELECT CAST('101     ' AS NUMBER) / 10, CAST('      101' AS NUMBER) / 10;
     ]], {
-        10.1, 10
+        10, 10
     })
 
 test:do_execsql_test(


New patch:

From c00062c46fe603bdc4cacfa26635b8b66ddde849 Mon Sep 17 00:00:00 2001
From: Mergen Imeev <imeevma@gmail.com>
Date: Sat, 26 Oct 2019 17:27:53 +0300
Subject: [PATCH] sql: make NUMBER to be union of SQL numeric types

This patch makes number to be union of UNSIGNED, INTEGER and
DOUBLE numeric types. The first two types represented by Tarantool
UNSIGNED and INTEGER types. Currently there is not DOUBLE type in
Tarantool.

Closes #4233
Closes #4463

diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
index 03c63d8..2ceee1a 100644
--- a/src/box/sql/vdbe.c
+++ b/src/box/sql/vdbe.c
@@ -1586,7 +1586,6 @@ case OP_Subtract:              /* same as TK_MINUS, in1, in2, out3 */
 case OP_Multiply:              /* same as TK_STAR, in1, in2, out3 */
 case OP_Divide:                /* same as TK_SLASH, in1, in2, out3 */
 case OP_Remainder: {           /* same as TK_REM, in1, in2, out3 */
-	char bIntint;   /* Started out as two integer operands */
 	u32 flags;      /* Combined MEM_* flags from both inputs */
 	u16 type1;      /* Numeric type of left operand */
 	u16 type2;      /* Numeric type of right operand */
@@ -1609,7 +1608,6 @@ case OP_Remainder: {           /* same as TK_REM, in1, in2, out3 */
 		bool is_lhs_neg = pIn1->flags & MEM_Int;
 		bool is_rhs_neg = pIn2->flags & MEM_Int;
 		bool is_res_neg;
-		bIntint = 1;
 		switch( pOp->opcode) {
 		case OP_Add: {
 			if (sql_add_int(iA, is_lhs_neg, iB, is_rhs_neg,
@@ -1649,7 +1647,6 @@ case OP_Remainder: {           /* same as TK_REM, in1, in2, out3 */
 		}
 		mem_set_int(pOut, iB, is_res_neg);
 	} else {
-		bIntint = 0;
 		if (sqlVdbeRealValue(pIn1, &rA) != 0) {
 			diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
 				 sql_value_to_diag_str(pIn1), "numeric");
@@ -1660,6 +1657,7 @@ case OP_Remainder: {           /* same as TK_REM, in1, in2, out3 */
 				 sql_value_to_diag_str(pIn2), "numeric");
 			goto abort_due_to_error;
 		}
+		assert(((type1 | type2) & MEM_Real) != 0);
 		switch( pOp->opcode) {
 		case OP_Add:         rB += rA;       break;
 		case OP_Subtract:    rB -= rA;       break;
@@ -1685,9 +1683,6 @@ case OP_Remainder: {           /* same as TK_REM, in1, in2, out3 */
 		}
 		pOut->u.r = rB;
 		MemSetTypeFlag(pOut, MEM_Real);
-		if (((type1|type2)&MEM_Real)==0 && !bIntint) {
-			mem_apply_integer_type(pOut);
-		}
 	}
 	break;
 
@@ -2746,14 +2741,6 @@ case OP_Column: {
 	    default_val_mem != NULL) {
 		sqlVdbeMemShallowCopy(pDest, default_val_mem, MEM_Static);
 	}
-	if ((pDest->flags & (MEM_Int | MEM_UInt)) != 0) {
-		if (field_type == FIELD_TYPE_NUMBER) {
-			if ((pDest->flags & MEM_Int) != 0)
-				sqlVdbeMemSetDouble(pDest, pDest->u.i);
-			else
-				sqlVdbeMemSetDouble(pDest, pDest->u.u);
-		}
-	}
 	pDest->field_type = field_type;
 op_column_out:
 	REGISTER_TRACE(p, pOp->p3, pDest);
diff --git a/src/box/sql/vdbeInt.h b/src/box/sql/vdbeInt.h
index 0f32b4c..3c5b9cc 100644
--- a/src/box/sql/vdbeInt.h
+++ b/src/box/sql/vdbeInt.h
@@ -512,7 +512,6 @@ int sqlVdbeRealValue(Mem *, double *);
 int
 mem_value_bool(const struct Mem *mem, bool *b);
 
-int mem_apply_integer_type(Mem *);
 int sqlVdbeMemRealify(Mem *);
 int sqlVdbeMemNumerify(Mem *);
 int sqlVdbeMemCast(Mem *, enum field_type type);
diff --git a/src/box/sql/vdbemem.c b/src/box/sql/vdbemem.c
index 2d37b62..6d99efe 100644
--- a/src/box/sql/vdbemem.c
+++ b/src/box/sql/vdbemem.c
@@ -534,23 +534,6 @@ mem_value_bool(const struct Mem *mem, bool *b)
 }
 
 /*
- * The MEM structure is already a MEM_Real.  Try to also make it a
- * MEM_Int if we can.
- */
-int
-mem_apply_integer_type(Mem *pMem)
-{
-	int rc;
-	i64 ix;
-	assert(pMem->flags & MEM_Real);
-	assert(EIGHT_BYTE_ALIGNMENT(pMem));
-
-	if ((rc = doubleToInt64(pMem->u.r, (int64_t *) &ix)) == 0)
-		mem_set_int(pMem, ix, pMem->u.r <= -1);
-	return rc;
-}
-
-/*
  * Convert pMem to type integer.  Invalidate any prior representations.
  */
 int
@@ -608,18 +591,16 @@ int
 sqlVdbeMemNumerify(Mem * pMem)
 {
 	if ((pMem->flags & (MEM_Int | MEM_UInt | MEM_Real | MEM_Null)) == 0) {
-		assert((pMem->flags & (MEM_Blob | MEM_Str)) != 0);
+		if ((pMem->flags & (MEM_Blob | MEM_Str)) == 0)
+			return -1;
 		bool is_neg;
 		int64_t i;
 		if (sql_atoi64(pMem->z, &i, &is_neg, pMem->n) == 0) {
 			mem_set_int(pMem, i, is_neg);
 		} else {
-			double v;
-			if (sqlVdbeRealValue(pMem, &v))
+			if (sqlAtoF(pMem->z, &pMem->u.r, pMem->n) == 0)
 				return -1;
-			pMem->u.r = v;
 			MemSetTypeFlag(pMem, MEM_Real);
-			mem_apply_integer_type(pMem);
 		}
 	}
 	assert((pMem->flags & (MEM_Int | MEM_UInt | MEM_Real | MEM_Null)) != 0);
@@ -676,22 +657,6 @@ sqlVdbeMemCast(Mem * pMem, enum field_type type)
 	assert(type < field_type_MAX);
 	if (pMem->flags & MEM_Null)
 		return 0;
-	if ((pMem->flags & MEM_Blob) != 0 && type == FIELD_TYPE_NUMBER) {
-		bool is_neg;
-		if (sql_atoi64(pMem->z,  (int64_t *) &pMem->u.i, &is_neg,
-			       pMem->n) == 0) {
-			MemSetTypeFlag(pMem, MEM_Real);
-			if (is_neg)
-				pMem->u.r = pMem->u.i;
-			else
-				pMem->u.r = pMem->u.u;
-			return 0;
-		}
-		if (sqlAtoF(pMem->z, &pMem->u.r, pMem->n) == 0)
-			return -1;
-		MemSetTypeFlag(pMem, MEM_Real);
-		return 0;
-	}
 	switch (type) {
 	case FIELD_TYPE_SCALAR:
 		return 0;
@@ -742,7 +707,7 @@ sqlVdbeMemCast(Mem * pMem, enum field_type type)
 			return -1;
 		return 0;
 	case FIELD_TYPE_NUMBER:
-		return sqlVdbeMemRealify(pMem);
+		return sqlVdbeMemNumerify(pMem);
 	case FIELD_TYPE_VARBINARY:
 		if ((pMem->flags & MEM_Blob) != 0)
 			return 0;
@@ -1820,26 +1785,12 @@ mpstream_encode_vdbe_mem(struct mpstream *stream, struct Mem *var)
 	if (var->flags & MEM_Null) {
 		mpstream_encode_nil(stream);
 	} else if (var->flags & MEM_Real) {
-		/*
-		 * We can't pass to INT iterator float
-		 * value. Hence, if floating point value
-		 * lacks fractional component, we can
-		 * encode it as INT and successfully
-		 * pass to INT iterator.
-		 */
-		i = var->u.r;
-		if (i == var->u.r && i < 0)
-			goto encode_int;
-		if (i == var->u.r && i >= 0)
-			goto encode_uint;
 		mpstream_encode_double(stream, var->u.r);
 	} else if (var->flags & MEM_Int) {
 		i = var->u.i;
-encode_int:
 		mpstream_encode_int(stream, i);
 	} else if (var->flags & MEM_UInt) {
 		i = var->u.u;
-encode_uint:
 		mpstream_encode_uint(stream, i);
 	} else if (var->flags & MEM_Str) {
 		mpstream_encode_strn(stream, var->z, var->n);
diff --git a/test/sql-tap/cast.test.lua b/test/sql-tap/cast.test.lua
index 23229db..43c155a 100755
--- a/test/sql-tap/cast.test.lua
+++ b/test/sql-tap/cast.test.lua
@@ -628,14 +628,14 @@ test:do_execsql_test(
         SELECT CAST(9223372036854774800 AS NUMBER)
     ]], {
         -- <cast-3.2>
-        9223372036854774784
+        9223372036854774800LL
         -- </cast-3.2>
     })
 
 test:do_execsql_test(
     "cast-3.3",
     "SELECT CAST(9223372036854774800 AS NUMBER)",
-    {9.22337203685477e+18})
+    {9223372036854774800LL})
 
 test:do_execsql_test(
     "cast-3.4",
@@ -643,7 +643,7 @@ test:do_execsql_test(
         SELECT CAST(CAST(9223372036854774800 AS NUMBER) AS integer)
     ]], {
         -- <cast-3.4>
-        9223372036854774784LL
+        9223372036854774800LL
         -- </cast-3.4>
     })
 
@@ -663,14 +663,14 @@ test:do_execsql_test(
         SELECT CAST(-9223372036854774800 AS NUMBER)
     ]], {
         -- <cast-3.6>
-        -9223372036854774784
+        -9223372036854774800LL
         -- </cast-3.6>
     })
 
 test:do_execsql_test(
     "cast-3.7",
     "SELECT CAST(-9223372036854774800 AS NUMBER)",
-    {-9.22337203685477e+18})
+    {-9223372036854774800LL})
 
 test:do_execsql_test(
     "cast-3.8",
@@ -678,7 +678,7 @@ test:do_execsql_test(
         SELECT CAST(CAST(-9223372036854774800 AS NUMBER) AS integer)
     ]], {
         -- <cast-3.8>
-        -9223372036854774784LL
+        -9223372036854774800LL
         -- </cast-3.8>
     })
 
@@ -695,7 +695,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "cast-3.12",
     [[
-        SELECT CAST('9223372036854774800' AS NUMBER)
+        SELECT CAST('9223372036854774800.' AS NUMBER)
     ]], {
         -- <cast-3.12>
         9223372036854774784
@@ -705,12 +705,12 @@ test:do_execsql_test(
 test:do_execsql_test(
     "cast-3.13",
     "SELECT CAST(9223372036854774800 AS NUMBER)",
-    {9.22337203685477e+18})
+    {9223372036854774800LL})
 
 test:do_execsql_test(
     "cast-3.14",
     [[
-        SELECT CAST(CAST('9223372036854774800' AS NUMBER) AS integer)
+        SELECT CAST(CAST('9223372036854774800.' AS NUMBER) AS integer)
     ]], {
         -- <cast-3.14>
         9223372036854774784LL
@@ -732,7 +732,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "cast-3.16",
     [[
-        SELECT CAST('-9223372036854774800' AS NUMBER)
+        SELECT CAST('-9223372036854774800.' AS NUMBER)
     ]], {
         -- <cast-3.16>
         -9223372036854774784
@@ -741,13 +741,13 @@ test:do_execsql_test(
 
 test:do_execsql_test(
     "cast-3.17",
-    "SELECT CAST('-9223372036854774800' AS NUMBER)",
+    "SELECT CAST('-9223372036854774800.' AS NUMBER)",
     {-9.22337203685477e+18})
 
 test:do_execsql_test(
     "cast-3.18",
     [[
-        SELECT CAST(CAST('-9223372036854774800' AS NUMBER) AS integer)
+        SELECT CAST(CAST('-9223372036854774800.' AS NUMBER) AS integer)
     ]], {
         -- <cast-3.18>
         -9223372036854774784LL
@@ -770,7 +770,7 @@ if true then --test:execsql("PRAGMA encoding")[1][1]=="UTF-8" then
     test:do_execsql_test(
         "cast-3.22",
         [[
-            SELECT CAST(x'39323233333732303336383534373734383030' AS NUMBER)
+            SELECT CAST(x'393232333337323033363835343737343830302E' AS NUMBER)
         ]], {
             -- <cast-3.22>
             9223372036854774784
@@ -778,7 +778,7 @@ if true then --test:execsql("PRAGMA encoding")[1][1]=="UTF-8" then
         })
     test:do_execsql_test(
         "cast-3.23",
-        "SELECT CAST(x'39323233333732303336383534373734383030' AS NUMBER)",
+        "SELECT CAST(x'393232333337323033363835343737343830302E' AS NUMBER)",
         {9.22337203685477e+18})
 
     test:do_execsql_test(
@@ -788,7 +788,7 @@ if true then --test:execsql("PRAGMA encoding")[1][1]=="UTF-8" then
                         AS integer)
         ]], {
             -- <cast-3.24>
-            9223372036854774784LL
+            9223372036854774800LL
             -- </cast-3.24>
         })
 end
@@ -796,7 +796,7 @@ end
 test:do_execsql_test(
     "case-3.25",
     [[
-        SELECT CAST(x'3138343436373434303733373039353531363135' AS NUMBER);
+        SELECT CAST(x'31383434363734343037333730393535313631352E' AS NUMBER);
     ]], { 1.844674407371e+19 } )
 
 test:do_execsql_test(
diff --git a/test/sql-tap/e_select1.test.lua b/test/sql-tap/e_select1.test.lua
index a0a6716..d9fec77 100755
--- a/test/sql-tap/e_select1.test.lua
+++ b/test/sql-tap/e_select1.test.lua
@@ -760,7 +760,7 @@ test:do_execsql_test(
         INSERT INTO z1 VALUES(6, 63, '0', -26);
 
         INSERT INTO z2 VALUES(1, NULL, 21);
-        INSERT INTO z2 VALUES(2, 36, 6);
+        INSERT INTO z2 VALUES(2, 36.0, 6.0);
 
         INSERT INTO z3 VALUES(1, 123.21, 123.12);
         INSERT INTO z3 VALUES(2, 49.17, -67);
diff --git a/test/sql-tap/numcast.test.lua b/test/sql-tap/numcast.test.lua
index 9f72485..582344e 100755
--- a/test/sql-tap/numcast.test.lua
+++ b/test/sql-tap/numcast.test.lua
@@ -1,6 +1,6 @@
 #!/usr/bin/env tarantool
 test = require("sqltester")
-test:plan(11)
+test:plan(22)
 
 --!./tcltestrunner.lua
 -- 2013 March 20
@@ -65,5 +65,107 @@ for _, enc in ipairs({"utf8"}) do
     end
 end
 
+--
+-- gh-4233: Make sure that NUMBER can contain UNSIGNED, INTEGER
+-- and DOUBLE and is not automatically converted to DOUBLE.
+--
+test:do_execsql_test(
+    "numcast-2.1",
+    [[
+        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, 9223372036854775807.1);
+        SELECT n, n/100 FROM t1;
+    ]], {
+        9223372036854775807ULL, 92233720368547758ULL,
+        -9223372036854775807LL, -92233720368547758LL,
+        9223372036854775808, 92233720368547758.08
+    })
+
+test:do_execsql_test(
+    "numcast-2.2",
+    [[
+        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-2.3",
+    [[
+        SELECT CAST(x'3131313131313131313131313131313131313131' AS NUMBER);
+    ]], {
+        11111111111111111111ULL
+    })
+
+test:do_execsql_test(
+    "numcast-2.4",
+    [[
+        SELECT CAST(x'31313131313131313131313131313131313131312E' AS NUMBER);
+    ]], {
+        11111111111111110656
+    })
+
+test:do_execsql_test(
+    "numcast-2.5",
+    [[
+        SELECT CAST('11111111111111111111' AS NUMBER);
+    ]], {
+        11111111111111111111ULL
+    })
+
+test:do_execsql_test(
+    "numcast-2.6",
+    [[
+        SELECT CAST('101' AS NUMBER) / 10, CAST('101.' AS NUMBER) / 10;
+    ]], {
+        10, 10.1
+    })
+
+test:do_execsql_test(
+    "numcast-2.7",
+    [[
+        SELECT CAST('101     ' AS NUMBER) / 10, CAST('      101' AS NUMBER) / 10;
+    ]], {
+        10, 10
+    })
+
+test:do_execsql_test(
+    "numcast-2.8",
+    [[
+        SELECT (1 + 0) / 3, (1 + 0.) / 3, (1 + 0) / 3.;
+    ]], {
+        0, 0.33333333333333, 0.33333333333333
+    })
+
+test:do_execsql_test(
+    "numcast-2.9",
+    [[
+        SELECT (1 - 0) / 3, (1 - 0.) / 3, (1 - 0) / 3.;
+    ]], {
+        0, 0.33333333333333, 0.33333333333333
+    })
+
+test:do_execsql_test(
+    "numcast-2.10",
+    [[
+        SELECT (1 * 1) / 3, (1 * 1.) / 3, (1 * 1) / 3.;
+    ]], {
+        0, 0.33333333333333, 0.33333333333333
+    })
+
+test:do_execsql_test(
+    "numcast-2.11",
+    [[
+        SELECT (1 / 1) / 3, (1 / 1.) / 3, (1 / 1) / 3.;
+    ]], {
+        0, 0.33333333333333, 0.33333333333333
+    })
 
 test:finish_test()
diff --git a/test/sql-tap/select3.test.lua b/test/sql-tap/select3.test.lua
index 19f853d..f2637db 100755
--- a/test/sql-tap/select3.test.lua
+++ b/test/sql-tap/select3.test.lua
@@ -386,7 +386,7 @@ test:do_execsql_test("select3-8.1", [[
   SELECT typeof(sum(a3)) FROM a;
 ]], {
   -- <select3-8.1>
-  "number"
+  "integer"
   -- </select3-8.1>
 })
 
diff --git a/test/sql-tap/sort.test.lua b/test/sql-tap/sort.test.lua
index e156414..36074d6 100755
--- a/test/sql-tap/sort.test.lua
+++ b/test/sql-tap/sort.test.lua
@@ -243,7 +243,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.0"
+        "x-123.0", "x-2.15", "x-2b", "x-3.141592653", "x-4221.0", "x0.0013442", "x1.6", "x11"
         -- </sort-2.1.1>
     })
 
@@ -253,7 +253,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.0"
+        "x-123.0", "x-2.15", "x-2b", "x-3.141592653", "x-4221.0", "x0.0013442", "x1.6", "x11"
         -- </sort-2.1.2>
     })
 
@@ -263,7 +263,7 @@ test:do_execsql_test(
         SELECT v FROM t1 ORDER BY substr(v,2,999) DESC;
     ]], {
         -- <sort-2.1.4>
-        "x11.0", "x1.6", "x0.0013442", "x-4221.0", "x-3.141592653", "x-2b", "x-2.15", "x-123.0"
+        "x11", "x1.6", "x0.0013442", "x-4221.0", "x-3.141592653", "x-2b", "x-2.15", "x-123.0"
         -- </sort-2.1.4>
     })
 
@@ -381,7 +381,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.0", "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", "x2.7", "x5.0e10"
         -- </sort-4.6>
     })
 
@@ -391,7 +391,7 @@ test:do_execsql_test(
         SELECT v FROM t1 ORDER BY 1 DESC;
     ]], {
         -- <sort-4.7>
-        "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"
+        "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"
         -- </sort-4.7>
     })
 
@@ -401,7 +401,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.0","2.7","5.0e10"
+    "-123.0","-2.15","-2b","-3.141592653","-4.0e9","-4221.0","0.0013442","01234567890123456789","1.6","11","2.7","5.0e10"
         -- </sort-4.8>
     })
 
diff --git a/test/sql-tap/tkt-91e2e8ba6f.test.lua b/test/sql-tap/tkt-91e2e8ba6f.test.lua
index 7622f75..b12b6e0 100755
--- a/test/sql-tap/tkt-91e2e8ba6f.test.lua
+++ b/test/sql-tap/tkt-91e2e8ba6f.test.lua
@@ -35,7 +35,7 @@ test:do_execsql_test(
         SELECT x/10, y/10 FROM t1;
     ]], {
         -- <1.2>
-        1, 1.1
+        1, 1
         -- </1.2>
     })
 
@@ -45,7 +45,7 @@ test:do_execsql_test(
         SELECT x/10, y/10 FROM (SELECT * FROM t1);
     ]], {
         -- <1.3>
-        1, 1.1
+        1, 1
         -- </1.3>
     })
 
@@ -55,7 +55,7 @@ test:do_execsql_test(
         SELECT x/10, y/10 FROM (SELECT * FROM t1 LIMIT 5 OFFSET 0);
     ]], {
         -- <1.4>
-        1, 1.1
+        1, 1
         -- </1.4>
     })
 
@@ -65,7 +65,7 @@ test:do_execsql_test(
         SELECT x/10, y/10 FROM (SELECT * FROM t1 LIMIT 5 OFFSET 0) LIMIT 5 OFFSET 0;
     ]], {
         -- <1.5>
-        1, 1.1
+        1, 1
         -- </1.5>
     })
 
@@ -77,7 +77,7 @@ test:do_execsql_test(
         LIMIT 5 OFFSET 0;
     ]], {
         -- <1.6>
-        1, 1.1
+        1, 1
         -- </1.6>
     })
 
@@ -92,7 +92,7 @@ test:do_execsql_test(
         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
+        1, 1
         -- </1.7>
     })
 
diff --git a/test/sql/integer-overflow.result b/test/sql/integer-overflow.result
index 223ba02..1849314 100644
--- a/test/sql/integer-overflow.result
+++ b/test/sql/integer-overflow.result
@@ -171,7 +171,7 @@ box.execute("SELECT * FROM t;")
   - name: A
     type: number
   rows:
-  - [1, 1.844674407371e+19]
+  - [1, 18446744073709551615]
   - [2, -1]
 ...
 box.space.T:drop()
diff --git a/test/sql/types.result b/test/sql/types.result
index 37350f0..81dd169 100644
--- a/test/sql/types.result
+++ b/test/sql/types.result
@@ -1020,7 +1020,7 @@ box.execute("SELECT CAST(18446744073709551615 AS NUMBER);")
   - name: CAST(18446744073709551615 AS NUMBER)
     type: number
   rows:
-  - [1.844674407371e+19]
+  - [18446744073709551615]
 ...
 box.execute("SELECT CAST(18446744073709551615 AS TEXT);")
 ---

  reply	other threads:[~2019-11-07  9:30 UTC|newest]

Thread overview: 7+ messages / expand[flat|nested]  mbox.gz  Atom feed  top
2019-11-02 11:30 imeevma
2019-11-02 17:49 ` Vladislav Shpilevoy
2019-11-07  9:30   ` Mergen Imeev [this message]
2019-11-07 11:17     ` Vladislav Shpilevoy
2019-11-07 12:13 imeevma
2019-11-07 12:15 ` Imeev Mergen
2019-11-08 14:09 ` Nikita Pettik

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

  Avoid top-posting and favor interleaved quoting:
  https://en.wikipedia.org/wiki/Posting_style#Interleaved_style

* Reply using the --to, --cc, and --in-reply-to
  switches of git-send-email(1):

  git send-email \
    --in-reply-to=20191107093017.GA13486@tarantool.org \
    --to=imeevma@tarantool.org \
    --cc=tarantool-patches@dev.tarantool.org \
    --cc=v.shpilevoy@tarantool.org \
    --subject='Re: [Tarantool-patches] [PATCH v1 1/1] sql: make NUMBER to be union of SQL numeric types' \
    /path/to/YOUR_REPLY

  https://kernel.org/pub/software/scm/git/docs/git-send-email.html

* If your mail client supports setting the In-Reply-To header
  via mailto: links, try the mailto: link

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