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 D5BE62A4D9 for ; Wed, 20 Mar 2019 07:12:10 -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 TQTomCEH1Y01 for ; Wed, 20 Mar 2019 07:12:10 -0400 (EDT) Received: from smtp45.i.mail.ru (smtp45.i.mail.ru [94.100.177.105]) (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 35CB4290A4 for ; Wed, 20 Mar 2019 07:12:10 -0400 (EDT) From: Ivan Koptelov Subject: [tarantool-patches] [PATCH 2/2] sql: rename instr to position & add collation usage Date: Wed, 20 Mar 2019 14:11:41 +0300 Message-Id: <2f6396ca10b94874467361e482555b225d5256de.1553078164.git.ivan.koptelov@tarantool.org> In-Reply-To: References: MIME-Version: 1.0 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: korablev@tarantool.org, Ivan Koptelov Before this patch we had instr() SQL function. After the patch it is renamed to position() for a better ANSI compatibility. Also a few things have been changed: arguments order, allowed arguments types and usage of collations. Note: after the patch position() syntax is still different from ANSI. The desirable syntax is POSITION(substring IN string). It is not possible to implement right now, because in our SQL grammar we lack expr types. We have only basic 'expr' type and it is not possible to write unambiguous rule for POSITION IN. To solve this we have to refactor grammar and add something like 'string_expr' (as it is done in other DBs grammars) Workaround for #3933 @TarantoolBot document Title: instr() is replaced with position() Name and order of the arguments has changed for a better ANSI compatibility: Before: instr(haystack, needle). After: position(needle, haystack). Type checking became more strict: before it was possible to call the function with INTEGER arguments or with arguments of different types. Now both arguments must have the same type and be either text or binary strings. Before the patch collations were not taken into consideration during the search. Now it is fixed, and both implicit (column) collations and explicit (using COLLATE expression) are used. Single collation which would be used in function is determined using ANSI “Type combination” rules. --- Before I changed postion() functionality, I refactored it: --- src/box/sql/func.c | 68 ++++++++++++++++++++++------------------------ 1 file changed, 33 insertions(+), 35 deletions(-) diff --git a/src/box/sql/func.c b/src/box/sql/func.c index 2de6ef5ce..0100bb22f 100644 --- a/src/box/sql/func.c +++ b/src/box/sql/func.c @@ -223,49 +223,46 @@ absFunc(sql_context * context, int argc, sql_value ** argv) * or 0 if needle never occurs in haystack. */ static void -instrFunc(sql_context * context, int argc, sql_value ** argv) +position_func(sql_context * context, int argc, sql_value ** argv) { - const unsigned char *zHaystack; - const unsigned char *zNeedle; - int nHaystack; - int nNeedle; - int typeHaystack, typeNeedle; - int N = 1; - int isText; - UNUSED_PARAMETER(argc); - typeHaystack = sql_value_type(argv[0]); - typeNeedle = sql_value_type(argv[1]); - if (typeHaystack == SQL_NULL || typeNeedle == SQL_NULL) + int type_haystack = sql_value_type(argv[0]); + int type_needle = sql_value_type(argv[1]); + if (type_haystack == SQL_NULL || type_needle == SQL_NULL) return; - nHaystack = sql_value_bytes(argv[0]); - nNeedle = sql_value_bytes(argv[1]); - if (nNeedle > 0) { - if (typeHaystack == SQL_BLOB && typeNeedle == SQL_BLOB) { - zHaystack = sql_value_blob(argv[0]); - zNeedle = sql_value_blob(argv[1]); - assert(zNeedle != 0); - assert(zHaystack != 0 || nHaystack == 0); - isText = 0; + + int n_haystack = sql_value_bytes(argv[0]); + int n_needle = sql_value_bytes(argv[1]); + const unsigned char *haystack; + const unsigned char *needle; + int position = 1; + int is_text; + if (n_needle > 0) { + if (type_haystack == SQL_BLOB && type_needle == SQL_BLOB) { + haystack = sql_value_blob(argv[0]); + needle = sql_value_blob(argv[1]); + assert(needle != 0); + assert(haystack != 0 || n_haystack == 0); + is_text = 0; } else { - zHaystack = sql_value_text(argv[0]); - zNeedle = sql_value_text(argv[1]); - isText = 1; - if (zHaystack == 0 || zNeedle == 0) + haystack = sql_value_text(argv[0]); + needle = sql_value_text(argv[1]); + is_text = 1; + if (haystack == 0 || needle == 0) return; } - while (nNeedle <= nHaystack - && memcmp(zHaystack, zNeedle, nNeedle) != 0) { - N++; + while (n_needle <= n_haystack + && memcmp(haystack, needle, n_needle) != 0) { + position++; do { - nHaystack--; - zHaystack++; - } while (isText && (zHaystack[0] & 0xc0) == 0x80); + n_haystack--; + haystack++; + } while (is_text && (haystack[0] & 0xc0) == 0x80); } - if (nNeedle > nHaystack) - N = 0; + if (n_needle > n_haystack) + position = 0; } - sql_result_int(context, N); + sql_result_int(context, position); } /* @@ -1760,7 +1757,8 @@ sqlRegisterBuiltinFunctions(void) FIELD_TYPE_STRING), FUNCTION2(length, 1, 0, 0, lengthFunc, SQL_FUNC_LENGTH, FIELD_TYPE_INTEGER), - FUNCTION(instr, 2, 0, 0, instrFunc, FIELD_TYPE_INTEGER), + FUNCTION2(position, 2, 0, 1, position_func, SQL_FUNC_NEEDCOLL, + FIELD_TYPE_INTEGER), FUNCTION(printf, -1, 0, 0, printfFunc, FIELD_TYPE_STRING), FUNCTION(unicode, 1, 0, 0, unicodeFunc, FIELD_TYPE_STRING), FUNCTION(char, -1, 0, 0, charFunc, FIELD_TYPE_STRING), -- src/box/sql/expr.c | 8 +- src/box/sql/func.c | 157 ++++-- src/box/sql/vdbeInt.h | 9 + test/sql-tap/func.test.lua | 10 +- test/sql-tap/func5.test.lua | 4 +- test/sql-tap/instr.test.lua | 706 ------------------------- test/sql-tap/position.test.lua | 905 +++++++++++++++++++++++++++++++++ test/sql-tap/with1.test.lua | 4 +- 8 files changed, 1042 insertions(+), 761 deletions(-) delete mode 100755 test/sql-tap/instr.test.lua create mode 100755 test/sql-tap/position.test.lua diff --git a/src/box/sql/expr.c b/src/box/sql/expr.c index 2f48d90c6..53926e3f2 100644 --- a/src/box/sql/expr.c +++ b/src/box/sql/expr.c @@ -4137,9 +4137,7 @@ sqlExprCodeTarget(Parse * pParse, Expr * pExpr, int target) lhs_id, is_lhs_forced, rhs_id, is_rhs_forced, &temp_id) != 0) { - pParse->rc = - SQL_TARANTOOL_ERROR; - pParse->nErr++; + pParse->is_aborted = true; return 0; } @@ -4152,9 +4150,7 @@ sqlExprCodeTarget(Parse * pParse, Expr * pExpr, int target) curr_id, is_curr_forced, temp_id, is_temp_forced, &curr_id) != 0) { - pParse->rc = - SQL_TARANTOOL_ERROR; - pParse->nErr++; + pParse->is_aborted = true; return 0; } } diff --git a/src/box/sql/func.c b/src/box/sql/func.c index a750e52a1..6654d4998 100644 --- a/src/box/sql/func.c +++ b/src/box/sql/func.c @@ -38,6 +38,7 @@ #include "vdbeInt.h" #include "version.h" #include "coll/coll.h" +#include "tarantoolInt.h" #include #include #include @@ -212,9 +213,9 @@ absFunc(sql_context * context, int argc, sql_value ** argv) } /* - * Implementation of the instr() function. + * Implementation of the position() function. * - * instr(haystack,needle) finds the first occurrence of needle + * position(needle, haystack) finds the first occurrence of needle * in haystack and returns the number of previous characters plus 1, * or 0 if needle does not occur within haystack. * @@ -223,49 +224,124 @@ absFunc(sql_context * context, int argc, sql_value ** argv) * or 0 if needle never occurs in haystack. */ static void -instrFunc(sql_context * context, int argc, sql_value ** argv) +position_func(sql_context * context, int argc, sql_value ** argv) { - const unsigned char *zHaystack; - const unsigned char *zNeedle; - int nHaystack; - int nNeedle; - int typeHaystack, typeNeedle; - int N = 1; - int isText; - UNUSED_PARAMETER(argc); - typeHaystack = sql_value_type(argv[0]); - typeNeedle = sql_value_type(argv[1]); - if (typeHaystack == SQL_NULL || typeNeedle == SQL_NULL) + sql_value *needle = argv[0]; + sql_value *haystack = argv[1]; + int type_needle = sql_value_type(needle); + int type_haystack = sql_value_type(haystack); + + if (type_haystack == SQL_NULL || type_needle == SQL_NULL) + return; + /* + * Position function can be called only with string + * or blob params. + */ + sql_value *inconsistent_type_arg = NULL; + if (type_needle != SQL_TEXT && type_needle != SQL_BLOB) + inconsistent_type_arg = needle; + if (type_haystack != SQL_TEXT && type_haystack != SQL_BLOB) + inconsistent_type_arg = haystack; + if (inconsistent_type_arg != NULL) { + diag_set(ClientError, ER_INCONSISTENT_TYPES, "TEXT or BLOB", + mem_type_to_str(inconsistent_type_arg)); + context->pVdbe->pParse->is_aborted = true; + sql_result_error(context, tarantoolErrorMessage(), -1); + return; + } + /* + * Both params of Position function must be of the same + * type. + */ + if (type_haystack != type_needle) { + diag_set(ClientError, ER_INCONSISTENT_TYPES, + mem_type_to_str(needle), + mem_type_to_str(haystack)); + context->pVdbe->pParse->is_aborted = true; + sql_result_error(context, tarantoolErrorMessage(), -1); return; - nHaystack = sql_value_bytes(argv[0]); - nNeedle = sql_value_bytes(argv[1]); - if (nNeedle > 0) { - if (typeHaystack == SQL_BLOB && typeNeedle == SQL_BLOB) { - zHaystack = sql_value_blob(argv[0]); - zNeedle = sql_value_blob(argv[1]); - assert(zNeedle != 0); - assert(zHaystack != 0 || nHaystack == 0); - isText = 0; + } + + int n_needle_bytes = sql_value_bytes(needle); + int n_haystack_bytes = sql_value_bytes(haystack); + int position = 1; + if (n_needle_bytes > 0) { + const unsigned char *haystack_str; + const unsigned char *needle_str; + if (type_haystack == SQL_BLOB) { + needle_str = sql_value_blob(needle); + haystack_str = sql_value_blob(haystack); + assert(needle_str != NULL); + assert(haystack_str != NULL || n_haystack_bytes == 0); + + while (n_needle_bytes <= n_haystack_bytes + && memcmp(haystack_str, needle_str, n_needle_bytes) != 0) { + position++; + n_haystack_bytes--; + haystack_str++; + } + if (n_needle_bytes > n_haystack_bytes) + position = 0; } else { - zHaystack = sql_value_text(argv[0]); - zNeedle = sql_value_text(argv[1]); - isText = 1; - if (zHaystack == 0 || zNeedle == 0) - return; - } - while (nNeedle <= nHaystack - && memcmp(zHaystack, zNeedle, nNeedle) != 0) { - N++; - do { - nHaystack--; - zHaystack++; - } while (isText && (zHaystack[0] & 0xc0) == 0x80); + /* + * Code below handles not only simple + * cases like position('a', 'bca'), but + * also more complex ones: + * position('a', 'bcá' COLLATE "unicode_ci") + * To do so, we need to use comparison + * window, which has constant character + * size, but variable byte size. + * Character size is equal to + * needle char size. + */ + haystack_str = sql_value_text(haystack); + needle_str = sql_value_text(needle); + + int n_needle_chars = + sql_utf8_char_count(needle_str, n_needle_bytes); + int n_haystack_chars = + sql_utf8_char_count(haystack_str, n_haystack_bytes); + + if (n_haystack_chars < n_needle_chars) { + position = 0; + goto finish; + } + /* + * Comparison window is determined by + * beg_offset and end_offset. beg_offset + * is offset in bytes from haystack + * beginning to window beginning. + * end_offset is offset in bytes from + * haystack beginning to window end. + */ + int end_offset = 0; + for (int c = 0; c < n_needle_chars; c++) { + SQL_UTF8_FWD_1(haystack_str, end_offset, + n_haystack_bytes); + } + int beg_offset = 0; + struct coll *coll = sqlGetFuncCollSeq(context); + int c; + for (c = 0; c + n_needle_chars <= n_haystack_chars; c++) { + if (coll->cmp((const char *) haystack_str + beg_offset, + end_offset - beg_offset, + (const char *) needle_str, + n_needle_bytes, coll) == 0) + goto finish; + position++; + /* Update offsets. */ + SQL_UTF8_FWD_1(haystack_str, beg_offset, + n_haystack_bytes); + SQL_UTF8_FWD_1(haystack_str, end_offset, + n_haystack_bytes); + } + /* Needle was not found in the haystack. */ + position = 0; } - if (nNeedle > nHaystack) - N = 0; } - sql_result_int(context, N); +finish: + sql_result_int(context, position); } /* @@ -1756,7 +1832,8 @@ sqlRegisterBuiltinFunctions(void) FIELD_TYPE_STRING), FUNCTION2(length, 1, 0, 0, lengthFunc, SQL_FUNC_LENGTH, FIELD_TYPE_INTEGER), - FUNCTION(instr, 2, 0, 0, instrFunc, FIELD_TYPE_INTEGER), + FUNCTION2(position, 2, 0, 1, position_func, SQL_FUNC_NEEDCOLL, + FIELD_TYPE_INTEGER), FUNCTION(printf, -1, 0, 0, printfFunc, FIELD_TYPE_STRING), FUNCTION(unicode, 1, 0, 0, unicodeFunc, FIELD_TYPE_STRING), FUNCTION(char, -1, 0, 0, charFunc, FIELD_TYPE_STRING), diff --git a/src/box/sql/vdbeInt.h b/src/box/sql/vdbeInt.h index c84f22caf..8af4fa359 100644 --- a/src/box/sql/vdbeInt.h +++ b/src/box/sql/vdbeInt.h @@ -607,4 +607,13 @@ char * sql_vdbe_mem_encode_tuple(struct Mem *fields, uint32_t field_count, uint32_t *tuple_size, struct region *region); +/** + * Simple type to str convertor. It is used to simplify + * error reporting. + * @param mem Memory field which type we want to get. + * @retval String representing name of the type. + */ +char * +mem_type_to_str(const struct Mem *p); + #endif /* !defined(SQL_VDBEINT_H) */ diff --git a/test/sql-tap/func.test.lua b/test/sql-tap/func.test.lua index 889fc5867..ddfd79163 100755 --- a/test/sql-tap/func.test.lua +++ b/test/sql-tap/func.test.lua @@ -2860,25 +2860,25 @@ test:do_execsql_test( SELECT GROUP_CONCAT(b, '') FROM t100;", {string.char(00,65,00,65,00)}) --- INSTR +-- POSITION test:do_execsql_test( "func-73", - "SELECT INSTR(CHAR(00,65,00,66,00), CHAR(65));", + "SELECT POSITION(CHAR(65), CHAR(00,65,00,66,00));", {2}) test:do_execsql_test( "func-74", - "SELECT INSTR(CHAR(00,65,00,66,00), CHAR(66));", + "SELECT POSITION(CHAR(66), CHAR(00,65,00,66,00));", {4}) test:do_execsql_test( "func-75", - "SELECT INSTR(CHAR(00,65,00,66,00), CHAR(00));", + "SELECT POSITION(CHAR(00), CHAR(00,65,00,66,00));", {1}) test:do_execsql_test( "func-76", - "SELECT INSTR(CHAR(65,66), CHAR(00));", + "SELECT POSITION(CHAR(00), CHAR(65,66));", {0}) test:finish_test() diff --git a/test/sql-tap/func5.test.lua b/test/sql-tap/func5.test.lua index bb9e11a04..6605a2ba1 100755 --- a/test/sql-tap/func5.test.lua +++ b/test/sql-tap/func5.test.lua @@ -30,7 +30,7 @@ test:do_execsql_test( INSERT INTO t1 VALUES(3,'pqr','fuzzy',99); INSERT INTO t1 VALUES(4,'abcdefg','xy',22); INSERT INTO t1 VALUES(5,'shoe','mayer',2953); - SELECT x FROM t1 WHERE c=instr('abcdefg',b) OR a='abcdefg' ORDER BY +x; + SELECT x FROM t1 WHERE c=position(b, 'abcdefg') OR a='abcdefg' ORDER BY +x; ]], { -- 2, 4 @@ -40,7 +40,7 @@ test:do_execsql_test( test:do_execsql_test( "func5-1.2", [[ - SELECT x FROM t1 WHERE a='abcdefg' OR c=instr('abcdefg',b) ORDER BY +x; + SELECT x FROM t1 WHERE a='abcdefg' OR c=position(b, 'abcdefg') ORDER BY +x; ]], { -- 2, 4 diff --git a/test/sql-tap/instr.test.lua b/test/sql-tap/instr.test.lua deleted file mode 100755 index d42da5ccd..000000000 --- a/test/sql-tap/instr.test.lua +++ /dev/null @@ -1,706 +0,0 @@ -#!/usr/bin/env tarantool -test = require("sqltester") -test:plan(65) - ---!./tcltestrunner.lua --- 2012 October 24 --- --- The author disclaims copyright to this source code. In place of --- a legal notice, here is a blessing: --- --- May you do good and not evil. --- May you find forgiveness for yourself and forgive others. --- May you share freely, never taking more than you give. --- -------------------------------------------------------------------------- --- This file implements regression tests for sql library. The --- focus of this file is testing the built-in INSTR() functions. --- --- EVIDENCE-OF: R-27549-59611 The instr(X,Y) function finds the first --- occurrence of string Y within string X and returns the number of prior --- characters plus 1, or 0 if Y is nowhere found within X. --- --- ["set","testdir",[["file","dirname",["argv0"]]]] --- ["source",[["testdir"],"\/tester.tcl"]] --- Create a table to work with. --- -test:do_test( - "instr-1.1", - function() - return test:execsql "SELECT instr('abcdefg','a');" - end, { - -- - 1 - -- - }) - -test:do_test( - "instr-1.2", - function() - return test:execsql "SELECT instr('abcdefg','b');" - end, { - -- - 2 - -- - }) - -test:do_test( - "instr-1.3", - function() - return test:execsql "SELECT instr('abcdefg','c');" - end, { - -- - 3 - -- - }) - -test:do_test( - "instr-1.4", - function() - return test:execsql "SELECT instr('abcdefg','d');" - end, { - -- - 4 - -- - }) - -test:do_test( - "instr-1.5", - function() - return test:execsql "SELECT instr('abcdefg','e');" - end, { - -- - 5 - -- - }) - -test:do_test( - "instr-1.6", - function() - return test:execsql "SELECT instr('abcdefg','f');" - end, { - -- - 6 - -- - }) - -test:do_test( - "instr-1.7", - function() - return test:execsql "SELECT instr('abcdefg','g');" - end, { - -- - 7 - -- - }) - -test:do_test( - "instr-1.8", - function() - return test:execsql "SELECT instr('abcdefg','h');" - end, { - -- - 0 - -- - }) - -test:do_test( - "instr-1.9", - function() - return test:execsql "SELECT instr('abcdefg','abcdefg');" - end, { - -- - 1 - -- - }) - -test:do_test( - "instr-1.10", - function() - return test:execsql "SELECT instr('abcdefg','abcdefgh');" - end, { - -- - 0 - -- - }) - -test:do_test( - "instr-1.11", - function() - return test:execsql "SELECT instr('abcdefg','bcdefg');" - end, { - -- - 2 - -- - }) - -test:do_test( - "instr-1.12", - function() - return test:execsql "SELECT instr('abcdefg','bcdefgh');" - end, { - -- - 0 - -- - }) - -test:do_test( - "instr-1.13", - function() - return test:execsql "SELECT instr('abcdefg','cdefg');" - end, { - -- - 3 - -- - }) - -test:do_test( - "instr-1.14", - function() - return test:execsql "SELECT instr('abcdefg','cdefgh');" - end, { - -- - 0 - -- - }) - -test:do_test( - "instr-1.15", - function() - return test:execsql "SELECT instr('abcdefg','defg');" - end, { - -- - 4 - -- - }) - -test:do_test( - "instr-1.16", - function() - return test:execsql "SELECT instr('abcdefg','defgh');" - end, { - -- - 0 - -- - }) - -test:do_test( - "instr-1.17", - function() - return test:execsql "SELECT instr('abcdefg','efg');" - end, { - -- - 5 - -- - }) - -test:do_test( - "instr-1.18", - function() - return test:execsql "SELECT instr('abcdefg','efgh');" - end, { - -- - 0 - -- - }) - -test:do_test( - "instr-1.19", - function() - return test:execsql "SELECT instr('abcdefg','fg');" - end, { - -- - 6 - -- - }) - -test:do_test( - "instr-1.20", - function() - return test:execsql "SELECT instr('abcdefg','fgh');" - end, { - -- - 0 - -- - }) - -test:do_test( - "instr-1.21", - function() - return test:execsql "SELECT coalesce(instr('abcdefg',NULL),'nil');" - end, { - -- - "nil" - -- - }) - -test:do_test( - "instr-1.22", - function() - return test:execsql "SELECT coalesce(instr(NULL,'x'),'nil');" - end, { - -- - "nil" - -- - }) - -test:do_test( - "instr-1.23", - function() - return test:execsql "SELECT instr(12345,34);" - end, { - -- - 3 - -- - }) - -test:do_test( - "instr-1.24", - function() - return test:execsql "SELECT instr(123456.78,34);" - end, { - -- - 3 - -- - }) - -test:do_test( - "instr-1.25", - function() - return test:execsql "SELECT instr(123456.78,x'3334');" - end, { - -- - 3 - -- - }) - -test:do_test( - "instr-1.26", - function() - return test:execsql "SELECT instr('äbcdefg','efg');" - end, { - -- - 5 - -- - }) - -test:do_test( - "instr-1.27", - function() - return test:execsql "SELECT instr('€xyzzy','xyz');" - end, { - -- - 2 - -- - }) - -test:do_test( - "instr-1.28", - function() - return test:execsql "SELECT instr('abc€xyzzy','xyz');" - end, { - -- - 5 - -- - }) - -test:do_test( - "instr-1.29", - function() - return test:execsql "SELECT instr('abc€xyzzy','€xyz');" - end, { - -- - 4 - -- - }) - -test:do_test( - "instr-1.30", - function() - return test:execsql "SELECT instr('abc€xyzzy','c€xyz');" - end, { - -- - 3 - -- - }) - -test:do_test( - "instr-1.31", - function() - return test:execsql "SELECT instr(x'0102030405',x'01');" - end, { - -- - 1 - -- - }) - -test:do_test( - "instr-1.32", - function() - return test:execsql "SELECT instr(x'0102030405',x'02');" - end, { - -- - 2 - -- - }) - -test:do_test( - "instr-1.33", - function() - return test:execsql "SELECT instr(x'0102030405',x'03');" - end, { - -- - 3 - -- - }) - -test:do_test( - "instr-1.34", - function() - return test:execsql "SELECT instr(x'0102030405',x'04');" - end, { - -- - 4 - -- - }) - -test:do_test( - "instr-1.35", - function() - return test:execsql "SELECT instr(x'0102030405',x'05');" - end, { - -- - 5 - -- - }) - -test:do_test( - "instr-1.36", - function() - return test:execsql "SELECT instr(x'0102030405',x'06');" - end, { - -- - 0 - -- - }) - -test:do_test( - "instr-1.37", - function() - return test:execsql "SELECT instr(x'0102030405',x'0102030405');" - end, { - -- - 1 - -- - }) - -test:do_test( - "instr-1.38", - function() - return test:execsql "SELECT instr(x'0102030405',x'02030405');" - end, { - -- - 2 - -- - }) - -test:do_test( - "instr-1.39", - function() - return test:execsql "SELECT instr(x'0102030405',x'030405');" - end, { - -- - 3 - -- - }) - -test:do_test( - "instr-1.40", - function() - return test:execsql "SELECT instr(x'0102030405',x'0405');" - end, { - -- - 4 - -- - }) - -test:do_test( - "instr-1.41", - function() - return test:execsql "SELECT instr(x'0102030405',x'0506');" - end, { - -- - 0 - -- - }) - -test:do_test( - "instr-1.42", - function() - return test:execsql "SELECT instr(x'0102030405',x'');" - end, { - -- - 1 - -- - }) - -test:do_test( - "instr-1.43", - function() - return test:execsql "SELECT instr(x'',x'');" - end, { - -- - 1 - -- - }) - -test:do_test( - "instr-1.44", - function() - return test:execsql "SELECT instr('','');" - end, { - -- - 1 - -- - }) - -test:do_test( - "instr-1.45", - function() - return test:execsql "SELECT instr('abcdefg','');" - end, { - -- - 1 - -- - }) - --- ["unset","-nocomplain","longstr"] -local longstr = "abcdefghijklmonpqrstuvwxyz" -longstr = longstr .. longstr -longstr = longstr .. longstr -longstr = longstr .. longstr -longstr = longstr .. longstr -longstr = longstr .. longstr -longstr = longstr .. longstr -longstr = longstr .. longstr -longstr = longstr .. longstr -longstr = longstr .. longstr -longstr = longstr .. longstr -longstr = longstr .. longstr -longstr = longstr .. longstr --- puts [string length '"..longstr.."'] -longstr = longstr .. "Xabcde" -test:do_test( - "instr-1.46", - function() - return test:execsql("SELECT instr('"..longstr.."','X');") - end, { - -- - 106497 - -- - }) - -test:do_test( - "instr-1.47", - function() - return test:execsql("SELECT instr('"..longstr.."','Y');") - end, { - -- - 0 - -- - }) - -test:do_test( - "instr-1.48", - function() - return test:execsql( "SELECT instr('"..longstr.."','Xa');") - end, { - -- - 106497 - -- - }) - -test:do_test( - "instr-1.49", - function() - return test:execsql("SELECT instr('"..longstr.."','zXa');") - end, { - -- - 106496 - -- - }) - -longstr = string.gsub(longstr, "a", "ä") -test:do_test( - "instr-1.50", - function() - return test:execsql("SELECT instr('"..longstr.."','X');") - end, { - -- - 106497 - -- - }) - -test:do_test( - "instr-1.51", - function() - return test:execsql("SELECT instr('"..longstr.."','Y');") - end, { - -- - 0 - -- - }) - -test:do_test( - "instr-1.52", - function() - return test:execsql("SELECT instr('"..longstr.."','Xä');") - end, { - -- - 106497 - -- - }) - -test:do_test( - "instr-1.53", - function() - return test:execsql("SELECT instr('"..longstr.."','zXä');") - end, { - -- - 106496 - -- - }) - -test:do_test( - "instr-1.54", - function() - return test:execsql("SELECT instr(x'78c3a4e282ac79','x');") - end, { - -- - 1 - -- - }) - -test:do_test( - "instr-1.55", - function() - return test:execsql "SELECT instr(x'78c3a4e282ac79','y');" - end, { - -- - 4 - -- - }) - --- EVIDENCE-OF: R-46421-32541 Or, if X and Y are both BLOBs, then --- instr(X,Y) returns one more than the number bytes prior to the first --- occurrence of Y, or 0 if Y does not occur anywhere within X. --- -test:do_test( - "instr-1.56.1", - function() - return test:execsql "SELECT instr(x'78c3a4e282ac79',x'79');" - end, { - -- - 7 - -- - }) - -test:do_test( - "instr-1.56.2", - function() - return test:execsql "SELECT instr(x'78c3a4e282ac79',x'7a');" - end, { - -- - 0 - -- - }) - -test:do_test( - "instr-1.56.3", - function() - return test:execsql "SELECT instr(x'78c3a4e282ac79',x'78');" - end, { - -- - 1 - -- - }) - -test:do_test( - "instr-1.56.3", - function() - return test:execsql "SELECT instr(x'78c3a4e282ac79',x'a4');" - end, { - -- - 3 - -- - }) - --- EVIDENCE-OF: R-17329-35644 If both arguments X and Y to instr(X,Y) are --- non-NULL and are not BLOBs then both are interpreted as strings. --- -test:do_test( - "instr-1.57.1", - function() - return test:execsql "SELECT instr('xä€y',x'79');" - end, { - -- - 4 - -- - }) - -test:do_test( - "instr-1.57.2", - function() - return test:execsql "SELECT instr('xä€y',x'a4');" - end, { - -- - 0 - -- - }) - -test:do_test( - "instr-1.57.3", - function() - return test:execsql "SELECT instr(x'78c3a4e282ac79','y');" - end, { - -- - 4 - -- - }) - --- EVIDENCE-OF: R-14708-27487 If either X or Y are NULL in instr(X,Y) --- then the result is NULL. --- -test:do_execsql_test( - "instr-1.60", - [[ - SELECT coalesce(instr(NULL,'abc'), 999); - ]], { - -- - 999 - -- - }) - -test:do_execsql_test( - "instr-1.61", - [[ - SELECT coalesce(instr('abc',NULL), 999); - ]], { - -- - 999 - -- - }) - -test:do_execsql_test( - "instr-1.62", - [[ - SELECT coalesce(instr(NULL,NULL), 999); - ]], { - -- - 999 - -- - }) - - - -test:finish_test() diff --git a/test/sql-tap/position.test.lua b/test/sql-tap/position.test.lua new file mode 100755 index 000000000..70d11444c --- /dev/null +++ b/test/sql-tap/position.test.lua @@ -0,0 +1,905 @@ +#!/usr/bin/env tarantool +test = require("sqltester") +test:plan(80) + +--!./tcltestrunner.lua +-- 2012 October 24 +-- +-- The author disclaims copyright to this source code. In place of +-- a legal notice, here is a blessing: +-- +-- May you do good and not evil. +-- May you find forgiveness for yourself and forgive others. +-- May you share freely, never taking more than you give. +-- +------------------------------------------------------------------------- +-- This file implements regression tests for sql library. The +-- focus of this file is testing the built-in POSITION() functions. +-- +-- EVIDENCE-OF: R-27549-59611 The position(X,Y) function finds the first +-- occurrence of string X within string Y and returns the number of prior +-- characters plus 1, or 0 if X is nowhere found within Y. +-- +-- ["set","testdir",[["file","dirname",["argv0"]]]] +-- ["source",[["testdir"],"\/tester.tcl"]] +-- Create a table to work with. +-- +test:do_test( + "position-1.1", + function() + return test:execsql "SELECT position('a', 'abcdefg');" + end, { + -- + 1 + -- + }) + +test:do_test( + "position-1.2", + function() + return test:execsql "SELECT position('b', 'abcdefg');" + end, { + -- + 2 + -- + }) + +test:do_test( + "position-1.3", + function() + return test:execsql "SELECT position('c', 'abcdefg');" + end, { + -- + 3 + -- + }) + +test:do_test( + "position-1.4", + function() + return test:execsql "SELECT position('d', 'abcdefg');" + end, { + -- + 4 + -- + }) + +test:do_test( + "position-1.5", + function() + return test:execsql "SELECT position('e', 'abcdefg');" + end, { + -- + 5 + -- + }) + +test:do_test( + "position-1.6", + function() + return test:execsql "SELECT position('f', 'abcdefg');" + end, { + -- + 6 + -- + }) + +test:do_test( + "position-1.7", + function() + return test:execsql "SELECT position('g', 'abcdefg');" + end, { + -- + 7 + -- + }) + +test:do_test( + "position-1.8", + function() + return test:execsql "SELECT position('h', 'abcdefg');" + end, { + -- + 0 + -- + }) + +test:do_test( + "position-1.9", + function() + return test:execsql "SELECT position('abcdefg', 'abcdefg');" + end, { + -- + 1 + -- + }) + +test:do_test( + "position-1.10", + function() + return test:execsql "SELECT position('abcdefgh', 'abcdefg');" + end, { + -- + 0 + -- + }) + +test:do_test( + "position-1.11", + function() + return test:execsql "SELECT position('bcdefg', 'abcdefg');" + end, { + -- + 2 + -- + }) + +test:do_test( + "position-1.12", + function() + return test:execsql "SELECT position('bcdefgh', 'abcdefg');" + end, { + -- + 0 + -- + }) + +test:do_test( + "position-1.13", + function() + return test:execsql "SELECT position('cdefg', 'abcdefg');" + end, { + -- + 3 + -- + }) + +test:do_test( + "position-1.14", + function() + return test:execsql "SELECT position('cdefgh', 'abcdefg');" + end, { + -- + 0 + -- + }) + +test:do_test( + "position-1.15", + function() + return test:execsql "SELECT position('defg', 'abcdefg');" + end, { + -- + 4 + -- + }) + +test:do_test( + "position-1.16", + function() + return test:execsql "SELECT position('defgh', 'abcdefg');" + end, { + -- + 0 + -- + }) + +test:do_test( + "position-1.17", + function() + return test:execsql "SELECT position('efg', 'abcdefg');" + end, { + -- + 5 + -- + }) + +test:do_test( + "position-1.18", + function() + return test:execsql "SELECT position('efgh', 'abcdefg');" + end, { + -- + 0 + -- + }) + +test:do_test( + "position-1.19", + function() + return test:execsql "SELECT position('fg', 'abcdefg');" + end, { + -- + 6 + -- + }) + +test:do_test( + "position-1.20", + function() + return test:execsql "SELECT position('fgh', 'abcdefg');" + end, { + -- + 0 + -- + }) + +test:do_test( + "position-1.21", + function() + return test:execsql "SELECT coalesce(position(NULL, 'abcdefg'), 'nil');" + end, { + -- + "nil" + -- + }) + +test:do_test( + "position-1.22", + function() + return test:execsql "SELECT coalesce(position('x', NULL), 'nil');" + end, { + -- + "nil" + -- + }) + +test:do_test( + "position-1.23", + function() + return test:catchsql "SELECT position(34, 12345);" + end, { + -- + 1, "Inconsistent types: expected TEXT or BLOB got INTEGER" + -- + }) + +test:do_test( + "position-1.24", + function() + return test:catchsql "SELECT position(34, 123456.78);" + end, { + -- + 1, "Inconsistent types: expected TEXT or BLOB got REAL" + -- + }) + +test:do_test( + "position-1.25", + function() + return test:catchsql "SELECT position(x'3334', 123456.78);" + end, { + -- + 1, "Inconsistent types: expected TEXT or BLOB got REAL" + -- + }) + +test:do_test( + "position-1.26", + function() + return test:execsql "SELECT position('efg', 'äbcdefg');" + end, { + -- + 5 + -- + }) + +test:do_test( + "position-1.27", + function() + return test:execsql "SELECT position('xyz', '€xyzzy');" + end, { + -- + 2 + -- + }) + +test:do_test( + "position-1.28", + function() + return test:execsql "SELECT position('xyz', 'abc€xyzzy');" + end, { + -- + 5 + -- + }) + +test:do_test( + "position-1.29", + function() + return test:execsql "SELECT position('€xyz', 'abc€xyzzy');" + end, { + -- + 4 + -- + }) + +test:do_test( + "position-1.30", + function() + return test:execsql "SELECT position('c€xyz', 'abc€xyzzy');" + end, { + -- + 3 + -- + }) + +test:do_test( + "position-1.31", + function() + return test:execsql "SELECT position(x'01', x'0102030405');" + end, { + -- + 1 + -- + }) + +test:do_test( + "position-1.32", + function() + return test:execsql "SELECT position(x'02', x'0102030405');" + end, { + -- + 2 + -- + }) + +test:do_test( + "position-1.33", + function() + return test:execsql "SELECT position(x'03', x'0102030405');" + end, { + -- + 3 + -- + }) + +test:do_test( + "position-1.34", + function() + return test:execsql "SELECT position(x'04', x'0102030405');" + end, { + -- + 4 + -- + }) + +test:do_test( + "position-1.35", + function() + return test:execsql "SELECT position(x'05', x'0102030405');" + end, { + -- + 5 + -- + }) + +test:do_test( + "position-1.36", + function() + return test:execsql "SELECT position(x'06', x'0102030405');" + end, { + -- + 0 + -- + }) + +test:do_test( + "position-1.37", + function() + return test:execsql "SELECT position(x'0102030405', x'0102030405');" + end, { + -- + 1 + -- + }) + +test:do_test( + "position-1.38", + function() + return test:execsql "SELECT position(x'02030405', x'0102030405');" + end, { + -- + 2 + -- + }) + +test:do_test( + "position-1.39", + function() + return test:execsql "SELECT position(x'030405', x'0102030405');" + end, { + -- + 3 + -- + }) + +test:do_test( + "position-1.40", + function() + return test:execsql "SELECT position(x'0405', x'0102030405');" + end, { + -- + 4 + -- + }) + +test:do_test( + "position-1.41", + function() + return test:execsql "SELECT position(x'0506', x'0102030405');" + end, { + -- + 0 + -- + }) + +test:do_test( + "position-1.42", + function() + return test:execsql "SELECT position(x'', x'0102030405');" + end, { + -- + 1 + -- + }) + +test:do_test( + "position-1.43", + function() + return test:execsql "SELECT position(x'', x'');" + end, { + -- + 1 + -- + }) + +test:do_test( + "position-1.44", + function() + return test:execsql "SELECT position('', '');" + end, { + -- + 1 + -- + }) + +test:do_test( + "position-1.45", + function() + return test:execsql "SELECT position('', 'abcdefg');" + end, { + -- + 1 + -- + }) + +-- ["unset","-nocomplain","longstr"] +local longstr = "abcdefghijklmonpqrstuvwxyz" +longstr = longstr .. longstr +longstr = longstr .. longstr +longstr = longstr .. longstr +longstr = longstr .. longstr +longstr = longstr .. longstr +longstr = longstr .. longstr +longstr = longstr .. longstr +longstr = longstr .. longstr +longstr = longstr .. longstr +longstr = longstr .. longstr +longstr = longstr .. longstr +longstr = longstr .. longstr +-- puts [string length '"..longstr.."'] +longstr = longstr .. "Xabcde" +test:do_test( + "position-1.46", + function() + return test:execsql("SELECT position('X', '"..longstr.."');") + end, { + -- + 106497 + -- + }) + +test:do_test( + "position-1.47", + function() + return test:execsql("SELECT position('Y', '"..longstr.."');") + end, { + -- + 0 + -- + }) + +test:do_test( + "position-1.48", + function() + return test:execsql( "SELECT position('Xa', '"..longstr.."');") + end, { + -- + 106497 + -- + }) + +test:do_test( + "position-1.49", + function() + return test:execsql("SELECT position('zXa', '"..longstr.."');") + end, { + -- + 106496 + -- + }) + +longstr = string.gsub(longstr, "a", "ä") +test:do_test( + "position-1.50", + function() + return test:execsql("SELECT position('X', '"..longstr.."');") + end, { + -- + 106497 + -- + }) + +test:do_test( + "position-1.51", + function() + return test:execsql("SELECT position('Y', '"..longstr.."');") + end, { + -- + 0 + -- + }) + +test:do_test( + "position-1.52", + function() + return test:execsql("SELECT position('Xä', '"..longstr.."');") + end, { + -- + 106497 + -- + }) + +test:do_test( + "position-1.53", + function() + return test:execsql("SELECT position('zXä', '"..longstr.."');") + end, { + -- + 106496 + -- + }) + +test:do_test( + "position-1.54", + function() + return test:catchsql("SELECT position('x', x'78c3a4e282ac79');") + end, { + -- + 1, "Inconsistent types: expected TEXT got BLOB" + -- + }) + +test:do_test( + "position-1.55", + function() + return test:catchsql "SELECT position('y', x'78c3a4e282ac79');" + end, { + -- + 1, "Inconsistent types: expected TEXT got BLOB" + -- + }) + +-- EVIDENCE-OF: R-46421-32541 Or, if X and Y are both BLOBs, then +-- position(X,Y) returns one more than the number bytes prior to the first +-- occurrence of X, or 0 if X does not occur anywhere within Y. +-- +test:do_test( + "position-1.56.1", + function() + return test:execsql "SELECT position(x'79', x'78c3a4e282ac79');" + end, { + -- + 7 + -- + }) + +test:do_test( + "position-1.56.2", + function() + return test:execsql "SELECT position(x'7a', x'78c3a4e282ac79');" + end, { + -- + 0 + -- + }) + +test:do_test( + "position-1.56.3", + function() + return test:execsql "SELECT position(x'78', x'78c3a4e282ac79');" + end, { + -- + 1 + -- + }) + +test:do_test( + "position-1.56.3", + function() + return test:execsql "SELECT position(x'a4', x'78c3a4e282ac79');" + end, { + -- + 3 + -- + }) + +-- EVIDENCE-OF: R-17329-35644 If both arguments X and Y to position(X,Y) are +-- non-NULL and are not BLOBs then both are interpreted as strings. +-- +test:do_test( + "position-1.57.1", + function() + return test:catchsql "SELECT position(x'79', 'xä€y');" + end, { + -- + 1, "Inconsistent types: expected BLOB got TEXT" + -- + }) + +test:do_test( + "position-1.57.2", + function() + return test:catchsql "SELECT position(x'a4', 'xä€y');" + end, { + -- + 1, "Inconsistent types: expected BLOB got TEXT" + -- + }) + +test:do_test( + "position-1.57.3", + function() + return test:catchsql "SELECT position('y', x'78c3a4e282ac79');" + end, { + -- + 1, "Inconsistent types: expected TEXT got BLOB" + -- + }) + +-- EVIDENCE-OF: R-14708-27487 If either X or Y are NULL in position(X,Y) +-- then the result is NULL. +-- +test:do_execsql_test( + "position-1.60", + [[ + SELECT coalesce(position(NULL, 'abc'), 999); + ]], { + -- + 999 + -- + }) + +test:do_execsql_test( + "position-1.61", + [[ + SELECT coalesce(position('abc', NULL), 999); + ]], { + -- + 999 + -- + }) + +test:do_execsql_test( + "position-1.62", + [[ + SELECT coalesce(position(NULL, NULL), 999); + ]], { + -- + 999 + -- + }) + +-- Tests that make use of collations. +-- A short remainder of how "unicode" and "unicode_ci" collations +-- works: +-- unicode_ci: „a“ = „A“ = „á“ = „Á“. +-- unicode: all symbols above are pairwise unequal. + +-- Collation is set in space format. + +test:do_execsql_test( + "position-1.63", + [[ + CREATE TABLE test1 (s1 VARCHAR(5) PRIMARY KEY COLLATE "unicode_ci"); + INSERT INTO test1 VALUES('à'); + SELECT POSITION('a', s1) FROM test1; + DROP TABLE test1; + ]], { + 1 + } +) + +test:do_execsql_test( + "position-1.64", + [[ + CREATE TABLE test1 (s1 VARCHAR(5) PRIMARY KEY COLLATE "unicode_ci"); + INSERT INTO test1 VALUES('qwèrty'); + SELECT POSITION('er', s1) FROM test1; + DROP TABLE test1; + ]], { + 3 + } +) + +test:do_execsql_test( + "position-1.65", + [[ + CREATE TABLE test1 (s1 VARCHAR(5) PRIMARY KEY COLLATE "unicode_ci"); + INSERT INTO test1 VALUES('qwèrtÿ'); + SELECT POSITION('y', s1) FROM test1; + DROP TABLE test1; + ]], { + 6 + } +) + +-- Collation is set in space format and also in position() - +-- for haystack (string) only. + +test:do_execsql_test( + "position-1.66", + [[ + CREATE TABLE test1 (s1 VARCHAR(5) PRIMARY KEY COLLATE "unicode_ci"); + INSERT INTO test1 VALUES('à'); + SELECT POSITION('a', s1 COLLATE "unicode") FROM test1; + DROP TABLE test1; + ]], { + 0 + } +) + +test:do_execsql_test( + "position-1.67", + [[ + CREATE TABLE test1 (s1 VARCHAR(5) PRIMARY KEY COLLATE "unicode_ci"); + INSERT INTO test1 VALUES('qwèrty'); + SELECT POSITION('er', s1 COLLATE "unicode") FROM test1; + DROP TABLE test1; + ]], { + 0 + } +) + +test:do_execsql_test( + "position-1.68", + [[ + CREATE TABLE test1 (s1 VARCHAR(5) PRIMARY KEY COLLATE "unicode_ci"); + INSERT INTO test1 VALUES('qwèrtÿ'); + SELECT POSITION('Y', s1 COLLATE "unicode") FROM test1; + DROP TABLE test1; + ]], { + 0 + } +) + +-- Collation is set in space format and also in position () - +-- for needle (string) only. + +test:do_execsql_test( + "position-1.69", + [[ + CREATE TABLE test1 (s1 VARCHAR(5) PRIMARY KEY COLLATE "unicode_ci"); + INSERT INTO test1 VALUES('à'); + SELECT POSITION('a' COLLATE "unicode", s1) FROM test1; + DROP TABLE test1; + ]], { + 0 + } +) + +test:do_execsql_test( + "position-1.70", + [[ + CREATE TABLE test1 (s1 VARCHAR(5) PRIMARY KEY COLLATE "unicode_ci"); + INSERT INTO test1 VALUES('qwèrty'); + SELECT POSITION('er' COLLATE "unicode", s1) FROM test1; + DROP TABLE test1; + ]], { + 0 + } +) + +test:do_execsql_test( + "position-1.71", + [[ + CREATE TABLE test1 (s1 VARCHAR(5) PRIMARY KEY COLLATE "unicode_ci"); + INSERT INTO test1 VALUES('qwèrtÿ'); + SELECT POSITION('Y' COLLATE "unicode", s1) FROM test1; + DROP TABLE test1; + ]], { + 0 + } +) + +-- Collation is set in space format and also in position() - +-- for both arguments. Arguments have the same collations. + +test:do_execsql_test( + "position-1.72", + [[ + CREATE TABLE test1 (s1 VARCHAR(5) PRIMARY KEY COLLATE "unicode_ci"); + INSERT INTO test1 VALUES('à'); + SELECT POSITION('a' COLLATE "unicode", s1 COLLATE "unicode") FROM test1; + DROP TABLE test1; + ]], { + 0 + } +) + +test:do_execsql_test( + "position-1.73", + [[ + CREATE TABLE test1 (s1 VARCHAR(5) PRIMARY KEY COLLATE "unicode_ci"); + INSERT INTO test1 VALUES('qwèrty'); + SELECT POSITION('er' COLLATE "unicode", s1 COLLATE "unicode") FROM test1; + DROP TABLE test1; + ]], { + 0 + } +) + +test:do_execsql_test( + "position-1.74", + [[ + CREATE TABLE test1 (s1 VARCHAR(5) PRIMARY KEY COLLATE "unicode_ci"); + INSERT INTO test1 VALUES('qwèrtÿ'); + SELECT POSITION('Y'COLLATE "unicode", s1 COLLATE "unicode") FROM test1; + DROP TABLE test1; + ]], { + 0 + } +) + +-- Collation is set in space format and also in position() - +-- for both arguments. Arguments have different explicit +-- collations thus an error is expected. + +test:do_catchsql_test( + "position-1.75", + [[ + CREATE TABLE test1 (s1 VARCHAR(5) PRIMARY KEY COLLATE "unicode_ci"); + INSERT INTO test1 VALUES('à'); + SELECT POSITION('a' COLLATE "unicode_ci", s1 COLLATE "unicode") FROM test1; + ]], { + 1, "Illegal mix of collations" + } +) + +test:do_catchsql_test( + "position-1.76", + [[ + DROP TABLE test1; + CREATE TABLE test1 (s1 VARCHAR(5) PRIMARY KEY COLLATE "unicode_ci"); + INSERT INTO test1 VALUES('qwèrty'); + SELECT POSITION('er' COLLATE "unicode_ci", s1 COLLATE "unicode") FROM test1; + DROP TABLE test1; + ]], { + 1, "Illegal mix of collations" + } +) + +test:do_catchsql_test( + "position-1.77", + [[ + DROP TABLE test1; + CREATE TABLE test1 (s1 VARCHAR(5) PRIMARY KEY COLLATE "unicode_ci"); + INSERT INTO test1 VALUES('qwèrtÿ'); + SELECT POSITION('Y' COLLATE "unicode_ci", s1 COLLATE "unicode") FROM test1; + ]], { + 1, "Illegal mix of collations" + } +) + +test:finish_test() diff --git a/test/sql-tap/with1.test.lua b/test/sql-tap/with1.test.lua index 16c9b1207..ecc16755e 100755 --- a/test/sql-tap/with1.test.lua +++ b/test/sql-tap/with1.test.lua @@ -595,11 +595,11 @@ test:do_execsql_test("8.2-soduko", [[ /* The tricky bit. */ x(s, ind) AS ( - SELECT sud, instr(sud, '.') FROM input + SELECT sud, position('.', sud) FROM input UNION ALL SELECT substr(s, 1, ind-1) || z || substr(s, ind+1), - instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' ) + position('.', substr(s, 1, ind-1) || z || substr(s, ind+1)) FROM x, digits AS z WHERE ind>0 AND NOT EXISTS ( -- 2.20.1