[hibernate-issues] [Hibernate-JIRA] Created: (HHH-3989) MYSQLDialect should not use row-value constructor syntax for UPDATE statements

[Stephen Henderson (JIRA)]

MYSQLDialect should not use row-value constructor syntax for UPDATE statements
------------------------------------------------------------------------------

                 Key: HHH-3989
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3989
             Project: Hibernate Core
          Issue Type: Bug
          Components: core
    Affects Versions: 3.3.2
            Reporter: Stephen Henderson
            Priority: Minor


The MYSQLDialect (and subclasses) returns true for {{supportsRowValueConstructorSyntax()}}, but this only seems to apply for SELECT statements. For update statements row-value syntax returns an sql syntax error.

i.e. "SELECT * FROM myTable WHERE (a,b,c) = (?,?,?)" works ok, but "UPDATE myTable SET (a,b,c) = (?,?,?)" gives a syntax error.

In hibernate code this seems to mostly affect updating embeddable columns through update hql statements.e.g. "update myEntity set address = :address" where address is an instance of an embeddable type.

For example, if I have a simple embeddable address like:
{code}
@Embeddable
public class Address {
	String street;
	String county;
	String postCode;
	...
{code}

Then if I attempt to update the address column for an entity like this:
{code}
		Address a = new Address();
		a.setCounty("my county");
		a.setPostCode("MY1 PC1");
		a.setStreet("my street");
		
		Query qry = session.createQuery("update Customer c set c.address = :address where c.customerId=1");
		qry.setParameter("address", a);
		qry.executeUpdate();
{code}

I get the following error:
{noformat}
org.hibernate.exception.SQLGrammarException: could not execute update query
	at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)
	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
	at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:110)
	at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:421)
	at org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:283)
	at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1169)
	at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:117)
	at core.sql.mysql.BulkInsertTest.testEntityRowColumn(BulkInsertTest.java:152)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.junit.internal.runners.TestMethod.invoke(TestMethod.java:59)
	at org.junit.internal.runners.MethodRoadie.runTestMethod(MethodRoadie.java:98)
	at org.junit.internal.runners.MethodRoadie$2.run(MethodRoadie.java:79)
	at org.junit.internal.runners.MethodRoadie.runBeforesThenTestThenAfters(MethodRoadie.java:87)
	at org.junit.internal.runners.MethodRoadie.runTest(MethodRoadie.java:77)
	at org.junit.internal.runners.MethodRoadie.run(MethodRoadie.java:42)
	at org.junit.internal.runners.JUnit4ClassRunner.invokeTestMethod(JUnit4ClassRunner.java:88)
	at org.junit.internal.runners.JUnit4ClassRunner.runMethods(JUnit4ClassRunner.java:51)
	at org.junit.internal.runners.JUnit4ClassRunner$1.run(JUnit4ClassRunner.java:44)
	at org.junit.internal.runners.ClassRoadie.runUnprotected(ClassRoadie.java:27)
	at org.junit.internal.runners.ClassRoadie.runProtected(ClassRoadie.java:37)
	at org.junit.internal.runners.JUnit4ClassRunner.run(JUnit4ClassRunner.java:42)
	at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:38)
	at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
Caused by: 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 ' postCode, street=('my county', 'MY1 PC1', 'my street') where customerId=1' at line 1

Query being executed when exception was thrown:

update Customer set county, postCode, street=('my county', 'MY1 PC1', 'my street') where customerId=1
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
	at com.mysql.jdbc.Util.getInstance(Util.java:381)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2019)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1937)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1922)
	at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:105)
	at project.tripplanner.database.hibernate.HibernatePreparedStatementWrapper.executeUpdate(HibernatePreparedStatementWrapper.java:164)
	at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:101)
	... 27 more
{noformat}


I tried extending the MYSQLDialect classes and overriding {{supportsRowValueConstructorSyntax()}} to return false, but this caused other errors.


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