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

     1  # LogicTest: local
     2  
     3  #
     4  # Tests that verify DistSQL support and auto mode determination.
     5  # The cluster size or distsql mode aren't important for these tests.
     6  #
     7  
     8  # "local" logic test configuration overrides the DistSQL mode to 'off', but
     9  # we're interested in behavior with 'auto' in this test file.
    10  statement ok
    11  SET distsql=auto
    12  
    13  statement ok
    14  CREATE TABLE kv (k INT PRIMARY KEY, v INT)
    15  
    16  # Verify that EXPLAIN (DISTSQL) hides the JSON column by default (#21089)
    17  query BT colnames
    18  EXPLAIN (DISTSQL) SELECT 1
    19  ----
    20  automatic  url
    21  false      https://cockroachdb.github.io/distsqlplan/decode.html#eJyMj0FLxDAUhO_-ijAnhYDtNTfRHgplXe0eBOkhJI-lEPtqXipCyX-XTQ5ee5xvZt7wdsh3gEH3cR6e-pO6f-nHy_g2PKixG7rni2qhsbCnk_0igflEi0ljjexIhOMN7SXQ-1-YRmNe1i3d8KThOBLMjjSnQDAI7GxQPzZsJKp5bKDhKdk5lHzW4C39tyXZK8G0WR9feCdZeRE6dLnJkwb5K9UvhLfo6BzZlZkqX0uvAE-SqttW0S_VylO--wsAAP__NiFqpA==
    22  
    23  # Check the JSON column is still there, albeit hidden.
    24  query T colnames
    25  SELECT json FROM [EXPLAIN (DISTSQL) SELECT * FROM kv] WHERE false
    26  ----
    27  json
    28  
    29  # Full table scan - distribute.
    30  query B
    31  SELECT automatic FROM [EXPLAIN (DISTSQL) SELECT * FROM kv]
    32  ----
    33  true
    34  
    35  # Partial scan - don't distribute.
    36  query B
    37  SELECT automatic FROM [EXPLAIN (DISTSQL) SELECT * FROM kv WHERE k=1]
    38  ----
    39  false
    40  
    41  # Partial scan - don't distribute.
    42  query B
    43  SELECT automatic FROM [EXPLAIN (DISTSQL) SELECT * FROM kv WHERE k>1]
    44  ----
    45  false
    46  
    47  # Partial scan with filter - distribute.
    48  query B
    49  SELECT automatic FROM [EXPLAIN (DISTSQL) SELECT * FROM kv WHERE k>1 AND v=1]
    50  ----
    51  true
    52  
    53  # Sort - distribute.
    54  query B
    55  SELECT automatic FROM [EXPLAIN (DISTSQL) SELECT * FROM kv WHERE k>1 ORDER BY v]
    56  ----
    57  true
    58  
    59  # Aggregation - distribute.
    60  query B
    61  SELECT automatic FROM [EXPLAIN (DISTSQL) SELECT k, sum(v) FROM kv WHERE k>1 GROUP BY k]
    62  ----
    63  true
    64  
    65  # Hard limit in scan - distribute.
    66  query B
    67  SELECT automatic FROM [EXPLAIN (DISTSQL) SELECT * FROM kv LIMIT 1]
    68  ----
    69  true
    70  
    71  # Soft limit in scan - don't distribute.
    72  # TODO(yuzefovich): soft limits are currently ignored in scans.
    73  query B
    74  SELECT automatic FROM [EXPLAIN (DISTSQL) SELECT * FROM kv UNION SELECT * FROM kv LIMIT 1]
    75  ----
    76  true
    77  
    78  # Limit after sort - distribute.
    79  query B
    80  SELECT automatic FROM [EXPLAIN (DISTSQL) SELECT * FROM kv WHERE k>1 ORDER BY v LIMIT 1]
    81  ----
    82  true
    83  
    84  # Limit after aggregation - distribute.
    85  query B
    86  SELECT automatic FROM [EXPLAIN (DISTSQL) SELECT k, sum(v) FROM kv WHERE k>1 GROUP BY k LIMIT 1]
    87  ----
    88  true
    89  
    90  statement ok
    91  CREATE TABLE kw (k INT PRIMARY KEY, w INT)
    92  
    93  # Join - distribute.
    94  query B
    95  SELECT automatic FROM [EXPLAIN (DISTSQL) SELECT * FROM kv NATURAL JOIN kw]
    96  ----
    97  true
    98  
    99  # Join with span - distribute.
   100  query B
   101  SELECT automatic FROM [EXPLAIN (DISTSQL) SELECT * FROM kv NATURAL JOIN kw WHERE k=1]
   102  ----
   103  true
   104  
   105  statement ok
   106  CREATE TABLE abc (a INT PRIMARY KEY, b INT, c INT, INDEX b (b))
   107  
   108  # Index join - don't distribute.
   109  query B
   110  SELECT automatic FROM [EXPLAIN (DISTSQL) SELECT * FROM abc WHERE b=1]
   111  ----
   112  false
   113  
   114  # Index join with filter on result - don't distribute.
   115  query B
   116  SELECT automatic FROM [EXPLAIN (DISTSQL) SELECT * FROM abc WHERE b=1 AND c%2=0]
   117  ----
   118  false
   119  
   120  # Index join with filter on index scan - distribute.
   121  query B
   122  SELECT automatic FROM [EXPLAIN (DISTSQL) SELECT * FROM abc WHERE b=1 AND a%2=0]
   123  ----
   124  true