View source for Osiris Gotchas

= 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?

# 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 ';')
# Osiris now goes over the rest of the conditions for each row in the result set that was generated:
## using row "a" of the Result Set:
### Osiris sees the query on DB_B and generates a result set for it
###* That Result Set is: ''(1);(2)''
### Osiris combines the result sets and ends up with this result set: ''("a",1);("a",2)''
### For each row in that result set:
#### using ''("a",1)''
##### Osiris executes the THEN part of the rule and adds ("a",1) to DB_AB
#### using ''("a",2)''
##### Osiris executes the THEN part of the rule and adds ("a",2) to DB_AB
## using row "b" of the Result Set:
### Osiris sees the query on DB_B and generates a result set for it
###* That Result Set is: ''(1);(2)''
### Osiris combines the result sets and ends up with this result set: ("b",1);("b",2)
### For each row in that result set:
#### using ''("b",1)''
####* Osiris executes the ''THEN''-part of the rule and adds ''("b",1)'' to ''DB_AB''
#### 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:

# 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
# 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!
# 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)
# 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!
# 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'''
# '''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:
<ol>
<li> 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:<br/>
&nbsp;''(_Player,_ID,_Enemy1a,_Enemy2a)''<br/>
&nbsp;''(_Player,_ID,_Enemy1b,_Enemy2b)''<br/>
&nbsp;''(_Player,_ID,_Enemy1c,_Enemy2c)''<br/>
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.</li>
<li>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:
<pre>
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
</pre>
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.</li>
<li> 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.</li>
</ol>

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''.

[[Category:Osiris]]