Bug #67765 Ordering only works with the first column of the primary key
Submitted: 30 Nov 2012 14:36 Modified: 9 Jan 2015 10:16
Reporter: Jon Strayer Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Cluster: Cluster/J Severity:S1 (Critical)
Version:7.2.8 & 7.2.9, 7.2.14 OS:Any
Assigned to: CPU Architecture:Any
Tags: ClusterJ, ordering

[30 Nov 2012 14:36] Jon Strayer
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.
[22 Jul 2013 20:19] Sveta Smirnova
Thank you for the report.

Verified as described.
[7 Nov 2013 0:54] Craig Russell
Posted by developer:
 
Given the schema and mapping, there are four valid orderings based on the two indices:
updatedAt
sourceUserId
sourceUserId, destUserId
sourceUserId, destUserId, queryHistoryId

The behavior of clusterj in the last three cases is identical.
[3 May 2014 4:17] Robert Gass
I think they key is to use a UNIQUE INDEX.  For example,

  UNIQUE INDEX `IX_updated_at` (`updated_at`)