Tarantool development patches archive
 help / color / mirror / Atom feed
From: Nikita Pettik <korablev@tarantool.org>
To: tarantool-patches@freelists.org
Cc: v.shpilevoy@tarantool.org, kostja@tarantool.org,
	Nikita Pettik <korablev@tarantool.org>
Subject: [tarantool-patches] [PATCH 4/9] sql: introduce type boolean
Date: Sun, 14 Apr 2019 18:04:02 +0300	[thread overview]
Message-ID: <e9cc494f59592a18065f5b6682ad199b42b571c9.1555252410.git.korablev@tarantool.org> (raw)
In-Reply-To: <cover.1555252410.git.korablev@tarantool.org>
In-Reply-To: <cover.1555252410.git.korablev@tarantool.org>

This patch introduces basic facilities to operate on boolean type:
boolean literals "true" and "false" where true > false; alias to null -
unknown; column type "BOOLEAN" and shortcut "BOOL"; opportunity to
insert and select boolean values from table; OR and AND predicates
accept boolean arguments; CAST operation involving boolean type;
comparison between boolean values (including VDBE sorter routines).

Part of #3648
---
 extra/mkkeywordhash.c        |   5 +
 src/box/execute.c            |   9 +
 src/box/lua/lua_sql.c        |   3 +
 src/box/sql/build.c          |  11 +-
 src/box/sql/expr.c           |  14 +
 src/box/sql/func.c           |  15 ++
 src/box/sql/parse.y          |  15 ++
 src/box/sql/sqlInt.h         |   6 +
 src/box/sql/vdbe.c           |  34 ++-
 src/box/sql/vdbeInt.h        |   6 +-
 src/box/sql/vdbeapi.c        |  16 ++
 src/box/sql/vdbeaux.c        |  31 ++-
 src/box/sql/vdbemem.c        |  73 +++++-
 test/sql-tap/whereG.test.lua |   6 +-
 test/sql/types.result        | 601 ++++++++++++++++++++++++++++++++++++++++++-
 test/sql/types.test.lua      | 132 ++++++++++
 16 files changed, 952 insertions(+), 25 deletions(-)

diff --git a/extra/mkkeywordhash.c b/extra/mkkeywordhash.c
index be7bd5545..8466cc8ee 100644
--- a/extra/mkkeywordhash.c
+++ b/extra/mkkeywordhash.c
@@ -109,6 +109,8 @@ static Keyword aKeywordTable[] = {
   { "BEFORE",                 "TK_BEFORE",      TRIGGER,          false },
   { "BEGIN",                  "TK_BEGIN",       TRIGGER,          true  },
   { "BETWEEN",                "TK_BETWEEN",     ALWAYS,           true  },
+  { "BOOL",                   "TK_BOOLEAN",     ALWAYS,           true  },
+  { "BOOLEAN",                "TK_BOOLEAN",     ALWAYS,           true  },
   { "BY",                     "TK_BY",          ALWAYS,           true  },
   { "CASCADE",                "TK_CASCADE",     FKEY,             false },
   { "CASE",                   "TK_CASE",        ALWAYS,           true  },
@@ -140,6 +142,7 @@ static Keyword aKeywordTable[] = {
   { "EXISTS",                 "TK_EXISTS",      ALWAYS,           true  },
   { "EXPLAIN",                "TK_EXPLAIN",     EXPLAIN,          true  },
   { "FAIL",                   "TK_FAIL",        CONFLICT|TRIGGER, false },
+  { "FALSE",                  "TK_FALSE",       ALWAYS,           true  },
   { "FOR",                    "TK_FOR",         TRIGGER,          true  },
   { "FOREIGN",                "TK_FOREIGN",     FKEY,             true  },
   { "FROM",                   "TK_FROM",        ALWAYS,           true  },
@@ -202,8 +205,10 @@ static Keyword aKeywordTable[] = {
   { "TO",                     "TK_TO",          ALWAYS,           true  },
   { "TRANSACTION",            "TK_TRANSACTION", ALWAYS,           true  },
   { "TRIGGER",                "TK_TRIGGER",     TRIGGER,          true  },
+  { "TRUE",                   "TK_TRUE",        ALWAYS,           true  },
   { "UNION",                  "TK_UNION",       COMPOUND,         true  },
   { "UNIQUE",                 "TK_UNIQUE",      ALWAYS,           true  },
+  { "UNKNOWN",                "TK_NULL",        ALWAYS,           true  },
   { "UPDATE",                 "TK_UPDATE",      ALWAYS,           true  },
   { "USING",                  "TK_USING",       ALWAYS,           true  },
   { "VALUES",                 "TK_VALUES",      ALWAYS,           true  },
diff --git a/src/box/execute.c b/src/box/execute.c
index 2da2bc88b..180519196 100644
--- a/src/box/execute.c
+++ b/src/box/execute.c
@@ -189,6 +189,15 @@ sql_column_to_messagepack(struct sql_stmt *stmt, int i,
 		}
 		break;
 	}
+	case MP_BOOL: {
+		bool b = sql_column_boolean(stmt, i);
+		size = mp_sizeof_bool(b);
+		char *pos = (char *) region_alloc(region, size);
+		if (pos == NULL)
+			goto oom;
+		mp_encode_bool(pos, b);
+		break;
+	}
 	case MP_NIL: {
 		size = mp_sizeof_nil();
 		char *pos = (char *) region_alloc(region, size);
diff --git a/src/box/lua/lua_sql.c b/src/box/lua/lua_sql.c
index 239ed1b2f..b4b2abab8 100644
--- a/src/box/lua/lua_sql.c
+++ b/src/box/lua/lua_sql.c
@@ -73,6 +73,9 @@ lua_sql_call(sql_context *pCtx, int nVal, sql_value **apVal) {
 		case MP_NIL:
 			lua_rawgeti(L, LUA_REGISTRYINDEX, luaL_nil_ref);
 			break;
+		case MP_BOOL:
+			lua_pushboolean(L, sql_value_boolean(param));
+			break;
 		default:
 			sql_result_error(pCtx, "Unsupported type passed "
 					     "to Lua", -1);
diff --git a/src/box/sql/build.c b/src/box/sql/build.c
index 7724e9415..c6fbb1af6 100644
--- a/src/box/sql/build.c
+++ b/src/box/sql/build.c
@@ -906,7 +906,6 @@ emitNewSysSequenceRecord(Parse *pParse, int reg_seq_id, const char *seq_name)
 
 	const long long int min_usigned_long_long = 0;
 	const long long int max_usigned_long_long = LLONG_MAX;
-	const bool const_false = false;
 
 	/* 1. New sequence id  */
 	sqlVdbeAddOp2(v, OP_SCopy, reg_seq_id, first_col + 1);
@@ -932,8 +931,7 @@ emitNewSysSequenceRecord(Parse *pParse, int reg_seq_id, const char *seq_name)
 	sqlVdbeAddOp2(v, OP_Integer, 0, first_col + 8);
 
 	/* 9. Cycle  */
-	sqlVdbeAddOp2(v, OP_Bool, 0, first_col + 9);
-	sqlVdbeChangeP4(v, -1, (char*)&const_false, P4_BOOL);
+	sqlVdbeAddOp2(v, OP_Bool, false, first_col + 9);
 
 	sqlVdbeAddOp3(v, OP_MakeRecord, first_col + 1, 9, first_col);
 
@@ -947,7 +945,6 @@ int
 emitNewSysSpaceSequenceRecord(Parse *pParse, int space_id, const char reg_seq_id)
 {
 	Vdbe *v = sqlGetVdbe(pParse);
-	const bool const_true = true;
 	int first_col = pParse->nMem + 1;
 	pParse->nMem += 4; /* 3 fields + new record pointer  */
 
@@ -958,8 +955,7 @@ emitNewSysSpaceSequenceRecord(Parse *pParse, int space_id, const char reg_seq_id
 	sqlVdbeAddOp2(v, OP_IntCopy, reg_seq_id, first_col + 2);
 
 	/* 3. True, which is 1 in SQL  */
-	sqlVdbeAddOp2(v, OP_Bool, 0, first_col + 3);
-	sqlVdbeChangeP4(v, -1, (char*)&const_true, P4_BOOL);
+	sqlVdbeAddOp2(v, OP_Bool, true, first_col + 3);
 
 	sqlVdbeAddOp3(v, OP_MakeRecord, first_col + 1, 3, first_col);
 
@@ -1023,8 +1019,7 @@ vdbe_emit_fk_constraint_create(struct Parse *parse_context,
 					      ER_CONSTRAINT_EXISTS, error_msg,
 					      false, OP_NoConflict) != 0)
 		return;
-	sqlVdbeAddOp2(vdbe, OP_Bool, 0, constr_tuple_reg + 3);
-	sqlVdbeChangeP4(vdbe, -1, (char*)&fk->is_deferred, P4_BOOL);
+	sqlVdbeAddOp2(vdbe, OP_Bool, fk->is_deferred, constr_tuple_reg + 3);
 	sqlVdbeAddOp4(vdbe, OP_String8, 0, constr_tuple_reg + 4, 0,
 			  fk_constraint_match_strs[fk->match], P4_STATIC);
 	sqlVdbeAddOp4(vdbe, OP_String8, 0, constr_tuple_reg + 5, 0,
diff --git a/src/box/sql/expr.c b/src/box/sql/expr.c
index 4b98bd175..6b38e8e66 100644
--- a/src/box/sql/expr.c
+++ b/src/box/sql/expr.c
@@ -3360,6 +3360,15 @@ expr_code_int(struct Parse *parse, struct Expr *expr, bool is_neg,
 	}
 }
 
+static void
+vdbe_emit_bool(struct Vdbe *v, const struct Expr *expr, int mem)
+{
+	const char *z = expr->u.zToken;
+	assert(z != NULL);
+	bool val = strncasecmp(z, "TRUE", 4) == 0;
+	sqlVdbeAddOp2(v, OP_Bool, val, mem);
+}
+
 /*
  * Erase column-cache entry number i
  */
@@ -3764,6 +3773,11 @@ sqlExprCodeTarget(Parse * pParse, Expr * pExpr, int target)
 			expr_code_int(pParse, pExpr, false, target);
 			return target;
 		}
+	case TK_TRUE:
+	case TK_FALSE: {
+			vdbe_emit_bool(pParse->pVdbe, pExpr, target);
+			return target;
+		}
 #ifndef SQL_OMIT_FLOATING_POINT
 	case TK_FLOAT:{
 			pExpr->type = FIELD_TYPE_INTEGER;
diff --git a/src/box/sql/func.c b/src/box/sql/func.c
index 3cdb119c8..860cd8920 100644
--- a/src/box/sql/func.c
+++ b/src/box/sql/func.c
@@ -122,6 +122,9 @@ typeofFunc(sql_context * context, int NotUsed, sql_value ** argv)
 	case MP_BIN:
 		z = "blob";
 		break;
+	case MP_BOOL:
+		z = "boolean";
+		break;
 	default:
 		z = "null";
 		break;
@@ -197,6 +200,13 @@ absFunc(sql_context * context, int argc, sql_value ** argv)
 			sql_result_null(context);
 			break;
 		}
+	case MP_BOOL: {
+		diag_set(ClientError, ER_INCONSISTENT_TYPES, "number",
+			 "boolean");
+		context->isError = SQL_TARANTOOL_ERROR;
+		context->fErrorOrAux = 1;
+		return;
+	}
 	default:{
 			/* Because sql_value_double() returns 0.0 if the argument is not
 			 * something that can be converted into a number, we have:
@@ -1091,6 +1101,11 @@ quoteFunc(sql_context * context, int argc, sql_value ** argv)
 			}
 			break;
 		}
+	case MP_BOOL: {
+		sql_result_text(context, argv[0]->u.b ? "true" : "false", -1,
+				SQL_TRANSIENT);
+		break;
+	}
 	default:{
 			assert(sql_value_mp_type(argv[0]) == MP_NIL);
 			sql_result_text(context, "NULL", 4, SQL_STATIC);
diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y
index ed5c05436..f64a84948 100644
--- a/src/box/sql/parse.y
+++ b/src/box/sql/parse.y
@@ -918,6 +918,11 @@ idlist(A) ::= nm(Y). {
       case TK_FLOAT:
         p->type = FIELD_TYPE_NUMBER;
         break;
+      case TK_TRUE:
+      case TK_FALSE:
+      case TK_UNKNOWN:
+        p->type = FIELD_TYPE_BOOLEAN;
+        break;
       case TK_VARIABLE:
         /*
          * For variables we set BOOLEAN type since
@@ -979,6 +984,10 @@ expr(A) ::= nm(X) DOT nm(Y). {
 }
 term(A) ::= FLOAT|BLOB(X). {spanExpr(&A,pParse,@X,X);/*A-overwrites-X*/}
 term(A) ::= STRING(X).     {spanExpr(&A,pParse,@X,X);/*A-overwrites-X*/}
+term(A) ::= FALSE(X) . {spanExpr(&A,pParse,@X,X);/*A-overwrites-X*/}
+term(A) ::= TRUE(X) . {spanExpr(&A,pParse,@X,X);/*A-overwrites-X*/}
+term(A) ::= UNKNOWN(X) . {spanExpr(&A,pParse,@X,X);/*A-overwrites-X*/}
+
 term(A) ::= INTEGER(X). {
   A.pExpr = sql_expr_new_dequoted(pParse->db, TK_INTEGER, &X);
   if (A.pExpr == NULL) {
@@ -1435,6 +1444,8 @@ cmd ::= PRAGMA .                            {
 
 nmnum(A) ::= plus_num(A).
 nmnum(A) ::= STRING(A).
+nmnum(A) ::= TRUE(A).
+nmnum(A) ::= FALSE(A).
 nmnum(A) ::= nm(A).
 nmnum(A) ::= ON(A).
 nmnum(A) ::= DELETE(A).
@@ -1667,6 +1678,10 @@ wqlist(A) ::= wqlist(A) COMMA nm(X) eidlist_opt(Y) AS LP select(Z) RP. {
 %type typedef {struct type_def}
 typedef(A) ::= TEXT . { A.type = FIELD_TYPE_STRING; }
 typedef(A) ::= SCALAR . { A.type = FIELD_TYPE_SCALAR; }
+/** BOOL | BOOLEAN is not used due to possible bug in Lemon. */
+typedef(A) ::= BOOL . { A.type = FIELD_TYPE_BOOLEAN; }
+typedef(A) ::= BOOLEAN . { A.type = FIELD_TYPE_BOOLEAN; }
+
 /**
  * Time-like types are temporary disabled, until they are
  * implemented as a native Tarantool types (gh-3694).
diff --git a/src/box/sql/sqlInt.h b/src/box/sql/sqlInt.h
index 9134f767d..4f62c2782 100644
--- a/src/box/sql/sqlInt.h
+++ b/src/box/sql/sqlInt.h
@@ -437,6 +437,9 @@ sql_value_bytes(sql_value *);
 double
 sql_value_double(sql_value *);
 
+bool
+sql_value_boolean(sql_value *val);
+
 int
 sql_value_int(sql_value *);
 
@@ -573,6 +576,9 @@ sql_column_double(sql_stmt *, int iCol);
 int
 sql_column_int(sql_stmt *, int iCol);
 
+bool
+sql_column_boolean(sql_stmt *stmt, int column);
+
 sql_int64
 sql_column_int64(sql_stmt *, int iCol);
 
diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
index c689aaf1d..10794b18e 100644
--- a/src/box/sql/vdbe.c
+++ b/src/box/sql/vdbe.c
@@ -333,6 +333,10 @@ mem_apply_type(struct Mem *record, enum field_type type)
 			return 0;
 		}
 		return sqlVdbeMemIntegerify(record, false);
+	case FIELD_TYPE_BOOLEAN:
+		if ((record->flags & MEM_Bool) == MEM_Bool)
+			return 0;
+		return -1;
 	case FIELD_TYPE_NUMBER:
 		if ((record->flags & (MEM_Real | MEM_Int)) != 0)
 			return 0;
@@ -504,6 +508,8 @@ memTracePrint(Mem *p)
 	} else if (p->flags & MEM_Real) {
 		printf(" r:%g", p->u.r);
 #endif
+	} else if (p->flags & MEM_Bool) {
+		printf(" bool:%s", p->u.b ? "true" : "false");
 	} else {
 		char zBuf[200];
 		sqlVdbeMemPrettyPrint(p, zBuf);
@@ -1091,9 +1097,9 @@ case OP_Integer: {         /* out2 */
  */
 case OP_Bool: {         /* out2 */
 	pOut = out2Prerelease(p, pOp);
-	assert(pOp->p4type == P4_BOOL);
+	assert(pOp->p1 == 1 || pOp->p1 == 0);
 	pOut->flags = MEM_Bool;
-	pOut->u.b = pOp->p4.p;
+	pOut->u.b = pOp->p1;
 	break;
 }
 
@@ -2161,6 +2167,24 @@ case OP_Ge: {             /* same as TK_GE, jump, in1, in3 */
 			}
 			break;
 		}
+	} else if ((flags1 | flags3) & MEM_Bool) {
+		/*
+		 * If one of values is of type BOOLEAN, then the
+		 * second one must be BOOLEAN as well. Otherwise
+		 * an error is raised.
+		 */
+		bool is_bool_type_arg1 = flags1 & MEM_Bool;
+		bool is_bool_type_arg3 = flags3 & MEM_Bool;
+		if (! is_bool_type_arg1 || ! is_bool_type_arg3) {
+			char *inconsistent_type = ! is_bool_type_arg1 ?
+						  mem_type_to_str(pIn1) :
+						  mem_type_to_str(pIn3);
+			diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
+				 inconsistent_type, "boolean");
+			rc = SQL_TARANTOOL_ERROR;
+			goto abort_due_to_error;
+		}
+		res = sqlMemCompare(pIn3, pIn1, NULL);
 	} else {
 		enum field_type type = pOp->p5 & FIELD_TYPE_MASK;
 		if (sql_type_is_numeric(type)) {
@@ -2414,6 +2438,8 @@ case OP_Or: {             /* same as TK_OR, in1, in2, out3 */
 	pIn1 = &aMem[pOp->p1];
 	if (pIn1->flags & MEM_Null) {
 		v1 = 2;
+	} else if ((pIn1->flags & MEM_Bool) != 0) {
+		v1 = pIn1->u.b;
 	} else {
 		int64_t i;
 		if (sqlVdbeIntValue(pIn1, &i) != 0) {
@@ -2427,6 +2453,8 @@ case OP_Or: {             /* same as TK_OR, in1, in2, out3 */
 	pIn2 = &aMem[pOp->p2];
 	if (pIn2->flags & MEM_Null) {
 		v2 = 2;
+	} else if ((pIn2->flags & MEM_Bool) != 0) {
+		v2 = pIn2->u.b;
 	} else {
 		int64_t i;
 		if (sqlVdbeIntValue(pIn2, &i) != 0) {
@@ -2540,6 +2568,8 @@ case OP_IfNot: {            /* jump, in1 */
 	pIn1 = &aMem[pOp->p1];
 	if (pIn1->flags & MEM_Null) {
 		c = pOp->p3;
+	} else if ((pIn1->flags & MEM_Bool) != 0) {
+		c = pOp->opcode==OP_IfNot ? ! pIn1->u.b : pIn1->u.b;
 	} else {
 		double v;
 		if (sqlVdbeRealValue(pIn1, &v) != 0) {
diff --git a/src/box/sql/vdbeInt.h b/src/box/sql/vdbeInt.h
index ec9123a66..5251e76ab 100644
--- a/src/box/sql/vdbeInt.h
+++ b/src/box/sql/vdbeInt.h
@@ -259,7 +259,7 @@ struct Mem {
  * auxiliary flags.
  */
 enum {
-	MEM_PURE_TYPE_MASK = 0x1f
+	MEM_PURE_TYPE_MASK = 0x3f
 };
 
 /**
@@ -508,6 +508,10 @@ int sqlVdbeMemStringify(Mem *, u8);
 int sqlVdbeIntValue(Mem *, int64_t *);
 int sqlVdbeMemIntegerify(Mem *, bool is_forced);
 int sqlVdbeRealValue(Mem *, double *);
+
+int
+vdbe_value_boolean(Mem *mem, bool *b);
+
 int mem_apply_integer_type(Mem *);
 int sqlVdbeMemRealify(Mem *);
 int sqlVdbeMemNumerify(Mem *);
diff --git a/src/box/sql/vdbeapi.c b/src/box/sql/vdbeapi.c
index 6e867ca84..e1302afc0 100644
--- a/src/box/sql/vdbeapi.c
+++ b/src/box/sql/vdbeapi.c
@@ -211,6 +211,14 @@ sql_value_double(sql_value * pVal)
 	return v;
 }
 
+bool
+sql_value_boolean(sql_value *val)
+{
+	bool b;
+	vdbe_value_boolean((struct Mem *) val, &b);
+	return b;
+}
+
 int
 sql_value_int(sql_value * pVal)
 {
@@ -982,6 +990,14 @@ sql_column_int(sql_stmt * pStmt, int i)
 	return val;
 }
 
+bool
+sql_column_boolean(sql_stmt *stmt, int i)
+{
+	bool val = sql_value_boolean(columnMem(stmt, i));
+	columnMallocFailure(stmt);
+	return val;
+}
+
 sql_int64
 sql_column_int64(sql_stmt * pStmt, int i)
 {
diff --git a/src/box/sql/vdbeaux.c b/src/box/sql/vdbeaux.c
index 0cc3c1487..0f56028e5 100644
--- a/src/box/sql/vdbeaux.c
+++ b/src/box/sql/vdbeaux.c
@@ -3383,6 +3383,17 @@ sqlMemCompare(const Mem * pMem1, const Mem * pMem2, const struct coll * pColl)
 		return (f2 & MEM_Null) - (f1 & MEM_Null);
 	}
 
+	if (combined_flags & MEM_Bool) {
+		if ((f1 & f2 & MEM_Bool) != 0) {
+			if (pMem1->u.b == pMem2->u.b)
+				return 0;
+			if (pMem1->u.b)
+				return 1;
+			return -1;
+		}
+		return -1;
+	}
+
 	/* At least one of the two values is a number
 	 */
 	if (combined_flags & (MEM_Int | MEM_Real)) {
@@ -3561,10 +3572,16 @@ sqlVdbeCompareMsgpack(const char **key1,
 			break;
 		}
 	case MP_BOOL:{
-			assert((unsigned char)(*aKey1) == 0xc2
-			       || (unsigned char)*aKey1 == 0xc3);
-			mem1.u.i = (unsigned)(size_t) aKey1++ - 0xc2;
-			goto do_int;
+
+			mem1.u.b = mp_decode_bool(&aKey1);
+			if ((pKey2->flags & MEM_Bool) != 0) {
+				if (mem1.u.b != pKey2->u.b) {
+					rc = mem1.u.b ? 1 : -1;
+				}
+			} else {
+				rc = (pKey2->flags & MEM_Null) != 0 ? +1 : -1;
+			}
+			break;
 		}
 	case MP_UINT:{
 			uint64_t v = mp_decode_uint(&aKey1);
@@ -3716,10 +3733,8 @@ vdbe_decode_msgpack_into_mem(const char *buf, struct Mem *mem, uint32_t *len)
 		break;
 	}
 	case MP_BOOL: {
-		assert((unsigned char)*buf == 0xc2 ||
-		       (unsigned char)*buf == 0xc3);
-		mem->u.i = (unsigned char)*buf - 0xc2;
-		mem->flags = MEM_Int;
+		mem->u.b = mp_decode_bool(&buf);
+		mem->flags = MEM_Bool;
 		break;
 	}
 	case MP_UINT: {
diff --git a/src/box/sql/vdbemem.c b/src/box/sql/vdbemem.c
index 15a2f55cb..4fed0eefe 100644
--- a/src/box/sql/vdbemem.c
+++ b/src/box/sql/vdbemem.c
@@ -289,7 +289,7 @@ sqlVdbeMemStringify(Mem * pMem, u8 bForce)
 		return SQL_OK;
 
 	assert(!(fg & MEM_Zero));
-	assert(fg & (MEM_Int | MEM_Real));
+	assert(fg & (MEM_Int | MEM_Real | MEM_Bool));
 	assert(EIGHT_BYTE_ALIGNMENT(pMem));
 
 	if (sqlVdbeMemClearAndResize(pMem, nByte)) {
@@ -297,6 +297,8 @@ sqlVdbeMemStringify(Mem * pMem, u8 bForce)
 	}
 	if (fg & MEM_Int) {
 		sql_snprintf(nByte, pMem->z, "%lld", pMem->u.i);
+	} else if ((fg & MEM_Bool) != 0) {
+		sql_snprintf(nByte, pMem->z, "%s", pMem->u.b ? "true" : "false");
 	} else {
 		assert(fg & MEM_Real);
 		sql_snprintf(nByte, pMem->z, "%!.15g", pMem->u.r);
@@ -499,6 +501,16 @@ sqlVdbeRealValue(Mem * pMem, double *v)
 	return -1;
 }
 
+int
+vdbe_value_boolean(Mem *mem, bool *b)
+{
+	if (mem->flags  & MEM_Bool) {
+		*b = mem->u.b;
+		return 0;
+	}
+	return -1;
+}
+
 /*
  * The MEM structure is already a MEM_Real.  Try to also make it a
  * MEM_Int if we can.
@@ -594,6 +606,37 @@ sqlVdbeMemNumerify(Mem * pMem)
 	return SQL_OK;
 }
 
+/**
+ * According to ANSI SQL string value can be converted to boolean
+ * type if string consists of literal "true" or "false" and
+ * number of leading spaces.
+ *
+ * For instance, "   tRuE" can be successfully converted to
+ * boolean value true.
+ *
+ * @param str String to be converted to boolean.
+ *            Assumed to be null terminated.
+ * @param result Resulting value of cast.
+ * @retval 0 If string satisfies conditions above.
+ * @retval -1 Otherwise.
+ */
+static int
+str_cast_to_boolean(const char *str, bool *result)
+{
+	assert(str != NULL);
+	for (; *str == ' '; str++);
+	size_t rest_str_len = strlen(str);
+	if (rest_str_len == 4 && strncasecmp(str, "true", 4) == 0) {
+		*result = true;
+		return 0;
+	}
+	if (rest_str_len == 5 && strncasecmp(str, "false", 5) == 0) {
+		*result = false;
+		return 0;
+	}
+	return -1;
+}
+
 /*
  * Cast the datatype of the value in pMem according to the type
  * @type.  Casting is different from applying type in that a cast
@@ -618,6 +661,23 @@ sqlVdbeMemCast(Mem * pMem, enum field_type type)
 	switch (type) {
 	case FIELD_TYPE_SCALAR:
 		return 0;
+	case FIELD_TYPE_BOOLEAN:
+		if ((pMem->flags & MEM_Int) != 0) {
+			pMem->u.b = pMem->u.i;
+			MemSetTypeFlag(pMem, MEM_Bool);
+			return 0;
+		}
+		if ((pMem->flags & MEM_Str) != 0) {
+			bool value;
+			if (str_cast_to_boolean(pMem->z, &value) != 0)
+				return -1;
+			MemSetTypeFlag(pMem, MEM_Bool);
+			pMem->u.b = value;
+			return 0;
+		}
+		if ((pMem->flags & MEM_Bool)  != 0)
+			return 0;
+		return -1;
 	case FIELD_TYPE_INTEGER:
 		if ((pMem->flags & MEM_Blob) != 0) {
 			if (sql_atoi64(pMem->z, (int64_t *) &pMem->u.i,
@@ -626,12 +686,23 @@ sqlVdbeMemCast(Mem * pMem, enum field_type type)
 			MemSetTypeFlag(pMem, MEM_Int);
 			return 0;
 		}
+		if ((pMem->flags & MEM_Bool) != 0) {
+			pMem->u.i = pMem->u.b;
+			MemSetTypeFlag(pMem, MEM_Int);
+			return 0;
+		}
 		return sqlVdbeMemIntegerify(pMem, true);
 	case FIELD_TYPE_NUMBER:
 		return sqlVdbeMemRealify(pMem);
 	default:
 		assert(type == FIELD_TYPE_STRING);
 		assert(MEM_Str == (MEM_Blob >> 3));
+		if ((pMem->flags & MEM_Bool) != 0) {
+			const char *str_bool = pMem->u.b ? "TRUE" : "FALSE";
+			sqlVdbeMemSetStr(pMem, str_bool, strlen(str_bool), 1,
+					 SQL_TRANSIENT);
+			return 0;
+		}
 		pMem->flags |= (pMem->flags & MEM_Blob) >> 3;
 			sql_value_apply_type(pMem, FIELD_TYPE_STRING);
 		assert(pMem->flags & MEM_Str || pMem->db->mallocFailed);
diff --git a/test/sql-tap/whereG.test.lua b/test/sql-tap/whereG.test.lua
index 510df2e4f..e9a39c5ce 100755
--- a/test/sql-tap/whereG.test.lua
+++ b/test/sql-tap/whereG.test.lua
@@ -329,10 +329,10 @@ test:do_execsql_test(
         CREATE TABLE t1(i int PRIMARY KEY, x INT , y INT , z INT );
         INSERT INTO t1 VALUES (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4);
         DROP TABLE IF EXISTS t2;
-        CREATE TABLE t2(i int PRIMARY KEY, bool TEXT);
+        CREATE TABLE t2(i int PRIMARY KEY, b TEXT);
         INSERT INTO t2 VALUES(1,'T'), (2,'F');
-        SELECT count(*) FROM t1 LEFT JOIN t2 ON t1.i=t2.i AND bool='T' union all
-        SELECT count(*) FROM t1 LEFT JOIN t2 ON likely(t1.i=t2.i) AND bool='T';
+        SELECT count(*) FROM t1 LEFT JOIN t2 ON t1.i=t2.i AND b='T' union all
+        SELECT count(*) FROM t1 LEFT JOIN t2 ON likely(t1.i=t2.i) AND b='T';
     ]], {
         -- <6.0>
         4, 4
diff --git a/test/sql/types.result b/test/sql/types.result
index 0a5085658..3aa0169e2 100644
--- a/test/sql/types.result
+++ b/test/sql/types.result
@@ -142,8 +142,8 @@ box.execute("SELECT * FROM test")
   - name: A
     type: boolean
   rows:
-  - [1, 1]
-  - [2, 0]
+  - [1, true]
+  - [2, false]
 ...
 sp:drop()
 ---
@@ -247,3 +247,600 @@ box.execute("SELECT NULL LIKE s FROM t1;")
 box.space.T1:drop()
 ---
 ...
+-- Test basic capabilities of boolean type.
+--
+box.execute("SELECT true;")
+---
+- metadata:
+  - name: 'true'
+    type: boolean
+  rows:
+  - [true]
+...
+box.execute("SELECT false;")
+---
+- metadata:
+  - name: 'false'
+    type: boolean
+  rows:
+  - [false]
+...
+box.execute("SELECT unknown;")
+---
+- metadata:
+  - name: unknown
+    type: scalar
+  rows:
+  - [null]
+...
+box.execute("SELECT true = false;")
+---
+- metadata:
+  - name: true = false
+    type: integer
+  rows:
+  - [0]
+...
+box.execute("SELECT true = true;")
+---
+- metadata:
+  - name: true = true
+    type: integer
+  rows:
+  - [1]
+...
+box.execute("SELECT true > false;")
+---
+- metadata:
+  - name: true > false
+    type: integer
+  rows:
+  - [1]
+...
+box.execute("SELECT true < false;")
+---
+- metadata:
+  - name: true < false
+    type: integer
+  rows:
+  - [0]
+...
+box.execute("SELECT null = true;")
+---
+- metadata:
+  - name: null = true
+    type: integer
+  rows:
+  - [null]
+...
+box.execute("SELECT unknown = true;")
+---
+- metadata:
+  - name: unknown = true
+    type: integer
+  rows:
+  - [null]
+...
+box.execute("SELECT 1 = true;")
+---
+- error: 'Type mismatch: can not convert INTEGER to boolean'
+...
+box.execute("SELECT 'abc' = true;")
+---
+- error: 'Type mismatch: can not convert TEXT to boolean'
+...
+box.execute("SELECT 1.123 > true;")
+---
+- error: 'Type mismatch: can not convert REAL to boolean'
+...
+box.execute("SELECT true IN (1, 'abc', true)")
+---
+- metadata:
+  - name: true IN (1, 'abc', true)
+    type: integer
+  rows:
+  - [1]
+...
+box.execute("SELECT true IN (1, 'abc', false)")
+---
+- metadata:
+  - name: true IN (1, 'abc', false)
+    type: integer
+  rows:
+  - [0]
+...
+box.execute("SELECT 1 LIMIT true;")
+---
+- error: Only positive integers are allowed in the LIMIT clause
+...
+box.execute("SELECT 1 LIMIT 1 OFFSET true;")
+---
+- error: Only positive integers are allowed in the OFFSET clause
+...
+box.execute("SELECT 'abc' || true;")
+---
+- error: 'Inconsistent types: expected TEXT or BLOB got BOOLEAN'
+...
+-- Boolean can take part in arithmetic operations.
+--
+box.execute("SELECT true + false;")
+---
+- error: 'Type mismatch: can not convert false to numeric'
+...
+box.execute("SELECT true * 1;")
+---
+- error: 'Type mismatch: can not convert true to numeric'
+...
+box.execute("SELECT false / 0;")
+---
+- error: 'Type mismatch: can not convert false to numeric'
+...
+box.execute("SELECT not true;")
+---
+- error: 'Type mismatch: can not convert true to integer'
+...
+box.execute("SELECT ~true;")
+---
+- error: 'Type mismatch: can not convert true to integer'
+...
+box.execute("SELECT -true;")
+---
+- error: 'Type mismatch: can not convert true to numeric'
+...
+box.execute("SELECT true << 1;")
+---
+- error: 'Type mismatch: can not convert true to integer'
+...
+box.execute("SELECT true | 1;")
+---
+- error: 'Type mismatch: can not convert true to integer'
+...
+box.execute("SELECT true and false;")
+---
+- metadata:
+  - name: true and false
+    type: number
+  rows:
+  - [0]
+...
+box.execute("SELECT true or unknown;")
+---
+- metadata:
+  - name: true or unknown
+    type: number
+  rows:
+  - [1]
+...
+box.execute("CREATE TABLE t (id INT PRIMARY KEY, b BOOLEAN);")
+---
+- rowcount: 1
+...
+box.execute("INSERT INTO t VALUES (1, true);")
+---
+- rowcount: 1
+...
+box.execute("INSERT INTO t VALUES (2, false);")
+---
+- rowcount: 1
+...
+box.execute("INSERT INTO t VALUES (3, unknown)")
+---
+- rowcount: 1
+...
+box.execute("SELECT b FROM t;")
+---
+- metadata:
+  - name: B
+    type: boolean
+  rows:
+  - [true]
+  - [false]
+  - [null]
+...
+box.execute("SELECT b FROM t WHERE b = false;")
+---
+- metadata:
+  - name: B
+    type: boolean
+  rows:
+  - [false]
+...
+box.execute("SELECT b FROM t WHERE b IS NULL;")
+---
+- metadata:
+  - name: B
+    type: boolean
+  rows:
+  - [null]
+...
+box.execute("SELECT b FROM t WHERE b IN (false, 1, 'abc')")
+---
+- metadata:
+  - name: B
+    type: boolean
+  rows:
+  - [false]
+...
+box.execute("SELECT b FROM t WHERE b BETWEEN false AND true;")
+---
+- metadata:
+  - name: B
+    type: boolean
+  rows:
+  - [true]
+  - [false]
+...
+box.execute("SELECT b FROM t WHERE b BETWEEN true AND false;")
+---
+- metadata:
+  - name: B
+    type: boolean
+  rows: []
+...
+box.execute("SELECT b FROM t ORDER BY b;")
+---
+- metadata:
+  - name: B
+    type: scalar
+  rows:
+  - [null]
+  - [false]
+  - [true]
+...
+box.execute("SELECT b FROM t ORDER BY +b;")
+---
+- metadata:
+  - name: B
+    type: boolean
+  rows:
+  - [null]
+  - [false]
+  - [true]
+...
+box.execute("SELECT b FROM t ORDER BY b LIMIT 1;")
+---
+- metadata:
+  - name: B
+    type: scalar
+  rows:
+  - [null]
+...
+box.execute("SELECT b FROM t GROUP BY b LIMIT 1;")
+---
+- metadata:
+  - name: B
+    type: scalar
+  rows:
+  - [null]
+...
+box.execute("SELECT b FROM t LIMIT true;")
+---
+- error: Only positive integers are allowed in the LIMIT clause
+...
+-- Most of aggregates don't accept boolean arguments.
+--
+box.execute("SELECT sum(b) FROM t;")
+---
+- error: 'Type mismatch: can not convert true to number'
+...
+box.execute("SELECT avg(b) FROM t;")
+---
+- error: 'Type mismatch: can not convert true to number'
+...
+box.execute("SELECT total(b) FROM t;")
+---
+- error: 'Type mismatch: can not convert true to number'
+...
+box.execute("SELECT min(b) FROM t;")
+---
+- metadata:
+  - name: min(b)
+    type: scalar
+  rows:
+  - [false]
+...
+box.execute("SELECT max(b) FROM t;")
+---
+- metadata:
+  - name: max(b)
+    type: scalar
+  rows:
+  - [true]
+...
+box.execute("SELECT count(b) FROM t;")
+---
+- metadata:
+  - name: count(b)
+    type: integer
+  rows:
+  - [2]
+...
+box.execute("SELECT group_concat(b) FROM t;")
+---
+- metadata:
+  - name: group_concat(b)
+    type: string
+  rows:
+  - ['true,false']
+...
+-- Check other built-in functions.
+--
+box.execute("SELECT lower(b) FROM t;")
+---
+- metadata:
+  - name: lower(b)
+    type: string
+  rows:
+  - ['true']
+  - ['false']
+  - [null]
+...
+box.execute("SELECT upper(b) FROM t;")
+---
+- metadata:
+  - name: upper(b)
+    type: string
+  rows:
+  - ['TRUE']
+  - ['FALSE']
+  - [null]
+...
+box.execute("SELECT abs(b) FROM t;")
+---
+- error: 'Inconsistent types: expected number got boolean'
+...
+box.execute("SELECT typeof(b) FROM t;")
+---
+- metadata:
+  - name: typeof(b)
+    type: string
+  rows:
+  - ['boolean']
+  - ['boolean']
+  - ['null']
+...
+box.execute("SELECT quote(b) FROM t;")
+---
+- metadata:
+  - name: quote(b)
+    type: string
+  rows:
+  - ['true']
+  - ['false']
+  - ['NULL']
+...
+box.execute("SELECT min(b, true) FROM t;")
+---
+- metadata:
+  - name: min(b, true)
+    type: scalar
+  rows:
+  - [true]
+  - [false]
+  - [null]
+...
+box.execute("SELECT quote(b) FROM t;")
+---
+- metadata:
+  - name: quote(b)
+    type: string
+  rows:
+  - ['true']
+  - ['false']
+  - ['NULL']
+...
+-- Test index search using boolean values.
+--
+box.execute("CREATE INDEX ib ON t(b);")
+---
+- rowcount: 1
+...
+box.execute("SELECT b FROM t WHERE b = false;")
+---
+- metadata:
+  - name: B
+    type: boolean
+  rows:
+  - [false]
+...
+box.execute("SELECT b FROM t WHERE b OR unknown ORDER BY b;")
+---
+- metadata:
+  - name: B
+    type: boolean
+  rows:
+  - [true]
+...
+-- Test UPDATE on boolean field.
+--
+box.execute("UPDATE t SET b = true WHERE b = false;")
+---
+- rowcount: 1
+...
+box.execute("SELECT b FROM t;")
+---
+- metadata:
+  - name: B
+    type: boolean
+  rows:
+  - [true]
+  - [true]
+  - [null]
+...
+-- Test constraints functionality.
+--
+box.execute("CREATE TABLE parent (id INT PRIMARY KEY, a BOOLEAN UNIQUE);")
+---
+- rowcount: 1
+...
+box.space.T:truncate()
+---
+...
+box.execute("ALTER TABLE t ADD CONSTRAINT fk1 FOREIGN KEY (b) REFERENCES parent (a);")
+---
+- rowcount: 0
+...
+box.execute("INSERT INTO t VALUES (1, true);")
+---
+- error: 'Failed to execute SQL statement: FOREIGN KEY constraint failed'
+...
+box.execute("INSERT INTO parent VALUES (1, true);")
+---
+- rowcount: 1
+...
+box.execute("INSERT INTO t VALUES (1, true);")
+---
+- rowcount: 1
+...
+box.execute("ALTER TABLE t DROP CONSTRAINT fk1;")
+---
+- rowcount: 0
+...
+box.space.PARENT:drop()
+---
+...
+box.execute("CREATE TABLE t1 (id INT PRIMARY KEY, a BOOLEAN CHECK (a = true));")
+---
+- rowcount: 1
+...
+box.execute("INSERT INTO t1 VALUES (1, false);")
+---
+- error: 'Failed to execute SQL statement: CHECK constraint failed: T1'
+...
+box.execute("INSERT INTO t1 VALUES (1, true);")
+---
+- rowcount: 1
+...
+box.space.T1:drop()
+---
+...
+box.execute("CREATE TABLE t1 (id INT PRIMARY KEY, a BOOLEAN DEFAULT true);")
+---
+- rowcount: 1
+...
+box.execute("INSERT INTO t1 (id) VALUES (1);")
+---
+- rowcount: 1
+...
+box.space.T1:select()
+---
+- - [1, true]
+...
+box.space.T1:drop()
+---
+...
+-- Check that VIEW inherits boolean type.
+--
+box.execute("CREATE VIEW v AS SELECT b FROM t;")
+---
+- rowcount: 1
+...
+box.space.V:format()[1]['type']
+---
+- boolean
+...
+box.space.V:drop()
+---
+...
+-- Test CAST facilities.
+--
+box.execute("SELECT CAST(true AS INTEGER);")
+---
+- metadata:
+  - name: CAST(true AS INTEGER)
+    type: integer
+  rows:
+  - [1]
+...
+box.execute("SELECT CAST(true AS TEXT);")
+---
+- metadata:
+  - name: CAST(true AS TEXT)
+    type: string
+  rows:
+  - ['TRUE']
+...
+box.execute("SELECT CAST(true AS FLOAT);")
+---
+- error: 'Type mismatch: can not convert true to number'
+...
+box.execute("SELECT CAST(true AS SCALAR);")
+---
+- metadata:
+  - name: CAST(true AS SCALAR)
+    type: scalar
+  rows:
+  - [true]
+...
+box.execute("SELECT CAST(1 AS BOOLEAN);")
+---
+- metadata:
+  - name: CAST(1 AS BOOLEAN)
+    type: boolean
+  rows:
+  - [true]
+...
+box.execute("SELECT CAST(1.123 AS BOOLEAN);")
+---
+- error: 'Type mismatch: can not convert 1.123 to boolean'
+...
+box.execute("SELECT CAST('abc' AS BOOLEAN);")
+---
+- error: 'Type mismatch: can not convert abc to boolean'
+...
+box.execute("SELECT CAST('  TrUe' AS BOOLEAN);")
+---
+- metadata:
+  - name: CAST('  TrUe' AS BOOLEAN)
+    type: boolean
+  rows:
+  - [true]
+...
+box.execute("SELECT CAST(X'4D6564766564' AS BOOLEAN);")
+---
+- error: 'Type mismatch: can not convert Medved to boolean'
+...
+-- Make sure that SCALAR can handle boolean values.
+--
+box.execute("CREATE TABLE t1 (id INT PRIMARY KEY, s SCALAR);")
+---
+- rowcount: 1
+...
+box.execute("INSERT INTO t1 SELECT * FROM t;")
+---
+- rowcount: 1
+...
+box.execute("SELECT s FROM t1 WHERE s = true;")
+---
+- metadata:
+  - name: S
+    type: scalar
+  rows:
+  - [true]
+...
+box.execute("INSERT INTO t1 VALUES (3, 'abc'), (4, 12.5);")
+---
+- rowcount: 2
+...
+box.execute("SELECT s FROM t1 WHERE s = true;")
+---
+- error: 'Type mismatch: can not convert TEXT to boolean'
+...
+box.execute("SELECT s FROM t1 WHERE s < true;")
+---
+- error: 'Type mismatch: can not convert TEXT to boolean'
+...
+box.execute("SELECT s FROM t1 WHERE s IN (true, 1, 'abcd')")
+---
+- metadata:
+  - name: S
+    type: scalar
+  rows:
+  - [true]
+...
+box.space.T:drop()
+---
+...
+box.space.T1:drop()
+---
+...
diff --git a/test/sql/types.test.lua b/test/sql/types.test.lua
index dab5872e0..2aed0fe94 100644
--- a/test/sql/types.test.lua
+++ b/test/sql/types.test.lua
@@ -70,3 +70,135 @@ box.execute("SELECT * FROM t1 WHERE s LIKE 'int';")
 box.execute("SELECT * FROM t1 WHERE 'int' LIKE 4;")
 box.execute("SELECT NULL LIKE s FROM t1;")
 box.space.T1:drop()
+
+-- Test basic capabilities of boolean type.
+--
+box.execute("SELECT true;")
+box.execute("SELECT false;")
+box.execute("SELECT unknown;")
+box.execute("SELECT true = false;")
+box.execute("SELECT true = true;")
+box.execute("SELECT true > false;")
+box.execute("SELECT true < false;")
+box.execute("SELECT null = true;")
+box.execute("SELECT unknown = true;")
+box.execute("SELECT 1 = true;")
+box.execute("SELECT 'abc' = true;")
+box.execute("SELECT 1.123 > true;")
+box.execute("SELECT true IN (1, 'abc', true)")
+box.execute("SELECT true IN (1, 'abc', false)")
+box.execute("SELECT 1 LIMIT true;")
+box.execute("SELECT 1 LIMIT 1 OFFSET true;")
+box.execute("SELECT 'abc' || true;")
+
+-- Boolean can take part in arithmetic operations.
+--
+box.execute("SELECT true + false;")
+box.execute("SELECT true * 1;")
+box.execute("SELECT false / 0;")
+box.execute("SELECT not true;")
+box.execute("SELECT ~true;")
+box.execute("SELECT -true;")
+box.execute("SELECT true << 1;")
+box.execute("SELECT true | 1;")
+box.execute("SELECT true and false;")
+box.execute("SELECT true or unknown;")
+
+box.execute("CREATE TABLE t (id INT PRIMARY KEY, b BOOLEAN);")
+box.execute("INSERT INTO t VALUES (1, true);")
+box.execute("INSERT INTO t VALUES (2, false);")
+box.execute("INSERT INTO t VALUES (3, unknown)")
+box.execute("SELECT b FROM t;")
+box.execute("SELECT b FROM t WHERE b = false;")
+box.execute("SELECT b FROM t WHERE b IS NULL;")
+box.execute("SELECT b FROM t WHERE b IN (false, 1, 'abc')")
+box.execute("SELECT b FROM t WHERE b BETWEEN false AND true;")
+box.execute("SELECT b FROM t WHERE b BETWEEN true AND false;")
+box.execute("SELECT b FROM t ORDER BY b;")
+box.execute("SELECT b FROM t ORDER BY +b;")
+box.execute("SELECT b FROM t ORDER BY b LIMIT 1;")
+box.execute("SELECT b FROM t GROUP BY b LIMIT 1;")
+box.execute("SELECT b FROM t LIMIT true;")
+
+-- Most of aggregates don't accept boolean arguments.
+--
+box.execute("SELECT sum(b) FROM t;")
+box.execute("SELECT avg(b) FROM t;")
+box.execute("SELECT total(b) FROM t;")
+box.execute("SELECT min(b) FROM t;")
+box.execute("SELECT max(b) FROM t;")
+box.execute("SELECT count(b) FROM t;")
+box.execute("SELECT group_concat(b) FROM t;")
+
+-- Check other built-in functions.
+--
+box.execute("SELECT lower(b) FROM t;")
+box.execute("SELECT upper(b) FROM t;")
+box.execute("SELECT abs(b) FROM t;")
+box.execute("SELECT typeof(b) FROM t;")
+box.execute("SELECT quote(b) FROM t;")
+box.execute("SELECT min(b, true) FROM t;")
+box.execute("SELECT quote(b) FROM t;")
+
+-- Test index search using boolean values.
+--
+box.execute("CREATE INDEX ib ON t(b);")
+box.execute("SELECT b FROM t WHERE b = false;")
+box.execute("SELECT b FROM t WHERE b OR unknown ORDER BY b;")
+
+-- Test UPDATE on boolean field.
+--
+box.execute("UPDATE t SET b = true WHERE b = false;")
+box.execute("SELECT b FROM t;")
+
+-- Test constraints functionality.
+--
+box.execute("CREATE TABLE parent (id INT PRIMARY KEY, a BOOLEAN UNIQUE);")
+box.space.T:truncate()
+box.execute("ALTER TABLE t ADD CONSTRAINT fk1 FOREIGN KEY (b) REFERENCES parent (a);")
+box.execute("INSERT INTO t VALUES (1, true);")
+box.execute("INSERT INTO parent VALUES (1, true);")
+box.execute("INSERT INTO t VALUES (1, true);")
+box.execute("ALTER TABLE t DROP CONSTRAINT fk1;")
+box.space.PARENT:drop()
+
+box.execute("CREATE TABLE t1 (id INT PRIMARY KEY, a BOOLEAN CHECK (a = true));")
+box.execute("INSERT INTO t1 VALUES (1, false);")
+box.execute("INSERT INTO t1 VALUES (1, true);")
+box.space.T1:drop()
+
+box.execute("CREATE TABLE t1 (id INT PRIMARY KEY, a BOOLEAN DEFAULT true);")
+box.execute("INSERT INTO t1 (id) VALUES (1);")
+box.space.T1:select()
+box.space.T1:drop()
+
+-- Check that VIEW inherits boolean type.
+--
+box.execute("CREATE VIEW v AS SELECT b FROM t;")
+box.space.V:format()[1]['type']
+box.space.V:drop()
+
+-- Test CAST facilities.
+--
+box.execute("SELECT CAST(true AS INTEGER);")
+box.execute("SELECT CAST(true AS TEXT);")
+box.execute("SELECT CAST(true AS FLOAT);")
+box.execute("SELECT CAST(true AS SCALAR);")
+box.execute("SELECT CAST(1 AS BOOLEAN);")
+box.execute("SELECT CAST(1.123 AS BOOLEAN);")
+box.execute("SELECT CAST('abc' AS BOOLEAN);")
+box.execute("SELECT CAST('  TrUe' AS BOOLEAN);")
+box.execute("SELECT CAST(X'4D6564766564' AS BOOLEAN);")
+
+-- Make sure that SCALAR can handle boolean values.
+--
+box.execute("CREATE TABLE t1 (id INT PRIMARY KEY, s SCALAR);")
+box.execute("INSERT INTO t1 SELECT * FROM t;")
+box.execute("SELECT s FROM t1 WHERE s = true;")
+box.execute("INSERT INTO t1 VALUES (3, 'abc'), (4, 12.5);")
+box.execute("SELECT s FROM t1 WHERE s = true;")
+box.execute("SELECT s FROM t1 WHERE s < true;")
+box.execute("SELECT s FROM t1 WHERE s IN (true, 1, 'abcd')")
+
+box.space.T:drop()
+box.space.T1:drop()
-- 
2.15.1

  parent reply	other threads:[~2019-04-14 15:04 UTC|newest]

Thread overview: 42+ messages / expand[flat|nested]  mbox.gz  Atom feed  top
2019-04-14 15:03 [tarantool-patches] [PATCH 0/9] Introduce type BOOLEAN in SQL Nikita Pettik
2019-04-14 15:03 ` [tarantool-patches] [PATCH 1/9] sql: refactor mem_apply_numeric_type() Nikita Pettik
2019-04-14 15:04 ` [tarantool-patches] [PATCH 2/9] sql: disallow text values participate in sum() aggregate Nikita Pettik
2019-04-16 14:12   ` [tarantool-patches] " Vladislav Shpilevoy
2019-04-18 17:54     ` n.pettik
2019-04-22 18:02       ` Vladislav Shpilevoy
2019-04-23 19:58         ` n.pettik
2019-04-14 15:04 ` [tarantool-patches] [PATCH 3/9] sql: use msgpack types instead of custom ones Nikita Pettik
2019-04-16 14:12   ` [tarantool-patches] " Vladislav Shpilevoy
2019-04-18 17:54     ` n.pettik
2019-04-22 18:02       ` Vladislav Shpilevoy
2019-04-23 19:58         ` n.pettik
2019-04-14 15:04 ` Nikita Pettik [this message]
2019-04-16 14:12   ` [tarantool-patches] Re: [PATCH 4/9] sql: introduce type boolean Vladislav Shpilevoy
2019-04-18 17:54     ` n.pettik
2019-04-22 18:02       ` Vladislav Shpilevoy
2019-04-23 19:58         ` n.pettik
2019-04-23 21:06           ` Vladislav Shpilevoy
2019-04-14 15:04 ` [tarantool-patches] [PATCH 5/9] sql: improve type determination for column meta Nikita Pettik
2019-04-16 14:12   ` [tarantool-patches] " Vladislav Shpilevoy
2019-04-18 17:54     ` n.pettik
2019-04-22 18:02       ` Vladislav Shpilevoy
2019-04-23 19:58         ` n.pettik
2019-04-14 15:04 ` [tarantool-patches] [PATCH 6/9] sql: make comparison predicate return boolean Nikita Pettik
2019-04-16 14:12   ` [tarantool-patches] " Vladislav Shpilevoy
2019-04-18 17:54     ` n.pettik
2019-04-14 15:04 ` [tarantool-patches] [PATCH 7/9] sql: make predicates accept and " Nikita Pettik
2019-04-16 14:12   ` [tarantool-patches] " Vladislav Shpilevoy
2019-04-18 17:55     ` n.pettik
2019-04-14 15:04 ` [tarantool-patches] [PATCH 9/9] sql: make <search condition> accept only boolean Nikita Pettik
2019-04-16 14:12   ` [tarantool-patches] " Vladislav Shpilevoy
2019-04-18 17:55     ` n.pettik
2019-04-22 18:02       ` Vladislav Shpilevoy
2019-04-23 19:59         ` n.pettik
2019-04-23 21:06           ` Vladislav Shpilevoy
2019-04-23 22:01             ` n.pettik
     [not found] ` <b2a84f129c2343d3da3311469cbb7b20488a21c2.1555252410.git.korablev@tarantool.org>
2019-04-16 14:12   ` [tarantool-patches] Re: [PATCH 8/9] sql: make LIKE predicate return boolean result Vladislav Shpilevoy
2019-04-18 17:55     ` n.pettik
2019-04-22 18:02       ` Vladislav Shpilevoy
2019-04-23 19:58         ` n.pettik
2019-04-24 10:28 ` [tarantool-patches] Re: [PATCH 0/9] Introduce type BOOLEAN in SQL Vladislav Shpilevoy
2019-04-25  8:46 ` Kirill Yukhin

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=e9cc494f59592a18065f5b6682ad199b42b571c9.1555252410.git.korablev@tarantool.org \
    --to=korablev@tarantool.org \
    --cc=kostja@tarantool.org \
    --cc=tarantool-patches@freelists.org \
    --cc=v.shpilevoy@tarantool.org \
    --subject='Re: [tarantool-patches] [PATCH 4/9] sql: introduce type boolean' \
    /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