pingcap / pingcap/tidb

`show create table` output for virtual column using json_extract has an extra space

Open
#46,182 1 comment 0 reactions 1 assignee Claimed by @bb7133 View on GitHub
severity/moderate sig/sql-infra type/bug
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

`show create table` output for virtual column using `json_extract` has an extra space in TiDB. This issue is based on Ruby on Rails unit test failure below.

```ruby
Failure:
VirtualColumnTest#test_schema_dumping [/Users/yahonda/src/github.com/rails/rails/activerecord/test/cases/adapters/abstract_mysql_adapter/virtual_column_test.rb:65]:
Expected /t\.virtual\s+"profile_email",\s+type: :string,\s+as: "json_extract\(`profile`,\w*?'\$\.email'\)", stored: true$/i to match "# This file is auto-generated from the current state of the database. Instead
# of editing this file, please use the migrations feature of Active Record to
# incrementally modify your database, and then regenerate this schema definition.
#
# This file is the source Rails uses to define your schema when running `bin/rails
# db:schema:load`. When creating a new database, `bin/rails db:schema:load` tends to
# be faster and is potentially less error prone than running all of your
# migrations from scratch. Old migrations may fail to apply correctly if those
# migrations use external dependencies or application code.
#
# It's strongly recommended that you check this file into your version control system.

ActiveRecord::Schema[7.1].define(version: 0) do
create_table \"virtual_columns\", charset: \"utf8mb4\", collation: \"utf8mb4_bin\", force: :cascade do |t|
t.string \"name\"
t.virtual \"upper_name\", type: :string, as: \"upper(`name`)\"
t.virtual \"name_length\", type: :integer, as: \"length(`name`)\", stored: true
t.virtual \"name_octet_length\", type: :integer, as: \"octet_length(`name`)\", stored: true
t.json \"profile\"
t.virtual \"profile_email\", type: :string, as: \"json_extract(`profile`, _utf8mb4'$.email')\", stored: true
t.datetime \"time\"
t.virtual \"time_mirror\", type: :datetime, as: \"`time`\"
end

end
"
```

### 1. Minimal reproduce step (Required)
1. Startup tiup playground
```
% tiup playground nightly
```
2. Connect to tiup playground
%mysql --comments --host 127.0.0.1 --port 4000 -u root test

3. Create `virtual_columns` table that has a generated column named `profile_email` using json_extract.
```sql
CREATE TABLE `virtual_columns`
(
`id` bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
`profile` json,
`profile_email` varchar(255) AS (json_extract(`profile`,_utf8mb4'$.email')) STORED
)
;
```
4. Run "show create table `virtual_columns`\G"

```sql
show create table `virtual_columns`\G
```

### 2. What did you expect to see? (Required)
The show create table output does not have an extra space in json_extract function like `json_extract(`profile`,_utf8mb4'$.email')` as MySQL 8.0.33 does.

```sql
mysql> show create table `virtual_columns`\G
*************************** 1. row ***************************
Table: virtual_columns
Create Table: CREATE TABLE `virtual_columns` (
`id` bigint NOT NULL AUTO_INCREMENT,
`profile` json DEFAULT NULL,
`profile_email` varchar(255) GENERATED ALWAYS AS (json_extract(`profile`,_utf8mb4'$.email')) STORED,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql>
```

### 3. What did you see instead (Required)
The show create table output does has an extra space in json_extract function like `json_extract(`profile`, _utf8mb4'$.email')`

```sql
mysql> show create table `virtual_columns`\G
*************************** 1. row ***************************
Table: virtual_columns
Create Table: CREATE TABLE `virtual_columns` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`profile` json DEFAULT NULL,
`profile_email` varchar(255) GENERATED ALWAYS AS (json_extract(`profile`, _utf8mb4'$.email')) STORED,
PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.01 sec)

mysql>
```

### 4. What is your TiDB version? (Required)

```sql
mysql> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v7.4.0-alpha
Edition: Community
Git Commit Hash: 4fc7970f216e6ff0e2c9f09d07a9545ba128243c
Git Branch: heads/refs/tags/v7.4.0-alpha
UTC Build Time: 2023-08-16 14:25:04
GoVersion: go1.21.0
Race Enabled: false
Check Table Before Drop: false
Store: tikv
1 row in set (0.00 sec)

mysql>
```

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.