missing FROM-clause entry for table "player"

[pasita]

And now for a bit more complex question... some data here first, question in the end.

I see a
2011-03-30 19:35:26 EEST ERROR: missing FROM-clause entry for table "player" at character 2893
in a postgresqlxxxxx.log.
The 2893 is pointing to
(max(player.mycol_in_table_player)) as "mycol_in_table_player" ( (max(player.mycol_in_table_player)) is the value of a column I made via PT3, and I have made a new column in table player that's referenced here)

The FROM clause in the log is (I hid the player names)

FROM player as p, holdem_hand_summary as hhs, holdem_hand_player_statistics as hhps WHERE ( p.id_site = 100 AND (p.player_name_search='x' OR p.player_name_search='xx' OR p.player_name_search='xxx' OR p.player_name_search='xxxx') ) AND p.id_player = hhps.id_player_real AND hhps.id_hand = hhs.id_hand AND hhs.hand_no = 600xxxxxxxx) as session WHERE (holdem_hand_player_statistics.id_session = session.id_session) AND (holdem_hand_player_statistics.id_hand = holdem_hand_player_detail.id_hand AND holdem_hand_player_statistics.id_player = holdem_hand_player_detail.id_player) AND (holdem_hand_summary.id_hand = holdem_hand_player_statistics.id_hand) AND (session.id_player = holdem_hand_player_statistics.id_player) AND (holdem_hand_summary.id_hand = holdem_hand_player_detail.id_hand) AND (lookup_actions_p.id_action=holdem_hand_player_detail.id_action_p) AND ((holdem_hand_summary.cnt_players >= 3 AND holdem_hand_summary.cnt_players <= 10)AND (holdem_hand_player_statistics.date_played < '2011/03/30 00:12:03'))

The situation:
-I open a replayer window
-I get the error in the log
-previously, there were a lot more errors in the log. I changed the PT column (max(player.mycol_in_table_player)) to (max(player_real .mycol_in_table_player)) as suggested by the previous error. I think I got the replayer hud appear a lot faster. (It's still about 7 seconds from the replayer window popping up... my db is 200k ish hands)
-I've applied the stats, updated cache, rebuilt and reindexed and rebooted.... this error referencing table player instead of player_real still keeps popping up.

Where can the old definition (not referencing to player_real, but player) of the PT3 column still be lurking?

[kraada]

Can you paste the entire query please? Just the from clause is not sufficient to troubleshoot this error.

[pasita]

If I post it I'm not sure it fits on one page:)
It's basically just (bolded the stat that's referenced as 2893

2011-03-30 20:18:33 EEST ERROR: missing FROM-clause entry for table "player" at character 2893
2011-03-30 20:18:33 EEST STATEMENT: SELECT (session.str_player_name_search) as "str_player_name_search_real",...(load of other stats)...,(max(player.mycol_in_table_player)) as "mycol_in_table_player" , ...(load of other stats)...
FROM holdem_hand_player_statistics, holdem_hand_player_detail, holdem_hand_summary, lookup_actions lookup_actions_p, (SELECT hhps.id_session as id_session, (case when(p.id_player_alias <> 0) then p.id_player_alias else p.id_player end) as id_player, p.player_name as str_player_name, p.player_name_search as str_player_name_search, p.val_icon as val_icon FROM player as p, holdem_hand_summary as hhs, holdem_hand_player_statistics as hhps WHERE ( p.id_site = 100 AND (p.player_name_search='x' OR p.player_name_search='xx' OR p.player_name_search='xxx' OR p.player_name_search='xxxx') ) AND p.id_player = hhps.id_player_real AND hhps.id_hand = hhs.id_hand AND hhs.hand_no = yyyyyyy) as session WHERE (holdem_hand_player_statistics.id_session = session.id_session) AND (holdem_hand_player_statistics.id_hand = holdem_hand_player_detail.id_hand AND holdem_hand_player_statistics.id_player = holdem_hand_player_detail.id_player) AND (holdem_hand_summary.id_hand = holdem_hand_player_statistics.id_hand) AND (session.id_player = holdem_hand_player_statistics.id_player) AND (holdem_hand_summary.id_hand = holdem_hand_player_detail.id_hand) AND (lookup_actions_p.id_action=holdem_hand_player_detail.id_action_p) AND ((holdem_hand_summary.cnt_players >= 3 AND holdem_hand_summary.cnt_players <= 10)AND (holdem_hand_player_statistics.date_played < '2011/03/30 00:12:03')) GROUP BY (session.str_player_name_search)

Now I see that table player or player_real really isn't in the FROM clause which explains why some of my special huds have been acting up (PT builds the query so that it won't work)... but anywhoo, the question remains: why is player.mydef still in the query after I changed the column definition to player_real.mydef?

[kraada]

And you'll notice that in the FROM statement it's FROM player as p - therefore when you query for player.column not p.column it's not going to work. Try using player_real and see if that works here.

[pasita]

Can't edit the query as that's PT3 generated ( when the replayer pops up ) from my column definition.

[kraada]

No but you can change the definition in the column.

[pasita]

Ok this may be getting a bit academic (other than understanding why a column from my old definition still gets queried) as it's clear my use of table player is incompatible with the HUD.

I already use player_real.mycol in the PT3 built column. But in the erroneous query it's still player.mycol (although a LOT of errors went away when I changed from player to player_real), which is what this is all about:)

[kraada]

How big is the database in question? I'm wondering if a cache rebuild will help here to get everything using only the new column definition.

[pasita]

-I've applied the stats, updated cache, rebuilt and reindexed and rebooted.... this error referencing table player instead of player_real still keeps popping up.
?

Been there, done that...

[kraada]

Well it looks like the error is because in the from portion of the query, we're referring to the player table as 'p' (you can see it says FROM player as p), but then in your column you refer to 'player'. I don't think the validator would take p.my_column_in_table_player though because it wouldn't pass validation. I'll have to think about it to see if I can come up with a way to either trick the validator or get the final result to refer to the right table . . .