# Experimental Features

This page lists the experimental features that are available in CockroachDB 2.1.

Warning:

This page describes experimental features. Their interfaces and outputs are subject to change, and there may be bugs.
If you encounter a bug, please file an issue.

## Session variables

The table below lists the experimental session settings available in CockroachDB 2.1. For a complete list of session variables, see `SHOW` (session settings).

Variable | Default Value | Description
--- | --- | ---
`experimental_force_lookup_join` | `'off'` | Indicates whether the planner should try and plan a lookup join where the left side is scanned and index lookups are done on the right side. Will emit a warning if a lookup join cannot be planned.
`experimental_force_split_at` | `'off'` | Indicates whether checks to prevent incorrect usage of `ALTER TABLE ... SPLIT AT` should be skipped.
`experimental_force_zigzag_join` | `'off'` | Reserved for future use. Currently this setting has no effect.
`experimental_serial_normalization` | `'rowid'` | If set to `'virtual_sequence'`, make the `SERIAL` pseudo-type optionally auto-create a sequence for better compatibility with Hibernate sequences.

## SQL statements

### Keep SQL audit logs

Log queries against a table to a file. For more information, see `ALTER TABLE ... EXPERIMENTAL_AUDIT`.

    > ALTER TABLE t EXPERIMENTAL_AUDIT SET READ WRITE;

### Relocate leases and replicas

You have the following options for controlling lease and replica location:

1. Relocate leases and replicas using `EXPERIMENTAL_RELOCATE`
2. Relocate just leases using `EXPERIMENTAL_RELOCATE LEASE`

For example, to distribute leases and ranges for N primary keys across N stores in the cluster, run a statement with the following structure:

    > ALTER TABLE t EXPERIMENTAL_RELOCATE SELECT ARRAY[<storeid1>, <storeid2>, ..., <storeidN>], <primarykeycol1>, <primarykeycol2>, ..., <primarykeycolN>;

New in v2.1: To relocate just the lease without moving the replicas, run a statement like the one shown below, which moves the lease for the range containing primary key 'foo' to store 1.

    > ALTER TABLE t EXPERIMENTAL_RELOCATE LEASE SELECT 1, 'foo';

### Show statement fingerprints

If two expressions share the same fingerprint, then they are the identical expression. Fingerprints are used by the cost-based optimizer for plan caching.

Example:

    > SHOW EXPERIMENTAL_FINGERPRINTS FROM TABLE t;

     index_name |     fingerprint
    ------------+---------------------
     primary    | 1999042440040364641
    (1 row)

### Show a table's ranges

Show the ranges that make up a table or index. For more information, see `SHOW EXPERIMENTAL_RANGES`.

    SHOW EXPERIMENTAL_RANGES FROM TABLE t;

### Turn on KV event tracing

Use session tracing (via `SHOW TRACE FOR SESSION`) to report the replicas of all KV events that occur during its execution.

Example:

    > SET tracing = on;
    > SELECT * from t;
    > SET tracing = off;
    > SHOW EXPERIMENTAL_REPLICA TRACE FOR SESSION;

                timestamp             | node_id | store_id | replica_id
    ----------------------------------+---------+----------+------------
     2018-10-18 15:50:13.345879+00:00 |       3 |        3 |          7
     2018-10-18 15:50:20.628383+00:00 |       2 |        2 |         26

### Check for constraint violations with `SCRUB`

Checks the consistency of `UNIQUE` indexes, `CHECK` constraints, and more. Partially implemented; see cockroachdb/cockroach#10425 for details.

Note:

This example uses the "users" table from our open-source, fictional peer-to-peer ride-sharing application,MovR.

    >  EXPERIMENTAL SCRUB table movr.users;

     job_uuid |        error_type        | database | table |                       primary_key                        |         timestamp         | repaired |                                                                                                                                                                         details
    ----------+--------------------------+----------+-------+----------------------------------------------------------+---------------------------+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
              | index_key_decoding_error | movr     | users | ('boston','0009eeb5-d779-4bf8-b1bd-8566533b105c')        | 2018-10-18 16:00:38.65916 | f        | {"error_message": "key ordering did not match datum ordering. IndexDescriptor=ASC", "index_name": "primary", "row_data": {"address": "e'06484 Christine Villages\\nGrantport, TN 01572'", "city": "'boston'", "credit_card": "'4634253150884'", "id": "'0009eeb5-d779-4bf8-b1bd-8566533b105c'", "name": "'Jessica Webb'"}}
              | index_key_decoding_error | movr     | users | ('los angeles','0001252c-fc16-4006-b6dc-c6b1a0fd1f5b')   | 2018-10-18 16:00:38.65916 | f        | {"error_message": "key ordering did not match datum ordering. IndexDescriptor=ASC", "index_name": "primary", "row_data": {"address": "e'91309 Warner Springs\\nLake Danielmouth, PR 33400'", "city": "'los angeles'", "credit_card": "'3584736360686445'", "id": "'0001252c-fc16-4006-b6dc-c6b1a0fd1f5b'", "name": "'Rebecca Gibson'"}}
              | index_key_decoding_error | movr     | users | ('new york','000169a5-e337-4441-b664-dae63e682980')      | 2018-10-18 16:00:38.65916 | f        | {"error_message": "key ordering did not match datum ordering. IndexDescriptor=ASC", "index_name": "primary", "row_data": {"address": "e'0787 Christopher Highway Apt. 363\\nHamptonmouth, TX 91864-2620'", "city": "'new york'", "credit_card": "'4578562547256688'", "id": "'000169a5-e337-4441-b664-dae63e682980'", "name": "'Christopher Johnson'"}}
              | index_key_decoding_error | movr     | users | ('paris','00089fc4-e5b1-48f6-9f0b-409905f228c4')         | 2018-10-18 16:00:38.65916 | f        | {"error_message": "key ordering did not match datum ordering. IndexDescriptor=ASC", "index_name": "primary", "row_data": {"address": "e'46735 Martin Summit\\nMichaelview, OH 10906-5889'", "city": "'paris'", "credit_card": "'5102207609888778'", "id": "'00089fc4-e5b1-48f6-9f0b-409905f228c4'", "name": "'Nicole Fuller'"}}
              | index_key_decoding_error | movr     | users | ('rome','000209fc-69a1-4dd5-8053-3b5e5769876d')          | 2018-10-18 16:00:38.65916 | f        | {"error_message": "key ordering did not match datum ordering. IndexDescriptor=ASC", "index_name": "primary", "row_data": {"address": "e'473 Barrera Vista Apt. 890\\nYeseniaburgh, CO 78087'", "city": "'rome'", "credit_card": "'3534605564661093'", "id": "'000209fc-69a1-4dd5-8053-3b5e5769876d'", "name": "'Sheryl Shea'"}}
              | index_key_decoding_error | movr     | users | ('san francisco','00058767-1e83-4e18-999f-13b5a74d7225') | 2018-10-18 16:00:38.65916 | f        | {"error_message": "key ordering did not match datum ordering. IndexDescriptor=ASC", "index_name": "primary", "row_data": {"address": "e'5664 Acevedo Drive Suite 829\\nHernandezview, MI 13516'", "city": "'san francisco'", "credit_card": "'376185496850202'", "id": "'00058767-1e83-4e18-999f-13b5a74d7225'", "name": "'Kevin Turner'"}}
              | index_key_decoding_error | movr     | users | ('seattle','0002e904-1256-4528-8b5f-abad16e695ff')       | 2018-10-18 16:00:38.65916 | f        | {"error_message": "key ordering did not match datum ordering. IndexDescriptor=ASC", "index_name": "primary", "row_data": {"address": "e'81499 Samuel Crescent Suite 631\\nLake Christopherborough, PR 50401'", "city": "'seattle'", "credit_card": "'38743493725890'", "id": "'0002e904-1256-4528-8b5f-abad16e695ff'", "name": "'Mark Williams'"}}
              | index_key_decoding_error | movr     | users | ('washington dc','00007caf-2014-4696-85b0-840e7d8b6db9') | 2018-10-18 16:00:38.65916 | f        | {"error_message": "key ordering did not match datum ordering. IndexDescriptor=ASC", "index_name": "primary", "row_data": {"address": "e'4578 Holder Trafficway\\nReynoldsside, IL 23520-7418'", "city": "'washington dc'", "credit_card": "'30454993082943'", "id": "'00007caf-2014-4696-85b0-840e7d8b6db9'", "name": "'Marie Miller'"}}
    (8 rows)

## Functions and Operators

The table below lists the experimental SQL functions and operators available in CockroachDB 2.1. For more information, see each function's documentation at Functions and Operators.

Function | Description
--- | ---
`experimental_strftime` | Format time using standard `strftime` notation.
`experimental_strptime` | Format time using standard `strptime` notation.
`experimental_uuid_v4()` | Return a UUID.

## See Also

- `SHOW` (session)
- Functions and Operators
- `ALTER TABLE ... EXPERIMENTAL_AUDIT`
- `SHOW EXPERIMENTAL_RANGES`
- `SHOW TRACE FOR SESSION`