[tarantool-patches] [PATCH 9/9] sql: make <search condition> accept only boolean

[Nikita Pettik <korablev@tarantool.org>]

<search condition> is a predicate used as a part of WHERE and
JOIN clauses. ANSI SQL states that <search condition> must
accept only boolean arguments. In our SQL it is implemented as
bytecode instruction OP_If which in turn carries out logic of
conditional jump. Since it can be involved in executing other routines
different from <search condition>, we pass to it additional argument
when generating bytecode for WHERE and JOIN clauses. When VDBE performs
OP_If and detects such flag, it checks passed argument to be boolean.

Closes #3723
---
 src/box/sql/expr.c               | 49 ++++++++++++++++++++-----------------
 src/box/sql/sqlInt.h             |  2 ++
 src/box/sql/vdbe.c               | 15 ++++++++++--
 src/box/sql/where.c              |  3 ++-
 src/box/sql/wherecode.c          |  3 ++-
 test/sql-tap/e_delete.test.lua   |  8 +++----
 test/sql-tap/e_select1.test.lua  | 52 ++++++++++++++++++++--------------------
 test/sql-tap/func.test.lua       |  2 +-
 test/sql-tap/in1.test.lua        |  2 +-
 test/sql-tap/in3.test.lua        |  2 +-
 test/sql-tap/join5.test.lua      | 20 ++++++++--------
 test/sql-tap/limit.test.lua      |  2 +-
 test/sql-tap/resolver01.test.lua |  2 +-
 test/sql-tap/select2.test.lua    |  8 +++----
 test/sql-tap/select6.test.lua    |  4 ++--
 test/sql-tap/select9.test.lua    |  6 ++---
 test/sql-tap/subquery.test.lua   |  4 ++--
 test/sql-tap/tkt2832.test.lua    |  2 +-
 18 files changed, 103 insertions(+), 83 deletions(-)

diff --git a/src/box/sql/expr.c b/src/box/sql/expr.c
index c5ec55de2..9a9083392 100644
--- a/src/box/sql/expr.c
+++ b/src/box/sql/expr.c
@@ -727,10 +727,12 @@ codeVectorCompare(Parse * pParse,	/* Code generator context */
 		}
 		if (opx == TK_EQ) {
 			sqlVdbeAddOp2(v, OP_IfNot, dest, addrDone);
+			sqlVdbeChangeP5(v, OPFLAG_BOOLREQ);
 			VdbeCoverage(v);
 			p5 |= SQL_KEEPNULL;
 		} else if (opx == TK_NE) {
 			sqlVdbeAddOp2(v, OP_If, dest, addrDone);
+			sqlVdbeChangeP5(v, OPFLAG_BOOLREQ);
 			VdbeCoverage(v);
 			p5 |= SQL_KEEPNULL;
 		} else {
@@ -1083,16 +1085,11 @@ sqlPExprAddSelect(Parse * pParse, Expr * pExpr, Select * pSelect)
 	}
 }
 
-/*
+/**
  * If the expression is always either TRUE or FALSE (respectively),
- * then return 1.  If one cannot determine the truth value of the
+ * then return 1. If one cannot determine the truth value of the
  * expression at compile-time return 0.
  *
- * This is an optimization.  If is OK to return 0 here even if
- * the expression really is always false or false (a false negative).
- * But it is a bug to return 1 if the expression might have different
- * boolean values in different circumstances (a false positive.)
- *
  * Note that if the expression is part of conditional for a
  * LEFT JOIN, then we cannot determine at compile-time whether or not
  * is it true or false, so always return 0.
@@ -1100,23 +1097,21 @@ sqlPExprAddSelect(Parse * pParse, Expr * pExpr, Select * pSelect)
 static int
 exprAlwaysTrue(Expr * p)
 {
-	int v = 0;
 	if (ExprHasProperty(p, EP_FromJoin))
 		return 0;
-	if (!sqlExprIsInteger(p, &v))
-		return 0;
-	return v != 0;
+	if (p->op == TK_TRUE)
+		return 1;
+	return 0;
 }
 
 static int
 exprAlwaysFalse(Expr * p)
 {
-	int v = 0;
 	if (ExprHasProperty(p, EP_FromJoin))
 		return 0;
-	if (!sqlExprIsInteger(p, &v))
-		return 0;
-	return v == 0;
+	if (p->op == TK_FALSE)
+		return 1;
+	return 0;
 }
 
 struct Expr *
@@ -4744,7 +4739,7 @@ exprCodeBetween(Parse * pParse,	/* Parsing and code generating context */
  * continues straight thru if the expression is false.
  *
  * If the expression evaluates to NULL (neither true nor false), then
- * take the jump if the jumpIfNull flag is SQL_JUMPIFNULL.
+ * take the jump if the flag is SQL_JUMPIFNULL.
  *
  * This code depends on the fact that certain token values (ex: TK_EQ)
  * are the same as opcode values (ex: OP_Eq) that implement the corresponding
@@ -4753,14 +4748,14 @@ exprCodeBetween(Parse * pParse,	/* Parsing and code generating context */
  * below verify that the numbers are aligned correctly.
  */
 void
-sqlExprIfTrue(Parse * pParse, Expr * pExpr, int dest, int jumpIfNull)
+sqlExprIfTrue(Parse * pParse, Expr * pExpr, int dest, int flags)
 {
 	Vdbe *v = pParse->pVdbe;
 	int op = 0;
 	int regFree1 = 0;
 	int regFree2 = 0;
 	int r1, r2;
-
+	int jumpIfNull = flags & SQL_JUMPIFNULL;
 	assert(jumpIfNull == SQL_JUMPIFNULL || jumpIfNull == 0);
 	if (NEVER(v == 0))
 		return;		/* Existence of VDBE checked by caller */
@@ -4896,18 +4891,22 @@ sqlExprIfTrue(Parse * pParse, Expr * pExpr, int dest, int jumpIfNull)
  * continues straight thru if the expression is true.
  *
  * If the expression evaluates to NULL (neither true nor false) then
- * jump if jumpIfNull is SQL_JUMPIFNULL or fall through if jumpIfNull
- * is 0.
+ * jump if flags contains SQL_JUMPIFNULL or fall through if it doesn't.
+ *
+ * IF flags contains SQL_BOOLREQ then OP_If(Not) is supplied with
+ * flag OPFLAG_BOOLREQ which forces additional verification of
+ * its arguments. It is required to make sure that searching
+ * condition is boolean (to disallow queries like ... WHERE 1+1;).
  */
 void
-sqlExprIfFalse(Parse * pParse, Expr * pExpr, int dest, int jumpIfNull)
+sqlExprIfFalse(Parse * pParse, Expr * pExpr, int dest, int flags)
 {
 	Vdbe *v = pParse->pVdbe;
 	int op = 0;
 	int regFree1 = 0;
 	int regFree2 = 0;
 	int r1, r2;
-
+	int jumpIfNull = flags & SQL_JUMPIFNULL;
 	assert(jumpIfNull == SQL_JUMPIFNULL || jumpIfNull == 0);
 	if (NEVER(v == 0))
 		return;		/* Existence of VDBE checked by caller */
@@ -5072,6 +5071,12 @@ sqlExprIfFalse(Parse * pParse, Expr * pExpr, int dest, int jumpIfNull)
 							 &regFree1);
 				sqlVdbeAddOp3(v, OP_IfNot, r1, dest,
 						  jumpIfNull != 0);
+				/*
+				 * Make sure that search condition
+				 * under WHERE clause returns boolean.
+				 */
+				if ((flags & SQL_BOOLREQ) != 0)
+					sqlVdbeChangeP5(v, OPFLAG_BOOLREQ);
 				VdbeCoverage(v);
 				testcase(regFree1 == 0);
 				testcase(jumpIfNull == 0);
diff --git a/src/box/sql/sqlInt.h b/src/box/sql/sqlInt.h
index 10bdb0597..488ef639f 100644
--- a/src/box/sql/sqlInt.h
+++ b/src/box/sql/sqlInt.h
@@ -1782,6 +1782,7 @@ struct Savepoint {
 #define SQL_KEEPNULL     0x40	/* Used by vector == or <> */
 #define SQL_NULLEQ       0x80	/* NULL=NULL */
 #define SQL_NOTNULL      0x90	/* Assert that operands are never NULL */
+#define SQL_BOOLREQ      0x100	/* Argument passed to OP_If must be boolean */
 
 /**
  * Return logarithm of tuple count in space.
@@ -2762,6 +2763,7 @@ struct Parse {
 #define OPFLAG_SYSTEMSP      0x20	/* OP_Open**: set if space pointer
 					 * points to system space.
 					 */
+#define OPFLAG_BOOLREQ       0x1000	/* OP_IF(Not): operand must be boolean. */
 
 /**
  * Prepare vdbe P5 flags for OP_{IdxInsert, IdxReplace, Update}
diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
index 5b86be522..717f56803 100644
--- a/src/box/sql/vdbe.c
+++ b/src/box/sql/vdbe.c
@@ -2541,13 +2541,18 @@ case OP_Once: {             /* jump */
 	break;
 }
 
-/* Opcode: If P1 P2 P3 * *
+/* Opcode: If P1 P2 P3 * P5
  *
  * Jump to P2 if the value in register P1 is true.  The value
  * is considered true if it is numeric and non-zero.  If the value
  * in P1 is NULL then take the jump if and only if P3 is non-zero.
+ *
+ * In case P5 contains BOOLREQ flag, then argument is supposed
+ * to be BOOLEAN. Otherwise, an error is raised. Such check is
+ * required to restrict <search condition> used in WHERE and
+ * JOIN clauses allowing only boolean values.
  */
-/* Opcode: IfNot P1 P2 P3 * *
+/* Opcode: IfNot P1 P2 P3 * P5
  *
  * Jump to P2 if the value in register P1 is False.  The value
  * is considered false if it has a numeric value of zero.  If the value
@@ -2562,6 +2567,12 @@ case OP_IfNot: {            /* jump, in1 */
 	} else if ((pIn1->flags & MEM_Bool) != 0) {
 		c = pOp->opcode==OP_IfNot ? ! pIn1->u.b : pIn1->u.b;
 	} else {
+		if (pOp->p5 == OPFLAG_BOOLREQ) {
+			diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
+				 sql_value_text(pIn1), "boolean");
+			rc = SQL_TARANTOOL_ERROR;
+			goto abort_due_to_error;
+		}
 		double v;
 		if (sqlVdbeRealValue(pIn1, &v) != 0) {
 			diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
diff --git a/src/box/sql/where.c b/src/box/sql/where.c
index 19ee2d03a..93020b148 100644
--- a/src/box/sql/where.c
+++ b/src/box/sql/where.c
@@ -4349,7 +4349,8 @@ sqlWhereBegin(Parse * pParse,	/* The parser context */
 		if (nTabList == 0
 		    || sqlExprIsConstantNotJoin(sWLB.pWC->a[ii].pExpr)) {
 			sqlExprIfFalse(pParse, sWLB.pWC->a[ii].pExpr,
-					   pWInfo->iBreak, SQL_JUMPIFNULL);
+				       pWInfo->iBreak,
+				       SQL_JUMPIFNULL | SQL_BOOLREQ);
 			sWLB.pWC->a[ii].wtFlags |= TERM_CODED;
 		}
 	}
diff --git a/src/box/sql/wherecode.c b/src/box/sql/wherecode.c
index a453fe979..5ee2efce7 100644
--- a/src/box/sql/wherecode.c
+++ b/src/box/sql/wherecode.c
@@ -1613,7 +1613,8 @@ sqlWhereCodeOneLoopStart(WhereInfo * pWInfo,	/* Complete information about the W
 			 */
 			continue;
 		}
-		sqlExprIfFalse(pParse, pE, addrCont, SQL_JUMPIFNULL);
+		sqlExprIfFalse(pParse, pE, addrCont,
+			       SQL_JUMPIFNULL | SQL_BOOLREQ);
 		if (skipLikeAddr)
 			sqlVdbeJumpHere(v, skipLikeAddr);
 		pTerm->wtFlags |= TERM_CODED;
diff --git a/test/sql-tap/e_delete.test.lua b/test/sql-tap/e_delete.test.lua
index a58dc87c7..374a7d3e4 100755
--- a/test/sql-tap/e_delete.test.lua
+++ b/test/sql-tap/e_delete.test.lua
@@ -89,15 +89,15 @@ test:do_delete_tests("e_delete-1.1", {
 -- NULL are retained.
 --
 test:do_delete_tests("e_delete-1.2", {
-    {1, "DELETE FROM t3 WHERE 1       ; SELECT x FROM t3", {}},
-    {2, "DELETE FROM t4 WHERE 0  ; SELECT x FROM t4", {1, 2, 3, 4, 5}},
-    {3, "DELETE FROM t4 WHERE 0.0     ; SELECT x FROM t4", {1, 2, 3, 4, 5}},
+    {1, "DELETE FROM t3 WHERE true       ; SELECT x FROM t3", {}},
+    {2, "DELETE FROM t4 WHERE false  ; SELECT x FROM t4", {1, 2, 3, 4, 5}},
+    {3, "DELETE FROM t4 WHERE false    ; SELECT x FROM t4", {1, 2, 3, 4, 5}},
     {4, "DELETE FROM t4 WHERE NULL    ; SELECT x FROM t4", {1, 2, 3, 4, 5}},
     {5, "DELETE FROM t4 WHERE y!='two'; SELECT x FROM t4", {2}},
     {6, "DELETE FROM t4 WHERE y='two' ; SELECT x FROM t4", {}},
     {7, "DELETE FROM t5 WHERE x=(SELECT max(x) FROM t5);SELECT x FROM t5", {1, 2, 3, 4}},
     {8, "DELETE FROM t5 WHERE (SELECT max(x) FROM t4)  ;SELECT x FROM t5", {1, 2, 3, 4}},
-    {9, "DELETE FROM t5 WHERE (SELECT max(x) FROM t6)  ;SELECT x FROM t5", {}},
+    {9, "DELETE FROM t5 WHERE (SELECT max(x) FROM t6) != 0  ;SELECT x FROM t5", {}},
     {10, "DELETE FROM t6 WHERE y>'seven' ; SELECT y FROM t6", {"one", "four", "five"}},
 })
 
diff --git a/test/sql-tap/e_select1.test.lua b/test/sql-tap/e_select1.test.lua
index 970eeeed9..e47b0f43d 100755
--- a/test/sql-tap/e_select1.test.lua
+++ b/test/sql-tap/e_select1.test.lua
@@ -136,13 +136,13 @@ test:do_select_tests(
         {"1100.1", "SELECT DISTINCT a, b, a||b FROM t1 ", {"a", "one", "aone", "b", "two", "btwo", "c", "three", "cthree"}},
         {"1200.1", "SELECT ALL a, b, a||b FROM t1 ", {"a", "one", "aone", "b", "two", "btwo", "c", "three", "cthree"}},
 
-        {"0010.1", "SELECT 1, 2, 3 WHERE 1 ", {1, 2, 3}},
-        {"0010.2", "SELECT 1, 2, 3 WHERE 0 ", {}},
+        {"0010.1", "SELECT 1, 2, 3 WHERE true ", {1, 2, 3}},
+        {"0010.2", "SELECT 1, 2, 3 WHERE false ", {}},
         {"0010.3", "SELECT 1, 2, 3 WHERE NULL ", {}},
 
-        {"1010.1", "SELECT DISTINCT 1, 2, 3 WHERE 1 ", {1, 2, 3}},
+        {"1010.1", "SELECT DISTINCT 1, 2, 3 WHERE true ", {1, 2, 3}},
 
-        {"2010.1", "SELECT ALL 1, 2, 3 WHERE 1 ", {1, 2, 3}},
+        {"2010.1", "SELECT ALL 1, 2, 3 WHERE true ", {1, 2, 3}},
 
         {"0110.1", "SELECT a, b, a||b FROM t1 WHERE a!='x' ", {"a", "one", "aone", "b", "two", "btwo", "c", "three", "cthree"}},
         {"0110.2", "SELECT a, b, a||b FROM t1 WHERE a=='x'", {}},
@@ -175,28 +175,28 @@ test:do_select_tests(
         {"2102.1", "SELECT ALL count(*), max(a) FROM t1 GROUP BY b HAVING count(*)=1", {1, "a", 1, "c", 1, "b"}},
         {"2102.2", "SELECT ALL count(*), max(a) FROM t1 GROUP BY b HAVING count(*)=2", {}},
 
-        {"0011.1", "SELECT 1, 2, 3 WHERE 1 GROUP BY 2", {1, 2, 3}},
-        {"0012.1", "SELECT 1, 2, 3 WHERE 0 GROUP BY 2 HAVING count(*)=1", {}},
-        {"0012.2", "SELECT 1, 2, 3 WHERE 0 GROUP BY 2 HAVING count(*)>1", {}},
+        {"0011.1", "SELECT 1, 2, 3 WHERE true GROUP BY 2", {1, 2, 3}},
+        {"0012.1", "SELECT 1, 2, 3 WHERE false GROUP BY 2 HAVING count(*)=1", {}},
+        {"0012.2", "SELECT 1, 2, 3 WHERE false GROUP BY 2 HAVING count(*)>1", {}},
 
-        {"1011.1", "SELECT DISTINCT 1, 2, 3 WHERE 0 GROUP BY 2", {}},
-        {"1012.1", "SELECT DISTINCT 1, 2, 3 WHERE 1 GROUP BY 2 HAVING count(*)=1", {1, 2, 3}},
+        {"1011.1", "SELECT DISTINCT 1, 2, 3 WHERE false GROUP BY 2", {}},
+        {"1012.1", "SELECT DISTINCT 1, 2, 3 WHERE true GROUP BY 2 HAVING count(*)=1", {1, 2, 3}},
         {"1012.2", "SELECT DISTINCT 1, 2, 3 WHERE NULL GROUP BY 2 HAVING count(*)>1", {}},
 
-        {"2011.1", "SELECT ALL 1, 2, 3 WHERE 1 GROUP BY 2", {1, 2, 3}},
-        {"2012.1", "SELECT ALL 1, 2, 3 WHERE 0 GROUP BY 2 HAVING count(*)=1", {}},
-        {"2012.2", "SELECT ALL 1, 2, 3 WHERE 2 GROUP BY 2 HAVING count(*)>1", {}},
+        {"2011.1", "SELECT ALL 1, 2, 3 WHERE true GROUP BY 2", {1, 2, 3}},
+        {"2012.1", "SELECT ALL 1, 2, 3 WHERE false GROUP BY 2 HAVING count(*)=1", {}},
+        {"2012.2", "SELECT ALL 1, 2, 3 WHERE true GROUP BY 2 HAVING count(*)>1", {}},
 
         {"0111.1", "SELECT count(*), max(a) FROM t1 WHERE a='a' GROUP BY b", {1, "a"}},
         {"0112.1", "SELECT count(*), max(a) FROM t1 WHERE a='c' GROUP BY b HAVING count(*)=1", {1, "c"}},
-        {"0112.2", "SELECT count(*), max(a) FROM t1 WHERE 0 GROUP BY b HAVING count(*)=2", { }},
+        {"0112.2", "SELECT count(*), max(a) FROM t1 WHERE false GROUP BY b HAVING count(*)=2", { }},
         {"1111.1", "SELECT DISTINCT count(*), max(a) FROM t1 WHERE a<'c' GROUP BY b", {1, "a", 1, "b"}},
         {"1112.1", "SELECT DISTINCT count(*), max(a) FROM t1 WHERE a>'a' GROUP BY b HAVING count(*)=1", {1, "c", 1, "b"}},
-        {"1112.2", "SELECT DISTINCT count(*), max(a) FROM t1 WHERE 0 GROUP BY b HAVING count(*)=2", { }},
+        {"1112.2", "SELECT DISTINCT count(*), max(a) FROM t1 WHERE false GROUP BY b HAVING count(*)=2", { }},
 
         {"2111.1", "SELECT ALL count(*), max(a) FROM t1 WHERE b>'one' GROUP BY b", {1, "c", 1, "b"}},
         {"2112.1", "SELECT ALL count(*), max(a) FROM t1 WHERE a!='b' GROUP BY b HAVING count(*)=1", {1, "a", 1, "c"}},
-        {"2112.2", "SELECT ALL count(*), max(a) FROM t1 WHERE 0 GROUP BY b HAVING count(*)=2", { }},
+        {"2112.2", "SELECT ALL count(*), max(a) FROM t1 WHERE false GROUP BY b HAVING count(*)=2", { }},
     })
 
 -- -- syntax diagram result-column
@@ -294,8 +294,8 @@ test:do_select_tests(
         {"2", "SELECT 'abc' WHERE NULL", {}},
         {"3", "SELECT NULL", {""}},
         {"4", "SELECT count(*)", {1}},
-        {"5", "SELECT count(*) WHERE 0", {0}},
-        {"6", "SELECT count(*) WHERE 1", {1}},
+        {"5", "SELECT count(*) WHERE false", {0}},
+        {"6", "SELECT count(*) WHERE true", {1}},
     })
 
 --
@@ -448,15 +448,15 @@ test:do_select_tests(
 -- true are included from the dataset.
 --
 local data ={
-    {"1"," SELECT * FROM t1 JOIN_PATTERN t2 ON (1) ",t1_cross_t2},
-    {"2"," SELECT * FROM t1 JOIN_PATTERN t2 ON (0) ",{}},
+    {"1"," SELECT * FROM t1 JOIN_PATTERN t2 ON (true) ",t1_cross_t2},
+    {"2"," SELECT * FROM t1 JOIN_PATTERN t2 ON (false) ",{}},
     {"3"," SELECT * FROM t1 JOIN_PATTERN t2 ON (NULL) ",{}},
-    {"6"," SELECT * FROM t1 JOIN_PATTERN t2 ON (0.9) ",t1_cross_t2},
-    {"7"," SELECT * FROM t1 JOIN_PATTERN t2 ON ('0.9') ",t1_cross_t2},
-    {"8"," SELECT * FROM t1 JOIN_PATTERN t2 ON (0.0) ",{}},
+    {"6"," SELECT * FROM t1 JOIN_PATTERN t2 ON (true) ",t1_cross_t2},
+    {"7"," SELECT * FROM t1 JOIN_PATTERN t2 ON (true) ",t1_cross_t2},
+    {"8"," SELECT * FROM t1 JOIN_PATTERN t2 ON (true) ",t1_cross_t2},
     {"9"," SELECT t1.b, t2.b FROM t1 JOIN_PATTERN t2 ON (t1.a = t2.a) ",{"one", "I", "two", "II", "three", "III"}},
     {"10"," SELECT t1.b, t2.b FROM t1 JOIN_PATTERN t2 ON (t1.a = 'a') ",{"one", "I", "one", "II", "one", "III"}},
-    {"11"," SELECT t1.b, t2.b FROM t1 JOIN_PATTERN t2 ON (CASE WHEN t1.a = 'a' THEN NULL ELSE 1 END)",
+    {"11"," SELECT t1.b, t2.b FROM t1 JOIN_PATTERN t2 ON (CASE WHEN t1.a = 'a' THEN NULL ELSE true END)",
     {"two", "I", "two", "II", "two", "III", "three", "I", "three", "II", "three", "III"}},
 }
 for _, val in ipairs(data) do
@@ -725,7 +725,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "e_select-3.2.3",
     [[
-        SELECT k FROM x1 NATURAL JOIN x2 WHERE x2.k
+        SELECT k FROM x1 NATURAL JOIN x2 WHERE x2.k <> 0
     ]], {
         -- <e_select-3.2.3>
         3
@@ -735,7 +735,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "e_select-3.2.4",
     [[
-        SELECT k FROM x1 NATURAL JOIN x2 WHERE x2.k-3
+        SELECT k FROM x1 NATURAL JOIN x2 WHERE x2.k-3 <> 0
     ]], {
         -- <e_select-3.2.4>
 
@@ -952,7 +952,7 @@ test:do_select_tests(
 test:do_select_tests(
     "e_select-4.7",
     {
-        {"1", "SELECT one, two, count(*) FROM a1 WHERE 0", {"", "", 0}},
+        {"1", "SELECT one, two, count(*) FROM a1 WHERE false", {"", "", 0}},
         {"2", "SELECT sum(two), * FROM a1, a2 WHERE three>5", {"", "", "", "", ""}},
         {"3", "SELECT max(one) IS NULL, one IS NULL, two IS NULL FROM a1 WHERE two=7", {1, 1, 1}},
     })
diff --git a/test/sql-tap/func.test.lua b/test/sql-tap/func.test.lua
index 2df832b1b..0cdcdfde6 100755
--- a/test/sql-tap/func.test.lua
+++ b/test/sql-tap/func.test.lua
@@ -1739,7 +1739,7 @@ test:do_catchsql_test(
 test:do_execsql_test(
     "func-19.1",
     [[
-        SELECT match(a,b) FROM t1 WHERE 0;
+        SELECT match(a,b) FROM t1 WHERE false;
     ]], {
         -- <func-19.1>
         
diff --git a/test/sql-tap/in1.test.lua b/test/sql-tap/in1.test.lua
index 08a7c3628..76112cff9 100755
--- a/test/sql-tap/in1.test.lua
+++ b/test/sql-tap/in1.test.lua
@@ -207,7 +207,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "in-2.10",
     [[
-        SELECT a FROM t1 WHERE min(0,b IN (a,30))
+        SELECT a FROM t1 WHERE min(0,b IN (a,30)) <> 0
     ]], {
         -- <in-2.10>
         
diff --git a/test/sql-tap/in3.test.lua b/test/sql-tap/in3.test.lua
index 988a9b2a7..a5e31f8a7 100755
--- a/test/sql-tap/in3.test.lua
+++ b/test/sql-tap/in3.test.lua
@@ -116,7 +116,7 @@ test:do_test(
 test:do_test(
     "in3-1.8",
     function()
-        return exec_neph(" SELECT a FROM t1 WHERE a IN (SELECT a FROM t1 WHERE 1); ")
+        return exec_neph(" SELECT a FROM t1 WHERE a IN (SELECT a FROM t1 WHERE true); ")
     end, {
         -- <in3-1.8>
         1, 1, 3, 5
diff --git a/test/sql-tap/join5.test.lua b/test/sql-tap/join5.test.lua
index 000370d84..c18be5230 100755
--- a/test/sql-tap/join5.test.lua
+++ b/test/sql-tap/join5.test.lua
@@ -98,7 +98,7 @@ test:do_test(
             INSERT INTO xy VALUES(2,3);
             INSERT INTO xy VALUES(NULL,1);
         ]]
-        return test:execsql "SELECT * FROM xy LEFT JOIN ab ON 0"
+        return test:execsql "SELECT * FROM xy LEFT JOIN ab ON false"
     end, {
         -- <join5-2.1>
         "", 1, "", "", 2, 3, "", ""
@@ -108,7 +108,7 @@ test:do_test(
 test:do_execsql_test(
     "join5-2.2",
     [[
-        SELECT * FROM xy LEFT JOIN ab ON 1
+        SELECT * FROM xy LEFT JOIN ab ON true
     ]], {
         -- <join5-2.2>
         "", 1, 1, 2, "", 1, 3, "", 2, 3, 1, 2, 2, 3, 3, ""
@@ -128,7 +128,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "join5-2.4",
     [[
-        SELECT * FROM xy LEFT JOIN ab ON 0 WHERE 0
+        SELECT * FROM xy LEFT JOIN ab ON false WHERE false
     ]], {
         -- <join5-2.4>
         
@@ -138,7 +138,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "join5-2.5",
     [[
-        SELECT * FROM xy LEFT JOIN ab ON 1 WHERE 0
+        SELECT * FROM xy LEFT JOIN ab ON true WHERE false
     ]], {
         -- <join5-2.5>
         
@@ -148,7 +148,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "join5-2.6",
     [[
-        SELECT * FROM xy LEFT JOIN ab ON NULL WHERE 0
+        SELECT * FROM xy LEFT JOIN ab ON NULL WHERE false
     ]], {
         -- <join5-2.6>
         
@@ -158,7 +158,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "join5-2.7",
     [[
-        SELECT * FROM xy LEFT JOIN ab ON 0 WHERE 1
+        SELECT * FROM xy LEFT JOIN ab ON false WHERE true
     ]], {
         -- <join5-2.7>
         "", 1, "", "", 2, 3, "", ""
@@ -168,7 +168,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "join5-2.8",
     [[
-        SELECT * FROM xy LEFT JOIN ab ON 1 WHERE 1
+        SELECT * FROM xy LEFT JOIN ab ON true WHERE true
     ]], {
         -- <join5-2.8>
         "",1 ,1, 2, "", 1, 3, "", 2, 3, 1, 2, 2, 3, 3, ""
@@ -178,7 +178,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "join5-2.9",
     [[
-        SELECT * FROM xy LEFT JOIN ab ON NULL WHERE 1
+        SELECT * FROM xy LEFT JOIN ab ON NULL WHERE true
     ]], {
         -- <join5-2.9>
         "", 1, "", "", 2, 3, "", ""
@@ -188,7 +188,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "join5-2.10",
     [[
-        SELECT * FROM xy LEFT JOIN ab ON 0 WHERE NULL
+        SELECT * FROM xy LEFT JOIN ab ON false WHERE NULL
     ]], {
         -- <join5-2.10>
         
@@ -198,7 +198,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "join5-2.11",
     [[
-        SELECT * FROM xy LEFT JOIN ab ON 1 WHERE NULL
+        SELECT * FROM xy LEFT JOIN ab ON true WHERE NULL
     ]], {
         -- <join5-2.11>
         
diff --git a/test/sql-tap/limit.test.lua b/test/sql-tap/limit.test.lua
index 632c63402..9b728d8e0 100755
--- a/test/sql-tap/limit.test.lua
+++ b/test/sql-tap/limit.test.lua
@@ -328,7 +328,7 @@ test:do_execsql_test(
     "limit-5.3",
     [[
         DELETE FROM t5;
-        INSERT INTO t5 SELECT id, x-y, x+y FROM t1 WHERE x ORDER BY x DESC LIMIT 31;
+        INSERT INTO t5 SELECT id, x-y, x+y FROM t1 WHERE x <> 0 ORDER BY x DESC LIMIT 31;
         SELECT x, y FROM t5 ORDER BY x LIMIT 2;
     ]], {
         -- <limit-5.3>
diff --git a/test/sql-tap/resolver01.test.lua b/test/sql-tap/resolver01.test.lua
index aa383b34b..9fcf0c7c0 100755
--- a/test/sql-tap/resolver01.test.lua
+++ b/test/sql-tap/resolver01.test.lua
@@ -347,7 +347,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "resolver01-7.1",
     [[
-        SELECT 2 AS x WHERE (SELECT x AS y WHERE 3>y);
+        SELECT 2 AS x WHERE (SELECT x AS y WHERE 3>y) <> 0;
     ]], {
         -- <resolver01-7.1>
         2
diff --git a/test/sql-tap/select2.test.lua b/test/sql-tap/select2.test.lua
index c7f1e5d86..7f0188d6f 100755
--- a/test/sql-tap/select2.test.lua
+++ b/test/sql-tap/select2.test.lua
@@ -212,7 +212,7 @@ test:do_execsql_test(
     "select2-4.2",
     [[
         INSERT INTO bb VALUES(0);
-        SELECT * FROM aa CROSS JOIN bb WHERE b;
+        SELECT * FROM aa CROSS JOIN bb WHERE b <> 0;
     ]], {
         -- <select2-4.2>
         1, 2, 1, 4, 3, 2, 3, 4
@@ -232,7 +232,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "select2-4.4",
     [[
-        SELECT * FROM aa, bb WHERE min(a,b);
+        SELECT * FROM aa, bb WHERE min(a,b) <> 0;
     ]], {
         -- <select2-4.4>
         1, 2, 1, 4, 3, 2, 3, 4
@@ -252,7 +252,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "select2-4.6",
     [[
-        SELECT * FROM aa, bb WHERE CASE WHEN a=b-1 THEN 1 END;
+        SELECT * FROM aa, bb WHERE CASE WHEN a=b-1 THEN true END;
     ]], {
         -- <select2-4.6>
         1, 2, 3, 4
@@ -262,7 +262,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "select2-4.7",
     [[
-        SELECT * FROM aa, bb WHERE CASE WHEN a=b-1 THEN 0 ELSE 1 END;
+        SELECT * FROM aa, bb WHERE CASE WHEN a=b-1 THEN false ELSE true END;
     ]], {
         -- <select2-4.7>
         1, 0, 1, 4, 3, 0, 3, 2
diff --git a/test/sql-tap/select6.test.lua b/test/sql-tap/select6.test.lua
index 7f6cc7939..c9960dc29 100755
--- a/test/sql-tap/select6.test.lua
+++ b/test/sql-tap/select6.test.lua
@@ -632,7 +632,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "select6-7.3",
     [[
-        SELECT c,b,a,* FROM (SELECT 1 AS a, 2 AS b, 'abc' AS c WHERE 0)
+        SELECT c,b,a,* FROM (SELECT 1 AS a, 2 AS b, 'abc' AS c WHERE false)
     ]], {
         -- <select6-7.3>
         
@@ -642,7 +642,7 @@ test:do_execsql_test(
 test:do_execsql2_test(
     "select6-7.4",
     [[
-        SELECT c,b,a,* FROM (SELECT 1 AS a, 2 AS b, 'abc' AS c WHERE 1)
+        SELECT c,b,a,* FROM (SELECT 1 AS a, 2 AS b, 'abc' AS c WHERE true)
     ]], {
         -- <select6-7.4>
         "C", "abc", "B", 2, "A", 1, "A", 1, "B", 2, "C", "abc"
diff --git a/test/sql-tap/select9.test.lua b/test/sql-tap/select9.test.lua
index a54010f89..1ae16a658 100755
--- a/test/sql-tap/select9.test.lua
+++ b/test/sql-tap/select9.test.lua
@@ -541,7 +541,7 @@ test:do_execsql_test(
         CREATE TABLE t62(b INT primary key);
         INSERT INTO t61 VALUES(111);
         INSERT INTO t62 VALUES(222);
-        SELECT a FROM t61 WHERE 0 UNION SELECT b FROM t62;
+        SELECT a FROM t61 WHERE false UNION SELECT b FROM t62;
     ]], {
         -- <select9-6.1>
         222
@@ -551,7 +551,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "select9-6.2",
     [[
-        SELECT a FROM t61 WHERE 0 UNION ALL SELECT b FROM t62;
+        SELECT a FROM t61 WHERE false UNION ALL SELECT b FROM t62;
     ]], {
         -- <select9-6.2>
         222
@@ -561,7 +561,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "select9-6.3",
     [[
-        SELECT a FROM t61 UNION SELECT b FROM t62 WHERE 0;
+        SELECT a FROM t61 UNION SELECT b FROM t62 WHERE false;
     ]], {
         -- <select9-6.3>
         111
diff --git a/test/sql-tap/subquery.test.lua b/test/sql-tap/subquery.test.lua
index 1532ee3d8..7a3e270dc 100755
--- a/test/sql-tap/subquery.test.lua
+++ b/test/sql-tap/subquery.test.lua
@@ -918,8 +918,8 @@ test:do_execsql_test(
 test:do_execsql_test(
     "subquery-8.1",
     [[
-        SELECT (SELECT 0 FROM (SELECT * FROM t1)) AS x WHERE x;
-        SELECT (SELECT 0 FROM (SELECT * FROM (SELECT 0))) AS x WHERE x;
+        SELECT (SELECT 0 FROM (SELECT * FROM t1)) AS x WHERE x <> 0;
+        SELECT (SELECT 0 FROM (SELECT * FROM (SELECT 0))) AS x WHERE x <> 0;
     ]], {
         -- <subquery-8.1>
         
diff --git a/test/sql-tap/tkt2832.test.lua b/test/sql-tap/tkt2832.test.lua
index d3f24b586..cd75cc507 100755
--- a/test/sql-tap/tkt2832.test.lua
+++ b/test/sql-tap/tkt2832.test.lua
@@ -90,7 +90,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "tkt2832-3.2",
     [[
-        DELETE FROM t3 WHERE 1
+        DELETE FROM t3 WHERE true
     ]], {
         -- <tkt2832-3.2>
 
-- 
2.15.1