Semantic join produce empty result if comparing uuid inside where clause

[ridwanadhip]

Hi speedment team. I'm getting empty result when executing this code:

List<InstallmentPurchasePayment> result = orm.getOrThrow(JoinComponent.class)
    .from(InstallmentPurchaseManager.IDENTIFIER)
    .where(InstallmentPurchase.BUYER_ID.equal(userId))
    .innerJoinOn(InstallmentPurchasePayment.INSTALLMENT_PURCHASE_ID)
    .equal(InstallmentPurchase.ID)
    .build(Tuples::of)
    .stream()
    .map(Tuple2::get1)
    .collect(Collectors.toList());

But when using ordinary filter method:

List<InstallmentPurchasePayment> result = orm.getOrThrow(JoinComponent.class)
    .from(InstallmentPurchaseManager.IDENTIFIER)
    .innerJoinOn(InstallmentPurchasePayment.INSTALLMENT_PURCHASE_ID)
    .equal(InstallmentPurchase.ID)
    .build(Tuples::of)
    .stream()
    .filter(e -> e.get0().getBuyerId().equals(userId))
    .map(Tuple2::get1)
    .collect(Collectors.toList());

The result will be normal. This only happen when comparing UUID field with another UUID object in where clause (in this case BUYER_ID and userId). Other data type producing normal result. Thanks

speedment.zip

[minborg]

Can you show how you declare your local variable userId ?

[minborg]

Please enable logging of STREAM as per https://speedment.github.io/speedment-doc/application_configuration.html#logging and post the output of the first join.

[ridwanadhip]

@minborg something like this:

UUID userId = user.getId(); // user is object retrieved from speedment stream

[ridwanadhip]

Here is the log:

2018-12-03T10:58:28.003Z DEBUG [vert.x-worker-thread-3] (#STREAM) - SELECT `id`,`username`,`password`,`password_salt`,`name`,`phone`,`email`,`created_at`,`modified_at`,`created_by`,`modified_by`,`is_deleted` FROM `hijra`.`user` WHERE (`username`  = ? COLLATE utf8mb4_bin), values:[Ade]
2018-12-03T10:58:34.799Z DEBUG [vert.x-worker-thread-5] (#JOIN) - Resolving join with 2 stages:
 # Table Identifier                 Join Type    R# Referenced Table Identifier      Field            Operation    Referenced Field
 0 hijra.hijra.installment_purchase null         -1 null                             null             null         null            
 1 hijra.hijra.installment_purchase_payment INNER_JOIN    0 hijra.hijra.installment_purchase installment_purchase_id EQUAL        id              
2018-12-03T10:58:34.815Z DEBUG [vert.x-worker-thread-5] (#STREAM) - SELECT A.`id`,A.`code`,A.`total_price`,A.`down_payment`,A.`monthly_bill`,A.`installment_product_id`,A.`installment_product_color_id`,A.`installment_product_tenor_id`,A.`buyer_id`,A.`state`,A.`created_at`,A.`modified_at`,A.`created_by`,A.`modified_by`,A.`is_deleted`, B.`id`,B.`installment_purchase_id`,B.`month`,B.`amount`,B.`start_date`,B.`due_date`,B.`state`,B.`transaction_id`,B.`paid_at`,B.`created_at`,B.`modified_at`,B.`created_by`,B.`modified_by`,B.`is_deleted` FROM `hijra`.`installment_purchase` AS A INNER JOIN `hijra`.`installment_purchase_payment` AS B ON (B.`installment_purchase_id` = A.`id`)  WHERE (A.`buyer_id` = ?), values:[b6b86ffd-a577-47d6-8131-99d0aeca2572]

[minborg]

Your speedment.json file indicates that you store your UUIDs as binary data. However, the code segment from the logged join query WHERE (A.buyer_id = ?), values:[b6b86ffd-a577-47d6-8131-99d0aeca2572] indicates that the UUID was not converted back to binary before it was rendered in the SQL predicate. We will take a look at this issue which is likely to be a bug in Speedment.

The selected TypeMapper::toDatabaseType should be invoked internally in Speedment when rendering JOIN predicates.

Thanks for identifying this issue.

[minborg]

While we are looking at this issue, you could use your second alternative as a temporary work-around.

[minborg]

We have been able to reproduce the error and are working on a solution.

[minborg]

A fix has been pushed to 3.1.10-SNAPSHOT. @ridwanadhip would it be possible for you to clone and build this snapshot version and try it in your application to make sure that it now works as expected?

[ridwanadhip]

@minborg I can confirm that latest snapshot fixed this bug, no more empty result. Thank you!

[minborg]

@ridwanadhip Thank you for testing the fix so quickly. I will mark this issue as closed now. Will be released in 3.1.10