1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273
|
#!/usr/bin/env tarantool
test = require("sqltester")
test:plan(16)
--!./tcltestrunner.lua
-- 2001 September 15
--
-- The author disclaims copyright to this source code. In place of
-- a legal notice, here is a blessing:
--
-- May you do good and not evil.
-- May you find forgiveness for yourself and forgive others.
-- May you share freely, never taking more than you give.
--
-------------------------------------------------------------------------
-- This file implements regression tests for sql library. The
-- focus of this file is testing the SELECT statement.
--
-- $Id: select2.test,v 1.28 2009/01/15 15:23:59 drh Exp $
-- ["set","testdir",[["file","dirname",["argv0"]]]]
-- ["source",[["testdir"],"\/tester.tcl"]]
-- Create a table with some data
--
test:execsql "DROP TABLE IF EXISTS tbl1"
test:execsql "CREATE TABLE tbl1(id int, f1 int, f2 int, primary key(id))"
test:execsql "START TRANSACTION"
for i = 0, 30, 1 do
test:execsql(string.format("INSERT INTO tbl1 VALUES(%s, %s,%s)", i, i%9, i%10))
end
test:execsql "COMMIT"
-- Do a second query inside a first.
--
test:do_test(
"select2-1.1",
function()
local r = {}
local data = test:execsql "SELECT DISTINCT f1 FROM tbl1 ORDER BY f1"
for _, f1 in ipairs(data) do
table.insert(r, f1..":")
local data = test:execsql( string.format("SELECT f2 FROM tbl1 WHERE f1=%s ORDER BY f2", f1))
for _, f2 in ipairs(data) do
table.insert(r, f2)
end
end
return r
end, {
-- <select2-1.1>
"0:", 0, 7, 8, 9, "1:", 0, 1, 8, 9, "2:", 0, 1, 2, 9, "3:", 0, 1, 2, 3, "4:", 2, 3, 4, "5:", 3, 4, 5, "6:", 4, 5, 6, "7:", 5, 6, 7, "8:", 6, 7, 8
-- </select2-1.1>
})
test:do_test(
"select2-1.2",
function()
local r = {}
local data = test:execsql "SELECT DISTINCT f1 FROM tbl1 WHERE f1>3 AND f1<5"
for _, f1 in ipairs(data) do
table.insert(r, f1..":")
local data = test:execsql( string.format("SELECT f2 FROM tbl1 WHERE f1=%s ORDER BY f2", f1))
for _, f2 in ipairs(data) do
table.insert(r, f2)
end
end
return r
end, {
-- <select2-1.2>
"4:", 2, 3, 4
-- </select2-1.2>
})
-- it was test for tcl cache
-- only time comparition was removed from tcl version
test:execsql "DROP TABLE if exists tbl2"
test:do_test(
"select2-2.0.2",
function()
test:execsql "CREATE TABLE tbl2(f1 int primary key, f2 int, f3 int); START TRANSACTION;"
for i = 1, 30000, 1 do
test:execsql( string.format("INSERT INTO tbl2 VALUES(%s,%s,%s)",i, i*2, i*3))
end
test:execsql("COMMIT");
return {}
end, {
-- <select2-2.0.2>
-- </select2-2.0.2>
})
test:do_execsql_test(
"select2-2.1",
[[
SELECT count(*) FROM tbl2
]], {
-- <select2-2.1>
30000
-- </select2-2.1>
})
test:do_execsql_test(
"select2-2.2",
[[
SELECT count(*) FROM tbl2 WHERE f2>1000
]], {
-- <select2-2.2>
29500
-- </select2-2.2>
})
test:do_execsql_test(
"select2-3.1",
[[
SELECT f1 FROM tbl2 WHERE 1000=f2
]], {
-- <select2-3.1>
500
-- </select2-3.1>
})
test:do_execsql_test(
"select2-3.2a",
[[
CREATE INDEX idx1 ON tbl2(f2)
]], {
-- <select2-3.2a>
-- </select2-3.2a>
})
test:do_execsql_test(
"select2-3.2b",
[[
SELECT f1 FROM tbl2 WHERE 1000=f2
]], {
-- <select2-3.2b>
500
-- </select2-3.2b>
})
test:do_execsql_test(
"select2-3.2c",
[[
SELECT f1 FROM tbl2 WHERE f2=1000
]], {
-- <select2-3.2c>
500
-- </select2-3.2c>
})
-- TODO: need access to sql_search_count vatiable
--test:do_test(
-- "select2-3.2d",
-- function()
-- sql_search_count = 0
-- test:execsql "SELECT * FROM tbl2 WHERE 1000=f2"
-- return sql_search_count
-- end, {
-- -- <select2-3.2d>
-- 1
-- -- </select2-3.2d>
-- })
-- TODO: need access to sql_search_count vatiable
--test:do_test(
-- "select2-3.2e",
-- function()
-- sql_search_count = 0
-- test:execsql "SELECT * FROM tbl2 WHERE f2=1000"
-- return sql_search_count
-- end, {
-- -- <select2-3.2e>
-- 1
-- -- </select2-3.2e>
-- })
-- TODO: need access to sql_search_count vatiable
-- Make sure queries run faster with an index than without
--
--test:do_test(
-- "select2-3.3",
-- function()
-- test:execsql "DROP INDEX idx1"
-- sql_search_count = 0
-- test:execsql "SELECT f1 FROM tbl2 WHERE f2==2000"
-- return sql_search_count
-- end, {
-- -- <select2-3.3>
-- 29999
-- -- </select2-3.3>
-- })
-- Make sure we can optimize functions in the WHERE clause that
-- use fields from two or more different table. (Bug #6)
--
test:do_execsql_test(
"select2-4.1",
[[
DROP TABLE IF EXISTS aa;
DROP TABLE IF EXISTS bb;
CREATE TABLE aa(a int primary key);
CREATE TABLE bb(b int primary key);
INSERT INTO aa VALUES(1);
INSERT INTO aa VALUES(3);
INSERT INTO bb VALUES(2);
INSERT INTO bb VALUES(4);
SELECT * FROM aa, bb WHERE GREATEST(a,b)>2;
]], {
-- <select2-4.1>
1, 4, 3, 2, 3, 4
-- </select2-4.1>
})
test:do_execsql_test(
"select2-4.2",
[[
INSERT INTO bb VALUES(0);
SELECT * FROM aa CROSS JOIN bb WHERE b <> 0;
]], {
-- <select2-4.2>
1, 2, 1, 4, 3, 2, 3, 4
-- </select2-4.2>
})
test:do_execsql_test(
"select2-4.3",
[[
SELECT * FROM aa CROSS JOIN bb WHERE NOT b <> 0;
]], {
-- <select2-4.3>
1, 0, 3, 0
-- </select2-4.3>
})
test:do_execsql_test(
"select2-4.4",
[[
SELECT * FROM aa, bb WHERE LEAST(a,b) <> 0;
]], {
-- <select2-4.4>
1, 2, 1, 4, 3, 2, 3, 4
-- </select2-4.4>
})
test:do_execsql_test(
"select2-4.5",
[[
SELECT * FROM aa, bb WHERE NOT LEAST(a,b) <> 0;
]], {
-- <select2-4.5>
1, 0, 3, 0
-- </select2-4.5>
})
test:do_execsql_test(
"select2-4.6",
[[
SELECT * FROM aa, bb WHERE CASE WHEN a=b-1 THEN true END;
]], {
-- <select2-4.6>
1, 2, 3, 4
-- </select2-4.6>
})
test:do_execsql_test(
"select2-4.7",
[[
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
-- </select2-4.7>
})
test:finish_test()
|