[FIXED][HIGH] - Missing goodies

[celebaglar]

In game 87 - Enola Fey - I have searched quite a lot and so far have found nothing at every lith and ruin that's been visited. Could it be that under certain conditions no goodies are initially allocated to the usual places at all?

Waters of life seem fine though.

[sparrowhawk]

Hi Bill,

This is a known bug ( I think that it's been raised twice before? Not sure exactly).

I'm pretty sure that the goodies get created correctly, so it must be in the seeking code.

[celebaglar]

Yes, I raised the last one I seem to remember...

However, there is a difference. While on that occasion there were quite a few empty liths, this time every lith and ruin seems empty, over 8 days and more than 10 searches.

I'll happily close this thread if it's a duplicate though.

[sparrowhawk]

I was wrong - I just checked and you are quite correct, there are no goodies at ANY liths in this game

There ARE goodies at:
Lakes
Henges
Caverns
Snowhalls
Villages

So Ruins are also not getting their goodies-allocation. Any terrain type that I've missed? (Towers work in a different way)

It's gone straight to the top of my to do list. Hopefully have it resolved by tomorrow.

[sparrowhawk]

OK, I need some help from fellow MySQL people out there.

It's not just ruins and liths, it's any terrain where more than 1 type of goodie might appear. So only Villages and Snowhalls are guranteed to work ok, as they have a 100% probablity of shelter.


INSERT INTO MSI_MapSpecialItems(MSI_GAM_ID, MSI_TTY_ID, MSI_Row, MSI_Column, MSI_SIT_ID)
SELECT $gameID, MPD_TTY_ID, MPD_Row, MPD_Column, TSI_SIT_ID          
 FROM MPD_MapDescriptions  INNER JOIN TSI_TerrainSpecialItems ON TSI_TTY_ID = MPD_TTY_ID            
 WHERE (CEILING( RAND() * 100) BETWEEN TSI_LowerBoundary AND TSI_UpperBoundary)
   AND MPD_SCN_ID = $scenarioID        
   GROUP BY MPD_ID  //This avoids duplicates


The culprit is the first WHERE clause, with the random number. It's identifying a record in the TSI table where the random number falls within the range set by two fields.

If I replace the CEILING( RAND() * 100) with a fixed number, say 25, then all liths for example get COD. With 55, they all get BOC. So the SQL is logically correct, but the fact that I am using a random number function to return an integer between 1 and 100 fails, whereas a hard coded value works

Can anyone shed any light onto this?


The TSI table and it's contents are as follows, if that helps (apologies for lack of formatting):

+--------+------------+------------+-------------------+-------------------+
| TSI_ID | TSI_TTY_ID | TSI_SIT_ID | TSI_LowerBoundary | TSI_UpperBoundary |
+--------+------------+------------+-------------------+-------------------+
| 1 | L | COD | 1 | 50 |
| 2 | L | BOC | 51 | 90 |
| 3 | L | CON | 91 | 95 |
| 4 | L | TOD | 96 | 100 |
| 5 | H | TOD | 1 | 30 |
| 6 | H | CON | 31 | 60 |
| 7 | H | COD | 61 | 70 |
| 8 | H | BOC | 71 | 80 |
| 9 | H | GLD | 81 | 90 |
| 10 | E | WOL | 1 | 90 |
| 11 | E | SW | 91 | 100 |
| 12 | S | SHR | 1 | 100 |
| 13 | V | SHR | 1 | 100 |
| 14 | N | SHR | 1 | 50 |
| 15 | N | BOC | 51 | 60 |
| 16 | N | TOD | 61 | 70 |
| 17 | N | GLD | 71 | 90 |
| 18 | N | COD | 91 | 100 |
| 19 | R | COD | 1 | 30 |
| 20 | R | CON | 31 | 60 |
| 21 | R | BOC | 61 | 70 |
| 22 | R | TOD | 71 | 80 |
| 23 | R | GLD | 81 | 90 |
| 24 | R | SHR | 91 | 100 |
+--------+------------+------------+-------------------+-------------------+

[Natmus]

Jean-Yves said:

If I replace the CEILING( RAND() * 100) with a fixed number, say 25, then all liths for example get COD. With 55, they all get BOC. So the SQL is logically correct, but the fact that I am using a random number function to return an integer between 1 and 100 fails, whereas a hard coded value works

Can anyone shed any light onto this?

I'm not really into MySQL, but have you checked a list of numbers generated by the "CEILING( RAND() * 100)" function. What exactly do the "CEILING" bit do?

[Matija]

Hmmm, strange. Try replacing WHERE with AND?

[sparrowhawk]

Natmus said:

I'm not really into MySQL, but have you checked a list of numbers generated by the "CEILING( RAND() * 100)" function. What exactly do the "CEILING" bit do?

Yes, I've tried that and it's generating a correct range of numbers between 1 and 100.

The ceiling function takes a real number and converts it to the nearest highest integer (so 1.23 become 2, -1.23 become 1).

[sparrowhawk]

Matija said:

Hmmm, strange. Try replacing WHERE with AND?

You need a WHERE to tell the SQL where the conditions are starting.

All very puzzling.

[sparrowhawk]

I have solved the problem by changing the code to use 2 queries instead of one, although this does mean that the second one gets called numerous times within the loop of the first's result set. So slower.

This does not explain WHY the original does not work though

[Matija]

Jean-Yves said:

You need a WHERE to tell the SQL where the conditions are starting.

All very puzzling.

By changing where to and I meant that you try to add the condition to inner join's ON clause.

[sparrowhawk]

Aha! Sorry, misunderstood you.

Yes, I'd already tried that, and had exactly the same result.

[sparrowhawk]

Anyway, this should now be fixed (I had to change the processing to use 2 queries instead of 1). Please could you report back after you have played NEW games (ie those started since the 0.2f upload)

[sparrowhawk]

I've created a few test games and done some spot checks - LOM liths = 93, populated liths = 93

The others also seem OK.

I'm going to mark this as Fixed - but let me know if you wish to reopen this bug.