`show create table` output for virtual column using json_extract has an extra space
- 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
Assessment
This issue has not been assessed yet.