Bots don't teleport to correct areas

[Rydelfox]

Issue: Bots are unable to teleport to appropriate areas in newer versions of SQL, and on older versions of SQL, they all crowd a single area.

Cause: In RandomPlayerbotMgr.cpp, 1126-1134 has this command: QueryResult results = WorldDatabase.Query("SELECT map, position_x, position_y, position_z " "FROM (SELECT map, position_x, position_y, position_z, AVG(t.maxlevel), AVG(t.minlevel), {} - (AVG(t.maxlevel) + AVG(t.minlevel)) / 2 delta " "FROM creature c INNER JOIN creature_template t ON c.id1 = t.entry WHERE t.npcflag = 0 AND t.lootid != 0 AND t.unit_flags != 768 GROUP BY t.entry HAVING COUNT(*) > 1) q " "WHERE delta >= 0 AND delta <= {} AND map IN ('{}') AND NOT EXISTS (SELECT map, position_x, position_y, position_z FROM " "(SELECT map, c.position_x, c.position_y, c.position_z, AVG(t.maxlevel), AVG(t.minlevel), {} - (AVG(t.maxlevel) + AVG(t.minlevel)) / 2 delta " "FROM creature c INNER JOIN creature_template t ON c.id1 = t.entry WHERE t.npcflag = 0 AND t.lootid != 0 GROUP BY t.entry) q1 WHERE abs(delta) > {} and q1.map = q.map AND SQRT(" "(q1.position_x - q.position_x) * (q1.position_x - q.position_x) + (q1.position_y - q.position_y) * (q1.position_y - q.position_y) + " "(q1.position_z - q.position_z) * (q1.position_z - q.position_z)) < {})", level, sPlayerbotAIConfig->randomBotTeleLevel, sPlayerbotAIConfig->randomBotMapsAsString.c_str(), level, sPlayerbotAIConfig->randomBotTeleLevel, (uint32)sPlayerbotAIConfig->sightDistance); This is not grouping by the fiends map, position_x, position_y, and position_z, but is attempting to pull from them anyway. In newer versions of SQL, (5.3 and later, I think), this will give an error, leaving some important data blank. In order versions of SQL, this will result in only one map coordinate per level range.

Fix: Change those lines to: QueryResult results = WorldDatabase.Query("SELECT map, position_x, position_y, position_z " "FROM (SELECT map, position_x, position_y, position_z, AVG(t.maxlevel), AVG(t.minlevel), {} - (AVG(t.maxlevel) + AVG(t.minlevel)) / 2 delta " "FROM creature c INNER JOIN creature_template t ON c.id1 = t.entry WHERE t.npcflag = 0 AND t.lootid != 0 AND t.unit_flags != 768 GROUP BY t.entry, map, position_x, position_y, position_z HAVING COUNT(*) > 1) q " "WHERE delta >= 0 AND delta <= {} AND map IN ('{}') AND NOT EXISTS (SELECT map, position_x, position_y, position_z FROM " "(SELECT map, c.position_x, c.position_y, c.position_z, AVG(t.maxlevel), AVG(t.minlevel), {} - (AVG(t.maxlevel) + AVG(t.minlevel)) / 2 delta " "FROM creature c INNER JOIN creature_template t ON c.id1 = t.entry WHERE t.npcflag = 0 AND t.lootid != 0 GROUP BY t.entry, map, position_x, position_y, position_z) q1 WHERE abs(delta) > {} and q1.map = q.map AND SQRT(" "(q1.position_x - q.position_x) * (q1.position_x - q.position_x) + (q1.position_y - q.position_y) * (q1.position_y - q.position_y) + " "(q1.position_z - q.position_z) * (q1.position_z - q.position_z)) < {})", level, sPlayerbotAIConfig->randomBotTeleLevel, sPlayerbotAIConfig->randomBotMapsAsString.c_str(), level, sPlayerbotAIConfig->randomBotTeleLevel, (uint32)sPlayerbotAIConfig->sightDistance);

[htc16]

Wow that's cool, looking forward to testing this! Thank you!

[htc16]

@ZhengPeiRu21

[htc16]

I still couldn't be sure about this change on how to produce it in the game, could you indicate me? I already made the change you suggested, however the bots still look the same. @Rydelfox

[Rydelfox]

I had issues when when the bots spawned in, most of them would just stand there. The few that would move, wouldn't attack any mobs. Additionally, the console was giving SQL errors about this statement. After fixing it, the errors went away, and the mobs were behaving normally.

EDIT: I did try disabling sql_mode only_full_group_by which reverts to old behavior and they worked partially, but the bots would only go to a few zones

[htc16]

very good, thank you!!

[htc16]

Oh, I just saw that my bots really improve. I had some bots that never left the same place. Cool!

[ZhengPeiRu21]

Thank you for submitting this fix! Sorry that it has taken me so long to get to this; development has been slow the past few months as I had to focus on other things but I am hope to resume a faster pace. This fix has been integrated in https://github.com/ZhengPeiRu21/mod-playerbots/commit/375d09abd231e02d1316ec9ff13219eb3ec3991d.