Documentation Home
MySQL Internals Manual
Download this Manual
EPUB - 0.8Mb


25.8.2 Too Greedy Test

Let's assume we have to check that every new created table causes a row in information_schema.tables.

 --replace_column 15 <CREATE_TIME> 16 <UPDATE_TIME> 17 <CHECK_TIME>
SELECT * FROM information_schema.tables; 

The SELECT above makes the test extreme "greedy" for changes in behaviour which is maybe good for general bug hunting but not for smart and frequent automatic tests. It is to be expected that such a test requires frequent maintenance like adjustment of expected results.

The problems with the select above: We will (sometimes only maybe) get a result set difference whenever

  • we get a new system table

Completeness of result sets without WHERE are outside of the focus.

Refined statement:

 --replace_column 15 <CREATE_TIME> 16 <UPDATE_TIME> 17 <CHECK_TIME>
SELECT * FROM information_schema.tables
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1'; 
  • the number of columns within information_schema.tables changes

  • the data type of a column within ... changes

  • the content within one of the columns within ... changes

Lets assume something intentional like VERSION is now 11 instead of 10.

This means we should try to avoid "SELECT *".

Final statement:

 Variant 1:
SELECT TABLE_SCHEMA,TABLE_NAME FROM information_schema.tables
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1'; 

Are we really consequent? No, but we could

 Variant 2:
SELECT COUNT(*) FROM information_schema.tables
  WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1'; 

It's on you which variant you choose. I decide depending on the situation.


User Comments
Sign Up Login You must be logged in to post a comment.