From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id BCFA52BBAB for ; Sun, 14 Apr 2019 11:04:14 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id eG6kx_6pI-Rh for ; Sun, 14 Apr 2019 11:04:14 -0400 (EDT) Received: from smtpng3.m.smailru.net (smtpng3.m.smailru.net [94.100.177.149]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id 13C4E2BB33 for ; Sun, 14 Apr 2019 11:04:14 -0400 (EDT) From: Nikita Pettik Subject: [tarantool-patches] [PATCH 4/9] sql: introduce type boolean Date: Sun, 14 Apr 2019 18:04:02 +0300 Message-Id: In-Reply-To: References: In-Reply-To: References: Sender: tarantool-patches-bounce@freelists.org Errors-to: tarantool-patches-bounce@freelists.org Reply-To: tarantool-patches@freelists.org List-Help: List-Unsubscribe: List-software: Ecartis version 1.0.0 List-Id: tarantool-patches List-Subscribe: List-Owner: List-post: List-Archive: To: tarantool-patches@freelists.org Cc: v.shpilevoy@tarantool.org, kostja@tarantool.org, Nikita Pettik 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