ddl: make output field name in `show tables/databases stmt` compatible with mysql by likzn · Pull Request #35136 · pingcap/tidb

likzn · 2022-06-02T10:25:52Z

What problem does this PR solve?

Issue Number: close #35116

Problem Summary: SHOW TABLES/DATABASES ... LIKE ..., the output field is a little different with Mysql.

QUERY >	 SHOW TABLES LIKE 'apigw_routes'
TiDB  >
+-------------------+
| Tables_in_corteza |
+-------------------+
| apigw_routes      |
+-------------------+
MySQL >
+----------------------------------+
| Tables_in_corteza (apigw_routes) |
+----------------------------------+
| apigw_routes                     |
+----------------------------------+

And the mysql will add PatternLikeName at the behind when we use like. If we do not use like, the behavior is the same.

Ref in Mysql: https://github.com/mysql/mysql-server/blob/3e90d07c3578e4da39dc1bce73559bbdf655c28c/sql/dd/info_schema/show.cc#L537-L545

What is changed and how it works?

When we buildShowSchema, format field name with PatternLikeExpr to be compatible with Mysql.

Check List

Tests

Unit test
Integration test
Manual test (add detailed scripts or steps below)
No code

Side effects

Performance regression: Consumes more CPU
Performance regression: Consumes more Memory
Breaking backward compatibility

Documentation

Release note

Please refer to Release Notes Language Style Guide to write a quality release note.

Make output field name in statement of `show tables/databases` compatible with mysql

ti-chi-bot · 2022-06-02T10:25:53Z

[REVIEW NOTIFICATION]

This pull request has been approved by:

tangenta
xhebox

To complete the pull request process, please ask the reviewers in the list to review by filling /cc @reviewer in the comment.
After your PR has acquired the required number of LGTMs, you can assign this pull request to the committer in the list by filling /assign @committer in the comment to help you merge this pull request.

The full list of commands accepted by this bot can be found here.

Details

Reviewer can indicate their review by submitting an approval review.
Reviewer can cancel approval by submitting a request changes review.

likzn · 2022-06-02T12:52:44Z

/run-unit-test

sre-bot · 2022-06-02T13:02:40Z

Code Coverage Details: https://codecov.io/github/pingcap/tidb/commit/9e9a37191ab9631b7998df055d755b9f1ec80a00

likzn · 2022-06-02T13:09:18Z

/cc @hawkingrei @xiongjiwei @xhebox

likzn · 2022-06-02T13:11:20Z

PTAL if free. By the way, how to join the slack of tidb-community? I input the email, but nothing response. And i also post in the AskAug https://asktug.com/t/topic/693265. But nobody reply yet

xhebox · 2022-06-06T02:09:33Z

PTAL if free. By the way, how to join the slack of tidb-community? I input the email, but nothing response. And i also post in the AskAug https://asktug.com/t/topic/693265. But nobody reply yet

Should receive one email if you join the slack. Maybe in the trash.

likzn · 2022-06-06T03:52:52Z

PTAL if free. By the way, how to join the slack of tidb-community? I input the email, but nothing response. And i also post in the AskAug https://asktug.com/t/topic/693265. But nobody reply yet

Should receive one email if you join the slack. Maybe in the trash.

It is strange. I had recived no email about slack.. Trash is also checked.

planner/core/planbuilder.go

Co-authored-by: tangenta <tangenta@126.com>

bb7133 · 2022-06-06T13:20:37Z

planner/core/planbuilder.go

-		if extractor.Extract() {
+		if extractor := newShowBaseExtractor(*show); extractor.Extract() {
 			p.Extractor = extractor
-			// Avoid building Selection.


Could you explain why removing show.Pattern = nil a little bit?

This is introduced in #31919 to fix case insentive with some show statment. It mean if we have newShowBaseExtractor , we will not build show.Pattern in line 3015. But in this case, we need extract patternLikeName in show.Pattern. So i remove it and add an extra check in line 3015 to make them behave same.

I think it is safer to mark an extra flag like noShowPattern or somethine similar. There are some other extractors that may be introduced later.

Done. An extra flag is more clearer.

Done. An extra flag is more clearer.

You don't need to add it to LogicalShow struct, a variable in buildShow is enough 🤣

Oh, yes. Just finished exercise, my head is a little dizzy

likzn · 2022-06-10T07:05:06Z

Has any friend take a look about this

likzn · 2022-06-14T02:29:49Z

/cc @wjhuang2016 @morgo

tangenta · 2022-06-28T09:29:06Z

/merge

ti-chi-bot · 2022-06-28T09:29:13Z

This pull request has been accepted and is ready to merge.

Details

Commit hash: 4ce665e

likzn · 2022-06-28T11:32:48Z

/run-check_dev_2

likzn · 2022-06-28T11:36:40Z

/run-mysql-test

xhebox · 2022-06-29T03:38:06Z

/hold

I think there is a stable test failure, @likzn , I'll take a look.

likzn · 2022-06-29T05:52:40Z

It is introduced by this PR. Error Message:

show databases like "test%";:
NEED: show tables like 't_';\nTables_in_lowercase_table\nT3\nt4\ndrop \n
BUT GOT: show tables like 't_';\nTables_in_lowercase_table (t_)\nT3\nt4\n\n"

The difference of them is the output field name. But i cant find where the mysql_test is. And i find test result in mysql is below https://github.com/mysql/mysql-server/blob/8d8c986e5716e38cb776b627a8eee9e92241b4ce/mysql-test/r/lowercase_table.result#L29-L32.

tangenta · 2022-06-29T07:12:11Z

/run-mysql-test tidb-test=pr/1853

tangenta · 2022-06-29T07:28:39Z

/unhold

sre-bot · 2022-06-29T08:23:34Z

TiDB MergeCI notify

🔴 Bad News! New failing [1] after this pr merged.
These new failed integration tests seem to be caused by the current PR, please try to fix these new failed integration tests, thanks!

CI Name	Result	Duration	Compare with Parent commit
idc-jenkins-ci-tidb/common-test	🟥 failed 2, success 10, total 12	12 min	New failing
idc-jenkins-ci-tidb/integration-common-test	🔴 failed 2, success 9, total 11	18 min	Existing failure
idc-jenkins-ci/integration-cdc-test	🟢 all 35 tests passed	25 min	Existing passed
idc-jenkins-ci-tidb/integration-ddl-test	🟢 all 6 tests passed	7 min 16 sec	Existing passed
idc-jenkins-ci-tidb/sqllogic-test-2	🟢 all 28 tests passed	6 min 40 sec	Existing passed
idc-jenkins-ci-tidb/tics-test	🟢 all 1 tests passed	5 min 43 sec	Existing passed
idc-jenkins-ci-tidb/sqllogic-test-1	🟢 all 26 tests passed	4 min 59 sec	Existing passed
idc-jenkins-ci-tidb/integration-compatibility-test	🟢 all 1 tests passed	3 min 28 sec	Existing passed
idc-jenkins-ci-tidb/mybatis-test	🟢 all 1 tests passed	3 min 13 sec	Existing passed
idc-jenkins-ci-tidb/plugin-test	🟢 build success, plugin test success	4min	Existing passed

…vars * upstream/master: (28 commits) *: enable unconvert (pingcap#35821) log-backup: check timezone when using datatime format ts string (pingcap#35811) planner: support the leading hint for outer join (pingcap#35264) lightning: revert new policy of allocating rowid and refine it later (pingcap#35817) txn: manage the initialization of RCCheckTS by transaction context provider (pingcap#35554) ddl: make output field name in `show tables/databases stmt` compatible with mysql (pingcap#35136) txn: remove `legacy.SimpleTxnContextProvider` (pingcap#35667) txn: fix the error message if retry could not be used for rc check ts (pingcap#35474) *: support canceling DDL statements with KILL (pingcap#35803) lightning: avoid extracting db schema when schema file is not provided (pingcap#35783) *: enable all gosimples (pingcap#35762) tools/check: skip 'tests' directory in 'make ut' (pingcap#35800) metrics: add loading region cache duration (pingcap#34679) ddl: DROP TABLE/VIEW/SEQUENCE now use XXXStmt as parameter (pingcap#35741) types: fix incompatible implementation of jsonpath extraction (pingcap#35320) planner: fix TRACE PLAN TARGET = 'estimation' panic when meeting partition table (pingcap#35743) *: Add `testfork.RunTest` to run multiple tests in one function (pingcap#35746) sessionctx/variable: add tests to ensure skipInit can be removed (pingcap#35703) helper: request another PD if one of them is unavailable (pingcap#35750) metrics: add cached table related metrics to grafana panel (pingcap#34718) ...

compatible show tables/databases stmt with mysql

60b8709

ti-chi-bot added release-note Denotes a PR that will be considered when it comes time to generate release notes. size/M Denotes a PR that changes 30-99 lines, ignoring generated files. labels Jun 2, 2022

fix test

8454e21

ti-chi-bot added size/L Denotes a PR that changes 100-499 lines, ignoring generated files. and removed size/M Denotes a PR that changes 30-99 lines, ignoring generated files. labels Jun 2, 2022

fix test

ce18ca5

ti-chi-bot added size/M Denotes a PR that changes 30-99 lines, ignoring generated files. and removed size/L Denotes a PR that changes 100-499 lines, ignoring generated files. labels Jun 2, 2022

likzn and others added 4 commits June 2, 2022 19:31

clean code

e5e99c1

Merge branch 'master' into fix_show_field

17c644d

fix test

2f060fa

Merge remote-tracking branch 'origin/fix_show_field' into fix_show_field

c07b523

ti-chi-bot requested review from hawkingrei, xhebox and xiongjiwei June 2, 2022 13:09

tangenta reviewed Jun 6, 2022

View reviewed changes

planner/core/planbuilder.go Outdated Show resolved Hide resolved

Update planner/core/planbuilder.go

b2283c1

Co-authored-by: tangenta <tangenta@126.com>

bb7133 reviewed Jun 6, 2022

View reviewed changes

likzn requested review from bb7133 and tangenta June 7, 2022 12:19

likzn added 2 commits June 24, 2022 13:59

refactor

1a25e10

Merge remote-tracking branch 'origin/fix_show_field' into fix_show_field

4ce665e

xhebox approved these changes Jun 24, 2022

View reviewed changes

ti-chi-bot added the status/LGT1 Indicates that a PR has LGTM 1. label Jun 24, 2022

tangenta approved these changes Jun 28, 2022

View reviewed changes

ti-chi-bot added status/LGT2 Indicates that a PR has LGTM 2. and removed status/LGT1 Indicates that a PR has LGTM 1. labels Jun 28, 2022

ti-chi-bot added the status/can-merge Indicates a PR has been approved by a committer. label Jun 28, 2022

ti-chi-bot added 3 commits June 28, 2022 17:29

Merge branch 'master' into fix_show_field

dbbb054

Merge branch 'master' into fix_show_field

a76c382

Merge branch 'master' into fix_show_field

d00da11

ti-chi-bot added 3 commits June 29, 2022 10:50

Merge branch 'master' into fix_show_field

0d68569

Merge branch 'master' into fix_show_field

90e1aae

Merge branch 'master' into fix_show_field

639586b

ti-chi-bot added the do-not-merge/hold Indicates that a PR should not merge because someone has issued a /hold command. label Jun 29, 2022

Merge branch 'master' into fix_show_field

9e9a371

ti-chi-bot removed the do-not-merge/hold Indicates that a PR should not merge because someone has issued a /hold command. label Jun 29, 2022

ti-chi-bot merged commit 9bde478 into pingcap:master Jun 29, 2022

lance6716 mentioned this pull request Jun 30, 2022

test(dm): align TiDB SHOW statement output pingcap/tiflow#6120

Merged

tangenta mentioned this pull request Jul 22, 2022

planner/core: add tests for prepare show statements #36458

Merged

12 tasks

Conversation

likzn commented Jun 2, 2022

What problem does this PR solve?

What is changed and how it works?

Check List

Release note

Uh oh!

ti-chi-bot commented Jun 2, 2022 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

likzn commented Jun 2, 2022

Uh oh!

sre-bot commented Jun 2, 2022 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

likzn commented Jun 2, 2022

Uh oh!

likzn commented Jun 2, 2022

Uh oh!

xhebox commented Jun 6, 2022 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

likzn commented Jun 6, 2022 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

Uh oh!

bb7133 Jun 6, 2022

Choose a reason for hiding this comment

Uh oh!

likzn Jun 6, 2022

Choose a reason for hiding this comment

Uh oh!

xhebox Jun 24, 2022 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Choose a reason for hiding this comment

Uh oh!

likzn Jun 24, 2022

Choose a reason for hiding this comment

Uh oh!

xhebox Jun 24, 2022

Choose a reason for hiding this comment

Uh oh!

likzn Jun 24, 2022

Choose a reason for hiding this comment

Uh oh!

likzn commented Jun 10, 2022

Uh oh!

likzn commented Jun 14, 2022

Uh oh!

tangenta commented Jun 28, 2022

Uh oh!

ti-chi-bot commented Jun 28, 2022

Uh oh!

likzn commented Jun 28, 2022

Uh oh!

likzn commented Jun 28, 2022

Uh oh!

xhebox commented Jun 29, 2022

Uh oh!

likzn commented Jun 29, 2022

Uh oh!

tangenta commented Jun 29, 2022

Uh oh!

tangenta commented Jun 29, 2022

Uh oh!

sre-bot commented Jun 29, 2022

TiDB MergeCI notify

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

7 participants

ti-chi-bot commented Jun 2, 2022 •

edited

Loading

sre-bot commented Jun 2, 2022 •

edited

Loading

xhebox commented Jun 6, 2022 •

edited

Loading

likzn commented Jun 6, 2022 •

edited

Loading

xhebox Jun 24, 2022 •

edited

Loading