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.
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.