[hibernate-issues] [Hibernate-JIRA] Created: (ANN-850) Projection of composite key causes invalid SQL

[Dmitry Katsubo (JIRA)]

Projection of composite key causes invalid SQL
----------------------------------------------

                 Key: ANN-850
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/ANN-850
             Project: Hibernate Annotations
          Issue Type: Bug
    Affects Versions: 3.3.1.GA
         Environment: Hibernate Annotations 3.4.0.GA
Hibernate Commons Annotations 3.1.0.GA
Hibernate 3.3.1.GA

            Reporter: Dmitry Katsubo
            Priority: Minor


When using {{Projections.id()}} for the entity, which has a composite ID, the generated SQL is not valid. The expression {{select this_.conceptid as y0_this_.termid as y1_}} should be {{select this_.conceptid as y0_, this_.termid as y1_}} (comma is missed)

07 14:40:37 DEBUG [ConnectionManager] opening JDBC connection
07 14:40:37 DEBUG [SQL] 
    select
        this_.conceptid as y0_this_.termid as y1_,
        this_.text as y1_ 
    from
        term this_ limit ?
07 14:40:37 DEBUG [AbstractBatcher] about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
07 14:40:37 DEBUG [JDBCExceptionReporter] could not execute query [select this_.conceptid as y0_this_.termid as y1_, this_.text as y1_ from term this_]
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.termid as y1_, this_.text as y1_ from term this_ limit 1000' at line 1

Code:
{quote}
final Session session = sessionFactory.openSession();

try {
	return session.createCriteria(TermImpl.class).setProjection(
			Projections.projectionList().add(Projections.id()).add(Projections.property("text")))
			.setMaxResults(1000).list();
} finally {
	session.close();
}
{quote}

Mapped entity:
{quote}
@Entity
@Table(name = "term")
public class TermImpl implements Term, Serializable {

	@Embeddable
	public static class TermId implements Serializable {

		private int id;

		private Concept concept;

		@Column(name = "termid")
		public int getId() {
			return id;
		}

		public void setId(int id) {
			this.id = id;
		}

		@ManyToOne(targetEntity = ConceptImpl.class)
		@JoinColumn(name = "conceptid")
		@Fetch(FetchMode.SELECT)
		public Concept getConcept() {
			return concept;
		}

		public void setConcept(Concept concept) {
			this.concept = concept;
		}

		@Override
		public boolean equals(Object obj) {
			if (obj instanceof TermId) {
				TermId termId = (TermId) obj;

				return id == termId.id && concept.equals(termId.concept);
			}

			return false;
		}

		@Override
		public int hashCode() {
			return id + concept.hashCode();
		}

		@Override
		public String toString() {
			final ToStringBuilder builder = new ToStringBuilder(this, ToStringStyle.SHORT_PREFIX_STYLE);

			builder.append("concept", concept);
			builder.append("id", id);

			return builder.toString();
		}

	}

	private TermId id;

	private String text;

	@Id
	public TermId getId() {
		return id;
	}

	public void setId(TermId id) {
		this.id = id;
	}

	@Transient
	@Override
	public Concept getConcept() {
		return id.getConcept();
	}

	@Column(name = "text")
	@Override
	public String getText() {
		return text;
	}

	public void setText(String text) {
		this.text = text;
	}

	@Override
	public boolean equals(Object obj) {
		if (obj instanceof TermImpl) {
			final TermImpl term = (TermImpl) obj;

			return id.equals(term.id);
		}

		return false;
	}

	@Override
	public int hashCode() {
		return id.hashCode();
	}

	@Override
	public String toString() {
		final ToStringBuilder builder = new ToStringBuilder(this, ToStringStyle.SHORT_PREFIX_STYLE);

		builder.append("id", id);
		builder.append("text", text);

		return builder.toString();
	}
}
{quote}

Table structure:
{quote}
CREATE TABLE `term` (
  `conceptid` int(11) NOT NULL,
  `termid` int(10) unsigned NOT NULL,
  `text` varchar(255) default NULL,
  PRIMARY KEY  (`conceptid`,`termid`)
);
{quote}

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira