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 B2A802963C for ; Thu, 7 Mar 2019 08:14:12 -0500 (EST) 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 F6oFxrNW1GZY for ; Thu, 7 Mar 2019 08:14:12 -0500 (EST) Received: from smtpng1.m.smailru.net (smtpng1.m.smailru.net [94.100.181.251]) (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 DD6F629618 for ; Thu, 7 Mar 2019 08:14:11 -0500 (EST) From: Nikita Pettik Subject: [tarantool-patches] [PATCH 4/4] sql: replace BLOB as column type with SCALAR Date: Thu, 7 Mar 2019 16:14:04 +0300 Message-Id: <83426c95235d880a4d16ca84927138961caf3c55.1551964360.git.korablev@tarantool.org> In-Reply-To: References: MIME-Version: 1.0 In-Reply-To: References: Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: 8bit 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, Nikita Pettik BLOB column type is represented by SCALAR field type in terms of NoSQL. We attempted at emulating BLOB behaviour, but such efforts turn out to be not decent enough. For this reason, we've decided to abandon these attempts and fairly replace it with SCALAR column type. SCALAR column type acts in the same way as it does in NoSQL: it is aggregator-type for INTEGER, NUMBER and STRING types. So, column declared with this type can contain values of these three (available in SQL) types. It is worth mentioning that CAST operator in this case does nothing. Still, we consider BLOB values as entries encoded in msgpack with MP_BIN format. To make this happen, values to be operated should be represented in BLOB form x'...' (e.g. x'000000'). What is more, there are two built-in functions returning BLOBs: randomblob() and zeroblob(). On the other hand, columns with STRING NoSQL type don't accept BLOB values. Closes #4019 Closes #4023 @TarantoolBot document Title: SQL types changes There are couple of recently introduced changes connected with SQL types. Firstly, we've removed support of DATE/TIME types from parser due to confusing behaviour of these types: they were mapped to NUMBER NoSQL type and have nothing in common with generally accepted DATE/TIME types (like in other DBs). In addition, all built-in functions related to these types (julianday(), date(), time(), datetime(), current_time(), current_date() etc) are disabled until we reimplement TIME-like types as a native NoSQL ones (see #3694 issue). Secondly, we've removed CHAR type (i.e. alias to VARCHAR and TEXT). The reason is that according to ANSI SQL CHAR(len) must accept only strings featuring length exactly equal to given in type definition. Obviously, now we don't provide such checks. Types VARCHAR and TEXT are still legal. For the same reason, we've removed NUMERIC and DECIMAL types, which were aliases to NUMBER NoSQL type. REAL, FLOAT and DOUBLE are still exist as aliases. Finally, we've renamed BLOB column type to SCALAR. We've decided that all our attempts to emulate BLOB behaviour using SCALAR NoSQL type don't seem decent enough, i.e. without native NoSQL type BLOB there always will be inconsistency, especially taking into account possible NoSQL-SQL interactions. In SQL SCALAR type works exactly in the same way as in NoSQL: it can store values of INTEGER, FLOAT and TEXT SQL types at the same time. Also, with this change behaviour of CAST operator has been slightly corrected: now cast to SCALAR doesn't affect type of value at all. Couple of examples: CREATE TABLE t1 (a SCALAR PRIMARY KEY); INSERT INTO t1 VALUES ('1'); SELECT * FROM t1 WHERE a = 1; -- [] Result is empty set since column "a" contains string literal value '1', not integer value 1. CAST(123 AS SCALAR); -- Returns 123 (integer) CAST('abc' AS SCALAR); -- Returns 'abc' (string) Note that in NoSQL values of BLOB type defined as ones decoded in msgpack with MP_BIN format. In SQL there are still a few ways to force this format: declaring literal in "BLOB" format (x'...') or using one of two built-in functions (randomblob() and zeroblob()). TEXT and VARCHAR SQL types don't accept BLOB values: CREATE TABLE t (a TEXT PRIMARAY KEY); INSERT INTO t VALUES (randomblob(5)); --- - error: 'Tuple field 1 type does not match one required: expected string' ... BLOB itself is going to be reimplemented in scope of #3650. --- extra/mkkeywordhash.c | 2 +- src/box/sql/parse.y | 2 +- src/box/sql/vdbe.c | 2 -- src/box/sql/vdbemem.c | 14 +------------ test/sql-tap/analyze9.test.lua | 2 +- test/sql-tap/blob.test.lua | 4 ++-- test/sql-tap/boundary1.test.lua | 2 +- test/sql-tap/boundary3.test.lua | 2 +- test/sql-tap/cast.test.lua | 30 ++++++++++++++-------------- test/sql-tap/default.test.lua | 2 +- test/sql-tap/delete4.test.lua | 2 +- test/sql-tap/distinct.test.lua | 11 +++------- test/sql-tap/e_expr.test.lua | 14 ++++++------- test/sql-tap/in3.test.lua | 9 ++++----- test/sql-tap/index4.test.lua | 4 ++-- test/sql-tap/substr.test.lua | 2 +- test/sql-tap/table.test.lua | 16 +++++++-------- test/sql-tap/tkt1443.test.lua | 2 +- test/sql-tap/tkt1449.test.lua | 14 ++++++------- test/sql-tap/types2.test.lua | 8 ++++---- test/sql-tap/whereB.test.lua | 18 ++++++++--------- test/sql/gh-3888-values-blob-assert.result | 8 ++++---- test/sql/gh-3888-values-blob-assert.test.lua | 4 ++-- test/sql/transition.result | 2 +- test/sql/transition.test.lua | 2 +- test/sql/types.result | 10 +++------- test/sql/types.test.lua | 7 +++---- 27 files changed, 84 insertions(+), 111 deletions(-) diff --git a/extra/mkkeywordhash.c b/extra/mkkeywordhash.c index 298c19c47..0a874c8e6 100644 --- a/extra/mkkeywordhash.c +++ b/extra/mkkeywordhash.c @@ -109,7 +109,6 @@ static Keyword aKeywordTable[] = { { "BEFORE", "TK_BEFORE", TRIGGER, false }, { "BEGIN", "TK_BEGIN", TRIGGER, true }, { "BETWEEN", "TK_BETWEEN", ALWAYS, true }, - { "BLOB", "TK_BLOB_KW", RESERVED, true }, { "BY", "TK_BY", ALWAYS, true }, { "CASCADE", "TK_CASCADE", FKEY, false }, { "CASE", "TK_CASE", ALWAYS, true }, @@ -193,6 +192,7 @@ static Keyword aKeywordTable[] = { { "ROLLBACK", "TK_ROLLBACK", ALWAYS, true }, { "ROW", "TK_ROW", TRIGGER, true }, { "SAVEPOINT", "TK_SAVEPOINT", ALWAYS, true }, + { "SCALAR", "TK_SCALAR", ALWAYS, true }, { "SELECT", "TK_SELECT", ALWAYS, true }, { "SET", "TK_SET", ALWAYS, true }, { "SIMPLE", "TK_SIMPLE", ALWAYS, true }, diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y index 8b45eceaf..996f55d37 100644 --- a/src/box/sql/parse.y +++ b/src/box/sql/parse.y @@ -1477,7 +1477,7 @@ wqlist(A) ::= wqlist(A) COMMA nm(X) eidlist_opt(Y) AS LP select(Z) RP. { ////////////////////////////// TYPE DECLARATION /////////////////////////////// %type typedef {struct type_def} typedef(A) ::= TEXT . { A.type = FIELD_TYPE_STRING; } -typedef(A) ::= BLOB_KW . { A.type = FIELD_TYPE_SCALAR; } +typedef(A) ::= SCALAR . { A.type = FIELD_TYPE_SCALAR; } /** * Time-like types are temporary disabled, until they are * implemented as a native Tarantool types (gh-3694). diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c index d2d8e9182..c1da9a4aa 100644 --- a/src/box/sql/vdbe.c +++ b/src/box/sql/vdbe.c @@ -364,8 +364,6 @@ mem_apply_type(struct Mem *record, enum field_type type) record->flags &= ~(MEM_Real | MEM_Int); return 0; case FIELD_TYPE_SCALAR: - if (record->flags & (MEM_Str | MEM_Blob)) - record->flags |= MEM_Blob; return 0; default: return -1; diff --git a/src/box/sql/vdbemem.c b/src/box/sql/vdbemem.c index f67c32e1d..074ff8c96 100644 --- a/src/box/sql/vdbemem.c +++ b/src/box/sql/vdbemem.c @@ -617,19 +617,7 @@ sqlVdbeMemCast(Mem * pMem, enum field_type type) } switch (type) { case FIELD_TYPE_SCALAR: - if (pMem->flags & MEM_Blob) - return SQL_OK; - if (pMem->flags & MEM_Str) { - MemSetTypeFlag(pMem, MEM_Blob); - return SQL_OK; - } - if (pMem->flags & MEM_Int || pMem->flags & MEM_Real) { - if (sqlVdbeMemStringify(pMem, 1) != 0) - return -1; - MemSetTypeFlag(pMem, MEM_Blob); - return 0; - } - return SQL_ERROR; + return 0; case FIELD_TYPE_INTEGER: if ((pMem->flags & MEM_Blob) != 0) { if (sql_atoi64(pMem->z, (int64_t *) &pMem->u.i, diff --git a/test/sql-tap/analyze9.test.lua b/test/sql-tap/analyze9.test.lua index b7033234f..3969c45af 100755 --- a/test/sql-tap/analyze9.test.lua +++ b/test/sql-tap/analyze9.test.lua @@ -391,7 +391,7 @@ test:do_execsql_test( INSERT INTO t1 VALUES(null, 4, 4); INSERT INTO t1 VALUES(null, 5, 5); ANALYZE; - CREATE TABLE x1(tbl TEXT, idx TEXT , neq TEXT, nlt TEXT, ndlt TEXT, sample BLOB, PRIMARY KEY(tbl, idx, sample)); + CREATE TABLE x1(tbl TEXT, idx TEXT , neq TEXT, nlt TEXT, ndlt TEXT, sample SCALAR, PRIMARY KEY(tbl, idx, sample)); INSERT INTO x1 SELECT * FROM "_sql_stat4"; DELETE FROM "_sql_stat4"; INSERT INTO "_sql_stat4" SELECT * FROM x1; diff --git a/test/sql-tap/blob.test.lua b/test/sql-tap/blob.test.lua index c7b328a9f..376d5b183 100755 --- a/test/sql-tap/blob.test.lua +++ b/test/sql-tap/blob.test.lua @@ -170,7 +170,7 @@ test:do_test( "blob-2.0", function() test:execsql [[ - CREATE TABLE t1(a BLOB primary key, b BLOB); + CREATE TABLE t1(a SCALAR primary key, b SCALAR); INSERT INTO t1 VALUES(X'123456', x'7890ab'); INSERT INTO t1 VALUES(X'CDEF12', x'345678'); ]] @@ -186,7 +186,7 @@ test:do_test( -- }) --- An index on a blob column +-- An index on a SCALAR column test:do_test( "blob-2.1", function() diff --git a/test/sql-tap/boundary1.test.lua b/test/sql-tap/boundary1.test.lua index e266cf750..ffd667c15 100755 --- a/test/sql-tap/boundary1.test.lua +++ b/test/sql-tap/boundary1.test.lua @@ -26,7 +26,7 @@ test:do_test( "boundary1-1.1", function() return test:execsql([[ - CREATE TABLE t1(rowid INT primary key, a INT ,x BLOB); + CREATE TABLE t1(rowid INT primary key, a INT ,x SCALAR); INSERT INTO t1(rowid,a,x) VALUES(-8388609,1,'ffffffffff7fffff'); INSERT INTO t1(rowid,a,x) VALUES(-36028797018963969,2,'ff7fffffffffffff'); INSERT INTO t1(rowid,a,x) VALUES(9223372036854775807,3,'7fffffffffffffff'); diff --git a/test/sql-tap/boundary3.test.lua b/test/sql-tap/boundary3.test.lua index 56130935c..fef15c93e 100755 --- a/test/sql-tap/boundary3.test.lua +++ b/test/sql-tap/boundary3.test.lua @@ -26,7 +26,7 @@ test:do_test( "boundary3-1.1", function() return test:execsql([[ - CREATE TABLE t1(rowid INT primary key, a FLOAT ,x BLOB); + CREATE TABLE t1(rowid INT primary key, a FLOAT ,x SCALAR); INSERT INTO t1(rowid,a,x) VALUES(-8388609,1,'ffffffffff7fffff'); INSERT INTO t1(rowid,a,x) VALUES(-36028797018963969,2,'ff7fffffffffffff'); INSERT INTO t1(rowid,a,x) VALUES(9223372036854775806,3,'7fffffffffffffff'); diff --git a/test/sql-tap/cast.test.lua b/test/sql-tap/cast.test.lua index 05fa872b5..e848991ba 100755 --- a/test/sql-tap/cast.test.lua +++ b/test/sql-tap/cast.test.lua @@ -22,7 +22,7 @@ test:plan(82) -- Only run these tests if the build includes the CAST operator --- Tests for the CAST( AS blob), CAST( AS text) and CAST( AS numeric) built-ins +-- Tests for the CAST( AS SCALAR), CAST( AS text) and CAST( AS numeric) built-ins -- test:do_execsql_test( "cast-1.1", @@ -77,7 +77,7 @@ test:do_catchsql_test( test:do_execsql_test( "cast-1.7", [[ - SELECT CAST(x'616263' AS blob) + SELECT CAST(x'616263' AS SCALAR) ]], { -- "abc" @@ -87,7 +87,7 @@ test:do_execsql_test( test:do_execsql_test( "cast-1.8", [[ - SELECT typeof(CAST(x'616263' AS blob)) + SELECT typeof(CAST(x'616263' AS SCALAR)) ]], { -- "blob" @@ -167,7 +167,7 @@ test:do_execsql_test( test:do_execsql_test( "cast-1.17", [[ - SELECT CAST(NULL AS blob) + SELECT CAST(NULL AS SCALAR) ]], { -- "" @@ -177,7 +177,7 @@ test:do_execsql_test( test:do_execsql_test( "cast-1.18", [[ - SELECT typeof(CAST(NULL AS blob)) + SELECT typeof(CAST(NULL AS SCALAR)) ]], { -- "null" @@ -267,20 +267,20 @@ test:do_execsql_test( test:do_execsql_test( "cast-1.27", [[ - SELECT CAST(123 AS blob) + SELECT CAST(123 AS SCALAR) ]], { -- - "123" + 123 -- }) test:do_execsql_test( "cast-1.28", [[ - SELECT typeof(CAST(123 AS blob)) + SELECT typeof(CAST(123 AS SCALAR)) ]], { -- - "blob" + "integer" -- }) @@ -367,20 +367,20 @@ test:do_execsql_test( test:do_execsql_test( "cast-1.37", [[ - SELECT CAST(123.456 AS blob) + SELECT CAST(123.456 AS SCALAR) ]], { -- - "123.456" + 123.456 -- }) test:do_execsql_test( "cast-1.38", [[ - SELECT typeof(CAST(123.456 AS blob)) + SELECT typeof(CAST(123.456 AS SCALAR)) ]], { -- - "blob" + "real" -- }) @@ -457,10 +457,10 @@ test:do_catchsql_test( test:do_execsql_test( "cast-1.48", [[ - SELECT typeof(CAST('123abc' AS blob)) + SELECT typeof(CAST('123abc' AS SCALAR)) ]], { -- - "blob" + "text" -- }) diff --git a/test/sql-tap/default.test.lua b/test/sql-tap/default.test.lua index 349bcdf6c..267ce884d 100755 --- a/test/sql-tap/default.test.lua +++ b/test/sql-tap/default.test.lua @@ -28,7 +28,7 @@ test:do_execsql_test( CREATE TABLE t1( rowid INTEGER PRIMARY KEY AUTOINCREMENT, a INTEGER, - b BLOB DEFAULT x'6869' + b SCALAR DEFAULT x'6869' ); INSERT INTO t1(a) VALUES(1); SELECT a, b from t1; diff --git a/test/sql-tap/delete4.test.lua b/test/sql-tap/delete4.test.lua index 55c99a163..7c0eec15e 100755 --- a/test/sql-tap/delete4.test.lua +++ b/test/sql-tap/delete4.test.lua @@ -56,7 +56,7 @@ test:do_execsql_test( 2.1, [[ DROP TABLE IF EXISTS t1; - CREATE TABLE t1(x INTEGER PRIMARY KEY, y INT , z BLOB); + CREATE TABLE t1(x INTEGER PRIMARY KEY, y INT , z SCALAR); INSERT INTO t1 VALUES(1, 0, randomblob(200)); INSERT INTO t1 VALUES(2, 1, randomblob(200)); INSERT INTO t1 VALUES(3, 0, randomblob(200)); diff --git a/test/sql-tap/distinct.test.lua b/test/sql-tap/distinct.test.lua index e2041ba3a..c66375184 100755 --- a/test/sql-tap/distinct.test.lua +++ b/test/sql-tap/distinct.test.lua @@ -222,7 +222,7 @@ if (1 > 0) then --------------------------------------------------------------------------- -- Ticket [fccbde530a6583bf2748400919f1603d5425995c] (2014-01-08) -- The logic that computes DISTINCT sometimes thinks that a zeroblob() - -- and a blob of all zeros are different when they should be the same. + -- and a blob of all zeros are different when they should be the same. -- test:do_execsql_test( 4.1, @@ -236,13 +236,8 @@ if (1 > 0) then INSERT INTO t1 VALUES(4,2); INSERT INTO t1 VALUES(5,3); INSERT INTO t1 VALUES(6,1); - CREATE TABLE t2(x BLOB primary key); - INSERT INTO t2 - SELECT DISTINCT - CASE a WHEN 1 THEN x'0000000000' - WHEN 2 THEN zeroblob(5) - ELSE 'xyzzy' END - FROM t1; + CREATE TABLE t2(x SCALAR primary key); + INSERT INTO t2 SELECT DISTINCT CASE a WHEN 1 THEN x'0000000000' WHEN 2 THEN zeroblob(5) ELSE 'xyzzy' END FROM t1; SELECT quote(x) FROM t2 ORDER BY 1; ]], { -- <4.1> diff --git a/test/sql-tap/e_expr.test.lua b/test/sql-tap/e_expr.test.lua index 24f522978..7b80651a8 100755 --- a/test/sql-tap/e_expr.test.lua +++ b/test/sql-tap/e_expr.test.lua @@ -3034,15 +3034,13 @@ test:do_execsql_test( -- do_expr_test("e_expr-27.2.1", " CAST(NULL AS integer) ", "null", "") do_expr_test("e_expr-27.2.2", " CAST(NULL AS text) ", "null", "") -do_expr_test("e_expr-27.2.3", " CAST(NULL AS blob) ", "null", "") +do_expr_test("e_expr-27.2.3", " CAST(NULL AS SCALAR) ", "null", "") do_expr_test("e_expr-27.2.4", " CAST(NULL AS numeric) ", "null", "") --- EVIDENCE-OF: R-22956-37754 Casting to a BLOB consists of first casting --- the value to TEXT in the encoding of the database connection, then --- interpreting the resulting byte sequence as a BLOB instead of as TEXT. +-- Casting to a SCALAR doesn't affect original value. -- -do_qexpr_test("e_expr-27.4.1", " CAST('ghi' AS blob) ", "X'676869'") -do_qexpr_test("e_expr-27.4.2", " CAST(456 AS blob) ", "X'343536'") -do_qexpr_test("e_expr-27.4.3", " CAST(1.78 AS blob) ", "X'312E3738'") +do_qexpr_test("e_expr-27.4.1", " CAST('ghi' AS SCALAR) ", "X'676869'") +do_qexpr_test("e_expr-27.4.2", " CAST(456 AS SCALAR) ", "X'343536'") +do_qexpr_test("e_expr-27.4.3", " CAST(1.78 AS SCALAR) ", "X'312E3738'") -- EVIDENCE-OF: R-22235-47006 Casting an INTEGER or REAL value into TEXT -- renders the value as if via sql_snprintf() except that the @@ -3170,7 +3168,7 @@ do_expr_test("e_expr-32.2.4", [[ CAST(9223372036854775807 AS NUMERIC) ]], "real", 9223372036854775807) -- EVIDENCE-OF: R-64550-29191 Note that the result from casting any --- non-BLOB value into a BLOB and the result from casting any BLOB value +-- non-BLOB value into a SCALAR and the result from casting any SCALAR value -- into a non-BLOB value may be different depending on whether the -- database encoding is UTF-8 -- diff --git a/test/sql-tap/in3.test.lua b/test/sql-tap/in3.test.lua index 18f57fa47..988a9b2a7 100755 --- a/test/sql-tap/in3.test.lua +++ b/test/sql-tap/in3.test.lua @@ -271,12 +271,12 @@ test:do_test( DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t1; - CREATE TABLE t1(id INT primary key, a BLOB, b FLOAT ,c TEXT); + CREATE TABLE t1(id INT primary key, a SCALAR, b FLOAT ,c TEXT); CREATE UNIQUE INDEX t1_i1 ON t1(a); /* no affinity */ CREATE UNIQUE INDEX t1_i2 ON t1(b); /* numeric affinity */ CREATE UNIQUE INDEX t1_i3 ON t1(c); /* text affinity */ - CREATE TABLE t2(id INT primary key, x BLOB, y FLOAT, z TEXT); + CREATE TABLE t2(id INT primary key, x SCALAR, y FLOAT, z TEXT); CREATE UNIQUE INDEX t2_i1 ON t2(x); /* no affinity */ CREATE UNIQUE INDEX t2_i2 ON t2(y); /* numeric affinity */ CREATE UNIQUE INDEX t2_i3 ON t2(z); /* text affinity */ @@ -318,9 +318,8 @@ test:do_test( test:do_test( "in3-3.4", function() - -- BLOB is compatible with TEXT, however index can't - -- be used since under the hood BLOB is SCALAR (which - -- can contain not only STRING values) and TEXT is STRING. + -- SCALAR is compatible with TEXT, however index can't + -- be used since SCALAR can accept not only string values. return exec_neph(" SELECT x IN (SELECT c FROM t1) FROM t2 ") end, { -- diff --git a/test/sql-tap/index4.test.lua b/test/sql-tap/index4.test.lua index 3745dab90..d8c6babad 100755 --- a/test/sql-tap/index4.test.lua +++ b/test/sql-tap/index4.test.lua @@ -22,7 +22,7 @@ testprefix = "index4" test:do_execsql_test( 1.1, [[ - CREATE TABLE t1(x BLOB primary key); + CREATE TABLE t1(x SCALAR primary key); START TRANSACTION; INSERT INTO t1 VALUES(randomblob(102)); INSERT INTO t1 SELECT randomblob(102) FROM t1; -- 2 @@ -78,7 +78,7 @@ test:do_execsql_test( 1.6, [[ DROP TABLE t1; - CREATE TABLE t1(x BLOB primary key); + CREATE TABLE t1(x SCALAR primary key); START TRANSACTION; INSERT INTO t1 VALUES('a'); INSERT INTO t1 VALUES('b'); diff --git a/test/sql-tap/substr.test.lua b/test/sql-tap/substr.test.lua index fbb97ff1c..a9e656e6d 100755 --- a/test/sql-tap/substr.test.lua +++ b/test/sql-tap/substr.test.lua @@ -25,7 +25,7 @@ test:plan(93) -- test:execsql [[ CREATE TABLE t1(id integer primary key --autoincrement - , t text, b blob) + , t text, b SCALAR) ]] local function substr_test(id, string, i1, i2, result) diff --git a/test/sql-tap/table.test.lua b/test/sql-tap/table.test.lua index 1583890da..aa3eea813 100755 --- a/test/sql-tap/table.test.lua +++ b/test/sql-tap/table.test.lua @@ -495,8 +495,8 @@ test:do_catchsql_test( key int, "14_vac" int, fuzzy_dog_12 varchar(10), - beginn blob, - endd blob + beginn SCALAR, + endd SCALAR ) ]=], { -- @@ -551,8 +551,8 @@ test:do_execsql2_test( key int, "14_vac" int, fuzzy_dog_12 varchar(10), - beginn blob, - endd blob + beginn SCALAR, + endd SCALAR ); INSERT INTO t2 SELECT * from weird; SELECT * FROM t2; @@ -704,7 +704,7 @@ test:do_catchsql_test( test:do_catchsql_test( "table-9.2", [[ - CREATE TABLE t6(a varchar(100) primary key, b blob, a integer); + CREATE TABLE t6(a varchar(100) primary key, b SCALAR, a integer); ]], { -- 1, "Space field 'A' is duplicate" @@ -895,8 +895,8 @@ test:do_execsql_test( b FLOAT, c VARCHAR(8), d VARCHAR(9), - e blob, - f BLOB, + e SCALAR, + f SCALAR, g Text, h text ); @@ -1142,7 +1142,7 @@ test:do_test( -- DROP TABLE IF EXISTS t1; -- BEGIN; -- CREATE TABLE t1 AS SELECT zeroblob(2e20); --- } {1 {string or blob too big}} +-- } {1 {string or SCALAR too big}} -- do_execsql_test table-18.2 { -- COMMIT; -- PRAGMA integrity_check; diff --git a/test/sql-tap/tkt1443.test.lua b/test/sql-tap/tkt1443.test.lua index e42ef9900..35f9f3226 100755 --- a/test/sql-tap/tkt1443.test.lua +++ b/test/sql-tap/tkt1443.test.lua @@ -85,7 +85,7 @@ test:do_test( CREATE TABLE Users ( userId INTEGER PRIMARY KEY, "user" TEXT UNIQUE, - salt BLOB, + salt SCALAR, password TEXT ); INSERT INTO Users VALUES(1, 'test', 'Šæ%s', diff --git a/test/sql-tap/tkt1449.test.lua b/test/sql-tap/tkt1449.test.lua index 54c58452b..87403000f 100755 --- a/test/sql-tap/tkt1449.test.lua +++ b/test/sql-tap/tkt1449.test.lua @@ -37,28 +37,28 @@ test:do_execsql_test( CREATE TABLE ACLS(ISSUEID varchar(50) not null, OBJECTID varchar(50) not null, PARTICIPANTID varchar(50) not null, PERMISSIONBITS int not null, constraint PK_ACLS primary key (ISSUEID, OBJECTID, PARTICIPANTID)); CREATE TABLE ACTIONITEMSTATUSES(CLASSID int null, SEQNO int not null, LASTMODONNODEID varchar(50) not null, PREVMODONNODEID varchar(50) null, ISSUEID varchar(50) not null, OBJECTID varchar(50) not null, REVISIONNUM int not null, CONTAINERID varchar(50) not null, AUTHORID varchar(50) not null, CREATIONDATE varchar(25) null, LASTMODIFIEDDATE varchar(25) null, UPDATENUMBER int null, PREVREVISIONNUM int null, LASTCMD int null, LASTCMDACLVERSION int null, USERDEFINEDFIELD varchar(300) null, LASTMODIFIEDBYID varchar(50) null, FRIENDLYNAME varchar(100) not null, REVISION int not null, SHORTNAME varchar(30) not null, LONGNAME varchar(200) not null, ATTACHMENTHANDLING int not null, RESULT int not null, NOTIFYCREATOR varchar(1) null, NOTIFYASSIGNEE varchar(1) null, NOTIFYFYI varchar(1) null, NOTIFYCLOSURETEAM varchar(1) null, NOTIFYCOORDINATORS varchar(1) null, COMMENTREQUIRED varchar(1) not null, constraint PK_ACTIONITEMSTATUSES primary key (ISSUEID, OBJECTID)); CREATE TABLE ACTIONITEMTYPES(CLASSID int null, SEQNO int not null, LASTMODONNODEID varchar(50) not null, PREVMODONNODEID varchar(50) null, ISSUEID varchar(50) not null, OBJECTID varchar(50) not null, REVISIONNUM int not null, CONTAINERID varchar(50) not null, AUTHORID varchar(50) not null, CREATIONDATE varchar(25) null, LASTMODIFIEDDATE varchar(25) null, UPDATENUMBER int null, PREVREVISIONNUM int null, LASTCMD int null, LASTCMDACLVERSION int null, USERDEFINEDFIELD varchar(300) null, LASTMODIFIEDBYID varchar(50) null, REVISION int not null, LABEL varchar(200) not null, INSTRUCTIONS text not null, EMAILINSTRUCTIONS text null, ALLOWEDSTATUSES text not null, INITIALSTATUS varchar(100) not null, COMMENTREQUIRED varchar(1) not null, ATTACHMENTHANDLING int not null, constraint PK_ACTIONITEMTYPES primary key (ISSUEID, OBJECTID)); - CREATE TABLE ATTACHMENTS(TQUNID varchar(36) not null, OBJECTID varchar(50) null, ISSUEID varchar(50) null, DATASTREAM blob not null, CONTENTENCODING varchar(50) null, CONTENTCHARSET varchar(50) null, CONTENTTYPE varchar(100) null, CONTENTID varchar(100) null, CONTENTLOCATION varchar(100) null, CONTENTNAME varchar(100) not null, constraint PK_ATTACHMENTS primary key (TQUNID)); + CREATE TABLE ATTACHMENTS(TQUNID varchar(36) not null, OBJECTID varchar(50) null, ISSUEID varchar(50) null, DATASTREAM SCALAR not null, CONTENTENCODING varchar(50) null, CONTENTCHARSET varchar(50) null, CONTENTTYPE varchar(100) null, CONTENTID varchar(100) null, CONTENTLOCATION varchar(100) null, CONTENTNAME varchar(100) not null, constraint PK_ATTACHMENTS primary key (TQUNID)); CREATE TABLE COMPLIANCEPOLICIES(CLASSID int null, SEQNO int not null, LASTMODONNODEID varchar(50) not null, PREVMODONNODEID varchar(50) null, ISSUEID varchar(50) not null, OBJECTID varchar(50) not null, REVISIONNUM int not null, CONTAINERID varchar(50) not null, AUTHORID varchar(50) not null, CREATIONDATE varchar(25) null, LASTMODIFIEDDATE varchar(25) null, UPDATENUMBER int null, PREVREVISIONNUM int null, LASTCMD int null, LASTCMDACLVERSION int null, USERDEFINEDFIELD varchar(300) null, LASTMODIFIEDBYID varchar(50) null, BODY text null, constraint PK_COMPLIANCEPOLICIES primary key (ISSUEID, OBJECTID)); CREATE TABLE DBHISTORY(id INT primary key, "DATETIME" varchar(25) not null, OPERATION varchar(20) not null, KUBIVERSION varchar(100) not null, FROMVERSION int null, TOVERSION int null); CREATE TABLE DBINFO(id INT primary key, FINGERPRINT varchar(32) not null, VERSION int not null); CREATE TABLE DETACHEDATTACHMENTS (TQUNID varchar(36) not null, ISSUEID varchar(50) not null, OBJECTID varchar(50) not null, PATH varchar(300) not null, DETACHEDFILELASTMODTIMESTAMP varchar(25) null, CONTENTID varchar(100) not null, constraint PK_DETACHEDATTACHMENTS primary key (TQUNID)); CREATE TABLE DOCREFERENCES(CLASSID int null, SEQNO int not null, LASTMODONNODEID varchar(50) not null, PREVMODONNODEID varchar(50) null, ISSUEID varchar(50) not null, OBJECTID varchar(50) not null, REVISIONNUM int not null, CONTAINERID varchar(50) not null, AUTHORID varchar(50) not null, CREATIONDATE varchar(25) null, LASTMODIFIEDDATE varchar(25) null, UPDATENUMBER int null, PREVREVISIONNUM int null, LASTCMD int null, LASTCMDACLVERSION int null, USERDEFINEDFIELD varchar(300) null, LASTMODIFIEDBYID varchar(50) null, REFERENCEDOCUMENTID varchar(50) null, constraint PK_DOCREFERENCES primary key (ISSUEID, OBJECTID)); - CREATE TABLE DQ (TQUNID varchar(36) not null, ISSUEID varchar(50) not null, DEPENDSID varchar(50) null, DEPENDSTYPE int null, DEPENDSCOMMANDSTREAM blob null, DEPENDSNODEIDSEQNOKEY varchar(100) null, DEPENDSACLVERSION int null, constraint PK_DQ primary key (TQUNID)); - CREATE TABLE EMAILQ(id INT primary key, TIMEQUEUED int not null, NODEID varchar(50) not null, MIME blob not null, TQUNID varchar(36) not null); + CREATE TABLE DQ (TQUNID varchar(36) not null, ISSUEID varchar(50) not null, DEPENDSID varchar(50) null, DEPENDSTYPE int null, DEPENDSCOMMANDSTREAM SCALAR null, DEPENDSNODEIDSEQNOKEY varchar(100) null, DEPENDSACLVERSION int null, constraint PK_DQ primary key (TQUNID)); + CREATE TABLE EMAILQ(id INT primary key, TIMEQUEUED int not null, NODEID varchar(50) not null, MIME SCALAR not null, TQUNID varchar(36) not null); CREATE TABLE ENTERPRISEDATA(CLASSID int null, SEQNO int not null, LASTMODONNODEID varchar(50) not null, PREVMODONNODEID varchar(50) null, ISSUEID varchar(50) not null, OBJECTID varchar(50) not null, REVISIONNUM int not null, CONTAINERID varchar(50) not null, AUTHORID varchar(50) not null, CREATIONDATE varchar(25) null, LASTMODIFIEDDATE varchar(25) null, UPDATENUMBER int null, PREVREVISIONNUM int null, LASTCMD int null, LASTCMDACLVERSION int null, USERDEFINEDFIELD varchar(300) null, LASTMODIFIEDBYID varchar(50) null, DATE1 varchar(25) null, DATE2 varchar(25) null, DATE3 varchar(25) null, DATE4 varchar(25) null, DATE5 varchar(25) null, DATE6 varchar(25) null, DATE7 varchar(25) null, DATE8 varchar(25) null, DATE9 varchar(25) null, DATE10 varchar(25) null, VALUE1 int null, VALUE2 int null, VALUE3 int null, VALUE4 int null, VALUE5 int null, VALUE6 int null, VALUE7 int null, VALUE8 int null, VALUE9 int null, VALUE10 int null, VALUE11 int null, VALUE12 int null, VALUE13 int null, V ALUE14 int null, VALUE15 int null, VALUE16 int null, VALUE17 int null, VALUE18 int null, VALUE19 int null, VALUE20 int null, STRING1 varchar(300) null, STRING2 varchar(300) null, STRING3 varchar(300) null, STRING4 varchar(300) null, STRING5 varchar(300) null, STRING6 varchar(300) null, STRING7 varchar(300) null, STRING8 varchar(300) null, STRING9 varchar(300) null, STRING10 varchar(300) null, LONGSTRING1 text null, LONGSTRING2 text null, LONGSTRING3 text null, LONGSTRING4 text null, LONGSTRING5 text null, LONGSTRING6 text null, LONGSTRING7 text null, LONGSTRING8 text null, LONGSTRING9 text null, LONGSTRING10 text null, constraint PK_ENTERPRISEDATA primary key (ISSUEID, OBJECTID)); CREATE TABLE FILEMORGUE(TQUNID varchar(36) not null, PATH varchar(300) not null, DELETEFOLDERWHENEMPTY varchar(1) null, constraint PK_FILEMORGUE primary key (TQUNID)); CREATE TABLE FILES(CLASSID int null, SEQNO int not null, LASTMODONNODEID varchar(50) not null, PREVMODONNODEID varchar(50) null, ISSUEID varchar(50) not null, OBJECTID varchar(50) not null, REVISIONNUM int not null, CONTAINERID varchar(50) not null, AUTHORID varchar(50) not null, CREATIONDATE varchar(25) null, LASTMODIFIEDDATE varchar(25) null, UPDATENUMBER int null, PREVREVISIONNUM int null, LASTCMD int null, LASTCMDACLVERSION int null, USERDEFINEDFIELD varchar(300) null, LASTMODIFIEDBYID varchar(50) null, PARENTENTITYID varchar(50) null, BODY text null, BODYCONTENTTYPE varchar(100) null, ISOBSOLETE varchar(1) null, FILENAME varchar(300) not null, VISIBLENAME varchar(300) not null, VERSIONSTRING varchar(300) not null, DOCUMENTHASH varchar(40) not null, ISFINAL varchar(1) null, DOCREFERENCEID varchar(50) not null, constraint PK_FILES primary key (ISSUEID, OBJECTID)); CREATE TABLE FOLDERS(CLASSID int null, SEQNO int not null, LASTMODONNODEID varchar(50) not null, PREVMODONNODEID varchar(50) null, ISSUEID varchar(50) not null, OBJECTID varchar(50) not null, REVISIONNUM int not null, CONTAINERID varchar(50) not null, AUTHORID varchar(50) not null, CREATIONDATE varchar(25) null, LASTMODIFIEDDATE varchar(25) null, UPDATENUMBER int null, PREVREVISIONNUM int null, LASTCMD int null, LASTCMDACLVERSION int null, USERDEFINEDFIELD varchar(300) null, LASTMODIFIEDBYID varchar(50) null, CONTAINERNAME varchar(300) null, CONTAINERACLSETTINGS text null, constraint PK_FOLDERS primary key (ISSUEID, OBJECTID)); CREATE TABLE GLOBALSETTINGS(CLASSID int null, SEQNO int not null, LASTMODONNODEID varchar(50) not null, PREVMODONNODEID varchar(50) null, ISSUEID varchar(50) not null, OBJECTID varchar(50) not null, REVISIONNUM int not null, CONTAINERID varchar(50) not null, AUTHORID varchar(50) not null, CREATIONDATE varchar(25) null, LASTMODIFIEDDATE varchar(25) null, UPDATENUMBER int null, PREVREVISIONNUM int null, LASTCMD int null, LASTCMDACLVERSION int null, USERDEFINEDFIELD varchar(300) null, LASTMODIFIEDBYID varchar(50) null, SINGULARPROJECTLABEL varchar(30) not null, PLURALPROJECTLABEL varchar(30) not null, PROJECTREQUIRED varchar(1) not null, CUSTOMPROJECTSALLOWED varchar(1) not null, ACTIONITEMSPECXML text null, PROJECTLISTXML text null, ENTERPRISEDATALABELS text null, ENTERPRISEDATATABXSL text null, constraint PK_GLOBALSETTINGS primary key (ISSUEID, OBJECTID)); CREATE TABLE GLOBALSTRINGPROPERTIES(ID int not null, VALUE varchar(300) not null, constraint PK_GLOBALSTRINGPROPERTIES primary key (ID)); - CREATE TABLE IMQ(TQUNID varchar(36) not null, DATETIMEQUEUED varchar(25) not null, ISSUEID varchar(50) not null, KUBIBUILD varchar(30) not null, FAILCOUNT int not null, LASTRUN varchar(25) null, ENVELOPESTREAM blob not null, PAYLOADSTREAM blob not null, constraint PK_IMQ primary key (TQUNID)); + CREATE TABLE IMQ(TQUNID varchar(36) not null, DATETIMEQUEUED varchar(25) not null, ISSUEID varchar(50) not null, KUBIBUILD varchar(30) not null, FAILCOUNT int not null, LASTRUN varchar(25) null, ENVELOPESTREAM SCALAR not null, PAYLOADSTREAM SCALAR not null, constraint PK_IMQ primary key (TQUNID)); CREATE TABLE INVITATIONNODES(INVITATIONID varchar(50) not null, RECIPIENTNODEID varchar(50) not null, DATECREATED varchar(25) not null, constraint PK_INVITATIONNODES primary key (INVITATIONID, RECIPIENTNODEID)); - CREATE TABLE INVITATIONS (id INT primary key, INVITATIONID varchar(50) not null, SENDERNODEID varchar(50) not null, RECIPIENTEMAILADDR varchar(200) not null, RECIPIENTUSERID varchar(50) null, RECIPIENTNODES text null, ISSUEID varchar(50) not null, ENVELOPE text not null, MESSAGEBLOB blob not null, INVITATIONSTATE int not null, TQUNID varchar(36) not null, DATECREATED varchar(25) not null); + CREATE TABLE INVITATIONS (id INT primary key, INVITATIONID varchar(50) not null, SENDERNODEID varchar(50) not null, RECIPIENTEMAILADDR varchar(200) not null, RECIPIENTUSERID varchar(50) null, RECIPIENTNODES text null, ISSUEID varchar(50) not null, ENVELOPE text not null, MESSAGEBLOB SCALAR not null, INVITATIONSTATE int not null, TQUNID varchar(36) not null, DATECREATED varchar(25) not null); CREATE TABLE ISSUES (CLASSID int null, SEQNO int not null, LASTMODONNODEID varchar(50) not null, PREVMODONNODEID varchar(50) null, ISSUEID varchar(50) not null, OBJECTID varchar(50) not null, REVISIONNUM int not null, CONTAINERID varchar(50) not null, AUTHORID varchar(50) not null, CREATIONDATE varchar(25) null, LASTMODIFIEDDATE varchar(25) null, UPDATENUMBER int null, PREVREVISIONNUM int null, LASTCMD int null, LASTCMDACLVERSION int null, USERDEFINEDFIELD varchar(300) null, LASTMODIFIEDBYID varchar(50) null, CONTAINERNAME varchar(300) null, CONTAINERACLSETTINGS text null, ISINITIALIZED varchar(1) null, BLINDINVITES text null, ISSYSTEMISSUE varchar(1) not null, ISSUETYPE int not null, ACTIVITYTYPEID varchar(50) null, ISINCOMPLETE varchar(1) not null, constraint PK_ISSUES primary key (ISSUEID, OBJECTID)); CREATE TABLE ISSUESETTINGS (CLASSID int null, SEQNO int not null, LASTMODONNODEID varchar(50) not null, PREVMODONNODEID varchar(50) null, ISSUEID varchar(50) not null, OBJECTID varchar(50) not null, REVISIONNUM int not null, CONTAINERID varchar(50) not null, AUTHORID varchar(50) not null, CREATIONDATE varchar(25) null, LASTMODIFIEDDATE varchar(25) null, UPDATENUMBER int null, PREVREVISIONNUM int null, LASTCMD int null, LASTCMDACLVERSION int null, USERDEFINEDFIELD varchar(300) null, LASTMODIFIEDBYID varchar(50) null, ISSUENAME varchar(300) not null, ISSUEACLSETTINGS text not null, ISSUEDUEDATE varchar(25) null, ISSUEPRIORITY int null, ISSUESTATUS int null, DESCRIPTION text null, PROJECTID varchar(100) null, PROJECTNAME text null, PROJECTNAMEISCUSTOM varchar(1) null, ISSYSTEMISSUE varchar(1) not null, ACTIONITEMREVNUM int not null, constraint PK_ISSUESETTINGS primary key (ISSUEID, OBJECTID)); - CREATE TABLE KMTPMSG (MSGID integer not null, SENDERID varchar(50) null, RECIPIENTIDLIST text not null, ISSUEID varchar(50) null, MESSAGETYPE int not null, ENVELOPE text null, MESSAGEBLOB blob not null, RECEIVEDDATE varchar(25) not null, constraint PK_KMTPMSG primary key (MSGID)); + CREATE TABLE KMTPMSG (MSGID integer not null, SENDERID varchar(50) null, RECIPIENTIDLIST text not null, ISSUEID varchar(50) null, MESSAGETYPE int not null, ENVELOPE text null, MESSAGEBLOB SCALAR not null, RECEIVEDDATE varchar(25) not null, constraint PK_KMTPMSG primary key (MSGID)); CREATE TABLE KMTPNODEQ(id INT primary key, NODEID varchar(50) not null, MSGID int not null, RECEIVEDDATE varchar(25) not null, SENDCOUNT int not null); - CREATE TABLE KMTPQ(MSGID integer not null, SENDERID varchar(50) null, RECIPIENTIDLIST text not null, ISSUEID varchar(50) null, MESSAGETYPE int not null, ENVELOPE text null, MESSAGEBLOB blob not null, constraint PK_KMTPQ primary key (MSGID)); + CREATE TABLE KMTPQ(MSGID integer not null, SENDERID varchar(50) null, RECIPIENTIDLIST text not null, ISSUEID varchar(50) null, MESSAGETYPE int not null, ENVELOPE text null, MESSAGEBLOB SCALAR not null, constraint PK_KMTPQ primary key (MSGID)); CREATE TABLE LOGENTRIES(CLASSID int null, SEQNO int not null, LASTMODONNODEID varchar(50) not null, PREVMODONNODEID varchar(50) null, ISSUEID varchar(50) not null, OBJECTID varchar(50) not null, REVISIONNUM int not null, CONTAINERID varchar(50) not null, AUTHORID varchar(50) not null, CREATIONDATE varchar(25) null, LASTMODIFIEDDATE varchar(25) null, UPDATENUMBER int null, PREVREVISIONNUM int null, LASTCMD int null, LASTCMDACLVERSION int null, USERDEFINEDFIELD varchar(300) null, LASTMODIFIEDBYID varchar(50) null, PARENTENTITYID varchar(50) null, BODY text null, BODYCONTENTTYPE varchar(100) null, ISOBSOLETE varchar(1) null, ACTIONTYPE int not null, ASSOCIATEDOBJECTIDS text null, OLDENTITIES text null, NEWENTITIES text null, OTHERENTITIES text null, constraint PK_LOGENTRIES primary key (ISSUEID, OBJECTID)); CREATE TABLE LSBI(TQUNID varchar(36) not null, ISSUEID varchar(50) not null, TABLEITEMID varchar(50) null, TABLENODEID varchar(50) null, TABLECMD int null, TABLECONTAINERID varchar(50) null, TABLESEQNO int null, DIRTYCONTENT text null, STUBBED varchar(1) null, ENTITYSTUBDATA text null, UPDATENUMBER int not null, constraint PK_LSBI primary key (TQUNID)); CREATE TABLE LSBN(TQUNID varchar(36) not null, ISSUEID varchar(50) not null, NODEID varchar(50) not null, STORESEQNO int not null, SYNCSEQNO int not null, LASTMSGDATE varchar(25) null, constraint PK_LSBN primary key (TQUNID)); diff --git a/test/sql-tap/types2.test.lua b/test/sql-tap/types2.test.lua index bce42e796..0640c8048 100755 --- a/test/sql-tap/types2.test.lua +++ b/test/sql-tap/types2.test.lua @@ -43,7 +43,7 @@ test:execsql [[ n2 NUMERIC, t1 TEXT, t2 TEXT, - o1 BLOB, + o1 SCALAR, o2 BLOB ); INSERT INTO t1 VALUES(1, NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); @@ -112,7 +112,7 @@ test_bool("types2-1.28", "o1='500'", "'500.0' = o1", 0) local vals = { 10, "10.0", "'10'", "'10.0'", 20, "20.0", "'20'", "'20.0'", 30, "30.0", "'30'", "'30.0'" } -- 1 2 3 4 5 6 7 8 9 10 11 12 test:execsql [[ - CREATE TABLE t2(id INT primary key, i INTEGER, n NUMERIC, t TEXT, o BLOB); + CREATE TABLE t2(id INT primary key, i INTEGER, n NUMERIC, t TEXT, o SCALAR); CREATE INDEX t2i1 ON t2(i); CREATE INDEX t2i2 ON t2(n); CREATE INDEX t2i3 ON t2(t); @@ -274,7 +274,7 @@ test_boolset("types2-6.9", "id IN (1, 6, 10)", {1, 6, 10}) -- Tests types2-7.* concentrate on expressions of the form -- "x IN (SELECT...)" with no index. test:execsql [[ - CREATE TABLE t3(id INT primary key, i INTEGER, n NUMERIC, t TEXT, o BLOB); + CREATE TABLE t3(id INT primary key, i INTEGER, n NUMERIC, t TEXT, o SCALAR); INSERT INTO t3 VALUES(1, 1, 1, 1, 1); INSERT INTO t3 VALUES(2, 2, 2, 2, 2); INSERT INTO t3 VALUES(3, 3, 3, 3, 3); @@ -306,7 +306,7 @@ test_bool("types2-7.15", "o1='2'", "o1 IN (SELECT o||'' FROM t3)", 1) -- set vals [list 10 10.0 '10' '10.0' 20 20.0 '20' '20.0' 30 30.0 '30' '30.0'] -- 1 2 3 4 5 6 7 8 9 10 11 12 test:execsql [[ - CREATE TABLE t4(id INT primary key, i INTEGER, n NUMERIC, t VARCHAR(20), o BLOB); + CREATE TABLE t4(id INT primary key, i INTEGER, n NUMERIC, t VARCHAR(20), o SCALAR); INSERT INTO t4 VALUES(1, 10, 20, 20, 30); ]] test_boolset("types2-8.1", "i IN (SELECT i FROM t4)", {1, 2, 3, 4}) diff --git a/test/sql-tap/whereB.test.lua b/test/sql-tap/whereB.test.lua index 6ecc3cc5d..e04e37dc4 100755 --- a/test/sql-tap/whereB.test.lua +++ b/test/sql-tap/whereB.test.lua @@ -129,7 +129,7 @@ test:do_execsql_test( CREATE TABLE t1(x INT primary key, y TEXT); -- affinity of t1.y is TEXT INSERT INTO t1 VALUES(1,99); - CREATE TABLE t2(a INT primary key, b BLOB); -- affinity of t2.b is NONE + CREATE TABLE t2(a INT primary key, b SCALAR); -- affinity of t2.b is NONE CREATE INDEX t2b ON t2(b); INSERT INTO t2 VALUES(2, 99); @@ -222,10 +222,10 @@ test:do_execsql_test( DROP TABLE t1; DROP TABLE t2; - CREATE TABLE t1(x INT primary key, y BLOB); -- affinity of t1.y is NONE + CREATE TABLE t1(x INT primary key, y SCALAR); -- affinity of t1.y is NONE INSERT INTO t1 VALUES(1,99); - CREATE TABLE t2(a INT primary key, b BLOB); -- affinity of t2.b is NONE + CREATE TABLE t2(a INT primary key, b SCALAR); -- affinity of t2.b is NONE CREATE INDEX t2b ON t2(b); INSERT INTO t2 VALUES(2,'99'); @@ -318,7 +318,7 @@ test:do_execsql_test( DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; - CREATE TABLE t1(x INT primary key, y BLOB); -- affinity of t1.y is NONE + CREATE TABLE t1(x INT primary key, y SCALAR); -- affinity of t1.y is NONE INSERT INTO t1 VALUES(1,'99'); CREATE TABLE t2(a INT primary key, b FLOAT); -- affinity of t2.b is NUMERIC @@ -418,7 +418,7 @@ test:do_execsql_test( DROP TABLE t1; DROP TABLE t2; - CREATE TABLE t1(x INT primary key, y BLOB); -- affinity of t1.y is NONE + CREATE TABLE t1(x INT primary key, y SCALAR); -- affinity of t1.y is NONE INSERT INTO t1 VALUES(1,'99'); CREATE TABLE t2(a INT primary key, b INT); -- affinity of t2.b is INTEGER @@ -518,7 +518,7 @@ test:do_execsql_test( DROP TABLE t1; DROP TABLE t2; - CREATE TABLE t1(x INT primary key, y BLOB); -- affinity of t1.y is NONE + CREATE TABLE t1(x INT primary key, y SCALAR); -- affinity of t1.y is NONE INSERT INTO t1 VALUES(1,'99'); CREATE TABLE t2(a INT primary key, b FLOAT); -- affinity of t2.b is REAL @@ -621,7 +621,7 @@ test:do_execsql_test( CREATE TABLE t1(x INT primary key, y FLOAT); -- affinity of t1.y is NUMERIC INSERT INTO t1 VALUES(1,99); - CREATE TABLE t2(a INT primary key, b BLOB); -- affinity of t2.b is NONE + CREATE TABLE t2(a INT primary key, b SCALAR); -- affinity of t2.b is NONE CREATE INDEX t2b ON t2(b); INSERT INTO t2 VALUES(2,'99'); @@ -721,7 +721,7 @@ test:do_execsql_test( CREATE TABLE t1(x INT primary key, y INT); -- affinity of t1.y is INTEGER INSERT INTO t1 VALUES(1,99); - CREATE TABLE t2(a INT primary key, b BLOB); -- affinity of t2.b is NONE + CREATE TABLE t2(a INT primary key, b SCALAR); -- affinity of t2.b is NONE CREATE INDEX t2b ON t2(b); INSERT INTO t2 VALUES(2,'99'); @@ -821,7 +821,7 @@ test:do_execsql_test( CREATE TABLE t1(x INT primary key, y FLOAT); -- affinity of t1.y is REAL INSERT INTO t1 VALUES(1,99.0); - CREATE TABLE t2(a INT primary key, b BLOB); -- affinity of t2.b is NONE + CREATE TABLE t2(a INT primary key, b SCALAR); -- affinity of t2.b is NONE CREATE INDEX t2b ON t2(b); INSERT INTO t2 VALUES(2,'99'); diff --git a/test/sql/gh-3888-values-blob-assert.result b/test/sql/gh-3888-values-blob-assert.result index 67948cd3f..8deded7d0 100644 --- a/test/sql/gh-3888-values-blob-assert.result +++ b/test/sql/gh-3888-values-blob-assert.result @@ -14,18 +14,18 @@ box.sql.execute('pragma sql_default_engine=\''..engine..'\'') --- ... -- check 'VALUES' against typedef keywords (should fail) -box.sql.execute('VALUES(blob)') +box.sql.execute('VALUES(scalar)') --- -- error: 'near "blob": syntax error' +- error: 'near "scalar": syntax error' ... box.sql.execute('VALUES(float)') --- - error: 'near "float": syntax error' ... -- check 'SELECT' against typedef keywords (should fail) -box.sql.execute('SELECT blob') +box.sql.execute('SELECT scalar') --- -- error: 'near "blob": syntax error' +- error: 'near "scalar": syntax error' ... box.sql.execute('SELECT float') --- diff --git a/test/sql/gh-3888-values-blob-assert.test.lua b/test/sql/gh-3888-values-blob-assert.test.lua index 9adab488c..6e916c258 100644 --- a/test/sql/gh-3888-values-blob-assert.test.lua +++ b/test/sql/gh-3888-values-blob-assert.test.lua @@ -9,11 +9,11 @@ engine = test_run:get_cfg('engine') box.sql.execute('pragma sql_default_engine=\''..engine..'\'') -- check 'VALUES' against typedef keywords (should fail) -box.sql.execute('VALUES(blob)') +box.sql.execute('VALUES(scalar)') box.sql.execute('VALUES(float)') -- check 'SELECT' against typedef keywords (should fail) -box.sql.execute('SELECT blob') +box.sql.execute('SELECT scalar') box.sql.execute('SELECT float') -- check 'VALUES' against ID (should fail) diff --git a/test/sql/transition.result b/test/sql/transition.result index e7613c344..ea110c03c 100644 --- a/test/sql/transition.result +++ b/test/sql/transition.result @@ -178,7 +178,7 @@ box.sql.execute("DROP TABLE barfoo") --- ... -- attempt to create a table lacking PRIMARY KEY -box.sql.execute("CREATE TABLE without_rowid_lacking_primary_key(x BLOB)") +box.sql.execute("CREATE TABLE without_rowid_lacking_primary_key(x SCALAR)") --- - error: PRIMARY KEY missing on table WITHOUT_ROWID_LACKING_PRIMARY_KEY ... diff --git a/test/sql/transition.test.lua b/test/sql/transition.test.lua index 276ff7174..2fbee6255 100644 --- a/test/sql/transition.test.lua +++ b/test/sql/transition.test.lua @@ -63,7 +63,7 @@ box.sql.execute("DROP TABLE foobar") box.sql.execute("DROP TABLE barfoo") -- attempt to create a table lacking PRIMARY KEY -box.sql.execute("CREATE TABLE without_rowid_lacking_primary_key(x BLOB)") +box.sql.execute("CREATE TABLE without_rowid_lacking_primary_key(x SCALAR)") -- create a table with implicit indices (used to SEGFAULT) box.sql.execute("CREATE TABLE implicit_indices(a INT PRIMARY KEY,b INT,c INT,d TEXT UNIQUE)") diff --git a/test/sql/types.result b/test/sql/types.result index a0159d661..11b045c05 100644 --- a/test/sql/types.result +++ b/test/sql/types.result @@ -28,7 +28,7 @@ box.sql.execute("CREATE TABLE t1 (id INT PRIMARY KEY, a INT, b UNIQUE);") ... -- gh-3104: real type is stored in space format. -- -box.sql.execute("CREATE TABLE t1 (id TEXT PRIMARY KEY, a REAL, b INT, c TEXT, d BLOB);") +box.sql.execute("CREATE TABLE t1 (id TEXT PRIMARY KEY, a REAL, b INT, c TEXT, d SCALAR);") --- ... box.space.T1:format() @@ -155,20 +155,16 @@ box.sql.execute("SELECt 'a' || 'b' || 1;") ... -- What is more, they must be of the same type. -- -box.sql.execute("SELECT 'abc' || CAST('x' AS BLOB);") +box.sql.execute("SELECT 'abc' || randomblob(5);") --- - error: 'Inconsistent types: expected TEXT got BLOB' ... -box.sql.execute("SELECT CAST('abc' AS BLOB) || 'x';") +box.sql.execute("SELECT randomblob(5) || 'x';") --- - error: 'Inconsistent types: expected BLOB got TEXT' ... -- Result of BLOBs concatenation must be BLOB. -- -box.sql.execute("SELECT TYPEOF(CAST('abc' AS BLOB) || CAST('cda' AS BLOB))") ---- -- - ['blob'] -... box.sql.execute("VALUES (TYPEOF(randomblob(5) || zeroblob(5)));") --- - - ['blob'] diff --git a/test/sql/types.test.lua b/test/sql/types.test.lua index b944b081b..799e9aed3 100644 --- a/test/sql/types.test.lua +++ b/test/sql/types.test.lua @@ -11,7 +11,7 @@ box.sql.execute("CREATE TABLE t1 (id INT PRIMARY KEY, a INT, b UNIQUE);") -- gh-3104: real type is stored in space format. -- -box.sql.execute("CREATE TABLE t1 (id TEXT PRIMARY KEY, a REAL, b INT, c TEXT, d BLOB);") +box.sql.execute("CREATE TABLE t1 (id TEXT PRIMARY KEY, a REAL, b INT, c TEXT, d SCALAR);") box.space.T1:format() box.sql.execute("CREATE VIEW v1 AS SELECT b + a, b - a FROM t1;") box.space.V1:format() @@ -50,9 +50,8 @@ box.sql.execute("SELECT 1.123 || 'abc';") box.sql.execute("SELECt 'a' || 'b' || 1;") -- What is more, they must be of the same type. -- -box.sql.execute("SELECT 'abc' || CAST('x' AS BLOB);") -box.sql.execute("SELECT CAST('abc' AS BLOB) || 'x';") +box.sql.execute("SELECT 'abc' || randomblob(5);") +box.sql.execute("SELECT randomblob(5) || 'x';") -- Result of BLOBs concatenation must be BLOB. -- -box.sql.execute("SELECT TYPEOF(CAST('abc' AS BLOB) || CAST('cda' AS BLOB))") box.sql.execute("VALUES (TYPEOF(randomblob(5) || zeroblob(5)));") -- 2.15.1