JoinGroup acts as if parent key has changed/Statement returned more than one row

[Dian Fay]

I'm getting a similar error to someone a while back -- Statement '{selectOne}' returned more than one row. I'm using MySQL 5.5 and compiled orbroker 3.2.1-1 for Scala 2.10 locally since it's not out for that version yet. The table structure is a many-to-many join (although only the Project domain class has a practices property; it doesn't go the other way):

create table project (id binary(16) not null primary key, name varchar(100), url varchar(2083));

create table practice (id binary(16) not null primary key, code char(1), description varchar(50));

create table project_practice (project_id binary(16) not null, practice_id binary(16) not null, 

foreign key (project_id) references project(id),

foreign key (practice_id) references practice(id));

Debugging into orbroker shows that it's pulling the first project and practice out correctly, but when it's starting on the next row in JoinGroup.extractRows it determines that the key has changed and creates another project-practice pair. Here's the token and extractors I'm using:

val selectProjectById = Token('selectProjectById, ProjectExtractor, UUIDBinaryConv)

 

object ProjectExtractor extends JoinExtractor[Project] {

    val key = Set("id")

    def extract(row: Row, join: Join) = {

        val id = UUIDBinaryConv.fromBytes(row.binary("id").get)

        val name = row.string("name").get

        val url = row.string("url").get

        val practices = join.extractSeq(PracticeExtractor, Map("id" -> "practice_id"))

        new Project(id, name, url, practices)

    }

}

object PracticeExtractor extends JoinExtractor[Practice] {

    val key = Set("id")

    def extract(row: Row, join: Join) = {

      val id = UUIDBinaryConv.fromBytes(row.binary("id").get)

      new Practice(

            id,

            row.string("code").get,

            row.string("description").get

        )

    }

} 

And the service call that's giving me problems is a straightforward broker.readOnly() { session => session.selectOne(selectProjectById, "id" -> id) } against this query:

SELECT p.id, p.name, p.url, practice.id AS practice_id, practice.code, practice.description

FROM project p

  JOIN project_practice pp ON pp.project_id = p.id

  JOIN practice ON practice.id = pp.practice_id

WHERE p.id = :id

ORDER BY p.id;

 

I've gone through the testcases and other peoples' examples and it looks like it should be working. The only thing I could think of was a name collision between the ID columns (in spite of the rename map), but I went through and made both ID column names unique and got the exact same result. I'm not sure what I've missed, anyone else have an idea?

[Nils Kilden-Pedersen]

If it's a name collision it could be because some drivers mix up column names and labels. Take a look at org.orbroker.adapt.ColumnNameAdapter and see if mixing in a different implementation will fix this.

[Dian Fay]

I'm using MySQL Connector/J 5.1.23 which conforms to JDBC4 as far as names vs labels (http://bugs.mysql.com/bug.php?id=35610) so it looks like the default implementation should be fine. Since I tried renaming the ID fields to be unique (removing the labels entirely) and got the same error I don't think it's a name collision, that was just my best guess at first.

[Nils Kilden-Pedersen]

On Tuesday, March 12, 2013 10:07:03 AM UTC-5, Dian Fay wrote:

Debugging into orbroker shows that it's pulling the first project and practice out correctly, but when it's starting on the next row in JoinGroup.extractRows it determines that the key has changed

Presumably in row.matches(getKeyValues)? If so, can you debug and find out exactly why the two maps are different? Make sure you also check the datatypes of the values. Not sure if there's some inconsistency introduced in 2.10 that could break Map comparison.

[Dian Fay]

Apologies if it's a bit verbose -- I'm still trying to pick up Scala in the first place.

Entering the project extractor:

JoinGroup.extractGroup() -> newGroup() -> getKeyValues()

Map(ID -> [B@25e4e6db) (scala.runtime.ObjectRef)

ID -> [72,-62,104,-12,89,69,71,37,-87,98,52,37,41,-94,-88,-4] (String -> byte[16])

JoinGroup.row.newGroup(): newKeyValues

Map(ID -> [B@25e4e6db) (scala.collection.immutable.Map)

ID -> [72,-62,104,-12,89,69,71,37,-87,98,52,37,41,-94,-88,-4] (String -> byte[16])

enters match block, matches PracticeExtractor to JoinExtractor, enters extractJoin

instantiates proxy, which creates proxy row and group for the Practice:

Map(ID -> [B@474c0761) (scala.runtime.ObjectRef)

ID -> [78,79,111,78,77,15,68,37,-71,-60,86,95,73,-54,-20,-5] (String -> byte[16])

proxy.row.newGroup() newKeyValues

Map(ID -> [B@474c0761) (scala.collection.immutable.Map)

ID -> [78,79,111,78,77,15,68,37,-71,-60,86,95,73,-54,-20,-5] (String -> byte[16])

extractJoin calls getResultOrNull, which calls proxy.getKeyValues() and stores result in val:

Map(ID -> [B@474c0761) (scala.collection.immutable.Map)

ID -> [78,79,111,78,77,15,68,37,-71,-60,86,95,73,-54,-20,-5] (String -> byte[16])

getResultOrNull returns the first Practice

rsReadable set to rs.next (true)

while loop calls row.matches(getKeyValues). getKeyValues map:

Map(ID -> [B@79e4620e) (scala.runtime.ObjectRef)

ID -> [72,-62,104,-12,89,69,71,37,-87,98,52,37,41,-94,-88,-4] (String -> byte[16])

this is the expected result -- the same project.id, which it passes in to row.matches as compKeyValues. Trying to get the data out of row.keyValues throws "keyValues = No such instance method: 'org.orbroker.JoinGroup$$anon$1.keyValues'", but I would expect it to be the original project.id since the row itself shouldn't have changed.

[Nils Kilden-Pedersen]

Ahh, yes, it's perfectly clear what's happening. The keys are UUID, which are translated to byte arrays. Array equality is based on reference, not content.

Hmm, not sure what the right solution here is. I'm not convinced this is a O/R Broker problem, but maybe it is.

Your immediate way to fix this is to either not use UUIDs as primary keys, or to use a different conversion process that doesn't convert it into a byte array. E.g. conversion to String should work, although that will affect your performance (which you may or may not notice). A UUID consists of two Longs. It's also possible you could map that to two BIGINTs as a compound primary key. But that means mapping one field to two columns. Not sure if that's doable.

Nils

[Dian Fay]

oh jeez, I should've remembered that & looked at the addressing, thanks for pointing it out!

I'd push for supporting UUIDs, not solely out of self-interest (altho that's definitely part of it!) but because UUID keys are getting more and more common. Forcing a dual-bigint compound key leaves a bad taste in my mouth -- compound keys aren't always bad, but the requirement for them should come from the data itself not the access code imo -- and converting to String is just kind of sloppy, beyond the potential performance problems (which I don't think would be terrible for most apps, but still).

I think it'd be pretty easy to extend StatefulRow.matches to account for UUIDs, although I don't know if there're any other places that would need changing. This is a spare time project and nobody's breathing down my neck about it, so I'll work on that in my clone some & see if it makes it in -- I'm not in any rush.

[Dian Fay]

I've committed support for Array[Byte] keys in StatefulRows to my clone, along with a passing unit test (and an unfortunately largish number of supporting SQL files) based on my project. I'm quite sure it's not as good as it could be, given that I just started learning Scala a couple weeks ago, but it's something at least. 

[Dian Fay]

Nils, just thought I'd check with you to see if you've had a chance to look at this?

[Nils Kilden-Pedersen]

On Tuesday, March 19, 2013 9:22:58 AM UTC-5, Dian Fay wrote:

Nils, just thought I'd check with you to see if you've had a chance to look at this?

I've pushed the fix in the 3.2 branch.
 

[Dian Fay]

oh fantastic, thanks!