Wrong query result on information_schema query

Help
1

Create this schema:

create table t (i int primary key, j int);
create index i on t (j);

Now, when trying to discover the index i from the information_schema, one might think the following query could be useful, yet it does not return any data:

select *
from information_schema.STATISTICS s
where not exists (
  select 1
  from information_schema.TABLE_CONSTRAINTS c
  where s.INDEX_SCHEMA = c.CONSTRAINT_SCHEMA
  and s.INDEX_NAME = c.CONSTRAINT_NAME
  and s.TABLE_NAME = c.TABLE_NAME
);

image
image.png715×315 11.8 KB

Add an additional predicate, and it now returns the index, as expected:

select *
from information_schema.STATISTICS s
where not exists (
  select 1
  from information_schema.TABLE_CONSTRAINTS c
  where s.INDEX_SCHEMA = c.CONSTRAINT_SCHEMA
  and s.INDEX_NAME = c.CONSTRAINT_NAME
  and s.TABLE_NAME = c.TABLE_NAME
)
and s.index_name = 'i';

image
image.png895×306 13.2 KB

select version(); yields 5.5.58. I’m using the latest version from docker: memsql/cluster-in-a-box

2

Using LEFT JOIN style anti joins seems to work here:

select *
from information_schema.STATISTICS s
left join information_schema.TABLE_CONSTRAINTS c
on s.INDEX_SCHEMA = c.CONSTRAINT_SCHEMA
and s.TABLE_NAME = c.TABLE_NAME
and s.INDEX_NAME = c.CONSTRAINT_NAME
where c.constraint_name is null;

image
image.png1017×297 12.4 KB

3

I tried running it on the 7.0 release candidate and the issue did not reproduce. The first query did produce a row.

By the way, to check the cluster version, use select @@memsql_version;

The version() function is for MySQL compatibility.

4

I see, thanks for the pointer about @@memsql_version. That returns 6.8.14.

Well, great to hear that it appears to already have been fixed in newer versions!