Missing WHERE clause arguments with @ManyToMany

[dannyweldon]

My sample code below creates an object and saves it correctly to the database, but then fails to load the same object from the database and gives an SQL syntax error as the HQL is missing the arguments to the WHERE clause:

ddbc.core.SQLException@../../.dub/packages/ddbc-0.3.2/ddbc/source/ddbc/drivers/mysqlddbc.d(252): MySQL error: 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 '' at line 1 while execution of query SELECT _t1.id, _t1.name FROM role AS _t1 WHERE

import std.stdio;
import hibernated.core;

class User {
    long id;
    string name;

    @ManyToMany
    Role[] roles;
}

class Role {
    long id;
    string name;

    @ManyToMany
    User[] users;
}

int main(string[] argv)
{
    EntityMetaData schema = new SchemaInfoImpl!(User, Role);

    // setup DB connection factory
    version (USE_MYSQL) {
        MySQLDriver driver = new MySQLDriver();
        string url = MySQLDriver.generateUrl("localhost", 3306, "test");
        string[string] params = MySQLDriver.setUserAndPassword("root", "");
        Dialect dialect = new MySQLDialect();
    } else {
        SQLITEDriver driver = new SQLITEDriver();
        string url = "test.db"; // file with DB
        static import std.file;
        if (std.file.exists(url))
                std.file.remove(url); // remove old DB file
        string[string] params;
        Dialect dialect = new SQLiteDialect();
    }
    DataSource ds = new ConnectionPoolDataSourceImpl(driver, url, params);

    // create session factory
    SessionFactory factory = new SessionFactoryImpl(schema, dialect, ds);
    scope(exit) factory.close();

    // Create schema if necessary
    {
        // get connection
        Connection conn = ds.getConnection();
        scope(exit) conn.close();

        // create tables if not exist
        factory.getDBMetaData().updateDBSchema(conn, false, true);
    }

    // Now you can use HibernateD

    // create session
    Session sess = factory.openSession();
    scope(exit) sess.close();

    Role admin = new Role();
    admin.name = "Admin";
    sess.save(admin);

    Role engineer = new Role();
    engineer.name = "Engineer";
    sess.save(engineer);

    Role unix = new Role();
    unix.name = "Unix";
    sess.save(unix);

    User john = new User();
    john.name = "John";
    john.roles = [admin,unix];
    sess.save(john);

    writeln(john.name ~ ":");
    foreach (role; john.roles) {
        writeln(role.name);
    }

    User user = new User();
    sess.load(user, 1);

    return 0;
}

Output:

Performing "debug" build using dmd for x86.
derelict-util 2.0.6: target for configuration "library" is up to date.
derelict-pq 2.0.3: target for configuration "library" is up to date.
mysql-native 0.1.6: target for configuration "library" is up to date.
ddbc 0.3.2: target for configuration "full" is up to date.
hibernated 0.2.32: target for configuration "full" is up to date.
hiberbug ~master: building configuration "application"...
Linking...
To force a rebuild of up-to-date targets, run again with --force.
Running ./hiberbug
John:
Admin
Unix
ddbc.core.SQLException@../../.dub/packages/ddbc-0.3.2/ddbc/source/ddbc/drivers/mysqlddbc.d(252): MySQL error: 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 '' at line 1 while execution of query SELECT _t1.id, _t1.name FROM role AS _t1 WHERE
----------------
../../.dub/packages/ddbc-0.3.2/ddbc/source/ddbc/drivers/mysqlddbc.d:252 ddbc.core.PreparedStatement ddbc.drivers.mysqlddbc.MySQLConnection.prepareStatement(immutable(char)[]) [0x8196884]
../../.dub/packages/ddbc-0.3.2/ddbc/source/ddbc/common.d:96 ddbc.core.PreparedStatement ddbc.common.ConnectionWrapper.prepareStatement(immutable(char)[]) [0x8192f05]
../../.dub/packages/hibernated-0.2.32/hibernated/source/hibernated/session.d:1066 Object[] hibernated.session.QueryImpl.listObjects(Object, hibernated.session.PropertyLoadMap) [0x8183838]
../../.dub/packages/hibernated-0.2.32/hibernated/source/hibernated/session.d:1010 void hibernated.session.QueryImpl.delayedLoadRelations(hibernated.session.PropertyLoadMap) [0x81833b4]
../../.dub/packages/hibernated-0.2.32/hibernated/source/hibernated/session.d:1083 Object[] hibernated.session.QueryImpl.listObjects(Object, hibernated.session.PropertyLoadMap) [0x8183963]
../../.dub/packages/hibernated-0.2.32/hibernated/source/hibernated/session.d:1051 Object[] hibernated.session.QueryImpl.listObjects(Object) [0x8183761]
../../.dub/packages/hibernated-0.2.32/hibernated/source/hibernated/session.d:844 Object hibernated.session.QueryImpl.uniqueObject(Object) [0x81822c5]
../../.dub/packages/hibernated-0.2.32/hibernated/source/hibernated/session.d:142 Object hibernated.session.Query.uniqueResult!(Object).uniqueResult(Object) [0x818418a]
../../.dub/packages/hibernated-0.2.32/hibernated/source/hibernated/session.d:397 Object hibernated.session.SessionImpl.getObject(const(hibernated.metadata.EntityInfo), Object, std.variant.VariantN!(24u).VariantN) [0x817f24d]
../../.dub/packages/hibernated-0.2.32/hibernated/source/hibernated/session.d:390 void hibernated.session.SessionImpl.loadObject(Object, std.variant.VariantN!(24u).VariantN) [0x817f045]
../../.dub/packages/hibernated-0.2.32/hibernated/source/hibernated/session.d:92 void hibernated.session.Session.load!(app.User, int).load(app.User, int) [0x8173880]
source/app.d:86 _Dmain [0x8154138]
??:? _D2rt6dmain211_d_run_mainUiPPaPUAAaZiZ6runAllMFZ9__lambda1MFZv [0x81e61aa]
??:? void rt.dmain2._d_run_main(int, char**, extern (C) int function(char[][])*).tryExec(scope void delegate()) [0x81e60fc]
??:? void rt.dmain2._d_run_main(int, char**, extern (C) int function(char[][])*).runAll() [0x81e6166]
??:? void rt.dmain2._d_run_main(int, char**, extern (C) int function(char[][])*).tryExec(scope void delegate()) [0x81e60fc]
??:? _d_run_main [0x81e608e]
??:? main [0x8175933]
??:? __libc_start_main [0xb73d772d]
Program exited with code 1

Database contents:

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| role           |
| role_users     |
| user           |
+----------------+
3 rows in set (0.01 sec)

mysql> select * from role;
+----+----------+
| id | name     |
+----+----------+
|  1 | Admin    |
|  2 | Engineer |
|  3 | Unix     |
+----+----------+
3 rows in set (0.01 sec)

mysql> select * from user;
+----+------+
| id | name |
+----+------+
|  1 | John |
+----+------+
1 row in set (0.00 sec)

mysql> select * from role_users;
+---------+---------+
| role_fk | user_fk |
+---------+---------+
|       1 |       1 |
|       3 |       1 |
+---------+---------+
2 rows in set (0.00 sec)

[dannyweldon]

Hi Vadim,

I think I have tracked down the location of the problem, but don't feel confident enough to attempt to fix it.

In session.d, inside the block that starts like this:

            } else if (pi.oneToMany || pi.manyToMany) {
                string hql = "FROM " ~ pi.referencedEntity.name ~ " WHERE " ~ pi.referencedPropertyName ~ "." ~ pi.referencedEntity.keyProperty.propertyName ~ " IN (" ~ keys ~ ")";

I believe that the code in that block can only handle oneToMany. It would have to use the join table for a manyToMany relation, whereas now it just references the related table directly, which is correct only for oneToMany and pi.referencedPropertyName is even undefined for a manyToMany relation.

Merry Christmas!

[KrzaQ]

In case anyone has a similar problem in the future. Changing the type of @ManyToMany field from T[] to LazyCollection!T magically made it work for me.

[dannyweldon]

@KrzaQ Thanks. I tried that and it worked for me, too. It's still a bug, of course, as it should be possible to do this without using LazyCollection, but at least I now have a workaround.