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 ED741248CD for ; Wed, 10 Jul 2019 07:01:13 -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 0hwfLuYba6gK for ; Wed, 10 Jul 2019 07:01:13 -0400 (EDT) Received: from smtp60.i.mail.ru (smtp60.i.mail.ru [217.69.128.40]) (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 60C1F2064D for ; Wed, 10 Jul 2019 07:01:13 -0400 (EDT) From: Kirill Shcherbatov Subject: [tarantool-patches] [PATCH v2 02/12] sql: get rid of LIKELY, UNLIKELY and LIKEHOOD Date: Wed, 10 Jul 2019 14:01:01 +0300 Message-Id: <3f721bb190812977a76a740da4a178436f24df99.1562756438.git.kshcherbatov@tarantool.org> In-Reply-To: References: MIME-Version: 1.0 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, korablev@tarantool.org Cc: kostja@tarantool.org, Kirill Shcherbatov Got rid of LIKELY, UNLIKELY and LIKEHOOD builtins because they are not a part of the Standard and make source code more complicated. Needed for #4182 --- src/box/sql/expr.c | 26 +-- src/box/sql/func.c | 20 +-- src/box/sql/resolve.c | 52 ------ src/box/sql/sqlInt.h | 3 - src/box/sql/where.c | 166 +++++++----------- src/box/sql/whereInt.h | 1 - src/box/sql/whereexpr.c | 6 - test/sql-tap/collation.test.lua | 2 +- test/sql-tap/func3.test.lua | 289 ------------------------------- test/sql-tap/orderby5.test.lua | 2 +- test/sql-tap/select4.test.lua | 12 +- test/sql-tap/sql-errors.test.lua | 12 +- test/sql-tap/whereG.test.lua | 87 +++------- 13 files changed, 99 insertions(+), 579 deletions(-) delete mode 100755 test/sql-tap/func3.test.lua diff --git a/src/box/sql/expr.c b/src/box/sql/expr.c index d7104d8a0..20e490b96 100644 --- a/src/box/sql/expr.c +++ b/src/box/sql/expr.c @@ -176,23 +176,13 @@ sqlExprAddCollateString(Parse * pParse, Expr * pExpr, const char *zC) return sqlExprAddCollateToken(pParse, pExpr, &s, 0); } -/* - * Skip over any TK_COLLATE operators and any unlikely() - * or likelihood() function at the root of an expression. - */ +/* Skip over any TK_COLLATE operators. */ Expr * sqlExprSkipCollate(Expr * pExpr) { while (pExpr && ExprHasProperty(pExpr, EP_Skip)) { - if (ExprHasProperty(pExpr, EP_Unlikely)) { - assert(!ExprHasProperty(pExpr, EP_xIsSelect)); - assert(pExpr->x.pList->nExpr > 0); - assert(pExpr->op == TK_FUNCTION); - pExpr = pExpr->x.pList->a[0].pExpr; - } else { - assert(pExpr->op == TK_COLLATE); - pExpr = pExpr->pLeft; - } + assert(pExpr->op == TK_COLLATE); + pExpr = pExpr->pLeft; } return pExpr; } @@ -3981,16 +3971,6 @@ sqlExprCodeTarget(Parse * pParse, Expr * pExpr, int target) break; } - /* The UNLIKELY() function is a no-op. The result is the value - * of the first argument. - */ - if (pDef->funcFlags & SQL_FUNC_UNLIKELY) { - assert(nFarg >= 1); - return sqlExprCodeTarget(pParse, - pFarg->a[0].pExpr, - target); - } - for (i = 0; i < nFarg; i++) { if (i < 32 && sqlExprIsConstant(pFarg->a[i]. diff --git a/src/box/sql/func.c b/src/box/sql/func.c index f0e1b1f0c..29f2b5c6a 100644 --- a/src/box/sql/func.c +++ b/src/box/sql/func.c @@ -603,12 +603,14 @@ ICU_CASE_CONVERT(Upper); /* - * Some functions like COALESCE() and IFNULL() and UNLIKELY() are implemented - * as VDBE code so that unused argument values do not have to be computed. - * However, we still need some kind of function implementation for this - * routines in the function table. The noopFunc macro provides this. - * noopFunc will never be called so it doesn't matter what the implementation - * is. We might as well use the "version()" function as a substitute. + * Some functions like COALESCE() and IFNULL() are implemented + * as VDBE code so that unused argument values do not have to be + * computed. + * However, we still need some kind of function implementation for + * this routines in the function table. The noopFunc macro + * provides this. noopFunc will never be called so it doesn't + * matter what the implementation is. We might as well use the + * "version()" function as a substitute. */ #define noopFunc sql_func_version /* Substitute function - never called */ @@ -1780,12 +1782,6 @@ sqlRegisterBuiltinFunctions(void) * For peak efficiency, put the most frequently used function last. */ static FuncDef aBuiltinFunc[] = { - FUNCTION2(unlikely, 1, 0, 0, noopFunc, SQL_FUNC_UNLIKELY, - FIELD_TYPE_BOOLEAN), - FUNCTION2(likelihood, 2, 0, 0, noopFunc, SQL_FUNC_UNLIKELY, - FIELD_TYPE_BOOLEAN), - FUNCTION2(likely, 1, 0, 0, noopFunc, SQL_FUNC_UNLIKELY, - FIELD_TYPE_BOOLEAN), FUNCTION_COLL(trim, 1, 3, 0, trim_func_one_arg), FUNCTION_COLL(trim, 2, 3, 0, trim_func_two_args), FUNCTION_COLL(trim, 3, 3, 0, trim_func_three_args), diff --git a/src/box/sql/resolve.c b/src/box/sql/resolve.c index 0b90edd06..7c81b1980 100644 --- a/src/box/sql/resolve.c +++ b/src/box/sql/resolve.c @@ -496,24 +496,6 @@ sql_expr_new_column(struct sql *db, struct SrcList *src_list, int src_idx, return expr; } -/* - * Expression p should encode a floating point value between 1.0 and 0.0. - * Return 1024 times this value. Or return -1 if p is not a floating point - * value between 1.0 and 0.0. - */ -static int -exprProbability(Expr * p) -{ - double r = -1.0; - if (p->op != TK_FLOAT) - return -1; - sqlAtoF(p->u.zToken, &r, sqlStrlen30(p->u.zToken)); - assert(r >= 0.0); - if (r > 1.0) - return -1; - return (int)(r * 134217728.0); -} - /* * This routine is callback for sqlWalkExpr(). * @@ -613,40 +595,6 @@ resolveExprStep(Walker * pWalker, Expr * pExpr) } else { is_agg = pDef->xFinalize != 0; pExpr->type = pDef->ret_type; - const char *err = - "second argument to likelihood() must "\ - "be a constant between 0.0 and 1.0"; - if (pDef->funcFlags & SQL_FUNC_UNLIKELY) { - ExprSetProperty(pExpr, - EP_Unlikely | EP_Skip); - if (n == 2) { - pExpr->iTable = - exprProbability(pList->a[1]. - pExpr); - if (pExpr->iTable < 0) { - diag_set(ClientError, - ER_ILLEGAL_PARAMS, - err); - pParse->is_aborted = - true; - pNC->nErr++; - } - } else { - /* EVIDENCE-OF: R-61304-29449 The unlikely(X) function is - * equivalent to likelihood(X, 0.0625). - * EVIDENCE-OF: R-01283-11636 The unlikely(X) function is - * short-hand for likelihood(X,0.0625). - * EVIDENCE-OF: R-36850-34127 The likely(X) function is short-hand - * for likelihood(X,0.9375). - * EVIDENCE-OF: R-53436-40973 The likely(X) function is equivalent - * to likelihood(X,0.9375). - */ - /* TUNING: unlikely() probability is 0.0625. likely() is 0.9375 */ - pExpr->iTable = - pDef->zName[0] == - 'u' ? 8388608 : 125829120; - } - } if (pDef-> funcFlags & (SQL_FUNC_CONSTANT | SQL_FUNC_SLOCHNG)) { diff --git a/src/box/sql/sqlInt.h b/src/box/sql/sqlInt.h index 9305f7d5c..8ab9804fe 100644 --- a/src/box/sql/sqlInt.h +++ b/src/box/sql/sqlInt.h @@ -1284,7 +1284,6 @@ struct FuncDestructor { #define SQL_FUNC_TYPEOF 0x0080 /* Built-in typeof() function */ #define SQL_FUNC_COUNT 0x0100 /* Built-in count(*) aggregate */ #define SQL_FUNC_COALESCE 0x0200 /* Built-in coalesce() or ifnull() */ -#define SQL_FUNC_UNLIKELY 0x0400 /* Built-in unlikely() function */ #define SQL_FUNC_CONSTANT 0x0800 /* Constant inputs give a constant output */ #define SQL_FUNC_MINMAX 0x1000 /* True for min() and max() aggregates */ #define SQL_FUNC_SLOCHNG 0x2000 /* "Slow Change". Value constant during a @@ -1673,7 +1672,6 @@ struct Expr { int iTable; /* TK_COLUMN: cursor number of table holding column * TK_REGISTER: register number * TK_TRIGGER: 1 -> new, 0 -> old - * EP_Unlikely: 134217728 times likelihood * TK_SELECT: 1st register of result vector */ ynVar iColumn; /* TK_COLUMN: column index. @@ -1711,7 +1709,6 @@ struct Expr { #define EP_Static 0x008000 /* Held in memory not obtained from malloc() */ #define EP_MemToken 0x010000 /* Need to sqlDbFree() Expr.zToken */ #define EP_NoReduce 0x020000 /* Cannot EXPRDUP_REDUCE this Expr */ -#define EP_Unlikely 0x040000 /* unlikely() or likelihood() function */ #define EP_ConstFunc 0x080000 /* A sql_FUNC_CONSTANT or _SLOCHNG function */ #define EP_CanBeNull 0x100000 /* Can be null despite NOT NULL constraint */ #define EP_Subquery 0x200000 /* Tree contains a TK_SELECT operator */ diff --git a/src/box/sql/where.c b/src/box/sql/where.c index 5458c6a75..f93c7bda0 100644 --- a/src/box/sql/where.c +++ b/src/box/sql/where.c @@ -1111,20 +1111,13 @@ whereKeyStats(Parse * pParse, /* Database connection */ * bound on a range scan. Without considering pTerm, it is estimated * that the scan will visit nNew rows. This function returns the number * estimated to be visited after taking pTerm into account. - * - * If the user explicitly specified a likelihood() value for this term, - * then the return value is the likelihood multiplied by the number of - * input rows. Otherwise, this function assumes that an "IS NOT NULL" term - * has a likelihood of 0.50, and any other term a likelihood of 0.25. */ static LogEst whereRangeAdjust(WhereTerm * pTerm, LogEst nNew) { LogEst nRet = nNew; if (pTerm) { - if (pTerm->truthProb <= 0) { - nRet += pTerm->truthProb; - } else if ((pTerm->wtFlags & TERM_VNULL) == 0) { + if ((pTerm->wtFlags & TERM_VNULL) == 0) { nRet -= 20; assert(20 == sqlLogEst(4)); } @@ -1469,17 +1462,6 @@ whereRangeScanEst(Parse * pParse, /* Parsing & code generating context */ nNew = whereRangeAdjust(pLower, nOut); nNew = whereRangeAdjust(pUpper, nNew); - /* TUNING: If there is both an upper and lower limit and neither limit - * has an application-defined likelihood(), assume the range is - * reduced by an additional 75%. This means that, by default, an open-ended - * range query (e.g. col > ?) is assumed to match 1/4 of the rows in the - * index. While a closed range (e.g. col BETWEEN ? AND ?) is estimated to - * match 1/64 of the index. - */ - if (pLower && pLower->truthProb > 0 && pUpper && pUpper->truthProb > 0) { - nNew -= 20; - } - nOut -= (pLower != 0) + (pUpper != 0); if (nNew < 10) nNew = 10; @@ -1624,8 +1606,8 @@ whereTermPrint(WhereTerm * pTerm, int iTerm) pTerm->leftCursor); } sqlDebugPrintf - ("TERM-%-3d %p %s %-12s prob=%-3d op=0x%03x wtFlags=0x%04x", - iTerm, pTerm, zType, zLeft, pTerm->truthProb, + ("TERM-%-3d %p %s %-12s op=0x%03x wtFlags=0x%04x", + iTerm, pTerm, zType, zLeft, pTerm->eOperator, pTerm->wtFlags); if (pTerm->iField) { sqlDebugPrintf(" iField=%d\n", pTerm->iField); @@ -2117,11 +2099,6 @@ whereLoopInsert(WhereLoopBuilder * pBuilder, WhereLoop * pTemplate) * Adjust the WhereLoop.nOut value downward to account for terms of the * WHERE clause that reference the loop but which are not used by an * index. -* - * For every WHERE clause term that is not used by the index - * and which has a truth probability assigned by one of the likelihood(), - * likely(), or unlikely() SQL functions, reduce the estimated number - * of output rows by the probability specified. * * TUNING: For every WHERE clause term that is not used by the index * and which does not have an assigned truth probability, heuristics @@ -2170,27 +2147,21 @@ whereLoopOutputAdjust(WhereClause * pWC, /* The WHERE clause */ break; } if (j < 0) { - if (pTerm->truthProb <= 0) { - /* If a truth probability is specified using the likelihood() hints, - * then use the probability provided by the application. - */ - pLoop->nOut += pTerm->truthProb; - } else { - /* In the absence of explicit truth probabilities, use heuristics to - * guess a reasonable truth probability. - */ - pLoop->nOut--; - if ((pTerm->eOperator & WO_EQ) != 0) { - Expr *pRight = pTerm->pExpr->pRight; - if (sqlExprIsInteger(pRight, &k) - && k >= (-1) && k <= 1) { - k = 10; - } else { - k = 20; - } - if (iReduce < k) - iReduce = k; + /* In the absence of explicit truth + * probabilities, use heuristics to + * guess a reasonable truth probability. + */ + pLoop->nOut--; + if ((pTerm->eOperator & WO_EQ) != 0) { + Expr *pRight = pTerm->pExpr->pRight; + if (sqlExprIsInteger(pRight, &k) + && k >= (-1) && k <= 1) { + k = 10; + } else { + k = 20; } + if (iReduce < k) + iReduce = k; } } } @@ -2504,56 +2475,53 @@ whereLoopAddBtreeIndex(WhereLoopBuilder * pBuilder, /* The WhereLoop factory */ assert(eOp & (WO_ISNULL | WO_EQ | WO_IN)); assert(pNew->nOut == saved_nOut); - if (pTerm->truthProb <= 0 && probe->space_id != 0) { - assert((eOp & WO_IN) || nIn == 0); - testcase(eOp & WO_IN); - pNew->nOut += pTerm->truthProb; - pNew->nOut -= nIn; - } else { - tRowcnt nOut = 0; - if (nInMul == 0 - && stat->sample_count - && pNew->nEq <= stat->sample_field_count - && ((eOp & WO_IN) == 0 - || !ExprHasProperty(pTerm->pExpr, - EP_xIsSelect)) - ) { - Expr *pExpr = pTerm->pExpr; - if ((eOp & (WO_EQ | WO_ISNULL)) - != 0) { - testcase(eOp & WO_EQ); - testcase(eOp & WO_ISNULL); - rc = whereEqualScanEst(pParse, - pBuilder, - pExpr->pRight, - &nOut); - } else { - rc = whereInScanEst(pParse, - pBuilder, - pExpr->x.pList, - &nOut); - } - if (rc != 0) - break; /* Jump out of the pTerm loop */ - if (nOut) { - pNew->nOut = - sqlLogEst(nOut); - if (pNew->nOut > saved_nOut) - pNew->nOut = saved_nOut; - pNew->nOut -= nIn; - } + tRowcnt nOut = 0; + if (nInMul == 0 + && stat->sample_count + && pNew->nEq <= stat->sample_field_count + && ((eOp & WO_IN) == 0 + || !ExprHasProperty(pTerm->pExpr, + EP_xIsSelect)) + ) { + Expr *pExpr = pTerm->pExpr; + if ((eOp & (WO_EQ | WO_ISNULL)) + != 0) { + testcase(eOp & WO_EQ); + testcase(eOp & WO_ISNULL); + rc = whereEqualScanEst(pParse, + pBuilder, + pExpr->pRight, + &nOut); + } else { + rc = whereInScanEst(pParse, + pBuilder, + pExpr->x.pList, + &nOut); } - if (nOut == 0) { - pNew->nOut += - (index_field_tuple_est(probe, nEq) - - index_field_tuple_est(probe, nEq -1)); - if (eOp & WO_ISNULL) { - /* TUNING: If there is no likelihood() value, assume that a - * "col IS NULL" expression matches twice as many rows - * as (col=?). - */ - pNew->nOut += 10; - } + /* Jump out of the pTerm loop */ + if (rc != 0) + break; + if (nOut) { + pNew->nOut = + sqlLogEst(nOut); + if (pNew->nOut > saved_nOut) + pNew->nOut = saved_nOut; + pNew->nOut -= nIn; + } + } + if (nOut == 0) { + pNew->nOut += + (index_field_tuple_est(probe, nEq) - + index_field_tuple_est(probe, nEq -1)); + if (eOp & WO_ISNULL) { + /* + * Assume that a + * "col IS NULL" + * expression matches + * twice as many rows + * as (col=?). + */ + pNew->nOut += 10; } } } @@ -3038,13 +3006,7 @@ whereLoopAddOr(WhereLoopBuilder * pBuilder, Bitmask mPrereq, Bitmask mUnusable) * errors, it may be that the cost of the OR-scan is equal to its * most expensive sub-scan. Add the smallest possible penalty * (equivalent to multiplying the cost by 1.07) to ensure that - * this does not happen. Otherwise, for WHERE clauses such as the - * following where there is an index on "y": - * - * WHERE likelihood(x=?, 0.99) OR y=? - * - * the planner may elect to "OR" together a full-table scan and an - * index lookup. And other similarly odd results. + * this does not happen. */ pNew->rRun = sSum.a[i].rRun + 1; pNew->nOut = sSum.a[i].nOut; diff --git a/src/box/sql/whereInt.h b/src/box/sql/whereInt.h index e39e00210..01258e289 100644 --- a/src/box/sql/whereInt.h +++ b/src/box/sql/whereInt.h @@ -248,7 +248,6 @@ struct WherePath { struct WhereTerm { Expr *pExpr; /* Pointer to the subexpression that is this term */ WhereClause *pWC; /* The clause this term is part of */ - LogEst truthProb; /* Probability of truth for this expression */ u16 wtFlags; /* TERM_xxx bit flags. See below */ u16 eOperator; /* A WO_xx value describing */ u8 nChild; /* Number of children that must disable us */ diff --git a/src/box/sql/whereexpr.c b/src/box/sql/whereexpr.c index a88f96407..befe4ce73 100644 --- a/src/box/sql/whereexpr.c +++ b/src/box/sql/whereexpr.c @@ -110,11 +110,6 @@ whereClauseInsert(WhereClause * pWC, Expr * p, u16 wtFlags) pWC->nSlot = sqlMallocSize(pWC->a) / sizeof(pWC->a[0]); } pTerm = &pWC->a[idx = pWC->nTerm++]; - if (p && ExprHasProperty(p, EP_Unlikely)) { - pTerm->truthProb = sqlLogEst(p->iTable) - 270; - } else { - pTerm->truthProb = 1; - } pTerm->pExpr = sqlExprSkipCollate(p); pTerm->wtFlags = wtFlags; pTerm->pWC = pWC; @@ -360,7 +355,6 @@ static void markTermAsChild(WhereClause * pWC, int iChild, int iParent) { pWC->a[iChild].iParent = iParent; - pWC->a[iChild].truthProb = pWC->a[iParent].truthProb; pWC->a[iParent].nChild++; } diff --git a/test/sql-tap/collation.test.lua b/test/sql-tap/collation.test.lua index 79547361c..d41064d04 100755 --- a/test/sql-tap/collation.test.lua +++ b/test/sql-tap/collation.test.lua @@ -494,7 +494,7 @@ local like_testcases = {0, {"Aab", "aaa"}} }, {"2.1.2", "EXPLAIN QUERY PLAN SELECT * FROM tx1 WHERE s1 LIKE 'A%';", - {0, {0, 0, 0, "SEARCH TABLE TX1 USING COVERING INDEX I1 (S1>? AND S1? AND S1 - 9223372036854775807LL - -- - }) - -test:do_execsql_test( - "func3-5.2", - [[ - SELECT likelihood(-9223372036854775808, 0.5); - ]], { - -- - -9223372036854775808LL - -- - }) - -test:do_execsql_test( - "func3-5.3", - [[ - SELECT likelihood(14.125, 0.5); - ]], { - -- - 14.125 - -- - }) - -test:do_execsql_test( - "func3-5.4", - [[ - SELECT likelihood(NULL, 0.5); - ]], { - -- - "" - -- - }) - -test:do_execsql_test( - "func3-5.5", - [[ - SELECT likelihood('test-string', 0.5); - ]], { - -- - "test-string" - -- - }) - -test:do_execsql_test( - "func3-5.6", - [[ - SELECT quote(likelihood(x'010203000405', 0.5)); - ]], { - -- - "X'010203000405'" - -- - }) - --- EVIDENCE-OF: R-44133-61651 The value Y in likelihood(X,Y) must be a --- floating point constant between 0.0 and 1.0, inclusive. --- -test:do_execsql_test( - "func3-5.7", - [[ - SELECT likelihood(123, 1.0), likelihood(456, 0.0); - ]], { - -- - 123, 456 - -- - }) - -test:do_catchsql_test( - "func3-5.8", - [[ - SELECT likelihood(123, 1.000001); - ]], { - -- - 1, "Illegal parameters, second argument to likelihood() must be a constant between 0.0 and 1.0" - -- - }) - -test:do_catchsql_test( - "func3-5.9", - [[ - SELECT likelihood(123, -0.000001); - ]], { - -- - 1, "Illegal parameters, second argument to likelihood() must be a constant between 0.0 and 1.0" - -- - }) - -test:do_catchsql_test( - "func3-5.10", - [[ - SELECT likelihood(123, 0.5+0.3); - ]], { - -- - 1, "Illegal parameters, second argument to likelihood() must be a constant between 0.0 and 1.0" - -- - }) - --- EVIDENCE-OF: R-28535-44631 The likelihood(X) function is a no-op that --- the code generator optimizes away so that it consumes no CPU cycles --- during run-time (that is, during calls to sql_step()). --- -test:do_test( - "func3-5.20", - function() - return test:execsql "EXPLAIN SELECT likelihood(min(1.0+'2.0',4*11), 0.5)" - end, test:execsql("EXPLAIN SELECT min(1.0+'2.0',4*11)")) - --- EVIDENCE-OF: R-11152-23456 The unlikely(X) function returns the --- argument X unchanged. --- -test:do_execsql_test( - "func3-5.30", - [[ - SELECT unlikely(9223372036854775807); - ]], { - -- - 9223372036854775807LL - -- - }) - -test:do_execsql_test( - "func3-5.31", - [[ - SELECT unlikely(-9223372036854775808); - ]], { - -- - -9223372036854775808LL - -- - }) - -test:do_execsql_test( - "func3-5.32", - [[ - SELECT unlikely(14.125); - ]], { - -- - 14.125 - -- - }) - -test:do_execsql_test( - "func3-5.33", - [[ - SELECT unlikely(NULL); - ]], { - -- - "" - -- - }) - -test:do_execsql_test( - "func3-5.34", - [[ - SELECT unlikely('test-string'); - ]], { - -- - "test-string" - -- - }) - -test:do_execsql_test( - "func3-5.35", - [[ - SELECT quote(unlikely(x'010203000405')); - ]], { - -- - "X'010203000405'" - -- - }) - --- EVIDENCE-OF: R-22887-63324 The unlikely(X) function is a no-op that --- the code generator optimizes away so that it consumes no CPU cycles at --- run-time (that is, during calls to sql_step()). --- -test:do_test( - "func3-5.39", - function() - return test:execsql "EXPLAIN SELECT unlikely(min(1.0+'2.0',4*11))" - end, test:execsql "EXPLAIN SELECT min(1.0+'2.0',4*11)") - --- EVIDENCE-OF: R-23735-03107 The likely(X) function returns the argument --- X unchanged. --- -test:do_execsql_test( - "func3-5.50", - [[ - SELECT likely(9223372036854775807); - ]], { - -- - 9223372036854775807LL - -- - }) - -test:do_execsql_test( - "func3-5.51", - [[ - SELECT likely(-9223372036854775808); - ]], { - -- - -9223372036854775808LL - -- - }) - -test:do_execsql_test( - "func3-5.52", - [[ - SELECT likely(14.125); - ]], { - -- - 14.125 - -- - }) - -test:do_execsql_test( - "func3-5.53", - [[ - SELECT likely(NULL); - ]], { - -- - "" - -- - }) - -test:do_execsql_test( - "func3-5.54", - [[ - SELECT likely('test-string'); - ]], { - -- - "test-string" - -- - }) - -test:do_execsql_test( - "func3-5.55", - [[ - SELECT quote(likely(x'010203000405')); - ]], { - -- - "X'010203000405'" - -- - }) - --- EVIDENCE-OF: R-43464-09689 The likely(X) function is a no-op that the --- code generator optimizes away so that it consumes no CPU cycles at --- run-time (that is, during calls to sql_step()). --- -test:do_test( - "func3-5.59", - function() - return test:execsql "EXPLAIN SELECT likely(min(1.0+'2.0',4*11))" - end, test:execsql "EXPLAIN SELECT min(1.0+'2.0',4*11)") - - - -test:finish_test() diff --git a/test/sql-tap/orderby5.test.lua b/test/sql-tap/orderby5.test.lua index c7c09bc07..d5ce024f8 100755 --- a/test/sql-tap/orderby5.test.lua +++ b/test/sql-tap/orderby5.test.lua @@ -153,7 +153,7 @@ test:do_execsql_test( -- } {~/B-TREE/} -- do_execsql_test 2.1b { -- EXPLAIN QUERY PLAN --- SELECT * FROM t1 WHERE likelihood(a=0, 0.05) ORDER BY a, b, c; +-- SELECT * FROM t1 WHERE (a=0, 0.05) ORDER BY a, b, c; -- } {/B-TREE/} -- do_execsql_test 2.2 { -- EXPLAIN QUERY PLAN diff --git a/test/sql-tap/select4.test.lua b/test/sql-tap/select4.test.lua index 1c0804b8e..0355bddf5 100755 --- a/test/sql-tap/select4.test.lua +++ b/test/sql-tap/select4.test.lua @@ -1,6 +1,6 @@ #!/usr/bin/env tarantool test = require("sqltester") -test:plan(105) +test:plan(104) --!./tcltestrunner.lua -- 2001 September 15 @@ -1446,16 +1446,6 @@ test:do_execsql_test( -- }) -test:do_execsql_test( - "select4-14.15", - [[ - SELECT * FROM (SELECT 123), (SELECT 456) ON likely(false OR true) OR false; - ]], { - -- - 123, 456 - -- - }) - test:do_execsql_test( "select4-14.16", [[ diff --git a/test/sql-tap/sql-errors.test.lua b/test/sql-tap/sql-errors.test.lua index 636242129..74b59bfc5 100755 --- a/test/sql-tap/sql-errors.test.lua +++ b/test/sql-tap/sql-errors.test.lua @@ -1,6 +1,6 @@ #!/usr/bin/env tarantool test = require("sqltester") -test:plan(63) +test:plan(62) test:execsql([[ CREATE TABLE t0 (i INT PRIMARY KEY, a INT); @@ -406,16 +406,6 @@ test:do_catchsql_test( -- }) -test:do_catchsql_test( - "sql-errors-1.36", - [[ - SELECT likelihood(1, 2); - ]], { - -- - 1,"Illegal parameters, second argument to likelihood() must be a constant between 0.0 and 1.0" - -- - }) - test:do_catchsql_test( "sql-errors-1.37", [[ diff --git a/test/sql-tap/whereG.test.lua b/test/sql-tap/whereG.test.lua index 177d9d14e..8e3250569 100755 --- a/test/sql-tap/whereG.test.lua +++ b/test/sql-tap/whereG.test.lua @@ -1,6 +1,6 @@ #!/usr/bin/env tarantool test = require("sqltester") -test:plan(23) +test:plan(20) --!./tcltestrunner.lua -- 2013-09-05 @@ -14,8 +14,6 @@ test:plan(23) -- ------------------------------------------------------------------------- -- --- Test cases for query planning decisions and the likely(), unlikely(), and --- likelihood() functions. -- ["set","testdir",[["file","dirname",["argv0"]]]] -- ["source",[["testdir"],"\/tester.tcl"]] testprefix = "whereG" @@ -75,7 +73,7 @@ test:do_eqp_test( [[ SELECT DISTINCT aname FROM album, composer, track - WHERE unlikely(cname LIKE '%bach%') + WHERE (cname LIKE '%bach%') AND composer.cid=track.cid AND album.aid=track.aid; ]], { @@ -89,7 +87,7 @@ test:do_execsql_test( [[ SELECT DISTINCT aname FROM album, composer, track - WHERE unlikely(cname LIKE '%bach%') + WHERE (cname LIKE '%bach%') AND composer.cid=track.cid AND album.aid=track.aid; ]], { @@ -104,7 +102,7 @@ test:do_eqp_test( [[ SELECT DISTINCT aname FROM album, composer, track - WHERE likelihood(cname LIKE '%bach%', 0.5) + WHERE (cname LIKE '%bach%') AND composer.cid=track.cid AND album.aid=track.aid; ]], { @@ -118,7 +116,7 @@ test:do_execsql_test( [[ SELECT DISTINCT aname FROM album, composer, track - WHERE likelihood(cname LIKE '%bach%', 0.5) + WHERE (cname LIKE '%bach%') AND composer.cid=track.cid AND album.aid=track.aid; ]], { @@ -161,8 +159,8 @@ test:do_eqp_test( SELECT DISTINCT aname FROM album, composer, track WHERE cname LIKE '%bach%' - AND unlikely(composer.cid=track.cid) - AND unlikely(album.aid=track.aid); + AND (composer.cid=track.cid) + AND (album.aid=track.aid); ]], { -- "/.*track.*(composer.*album|album.*composer).*/" @@ -175,56 +173,14 @@ test:do_execsql_test( SELECT DISTINCT aname FROM album, composer, track WHERE cname LIKE '%bach%' - AND unlikely(composer.cid=track.cid) - AND unlikely(album.aid=track.aid); + AND (composer.cid=track.cid) + AND (album.aid=track.aid); ]], { -- "Mass in B Minor, BWV 232" -- }) -test:do_catchsql_test( - "whereG-2.1", - [[ - SELECT DISTINCT aname - FROM album, composer, track - WHERE likelihood(cname LIKE '%bach%', -0.01) - AND composer.cid=track.cid - AND album.aid=track.aid; - ]], { - -- - 1, "Illegal parameters, second argument to likelihood() must be a constant between 0.0 and 1.0" - -- - }) - -test:do_catchsql_test( - "whereG-2.2", - [[ - SELECT DISTINCT aname - FROM album, composer, track - WHERE likelihood(cname LIKE '%bach%', 1.01) - AND composer.cid=track.cid - AND album.aid=track.aid; - ]], { - -- - 1, "Illegal parameters, second argument to likelihood() must be a constant between 0.0 and 1.0" - -- - }) - -test:do_catchsql_test( - "whereG-2.3", - [[ - SELECT DISTINCT aname - FROM album, composer, track - WHERE likelihood(cname LIKE '%bach%', track.cid) - AND composer.cid=track.cid - AND album.aid=track.aid; - ]], { - -- - 1, "Illegal parameters, second argument to likelihood() must be a constant between 0.0 and 1.0" - -- - }) - -- Commuting a term of the WHERE clause should not change the query plan -- test:do_execsql_test( @@ -270,9 +226,6 @@ test:do_execsql_test( }) --------------------------------------------------------------------------- --- Test that likelihood() specifications on indexed terms are taken into --- account by various forms of loops. --- -- 5.1.*: open ended range scans -- 5.2.*: skip-scans -- @@ -288,10 +241,10 @@ test:do_execsql_test( -- SELECT * FROM t1 WHERE a>? -- } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?)}} -- do_eqp_test 5.1.3 { --- SELECT * FROM t1 WHERE likelihood(a>?, 0.9) +-- SELECT * FROM t1 WHERE (a>?) -- } {0 0 0 {SCAN TABLE t1}} -- do_eqp_test 5.1.4 { --- SELECT * FROM t1 WHERE likely(a>?) +-- SELECT * FROM t1 WHERE (a>?) -- } {0 0 0 {SCAN TABLE t1}} -- do_test 5.2 { -- for {set i 0} {$i < 100} {incr i} { @@ -301,22 +254,22 @@ test:do_execsql_test( -- execsql { ANALYZE } -- } {} -- do_eqp_test 5.2.2 { --- SELECT * FROM t1 WHERE likelihood(b>?, 0.01) +-- SELECT * FROM t1 WHERE (b>?) -- } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (ANY(a) AND b>?)}} -- do_eqp_test 5.2.3 { --- SELECT * FROM t1 WHERE likelihood(b>?, 0.9) +-- SELECT * FROM t1 WHERE (b>?) -- } {0 0 0 {SCAN TABLE t1}} -- do_eqp_test 5.2.4 { --- SELECT * FROM t1 WHERE likely(b>?) +-- SELECT * FROM t1 WHERE (b>?) -- } {0 0 0 {SCAN TABLE t1}} -- do_eqp_test 5.3.1 { -- SELECT * FROM t1 WHERE a=? -- } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}} -- do_eqp_test 5.3.2 { --- SELECT * FROM t1 WHERE likelihood(a=?, 0.9) +-- SELECT * FROM t1 WHERE (a=?) -- } {0 0 0 {SCAN TABLE t1}} -- do_eqp_test 5.3.3 { --- SELECT * FROM t1 WHERE likely(a=?) +-- SELECT * FROM t1 WHERE (a=?) -- } {0 0 0 {SCAN TABLE t1}} -- 2015-06-18 -- Ticket [https://www.sql.org/see/tktview/472f0742a1868fb58862bc588ed70] @@ -332,7 +285,7 @@ test:do_execsql_test( CREATE TABLE t2(i int PRIMARY KEY, b TEXT); INSERT INTO t2 VALUES(1,'T'), (2,'F'); SELECT count(*) FROM t1 LEFT JOIN t2 ON t1.i=t2.i AND b='T' union all - SELECT count(*) FROM t1 LEFT JOIN t2 ON likely(t1.i=t2.i) AND b='T'; + SELECT count(*) FROM t1 LEFT JOIN t2 ON (t1.i=t2.i) AND b='T'; ]], { -- <6.0> 4, 4 @@ -351,7 +304,7 @@ test:do_execsql_test( DROP TABLE IF EXISTS t2; CREATE TABLE t2(x INT , y INT , PRIMARY KEY(x,y)); INSERT INTO t2 VALUES(3,3),(4,4); - SELECT likely(a), x FROM t1, t2 ORDER BY 1, 2; + SELECT (a), x FROM t1, t2 ORDER BY 1, 2; ]], { -- <7.0> 1, 3, 1, 4, 9, 3, 9, 4 @@ -361,7 +314,7 @@ test:do_execsql_test( test:do_execsql_test( "7.1", [[ - SELECT unlikely(a), x FROM t1, t2 ORDER BY 1, 2; + SELECT (a), x FROM t1, t2 ORDER BY 1, 2; ]], { -- <7.1> 1, 3, 1, 4, 9, 3, 9, 4 @@ -371,7 +324,7 @@ test:do_execsql_test( test:do_execsql_test( "7.2", [[ - SELECT likelihood(a,0.5), x FROM t1, t2 ORDER BY 1, 2; + SELECT a, x FROM t1, t2 ORDER BY 1, 2; ]], { -- <7.2> 1, 3, 1, 4, 9, 3, 9, 4 -- 2.21.0