Enrich instructions for string functions INSTR() and LCASE() #16144

jyf111 · 2024-01-13T10:31:40Z

First-time contributors' checklist

I've signed Contributor License Agreement that's required for repo owners to accept my contribution.

What is changed, added or deleted? (Required)

Which TiDB version(s) do your changes apply to? (Required)

Tips for choosing the affected version(s):

By default, CHOOSE MASTER ONLY so your changes will be applied to the next TiDB major or minor releases. If your PR involves a product feature behavior change or a compatibility change, CHOOSE THE AFFECTED RELEASE BRANCH(ES) AND MASTER.

For details, see tips for choosing the affected versions.

What is the related PR or file link(s)?

This PR is translated from:
Other reference link(s):

Do your changes match any of the following descriptions?

Delete files
Change aliases
Need modification after applied to another branch
Might cause conflicts after applied to another branch

ti-chi-bot · 2024-01-13T10:31:50Z

Welcome @jyf111!

It looks like this is your first PR to pingcap/docs 🎉.

I'm the bot to help you request reviewers, add labels and more, See available commands.

We want to make sure your contribution gets all the attention it needs!

Thank you, and welcome to pingcap/docs. 😃

functions-and-operators/string-functions.md

fix url Co-authored-by: Daniël van Eeden <[email protected]>

Co-authored-by: Daniël van Eeden <[email protected]>

fix url

add a case insensitive example for INSTR()

polish sentence

dveeden · 2024-01-17T08:29:38Z

@jyf111 there are a few issues reported by the verify ci job:

[2024-01-16T14:15:17.567Z] functions-and-operators/string-functions.md: this file has manual line breaks in the following lines:
[2024-01-16T14:15:17.567Z] 
[2024-01-16T14:15:17.567Z] MANUAL LINE BREAKS: L167
[2024-01-16T14:15:17.567Z] MANUAL LINE BREAKS: L168
[2024-01-16T14:15:17.567Z] MANUAL LINE BREAKS: L169
[2024-01-16T14:15:17.567Z] MANUAL LINE BREAKS: L177
[2024-01-16T14:15:17.567Z] MANUAL LINE BREAKS: L178
[2024-01-16T14:15:17.567Z] MANUAL LINE BREAKS: L235
[2024-01-16T14:15:17.567Z] MANUAL LINE BREAKS: L256
[2024-01-16T14:15:17.567Z] 
[2024-01-16T14:15:17.567Z] The above issues will cause website build failure. Please fix them.

functions-and-operators/string-functions.md

fix manual line breaks

functions-and-operators/string-functions.md

ti-chi-bot · 2024-02-01T07:22:32Z

[LGTM Timeline notifier]

Timeline:

2024-01-16 14:29:37.513592644 +0000 UTC m=+279819.077890347: ☑️ agreed by dveeden.
2024-02-01 07:22:31.189770372 +0000 UTC m=+1636592.754068077: ☑️ agreed by Oreoxmt.

Oreoxmt · 2024-02-01T07:29:37Z

/test pull-verify

xzhangxian1008

Other LGTM

xzhangxian1008 · 2024-02-01T09:09:54Z

functions-and-operators/string-functions.md

+```
+
+```sql
+SELECT INSTR("pingcap.com/tidb", "TiDB");


Which tidb version do you use? I tried it on my tidb cluster with tidb commit cac449b3370c5, and get different result.

mysql> SELECT INSTR("pingcap.com/tidb", "TiDB"); +-----------------------------------+ | INSTR("pingcap.com/tidb", "TiDB") | +-----------------------------------+ | 13 | +-----------------------------------+

and we can get 0 with this sql

mysql> SELECT INSTR(_utf8mb4 "pingcap.com/tidb", _utf8mb4 "TiDB"); +-----------------------------------------------------+ | INSTR(_utf8mb4 "pingcap.com/tidb", _utf8mb4 "TiDB") | +-----------------------------------------------------+ | 0 | +-----------------------------------------------------+

mysql> SELECT TIDB_VERSION()\G *************************** 1. row *************************** TIDB_VERSION(): Release Version: v7.1.1-serverless Edition: Community Git Commit Hash: 14db49a2968b68ac4cf3e4a59db87ba30a97996d Git Branch: release-7.1-serverless UTC Build Time: 2024-01-29 10:05:53 GoVersion: go1.21.6 Race Enabled: false TiKV Min Version: 6.1.0 Check Table Before Drop: false Store: tikv 1 row in set (0.24 sec) mysql> SHOW VARIABLES LIKE 'collation\_%'; +----------------------+-------------+ | Variable_name | Value | +----------------------+-------------+ | collation_connection | utf8mb4_bin | | collation_database | utf8mb4_bin | | collation_server | utf8mb4_bin | +----------------------+-------------+ 3 rows in set (0.25 sec) mysql> SELECT INSTR("pingcap.com/tidb", "TiDB"); +-----------------------------------+ | INSTR("pingcap.com/tidb", "TiDB") | +-----------------------------------+ | 0 | +-----------------------------------+ 1 row in set (0.24 sec)

@jyf111, thanks for sharing the version info. Currently, the "master" branch of TiDB docs is for TiDB v7.6.0. Would you please try installing TiDB v7.6 according to this quick start guide? Thanks.

mysql> SELECT TIDB_VERSION()\G *************************** 1. row *************************** TIDB_VERSION(): Release Version: v7.6.0 Edition: Community Git Commit Hash: 52794d985ba6325d75a714d4eaa0838d59425eb6 Git Branch: heads/refs/tags/v7.6.0 UTC Build Time: 2024-01-22 14:20:42 GoVersion: go1.21.5 Race Enabled: false Check Table Before Drop: false Store: tikv 1 row in set (0.01 sec) mysql> SHOW VARIABLES LIKE 'collation\_%'; +----------------------+--------------------+ | Variable_name | Value | +----------------------+--------------------+ | collation_connection | utf8mb4_0900_ai_ci | | collation_database | utf8mb4_bin | | collation_server | utf8mb4_bin | +----------------------+--------------------+ 3 rows in set (0.01 sec) mysql> SELECT INSTR("pingcap.com/tidb", "TiDB"); +-----------------------------------+ | INSTR("pingcap.com/tidb", "TiDB") | +-----------------------------------+ | 13 | +-----------------------------------+ 1 row in set (0.00 sec)

Um.. But I don't know why collation_connection becomes utf8mb4_0900_ai_ci.

The latest doc still shows that the default value of collation_connection is utf8mb4_bin.

Hi @jyf111, thanks for checking the version.

According to @YangKeao (a TiDB developer), utf8mb4_0900_ai_ci was introduced in v7.4 to be compatible with MySQL 8.0 (pingcap/tidb#45650).

Starting from v7.4.0, the default collation of utf8mb4 on TiDB depends on the version of MySQL client, and @YangKeao will create a PR to clarify this issue in the doc.

@jyf111 FYI, here is the docPR created by @YangKeao: https://github.com/pingcap/docs/pull/16423/files

Great. I have updated the sentences related to case sensitivity and collations. PTAL

ti-chi-bot · 2024-02-01T09:17:16Z

@xzhangxian1008: adding LGTM is restricted to approvers and reviewers in OWNERS files.

In response to this:

Other LGTM

Instructions for interacting with me using PR comments are available here. If you have questions or suggestions related to my behavior, please file an issue against the kubernetes/test-infra repository.

qiancai · 2024-02-06T06:00:24Z

/retest

qiancai · 2024-02-06T06:23:13Z

/retest

qiancai · 2024-02-06T06:35:57Z

/retest

qiancai · 2024-02-06T06:40:18Z

/approve

ti-chi-bot · 2024-02-06T06:40:24Z

[APPROVALNOTIFIER] This PR is APPROVED

This pull-request has been approved by: qiancai

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

The pull request process is described here

Needs approval from an approver in each of these files:

~~OWNERS~~ [qiancai]

Approvers can indicate their approval by writing /approve in a comment
Approvers can cancel approval by writing /approve cancel in a comment

Enrich instructions for string functions INSTR() and LCASE()

59dce3d

ti-chi-bot bot added contribution This PR is from a community contributor. first-time-contributor Indicates that the PR was contributed by an external member and is a first-time contributor. labels Jan 13, 2024

ti-chi-bot bot added missing-translation-status This PR does not have translation status info. size/M Denotes a PR that changes 30-99 lines, ignoring generated files. labels Jan 13, 2024

fix a typo

b5dcede

dveeden reviewed Jan 15, 2024

View reviewed changes

functions-and-operators/string-functions.md Outdated Show resolved Hide resolved

functions-and-operators/string-functions.md Outdated Show resolved Hide resolved

jyf111 and others added 4 commits January 16, 2024 21:37

Update functions-and-operators/string-functions.md

94f0bb9

fix url Co-authored-by: Daniël van Eeden <[email protected]>

Update functions-and-operators/string-functions.md

443dfa8

Co-authored-by: Daniël van Eeden <[email protected]>

Update string-functions.md

8a168e0

fix url

Update string-functions.md

aacdc4c

add a case insensitive example for INSTR()

ti-chi-bot 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 Jan 16, 2024

Update string-functions.md

8c012be

polish sentence

jyf111 requested a review from dveeden January 16, 2024 14:12

dveeden approved these changes Jan 16, 2024

View reviewed changes

ti-chi-bot bot added the needs-1-more-lgtm Indicates a PR needs 1 more LGTM. label Jan 16, 2024

dveeden reviewed Jan 17, 2024

View reviewed changes

functions-and-operators/string-functions.md Outdated Show resolved Hide resolved

dveeden requested review from ran-huang and qiancai January 17, 2024 08:36

jyf111 added 2 commits January 17, 2024 21:37

Update string-functions.md

561c8d8

fix manual line breaks

Update string-functions.md

b96f05a

fix manual line breaks

qiancai added the 2024-tidb-docs-dash This issue or PR is included in the 2024 TiDB Docs Dash event. label Jan 31, 2024

qiancai self-assigned this Jan 31, 2024

qiancai added the translation/doing This PR's assignee is translating this PR. label Feb 1, 2024

ti-chi-bot bot removed the missing-translation-status This PR does not have translation status info. label Feb 1, 2024

qiancai reviewed Feb 1, 2024

View reviewed changes

functions-and-operators/string-functions.md Outdated Show resolved Hide resolved

qiancai reviewed Feb 1, 2024

View reviewed changes

functions-and-operators/string-functions.md Outdated Show resolved Hide resolved

qiancai reviewed Feb 1, 2024

View reviewed changes

wording updates

dc10530

Oreoxmt self-requested a review February 1, 2024 06:31

qiancai removed the request for review from ran-huang February 1, 2024 06:59

Oreoxmt approved these changes Feb 1, 2024

View reviewed changes

ti-chi-bot bot added lgtm and removed needs-1-more-lgtm Indicates a PR needs 1 more LGTM. labels Feb 1, 2024

Merge remote-tracking branch 'upstream/master' into pr/16144

72fe63c

xzhangxian1008 approved these changes Feb 1, 2024

View reviewed changes

Update string-functions.md to clarify the impact of collations

8d9c4c1

qiancai approved these changes Feb 6, 2024

View reviewed changes

Merge remote-tracking branch 'upstream/master' into pr/16144

a0c0262

Update string-functions.md

623249c

ti-chi-bot bot added the approved label Feb 6, 2024

ti-chi-bot bot merged commit e49428c into pingcap:master Feb 6, 2024
8 of 9 checks passed

qiancai mentioned this pull request Feb 6, 2024

Enrich instructions for string functions INSTR() and LCASE() pingcap/docs-cn#16539

Merged

18 tasks

qiancai added translation/done This PR has been translated from English into Chinese and updated to pingcap/docs-cn in a PR. and removed translation/doing This PR's assignee is translating this PR. labels Feb 6, 2024

dveeden mentioned this pull request Apr 2, 2024

Enrich instructions for string functions LOWER() and LPAD() #16937

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Enrich instructions for string functions INSTR() and LCASE() #16144

Enrich instructions for string functions INSTR() and LCASE() #16144

jyf111 commented Jan 13, 2024

ti-chi-bot bot commented Jan 13, 2024

dveeden commented Jan 17, 2024

ti-chi-bot bot commented Feb 1, 2024

Oreoxmt commented Feb 1, 2024

xzhangxian1008 left a comment

xzhangxian1008 Feb 1, 2024 •

edited

Loading

jyf111 Feb 1, 2024

qiancai Feb 1, 2024 •

edited

Loading

jyf111 Feb 2, 2024

qiancai Feb 4, 2024

qiancai Feb 4, 2024

jyf111 Feb 5, 2024

ti-chi-bot bot commented Feb 1, 2024

qiancai commented Feb 6, 2024

qiancai commented Feb 6, 2024

qiancai commented Feb 6, 2024

qiancai commented Feb 6, 2024

ti-chi-bot bot commented Feb 6, 2024

Enrich instructions for string functions INSTR() and LCASE() #16144

Enrich instructions for string functions INSTR() and LCASE() #16144

Conversation

jyf111 commented Jan 13, 2024

First-time contributors' checklist

What is changed, added or deleted? (Required)

Which TiDB version(s) do your changes apply to? (Required)

What is the related PR or file link(s)?

Do your changes match any of the following descriptions?

ti-chi-bot bot commented Jan 13, 2024

dveeden commented Jan 17, 2024

ti-chi-bot bot commented Feb 1, 2024

[LGTM Timeline notifier]

Oreoxmt commented Feb 1, 2024

xzhangxian1008 left a comment

Choose a reason for hiding this comment

xzhangxian1008 Feb 1, 2024 • edited Loading

Choose a reason for hiding this comment

jyf111 Feb 1, 2024

Choose a reason for hiding this comment

qiancai Feb 1, 2024 • edited Loading

Choose a reason for hiding this comment

jyf111 Feb 2, 2024

Choose a reason for hiding this comment

qiancai Feb 4, 2024

Choose a reason for hiding this comment

qiancai Feb 4, 2024

Choose a reason for hiding this comment

jyf111 Feb 5, 2024

Choose a reason for hiding this comment

ti-chi-bot bot commented Feb 1, 2024

qiancai commented Feb 6, 2024

qiancai commented Feb 6, 2024

qiancai commented Feb 6, 2024

qiancai commented Feb 6, 2024

ti-chi-bot bot commented Feb 6, 2024

xzhangxian1008 Feb 1, 2024 •

edited

Loading

qiancai Feb 1, 2024 •

edited

Loading