github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/exec/execbuilder/testdata/fk (about)

     1  # LogicTest: local
     2  
     3  # To get an unvalidated foreign key for testing, use the loophole that we
     4  # currently don't support adding a validated FK in the same transaction as
     5  # CREATE TABLE
     6  
     7  statement ok
     8  CREATE TABLE a (
     9    x STRING NULL,
    10    y STRING NULL,
    11    z STRING NULL,
    12    CONSTRAINT "primary" PRIMARY KEY (z, y, x)
    13  )
    14  
    15  statement ok
    16  CREATE TABLE b (
    17    a_y STRING NULL,
    18    a_x STRING NULL,
    19    a_z STRING NULL,
    20    INDEX idx (a_z, a_y, a_x)
    21  )
    22  
    23  statement ok
    24  ALTER TABLE b ADD CONSTRAINT fk_ref FOREIGN KEY (a_z, a_y, a_x) REFERENCES a (z, y, x) NOT VALID
    25  
    26  # Verify that the optimizer doesn't use an unvalidated constraint to simplify plans.
    27  query TTT colnames
    28  EXPLAIN SELECT
    29    s.a_z, s.a_y, s.a_x
    30  FROM
    31    (SELECT * FROM b WHERE a_z IS NOT NULL AND a_y IS NOT NULL AND a_x IS NOT NULL) AS s
    32    LEFT JOIN a AS t ON s.a_z = t.z AND s.a_y = t.y AND s.a_x = t.x
    33  WHERE
    34    t.z IS NULL
    35  ----
    36  tree                  field               description
    37  ·                     distributed         false
    38  ·                     vectorized          true
    39  render                ·                   ·
    40   └── filter           ·                   ·
    41        │               filter              z IS NULL
    42        └── merge-join  ·                   ·
    43             │          type                left outer
    44             │          equality            (a_z, a_y, a_x) = (z, y, x)
    45             │          right cols are key  ·
    46             │          mergeJoinOrder      +"(a_z=z)",+"(a_y=y)",+"(a_x=x)"
    47             ├── scan   ·                   ·
    48             │          table               b@idx
    49             │          spans               /!NULL-
    50             │          filter              (a_y IS NOT NULL) AND (a_x IS NOT NULL)
    51             └── scan   ·                   ·
    52  ·                     table               a@primary
    53  ·                     spans               FULL SCAN
    54  
    55  statement ok
    56  ALTER TABLE b VALIDATE CONSTRAINT fk_ref
    57  
    58  # Now the plan should be simplified.
    59  query TTT colnames
    60  EXPLAIN SELECT
    61    s.a_z, s.a_y, s.a_x
    62  FROM
    63    (SELECT * FROM b WHERE a_z IS NOT NULL AND a_y IS NOT NULL AND a_x IS NOT NULL) AS s
    64    LEFT JOIN a AS t ON s.a_z = t.z AND s.a_y = t.y AND s.a_x = t.x
    65  WHERE
    66    t.z IS NULL
    67  ----
    68  tree         field        description
    69  ·            distributed  false
    70  ·            vectorized   true
    71  render       ·            ·
    72   └── norows  ·            ·