Hi all,
We are trying to migrate our db from pgsql to mysql. Everything are ok expect queries contains subquery to a many-to-many explicit join table. Mysql always complains "Operand should contains 1 column(s)". Hereunder are simple test code
Code:
import javax.persistence.*;
import org.hibernate.annotations.GenericGenerator;
@Entity
@Table( name = "student")
public class Student {
@Id
@Column( name = "student_id", length = 32)
@GeneratedValue(generator="hibernate-uuid.hex")
@GenericGenerator( name="hibernate-uuid.hex", strategy="org.hibernate.id.UUIDHexGenerator")
private String id;
@Column( name = "name")
private String name;
/* getter / setter */
...
}
@Entity
@Table( name = "course")
public class Course {
@Id
@Column( name = "course_id", length = 32)
@GeneratedValue(generator="hibernate-uuid.hex")
@GenericGenerator( name="hibernate-uuid.hex", strategy="org.hibernate.id.UUIDHexGenerator")
private String id;
@Column( name = "name")
private String name;
/* getter / setter */
...
}
@Entity
@Table( name = "asso_student_course")
public class StudentCourse implements java.io.Serializable {
@Embeddable
public static class Id implements java.io.Serializable {
@Column( name = "student_id", length = 32)
private String studentId;
@Column( name = "course_id", length = 32)
private String courseId;
public Id() {};
public Id( String studentId, String courseId) {
this.studentId = studentId;
this.courseId = courseId;
}
public boolean equals( Object o) {
if( o != null && o instanceof Id) {
Id that = (Id) o;
return this.studentId.equals( that.studentId) && this.courseId.equals( that.courseId);
} else {
return false;
}
}
public int hashCode() {
return this.studentId.hashCode() + this.courseId.hashCode();
}
public String getStudentId() {
return studentId;
}
public void setStudentId(String studentId) {
this.studentId = studentId;
}
public String getCourseId() {
return courseId;
}
public void setCourseId(String courseId) {
this.courseId = courseId;
}
}
@EmbeddedId
private Id id = new Id();
/**
* student relation
*/
@ManyToOne
@JoinColumn( name = "student_id", insertable = false, updatable = false)
private Student student;
/**
* baseItem relation
*/
@ManyToOne
@JoinColumn( name = "course_id", insertable = false, updatable = false)
private Course course;
/* getter / setter */
...
}
I tried to execute underlying query
Code:
select stu from Student stu where exists ( select sc from StudentCourse sc where sc.student = stu)
The generated SQL is
Code:
select
student0_.student_id as student1_34_,
student0_.name as name34_
from
student student0_
where
exists (
select
(studentcou1_.course_id,
studentcou1_.student_id)
from
asso_student_course studentcou1_
where
studentcou1_.student_id=student0_.student_id
)
The exception I got when the backend database is mysql
Code:
java.sql.SQLException: Operand should contain 1 column(s)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3283)
If the backend database is postgresql, the generated SQL is exactly the same and works perfectly.
I tried to remove the parenthesis in the subquery of generated SQL and it works even on MySQL.
Code:
select
student0_.student_id as student1_34_,
student0_.name as name34_
from
student student0_
where
exists (
select
studentcou1_.course_id,
studentcou1_.student_id
from
asso_student_course studentcou1_
where
studentcou1_.student_id=student0_.student_id
)
The execution environments are:
MySQL 5.0.67
j-connector 5.0.8
hibernate-core 3.3.1.GA
hibernate-entitymanager 3.4.0.GA
hibernate-annotations-3.4.0.GA
I am wondering if I missed something or this is a bug in Dialect? Any comment will be very appreciated.
//Austin
Login
FAQ
Search
