Description:
Given this table:
CREATE TABLE `conversation_summary` (
`source_user_id` bigint(11) NOT NULL,
`destination_user_id` bigint(11) NOT NULL,
`last_message_user_id` bigint(11) NOT NULL,
`text_summary` varchar(255) NOT NULL DEFAULT '',
`query_history_id` bigint(20) NOT NULL DEFAULT '0',
`answerer_id` bigint(11) NOT NULL,
`viewed` bit(1) NOT NULL,
`updated_at` bigint(20) NOT NULL,
PRIMARY KEY (`source_user_id`,`destination_user_id`,`query_history_id`),
KEY `IX_updated_at` (`updated_at`)
) ENGINE=ndbcluster DEFAULT CHARSET=utf8$$
And this DTO
@PersistenceCapable(table = "conversation_summary")
public interface ConversationSummaryDTO {
@PrimaryKey
@Column(name = "source_user_id")
long getSourceUserId();
void setSourceUserId(long id);
@PrimaryKey
@Column(name = "destination_user_id")
long getDestUserId();
void setDestUserId(long id);
@Column(name = "last_message_user_id")
long getLastMessageById();
void setLastMessageById(long id);
@Column(name = "text_summary")
String getText();
void setText(String text);
@PrimaryKey
@Column(name = "query_history_id")
long getQueryHistoryId();
void setQueryHistoryId(long id);
@Column(name = "answerer_id")
long getAnswererId();
void setAnswererId(long id);
@Column(name = "viewed")
boolean getViewed();
void setViewed(boolean viewed);
@Column(name = "updated_at")
@Index(name="IX_updated_at")
long getUpdatedAt();
void setUpdatedAt(long updated);
}
This code fails:
QueryBuilder builder = session.getQueryBuilder();
QueryDomainType<ConversationSummaryDTO> domain = builder.createQueryDefinition(ConversationSummaryDTO.class);
Predicate compare = null;
PredicateOperand column = domain.get("sourceUserId");
PredicateOperand param = domain.param("filter_sourceUserId");
compare = column.equal(param);
PredicateOperand column2 = domain.get("updatedAt");
PredicateOperand param2 = domain.param("filter_updatedAt1");
compare = compare.and(column2.greaterEqual(param2));
PredicateOperand column3 = domain.get("updatedAt");
PredicateOperand param3 = domain.param("filter_updatedAt2");
compare = compare.and(column3.lessEqual(param3));
domain.where(compare);
Query<ConversationSummaryDTO> query = session.createQuery(domain);
query.setParameter("filter_sourceUserId", userId);
query.setParameter("filter_updatedAt1", startDate);
query.setParameter("filter_updatedAt2", endDate);
query.setLimits(0, count);
query.setOrdering(Ordering.ASCENDING, "updatedAt");
return query.getResultList();
With this error:
com.mysql.clusterj.ClusterJUserException: There is no index containing the ordering fields.
at com.mysql.clusterj.core.query.QueryDomainTypeImpl.getResultData(QueryDomainTypeImpl.java:281)
at com.mysql.clusterj.core.query.QueryDomainTypeImpl.getResultList(QueryDomainTypeImpl.java:181)
at com.mysql.clusterj.core.query.QueryImpl.getResultList(QueryImpl.java:144)
at com.chacha.chat.dao.impl.ClusterJConversationSummaryDao$1.execute(ClusterJConversationSummaryDao.java:146)
I should be able to sort on any of the four columns in the two indexes, but it only works with 'sourceUserId".
How to repeat:
As far as I can tell try to sort on any index field other than the first one in the primary key index.