Filtering `information_schema.schemata` by multiple `schema_name` filters behaves unexpectedly

[knummert]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

• SELECT * FROM information_schema.schemata WHERE schema_name LIKE 'nothing_%' returns 0 rows
• SELECT * FROM information_schema.schemata WHERE schema_name LIKE 'nothing_%' AND schema_name LIKE '%i%' return 27 rows

Same issue with these queries:

WITH nothing_schemas AS (
    SELECT *
    FROM information_schema.schemata
    WHERE schema_name LIKE 'nothing_%'
) SELECT * FROM nothing_schemas WHERE schema_name LIKE '%i%'

SELECT *
FROM information_schema.schemata
WHERE schema_name REGEXP '^nothing_'
  AND schema_name LIKE '%i%'

SELECT *
FROM information_schema.schemata
WHERE schema_name REGEXP '^nothing_'
  AND schema_name REGEXP '%i%'

Also

SELECT *
FROM information_schema.schemata
WHERE schema_name LIKE '%i%'

returns 27 rows

SELECT *
FROM information_schema.schemata
WHERE schema_name LIKE '%i%'
  AND schema_name LIKE '%i%'

returns 58 rows

SELECT *
FROM information_schema.schemata
WHERE schema_name LIKE '%i%'
  AND schema_name LIKE '%i%'
  AND schema_name LIKE '%i%'

returns 87 rows

While

SELECT schema_name
FROM information_schema.schemata
WHERE schema_name LIKE '%i%'

returns 27 distinct rows then

SELECT schema_name
FROM information_schema.schemata
WHERE schema_name LIKE '%i%'
  AND schema_name LIKE '%i%'
  AND schema_name LIKE '%i%'

only returns INFORMATION_SCHEMA 87 times.

2. What did you expect to see? (Required)

Narrowing query predicates shouldn't increase the number of rows returned.

3. What did you see instead (Required)

Only rows that match all predicates.

4. What is your TiDB version? (Required)

Release Version: v8.5.1
Edition: Enterprise
Git Commit Hash: fea86c8e35ad4a86a5e1160701f99493c2ee547c
Git Branch: HEAD
UTC Build Time: 2025-01-16 07:39:38
GoVersion: go1.23.4
Race Enabled: false
Check Table Before Drop: false
Store: tikv
Enterprise Extension Commit Hash: 288d29c9b481cfeb54d95ca08aaff0b807fc2ce3

[JQWong7]

/assign