[tarantool-patches] [PATCH] sql: export `sql_current_time` && enable tests

[tarantool-patches] [PATCH] sql: export `sql_current_time` && enable tests

[AKhatskevich]

branch: kh/gh-2646-current_time

------- commit message ------

`sql_current_time` is exported only in debug mode and used to check
builtin datetime sql functions behavior in specific time moments.

Closes #2646
---
 extra/exports                         |   4 ++
 src/box/sql/os_unix.c                 |   6 +-
 test/sql-tap/date.test.lua            |  33 ---------
 test/sql-tap/debug_mode_only.test.lua | 123 ++++++++++++++++++++++++++++++++++
 test/sql-tap/e_expr.test.lua          |  42 ------------
 test/sql-tap/suite.ini                |   1 +
 test/sql-tap/table.test.lua           |  48 +------------
 7 files changed, 132 insertions(+), 125 deletions(-)
 create mode 100755 test/sql-tap/debug_mode_only.test.lua

diff --git a/extra/exports b/extra/exports
index a274bb23b..6b76a512e 100644
--- a/extra/exports
+++ b/extra/exports
@@ -350,6 +350,10 @@ luaJIT_profile_start
 luaJIT_profile_stop
 luaJIT_profile_dumpstack
 
+# SQL testing
+
+sql_current_time
+
 # Temporary
 
 # Important! This function will be removed from exports soon (in the scope of
diff --git a/src/box/sql/os_unix.c b/src/box/sql/os_unix.c
index 9cb8a54d0..b178f6ced 100644
--- a/src/box/sql/os_unix.c
+++ b/src/box/sql/os_unix.c
@@ -4857,7 +4857,7 @@ unixSleep(sqlite3_vfs * NotUsed, int microseconds)
  * sqlite3OsCurrentTime() during testing.
  */
 #ifdef SQLITE_TEST
-int sqlite3_current_time = 0;	/* Fake system time in seconds since 1970. */
+int sql_current_time = 0;	/* Fake system time in seconds since 1970. */
 #endif
 
 /*
@@ -4889,9 +4889,9 @@ unixCurrentTimeInt64(sqlite3_vfs * NotUsed, sqlite3_int64 * piNow)
 #endif
 
 #ifdef SQLITE_TEST
-	if (sqlite3_current_time) {
+	if (sql_current_time) {
 		*piNow =
-		    1000 * (sqlite3_int64) sqlite3_current_time + unixEpoch;
+		    1000 * (sqlite3_int64) sql_current_time + unixEpoch;
 	}
 #endif
 	UNUSED_PARAMETER(NotUsed);
diff --git a/test/sql-tap/date.test.lua b/test/sql-tap/date.test.lua
index 347afb495..5c22bf606 100755
--- a/test/sql-tap/date.test.lua
+++ b/test/sql-tap/date.test.lua
@@ -127,9 +127,6 @@ datetest(2.36, "datetime('2003-10-22 12:24','+1 abcdef')", "NULL")
 datetest(2.37, "datetime('2003-10-22 12:24','+1 abcdefg')", "NULL")
 datetest(2.38, "datetime('2003-10-22 12:24','+1 abcdefgh')", "NULL")
 datetest(2.39, "datetime('2003-10-22 12:24','+1 abcdefghi')", "NULL")
---sqlite_current_time = 1199243045
---datetest(2.40, "datetime()", "2008-01-02 03:04:05")
---sqlite_current_time = 0
 datetest(2.41, "datetime('2003-10-22 12:24','23 seconds')", "2003-10-22 12:24:23")
 datetest(2.42, "datetime('2003-10-22 12:24','345 second')", "2003-10-22 12:29:45")
 datetest(2.43, "datetime('2003-10-22 12:24','4 second')", "2003-10-22 12:24:04")
@@ -211,10 +208,6 @@ end
 --
 datetest(3.20, "strftime('%d/%f/%H/%W/%j/%m/%M/%S/%Y','0421-01-02 03:04:05.006')", "02/05.006/03/00/002/01/04/05/0421")
 
--- TBI to be implemented sqlite_current_time
---sqlite_current_time = 1157124367
---datetest(4.1, "date('now')", "2006-09-01")
---sqlite_current_time = 0
 datetest(5.1, "datetime('1994-04-16 14:00:00 +05:00')", "1994-04-16 09:00:00")
 datetest(5.2, "datetime('1994-04-16 14:00:00 -05:15')", "1994-04-16 19:15:00")
 datetest(5.3, "datetime('1994-04-16 05:00:00 +08:30')", "1994-04-15 20:30:00")
@@ -349,32 +342,6 @@ datetest(7.13, "strftime(null,'now')", "NULL")
 datetest(7.14, "strftime('%s',null)", "NULL")
 datetest(7.15, "strftime('%s','now',null)", "NULL")
 datetest(7.16, "strftime('%s','now','localtime',null)", "NULL")
--- Test modifiers when the date begins as a julian day number - to
--- make sure the HH:MM:SS is preserved.  Ticket #551.
---
--- TBI to be implemented sqlite_current_time
---sqlite_current_time = test:execsql "SELECT strftime('%s','2003-10-22 12:34:00')"
---datetest(8.1, "datetime('now','weekday 0')", "2003-10-26 12:34:00")
---datetest(8.2, "datetime('now','weekday 1')", "2003-10-27 12:34:00")
---datetest(8.3, "datetime('now','weekday 2')", "2003-10-28 12:34:00")
---datetest(8.4, "datetime('now','weekday 3')", "2003-10-22 12:34:00")
---datetest(8.5, "datetime('now','start of month')", "2003-10-01 00:00:00")
---datetest(8.6, "datetime('now','start of year')", "2003-01-01 00:00:00")
---datetest(8.7, "datetime('now','start of day')", "2003-10-22 00:00:00")
---datetest(8.8, "datetime('now','1 day')", "2003-10-23 12:34:00")
---datetest(8.9, "datetime('now','+1 day')", "2003-10-23 12:34:00")
---datetest(8.10, "datetime('now','+1.25 day')", "2003-10-23 18:34:00")
---datetest(8.11, "datetime('now','-1.0 day')", "2003-10-21 12:34:00")
---datetest(8.12, "datetime('now','1 month')", "2003-11-22 12:34:00")
---datetest(8.13, "datetime('now','11 month')", "2004-09-22 12:34:00")
---datetest(8.14, "datetime('now','-13 month')", "2002-09-22 12:34:00")
---datetest(8.15, "datetime('now','1.5 months')", "2003-12-07 12:34:00")
---datetest(8.16, "datetime('now','-5 years')", "1998-10-22 12:34:00")
---datetest(8.17, "datetime('now','+10.5 minutes')", "2003-10-22 12:44:30")
---datetest(8.18, "datetime('now','-1.25 hours')", "2003-10-22 11:19:00")
---datetest(8.19, "datetime('now','11.25 seconds')", "2003-10-22 12:34:11")
---datetest(8.90, "datetime('now','abcdefghijklmnopqrstuvwyxzABCDEFGHIJLMNOP')", "NULL")
---sqlite_current_time = 0
 -- Negative years work.  Example:  '-4713-11-26' is JD 1.5.
 --
 datetest(9.1, "julianday('-4713-11-24 12:00:00')", "0")
diff --git a/test/sql-tap/debug_mode_only.test.lua b/test/sql-tap/debug_mode_only.test.lua
new file mode 100755
index 000000000..2b3f6a1a0
--- /dev/null
+++ b/test/sql-tap/debug_mode_only.test.lua
@@ -0,0 +1,123 @@
+#!/usr/bin/env tarantool
+-- This file contains testcases which can only be valudated in debug mode.
+test = require("sqltester")
+test:plan(30)
+
+local ffi = require("ffi")
+
+ffi.cdef("extern int sql_current_time;")
+
+local function datetest(tnum, expr, result)
+    test:do_test(
+        "date-"..tnum,
+        function ()
+            local r = test:execsql("SELECT coalesce( "..expr..", 'NULL' )")
+            return ""..r[1]
+        end,
+        result)
+end
+
+ffi.C.sql_current_time = 1157124367
+datetest(4.1, "date('now')", "2006-09-01")
+ffi.C.sql_current_time = 0
+ffi.C.sql_current_time = 1199243045
+datetest(2.40, "datetime()", "2008-01-02 03:04:05")
+ffi.C.sql_current_time = 0
+-- Test modifiers when the date begins as a julian day number - to
+-- make sure the HH:MM:SS is preserved.  Ticket #551.
+--
+-- TBI to be implemented ffi.C.sql_current_time
+ffi.C.sql_current_time =
+    tonumber(test:execsql("SELECT strftime('%s','2003-10-22 12:34:00')")[1])
+datetest(8.1, "datetime('now','weekday 0')", "2003-10-26 12:34:00")
+datetest(8.2, "datetime('now','weekday 1')", "2003-10-27 12:34:00")
+datetest(8.3, "datetime('now','weekday 2')", "2003-10-28 12:34:00")
+datetest(8.4, "datetime('now','weekday 3')", "2003-10-22 12:34:00")
+datetest(8.5, "datetime('now','start of month')", "2003-10-01 00:00:00")
+datetest(8.6, "datetime('now','start of year')", "2003-01-01 00:00:00")
+datetest(8.7, "datetime('now','start of day')", "2003-10-22 00:00:00")
+datetest(8.8, "datetime('now','1 day')", "2003-10-23 12:34:00")
+datetest(8.9, "datetime('now','+1 day')", "2003-10-23 12:34:00")
+datetest(8.10, "datetime('now','+1.25 day')", "2003-10-23 18:34:00")
+datetest(8.11, "datetime('now','-1.0 day')", "2003-10-21 12:34:00")
+datetest(8.12, "datetime('now','1 month')", "2003-11-22 12:34:00")
+datetest(8.13, "datetime('now','11 month')", "2004-09-22 12:34:00")
+datetest(8.14, "datetime('now','-13 month')", "2002-09-22 12:34:00")
+datetest(8.15, "datetime('now','1.5 months')", "2003-12-07 12:34:00")
+datetest(8.16, "datetime('now','-5 years')", "1998-10-22 12:34:00")
+datetest(8.17, "datetime('now','+10.5 minutes')", "2003-10-22 12:44:30")
+datetest(8.18, "datetime('now','-1.25 hours')", "2003-10-22 11:19:00")
+datetest(8.19, "datetime('now','11.25 seconds')", "2003-10-22 12:34:11")
+datetest(8.90, "datetime('now','abcdefghijklmnopqrstuvwyxzABCDEFGHIJLMNOP')", "NULL")
+ffi.C.sql_current_time = 0
+--
+-- Test the ability to have default values of CURRENT_TIME, CURRENT_DATE
+-- and CURRENT_TIMESTAMP.
+--
+test:do_execsql_test(
+    "table-13.1",
+    [[
+        CREATE TABLE tablet8(
+           a integer primary key,
+           tm text DEFAULT CURRENT_TIME,
+           dt text DEFAULT CURRENT_DATE,
+           dttm text DEFAULT CURRENT_TIMESTAMP
+        );
+        SELECT * FROM tablet8;
+    ]], {})
+
+local data = {
+    {"1976-07-04", "12:00:00", 205329600},
+    {"1994-04-16", "14:00:00", 766504800},
+    {"2000-01-01", "00:00:00", 946684800},
+    {"2003-12-31", "12:34:56", 1072874096},
+}
+for i ,val in ipairs(data) do
+    local date = val[1]
+    local time = val[2]
+    local seconds = val[3]
+    ffi.C.sql_current_time = seconds
+    test:do_execsql_test(
+        "table-13.2."..i,
+        string.format([[
+            INSERT INTO tablet8(a) VALUES(%s);
+            SELECT tm, dt, dttm FROM tablet8 WHERE a=%s;
+        ]], i, i), {
+            time, date, date .. " " .. time
+        })
+
+end
+ffi.C.sql_current_time = 1
+test:do_execsql_test(
+    "e_expr-12.2.6",
+    [[
+        SELECT CURRENT_TIME
+    ]], {
+        -- <e_expr-12.2.6>
+        "00:00:01"
+        -- </e_expr-12.2.6>
+    })
+
+test:do_execsql_test(
+    "e_expr-12.2.7",
+    [[
+        SELECT CURRENT_DATE
+    ]], {
+        -- <e_expr-12.2.7>
+        "1970-01-01"
+        -- </e_expr-12.2.7>
+    })
+
+test:do_execsql_test(
+    "e_expr-12.2.8",
+    [[
+        SELECT CURRENT_TIMESTAMP
+    ]], {
+        -- <e_expr-12.2.8>
+        "1970-01-01 00:00:01"
+        -- </e_expr-12.2.8>
+    })
+
+ffi.C.sql_current_time = 0
+
+test:finish_test()
diff --git a/test/sql-tap/e_expr.test.lua b/test/sql-tap/e_expr.test.lua
index d0f68954f..cafdaa053 100755
--- a/test/sql-tap/e_expr.test.lua
+++ b/test/sql-tap/e_expr.test.lua
@@ -1440,48 +1440,6 @@ test:do_execsql_test(
         -- </e_expr-12.2.5>
     })
 
--- MUST_WORK_TEST uses sqlite_current_time
-if 0>0 then
-    sqlite_current_time = 1
-    test:do_execsql_test(
-        "e_expr-12.2.6",
-        [[
-            SELECT CURRENT_TIME
-        ]], {
-            -- <e_expr-12.2.6>
-            "00:00:01"
-            -- </e_expr-12.2.6>
-        })
-
-    test:do_execsql_test(
-        "e_expr-12.2.7",
-        [[
-            SELECT CURRENT_DATE
-        ]], {
-            -- <e_expr-12.2.7>
-            "1970-01-01"
-            -- </e_expr-12.2.7>
-        })
-
-    test:do_execsql_test(
-        "e_expr-12.2.8",
-        [[
-            SELECT CURRENT_TIMESTAMP
-        ]], {
-            -- <e_expr-12.2.8>
-            "1970-01-01 00:00:01"
-            -- </e_expr-12.2.8>
-        })
-
-    sqlite_current_time = 0
-end
--- # -- syntax diagram expr
--- #
--- forcedelete test.db2
--- execsql {
---   ATTACH 'test.db2' AS dbname;
---   CREATE TABLE dbname.tblname(cname);
--- }
 test:execsql [[
     CREATE TABLE tblname(cname PRIMARY KEY);
 ]]
diff --git a/test/sql-tap/suite.ini b/test/sql-tap/suite.ini
index e259be2f0..43404d263 100644
--- a/test/sql-tap/suite.ini
+++ b/test/sql-tap/suite.ini
@@ -5,3 +5,4 @@ disabled =
 	reindex.test.lua ; This test is banned in scope of #2174
 lua_libs = lua/sqltester.lua ../sql/lua/sql_tokenizer.lua ../box/lua/identifier.lua
 is_parallel = True
+release_disabled = debug_mode_only.test.lua
diff --git a/test/sql-tap/table.test.lua b/test/sql-tap/table.test.lua
index 6e1056193..b5191ebec 100755
--- a/test/sql-tap/table.test.lua
+++ b/test/sql-tap/table.test.lua
@@ -1,6 +1,6 @@
 #!/usr/bin/env tarantool
 test = require("sqltester")
-test:plan(56)
+test:plan(55)
 
 --!./tcltestrunner.lua
 -- 2001 September 15
@@ -935,32 +935,6 @@ test:do_execsql_test(
         -- </table-13.1>
     })
 
--- MUST_WORK_TEST debug var sqlite_current_time #2646
-if (0 > 0) then
--- ["unset","-nocomplain","date","time","seconds"]
-local data = {
-    {"1976-07-04", "12:00:00", 205329600},
-    {"1994-04-16", "14:00:00", 766504800},
-    {"2000-01-01", "00:00:00", 946684800},
-    {"2003-12-31", "12:34:56", 1072874096},
-}
-for i ,val in ipairs(data) do
-    local date = val[1]
-    local time = val[2]
-    local seconds = val[3]
-    sqlite_current_time = seconds
-    test:do_execsql_test(
-        "table-13.2."..i,
-        string.format([[
-            INSERT INTO tablet8(a) VALUES(%s);
-            SELECT tm, dt, dttm FROM tablet8 WHERE a=%s;
-        ]], i, i), {
-            time, date, { date, time }
-        })
-
-end
-sqlite_current_time = 0
-end
 ----------------------------------------------------------------------
 -- Test cases table-14.*
 --
@@ -970,26 +944,6 @@ end
 -- But DROP TABLE is still prohibited because we do not want to
 -- delete a table out from under a running query.
 --
--- db eval {
---   pragma vdbe_trace = 0;
--- }
--- Try to create a table from within a callback:
--- ["unset","-nocomplain","result"]
-test:do_test(
-    "table-14.1",
-    function()
-        local rc = pcall(function()
-            test:execsql("SELECT * FROM tablet8 LIMIT 1")
-            test:execsql("CREATE TABLE t9(a primary key, b, c)")
-            end)
-        rc = rc == true and 0 or 1
-        return { rc }
-    end, {
-        -- <table-14.1>
-        0
-        -- </table-14.1>
-    })
-
 -- MUST_WORK_TEST database should be locked #2554
 if 0>0 then
 local function try_drop_t9()
-- 
2.14.1

[tarantool-patches] Re: [PATCH] sql: export `sql_current_time` && enable tests

[n.pettik]

> -- Test cases table-14.*
> --
> @@ -970,26 +944,6 @@ end
> -- But DROP TABLE is still prohibited because we do not want to
> -- delete a table out from under a running query.
> --
> --- db eval {
> ---   pragma vdbe_trace = 0;
> --- }
> --- Try to create a table from within a callback:
> --- ["unset","-nocomplain","result"]
> -test:do_test(
> -    "table-14.1",
> -    function()
> -        local rc = pcall(function()
> -            test:execsql("SELECT * FROM tablet8 LIMIT 1")
> -            test:execsql("CREATE TABLE t9(a primary key, b, c)")
> -            end)
> -        rc = rc == true and 0 or 1
> -        return { rc }
> -    end, {
> -        -- <table-14.1>
> -        0
> -        -- </table-14.1>
> -    })
> -

Idk why did you delete this test within current patch?

The rest seems to be OK.

[tarantool-patches] Re: [PATCH] sql: export `sql_current_time` && enable tests

[Alex Khatskevich]

On 26.06.2018 19:21, n.pettik wrote:
>> -- Test cases table-14.*
>> --
>> @@ -970,26 +944,6 @@ end
>> -- But DROP TABLE is still prohibited because we do not want to
>> -- delete a table out from under a running query.
>> --
>> --- db eval {
>> ---   pragma vdbe_trace = 0;
>> --- }
>> --- Try to create a table from within a callback:
>> --- ["unset","-nocomplain","result"]
>> -test:do_test(
>> -    "table-14.1",
>> -    function()
>> -        local rc = pcall(function()
>> -            test:execsql("SELECT * FROM tablet8 LIMIT 1")
>> -            test:execsql("CREATE TABLE t9(a primary key, b, c)")
>> -            end)
>> -        rc = rc == true and 0 or 1
>> -        return { rc }
>> -    end, {
>> -        -- <table-14.1>
>> -        0
>> -        -- </table-14.1>
>> -    })
>> -
> Idk why did you delete this test within current patch?
>
> The rest seems to be OK.
>
As far as I remember, test was testing ddl inside of a running 
transaction in SQLite with the help of
special TCL api.
However, those lines do not test anything at all and there is a 14.2 
test which does test this case.

1. I have added this text to the commit message.
2. I have rebased this branch on top of 2.0

[tarantool-patches] Re: [PATCH] sql: export `sql_current_time` && enable tests

[n.pettik]

> 1. I have added this text to the commit message.
> 2. I have rebased this branch on top of 2.0

Ok, then LGTM.

[tarantool-patches] Re: [PATCH] sql: export `sql_current_time` && enable tests

[Kirill Yukhin]

On 28 июн 00:53, n.pettik wrote:
> 
> > 1. I have added this text to the commit message.
> > 2. I have rebased this branch on top of 2.0
> 
> Ok, then LGTM.
Checked into 2.0 branch.

--
Regards, Kirill Yukhin