Unbalanced Parens in SQL query from valid expression filter

Questions and discussion about PokerTracker 4 for Windows

Moderators: WhiteRider, kraada, Flag_Hippo, morny, Moderators

Unbalanced Parens in SQL query from valid expression filter

Postby benku » Sat Sep 17, 2022 2:33 am

I just wrote the expression filter
Code: Select all
flg_f_face_action AND flg_f_donk
and got an Error:

Code: Select all
Error: Unable to execute query: Fatal Error; Reason: ERROR:  syntax error at or near ")" LINE 1: ...ise > 0 OR cash_hand_player_statistics.flg_f_fold))   AND ((...


Looked like maybe an unbalanced paren problem so I copy/pasted the query from the log into VSCode, ran a linter, and yup, extra closing paren. I'm including the query from the logs at the end of this.

I also tried swapping the order of the arguments around but got same behavior/error:

Code: Select all
flg_f_donk AND flg_f_face_action


Replacing AND with OR behaves as expected and no error is reported.

Is this a known bug? Is there a workaround? Thanks!

Code: Select all
SELECT (cash_hand_summary.id_hand) as "id_hand", (cash_hand_summary.id_site) as "id_site", (cash_hand_summary.hand_no) as "hand_no", (cash_hand_summary.id_gametype) as "id_gametype_summary", (cash_hand_summary.flg_note) as "flg_note", (cash_hand_summary.flg_tag) as "flg_tag", (timezone('UTC',  cash_hand_player_statistics.date_played  + INTERVAL '0 HOURS')) as "date_played", (cash_hand_player_statistics.id_limit) as "id_limit", ((case when( cash_hand_player_statistics.val_curr_conv != 0) then  cash_hand_player_statistics.val_curr_conv*cash_hand_player_statistics.amt_won else  0.0  end)) as "amt_won_curr_conv", ((case when( cash_hand_player_statistics.val_curr_conv != 0) then  cash_hand_player_statistics.val_curr_conv*cash_hand_player_statistics.amt_expected_won else  0.0 end)) as "amt_expected_won_curr_conv", ( (CASE WHEN ( cash_limit.amt_bb) <> 0 THEN ((cash_hand_player_statistics.amt_won * 1.0 )/( cash_limit.amt_bb)) ELSE 0 END) ) as "amt_bb_won", (cash_hand_summary.amt_pot) as "amt_pot", (cash_limit.limit_currency) as "limit_currency", (cash_hand_player_statistics.id_final_hand) as "id_final_hand", (cash_hand_player_statistics.flg_showed) as "flg_showed", (cash_hand_player_statistics.enum_folded) as "enum_folded", (cash_hand_player_statistics.holecard_1) as "id_holecard1", (cash_hand_player_statistics.holecard_2) as "id_holecard2", (cash_hand_player_statistics.holecard_3) as "id_holecard3", (cash_hand_player_statistics.holecard_4) as "id_holecard4", (lookup_actions_p.action) as "str_actions_p", (cash_hand_summary.card_1) as "id_flop1", (cash_hand_summary.card_2) as "id_flop2", (cash_hand_summary.card_3) as "id_flop3", (lookup_actions_f.action) as "str_actions_f", (cash_hand_summary.card_4) as "id_turn", (lookup_actions_t.action) as "str_actions_t", (cash_hand_summary.card_5) as "id_river", (lookup_actions_r.action) as "str_actions_r", (player_winner.player_name) as "str_winner", (cash_hand_summary.id_win_hand) as "id_win_hand", (cash_hand_summary.amt_rake) as "amt_rake", (cash_hand_summary.amt_rake * ( (CASE WHEN ( ((cash_hand_summary.amt_pot) - cash_hand_summary.amt_rake - cash_hand_summary.amt_jackpot_fee)) <> 0 THEN (( (cash_hand_player_statistics.amt_won + (cash_hand_player_statistics.amt_bet_ttl)) * 1.0 )/( ((cash_hand_summary.amt_pot) - cash_hand_summary.amt_rake - cash_hand_summary.amt_jackpot_fee))) ELSE 0 END) )) as "amt_rake_player", (cash_hand_player_statistics.amt_bet_ttl) as "amt_bet_ttl", ((case when( cash_hand_player_statistics.val_curr_conv != 0) then  cash_hand_player_statistics.val_curr_conv*cash_hand_summary.amt_rake else  0.0 end)) as "amt_rake_curr_conv", ((case when(cash_hand_player_statistics.amt_bet_ttl > 0) then  (case when(cash_hand_player_statistics.val_curr_conv != 0) then  cash_hand_player_statistics.val_curr_conv * ( (CASE WHEN ( cash_hand_summary.amt_pot) <> 0 THEN ((cash_hand_player_statistics.amt_bet_ttl * 1.0 )/( cash_hand_summary.amt_pot)) ELSE 0 END) ) * cash_hand_summary.amt_rake else  0.0 end) else  0 end)) as "amt_rake_weighted_contributed_curr_conv", ((case when(cash_hand_player_statistics.val_curr_conv != 0) then  cash_hand_player_statistics.val_curr_conv * cash_hand_summary.amt_mgr else  0.0 end)) as "amt_rake_share_curr_conv", ((case when( cash_hand_player_statistics.val_curr_conv != 0) then  cash_hand_player_statistics.val_curr_conv*cash_hand_summary.amt_rake * ( (CASE WHEN ( ((cash_hand_summary.amt_pot) - cash_hand_summary.amt_rake - cash_hand_summary.amt_jackpot_fee)) <> 0 THEN (( (cash_hand_player_statistics.amt_won + (cash_hand_player_statistics.amt_bet_ttl)) * 1.0 )/( ((cash_hand_summary.amt_pot) - cash_hand_summary.amt_rake - cash_hand_summary.amt_jackpot_fee))) ELSE 0 END) ) else  0.0 end)) as "amt_rake_player_curr_conv", (cash_hand_player_statistics.flg_f_donk) as "flg_f_donk", (cash_hand_player_statistics.cnt_f_call > 0 OR cash_hand_player_statistics.cnt_f_raise > 0 OR cash_hand_player_statistics.flg_f_fold) as "flg_f_face_action" FROM   cash_hand_player_statistics , cash_hand_summary, lookup_actions lookup_actions_p, lookup_actions lookup_actions_f, lookup_actions lookup_actions_t, lookup_actions lookup_actions_r, player player_winner, cash_limit WHERE  (cash_hand_summary.id_hand = cash_hand_player_statistics.id_hand  AND cash_hand_summary.id_limit = cash_hand_player_statistics.id_limit)  AND (lookup_actions_p.id_action=cash_hand_player_statistics.id_action_p)  AND (lookup_actions_f.id_action=cash_hand_player_statistics.id_action_f)  AND (lookup_actions_t.id_action=cash_hand_player_statistics.id_action_t)  AND (lookup_actions_r.id_action=cash_hand_player_statistics.id_action_r)  AND (cash_limit.id_limit = cash_hand_player_statistics.id_limit)  AND (player_winner.id_player = cash_hand_summary.id_winner)  AND (cash_limit.id_limit = cash_hand_summary.id_limit)    AND (cash_hand_player_statistics.cnt_f_call > 0 OR cash_hand_player_statistics.cnt_f_raise > 0 OR cash_hand_player_statistics.flg_f_fold))   AND (((1=1))AND ((cash_hand_player_statistics.id_limit in ((SELECT id_limit FROM cash_limit WHERE limit_name=E'$0.25 NL (6 max)')) ))) ORDER BY (timezone('UTC',  cash_hand_player_statistics.date_played  + INTERVAL '0 HOURS')) desc LIMIT 100
benku
 
Posts: 10
Joined: Mon Aug 17, 2020 12:10 am

Re: Unbalanced Parens in SQL query from valid expression fil

Postby benku » Sat Sep 17, 2022 2:35 am

Okay, there is the obvious work around of
Code: Select all
(NOT (NOT flg_f_face_action) OR (NOT flg_f_donk))

(which works btw)
benku
 
Posts: 10
Joined: Mon Aug 17, 2020 12:10 am

Re: Unbalanced Parens in SQL query from valid expression fil

Postby benku » Sat Sep 17, 2022 3:02 am

sorry typo, i've been writing a lot of lisp lately so i wrote an s-expression. The correct version is:

Code: Select all
NOT ((NOT flg_f_flop) OR (NOT flg_f_face_action))


I'm assuming NOT binds to the first clause in first version I posted and we want it applied to the entire OR expression
benku
 
Posts: 10
Joined: Mon Aug 17, 2020 12:10 am

Re: Unbalanced Parens in SQL query from valid expression fil

Postby WhiteRider » Sat Sep 17, 2022 3:25 am

I'm not sure why this matters, but if you use lowercase "and" instead of uppercase "AND" that will stop the error.
However, you should use the full database field names (instead of column names from statistics) in filters like this:

Code: Select all
cash_hand_player_statistics.flg_f_donk and cash_hand_player_statistics.flg_f_face_raise


(Notice that the database field you need for this situation is "face raise", not "face action", as there there is no single database field to specify "face action" and after making a bet any action faced will always be a raise.)
WhiteRider
Moderator
 
Posts: 53961
Joined: Sat Jan 19, 2008 7:06 pm
Location: UK

Re: Unbalanced Parens in SQL query from valid expression fil

Postby benku » Sat Sep 17, 2022 2:58 pm

Oh interesting, thanks. Is there a place where I can report a bug? seems like the case thing has gotta be a typo somewhere in a parser or something?

you should use the full database field names (instead of column names from statistics) in filters like this


Oh cool, thanks for the heads up! Why is this? I was using the Insert feature for field/statistic discovery. What are those entries? They don't appear to be in the DB schema, so I'm assuming they are expanded to an sql query? I also noticed that the available insertable entries change depending on the report type which was a little confusing. Is there a good writeup anywhere that explains this? I didn't see anything in tutorials.

Thanks again!
benku
 
Posts: 10
Joined: Mon Aug 17, 2020 12:10 am

Re: Unbalanced Parens in SQL query from valid expression fil

Postby WhiteRider » Sat Sep 17, 2022 4:21 pm

We have a report of the case issue so you don't need to report it, but if you want to receive a notification if/when it is fixed you can open a Support Ticket about the issue and request to be notified.

If you are using a Hand report then it won't matter too much if you use the simple column names instead, but for player reports using the database fields directly will give more consistent results.
The different report types use different data, which is why the insertable options change. Hand data is quite different to Player data because Player data is the totals across all hands where Hand data is for just one hand.
Those longer names are the full database table and field names. The short versions would refer to a "Column" in PT4's statistics system (which are built from the database tables, but with the way that reports and statistics interact using the database fields directly is better).
I don't think there is a full write-up of expression filters but you may find some examples by searching for 'expression filter' in these forums. In simple terms, the expressions used here are very much like those you would use in the Columns used by a custom statistic.
WhiteRider
Moderator
 
Posts: 53961
Joined: Sat Jan 19, 2008 7:06 pm
Location: UK


Return to PokerTracker 4

Who is online

Users browsing this forum: No registered users and 61 guests

cron
highfalutin