Changeset: 10bb55889e33 for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=10bb55889e33
Modified Files:
sql/backends/monet5/Tests/limithack.sql
sql/backends/monet5/Tests/limithack.stable.err
sql/backends/monet5/Tests/limithack.stable.out
Branch: default
Log Message:
Make test independent from chance contents of sys.tables.
diffs (133 lines):
diff --git a/sql/backends/monet5/Tests/limithack.sql
b/sql/backends/monet5/Tests/limithack.sql
--- a/sql/backends/monet5/Tests/limithack.sql
+++ b/sql/backends/monet5/Tests/limithack.sql
@@ -1,10 +1,64 @@
-- how to deal with limit clause limitation in MonetDB
--- select * from tables where id in (select id from tables limit 1);
+CREATE TABLE "sys"."tbls" (
+ "id" INTEGER,
+ "name" VARCHAR(1024),
+ "schema_id" INTEGER,
+ "query" VARCHAR(2048),
+ "type" SMALLINT,
+ "system" BOOLEAN,
+ "commit_action" SMALLINT,
+ "readonly" BOOLEAN,
+ "temporary" SMALLINT
+);
+COPY 40 RECORDS INTO "sys"."tbls" FROM stdin USING DELIMITERS '\t','\n','"';
+2001 "schemas" 2000 NULL 0 true 0 false 0
+2007 "types" 2000 NULL 0 true 0 false 0
+2016 "functions" 2000 NULL 0 true 0 false 0
+2027 "args" 2000 NULL 0 true 0 false 0
+2036 "sequences" 2000 NULL 0 true 0 false 0
+2046 "dependencies" 2000 NULL 0 true 0 false 0
+2050 "connections" 2000 NULL 0 true 0 false 0
+2059 "_tables" 2000 NULL 0 true 0 false 0
+2068 "_columns" 2000 NULL 0 true 0 false 0
+2079 "keys" 2000 NULL 0 true 0 false 0
+2086 "idxs" 2000 NULL 0 true 0 false 0
+2091 "triggers" 2000 NULL 0 true 0 false 0
+2102 "objects" 2000 NULL 0 true 0 false 0
+2107 "_tables" 2106 NULL 0 true 2 false 0
+2116 "_columns" 2106 NULL 0 true 2 false 0
+2127 "keys" 2106 NULL 0 true 2 false 0
+2134 "idxs" 2106 NULL 0 true 2 false 0
+2139 "triggers" 2106 NULL 0 true 2 false 0
+2150 "objects" 2106 NULL 0 true 2 false 0
+5183 "tables" 2000 "SELECT * FROM (SELECT p.*, 0 AS ""temporary""
FROM ""sys"".""_tables"" AS p UNION ALL SELECT t.*, 1 AS ""temporary"" FROM
""tmp"".""_tables"" AS t) AS tables where tables.type <> 2;" 1 true 0
false 0
+5193 "columns" 2000 "SELECT * FROM (SELECT p.* FROM
""sys"".""_columns"" AS p UNION ALL SELECT t.* FROM ""tmp"".""_columns"" AS t)
AS columns;" 1 true 0 false 0
+5209 "db_user_info" 2000 NULL 0 true 0 false 0
+5215 "users" 2000 "SELECT u.""name"" AS ""name"", ui.""fullname"",
ui.""default_schema"" FROM db_users() AS u LEFT JOIN ""sys"".""db_user_info""
AS ui ON u.""name"" = ui.""name"" ;" 1 true 0 false 0
+5219 "user_role" 2000 NULL 0 true 0 false 0
+5222 "auths" 2000 NULL 0 true 0 false 0
+5226 "privileges" 2000 NULL 0 true 0 false 0
+5399 "querylog_catalog" 2000 "-- create table views for
convenience\ncreate view sys.querylog_catalog as select * from
sys.querylog_catalog();" 1 true 0 false 0
+5411 "querylog_calls" 2000 "create view sys.querylog_calls as
select * from sys.querylog_calls();" 1 true 0 false 0
+5429 "querylog_history" 2000 "create view sys.querylog_history
as\nselect qd.*, ql.""start"",ql.""stop"", ql.arguments, ql.tuples, ql.run,
ql.ship, ql.cpu, ql.space, ql.io \nfrom sys.querylog_catalog() qd,
sys.querylog_calls() ql\nwhere qd.id = ql.id and qd.owner = user;" 1
true 0 false 0
+5466 "tracelog" 2000 "create view sys.tracelog as select * from
sys.tracelog();" 1 true 0 false 0
+5591 "sessions" 2000 "create view sys.sessions as select * from
sys.sessions();" 1 true 0 false 0
+5671 "optimizers" 2000 "create view sys.optimizers as select * from
sys.optimizers();" 1 true 0 false 0
+5679 "environment" 2000 "create view sys.environment as select * from
sys.environment();" 1 true 0 false 0
+5717 "queue" 2000 "create view sys.queue as select * from sys.queue();"
1 true 0 false 0
+6368 "storage" 2000 "create view sys.storage as select * from
sys.storage();" 1 true 0 false 0
+6380 "storagemodelinput" 2000 NULL 0 true 0 false 0
+6428 "storagemodel" 2000 "create view sys.storagemodel as select * from
sys.storagemodel();" 1 true 0 false 0
+6438 "tablestoragemodel" 2000 "-- A summary of the table storage
requirement is is available as a table view.\n-- The auxiliary column denotes
the maximum space if all non-sorted columns\n-- would be augmented with a hash
(rare situation)\ncreate view sys.tablestoragemodel\nas select
""schema"",""table"",max(count) as ""count"",\n\tsum(columnsize) as
columnsize,\n\tsum(heapsize) as heapsize,\n\tsum(hashes) as
hashes,\n\tsum(imprints) as imprints,\n\tsum(case when sorted = false then 8 *
count else 0 end) as auxiliary\nfrom sys.storagemodel() group by
""schema"",""table"";" 1 true 0 false 0
+6453 "statistics" 2000 NULL 0 true 0 false 0
+6616 "systemfunctions" 2000 NULL 0 true 0 false 0
+
+-- select * from tbls where id in (select id from tbls limit 1);
create function limited()
returns table (id integer)
begin
- return select id from tables order by id desc limit 1;
+ return select id from tbls order by id desc limit 1;
end;
-select * from tables where id in (select id from limited());
+select * from tbls where id in (select id from limited());
+drop table tbls;
diff --git a/sql/backends/monet5/Tests/limithack.stable.err
b/sql/backends/monet5/Tests/limithack.stable.err
--- a/sql/backends/monet5/Tests/limithack.stable.err
+++ b/sql/backends/monet5/Tests/limithack.stable.err
@@ -30,6 +30,9 @@ stderr of test 'limithack` in directory
# 17:27:34 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-26146" "--port=32494"
# 17:27:34 >
+MAPI = (monetdb) /var/tmp/mtest-29474/.s.monetdb.31917
+QUERY = drop table tbls;
+
# 17:27:34 >
# 17:27:34 > "Done."
diff --git a/sql/backends/monet5/Tests/limithack.stable.out
b/sql/backends/monet5/Tests/limithack.stable.out
--- a/sql/backends/monet5/Tests/limithack.stable.out
+++ b/sql/backends/monet5/Tests/limithack.stable.out
@@ -57,17 +57,41 @@ Ready.
# 17:27:34 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-26146" "--port=32494"
# 17:27:34 >
+#CREATE TABLE "sys"."tbls" (
+# "id" INTEGER,
+# "name" VARCHAR(1024),
+# "schema_id" INTEGER,
+# "query" VARCHAR(2048),
+# "type" SMALLINT,
+# "system" BOOLEAN,
+# "commit_action" SMALLINT,
+# "readonly" BOOLEAN,
+# "temporary" SMALLINT
+#);
+#COPY 40 RECORDS INTO "sys"."tbls" FROM stdin USING DELIMITERS '\t','\n','"';
+#2001 "schemas" 2000 NULL 0 true 0 false 0
+#2007 "types" 2000 NULL 0 true 0 false 0
+#2016 "functions" 2000 NULL 0 true 0 false 0
+#2027 "args" 2000 NULL 0 true 0 false 0
+#2036 "sequences" 2000 NULL 0 true 0 false 0
+#2046 "dependencies" 2000 NULL 0 true 0 false 0
+#2050 "connections" 2000 NULL 0 true 0 false 0
+#2059 "_tables" 2000 NULL 0 true 0 false 0
+#2068 "_columns" 2000 NULL 0 true 0 false 0
+#2079 "keys" 2000 NULL 0 true 0 false 0
+#2086 "idxs" 2000 NULL 0 true 0 false 0
+[ 40 ]
#create function limited()
#returns table (id integer)
#begin
# return select id from tables order by id desc limit 1;
#end;
-#select * from tables where id in (select id from limited());
-% .tables, .tables, .tables, .tables, .tables,
.tables, .tables, .tables, .tables # table_name
+#select * from tbls where id in (select id from limited());
+% sys.tbls, sys.tbls, sys.tbls, sys.tbls, sys.tbls,
sys.tbls, sys.tbls, sys.tbls, sys.tbls # table_name
% id, name, schema_id, query, type, system, commit_action,
readonly, temporary # name
-% int, varchar, int, varchar, smallint, boolean,
smallint, boolean, tinyint # type
+% int, varchar, int, varchar, smallint, boolean,
smallint, boolean, smallint # type
% 4, 15, 4, 0, 1, 5, 1, 5, 1 # length
-[ 7211, "systemfunctions", 2000, NULL, 0, true, 0,
false, 0 ]
+[ 6616, "systemfunctions", 2000, NULL, 0, true, 0,
false, 0 ]
# 17:27:34 >
# 17:27:34 > "Done."
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
