MySQL Bugs: #96735: Select doesn't return results from indexed table running on ndbcluster engine

Bug #96735	Select doesn't return results from indexed table running on ndbcluster engine
Submitted:	4 Sep 2019 6:50	Modified:	6 Sep 2019 12:53
Reporter:	Piotr Maślany	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S2 (Serious)
Version:	8.0.17	OS:	CentOS
Assigned to:		CPU Architecture:	x86
Tags:	regression

Description:
Below is a scenario to reproduce an error on table managed by ndbcluster engine. Same working fine on InnoDB. 
It's a very basic scenario where top row (max id) matching criteria should be returned. Table contains text column, but it doesn't act in where clause.

How to repeat:
drop table if exists ndbcluster_test;

create table ndbcluster_test(
                                id bigint not null auto_increment,
                                cpr varchar(10) not null,
                                payload text,
                                payload_hash varchar(128) default NULL,
                                primary key (id)
) ENGINE=ndbcluster;

insert into ndbcluster_test (cpr, payload, payload_hash) VALUES ('1234567890', 'ala', 'sfsdf');
insert into ndbcluster_test (cpr, payload, payload_hash) VALUES ('1234567890', 'adff', 'sfsdf');
insert into ndbcluster_test (cpr, payload, payload_hash) VALUES ('1234567890', null, null);
insert into ndbcluster_test (cpr, payload, payload_hash) VALUES ('9876543210', 'alfdsa', 'sfsdf');
insert into ndbcluster_test (cpr, payload, payload_hash) VALUES ('9876543210', 'sfala', 'sfsdf');
insert into ndbcluster_test (cpr, payload, payload_hash) VALUES ('9876543210', null, null);

-- Query returns a results (1 row)
select * from ndbcluster_test
where cpr='1234567890'
  and payload_hash is not null
order by id desc limit 1;

-- Now create an index on cpr column:
create index ndbcluster_test_idx on ndbcluster_test(cpr);

-- Query doesn't return any results (expected 1)
-- Base problem is HERE:
select * from ndbcluster_test
where cpr='1234567890'
  and payload_hash is not null
order by id desc limit 1;

-- Same query without fetching payload (text) in a result returns 1 row
select id, cpr, payload_hash from ndbcluster_test
where cpr='1234567890'
  and payload_hash is not null
order by id desc limit 1;

-- Same query without order by returns properly results (2 rows)
select * from ndbcluster_test
where cpr='1234567890'
  and payload_hash is not null;

-- Same query with a changed where clause forced to not use index (as cpr is varchar) returns result properly
select * from ndbcluster_test
where cpr=1234567890
  and payload_hash is not null
order by id desc limit 1;

-- Original query returns 1 row properly if we drop an index on cpr
-- To check drop an index temporarily and perform first query
-- drop index ndbcluster_test_idx on ndbcluster_test;

-- Another working workaround is to have a composite index on cpr and id columns:
-- create index ndbcluster_test_idx1 on ndbcluster_test(cpr,id);

-- However if we change an engine for a table to InnoDB initial query works fine regardless we have an index on cpr or not.
ALTER TABLE ndbcluster_test ENGINE=InnoDB;

Hello Piotr Maślany,

Thank you for the report and test case.
Verified as described.

regards,
Umesh

There is the same issue if we specify a payload column to be varchar with proper length. Seems to me like there is some limit on a database row size, if it exceeds about 1024 then query fails. See working example:

drop table ndbcluster_test;

create table ndbcluster_test(
                                id bigint not null auto_increment,
                                cpr varchar(500) not null,
                                payload varchar(500),
                                payload_hash varchar(18) default NULL,
                                primary key (id)
) ENGINE=ndbcluster;

insert into ndbcluster_test (cpr, payload, payload_hash) VALUES ('1234567890', 'ala', 'sfsdf');
insert into ndbcluster_test (cpr, payload, payload_hash) VALUES ('1234567890', 'adff', 'sfsdf');
insert into ndbcluster_test (cpr, payload, payload_hash) VALUES ('1234567890', null, null);
insert into ndbcluster_test (cpr, payload, payload_hash) VALUES ('9876543210', 'alfdsa', 'sfsdf');
insert into ndbcluster_test (cpr, payload, payload_hash) VALUES ('9876543210', 'sfala', 'sfsdf');
insert into ndbcluster_test (cpr, payload, payload_hash) VALUES ('9876543210', null, null);

-- Now create an index on cpr column:
create index ndbcluster_test_idx on ndbcluster_test(cpr);

-- Query returns a results (1 row)
select * from ndbcluster_test
where cpr='1234567890'
  and payload_hash is not null
order by id desc limit 1;

And now if we change one of varchar columns and append a size by 1 the query doesn't return results:

create table ndbcluster_test(
                                id bigint not null auto_increment,
                                cpr varchar(501) not null,
                                payload varchar(500),
                                payload_hash varchar(18) default NULL,
                                primary key (id)
) ENGINE=ndbcluster;

Same issue exist on on Redhat Enterprise Linux Server 7.7. (Maipo)

Issue resolved in MySQL Cluster 8.0.18