MySQL Bugs: #48381: "show tables not like" isnt valid

Bug #48381	"show tables not like" isnt valid
Submitted:	28 Oct 2009 15:44	Modified:	28 Oct 2009 18:00
Reporter:	Jan Klopper	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S4 (Feature request)
Version:	5.0.51a-24+lenny1	OS:	Linux (Debian 5.0)
Assigned to:		CPU Architecture:	Any

Description:
A query to get all tables named like works flawlessly,
Doing the reverse, showing all tables 'not like' a condition gives:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'not like 'condition'' at line 1

Id love to be able to query the database for tables using a more powerful conditions system.

How to repeat:
show tables not like 'x'

Suggested fix:
use the normal condition parser for table names like you would for normal text/varchar fields.

Syntax you want is NOT presented in the manual, http://dev.mysql.com/doc/refman/5.0/en/show-tables.html. So, this is definitely NOT a bug.

It sounds like a reasonable feature request, but why add anything else to SHOW if one can just use SELECT ... FROM INFORMATION_SCHEMA.TABLES WHERE ..., like this:

mysql> select table_name from information_schema.tables  
    -> where table_name NOT LIKE 't%';

Correct, this aint a bug.

But why support like trough a separate codepath if apparently there's an other more flexible way to select tables anyway.

Supporting the whole condition system shouldn't be to hard if the underlying code is the same.

Some historical notes: SHOW TABLES appeared first, long time ago, with LIKE only. Then, in 5.0, INFORMATION_SCHEMA was added, with full WHERE support for its tables. SHOW was slightly extended and is still supported for backward compatibility, but there is no much reason to implement new clauses/conditions for SHOW when we support full-featured SELECT and INFORMATION_SCHEMA already. 

To summarize, I do NOT expect any further extensions to SHOW statements in 5.x. I do expect new tables in INFORMATION_SCHEMA and its further enhancements.