docs: Explain Analyze delete doesn't do a dry run, results in lost data

[gigatexal]

Exalate commented:

Describe the problem

explain analyze doesn't issue a dry run when analyzing a delete statement leading to data being deleted

To Reproduce

create table foo(id int primary key, name string);
insert into foo(id, name) select 1,'bob' union all select 2,'james';

output

root@figo-p-cockroachdb-public:26257/authdb> select * from foo;
  id | name
+----+-------+
   1 | bob
   2 | james
(2 rows)

Time: 8.726078ms

root@figo-p-cockroachdb-public:26257/authdb> explain analyze delete from foo where id = 1 and name = 'bob';
  automatic |                                                                                                                                      url
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    false   | https://cockroachdb.github.io/distsqlplan/decode.html#eJyMj7FqxjAMhPc-RbjZUGf1G2RpS9fiwdgiBFwrWDIUgt-9xB7-NeN9d6dDFwon-gi_JHA_WOENzsqRRLjeaAa29AdnDY5yNr2xN4hcCe6CHpoJDpljyEvkVnSx7xYGiTQcecS7ATd9lUXDTnBrN88HvklOLkKPLtvuDSjtNJ8QbjXSV-U4Zqb8HL0BEolOd51iK9Pqvr_9BwAA__-ieGEo
(1 row)

Time: 239.5563ms

root@figo-p-cockroachdb-public:26257/authdb> select * from foo;
  id | name
+----+-------+
   2 | james
(1 row)

Time: 1.030155ms

Expected behavior Don't delete data

Added data

  application_name                  | $ cockroach sql
  bytea_output                      | hex
  client_encoding                   | UTF8
  client_min_messages               | notice
  crdb_version                      | CockroachDB OSS v2.1.3 (x86_64-unknown-linux-gnu, built 2018/12/17 19:15:31, go1.10.3)
  database                          | authdb
  datestyle                         | ISO, MDY
  default_transaction_isolation     | serializable
  default_transaction_read_only     | off
  distsql                           | auto
  experimental_force_lookup_join    | off
  experimental_force_split_at       | off
  experimental_force_zigzag_join    | off
  experimental_serial_normalization | rowid
  extra_float_digits                | 2
  integer_datetimes                 | on
  intervalstyle                     | postgres
  max_index_keys                    | 32
  node_id                           | 1
  optimizer                         | on
  search_path                       | public
  server_encoding                   | UTF8
  server_version                    | 9.5.0
  server_version_num                | 90500
  session_user                      | root
  sql_safe_updates                  | on
  standard_conforming_strings       | on
  statement_timeout                 | 0
  timezone                          | UTC
  tracing                           | off
  transaction_isolation             | serializable
  transaction_priority              | normal
  transaction_read_only             | off
  transaction_status                | NoTxn

Jira Issue: DOC-223

[RaduBerinde]

This is intended and consistent with the documentation https://www.cockroachlabs.com/docs/stable/explain-analyze.html

However, I can see how this can be confusing. We should at least post a big warning in the documentation.

Note that this is consistent with Postgres (we would have probably chosen different syntax).

[RaduBerinde]

See the warning and suggestion in https://www.postgresql.org/docs/9.1/sql-explain.html. Our docs should have something similar.

[gigatexal]

Yup, this is embarrassing. https://www.cockroachlabs.com/docs/stable/explain-analyze.html

Closing, thanks everyone!

[RaduBerinde]

Let's keep it open to track improving the documentation.

[jseldess]

@RaduBerinde, ok if we move this to the docs repo?

[RaduBerinde]

Yes please!