Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Digester does not support escaped reserved words #14241

Closed
wwar opened this issue Dec 25, 2019 · 3 comments · Fixed by #22893
Closed

Digester does not support escaped reserved words #14241

wwar opened this issue Dec 25, 2019 · 3 comments · Fixed by #22893
Labels
component/parser help wanted Denotes an issue that needs help from a contributor. Must meet "help wanted" guidelines. severity/minor sig/sql-infra SIG: SQL Infra type/bug The issue is confirmed as a bug.

Comments

@wwar
Copy link

wwar commented Dec 25, 2019

Bug Report

Please answer these questions before submitting your issue. Thanks!

  1. What did you do?

This is a minor issue, but it bugs me that the digest text could print back invalid SQL. i.e. start by creating a table in any schema, and then read back in performance_schema:

CREATE TABLE `table` (a int);
  1. What did you expect to see?

In MySQL 8.0, the digest_text will be:

mysql> SELECT * FROM performance_schema.events_statements_summary_by_digest where digest_text like '%table%'\G
...
                SCHEMA_NAME: test
                     DIGEST: 9f5c848d0fad440b84d889d5506889b0980cf8602617ed7bd02b3bf168fc5154
                DIGEST_TEXT: CREATE TABLE `table` ( `a` INTEGER )
                 COUNT_STAR: 1
             SUM_TIMER_WAIT: 22069280000
             MIN_TIMER_WAIT: 22069280000
             AVG_TIMER_WAIT: 22069280000
             MAX_TIMER_WAIT: 22069280000
              SUM_LOCK_TIME: 363000000
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 0
          SUM_ROWS_EXAMINED: 0
SUM_CREATED_TMP_DISK_TABLES: 0
     SUM_CREATED_TMP_TABLES: 0
       SUM_SELECT_FULL_JOIN: 0
 SUM_SELECT_FULL_RANGE_JOIN: 0
           SUM_SELECT_RANGE: 0
     SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 0
      SUM_SORT_MERGE_PASSES: 0
             SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 0
     SUM_NO_GOOD_INDEX_USED: 0
                 FIRST_SEEN: 2019-12-25 12:20:42.723886
                  LAST_SEEN: 2019-12-25 12:20:42.723886
                QUANTILE_95: 22908676527
                QUANTILE_99: 22908676527
               QUANTILE_999: 22908676527
          QUERY_SAMPLE_TEXT: CREATE TABLE `table` (a int)
          QUERY_SAMPLE_SEEN: 2019-12-25 12:20:42.723886
    QUERY_SAMPLE_TIMER_WAIT: 22069280000
2 rows in set (0.01 sec)
  1. What did you see instead?

In TiDB:

*************************** 12. row ***************************
       SUMMARY_BEGIN_TIME: 2019-12-25 12:00:00
         SUMMARY_END_TIME: 2019-12-25 12:30:00
                STMT_TYPE: createtable
              SCHEMA_NAME: test
                   DIGEST: e302b5acc98f3e18ff8ec1da66df4130f1113b53e8cc49944e5679d6ebd7890a
              DIGEST_TEXT: create table table ( a int )
              TABLE_NAMES: test.table
              INDEX_NAMES: NULL
              SAMPLE_USER: root@127.0.0.1
               EXEC_COUNT: 1
              SUM_LATENCY: 608728473
              MAX_LATENCY: 608728473
              MIN_LATENCY: 608728473
              AVG_LATENCY: 608728473
        AVG_PARSE_LATENCY: 23120702
        MAX_PARSE_LATENCY: 23120702
      AVG_COMPILE_LATENCY: 78389
      MAX_COMPILE_LATENCY: 78389
             COP_TASK_NUM: 0
     AVG_COP_PROCESS_TIME: 0
     MAX_COP_PROCESS_TIME: 0
  MAX_COP_PROCESS_ADDRESS: NULL
        AVG_COP_WAIT_TIME: 0
        MAX_COP_WAIT_TIME: 0
     MAX_COP_WAIT_ADDRESS: NULL
         AVG_PROCESS_TIME: 0
         MAX_PROCESS_TIME: 0
            AVG_WAIT_TIME: 0
            MAX_WAIT_TIME: 0
         AVG_BACKOFF_TIME: 0
         MAX_BACKOFF_TIME: 0
           AVG_TOTAL_KEYS: 0
           MAX_TOTAL_KEYS: 0
       AVG_PROCESSED_KEYS: 0
       MAX_PROCESSED_KEYS: 0
        AVG_PREWRITE_TIME: 0
        MAX_PREWRITE_TIME: 0
          AVG_COMMIT_TIME: 0
          MAX_COMMIT_TIME: 0
   AVG_GET_COMMIT_TS_TIME: 0
   MAX_GET_COMMIT_TS_TIME: 0
  AVG_COMMIT_BACKOFF_TIME: 0
  MAX_COMMIT_BACKOFF_TIME: 0
    AVG_RESOLVE_LOCK_TIME: 0
    MAX_RESOLVE_LOCK_TIME: 0
AVG_LOCAL_LATCH_WAIT_TIME: 0
MAX_LOCAL_LATCH_WAIT_TIME: 0
           AVG_WRITE_KEYS: 0
           MAX_WRITE_KEYS: 0
           AVG_WRITE_SIZE: 0
           MAX_WRITE_SIZE: 0
     AVG_PREWRITE_REGIONS: 0
     MAX_PREWRITE_REGIONS: 0
            AVG_TXN_RETRY: 0
            MAX_TXN_RETRY: 0
        SUM_BACKOFF_TIMES: 0
            BACKOFF_TYPES: NULL
                  AVG_MEM: 0
                  MAX_MEM: 0
        AVG_AFFECTED_ROWS: 0
               FIRST_SEEN: 2019-12-25 12:14:40
                LAST_SEEN: 2019-12-25 12:14:40
        QUERY_SAMPLE_TEXT: CREATE TABLE `table` (a int)
         PREV_SAMPLE_TEXT:

I also like that MySQL capitalizes the keywords, but this can be a personal preference. MySQL is also normalizing the data type of int to integer. We can see that TiDB doesn't do this with a second example. It's probably not that important, since MySQL doesn't consistently do it (the data type serial, which is an alias for a bigint auto-inc, will digest with the actual word serial.)

mysql> SELECT * FROM performance_schema.events_statements_summary_by_digest where digest_text like '%integer%'\G
*************************** 1. row ***************************
       SUMMARY_BEGIN_TIME: 2019-12-25 12:00:00
         SUMMARY_END_TIME: 2019-12-25 12:30:00
                STMT_TYPE: createtable
              SCHEMA_NAME: test
                   DIGEST: a5b735dc3cf780368f27e925b1f454e11ef8533a1639763a1a79e4c0da170894
              DIGEST_TEXT: create table table ( a integer )
              TABLE_NAMES: test.table
              INDEX_NAMES: NULL
              SAMPLE_USER: root@127.0.0.1
               EXEC_COUNT: 1
              SUM_LATENCY: 925483353
              MAX_LATENCY: 925483353
              MIN_LATENCY: 925483353
              AVG_LATENCY: 925483353
        AVG_PARSE_LATENCY: 42070
        MAX_PARSE_LATENCY: 42070
      AVG_COMPILE_LATENCY: 68999
      MAX_COMPILE_LATENCY: 68999
             COP_TASK_NUM: 0
     AVG_COP_PROCESS_TIME: 0
     MAX_COP_PROCESS_TIME: 0
  MAX_COP_PROCESS_ADDRESS: NULL
        AVG_COP_WAIT_TIME: 0
        MAX_COP_WAIT_TIME: 0
     MAX_COP_WAIT_ADDRESS: NULL
         AVG_PROCESS_TIME: 0
         MAX_PROCESS_TIME: 0
            AVG_WAIT_TIME: 0
            MAX_WAIT_TIME: 0
         AVG_BACKOFF_TIME: 0
         MAX_BACKOFF_TIME: 0
           AVG_TOTAL_KEYS: 0
           MAX_TOTAL_KEYS: 0
       AVG_PROCESSED_KEYS: 0
       MAX_PROCESSED_KEYS: 0
        AVG_PREWRITE_TIME: 0
        MAX_PREWRITE_TIME: 0
          AVG_COMMIT_TIME: 0
          MAX_COMMIT_TIME: 0
   AVG_GET_COMMIT_TS_TIME: 0
   MAX_GET_COMMIT_TS_TIME: 0
  AVG_COMMIT_BACKOFF_TIME: 0
  MAX_COMMIT_BACKOFF_TIME: 0
    AVG_RESOLVE_LOCK_TIME: 0
    MAX_RESOLVE_LOCK_TIME: 0
AVG_LOCAL_LATCH_WAIT_TIME: 0
MAX_LOCAL_LATCH_WAIT_TIME: 0
           AVG_WRITE_KEYS: 0
           MAX_WRITE_KEYS: 0
           AVG_WRITE_SIZE: 0
           MAX_WRITE_SIZE: 0
     AVG_PREWRITE_REGIONS: 0
     MAX_PREWRITE_REGIONS: 0
            AVG_TXN_RETRY: 0
            MAX_TXN_RETRY: 0
        SUM_BACKOFF_TIMES: 0
            BACKOFF_TYPES: NULL
                  AVG_MEM: 0
                  MAX_MEM: 0
        AVG_AFFECTED_ROWS: 0
               FIRST_SEEN: 2019-12-25 12:23:10
                LAST_SEEN: 2019-12-25 12:23:10
        QUERY_SAMPLE_TEXT: CREATE TABLE `table` (a integer)
         PREV_SAMPLE_TEXT: 
1 row in set (0.00 sec)
  1. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?
mysql> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-alpha-1246-g7f8c3c808
Git Commit Hash: 7f8c3c8083ced5a0d05684c1b55654577896a846
Git Branch: master
UTC Build Time: 2019-12-24 02:06:14
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.01 sec)
@wwar wwar added the type/bug The issue is confirmed as a bug. label Dec 25, 2019
@winoros
Copy link
Member

winoros commented Dec 26, 2019

@djshow832 PTAL

@wwar wwar mentioned this issue Jun 28, 2020
Closed
@ghost
Copy link

ghost commented Jul 20, 2020

Confirming that this issue still exists in master:

DROP TABLE IF EXISTS `table`;
CREATE TABLE `table` (a int);
SELECT digest_text FROM information_schema.statements_summary WHERE digest_text like '%table%';

..


mysql> SELECT digest_text FROM information_schema.statements_summary WHERE digest_text like '%table%';
+-----------------------------------+
| digest_text                       |
+-----------------------------------+
| create table table ( a int )      |
| drop table if exists table        |
| create table t ( d datetime )     |
| drop table if exists t            |
| show tables in information_schema |
+-----------------------------------+
5 rows in set (0.00 sec)

mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-798-gd941ff5cc
Edition: Community
Git Commit Hash: d941ff5cc8b4babf9dcfdd91b66a5c53b798c122
Git Branch: master
UTC Build Time: 2020-07-18 05:54:02
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

@ghost ghost added the component/parser label Jul 31, 2020
@ghost ghost mentioned this issue Jul 31, 2020
Closed
@qw4990 qw4990 added severity/minor help wanted Denotes an issue that needs help from a contributor. Must meet "help wanted" guidelines. labels Aug 26, 2020
@wjhuang2016 wjhuang2016 mentioned this issue Nov 16, 2020
Closed
@wjhuang2016 wjhuang2016 added the sig/sql-infra SIG: SQL Infra label Dec 10, 2020
@ichn-hu ichn-hu mentioned this issue Dec 10, 2020
Closed
@morgo morgo mentioned this issue Feb 22, 2021
Closed
@xiongjiwei xiongjiwei mentioned this issue Feb 23, 2021
Merged
@morgo morgo mentioned this issue Feb 24, 2021
Merged
@ti-srebot
Copy link
Contributor

ti-srebot commented Feb 26, 2021 •
edited by seiya-annie
Loading

Please edit this comment or add a new comment to complete the following information

Not a bug

  1. Remove the 'type/bug' label
  2. Add notes to indicate why it is not a bug

Duplicate bug

  1. Add the 'type/duplicate' label
  2. Add the link to the original bug

Bug

Note: Make Sure that 'component', and 'severity' labels are added
Example for how to fill out the template: #20100

1. Root Cause Analysis (RCA) (optional)

2. Symptom (optional)

3. All Trigger Conditions (optional)

4. Workaround (optional)

5. Affected versions

[v3.0.0:v3.0.20],[v4.0.0:v4.0.11],[v5.0.0-rc]

6. Fixed versions

master

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
component/parser help wanted Denotes an issue that needs help from a contributor. Must meet "help wanted" guidelines. severity/minor sig/sql-infra SIG: SQL Infra type/bug The issue is confirmed as a bug.
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

parser: quote identifier with backquote when getting SQL digest xiongjiwei/tidb
5 participants
@qw4990 @winoros @wjhuang2016 @wwar @ti-srebot