Latest revision as of 12:34, 2 October 2017

Understanding Result Sets

Every time you do a query on a database you ask Osiris to give you back all the rows in that database that match the values you passed to it. The collection of these rows is called a result set. Osiris will take all rows in the database and filter out the rows that have keys (columns) that don't match the passed parameters. Any parameter that Osiris doesn't know will be considered an output parameter and will match any value in that column.

That's the abstract theory, here's a little example:

PROC
ProcQueryDatabase()
AND
DB_TestDB(_Col1,_Col2)
THEN
ProcProcessValues(_Col1,_Col2);

In this example, _Col1 and _Col2 don't have a passed value, so Osiris will treat them as output parameters. Since these will match everything, you get a result set that encompasses the complete database. This allows you to iterate over the database, row by row.

PROC
ProcQueryDatabase((INTEGER)_PassedCol1)
AND
DB_TestDB(_PassedCol1,_Col2)
THEN
ProcProcessValues(_PassedCol1,_Col2);

In this example, we passed a value for the first column. So now Osiris will only return the rows that have a matching value in the first column. If there are no rows with a matching value for the first column, the result set is empty and the THEN part of this rule will not executed.

What happens if you combine multiple database queries together? Osiris will then combine the result sets together to come up with 1 big result set. It will do this by taking the result set of each query and use each row in that result set to generate a result set for the subsequent queries.

To clarify this with an example:

DB_A("a");
DB_A("b");

DB_B(1);
DB_B(2);

PROC
ProcQueryDatabase()
AND
DB_A(_A)
AND
DB_B(_B)
THEN
DB_AB(_A,_B);

So what happens when Osiris encounters something like this?

1. Osiris sees the query on DB_A and generates a result set for it
   • The Result Set is: ("a");("b" ) (where the rows in the Result Set are seperated by ';')
2. Osiris now goes over the rest of the conditions for each row in the result set that was generated:
   1. using row "a" of the Result Set:
      1. Osiris sees the query on DB_B and generates a result set for it
         • That Result Set is: (1);(2)
      2. Osiris combines the result sets and ends up with this result set: ("a",1);("a",2)
      3. For each row in that result set:
         1. using ("a",1)
            1. Osiris executes the THEN part of the rule and adds ("a",1) to DB_AB
         2. using ("a",2)
            1. Osiris executes the THEN part of the rule and adds ("a",2) to DB_AB
   2. using row "b" of the Result Set:
      1. Osiris sees the query on DB_B and generates a result set for it
         • That Result Set is: (1);(2)
      2. Osiris combines the result sets and ends up with this result set: ("b",1);("b",2)
      3. For each row in that result set:
         1. using ("b",1)
            • Osiris executes the THEN-part of the rule and adds ("b",1) to DB_AB
         2. using ("b",2)
            • Osiris executes the THEN-part of the rule and adds ("b",2) to DB_AB

Some things to note about this example:

• Osiris runs over databases in the order the entries were added to. In the example "a" came before "b".
• Osiris generates and merges result sets for each database query you do and processes the rest of the conditions like that!

Now let's look at the following example:

PROC
ProcComplicatedDatabaseQuery((CHARACTERGUID)_Player,(INTEGER)_ID)
AND
DB_CombatCharacters(_Player, _ID)        //1
AND
DB_CombatCharacters(_Enemy1, _ID)        //2
AND
DB_CombatCharacters(_Enemy2, _ID)        //3
AND
CharacterIsEnemy(_Enemy1, _Player, 1)    
AND
CharacterIsEnemy(_Enemy2, _Player, 1)
AND
_Enemy2 != _Enemy1
THEN
//do something with _Player,_Enemy1 and _Enemy2

From the previous discussion we see that this happens:

1. Osiris sees the first query and generates a result set for it. Since all values were passed this will be either empty or contain exactly 1 row. Let's assume it contained exactly 1 row
2. For the row of 1, Osiris now generates a Result Set for query 2. Since only _ID was passed this is a Result Set that contains every row that has a second column with _ID as a value. This will be a Result Set with multiple rows in it!
3. Osiris now combines the 2 result sets to make a new result set: this will be a set with rows that are of the form:

   (_Player,_ID,_Enemy1a)

   (_Player,_ID,_Enemy1b)

   (_Player,_ID,_Enemy1c)

   (_Player,_ID,_Enemy1d)

   ...

   (_Player,_ID,_Enemy1z) (where Z denotes the last row in that DB_CombatCharacters database)

4. Osiris now runs over that combined Result Set and encounters query 3. It generates a Result Set for this Query. Since only _ID was a passed value, this is a Result Set that contains every row that has a second column with _ID as a value. Note that these are the same values as the ones returned by query 2!
5. Osiris now combines the Result Sets to come up with a new Result Set. These will be of the form:

   (_Player,_ID,_Enemy1a,_Enemy2a)

   (_Player,_ID,_Enemy1a,_Enemy2b)

   (_Player,_ID,_Enemy1a,_Enemy2c)

   ...

   (_Player,_ID,_Enemy1a,_Enemy2z)

   (_Player,_ID,_Enemy1b,_Enemy2a)

   (_Player,_ID,_Enemy1b,_Enemy2b)

   (_Player,_ID,_Enemy1b,_Enemy2c)

   ...

   (_Player,_ID,_Enemy1b,_Enemy2z)

   (_Player,_ID,_Enemy1c,_Enemy2a)

   (_Player,_ID,_Enemy1c,_Enemy2b)

   (_Player,_ID,_Enemy1c,_Enemy2c)

   ...

   (_Player,_ID,_Enemy1c,_Enemy2z)

   ...

   NOTE that this Result Set's rows has exploded! It runs over the first Result Set of query 1 in order by combining it with the Reuslt Set of query 2

6. For every row in the Result Set of step 5: execute the CharacterIsEnemy query on _enemy1 and then on _enemy2. Then compare the 2 for equality.

This example demonstrates a couple of problems:

1. Because of the database queries with unconstrained (ie output) parameters the Result Set grows tremendously. Each resultset gets combined with the previous one and is run over in order. Step 5 of the example clearly demonstrates this. Osiris does NOT generate sets of the form:
    (_Player,_ID,_Enemy1a,_Enemy2a)
    (_Player,_ID,_Enemy1b,_Enemy2b)
    (_Player,_ID,_Enemy1c,_Enemy2c)
   Instead it combines each row of the previous result set with each row of the second result set, before moving on to the next row in the first result set.
2. The CharacterIsEnemy(_Enemy1, _Player, 1) query is executed for every row of the combined Result Set. If it was moved up higher the first Result Set could've been constrained:

   PROC
   ProcComplicatedDatabaseQuery((CHARACTERGUID)_Player,(INTEGER)_ID)
   AND
   DB_CombatCharacters(_Player, _ID)        //1
   AND
   DB_CombatCharacters(_Enemy1, _ID)        //2
   AND
   CharacterIsEnemy(_Enemy1, _Player, 1)    
   AND
   DB_CombatCharacters(_Enemy2, _ID)        //3
   AND
   CharacterIsEnemy(_Enemy2, _Player, 1)
   AND
   

   Using the above construction will not include _Enemy1 rows that do not pass the IsEnemy check. This means that every _Enemy1 is only checked once with the CharacterIsEnemy query. And not every one of them will contribute to the combined Result Set of query //3.
3. The _Enemy2 != _Enemy1 check could've been done as soon as _Enemy2 was queried. Now a CharacterIsEnemy check is done, followed by a comparison. If the 2 objects were the same, the CharacterIsEnemy query was unnecessary. Remember that an Osiris comparison or DB lookup is cheaper than making a call to the game code.

Is there anything we can do to avoid problems like the one shown in the example? Depending on what you want to do, it might be better or possible to divide the problem in separate steps using multiple procedures:

PROC
ProcComplicatedDatabaseQuery((CHARACTERGUID)_Player,(INTEGER)_ID)
AND
DB_CombatCharacters(_Enemy, _ID)        
AND
CharacterIsEnemy(_Enemy, _Player, 1)    
THEN
DB_EnemyDB(_Enemy);

PROC
ProcComplicatedDatabaseQuery((CHARACTERGUID)_Player,(INTEGER)_ID)
AND
DB_EnemyDB(_Enemy)
AND
DB_EnemyDB(_Enemy2)
AND
_Enemy!=_Enemy2
THEN
//do something with _Player,_Enemy and _Enemy2

//clean up intermediate result
PROC
ProcComplicatedDatabaseQuery((CHARACTERGUID)_Player,(INTEGER)_ID)
AND
DB_EnemyDB(_Enemy)
THEN
NOT DB_EnemyDB(_Enemy);

Structuring the problem like this, compared to the initial version we can see that each object in the DB_CombatCharacters database is only checked once with the CharacterIsEnemy query. The procedure that does the work (the middle definition) is also more compact and thus easier to read and maintain. If complexer filtering rules are needed they could be added to the first procedure that does nothing but filter.

Osiris bug with User Queries in Rule Conditions

There is a bug in Osiris in how it handles User Queries that could result in recursive calls to that same condition. Observe the following example:

IF
DB_ThatTriggersRule1(_Char)
AND
ObjectIsCharacter(_Char, 1)
AND
NOT DB_Dead(_Char)
AND
QRY_ReturnAResult(_Char)
AND
DB_QueryResult(_Result)
THEN
//do something

QRY
QRY_ReturnAResult((CHARACTERGUID)_Char)
AND
DB_QueryResult(_Rv)
THEN
NOT DB_QueryResult(_Rv);

QRY
QRY_ReturnAResult((CHARACTERGUID)_Char)
THEN
DB_QueryResult(_Rv);

What happens here is that when a fact is added to the DB_ThatTriggersRule1 database, it runs through the rule and executes the User Query QRY_ReturnAResult as part of the conditions. When the Query adds to the DB_QueryResult DB, however, Osiris sees that the DB DB_QueryResult is modified and it re-evaluates the rule again. This results (at the moment) in Osiris overwriting intermediate results and would result in a crash. This will now generate a critical assert (so it won't crash in Shipping) and stops forwarding the invalid results.

To avoid these situations, avoid having Queries with "return values" as part of rules that react to database adds. Instead defer these to procedures:

PROC
ProcCalledWhenDBIsModified((CHARACTERGUID)_Char)
AND
ObjectIsCharacter(_Char, 1)
AND
NOT DB_Dead(_Char)
AND
QRY_ReturnAResult(_Char)
AND
DB_QueryResult(_Result)
THEN
//do something

QRY
QRY_ReturnAResult((CHARACTERGUID)_Char)
AND
DB_QueryResult(_Rv)
THEN
NOT DB_QueryResult(_Rv);

QRY
QRY_ReturnAResult((CHARACTERGUID)_Char)
THEN
DB_QueryResult(_Rv);

Since this is now called from a procedure it will not cause a re-evaluate of the conditions in ProcCalledWhenDBIsModified.