pingcap / pingcap/tidb

Lightning: Recognize "True"/"False"/etc as valid boolean values

Open
#38,008 0 comments 0 reactions 0 assignees View on GitHub
component/lightning type/feature-request
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Feature Request

**Is your feature request related to a problem? Please describe:**

When exporting data from PostgreSQL or other databases that have a proper `BOOLEAN` type (unlike MySQL and TiDB which is just an alias of `TINYINT(1)`), their content is sometimes written literally as FALSE and TRUE rather than 0 and 1.

For example, from PostgreSQL 14:

```sql
CREATE SCHEMA test;
CREATE TABLE test.tb (a boolean);
INSERT INTO test.tb VALUES (true), (false);
COPY test.tb TO '/tmp/1.csv' DELIMITER ',' CSV HEADER;
```

The content of the CSV file is

```csv
a
t
f
```

Here we see "TRUE" is written as `t` and "FALSE" is written as `f`. Such file cannot be imported correctly into TiDB because `t` and `f` are not valid `TINYINT(1)` values.

```console
$ cat test.tb-schema.sql
create table test.tb(a boolean);
$ cat test.tb.0.csv
a
t
f
$ tiup tidb-lightning -tidb-port 4000 -d . -backend tidb
...
tidb lightning exit successfully
$ mysql -u root -h 127.0.0.1 -P 4000 -e 'select * from test.tb'
+------+
| a |
+------+
| 0 |
| 0 |
+------+
```

**Describe the feature you'd like:**

Let Lightning recognize TRUE/FALSE etc when the target type is `TINYINT(1)`. In particular the following strings should be supported,

* true / false
* t / f
* yes / no
* y / n
* on / off

These form a set of recognized representations in [PostgreSQL 14](https://www.postgresql.org/docs/14/datatype-boolean.html) and [YAML 1.1](https://yaml.org/type/bool.html) Boolean types which should have been enough in most cases.

**Describe alternatives you've considered:**

Support the LOAD DATA `SET` clause so users can manually transform the input. It has to be done on every boolean column which is tedious.

**Teachability, Documentation, Adoption, Migration Strategy:**

The transformation is performed automatically so no configuration is needed.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.