Changeset: 96fb7ebb521f for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/96fb7ebb521f
Modified Files:
sql/backends/monet5/sql.c
sql/backends/monet5/sql_statistics.c
sql/scripts/80_statistics.sql
sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.test
sql/test/sys-schema/Tests/check_Not_Nullable_columns.test
sql/test/sys-schema/Tests/check_PrimaryKey_uniqueness.test
Branch: analyze-fix
Log Message:
Added "column_id" field back. Reverted some of the changes
diffs (227 lines):
diff --git a/sql/backends/monet5/sql.c b/sql/backends/monet5/sql.c
--- a/sql/backends/monet5/sql.c
+++ b/sql/backends/monet5/sql.c
@@ -5272,10 +5272,10 @@ static mel_func sql_init_funcs[] = {
pattern("sql", "analyze", sql_analyze, true, "Update statistics for schema",
args(1,2, arg("",void),arg("sch",str))),
pattern("sql", "analyze", sql_analyze, true, "Update statistics for table",
args(1,3, arg("",void),arg("sch",str),arg("tbl",str))),
pattern("sql", "analyze", sql_analyze, true, "Update statistics for column",
args(1,4, arg("",void),arg("sch",str),arg("tbl",str),arg("col",str))),
- pattern("sql", "statistics", sql_statistics, false, "return a table with
statistics information", args(12,12,
batarg("schema",str),batarg("table",str),batarg("column",str),batarg("type",str),batarg("with",int),batarg("count",lng),batarg("unique",bit),batarg("nils",bit),batarg("minval",str),batarg("maxval",str),batarg("sorted",bit),batarg("revsorted",bit))),
- pattern("sql", "statistics", sql_statistics, false, "return a table with
statistics information for a particular schema", args(12,13,
batarg("schema",str),batarg("table",str),batarg("column",str),batarg("type",str),batarg("with",int),batarg("count",lng),batarg("unique",bit),batarg("nils",bit),batarg("minval",str),batarg("maxval",str),batarg("sorted",bit),batarg("revsorted",bit),arg("sname",str))),
- pattern("sql", "statistics", sql_statistics, false, "return a table with
statistics information for a particular table", args(12,14,
batarg("schema",str),batarg("table",str),batarg("column",str),batarg("type",str),batarg("with",int),batarg("count",lng),batarg("unique",bit),batarg("nils",bit),batarg("minval",str),batarg("maxval",str),batarg("sorted",bit),batarg("revsorted",bit),arg("sname",str),arg("tname",str))),
- pattern("sql", "statistics", sql_statistics, false, "return a table with
statistics information for a particular column", args(12,15,
batarg("schema",str),batarg("table",str),batarg("column",str),batarg("type",str),batarg("with",int),batarg("count",lng),batarg("unique",bit),batarg("nils",bit),batarg("minval",str),batarg("maxval",str),batarg("sorted",bit),batarg("revsorted",bit),arg("sname",str),arg("tname",str),arg("cname",str))),
+ pattern("sql", "statistics", sql_statistics, false, "return a table with
statistics information", args(13,13,
batarg("columnid",int),batarg("schema",str),batarg("table",str),batarg("column",str),batarg("type",str),batarg("with",int),batarg("count",lng),batarg("unique",bit),batarg("nils",bit),batarg("minval",str),batarg("maxval",str),batarg("sorted",bit),batarg("revsorted",bit))),
+ pattern("sql", "statistics", sql_statistics, false, "return a table with
statistics information for a particular schema", args(13,14,
batarg("columnid",int),batarg("schema",str),batarg("table",str),batarg("column",str),batarg("type",str),batarg("with",int),batarg("count",lng),batarg("unique",bit),batarg("nils",bit),batarg("minval",str),batarg("maxval",str),batarg("sorted",bit),batarg("revsorted",bit),arg("sname",str))),
+ pattern("sql", "statistics", sql_statistics, false, "return a table with
statistics information for a particular table", args(13,15,
batarg("columnid",int),batarg("schema",str),batarg("table",str),batarg("column",str),batarg("type",str),batarg("with",int),batarg("count",lng),batarg("unique",bit),batarg("nils",bit),batarg("minval",str),batarg("maxval",str),batarg("sorted",bit),batarg("revsorted",bit),arg("sname",str),arg("tname",str))),
+ pattern("sql", "statistics", sql_statistics, false, "return a table with
statistics information for a particular column", args(13,16,
batarg("columnid",int),batarg("schema",str),batarg("table",str),batarg("column",str),batarg("type",str),batarg("with",int),batarg("count",lng),batarg("unique",bit),batarg("nils",bit),batarg("minval",str),batarg("maxval",str),batarg("sorted",bit),batarg("revsorted",bit),arg("sname",str),arg("tname",str),arg("cname",str))),
pattern("sql", "storage", sql_storage, false, "return a table with storage
information ", args(17,17,
batarg("schema",str),batarg("table",str),batarg("column",str),batarg("type",str),batarg("mode",str),batarg("location",str),batarg("count",lng),batarg("atomwidth",int),batarg("columnsize",lng),batarg("heap",lng),batarg("hashes",lng),batarg("phash",bit),batarg("imprints",lng),batarg("sorted",bit),batarg("revsorted",bit),batarg("key",bit),batarg("orderidx",lng))),
pattern("sql", "storage", sql_storage, false, "return a table with storage
information for a particular schema ", args(17,18,
batarg("schema",str),batarg("table",str),batarg("column",str),batarg("type",str),batarg("mode",str),batarg("location",str),batarg("count",lng),batarg("atomwidth",int),batarg("columnsize",lng),batarg("heap",lng),batarg("hashes",lng),batarg("phash",bit),batarg("imprints",lng),batarg("sorted",bit),batarg("revsorted",bit),batarg("key",bit),batarg("orderidx",lng),arg("sname",str))),
pattern("sql", "storage", sql_storage, false, "return a table with storage
information for a particular table", args(17,19,
batarg("schema",str),batarg("table",str),batarg("column",str),batarg("type",str),batarg("mode",str),batarg("location",str),batarg("count",lng),batarg("atomwidth",int),batarg("columnsize",lng),batarg("heap",lng),batarg("hashes",lng),batarg("phash",bit),batarg("imprints",lng),batarg("sorted",bit),batarg("revsorted",bit),batarg("key",bit),batarg("orderidx",lng),arg("sname",str),arg("tname",str))),
diff --git a/sql/backends/monet5/sql_statistics.c
b/sql/backends/monet5/sql_statistics.c
--- a/sql/backends/monet5/sql_statistics.c
+++ b/sql/backends/monet5/sql_statistics.c
@@ -159,22 +159,23 @@ sql_analyze(Client cntxt, MalBlkPtr mb,
str
sql_statistics(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci)
{
- BAT *sch, *tab, *col, *type, *width, *count, *unique, *nils, *minval,
*maxval, *sorted, *revsorted, *bs = NULL, *fb = NULL;
+ BAT *cid, *sch, *tab, *col, *type, *width, *count, *unique, *nils,
*minval, *maxval, *sorted, *revsorted, *bs = NULL, *fb = NULL;
mvc *m = NULL;
sql_trans *tr = NULL;
sqlstore *store = NULL;
- bat *rsch = getArgReference_bat(stk, pci, 0);
- bat *rtab = getArgReference_bat(stk, pci, 1);
- bat *rcol = getArgReference_bat(stk, pci, 2);
- bat *rtype = getArgReference_bat(stk, pci, 3);
- bat *rwidth = getArgReference_bat(stk, pci, 4);
- bat *rcount = getArgReference_bat(stk, pci, 5);
- bat *runique = getArgReference_bat(stk, pci, 6);
- bat *rnils = getArgReference_bat(stk, pci, 7);
- bat *rminval = getArgReference_bat(stk, pci, 8);
- bat *rmaxval = getArgReference_bat(stk, pci, 9);
- bat *rsorted = getArgReference_bat(stk, pci, 10);
- bat *rrevsorted = getArgReference_bat(stk, pci, 11);
+ bat *rcid = getArgReference_bat(stk, pci, 0);
+ bat *rsch = getArgReference_bat(stk, pci, 1);
+ bat *rtab = getArgReference_bat(stk, pci, 2);
+ bat *rcol = getArgReference_bat(stk, pci, 3);
+ bat *rtype = getArgReference_bat(stk, pci, 4);
+ bat *rwidth = getArgReference_bat(stk, pci, 5);
+ bat *rcount = getArgReference_bat(stk, pci, 6);
+ bat *runique = getArgReference_bat(stk, pci, 7);
+ bat *rnils = getArgReference_bat(stk, pci, 8);
+ bat *rminval = getArgReference_bat(stk, pci, 9);
+ bat *rmaxval = getArgReference_bat(stk, pci, 10);
+ bat *rsorted = getArgReference_bat(stk, pci, 11);
+ bat *rrevsorted = getArgReference_bat(stk, pci, 12);
str sname = NULL, tname = NULL, cname = NULL, msg = MAL_SUCCEED;
struct os_iter si = {0};
int sfnd = 0, tfnd = 0, cfnd = 0;
@@ -246,6 +247,7 @@ sql_statistics(Client cntxt, MalBlkPtr m
if (cname && !cfnd)
throw(SQL, "sql.statistics", SQLSTATE(38000) "Column '%s' does
not exist", cname);
+ cid = COLnew(0, TYPE_int, 0, TRANSIENT);
sch = COLnew(0, TYPE_str, 0, TRANSIENT);
tab = COLnew(0, TYPE_str, 0, TRANSIENT);
col = COLnew(0, TYPE_str, 0, TRANSIENT);
@@ -259,7 +261,7 @@ sql_statistics(Client cntxt, MalBlkPtr m
sorted = COLnew(0, TYPE_bit, 0, TRANSIENT);
revsorted = COLnew(0, TYPE_bit, 0, TRANSIENT);
- if (!sch || !tab || !col || !type || !width || !count || !unique ||
!nils || !minval || !maxval || !sorted || !revsorted) {
+ if (!cid || !sch || !tab || !col || !type || !width || !count ||
!unique || !nils || !minval || !maxval || !sorted || !revsorted) {
msg = createException(SQL, "sql.statistics", SQLSTATE(HY013)
MAL_MALLOC_FAIL);
goto bailout;
}
@@ -298,7 +300,8 @@ sql_statistics(Client cntxt, MalBlkPtr m
issorted = bs->tsorted;
isrevsorted = bs->trevsorted;
- if (BUNappend(sch, b->name,
false) != GDK_SUCCEED ||
+ if (BUNappend(cid, &c->base.id,
false) != GDK_SUCCEED ||
+ BUNappend(sch, b->name,
false) != GDK_SUCCEED ||
BUNappend(tab,
bt->name, false) != GDK_SUCCEED ||
BUNappend(col,
c->base.name, false) != GDK_SUCCEED ||
BUNappend(type,
c->type.type->base.name, false) != GDK_SUCCEED ||
@@ -376,6 +379,7 @@ sql_statistics(Client cntxt, MalBlkPtr m
}
}
+ BBPkeepref(*rcid = cid->batCacheid);
BBPkeepref(*rsch = sch->batCacheid);
BBPkeepref(*rtab = tab->batCacheid);
BBPkeepref(*rcol = col->batCacheid);
@@ -390,6 +394,7 @@ sql_statistics(Client cntxt, MalBlkPtr m
BBPkeepref(*rrevsorted = revsorted->batCacheid);
return MAL_SUCCEED;
bailout:
+ BBPreclaim(cid);
BBPreclaim(sch);
BBPreclaim(tab);
BBPreclaim(col);
diff --git a/sql/scripts/80_statistics.sql b/sql/scripts/80_statistics.sql
--- a/sql/scripts/80_statistics.sql
+++ b/sql/scripts/80_statistics.sql
@@ -26,6 +26,7 @@ grant execute on procedure sys."analyze"
create function sys."statistics"()
returns table (
+ "column_id" integer,
"schema" varchar(1024),
"table" varchar(1024),
"column" varchar(1024),
@@ -54,6 +55,7 @@ grant select on sys."statistics" to publ
create function sys."statistics"("sname" varchar(1024))
returns table (
+ "column_id" integer,
"schema" varchar(1024),
"table" varchar(1024),
"column" varchar(1024),
@@ -72,6 +74,7 @@ grant execute on function sys."statistic
create function sys."statistics"("sname" varchar(1024), "tname" varchar(1024))
returns table (
+ "column_id" integer,
"schema" varchar(1024),
"table" varchar(1024),
"column" varchar(1024),
@@ -90,6 +93,7 @@ grant execute on function sys."statistic
create function sys."statistics"("sname" varchar(1024), "tname" varchar(1024),
"cname" varchar(1024))
returns table (
+ "column_id" integer,
"schema" varchar(1024),
"table" varchar(1024),
"column" varchar(1024),
diff --git
a/sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.test
b/sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.test
--- a/sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.test
+++ b/sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.test
@@ -286,11 +286,11 @@ query ITTTTIIII rowsort
SELECT * FROM sys.sessions WHERE "username" NOT IN (SELECT name FROM sys.users)
----
-query ITITIIIITTII rowsort
-SELECT * FROM sys.statistics WHERE "column" NOT IN (SELECT id FROM
sys._columns UNION ALL SELECT id FROM tmp._columns)
+query IITITIIIITTII rowsort
+SELECT * FROM sys.statistics WHERE column_id NOT IN (SELECT id FROM
sys._columns UNION ALL SELECT id FROM tmp._columns)
----
-query ITITIIIITTII rowsort
+query IITITIIIITTII rowsort
SELECT * FROM sys.statistics WHERE "type" NOT IN (SELECT sqlname FROM
sys.types)
----
diff --git a/sql/test/sys-schema/Tests/check_Not_Nullable_columns.test
b/sql/test/sys-schema/Tests/check_Not_Nullable_columns.test
--- a/sql/test/sys-schema/Tests/check_Not_Nullable_columns.test
+++ b/sql/test/sys-schema/Tests/check_Not_Nullable_columns.test
@@ -422,43 +422,47 @@ query IIITIIIIII rowsort
SELECT "start", * FROM "sys"."sequences" WHERE "start" IS NULL
----
-query TTTTTIITTTTTT rowsort
+query IITTTTIITTTTTT rowsort
+SELECT "column_id", * FROM "sys"."statistics" WHERE "column_id" IS NULL
+----
+
+query TITTTTIITTTTTT rowsort
SELECT "schema", * FROM "sys"."statistics" WHERE "schema" IS NULL
----
-query TTTTTIITTTTTT rowsort
+query TITTTTIITTTTTT rowsort
SELECT "table", * FROM "sys"."statistics" WHERE "table" IS NULL
----
-query TTTTTIITTTTTT rowsort
+query TITTTTIITTTTTT rowsort
SELECT "column", * FROM "sys"."statistics" WHERE "column" IS NULL
----
-query TTTTTIITTTTTT rowsort
+query TITTTTIITTTTTT rowsort
SELECT "type", * FROM "sys"."statistics" WHERE "type" IS NULL
----
-query ITTTTIITTTTTT rowsort
+query IITTTTIITTTTTT rowsort
SELECT "width", * FROM "sys"."statistics" WHERE "width" IS NULL
----
-query ITTTTIITTTTTT rowsort
+query IITTTTIITTTTTT rowsort
SELECT "count", * FROM "sys"."statistics" WHERE "count" IS NULL
----
-query TTTTTIITTTTTT rowsort
+query TITTTTIITTTTTT rowsort
SELECT "unique", * FROM "sys"."statistics" WHERE "unique" IS NULL
----
-query TTTTTIITTTTTT rowsort
+query TITTTTIITTTTTT rowsort
SELECT "nils", * FROM "sys"."statistics" WHERE "nils" IS NULL
----
-query TTTTTIITTTTTT rowsort
+query TITTTTIITTTTTT rowsort
SELECT "sorted", * FROM "sys"."statistics" WHERE "sorted" IS NULL
----
-query TTTTTIITTTTTT rowsort
+query TITTTTIITTTTTT rowsort
SELECT "revsorted", * FROM "sys"."statistics" WHERE "revsorted" IS NULL
----
diff --git a/sql/test/sys-schema/Tests/check_PrimaryKey_uniqueness.test
b/sql/test/sys-schema/Tests/check_PrimaryKey_uniqueness.test
--- a/sql/test/sys-schema/Tests/check_PrimaryKey_uniqueness.test
+++ b/sql/test/sys-schema/Tests/check_PrimaryKey_uniqueness.test
@@ -167,7 +167,7 @@ SELECT COUNT(*) AS duplicates, "username
----
query IT rowsort
-SELECT COUNT(*) AS duplicates, "column" FROM sys.statistics GROUP BY "column"
HAVING COUNT(*) > 1
+SELECT COUNT(*) AS duplicates, "column_id" FROM sys.statistics GROUP BY
"column_id" HAVING COUNT(*) > 1
----
query II rowsort
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
