Module materialize.checks.all_checks.join_types
Expand source code Browse git
# Copyright Materialize, Inc. and contributors. All rights reserved.
#
# Use of this software is governed by the Business Source License
# included in the LICENSE file at the root of this repository.
#
# As of the Change Date specified in that file, in accordance with
# the Business Source License, use of this software will be governed
# by the Apache License, Version 2.0.
from textwrap import dedent
from materialize.checks.actions import Testdrive
from materialize.checks.checks import Check
class JoinTypes(Check):
def manipulate(self) -> list[Testdrive]:
return [
Testdrive(dedent(s))
for s in [
"""
> CREATE SCHEMA join_schema;
> SET search_path=join_schema;
> CREATE TABLE join_schema.t1 (f1 INTEGER);
> CREATE TABLE join_schema.t1a (f1 INTEGER);
> CREATE TABLE join_schema.t2 (f2 INTEGER);
> CREATE MATERIALIZED VIEW join_schema.comma_join AS SELECT * FROM t1 , t2;
> CREATE MATERIALIZED VIEW join_schema.cross_join AS SELECT * FROM t1 CROSS JOIN t2;
> CREATE MATERIALIZED VIEW join_schema.natural_join AS SELECT * FROM t1 NATURAL JOIN t1a;
> CREATE MATERIALIZED VIEW join_schema.full_outer_join AS SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.f1 = t2.f2;
> CREATE MATERIALIZED VIEW join_schema.left_join AS SELECT * FROM t1 LEFT JOIN t2 ON t1.f1 = t2.f2;
> CREATE MATERIALIZED VIEW join_schema.right_join AS SELECT * FROM t1 RIGHT JOIN t2 ON t1.f1 = t2.f2;
> CREATE MATERIALIZED VIEW join_schema.join_on AS SELECT * FROM t1 INNER JOIN t2 ON (t1.f1 < t2.f2);
> CREATE MATERIALIZED VIEW join_schema.join_using AS SELECT * FROM t1 INNER JOIN t1a USING (f1);
> CREATE MATERIALIZED VIEW join_schema.join_table_alias AS SELECT a1.f1, a2.f2 FROM t1 AS a1 JOIN t2 AS a2 ON a1.f1 = a2.f2;
> CREATE MATERIALIZED VIEW join_schema.join_column_alias AS SELECT t1.a1, t2.a2 FROM t1 AS t1 (a1), t2 AS t2 (a2);
> CREATE MATERIALIZED VIEW join_schema.lateral_join AS SELECT * FROM t1 LATERAL JOIN ( SELECT f2 + 1 AS f2 FROM t2 ) ON TRUE;
> INSERT INTO join_schema.t1 VALUES (NULL), (1), (2);
> INSERT INTO join_schema.t1a VALUES (NULL), (1), (2);
""",
"""
> INSERT INTO join_schema.t2 VALUES (2), (3), (NULL);
""",
]
]
def validate(self) -> Testdrive:
return Testdrive(
dedent(
"""
> SET search_path=join_schema;
> SELECT * FROM comma_join;
1 2
1 3
1 <null>
2 2
2 3
2 <null>
<null> 2
<null> 3
<null> <null>
> SELECT * FROM cross_join;
1 2
1 3
1 <null>
2 2
2 3
2 <null>
<null> 2
<null> 3
<null> <null>
> SELECT * FROM natural_join;
1
2
> SELECT * FROM full_outer_join;
1 <null>
2 2
<null> 3
<null> <null>
<null> <null>
> SELECT * FROM left_join;
1 <null>
2 2
<null> <null>
> SELECT * FROM right_join;
2 2
<null> 3
<null> <null>
> SELECT * FROM join_on;
1 2
1 3
2 3
> SELECT * FROM join_using;
1
2
> SELECT * FROM join_table_alias;
2 2
> SELECT * FROM join_column_alias;
a1 a2
----
1 2
1 3
1 <null>
2 2
2 3
2 <null>
<null> 2
<null> 3
<null> <null>
> SELECT * FROM lateral_join;
1 3
1 4
1 <null>
2 3
2 4
2 <null>
<null> 3
<null> 4
<null> <null>
"""
)
)Classes
class JoinTypes (base_version: MzVersion, rng: random.Random | None)-
Expand source code Browse git
class JoinTypes(Check): def manipulate(self) -> list[Testdrive]: return [ Testdrive(dedent(s)) for s in [ """ > CREATE SCHEMA join_schema; > SET search_path=join_schema; > CREATE TABLE join_schema.t1 (f1 INTEGER); > CREATE TABLE join_schema.t1a (f1 INTEGER); > CREATE TABLE join_schema.t2 (f2 INTEGER); > CREATE MATERIALIZED VIEW join_schema.comma_join AS SELECT * FROM t1 , t2; > CREATE MATERIALIZED VIEW join_schema.cross_join AS SELECT * FROM t1 CROSS JOIN t2; > CREATE MATERIALIZED VIEW join_schema.natural_join AS SELECT * FROM t1 NATURAL JOIN t1a; > CREATE MATERIALIZED VIEW join_schema.full_outer_join AS SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.f1 = t2.f2; > CREATE MATERIALIZED VIEW join_schema.left_join AS SELECT * FROM t1 LEFT JOIN t2 ON t1.f1 = t2.f2; > CREATE MATERIALIZED VIEW join_schema.right_join AS SELECT * FROM t1 RIGHT JOIN t2 ON t1.f1 = t2.f2; > CREATE MATERIALIZED VIEW join_schema.join_on AS SELECT * FROM t1 INNER JOIN t2 ON (t1.f1 < t2.f2); > CREATE MATERIALIZED VIEW join_schema.join_using AS SELECT * FROM t1 INNER JOIN t1a USING (f1); > CREATE MATERIALIZED VIEW join_schema.join_table_alias AS SELECT a1.f1, a2.f2 FROM t1 AS a1 JOIN t2 AS a2 ON a1.f1 = a2.f2; > CREATE MATERIALIZED VIEW join_schema.join_column_alias AS SELECT t1.a1, t2.a2 FROM t1 AS t1 (a1), t2 AS t2 (a2); > CREATE MATERIALIZED VIEW join_schema.lateral_join AS SELECT * FROM t1 LATERAL JOIN ( SELECT f2 + 1 AS f2 FROM t2 ) ON TRUE; > INSERT INTO join_schema.t1 VALUES (NULL), (1), (2); > INSERT INTO join_schema.t1a VALUES (NULL), (1), (2); """, """ > INSERT INTO join_schema.t2 VALUES (2), (3), (NULL); """, ] ] def validate(self) -> Testdrive: return Testdrive( dedent( """ > SET search_path=join_schema; > SELECT * FROM comma_join; 1 2 1 3 1 <null> 2 2 2 3 2 <null> <null> 2 <null> 3 <null> <null> > SELECT * FROM cross_join; 1 2 1 3 1 <null> 2 2 2 3 2 <null> <null> 2 <null> 3 <null> <null> > SELECT * FROM natural_join; 1 2 > SELECT * FROM full_outer_join; 1 <null> 2 2 <null> 3 <null> <null> <null> <null> > SELECT * FROM left_join; 1 <null> 2 2 <null> <null> > SELECT * FROM right_join; 2 2 <null> 3 <null> <null> > SELECT * FROM join_on; 1 2 1 3 2 3 > SELECT * FROM join_using; 1 2 > SELECT * FROM join_table_alias; 2 2 > SELECT * FROM join_column_alias; a1 a2 ---- 1 2 1 3 1 <null> 2 2 2 3 2 <null> <null> 2 <null> 3 <null> <null> > SELECT * FROM lateral_join; 1 3 1 4 1 <null> 2 3 2 4 2 <null> <null> 3 <null> 4 <null> <null> """ ) )Ancestors
Methods
def manipulate(self) ‑> list[Testdrive]-
Expand source code Browse git
def manipulate(self) -> list[Testdrive]: return [ Testdrive(dedent(s)) for s in [ """ > CREATE SCHEMA join_schema; > SET search_path=join_schema; > CREATE TABLE join_schema.t1 (f1 INTEGER); > CREATE TABLE join_schema.t1a (f1 INTEGER); > CREATE TABLE join_schema.t2 (f2 INTEGER); > CREATE MATERIALIZED VIEW join_schema.comma_join AS SELECT * FROM t1 , t2; > CREATE MATERIALIZED VIEW join_schema.cross_join AS SELECT * FROM t1 CROSS JOIN t2; > CREATE MATERIALIZED VIEW join_schema.natural_join AS SELECT * FROM t1 NATURAL JOIN t1a; > CREATE MATERIALIZED VIEW join_schema.full_outer_join AS SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.f1 = t2.f2; > CREATE MATERIALIZED VIEW join_schema.left_join AS SELECT * FROM t1 LEFT JOIN t2 ON t1.f1 = t2.f2; > CREATE MATERIALIZED VIEW join_schema.right_join AS SELECT * FROM t1 RIGHT JOIN t2 ON t1.f1 = t2.f2; > CREATE MATERIALIZED VIEW join_schema.join_on AS SELECT * FROM t1 INNER JOIN t2 ON (t1.f1 < t2.f2); > CREATE MATERIALIZED VIEW join_schema.join_using AS SELECT * FROM t1 INNER JOIN t1a USING (f1); > CREATE MATERIALIZED VIEW join_schema.join_table_alias AS SELECT a1.f1, a2.f2 FROM t1 AS a1 JOIN t2 AS a2 ON a1.f1 = a2.f2; > CREATE MATERIALIZED VIEW join_schema.join_column_alias AS SELECT t1.a1, t2.a2 FROM t1 AS t1 (a1), t2 AS t2 (a2); > CREATE MATERIALIZED VIEW join_schema.lateral_join AS SELECT * FROM t1 LATERAL JOIN ( SELECT f2 + 1 AS f2 FROM t2 ) ON TRUE; > INSERT INTO join_schema.t1 VALUES (NULL), (1), (2); > INSERT INTO join_schema.t1a VALUES (NULL), (1), (2); """, """ > INSERT INTO join_schema.t2 VALUES (2), (3), (NULL); """, ] ] def validate(self) ‑> Testdrive-
Expand source code Browse git
def validate(self) -> Testdrive: return Testdrive( dedent( """ > SET search_path=join_schema; > SELECT * FROM comma_join; 1 2 1 3 1 <null> 2 2 2 3 2 <null> <null> 2 <null> 3 <null> <null> > SELECT * FROM cross_join; 1 2 1 3 1 <null> 2 2 2 3 2 <null> <null> 2 <null> 3 <null> <null> > SELECT * FROM natural_join; 1 2 > SELECT * FROM full_outer_join; 1 <null> 2 2 <null> 3 <null> <null> <null> <null> > SELECT * FROM left_join; 1 <null> 2 2 <null> <null> > SELECT * FROM right_join; 2 2 <null> 3 <null> <null> > SELECT * FROM join_on; 1 2 1 3 2 3 > SELECT * FROM join_using; 1 2 > SELECT * FROM join_table_alias; 2 2 > SELECT * FROM join_column_alias; a1 a2 ---- 1 2 1 3 1 <null> 2 2 2 3 2 <null> <null> 2 <null> 3 <null> <null> > SELECT * FROM lateral_join; 1 3 1 4 1 <null> 2 3 2 4 2 <null> <null> 3 <null> 4 <null> <null> """ ) )