Changeset: 1d35e5a884e9 for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=1d35e5a884e9
Added Files:
sql/test/sciql2sql/Tests/structural_joins.sql
Modified Files:
sql/test/sciql2sql/Tests/All
sql/test/sciql2sql/Tests/create_unbound_array.sql
sql/test/sciql2sql/Tests/create_unbound_step_array.sql
sql/test/sciql2sql/Tests/create_unbound_step_array.stable.out
sql/test/sciql2sql/Tests/select_vector_array.sql
Branch: SciQL-2
Log Message:
Structural join tests.
diffs (209 lines):
diff --git a/sql/test/sciql2sql/Tests/All b/sql/test/sciql2sql/Tests/All
--- a/sql/test/sciql2sql/Tests/All
+++ b/sql/test/sciql2sql/Tests/All
@@ -22,6 +22,9 @@ slice_2d_array
# selection with array type result
select_vector_array
+# all kind of array joins
+structural_joins
+
# tiling based on dimensional attributes
vector_fixed_tiles
#
diff --git a/sql/test/sciql2sql/Tests/create_unbound_array.sql
b/sql/test/sciql2sql/Tests/create_unbound_array.sql
--- a/sql/test/sciql2sql/Tests/create_unbound_array.sql
+++ b/sql/test/sciql2sql/Tests/create_unbound_array.sql
@@ -3,7 +3,7 @@ CREATE ARRAY array1Dunbound(x INTEGER DI
SELECT * FROM array1Dunbound;
DROP ARRAY array1Dunbound;
--- relational equivalent , step size can be cast as constraint
+-- relational equivalent
CREATE TABLE array1Dunbound(x INTEGER, v INTEGER DEFAULT 1);
SELECT * FROM array1Dunbound;
DROP ARRAY array1Dunbound;
diff --git a/sql/test/sciql2sql/Tests/create_unbound_step_array.sql
b/sql/test/sciql2sql/Tests/create_unbound_step_array.sql
--- a/sql/test/sciql2sql/Tests/create_unbound_step_array.sql
+++ b/sql/test/sciql2sql/Tests/create_unbound_step_array.sql
@@ -10,9 +10,10 @@ CREATE TABLE array1Dintval(idx INTEGER R
CREATE VIEW array1Dint
AS SELECT x,v FROM array1Dintdim, array1Dintval WHERE array1Dintdim.idx =
array1Dintval.idx;
-INSERT INTO array1Dintdim VALUES (0,0),(1,4);
+-- the boundaries are known and can be initialized
+INSERT INTO array1Dintdim VALUES (0,0),(1,3);
INSERT INTO array1Dintval VALUES (0,1),(1,1);
-SELECT * FROM array1Dintdim;
+SELECT * FROM array1Dint;
DROP VIEW array1Dint;
DROP TABLE array1Dintval;
diff --git a/sql/test/sciql2sql/Tests/create_unbound_step_array.stable.out
b/sql/test/sciql2sql/Tests/create_unbound_step_array.stable.out
--- a/sql/test/sciql2sql/Tests/create_unbound_step_array.stable.out
+++ b/sql/test/sciql2sql/Tests/create_unbound_step_array.stable.out
@@ -40,33 +40,16 @@ Ready.
[ 2 ]
#INSERT INTO array1Dintval VALUES (0,1),(1,1);
[ 2 ]
-#SELECT * FROM array1Dintdim;
-% sys.array1dintdim, sys.array1dintdim # table_name
-% idx, x # name
-% int, int # type
-% 1, 1 # length
-[ 0, 0 ]
-[ 1, 4 ]
-#DROP VIEW array1Dint;
-#DROP TABLE array1Dintval;
-#DROP TABLE array1Dintdim;
-#CREATE TABLE array1Dint(x INTEGER, v INTEGER DEFAULT 1);
-#INSERT INTO array1Dint VALUES
-#(0,1),
-#(1,1),
-#(2,1),
-#(3,1);
-[ 4 ]
#SELECT * FROM array1Dint;
% sys.array1dint, sys.array1dint # table_name
% x, v # name
% int, int # type
% 1, 1 # length
[ 0, 1 ]
-[ 1, 1 ]
-[ 2, 1 ]
-[ 3, 1 ]
-#DROP TABLE array1Dint;
+[ 4, 1 ]
+#DROP VIEW array1Dint;
+#DROP TABLE array1Dintval;
+#DROP TABLE array1Dintdim;
#CREATE ARRAY array1Dint(x INTEGER DIMENSION[0:*:*], v INTEGER DEFAULT 1);
#SELECT * FROM array1Dint;
% sys.array1dint, sys.array1dint # table_name
diff --git a/sql/test/sciql2sql/Tests/select_vector_array.sql
b/sql/test/sciql2sql/Tests/select_vector_array.sql
--- a/sql/test/sciql2sql/Tests/select_vector_array.sql
+++ b/sql/test/sciql2sql/Tests/select_vector_array.sql
@@ -22,7 +22,7 @@ SELECT x, v+w FROM vector;
SELECT [x+2], v+w FROM array1D;
-- relational equivalent
-SELECT x, v+w FROM vector;
+SELECT x+2, v+w FROM vector;
-- extend array with constant y
SELECT [x],[0], v+w FROM array1D;
@@ -40,7 +40,11 @@ CREATE TEMPORARY ARRAY tmp( v INTEGER DI
INSERT INTO tmp SELECT v,x,w FROM vector;
-- which arbitrary drops elements.
-- To mimick this all but one row of a group should be deleted.
--- TBD
+CREATE FUNCTION ord() RETURNS TABLE (v integer, x integer, w integer)
+BEGIN
+ RETURN SELECT row_number() as id, v,x,w FROM vector ORDER BY v,x,w;
+END;
+SELECT v, min(id) FROM ord() GROUP BY v;
-- In a strongly typed setting, a coercion error should be raised when
SELECT (SELECT count(*) FROM vector) = (SELECT count(*)
FROM vector
diff --git a/sql/test/sciql2sql/Tests/structural_joins.sql
b/sql/test/sciql2sql/Tests/structural_joins.sql
new file mode 100644
--- /dev/null
+++ b/sql/test/sciql2sql/Tests/structural_joins.sql
@@ -0,0 +1,97 @@
+-- structural joins over 4x4 and 2x2
+CREATE ARRAY image(x INTEGER DIMENSION[4], y INTEGER DIMENSION[4], gray
INTEGER DEFAULT 0);
+INSERT INTO image values
+( 0, 0, 2 ),
+( 0, 1, 2 ),
+( 0, 2, 2 ),
+( 0, 3, 2 ),
+( 1, 0, 2 ),
+( 1, 1, 2 ),
+( 1, 2, 2 ),
+( 1, 3, 2 ),
+( 2, 0, 2 ),
+( 2, 1, 2 ),
+( 2, 2, 2 ),
+( 2, 3, 2 ),
+( 3, 0, 2 ),
+( 3, 1, 2 ),
+( 3, 2, 2 ),
+( 3, 3, 2 );
+SELECT * from image;
+
+CREATE ARRAY patch(x INTEGER DIMENSION[2], y INTEGER DIMENSION[2], gray
INTEGER DEFAULT 0);
+( 0, 0, 4 ),
+( 0, 1, 4 ),
+( 1, 0, 4 ),
+( 1, 1, 4 ),
+( 2, 0, 4 ),
+( 2, 3, 4 ),
+( 3, 0, 4 ),
+( 3, 1, 4 );
+SELECT * FROM patch;
+
+-- relational equivalent
+CREATE TABLE imageR(x INTEGER CHECK(x >=0 and x < 4), y INTEGER CHECK( y>=0
and y<4), gray INTEGER DEFAULT 0);
+INSERT INTO imageR VALUES
+( 0, 0, 2 ),
+( 0, 1, 2 ),
+( 0, 2, 2 ),
+( 0, 3, 2 ),
+( 1, 0, 2 ),
+( 1, 1, 2 ),
+( 1, 2, 2 ),
+( 1, 3, 2 ),
+( 2, 0, 2 ),
+( 2, 1, 2 ),
+( 2, 2, 2 ),
+( 2, 3, 2 ),
+( 3, 0, 2 ),
+( 3, 1, 2 ),
+( 3, 2, 2 ),
+( 3, 3, 2 );
+
+CREATE TABLE patchR(x INTEGER CHECK(x >=0 and x < 2), y INTEGER CHECK( y>=0
and y<2), gray INTEGER DEFAULT 0);
+INSERT INTO patchR VALUES
+( 0, 0, 4 ),
+( 0, 1, 4 ),
+( 1, 0, 4 ),
+( 1, 1, 4 ),
+( 2, 0, 4 ),
+( 2, 3, 4 ),
+( 3, 0, 4 ),
+( 3, 1, 4 );
+
+-- straightforward matrix addition returning an 4x4 ARRAY
+-- Underlying semantics is to use a natural join the dimensions
+SELECT [A.x], [A.y], (A.gray + B.gray)
+FROM image[x][y] A JOIN image[x][y] B;
+
+-- relational equivalent
+SELECT A.x,A.y, A.gray+B.gray
+FROM imageR A join imageR B ON A.x= B.x and A.y=B.y;
+
+-- addition of the patch with the image returning a 2x2 ARRAY
+SELECT [A.x], [A.y], (A.gray + B.gray)
+FROM image A[x][y] JOIN patch[x+1][y+2] B;
+
+-- relational equivalent
+SELECT A.x,A.y, A.gray+B.gray
+FROM imageR A join patchR B ON A.x= B.x+1 and A.y=B.y+2;
+
+-- simple window based aggregation
+SELECT [x], [y], avg(gray)
+FROM image
+GROUP BY image[x:x+3][y:y+3];
+
+SELECT x AS XOFF, y AS YOFF, (
+ SELECT avg(A.gray + B.gray)
+ FROM imageR A, patchR B
+ WHERE A.x - R.x = B.x AND A.y- R.y = B.y )
+FROM imageR R;
+
+DROP ARRAY image;
+DROP ARRAY patch;
+
+DROP TABLE imageR;
+DROP TABLE patchR;
+
_______________________________________________
checkin-list mailing list
[email protected]
http://mail.monetdb.org/mailman/listinfo/checkin-list
