Unbalanced Parens in SQL query from valid expression filter

[benku]

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]

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]

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

[WhiteRider]

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

[benku]

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!

[WhiteRider]

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.