Bug #83487 INFORMATION_SCHEMA
Submitted: 23 Oct 2016 8:40 Modified: 3 Nov 2016 13:23
Reporter: Tomislav Hampl Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:5.7.16, 5.6.34 OS:Microsoft Windows
Assigned to:
Tags: information_schema, schemata

[23 Oct 2016 8:40] Tomislav Hampl
Description:
Get record from table where filed = database name

How to repeat:
select user_db from tbl_test
db001
db002
db003

select SCHEMA_NAME from INFORMATION_SCHEMA.SCHEMATA
test
db001
db002
.....

The result should be : db001, db002

#1
select user_db from tbl_test 
where user_db in (select SCHEMA_NAME from INFORMATION_SCHEMA.SCHEMATA)

#2
select user_db from tbl_test 
where user_db in (select convert(SCHEMA_NAME,char(5)) from INFORMATION_SCHEMA.SCHEMATA)

#3
select user_db from tbl_test 
where convert(user_db,char(5)) in (select convert(SCHEMA_NAME,char(5)) from INFORMATION_SCHEMA.SCHEMATA)

#4
select user_db from tbl_test 
where convert(user_db,char(5)CHARACTER SET latin1) in (select convert(SCHEMA_NAME,char(5) CHARACTER SET latin1) from INFORMATION_SCHEMA.SCHEMATA)
[3 Nov 2016 8:30] Umesh Shastry
Hello Tomislav Hampl,

Thank you for the report.
I'm sorry but why do you think this is a bug? If you want the SCHEMA_NAME  named to be in order then the select against INFORMATION_SCHEMA.SCHEMATA with an ORDER BY clause is required.

-- Without ORDER BY

mysql> select SCHEMA_NAME from INFORMATION_SCHEMA.SCHEMATA;
+--------------------+
| SCHEMA_NAME        |
+--------------------+
| information_schema |
| db001              |
| db002              |
| db003              |
| mysql              |
| performance_schema |
| test               |
+--------------------+
7 rows in set (0.00 sec)

-- ORDER BY

mysql> select SCHEMA_NAME from INFORMATION_SCHEMA.SCHEMATA ORDER BY SCHEMA_NAME;
+--------------------+
| SCHEMA_NAME        |
+--------------------+
| db001              |
| db002              |
| db003              |
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
7 rows in set (0.00 sec)

Thanks,
Umesh
[3 Nov 2016 9:37] Tomislav Hampl
http://prntscr.com/d2hx9l
[3 Nov 2016 13:23] Umesh Shastry
Thank you for the feedback!
Observed this with 5.6.34/5.7.16.
[3 Nov 2016 13:23] Umesh Shastry
test results

Attachment: 83487.results (application/octet-stream, text), 12.26 KiB.