[tarantool-patches] [PATCH v2 02/12] sql: get rid of LIKELY, UNLIKELY and LIKEHOOD
Kirill Shcherbatov
kshcherbatov at tarantool.org
Wed Jul 10 14:01:01 MSK 2019
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 <op> */
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<?) (~16384 rows)"}}},
+ {0, {0, 0, 0, "SEARCH TABLE TX1 USING COVERING INDEX I1 (S1>? AND S1<?) (~65536 rows)"}}},
{"2.2.0",
"PRAGMA case_sensitive_like = true;",
{0}},
diff --git a/test/sql-tap/func3.test.lua b/test/sql-tap/func3.test.lua
deleted file mode 100755
index 6d6411ca6..000000000
--- a/test/sql-tap/func3.test.lua
+++ /dev/null
@@ -1,289 +0,0 @@
-#!/usr/bin/env tarantool
-test = require("sqltester")
-test:plan(25)
-
---!./tcltestrunner.lua
--- 2010 August 27
---
--- 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 that destructor functions associated
--- with functions created using sql_create_function_v2() is
--- correctly invoked.
---
--- ["set","testdir",[["file","dirname",["argv0"]]]]
--- ["source",[["testdir"],"\/tester.tcl"]]
--- MUST_WORK_TEST built-in functions check desrtoy api of sql_create_function_v2
-
--- EVIDENCE-OF: R-41921-05214 The likelihood(X,Y) function returns
--- argument X unchanged.
---
-test:do_execsql_test(
- "func3-5.1",
- [[
- SELECT likelihood(9223372036854775807, 0.5);
- ]], {
- -- <func3-5.1>
- 9223372036854775807LL
- -- </func3-5.1>
- })
-
-test:do_execsql_test(
- "func3-5.2",
- [[
- SELECT likelihood(-9223372036854775808, 0.5);
- ]], {
- -- <func3-5.2>
- -9223372036854775808LL
- -- </func3-5.2>
- })
-
-test:do_execsql_test(
- "func3-5.3",
- [[
- SELECT likelihood(14.125, 0.5);
- ]], {
- -- <func3-5.3>
- 14.125
- -- </func3-5.3>
- })
-
-test:do_execsql_test(
- "func3-5.4",
- [[
- SELECT likelihood(NULL, 0.5);
- ]], {
- -- <func3-5.4>
- ""
- -- </func3-5.4>
- })
-
-test:do_execsql_test(
- "func3-5.5",
- [[
- SELECT likelihood('test-string', 0.5);
- ]], {
- -- <func3-5.5>
- "test-string"
- -- </func3-5.5>
- })
-
-test:do_execsql_test(
- "func3-5.6",
- [[
- SELECT quote(likelihood(x'010203000405', 0.5));
- ]], {
- -- <func3-5.6>
- "X'010203000405'"
- -- </func3-5.6>
- })
-
--- 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);
- ]], {
- -- <func3-5.7>
- 123, 456
- -- </func3-5.7>
- })
-
-test:do_catchsql_test(
- "func3-5.8",
- [[
- SELECT likelihood(123, 1.000001);
- ]], {
- -- <func3-5.8>
- 1, "Illegal parameters, second argument to likelihood() must be a constant between 0.0 and 1.0"
- -- </func3-5.8>
- })
-
-test:do_catchsql_test(
- "func3-5.9",
- [[
- SELECT likelihood(123, -0.000001);
- ]], {
- -- <func3-5.9>
- 1, "Illegal parameters, second argument to likelihood() must be a constant between 0.0 and 1.0"
- -- </func3-5.9>
- })
-
-test:do_catchsql_test(
- "func3-5.10",
- [[
- SELECT likelihood(123, 0.5+0.3);
- ]], {
- -- <func3-5.10>
- 1, "Illegal parameters, second argument to likelihood() must be a constant between 0.0 and 1.0"
- -- </func3-5.10>
- })
-
--- 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);
- ]], {
- -- <func3-5.30>
- 9223372036854775807LL
- -- </func3-5.30>
- })
-
-test:do_execsql_test(
- "func3-5.31",
- [[
- SELECT unlikely(-9223372036854775808);
- ]], {
- -- <func3-5.31>
- -9223372036854775808LL
- -- </func3-5.31>
- })
-
-test:do_execsql_test(
- "func3-5.32",
- [[
- SELECT unlikely(14.125);
- ]], {
- -- <func3-5.32>
- 14.125
- -- </func3-5.32>
- })
-
-test:do_execsql_test(
- "func3-5.33",
- [[
- SELECT unlikely(NULL);
- ]], {
- -- <func3-5.33>
- ""
- -- </func3-5.33>
- })
-
-test:do_execsql_test(
- "func3-5.34",
- [[
- SELECT unlikely('test-string');
- ]], {
- -- <func3-5.34>
- "test-string"
- -- </func3-5.34>
- })
-
-test:do_execsql_test(
- "func3-5.35",
- [[
- SELECT quote(unlikely(x'010203000405'));
- ]], {
- -- <func3-5.35>
- "X'010203000405'"
- -- </func3-5.35>
- })
-
--- 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);
- ]], {
- -- <func3-5.50>
- 9223372036854775807LL
- -- </func3-5.50>
- })
-
-test:do_execsql_test(
- "func3-5.51",
- [[
- SELECT likely(-9223372036854775808);
- ]], {
- -- <func3-5.51>
- -9223372036854775808LL
- -- </func3-5.51>
- })
-
-test:do_execsql_test(
- "func3-5.52",
- [[
- SELECT likely(14.125);
- ]], {
- -- <func3-5.52>
- 14.125
- -- </func3-5.52>
- })
-
-test:do_execsql_test(
- "func3-5.53",
- [[
- SELECT likely(NULL);
- ]], {
- -- <func3-5.53>
- ""
- -- </func3-5.53>
- })
-
-test:do_execsql_test(
- "func3-5.54",
- [[
- SELECT likely('test-string');
- ]], {
- -- <func3-5.54>
- "test-string"
- -- </func3-5.54>
- })
-
-test:do_execsql_test(
- "func3-5.55",
- [[
- SELECT quote(likely(x'010203000405'));
- ]], {
- -- <func3-5.55>
- "X'010203000405'"
- -- </func3-5.55>
- })
-
--- 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(
-- </select4-14.14>
})
-test:do_execsql_test(
- "select4-14.15",
- [[
- SELECT * FROM (SELECT 123), (SELECT 456) ON likely(false OR true) OR false;
- ]], {
- -- <select4-14.15>
- 123, 456
- -- </select4-14.15>
- })
-
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(
-- </sql-errors-1.35>
})
-test:do_catchsql_test(
- "sql-errors-1.36",
- [[
- SELECT likelihood(1, 2);
- ]], {
- -- <sql-errors-1.36>
- 1,"Illegal parameters, second argument to likelihood() must be a constant between 0.0 and 1.0"
- -- </sql-errors-1.36>
- })
-
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);
]], {
-- <whereG-1.7>
"/.*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);
]], {
-- <whereG-1.8>
"Mass in B Minor, BWV 232"
-- </whereG-1.8>
})
-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;
- ]], {
- -- <whereG-2.1>
- 1, "Illegal parameters, second argument to likelihood() must be a constant between 0.0 and 1.0"
- -- </whereG-2.1>
- })
-
-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;
- ]], {
- -- <whereG-2.2>
- 1, "Illegal parameters, second argument to likelihood() must be a constant between 0.0 and 1.0"
- -- </whereG-2.2>
- })
-
-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;
- ]], {
- -- <whereG-2.3>
- 1, "Illegal parameters, second argument to likelihood() must be a constant between 0.0 and 1.0"
- -- </whereG-2.3>
- })
-
-- 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
More information about the Tarantool-patches
mailing list