From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from [87.239.111.99] (localhost [127.0.0.1]) by dev.tarantool.org (Postfix) with ESMTP id CC6E971064; Thu, 11 Nov 2021 14:02:40 +0300 (MSK) DKIM-Filter: OpenDKIM Filter v2.11.0 dev.tarantool.org CC6E971064 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=tarantool.org; s=dev; t=1636628561; bh=cxVbM3zTh2ugMen2hPwMkA4JFCdyBTpMqd/B9hgfwlA=; h=To:Cc:Date:In-Reply-To:References:Subject:List-Id: List-Unsubscribe:List-Archive:List-Post:List-Help:List-Subscribe: From:Reply-To:From; b=GDYsd1n7l/kqlAIwKBnDb49741h++1cbAXG5/pHKw/ole9WIBw4bMZ7i1rJx4seW0 bsjuRlPHKBjCc3IhJIeGMhMyFX8mHl9JKkXT63POF0o8G/cVizG/HzLDeZLJL4LvSY qXzggQdT5jk18FB1uX+C5zpV1pi36pyq8MW4c5HE= Received: from smtpng1.i.mail.ru (smtpng1.i.mail.ru [94.100.181.251]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by dev.tarantool.org (Postfix) with ESMTPS id D2A9471188 for ; Thu, 11 Nov 2021 13:53:42 +0300 (MSK) DKIM-Filter: OpenDKIM Filter v2.11.0 dev.tarantool.org D2A9471188 Received: by smtpng1.m.smailru.net with esmtpa (envelope-from ) id 1ml7i9-0007qF-RM; Thu, 11 Nov 2021 13:53:42 +0300 To: kyukhin@tarantool.org Cc: tarantool-patches@dev.tarantool.org Date: Thu, 11 Nov 2021 13:53:41 +0300 Message-Id: X-Mailer: git-send-email 2.25.1 In-Reply-To: References: MIME-Version: 1.0 Content-Transfer-Encoding: 8bit X-4EC0790: 10 X-7564579A: 646B95376F6C166E X-77F55803: 4F1203BC0FB41BD9731B3922EC063979EDB9C2FB80C34FA759345864EB942FB700894C459B0CD1B9A47DD8398542F24D16D1AB92343508406156B6F96FCCACDC66F2506D529139D3 X-7FA49CB5: FF5795518A3D127A4AD6D5ED66289B5278DA827A17800CE714773D61402E8DE9EA1F7E6F0F101C67BD4B6F7A4D31EC0BCC500DACC3FED6E28638F802B75D45FF8AA50765F7900637BF3E16F0C87855308638F802B75D45FF36EB9D2243A4F8B5A6FCA7DBDB1FC311F39EFFDF887939037866D6147AF826D859F803CCC789FE701E625AE4D6B138E4117882F4460429724CE54428C33FAD305F5C1EE8F4F765FCF1175FABE1C0F9B6A471835C12D1D9774AD6D5ED66289B52BA9C0B312567BB23117882F446042972877693876707352033AC447995A7AD18C26CFBAC0749D213D2E47CDBA5A96583BA9C0B312567BB231DD303D21008E29813377AFFFEAFD269A417C69337E82CC2E827F84554CEF50127C277FBC8AE2E8BA83251EDC214901ED5E8D9A59859A8B66F6A3E018CF4DC80089D37D7C0E48F6C5571747095F342E88FB05168BE4CE3AF X-C1DE0DAB: C20DE7B7AB408E4181F030C43753B8186998911F362727C414F749A5E30D975C7E9FEBB9C11794A10F7AAB75218370B002B7BE97E1EB4A149C2B6934AE262D3EE7EAB7254005DCED7532B743992DF240BDC6A1CF3F042BAD6DF99611D93F60EFF7EC8CC465A83DA3699F904B3F4130E343918A1A30D5E7FCCB5012B2E24CD356 X-C8649E89: 4E36BF7865823D7055A7F0CF078B5EC49A30900B95165D34682FD2D2951524C540152F776FADC578AB77209868BFE5D319559185DCA3FCF3BFEC4DB86F2898551D7E09C32AA3244CE4B130AC919B3878A6DE3A597A45B95C853296C06374E602729B2BEF169E0186 X-D57D3AED: 3ZO7eAau8CL7WIMRKs4sN3D3tLDjz0dLbV79QFUyzQ2Ujvy7cMT6pYYqY16iZVKkSc3dCLJ7zSJH7+u4VD18S7Vl4ZUrpaVfd2+vE6kuoey4m4VkSEu530nj6fImhcD4MUrOEAnl0W826KZ9Q+tr5ycPtXkTV4k65bRjmOUUP8cvGozZ33TWg5HZplvhhXbhDGzqmQDTd6OAevLeAnq3Ra9uf7zvY2zzsIhlcp/Y7m53TZgf2aB4JOg4gkr2bioj4t8MBgWr8bJr4mYYE3HzbA== X-Mailru-Sender: 689FA8AB762F7393C37E3C1AEC41BA5D0E2C31D3712C644ABE4D1F83CFCB47FC83D72C36FC87018B9F80AB2734326CD2FB559BB5D741EB96352A0ABBE4FDA4210A04DAD6CC59E33667EA787935ED9F1B X-Mras: Ok Subject: [Tarantool-patches] [PATCH v1 3/3] sql: introduce field type ANY X-BeenThere: tarantool-patches@dev.tarantool.org X-Mailman-Version: 2.1.34 Precedence: list List-Id: Tarantool development patches List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , From: Mergen Imeev via Tarantool-patches Reply-To: imeevma@tarantool.org Errors-To: tarantool-patches-bounces@dev.tarantool.org Sender: "Tarantool-patches" Closes #3174 @TarantoolBot document Title: Field type ANY in SQL Properties of type ANY in SQL: 1) typeof() with an argument of type ANY returns "any"; 2) any value of any type can be implicitly and explicitly cast to the ANY type; 3) a value of type ANY cannot be implicitly cast to any other type; 4) a value of type ANY cannot participate in arithmetic, bitwise, comparison, and concationation operations. --- extra/mkkeywordhash.c | 3 +- src/box/sql/func.c | 4 +- src/box/sql/mem.c | 49 ++- src/box/sql/mem.h | 4 +- src/box/sql/parse.y | 3 +- src/box/sql/vdbe.c | 4 +- test/sql-tap/metatypes.test.lua | 568 ++++++++++++++++++++++++++++++-- 7 files changed, 594 insertions(+), 41 deletions(-) diff --git a/extra/mkkeywordhash.c b/extra/mkkeywordhash.c index 3e4200417..aaeb7d51b 100644 --- a/extra/mkkeywordhash.c +++ b/extra/mkkeywordhash.c @@ -176,10 +176,11 @@ static Keyword aKeywordTable[] = { { "VALUES", "TK_VALUES", true }, { "VARBINARY", "TK_VARBINARY", true }, { "VIEW", "TK_VIEW", true }, + { "WILDCARD", "TK_STANDARD", true }, { "WITH", "TK_WITH", true }, { "WHEN", "TK_WHEN", true }, { "WHERE", "TK_WHERE", true }, - { "ANY", "TK_STANDARD", true }, + { "ANY", "TK_ANY", true }, { "ASENSITIVE", "TK_STANDARD", true }, { "BLOB", "TK_STANDARD", true }, { "BINARY", "TK_ID", true }, diff --git a/src/box/sql/func.c b/src/box/sql/func.c index 63d7536d9..43f6fc40a 100644 --- a/src/box/sql/func.c +++ b/src/box/sql/func.c @@ -318,6 +318,8 @@ typeofFunc(struct sql_context *context, int argc, struct Mem *argv) { (void)argc; const char *z = 0; + if ((argv[0].flags & MEM_Any) != 0) + return mem_set_str0_static(context->pOut, "any"); if ((argv[0].flags & MEM_Number) != 0) return mem_set_str0_static(context->pOut, "number"); if ((argv[0].flags & MEM_Scalar) != 0) @@ -2013,7 +2015,7 @@ static struct sql_func_definition definitions[] = { {"MIN", 1, {FIELD_TYPE_STRING}, FIELD_TYPE_STRING, step_minmax, NULL}, {"MIN", 1, {FIELD_TYPE_SCALAR}, FIELD_TYPE_SCALAR, step_minmax, NULL}, - {"NULLIF", 2, {field_type_MAX, field_type_MAX}, FIELD_TYPE_SCALAR, + {"NULLIF", 2, {FIELD_TYPE_SCALAR, FIELD_TYPE_SCALAR}, FIELD_TYPE_SCALAR, nullifFunc, NULL}, {"POSITION", 2, {FIELD_TYPE_STRING, FIELD_TYPE_STRING}, FIELD_TYPE_INTEGER, position_func, NULL}, diff --git a/src/box/sql/mem.c b/src/box/sql/mem.c index f648c5d62..244415e02 100644 --- a/src/box/sql/mem.c +++ b/src/box/sql/mem.c @@ -727,7 +727,7 @@ str_to_bin(struct Mem *mem) { assert(mem->type == MEM_TYPE_STR); mem->type = MEM_TYPE_BIN; - mem->flags &= ~(MEM_Term | MEM_Scalar); + mem->flags &= ~(MEM_Term | MEM_Scalar | MEM_Any); return 0; } @@ -774,7 +774,7 @@ bin_to_str(struct Mem *mem) { assert(mem->type == MEM_TYPE_BIN); mem->type = MEM_TYPE_STR; - mem->flags &= ~MEM_Scalar; + mem->flags &= ~(MEM_Scalar | MEM_Any); return 0; } @@ -1376,7 +1376,7 @@ mem_to_str(struct Mem *mem) assert(mem->type < MEM_TYPE_INVALID); switch (mem->type) { case MEM_TYPE_STR: - mem->flags &= ~MEM_Scalar; + mem->flags &= ~(MEM_Scalar | MEM_Any); return 0; case MEM_TYPE_INT: case MEM_TYPE_UINT: @@ -1440,7 +1440,7 @@ mem_cast_explicit(struct Mem *mem, enum field_type type) if (mem->type == MEM_TYPE_STR) return str_to_bin(mem); if (mem_is_bytes(mem)) { - mem->flags &= ~MEM_Scalar; + mem->flags &= ~(MEM_Scalar | MEM_Any); return 0; } if (mem->type == MEM_TYPE_UUID) @@ -1478,7 +1478,11 @@ mem_cast_explicit(struct Mem *mem, enum field_type type) if ((mem->type & (MEM_TYPE_MAP | MEM_TYPE_ARRAY)) != 0) return -1; mem->flags |= MEM_Scalar; - mem->flags &= ~MEM_Number; + mem->flags &= ~(MEM_Number | MEM_Any); + return 0; + case FIELD_TYPE_ANY: + mem->flags |= MEM_Any; + mem->flags &= ~(MEM_Number | MEM_Scalar); return 0; default: break; @@ -1491,11 +1495,21 @@ mem_cast_implicit(struct Mem *mem, enum field_type type) { if (mem->type == MEM_TYPE_NULL || type == field_type_MAX) return 0; - if ((mem->flags & MEM_Scalar) != 0 && type != FIELD_TYPE_SCALAR) - return -1; - if ((mem->flags & MEM_Number) != 0 && type != FIELD_TYPE_SCALAR && - type != FIELD_TYPE_NUMBER) - return -1; + if (mem_is_metatype(mem) && type != FIELD_TYPE_ANY) { + if ((mem->flags & MEM_Number) != 0) { + if (type != FIELD_TYPE_NUMBER && + type != FIELD_TYPE_SCALAR && + type != FIELD_TYPE_ANY) + return -1; + } else if ((mem->flags & MEM_Scalar) != 0) { + if (type != FIELD_TYPE_SCALAR && + type != FIELD_TYPE_ANY) + return -1; + } else { + if (type != FIELD_TYPE_ANY) + return -1; + } + } switch (type) { case FIELD_TYPE_UNSIGNED: if (mem->type == MEM_TYPE_UINT) { @@ -1509,7 +1523,7 @@ mem_cast_implicit(struct Mem *mem, enum field_type type) return -1; case FIELD_TYPE_STRING: if (mem->type == MEM_TYPE_STR) { - mem->flags &= ~MEM_Scalar; + mem->flags &= ~(MEM_Scalar | MEM_Any); return 0; } return -1; @@ -1544,7 +1558,7 @@ mem_cast_implicit(struct Mem *mem, enum field_type type) case FIELD_TYPE_VARBINARY: if ((mem->type & (MEM_TYPE_BIN | MEM_TYPE_MAP | MEM_TYPE_ARRAY)) != 0) { - mem->flags &= ~MEM_Scalar; + mem->flags &= ~(MEM_Scalar | MEM_Any); return 0; } return -1; @@ -1579,7 +1593,7 @@ mem_cast_implicit(struct Mem *mem, enum field_type type) if ((mem->type & (MEM_TYPE_MAP | MEM_TYPE_ARRAY)) != 0) return -1; mem->flags |= MEM_Scalar; - mem->flags &= ~MEM_Number; + mem->flags &= ~(MEM_Number | MEM_Any); return 0; case FIELD_TYPE_UUID: if (mem->type != MEM_TYPE_UUID) @@ -1587,6 +1601,8 @@ mem_cast_implicit(struct Mem *mem, enum field_type type) mem->flags = 0; return 0; case FIELD_TYPE_ANY: + mem->flags |= MEM_Any; + mem->flags &= ~(MEM_Number | MEM_Scalar); return 0; default: break; @@ -2540,6 +2556,11 @@ mem_cmp(const struct Mem *a, const struct Mem *b, int *result, *result = 1; return 0; } + if (((a->flags | b->flags) & MEM_Any) != 0) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(a), + "comparable type"); + return -1; + } if (((a->flags | b->flags) & MEM_Scalar) != 0) { *result = mem_cmp_scalar(a, b, coll); return 0; @@ -2575,6 +2596,8 @@ char * mem_type_to_str(const struct Mem *p) { assert(p != NULL); + if ((p->flags & MEM_Any) != 0) + return "any"; if ((p->flags & MEM_Scalar) != 0) return "scalar"; if ((p->flags & MEM_Number) != 0) diff --git a/src/box/sql/mem.h b/src/box/sql/mem.h index 242f910db..a8b132479 100644 --- a/src/box/sql/mem.h +++ b/src/box/sql/mem.h @@ -98,6 +98,8 @@ struct Mem { #define MEM_Number 0x0001 /** MEM is of SCALAR meta-type. */ #define MEM_Scalar 0x0002 +/** MEM is of ANY meta-type. */ +#define MEM_Any 0x0004 #define MEM_Cleared 0x0200 /* NULL set by OP_Null, not from data */ /* Whenever Mem contains a valid string or blob representation, one of @@ -145,7 +147,7 @@ mem_is_num(const struct Mem *mem) static inline bool mem_is_metatype(const struct Mem *mem) { - return (mem->flags & (MEM_Number | MEM_Scalar)) != 0; + return (mem->flags & (MEM_Number | MEM_Scalar | MEM_Any)) != 0; } static inline bool diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y index ae480a5d1..4b32f33f5 100644 --- a/src/box/sql/parse.y +++ b/src/box/sql/parse.y @@ -270,7 +270,7 @@ columnlist ::= tcons. QUERY KEY OFFSET RAISE RELEASE REPLACE RESTRICT RENAME CTIME_KW IF ENABLE DISABLE UUID . -%wildcard ANY. +%wildcard WILDCARD. // And "ids" is an identifer-or-string. @@ -1841,6 +1841,7 @@ typedef(A) ::= BOOL . { A.type = FIELD_TYPE_BOOLEAN; } typedef(A) ::= BOOLEAN . { A.type = FIELD_TYPE_BOOLEAN; } typedef(A) ::= VARBINARY . { A.type = FIELD_TYPE_VARBINARY; } typedef(A) ::= UUID . { A.type = FIELD_TYPE_UUID; } +typedef(A) ::= ANY . { A.type = FIELD_TYPE_ANY; } /** * Time-like types are temporary disabled, until they are diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c index 7b1203bc8..0c4e38557 100644 --- a/src/box/sql/vdbe.c +++ b/src/box/sql/vdbe.c @@ -1934,7 +1934,9 @@ case OP_Column: { /* Currently PSEUDO cursor does not have info about field types. */ if (pC->eCurType == CURTYPE_TARANTOOL) field_type = pC->uc.pCursor->space->def->fields[p2].type; - if (field_type == FIELD_TYPE_SCALAR) + if (field_type == FIELD_TYPE_ANY) + pDest->flags |= MEM_Any; + else if (field_type == FIELD_TYPE_SCALAR) pDest->flags |= MEM_Scalar; else if (field_type == FIELD_TYPE_NUMBER) pDest->flags |= MEM_Number; diff --git a/test/sql-tap/metatypes.test.lua b/test/sql-tap/metatypes.test.lua index dfa5bbab4..12c3ce06d 100755 --- a/test/sql-tap/metatypes.test.lua +++ b/test/sql-tap/metatypes.test.lua @@ -1,32 +1,51 @@ #!/usr/bin/env tarantool local test = require("sqltester") -test:plan(19) +test:plan(80) -- Check that SCALAR and NUMBER meta-types works as intended. -box.execute([[CREATE TABLE t (i INT PRIMARY KEY, s SCALAR, n NUMBER);]]) +box.execute([[CREATE TABLE t (i INT PRIMARY KEY, s SCALAR, n NUMBER, a ANY);]]) +box.execute([[CREATE TABLE t1 (id INTEGER PRIMARY KEY AUTOINCREMENT, a ANY, + g UNSIGNED, t STRING, n NUMBER, f DOUBLE, + i INTEGER, b BOOLEAN, v VARBINARY, s SCALAR, + d DECIMAL, u UUID);]]) +box.execute([[INSERT INTO t1(id) VALUES(NULL);]]) -- -- Check that implicit cast from numeric types to NUMBER and from scalar types -- to SCALAR works properly. -- -local uuid = [[CAST('11111111-1111-1111-1111-111111111111' AS UUID)]] +local uuid_str = [[11111111-1111-1111-1111-111111111111]] +local uuid = require('uuid').fromstr(uuid_str) +local dec = require('decimal').new(1.5) test:do_execsql_test( "metatypes-1.1", [[ - INSERT INTO t VALUES(1, 1, 1); - INSERT INTO t VALUES(2, 2e0, 2e0); + INSERT INTO t VALUES(1, 1, 1, 1); + INSERT INTO t VALUES(2, 2e0, 2e0, 2e0); INSERT INTO t(i, s) VALUES(3, '3'); INSERT INTO t(i, s) VALUES(4, true); INSERT INTO t(i, s) VALUES(5, x'35'); - INSERT INTO t(i, s) VALUES(6, ]]..uuid..[[); + INSERT INTO t(i, s) VALUES(6, CAST(']]..uuid_str..[[' AS UUID)); + INSERT INTO t(i, a) VALUES(7, '3'); + INSERT INTO t(i, a) VALUES(8, true); + INSERT INTO t(i, a) VALUES(9, x'35'); + INSERT INTO t(i, a) VALUES(10, CAST(']]..uuid_str..[[' AS UUID)); + INSERT INTO t(i, a) VALUES(11, 3e0); + INSERT INTO t(i, a) VALUES(12, CAST(1.5 AS DECIMAL)); SELECT * FROM t; ]], { - 1,1,1, - 2,2,2, - 3,"3","", - 4,true,"", - 5,"5","", - 6,require('uuid').fromstr('11111111-1111-1111-1111-111111111111'),"" + 1, 1, 1, 1, + 2, 2, 2, 2, + 3, "3", "", "", + 4, true, "", "", + 5, "5", "", "", + 6, uuid, "", "", + 7, "", "", "3", + 8, "", "", true, + 9, "", "", "5", + 10, "", "", uuid, + 11, "", "", 3, + 12, "", "", dec }) -- Check that typeof() returns right result. @@ -35,7 +54,8 @@ test:do_execsql_test( [[ SELECT typeof(s) FROM t; ]], { - "scalar","scalar","scalar","scalar","scalar","scalar" + "scalar", "scalar", "scalar", "scalar", "scalar", "scalar", + "NULL", "NULL", "NULL", "NULL", "NULL", "NULL" }) test:do_execsql_test( @@ -43,7 +63,17 @@ test:do_execsql_test( [[ SELECT typeof(n) FROM t; ]], { - "number","number","NULL","NULL","NULL","NULL" + "number", "number", "NULL", "NULL", "NULL", "NULL", "NULL", "NULL", + "NULL", "NULL", "NULL", "NULL" + }) + +test:do_execsql_test( + "metatypes-1.4", + [[ + SELECT typeof(a) FROM t; + ]], { + "any", "any", "NULL", "NULL", "NULL", "NULL", "any", "any", "any", + "any", "any", "any" }) -- @@ -90,7 +120,98 @@ test:do_catchsql_test( 1, "Type mismatch: can not convert number(11) to integer" }) --- Check that arithmetic operations are prohibited for NUMBER and SCALAR values. +-- Check that implicit cast from ANY to any other type is prohibited. +test:do_execsql_test( + "metatypes-2.6", + [[ + INSERT INTO t1(a) VALUES(CAST(1 AS ANY)); + SELECT * FROM t1; + ]], { + 1, "", "", "", "", "", "", "", "", "", "", "", + 2, 1, "", "", "", "", "", "", "", "", "", "" + }) + +test:do_catchsql_test( + "metatypes-2.7", + [[ + INSERT INTO t1(g) VALUES(CAST(1 AS ANY)); + ]], { + 1, "Type mismatch: can not convert any(1) to unsigned" + }) + +test:do_catchsql_test( + "metatypes-2.8", + [[ + INSERT INTO t1(t) VALUES(CAST(1 AS ANY)); + ]], { + 1, "Type mismatch: can not convert any(1) to string" + }) + +test:do_catchsql_test( + "metatypes-2.9", + [[ + INSERT INTO t1(n) VALUES(CAST(1 AS ANY)); + ]], { + 1, "Type mismatch: can not convert any(1) to number" + }) + +test:do_catchsql_test( + "metatypes-2.10", + [[ + INSERT INTO t1(f) VALUES(CAST(1 AS ANY)); + ]], { + 1, "Type mismatch: can not convert any(1) to double" + }) + +test:do_catchsql_test( + "metatypes-2.11", + [[ + INSERT INTO t1(i) VALUES(CAST(1 AS ANY)); + ]], { + 1, "Type mismatch: can not convert any(1) to integer" + }) + +test:do_catchsql_test( + "metatypes-2.12", + [[ + INSERT INTO t1(b) VALUES(CAST(1 AS ANY)); + ]], { + 1, "Type mismatch: can not convert any(1) to boolean" + }) + +test:do_catchsql_test( + "metatypes-2.13", + [[ + INSERT INTO t1(v) VALUES(CAST(1 AS ANY)); + ]], { + 1, "Type mismatch: can not convert any(1) to varbinary" + }) + +test:do_catchsql_test( + "metatypes-2.14", + [[ + INSERT INTO t1(s) VALUES(CAST(1 AS ANY)); + ]], { + 1, "Type mismatch: can not convert any(1) to scalar" + }) + +test:do_catchsql_test( + "metatypes-2.15", + [[ + INSERT INTO t1(d) VALUES(CAST(1 AS ANY)); + ]], { + 1, "Type mismatch: can not convert any(1) to decimal" + }) + +test:do_catchsql_test( + "metatypes-2.16", + [[ + INSERT INTO t1(u) VALUES(CAST(1 AS ANY)); + ]], { + 1, "Type mismatch: can not convert any(1) to uuid" + }) + +-- Check that arithmetic operations are prohibited for NUMBER, SCALAR and ANY. test:do_catchsql_test( "metatypes-3.1", [[ @@ -107,7 +228,15 @@ test:do_catchsql_test( 1, "Type mismatch: can not convert scalar(1) to integer, decimal or double" }) --- Check that bitwise operations are prohibited for NUMBER and SCALAR values. +test:do_catchsql_test( + "metatypes-3.3", + [[ + SELECT CAST(1 AS ANY) - 1; + ]], { + 1, "Type mismatch: can not convert any(1) to integer, decimal or double" + }) + +-- Check that bitwise operations are prohibited for NUMBER, SCALAR and ANY. test:do_catchsql_test( "metatypes-4.1", [[ @@ -124,7 +253,15 @@ test:do_catchsql_test( 1, "Type mismatch: can not convert scalar(1) to unsigned" }) --- Check that concatination is prohibited for SCALAR values. +test:do_catchsql_test( + "metatypes-4.3", + [[ + SELECT CAST(1 AS ANY) | 1; + ]], { + 1, "Type mismatch: can not convert any(1) to unsigned" + }) + +-- Check that concatination is prohibited for SCALAR and ANY. test:do_catchsql_test( "metatypes-5", [[ @@ -133,13 +270,21 @@ test:do_catchsql_test( 1, "Inconsistent types: expected string or varbinary got scalar('asd')" }) +test:do_catchsql_test( + "metatypes-5", + [[ + SELECT CAST('asd' AS ANY) || 'dsa'; + ]], { + 1, "Inconsistent types: expected string or varbinary got any('asd')" + }) + -- Check that SCALAR values can be compared to values of any other scalar type. test:do_execsql_test( "metatypes-6.1", [[ SELECT s > false FROM t; ]], { - true, true, true, true, true, true + true, true, true, true, true, true, "", "", "", "", "", "" }) test:do_execsql_test( @@ -147,7 +292,7 @@ test:do_execsql_test( [[ SELECT s = 1 FROM t; ]], { - true, false, false, false, false, false + true, false, false, false, false, false, "", "", "", "", "", "" }) test:do_execsql_test( @@ -155,7 +300,7 @@ test:do_execsql_test( [[ SELECT s != 1.5 FROM t; ]], { - true, true, true, true, true, true + true, true, true, true, true, true, "", "", "", "", "", "" }) test:do_execsql_test( @@ -163,7 +308,7 @@ test:do_execsql_test( [[ SELECT s <= 'abc' FROM t; ]], { - true, true, true, true, false, false + true, true, true, true, false, false, "", "", "", "", "", "" }) test:do_execsql_test( @@ -171,7 +316,7 @@ test:do_execsql_test( [[ SELECT s < x'10' FROM t; ]], { - true, true, true, true, false, false + true, true, true, true, false, false, "", "", "", "", "", "" }) test:do_execsql_test( @@ -179,9 +324,386 @@ test:do_execsql_test( [[ SELECT s > CAST('11111111-1111-1111-1111-111111111110' AS UUID) FROM t; ]], { - false, false, false, false, false, true + false, false, false, false, false, true, "", "", "", "", "", "" + }) + +-- Check that ANY values cannot be compared to values of any other scalar type. +test:do_catchsql_test( + "metatypes-7.1", + [[ + SELECT a > false FROM t; + ]], { + 1, "Type mismatch: can not convert any(1) to comparable type" + }) + +test:do_catchsql_test( + "metatypes-7.2", + [[ + SELECT a = 1 FROM t; + ]], { + 1, "Type mismatch: can not convert any(1) to comparable type" + }) + +test:do_catchsql_test( + "metatypes-7.3", + [[ + SELECT a != 1.5 FROM t; + ]], { + 1, "Type mismatch: can not convert any(1) to comparable type" + }) + +test:do_catchsql_test( + "metatypes-7.4", + [[ + SELECT a <= 'abc' FROM t; + ]], { + 1, "Type mismatch: can not convert any(1) to comparable type" + }) + +test:do_catchsql_test( + "metatypes-7.5", + [[ + SELECT a < x'10' FROM t; + ]], { + 1, "Type mismatch: can not convert any(1) to comparable type" + }) + +test:do_catchsql_test( + "metatypes-7.6", + [[ + SELECT a > CAST('11111111-1111-1111-1111-111111111110' AS UUID) FROM t; + ]], { + 1, "Type mismatch: can not convert any(1) to comparable type" + }) + +test:do_catchsql_test( + "metatypes-7.7", + [[ + SELECT a >= CAST(1 AS DECIMAL) FROM t; + ]], { + 1, "Type mismatch: can not convert any(1) to comparable type" + }) + +test:do_catchsql_test( + "metatypes-7.8", + [[ + SELECT a = CAST(1 AS NUMBER) FROM t; + ]], { + 1, "Type mismatch: can not convert any(1) to comparable type" }) +test:do_catchsql_test( + "metatypes-7.9", + [[ + SELECT a != CAST(1 AS SCALAR) FROM t; + ]], { + 1, "Type mismatch: can not convert any(1) to comparable type" + }) + +-- Make sure the SQL built-in functions work correctly with ANY. +test:do_catchsql_test( + "metatypes-8.1", + [[ + SELECT ABS(a) FROM t; + ]], { + 1, "Type mismatch: can not convert any(1) to integer" + }) + +test:do_catchsql_test( + "metatypes-8.2", + [[ + SELECT AVG(a) FROM t; + ]], { + 1, "Type mismatch: can not convert any(1) to integer" + }) + +test:do_catchsql_test( + "metatypes-8.3", + [[ + SELECT CHAR(a) FROM t; + ]], { + 1, "Type mismatch: can not convert any(1) to integer" + }) + +test:do_catchsql_test( + "metatypes-8.4", + [[ + SELECT CHARACTER_LENGTH(a) FROM t; + ]], { + 1, "Type mismatch: can not convert any(1) to string" + }) + +test:do_catchsql_test( + "metatypes-8.5", + [[ + SELECT CHAR_LENGTH(a) FROM t; + ]], { + 1, "Type mismatch: can not convert any(1) to string" + }) + +test:do_execsql_test( + "metatypes-8.6", + [[ + SELECT COALESCE(s, a) FROM t; + ]], { + 1, 2, "3", true, "5", uuid, "3", true, "5", uuid, 3, dec + }) + +test:do_execsql_test( + "metatypes-8.7", + [[ + SELECT COUNT(a) FROM t; + ]], { + 8 + }) + +test:do_catchsql_test( + "metatypes-8.8", + [[ + SELECT GREATEST(s, a) FROM t; + ]], { + 1, "Type mismatch: can not convert any(1) to scalar" + }) + +test:do_catchsql_test( + "metatypes-8.9", + [[ + SELECT GROUP_CONCAT(a) FROM t; + ]], { + 1, "Type mismatch: can not convert any(1) to string" + }) + +test:do_catchsql_test( + "metatypes-8.10", + [[ + SELECT HEX(a) FROM t; + ]], { + 1, "Type mismatch: can not convert any(1) to varbinary" + }) + +test:do_execsql_test( + "metatypes-8.11", + [[ + SELECT IFNULL(s, a) FROM t; + ]], { + 1, 2, "3", true, "5", uuid, "3", true, "5", uuid, 3, dec + }) + +test:do_catchsql_test( + "metatypes-8.12", + [[ + SELECT LEAST(s, a) FROM t; + ]], { + 1, "Type mismatch: can not convert any(1) to scalar" + }) + +test:do_catchsql_test( + "metatypes-8.13", + [[ + SELECT LENGTH(a) FROM t; + ]], { + 1, "Type mismatch: can not convert any(1) to string" + }) + +test:do_catchsql_test( + "metatypes-8.14", + [[ + SELECT s LIKE a FROM t; + ]], { + 1, [[Failed to execute SQL statement: wrong arguments for function ]].. + [[LIKE()]] + }) + +test:do_execsql_test( + "metatypes-8.15", + [[ + SELECT LIKELIHOOD(a, 0.5e0) FROM t; + ]], { + 1, 2, "", "", "", "", "3", true, "5", uuid, 3, dec + }) + +test:do_execsql_test( + "metatypes-8.16", + [[ + SELECT LIKELY(a) FROM t; + ]], { + 1, 2, "", "", "", "", "3", true, "5", uuid, 3, dec + }) + +test:do_catchsql_test( + "metatypes-8.17", + [[ + SELECT LOWER(a) FROM t; + ]], { + 1, "Type mismatch: can not convert any(1) to string" + }) + +test:do_catchsql_test( + "metatypes-8.18", + [[ + SELECT MAX(a) FROM t; + ]], { + 1, "Type mismatch: can not convert any(1) to scalar" + }) + +test:do_catchsql_test( + "metatypes-8.19", + [[ + SELECT MIN(a) FROM t; + ]], { + 1, "Type mismatch: can not convert any(1) to scalar" + }) + +test:do_catchsql_test( + "metatypes-8.20", + [[ + SELECT NULLIF(s, a) FROM t; + ]], { + 1, "Type mismatch: can not convert any(1) to scalar" + }) + +test:do_catchsql_test( + "metatypes-8.21", + [[ + SELECT POSITION(s, a) FROM t; + ]], { + 1, [[Failed to execute SQL statement: wrong arguments for function ]].. + [[POSITION()]] + }) + +test:do_execsql_test( + "metatypes-8.22", + [[ + SELECT PRINTF(a) FROM t; + ]], { + "1", "2.0", "", "", "", "", "3", "TRUE", "5", + "11111111-1111-1111-1111-111111111111", "3.0", "1.5" + }) + +test:do_execsql_test( + "metatypes-8.23", + [[ + SELECT QUOTE(a) FROM t; + ]], { + 1, 2, "NULL", "NULL", "NULL", "NULL", "'3'", "TRUE", "X'35'", + "11111111-1111-1111-1111-111111111111", 3, dec + }) + +test:do_catchsql_test( + "metatypes-8.24", + [[ + SELECT RANDOMBLOB(a) FROM t; + ]], { + 1, "Type mismatch: can not convert any(1) to integer" + }) + +test:do_catchsql_test( + "metatypes-8.25", + [[ + SELECT REPLACE(s, n, a) FROM t; + ]], { + 1, [[Failed to execute SQL statement: wrong arguments for function ]].. + [[REPLACE()]] + }) + +test:do_catchsql_test( + "metatypes-8.26", + [[ + SELECT ROUND(a) FROM t; + ]], { + 1, "Type mismatch: can not convert any(1) to double" + }) + +test:do_catchsql_test( + "metatypes-8.27", + [[ + SELECT SOUNDEX(a) FROM t; + ]], { + 1, "Type mismatch: can not convert any(1) to string" + }) + +test:do_catchsql_test( + "metatypes-8.28", + [[ + SELECT SUBSTR(a, 1, 1) FROM t; + ]], { + 1, "Type mismatch: can not convert any(1) to string" + }) + +test:do_catchsql_test( + "metatypes-8.29", + [[ + SELECT SUM(a) FROM t; + ]], { + 1, "Type mismatch: can not convert any(1) to integer" + }) + +test:do_catchsql_test( + "metatypes-8.30", + [[ + SELECT TOTAL(a) FROM t; + ]], { + 1, "Type mismatch: can not convert any(1) to integer" + }) + +test:do_catchsql_test( + "metatypes-8.31", + [[ + SELECT TRIM(a) FROM t; + ]], { + 1, "Type mismatch: can not convert any(1) to string" + }) + +test:do_execsql_test( + "metatypes-8.32", + [[ + SELECT TYPEOF(a) FROM t; + ]], { + "any", "any", "NULL", "NULL", "NULL", "NULL", "any", "any", "any", + "any", "any", "any" + }) + +test:do_catchsql_test( + "metatypes-8.33", + [[ + SELECT UNICODE(a) FROM t; + ]], { + 1, "Type mismatch: can not convert any(1) to string" + }) + +test:do_execsql_test( + "metatypes-8.34", + [[ + SELECT UNLIKELY(a) FROM t; + ]], { + 1, 2, "", "", "", "", "3", true, "5", uuid, 3, dec + }) + +test:do_catchsql_test( + "metatypes-8.35", + [[ + SELECT UPPER(a) FROM t; + ]], { + 1, "Type mismatch: can not convert any(1) to string" + }) + +test:do_catchsql_test( + "metatypes-8.36", + [[ + SELECT UUID(a) FROM t; + ]], { + 1, "Type mismatch: can not convert any(1) to integer" + }) + +test:do_catchsql_test( + "metatypes-8.37", + [[ + SELECT ZEROBLOB(a) FROM t; + ]], { + 1, "Type mismatch: can not convert any(1) to integer" + }) + + box.execute([[DROP TABLE t;]]) test:finish_test() -- 2.25.1