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

     1  # LogicTest: 5node
     2  
     3  subtest scrub
     4  
     5  # TODO(radu): rework or remove these tests (the inner ORDER BY is ignored by
     6  # the optimizer).
     7  #
     8  # # Verify the index check execution plan uses a merge join.
     9  #
    10  # statement ok
    11  # CREATE TABLE test (k INT PRIMARY KEY, v INT, data INT, INDEX secondary (v) STORING (data))
    12  #
    13  # query T
    14  # SELECT url FROM [EXPLAIN (DISTSQL)
    15  #     SELECT leftside.v, leftside.k, leftside.data, rightside.v, rightside.k, rightside.data
    16  #     FROM
    17  #       (SELECT v,k,data FROM test@{FORCE_INDEX=[1]} ORDER BY v,k,data) AS leftside
    18  #     FULL OUTER JOIN
    19  #       (SELECT v,k,data FROM test@{FORCE_INDEX=[2]} ORDER BY v,k,data) AS rightside
    20  #       ON leftside.v = rightside.v AND leftside.k = rightside.k AND leftside.data = rightside.data
    21  #     WHERE (leftside.k IS NULL) OR
    22  #           (rightside.k IS NULL)
    23  # ]
    24  # ----
    25  # https://cockroachdb.github.io/distsqlplan/decode.html#eJyckc2K2zAQgO99CjGnLBlIJDs9CAq6dCFLGpdscio-uNY0a3AkM5Khy5J3L45hNw5x2vQ4I33zzc8bOG9pXRwogP4BEnKEhn1JIXjuUv2Hpf0Neo5QuaaNXTpHKD0T6DeIVawJNGyLnzVtqLDEszkgWIpFVZ_KNlwdCn41kUIEhKyNWhiFRqJJID8i-DZ-FA6x2BNoecR_lz97jsQzOfQaOUWjpmiS6ahG3aM5n1ENXYFK7-zdUyb_MWUyPiXCoYjli6jJaaFGremo9UPWOs-WmOzAlnfk375caf0b8Z6efOWIZ-mw_-1rQ1o87lYrke22XzfiKVuuAaGmX3FyNtzDF672L8MUIDxWdSTWYmKUWD6L9W61ehDZRkzM4j1-P4fE7iIJmhTNAs3n0Q0t7rnLhkLjXaDLTV2tPO_WQ3ZP_bqDb7mk7-zLk6YPsxN3SlgKsX-VfbB0_VPX4Dksb8LpAJaXsLoJJ7fNyR1mdQmnN-HFhTk_fvoTAAD__3P7gDg=
    26  #
    27  # # Verify the foreign key check execution plan uses a merge join.
    28  #
    29  # statement ok
    30  # CREATE TABLE parent (
    31  #   id INT PRIMARY KEY,
    32  #   id2 INT,
    33  #   UNIQUE INDEX (id, id2)
    34  # )
    35  #
    36  # statement ok
    37  # CREATE TABLE child (
    38  #   child_id INT PRIMARY KEY,
    39  #   id INT,
    40  #   id2 INT,
    41  #   FOREIGN KEY (id, id2) REFERENCES parent (id, id2)
    42  # )
    43  #
    44  # query T
    45  # SELECT url FROM [EXPLAIN (DISTSQL)
    46  #     SELECT p.child_id, p.id, p.id2
    47  #     FROM
    48  #       (SELECT child_id, id, id2 FROM child@{NO_INDEX_JOIN} ORDER BY id, id2) AS p
    49  #     FULL OUTER JOIN
    50  #       (SELECT id, id2 FROM parent@{FORCE_INDEX=[2]} ORDER BY id, id2) AS c
    51  #       ON p.id = c.id AND p.id2 = c.id2
    52  #     WHERE (p.id IS NOT NULL OR p.id2 IS NOT NULL) AND
    53  #           c.id IS NULL AND c.id2 IS NULL
    54  # ]
    55  # ----
    56  # https://cockroachdb.github.io/distsqlplan/decode.html#eJycklFrnTAUx9_3KcJ58nID1bi9BAYZbAWL0-G8T0PEmXNtqEskidBS_O7DCGstvRvdY345__wO5-QRtJFYdL_QAf8BCTQUJmt6dM7YFW0FmbwHHlNQepr9ihsKvbEI_BG88iMCh7r7OWKFnUR7FQMFib5TY3i2v1WjbLvZm1Zpifft-a5VsrV4bqfOovYiVECzUDCzf3I43w0IPFno__WR7PvYZKtaSdbe4YPYyEUxuyh-8s3aWIkW5c7VrMl_lbzS_Ve0A94YpdFesX339cOEnFyf8pyUp_pLRW7KrAAKI559JNiRivR4-GjVcOsjkRypYMcDULhWo0fLSRRFgpHsOynKmhSnPD-QsiKRSHfsQD4Vn0kk3gf6nHz4Q4BCOXtOREIFoyK9OL70LXur0E1GO3w5xldfjtfZoRxw24Uzs-3xmzV90GzHMuQCkOj8dsu2Q6bDVfhYz8PJG8LsZZj9NZzuwvHSLO9-BwAA__9_viDb
    57  
    58  subtest stats
    59  
    60  statement ok
    61  CREATE TABLE data (a INT, b INT, c FLOAT, d DECIMAL, PRIMARY KEY (a, b, c, d))
    62  
    63  # Split into ten parts.
    64  statement ok
    65  ALTER TABLE data SPLIT AT SELECT i FROM generate_series(1, 9) AS g(i)
    66  
    67  # Relocate the ten parts to the five nodes.
    68  statement ok
    69  ALTER TABLE data EXPERIMENTAL_RELOCATE
    70    SELECT ARRAY[i%5+1], i FROM generate_series(0, 9) AS g(i)
    71  
    72  # Verify data placement.
    73  query TTTI colnames,rowsort
    74  SELECT start_key, end_key, replicas, lease_holder FROM [SHOW RANGES FROM TABLE data]
    75  ----
    76  start_key  end_key  replicas  lease_holder
    77  NULL       /1       {1}       1
    78  /1         /2       {2}       2
    79  /2         /3       {3}       3
    80  /3         /4       {4}       4
    81  /4         /5       {5}       5
    82  /5         /6       {1}       1
    83  /6         /7       {2}       2
    84  /7         /8       {3}       3
    85  /8         /9       {4}       4
    86  /9         NULL     {5}       5
    87  
    88  query T
    89  SELECT url FROM [EXPLAIN (DISTSQL) CREATE STATISTICS s1 ON a FROM data]
    90  ----
    91  https://cockroachdb.github.io/distsqlplan/decode.html#eJy0lEGL2kAUx-_9FMM77cJIMkl03ZxWrIWA1a3JobDkMOs8rDRm0pkJtBW_e4lBtso6GYh7zIx_f39-b3h70L8KiCGdzWfTjNSqIF9Wy6_kZfb9eT5JFuTuc5Jm6bf5PZmuZpNsRtJskiVplkxTohlZLghvE4IbngOFUgpc8B1qiF-AAYUAKIRAIQIKQ8gpVEquUWupmp_sj4FE_IbYp7Atq9o0xzmFtVQI8R7M1hQIMWT8tcAVcoHK84GCQMO3xRFTqe2Oqz9PTQegkFa81DEZeIzwUhBGpPmBCigsaxOTJwb5gYKszRtLG75BiNmBuvdJ-a4qUHnD8y7tcbr9izFhvu83VVPD7eDgKviNV5dSCVQoznj54Wq1yWajcMONVB7z3UuSu8D3yWu9_olG31-tHJ5VZu6zYy6z89jAC3pNr6PRaXqjm08vcFcROKkIBl7YS0VHo5OKh5urCN1VhE4qwoEX9VLR0eikYnxzFZG7ishJRTTwhr1UdDQ6qXj80PX2DniFupKlxos19_4_-836Q7HBdldqWas1Piu5PmLaz-UxdzwQqE17y9qPpGyvmoL_h5k1HJyF2WU4sJM70KE1HdnDUZ_eQ2t4ZCeP-pAfrOGxnTzuQ360z8rveCb2R3bJzg-f_gUAAP__KeEKoQ==
    92  
    93  statement ok
    94  INSERT INTO data SELECT a, b, c::FLOAT, 1
    95  FROM generate_series(1,10) AS a, generate_series(1,10) AS b, generate_series(1,10) AS c;
    96  
    97  query T
    98  SELECT url FROM [EXPLAIN ANALYZE (DISTSQL) CREATE STATISTICS s1 ON a FROM data]
    99  ----
   100  https://cockroachdb.github.io/distsqlplan/decode.html#eJzElUFvm04Qxe__TzGaUyKtAws4cTjF_9SVrLp2aji0jThszMhFxSzdXdSmkb97BdRKHcWwlhLlyMKb9-Y3o-UB9Y8cQ4wms8l1DJXK4f1y8RFuJ59vZuPpHMbz8ezL1wmcvJtGcfRpdgrXy8k4nkAUj-NpFE-vI9AcFnMQrTIVRiTIsJApzcWGNIa3yJGhhwx9ZBggwyEmDEslV6S1VPUnD41gmv7C0GWYFWVl6uOE4UoqwvABTWZywhBjcZfTkkRKynGRYUpGZHljU6psI9T9VZ0BGUalKHQIA4eDKFLgIM03UshwUZkQrupQSv7UoEikIXC3rqaNyHMw2YZCcDUyvLs3tPvEP7uED9n_mGwZyso8ZtRGrAlDvmX2fURiU-aknOF-D-1xlP2mJlITKjKiDXzI2Dto_OhXFVKlpCjd80u2B6ON12tFa2GkcrhrHxJOPNeFu2r1nYw-PRjZ34vM7WfObWbu8IHj2Uzd65_6xdnoiKn3dLKb-vmLT92zR-hZIfQGjv8mCHs62SG8eHGEvj1C3wqhP3CCN0HY08kO4ejFEQb2CAMrhMHAGdog9PsRcu8Igj2N7Ahevurt_YzxknQpC01PbvHnK7v17U7pmtpfgZaVWtGNkqvGpn1cNLrmICVt2re8fZgW7as64L9i3in29sT8qdjrdu6x9jvVQbc4OCZ3Q7EB-neLNBUmBBeaFdovPOwsfN6d6vy1Ul10Fh51pxq9VqrL7vm7PavXvbjH5Eq2__0JAAD__2JIgrs=
   101  
   102  query T
   103  SELECT url FROM [EXPLAIN (DISTSQL, TYPES) SELECT * FROM data]
   104  ----
   105  https://cockroachdb.github.io/distsqlplan/decode.html#eJykk8GO0zAQhu88hfWfADlynaQccgJBkSqVbml6AK1yMPGwRErtYDsSqOq7ozorLUW7yNteItkzX_4vo8kB_mePCvVitXi_Y6Pr2cftzSd2u_iyWb1brtnLD8t6V39ecbb7ulnUr9h95-upT6ugGnAYq2mt9uRR3UKCIwdHAY4SHHM0HIOzLXlv3anlEIGl_oVqxtGZYQyn64ajtY5QHRC60BMq7NS3nrakNDkxA4emoLo-xgyu2yv3--3JARz1oIyvWCYkU0YzyWz4QQ7NkcOO4SHBB3VHqOSRP2HxED4a6zQ50ufBnQng98_vvVUhlttur3o0x0c-Ym0zO4j52Vue8srPvGT6dGTKdITMRH7BfPJ0jzzJI89EcYFHke5RJHkUmSgv8CjTPcokjzIT8yv39hGPLfnBGk9Jmzc7rS7pO5p-Am9H19LG2TbGTMebyMULTT5MVTkdliaWouDfsPwv_OYMnv0L59ckF9fA5TXw_Flwc3zxJwAA__8LVcwc