[odb-users] Request return different results if executed in command line

[Romain Gros]

Hi,

I am experiencing something weird with ODB right now, you will find the
code/view/data and the test case at the end.
I made an SQL request and it works fine, I got the behavior that I want.
I made an ODB view based on the sql request, and the output SQL statement
is the same as the handmade request.

But my view, when executed, doesn't get me the same results!
I have the same sql output, but not the same results and it seems really
weird.

In my request, I use 2 left joins to check if the corresponding record
doesn't exist, but it's like they are being ignored.

I am experiencing this problem on MySQL and PostgreSQL.

Do I do something wrong here?

Thanks a lot,
Romain Gros

PS: Sorry for the long post, I really am in the dark here.
PPS: I had another quick question, is it possible to make sub-queries in
the where clause using ODB instead of writing pure sql in the query ?

===== Code =====
---- The SQL, generate by odb : ----

SELECT
  `qs`.`idquest`,
  MIN(`qs`.`steporder`)
FROM
  `queststep` AS `qs`
  LEFT JOIN `quest` AS `q`
    ON `qs`.`idquest` = `q`.`idquest`
  LEFT JOIN `characterdonequeststep` AS `cdqs`
    ON `cdqs`.`idqueststep` = `qs`.`idqueststep`
    AND `cdqs`.`idcharacter` = 2
  LEFT JOIN `characterownedquest` AS `coq`
    ON `coq`.`idqueststep` = `qs`.`idqueststep`
    AND `coq`.`idcharacter` = 2
WHERE (
    (
      (`q`.`level` <= 1)
      AND (`cdqs`.`idqueststep` IS NULL)
    )
    AND (`coq`.`idqueststep` IS NULL)
  )
GROUP BY `qs`.`idquest`

---- The view ----

# pragma db view \
        object(QuestStep = qs) \
        object(Quest = q: qs::_quest) \
        object(CharacterDoneQuestStep = cdqs:
cdqs::_idcharacterdonequeststep._idqueststep + "=" + qs::_idqueststep + "
AND " + cdqs::_idcharacterdonequeststep._idcharacter + " = " +
odb::query<AvailableQuestStep>::_ref(AvailableQuestStep_idCharacter)) \
        object(CharacterOwnedQuest = coq:
coq::_idcharacterownedquest._idqueststep + "=" + qs::_idqueststep + " AND "
+ coq::_idcharacterownedquest._idcharacter + " = " +
odb::query<AvailableQuestStep>::_ref(AvailableQuestStep_idCharacter)) \
        query(((?)
   \
              && cdqs::_idcharacterdonequeststep._idqueststep.is_null()
  \
              && coq::_idcharacterownedquest._idqueststep.is_null())
    \
              + "GROUP BY" + qs::_quest)
struct AvailableQuestStep {
# pragma db column(qs::_quest)
  int _idquest;
# pragma db column("min(" + qs::_steporder + ")")
  int _steporder;
};


---- Objets ----
The QuestStep has a reference to the Quest.
The CharacterDoneQuestStep and CharacterOwnedQuest contain a composite id.

If needed, I can show the source but I didn't want to put too much here.


---- Database ----
In my example, I have:

3 Quests: id [4, 5, 6] (level 1)

3 QuestSteps:
- id: 9, quest: 4
- id: 10, quest: 5
- id: 11, quest: 6

0 CharacterDoneQuestStep

1 CharacterOwnedQuest:
- idcharacter: 2, idqueststep: 9


===== Test case =====
Executing the above request give me:
- idquest: 5, min(): 0
- idquest: 6, min(): 0

Which is good because the Character already owns the QuestStep 9.

But when I run it with ODB, it gives me:
- idquest: 4, min(): 0
- idquest: 5, min(): 0
- idquest: 6, min(): 0

Which is not what I want.