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 455486EC58; Tue, 25 May 2021 12:03:06 +0300 (MSK) DKIM-Filter: OpenDKIM Filter v2.11.0 dev.tarantool.org 455486EC58 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=tarantool.org; s=dev; t=1621933386; bh=1vntfuItJ4k/zHTD37hcZPmLtfSLW5tNKm/YInY/oL0=; h=To:Date:In-Reply-To:References:Subject:List-Id:List-Unsubscribe: List-Archive:List-Post:List-Help:List-Subscribe:From:Reply-To: From; b=ATU0b8yID+Zm6L3jbS1pFRpPjE4G850u+3GZEr0XecIvpECYnismATnihuE0LMJ2u iruJzdMDEZUlqbNK8jg9kP0u+Pjw9pdQM7JZN3DU5H/lA8JRxIHUGDfueXTfDXIMys XTAKiYehwdTJh2tBUXut09Pwi2Lr+66i3aQZ192A= Received: from smtp46.i.mail.ru (smtp46.i.mail.ru [94.100.177.106]) (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 212FB6E1D7 for ; Tue, 25 May 2021 12:01:36 +0300 (MSK) DKIM-Filter: OpenDKIM Filter v2.11.0 dev.tarantool.org 212FB6E1D7 Received: by smtp46.i.mail.ru with esmtpa (envelope-from ) id 1llSwR-0005YC-58; Tue, 25 May 2021 12:01:35 +0300 To: tarantool-patches@dev.tarantool.org, imeevma@tarantool.org Date: Tue, 25 May 2021 12:01:02 +0300 Message-Id: X-Mailer: git-send-email 2.29.2 In-Reply-To: References: MIME-Version: 1.0 Content-Transfer-Encoding: 8bit X-7564579A: 646B95376F6C166E X-77F55803: 4F1203BC0FB41BD91B019B01C53E51AF6F63A46F26EE68FD6CF19A7203B0589400894C459B0CD1B93A617B26A467B3823C8A0A6F76F4440686E560675804A5CA1473A5891B7656E9 X-7FA49CB5: FF5795518A3D127A4AD6D5ED66289B5278DA827A17800CE7E50C24D7D7C3118AC2099A533E45F2D0395957E7521B51C2CFCAF695D4D8E9FCEA1F7E6F0F101C6778DA827A17800CE7711EC8ACFEA21396EA1F7E6F0F101C6723150C8DA25C47586E58E00D9D99D84E1BDDB23E98D2D38BD6CF32B5F8F9D404E1713F5676A413618139B088B2B3CD86CC7F00164DA146DAFE8445B8C89999728AA50765F7900637F3E38EE449E3E2AE389733CBF5DBD5E9C8A9BA7A39EFB766F5D81C698A659EA7CC7F00164DA146DA9985D098DBDEAEC8062BEEFFB5F8EA3EF6B57BC7E6449061A352F6E88A58FB86F5D81C698A659EA73AA81AA40904B5D9A18204E546F3947C00FA66E0FB566236AD7EC71F1DB884274AD6D5ED66289B52698AB9A7B718F8C46E0066C2D8992A16725E5C173C3A84C3D7CBAC48D27AF77DBA3038C0950A5D36B5C8C57E37DE458B0BC6067A898B09E46D1867E19FE14079C09775C1D3CA48CF3D321E7403792E342EB15956EA79C166A417C69337E82CC275ECD9A6C639B01B78DA827A17800CE7503BF1445B848802731C566533BA786AA5CC5B56E945C8DA X-C1DE0DAB: C20DE7B7AB408E4181F030C43753B8186998911F362727C414F749A5E30D975CDFB9373DB28FDDFB17D533DF40F137B125149EF778C3DCC39C2B6934AE262D3EE7EAB7254005DCED114C52B35DBB74F4E7EAB7254005DCEDA5DF9383870C0FED1E0A4E2319210D9B64D260DF9561598F01A9E91200F654B08F3D2DDDCA87B9828E8E86DC7131B365E7726E8460B7C23C X-C8649E89: 4E36BF7865823D7055A7F0CF078B5EC49A30900B95165D34D1AE09A115117C96145A296BEC2E353494006641929D07260DF029C5D855F43FA6F2AB9E5F6231A71D7E09C32AA3244C9A73A687B18AC513B75ADB34C5F1F59D3C6EB905E3A8056BFACE5A9C96DEB163 X-D57D3AED: 3ZO7eAau8CL7WIMRKs4sN3D3tLDjz0dLbV79QFUyzQ2Ujvy7cMT6pYYqY16iZVKkSc3dCLJ7zSJH7+u4VD18S7Vl4ZUrpaVfd2+vE6kuoey4m4VkSEu530nj6fImhcD4MUrOEAnl0W826KZ9Q+tr5ycPtXkTV4k65bRjmOUUP8cvGozZ33TWg5HZplvhhXbhDGzqmQDTd6OAevLeAnq3Ra9uf7zvY2zzsIhlcp/Y7m53TZgf2aB4JOg4gkr2biojbL9S8ysBdXiFlDFT/xDPqs8oFX7zVcva X-Mailru-Sender: B5B6A6EBBD94DAD8C2E767F6285855A0D0E3CAC6389DF310465538F9CE1ED613D0169805F923CF225C2808D6142752370A8ED71B308007E3DC85537438B7E1A423D748DE48713E689437F6177E88F7363CDA0F3B3F5B9367 X-Mras: Ok Subject: [Tarantool-patches] [PATCH 3/3] sql: introduced explicit casts test e_casts.test.lua 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: Timur Safin via Tarantool-patches Reply-To: Timur Safin Errors-To: tarantool-patches-bounces@dev.tarantool.org Sender: "Tarantool-patches" * e_casts.test.lua is generating expressions of `cast(input as output)` form. And checks whether we expectedly succeed or fail, given the previously agreed excplicit conversion table from /doc/rfc/5910-consistent-sql-lua-types.md; * At the moment we skip DECIMALs, UUIDs, ARRAYs and MAPs as not yet fully supported. Need to be reenabled later; * there is `-verbose` mode one may activate to enable more detailed reporting. Relates to #5910, #6009 Part of #4407 --- test/sql-tap/e_casts.test.lua | 355 ++++++++++++++++++++++++++++++++++ 1 file changed, 355 insertions(+) create mode 100755 test/sql-tap/e_casts.test.lua diff --git a/test/sql-tap/e_casts.test.lua b/test/sql-tap/e_casts.test.lua new file mode 100755 index 000000000..eafebd5bc --- /dev/null +++ b/test/sql-tap/e_casts.test.lua @@ -0,0 +1,355 @@ +#!/usr/bin/env tarantool +local test = require("sqltester") +test:plan(222) + +local yaml = require("yaml") +local ffi = require("ffi") + +local verbose = 0 + +if arg[1] == '-v' or arg[1] == '--verbose' then + verbose = 1 +end + +ffi.cdef [[ + enum field_type { + FIELD_TYPE_ANY = 0, + FIELD_TYPE_UNSIGNED, + FIELD_TYPE_STRING, + FIELD_TYPE_NUMBER, + FIELD_TYPE_DOUBLE, + FIELD_TYPE_INTEGER, + FIELD_TYPE_BOOLEAN, + FIELD_TYPE_VARBINARY, + FIELD_TYPE_SCALAR, + FIELD_TYPE_DECIMAL, + FIELD_TYPE_UUID, + FIELD_TYPE_ARRAY, + FIELD_TYPE_MAP, + field_type_MAX + }; +]] + +-- Date/time/interval types to be uncommented and used +-- once corresponding box implementation completed +local t_any = ffi.C.FIELD_TYPE_ANY +local t_unsigned = ffi.C.FIELD_TYPE_UNSIGNED +local t_string = ffi.C.FIELD_TYPE_STRING +local t_number = ffi.C.FIELD_TYPE_NUMBER +local t_double = ffi.C.FIELD_TYPE_DOUBLE +local t_integer = ffi.C.FIELD_TYPE_INTEGER +local t_boolean = ffi.C.FIELD_TYPE_BOOLEAN +local t_varbinary = ffi.C.FIELD_TYPE_VARBINARY +local t_scalar = ffi.C.FIELD_TYPE_SCALAR +local t_decimal = ffi.C.FIELD_TYPE_DECIMAL +-- local t_date = -1 +-- local t_time = -2 +-- local t_timestamp = -3 +-- local t_interval = -4 +local t_uuid = ffi.C.FIELD_TYPE_UUID +local t_array = ffi.C.FIELD_TYPE_ARRAY +local t_map = ffi.C.FIELD_TYPE_MAP + +local proper_order = { + t_any, + t_unsigned, + t_string, + t_double, + t_integer, + t_boolean, + t_varbinary, + t_number, + t_decimal, + t_uuid, + -- t_date, + -- t_time, + -- t_timestamp, + -- t_interval, + t_array, + t_map, + t_scalar, +} + +local type_names = { + [t_any] = 'any', + [t_unsigned] = 'unsigned', + [t_string] = 'string', + [t_double] = 'double', + [t_integer] = 'integer', + [t_boolean] = 'boolean', + [t_varbinary] = 'varbinary', + [t_number] = 'number', + [t_decimal] = 'decimal', + [t_uuid] = 'uuid', + -- [t_date] = 'date', + -- [t_time] = 'time', + -- [t_timestamp] = 'timestamp', + -- [t_interval] = 'interval', + [t_array] = 'array', + [t_map] = 'map', + [t_scalar] = 'scalar', +} + +-- not all types implemented/enabled at the moment +-- but we do keep their projected status in the +-- spec table +local enabled_type = { + [t_any] = false, -- there is no way in SQL to instantiate ANY type expression + [t_unsigned] = true, + [t_string] = true, + [t_double] = true, + [t_integer] = true, + [t_boolean] = true, + [t_varbinary] = true, + [t_number] = true, + [t_decimal] = false, + [t_uuid] = false, + -- [t_date] = false, + -- [t_time] = false, + -- [t_timestamp]= false, + -- [t_interval] = False, + [t_array] = false, + [t_map] = false, + [t_scalar] = true, +} + +-- table of _TSV_ (tab separated values) +-- copied from sql-lua-tables-v5.xls // TNT implicit today +local explicit_casts_table_spec = { + [t_any] = {"Y", "S", "Y", "S", "S", "S", "S", "S", "S", "S", "S", "S", "S"}, + [t_unsigned]= {"Y", "Y", "Y", "Y", "Y", "" , "" , "Y", "Y", "" , "" , "" , "Y"}, + [t_string] = {"Y", "S", "Y", "S", "S", "S", "Y", "S", "S", "S", "S", "S", "Y"}, + [t_double] = {"Y", "S", "Y", "Y", "S", "" , "" , "Y", "Y", "" , "" , "" , "Y"}, + [t_integer] = {"Y", "S", "Y", "Y", "Y", "" , "" , "Y", "Y", "" , "" , "" , "Y"}, + [t_boolean] = {"Y", "" , "Y", "" , "" , "Y", "" , "" , "" , "" , "" , "" , "Y"}, + [t_varbinary]={"Y", "" , "Y", "N", "" , "" , "Y", "" , "" , "S", "" , "" , "Y"}, + [t_number] = {"Y", "S", "Y", "Y", "S", "" , "" , "Y", "Y", "" , "" , "" , "Y"}, + [t_decimal] = {"Y", "S", "Y", "S", "S", "" , "" , "Y", "Y", "" , "" , "" , "Y"}, + [t_uuid] = {"Y", "" , "Y", "" , "" , "" , "Y", "" , "" , "Y", "" , "" , "Y"}, + [t_array] = {"Y", "N", "Y", "N", "N", "N", "" , "" , "" , "" , "Y", "" , "N"}, + [t_map] = {"Y", "N", "Y", "N", "N", "N", "" , "" , "" , "" , "" , "Y", "N"}, + [t_scalar] = {"Y", "S", "Y", "S", "S", "S", "S", "S", "S", "S", "" , "" , "Y"}, +} + +-- local extra_checks = false +local explicit_casts = {} +-- local implicit_casts = {} + +-- implicit conversion table is considered consistent if +-- it's sort of symmetric against diagonal +-- (not necessary that always/sometimes are matching +-- but at least something should be presented) + +--[[ local function check_table_consistency(table) + for _, i in ipairs(proper_order) do + local string = '' + for _, j in ipairs(proper_order) do + print(i, j) + -- local item = implicit_casts[i][j] + -- string = string .. (xlat[item] or ' ') + end + print(string) + end +end +]] + + -- if there is enabled extra checks then check ocnsistency of input tables + -- just to make sure their sanity +--[[ if extra_checks then + check_table_consistency() + end + ]] + +local c_no = 0 +local c_maybe = 1 +local c_yes = 2 + +local function normalize_cast(v) + local xlat = { + ['Y'] = c_yes, + ['S'] = c_maybe, + ['N'] = c_no, + } + return xlat[v ~= nil and v or 'N'] +end + +local function human_cast(v) + local xlat = { + [c_yes] = 'Y', + [c_maybe] = 'S', + [c_no] = ' ' + } + return xlat[v ~= nil and v or c_no] +end + +local function load_casts_spec(spec_table) + local casts = {} + for i, t_from in ipairs(proper_order) do + local row = spec_table[t_from] + casts[t_from] = {} + for j, t_to in ipairs(proper_order) do + if enabled_type[t_from] and enabled_type[t_to] then + casts[t_from][t_to] = normalize_cast(spec_table[t_from][j]) + end + end + end + -- if there is enabled extra checks then check ocnsistency of input tables + -- just to make sure their sanity +--[[ if extra_checks then + check_table_consistency() + end ]] + + return casts +end + +explicit_casts = load_casts_spec(explicit_casts_table_spec) + +if verbose > 0 then + local function show_casts_table(table) + local max_len = #"12. varbinary" + 1 + + -- show banner + local col_names = '' + for i, t_val in ipairs(proper_order) do + col_names = col_names .. string.format("%2d |", t_val) + end + col_names = string.sub(col_names, 1, #col_names-1) + print(string.format("%"..max_len.."s|%s|", "", col_names)) + -- show splitter + local banner = '+---+---+---+---+---+---+---+---+---+---+---+---+---+' + print(string.format("%"..max_len.."s%s", "", banner)) + + for i, from in ipairs(proper_order) do + local line = '' + for j, to in ipairs(proper_order) do + line = line .. string.format("%2s |", human_cast(table[from][to])) + end + line = string.sub(line, 1, #line-1) + local s = string.format("%2d.%10s |%s|", from, type_names[from], line) + print(s) + end + print(string.format("%"..max_len.."s%s", "", banner)) + end + + show_casts_table(explicit_casts) +end + +local function merge_tables(...) + local n = select('#', ...) + local tables = {...} + local result = {} + + for i=1,n do + local t = tables[i] + --print(yaml.encode(t)) + assert(type(tables[i]) == 'table') + for j,v in pairs(t) do + table.insert(result, v) + end + end + return result +end + +local gen_type_samples = { + [t_unsigned] = {"0", "1", "2"}, + [t_integer] = {"-678", "-1", "0", "1", "2", "3", "678"}, + [t_double] = {"0.0", "123.4", "-567.8"}, + [t_string] = {"''", "'1'", "'abc'", "'def'", "'TRUE'", "'FALSE'"}, + [t_boolean] = {"false", "true", "null"}, + [t_varbinary] = {"X'312E3233'", "X'2D392E3837'", "X'302E30303031'"}, +} + +local function gen_type_exprs(type) + if type == t_number then + return merge_tables(gen_type_samples[t_unsigned], + gen_type_samples[t_integer], + gen_type_samples[t_double]) + end + if type == t_scalar then + return merge_tables(gen_type_samples[t_unsigned], + gen_type_samples[t_integer], + gen_type_samples[t_double], + gen_type_samples[t_string], + gen_type_samples[t_boolean], + gen_type_samples[t_varbinary]) + end + return gen_type_samples[type] or {} +end + +local function gen_sql_cast_from_to(t_from, t_to) + local queries = {} + local from_exprs = gen_type_exprs(t_from) + local to_typename = type_names[t_to] + for _, expr in pairs(from_exprs) do + local query = string.format([[ select cast(%s as %s); ]], expr, to_typename) + table.insert(queries, query) + end + return queries +end + +local function catch_query(query) + local result = {pcall(box.execute, query)} + + if not result[1] or result[3] ~= nil then + return false, result[3] + end + return true, result[2] +end + +local function label_for(from, to, query) + local parent_frame = debug.getinfo(2, "nSl") + local filename = parent_frame.source:sub(1,1) == "@" and parent_frame.source:sub(2) + local line = parent_frame.currentline + return string.format("%s+%d:[%s,%s] %s", filename, line, + type_names[from], type_names[to], query) +end + +for i, from in ipairs(proper_order) do + for j, to in ipairs(proper_order) do + -- skip ANY, DECIMAL, UUID, etc. + if enabled_type[from] and enabled_type[to] then + local cell = explicit_casts[from][to] + local gen = gen_sql_cast_from_to(from, to) + local failures = {} + local successes = {} + local castable = false + local expected = explicit_casts[from][to] + if verbose > 0 then + print(expected, yaml.encode(gen)) + end + for i, v in pairs(gen) do + local ok, result + ok, result = catch_query(v) + if verbose > 0 then + print(string.format("ok = %s, result = %s, query = %s", + ok, result, v)) + + end + -- print(v, 'ok'..yaml.encode(ok), 'result'..yaml.encode(result)) + if expected == c_yes then + test:ok(true == ok, label_for(from, to, v)) + elseif expected == c_no then + test:ok(false == ok, label_for(from, to, v)) + else + -- we can't report immediately for c_maybe because some + -- cases allowed to fail, so postpone decision + if ok then + castable = true + table.insert(successes, {result, v}) + else + table.insert(failures, {result, v}) + end + end + end + + -- ok, we aggregated stats for c_maybe mode - check it now + if expected == c_maybe then + test:ok(castable, label_for(from, to, #gen and gen[1] or ''), + failures) + end + end + end +end + + +test:finish_test() -- 2.29.2