Lightning: Recognize "True"/"False"/etc as valid boolean values
- 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
Assessment
This issue has not been assessed yet.