pgFormatter icon indicating copy to clipboard operation
pgFormatter copied to clipboard
verified publisher icon darold

Parenthesis error introduced on formatting

Open beperpg opened this issue 1 year ago • 1 comments

As per $title. I understand that during formatting some not needed parenthesis are cleaned up, but it broke this query for me. (It worked without a hiccup for hundreds of other queries, but I cannot figure out why it broke this one.)

The command used was: find ./ -name "*.sql" -print0 | xargs -0 -I pg_format {} -u 0 -B -o {}

Screenshot attached where the parenthesis was missing pairs (marked red): parenthesis_error

All suggestions are welcome. Thank you.

`-- original

select * FROM xxx WHERE ( timestamp_load >= '2024-09-19 00:00:00' AND timestamp_load < '2024-09-19 00:01:00' ) AND (event_action == 'accept') AND NOT ((source_ip != '' AND (toIPv4OrNull(source_ip) BETWEEN '192.168.125.0' AND '192.168.125.255')) OR destination_ip IN ('1.1.1.1') OR process_name IN ('PING')) AND ((((threat_threat_matches_destination_ip IS NOT NULL AND threat_threat_matches_destination_ip != '')) AND NOT ((destination_ip != '' AND (destination_ip IN ('127.0.0.1') OR toIPv4OrNull(destination_ip) BETWEEN '10.0.0.0' AND '10.255.255.255' OR toIPv4OrNull(destination_ip) BETWEEN '192.168.0.0' AND '192.168.255.255' OR toIPv4OrNull(destination_ip) BETWEEN '172.16.0.0' AND '172.31.255.255' OR toIPv6OrNull(destination_ip) BETWEEN 'fe80::' AND 'febf:ffff:ffff:ffff:ffff:ffff:ffff:ffff' OR toIPv6OrNull(destination_ip) BETWEEN '::1' AND '::1')))) OR (((threat_threat_matches_source_ip IS NOT NULL AND threat_threat_matches_source_ip != '')) AND NOT ((source_ip != '' AND (source_ip IN ('127.0.0.1') OR toIPv4OrNull(source_ip) BETWEEN '10.0.0.0' AND '10.255.255.255' OR toIPv4OrNull(source_ip) BETWEEN '192.168.0.0' AND '192.168.255.255' OR toIPv4OrNull(source_ip) BETWEEN '172.16.0.0' AND '172.31.255.255' OR toIPv6OrNull(source_ip) BETWEEN 'fe80::' AND 'febf:ffff:ffff:ffff:ffff:ffff:ffff:ffff' OR toIPv6OrNull(source_ip) BETWEEN '::1' AND '::1')))) OR (((threat_threat_matches_server_ip IS NOT NULL AND threat_threat_matches_server_ip != '' )) AND NOT ((server_ip != '' AND (server_ip IN ('127.0.0.1') OR toIPv4OrNull(server_ip) BETWEEN '10.0.0.0' AND '10.255.255.255' OR toIPv4OrNull(server_ip) BETWEEN '192.168.0.0' AND '192.168.255.255' OR toIPv4OrNull(server_ip) BETWEEN '172.16.0.0' AND '172.31.255.255' OR toIPv6OrNull(server_ip) BETWEEN 'fe80::' AND 'febf:ffff:ffff:ffff:ffff:ffff:ffff:ffff' OR toIPv6OrNull(server_ip) BETWEEN '::1' AND '::1')))) OR (((threat_threat_matches_client_ip IS NOT NULL AND threat_threat_matches_client_ip != '')) AND NOT ((client_ip != '' AND (client_ip IN ('127.0.0.1') OR toIPv4OrNull(client_ip) BETWEEN '10.0.0.0' AND '10.255.255.255' OR toIPv4OrNull(client_ip) BETWEEN '192.168.0.0' AND '192.168.255.255' OR toIPv4OrNull(client_ip) BETWEEN '172.16.0.0' AND '172.31.255.255' OR toIPv6OrNull(client_ip) BETWEEN 'fe80::' AND 'febf:ffff:ffff:ffff:ffff:ffff:ffff:ffff' OR toIPv6OrNull(client_ip) BETWEEN '::1' AND '::1')))) OR (((threat_threat_matches_p IS NOT NULL AND threat_threat_matches_p != '')) AND NOT ((threat_threat_matches_destination_ip IS NOT NULL AND threat_threat_matches_destination_ip != '') OR (threat_threat_matches_destination_ip_nat IS NOT NULL AND threat_threat_matches_destination_ip_nat != '') OR (threat_threat_matches_source_ip IS NOT NULL AND threat_threat_matches_source_ip != '') OR (threat_threat_matches_server_ip IS NOT NULL AND threat_threat_matches_server_ip != '') OR (threat_threat_matches_client_ip IS NOT NULL AND threat_threat_matches_client_ip != ''))))

-- formatted, parenthesis error introduced in line with word PING

select * FROM xxx WHERE (timestamp_load >= '2024-09-19 00:00:00' AND timestamp_load < '2024-09-19 00:01:00') AND (event_action == 'accept') AND NOT (source_ip != '' AND (toIPv4OrNull(source_ip) BETWEEN '192.168.125.0' AND '192.168.125.255')) OR destination_ip IN ('1.1.1.1') OR process_name IN ('PING')) AND ((((threat_threat_matches_destination_ip IS NOT NULL AND threat_threat_matches_destination_ip != '') AND NOT (destination_ip != '' AND (destination_ip IN ('127.0.0.1') OR toIPv4OrNull(destination_ip) BETWEEN '10.0.0.0' AND '10.255.255.255' OR toIPv4OrNull(destination_ip) BETWEEN '192.168.0.0' AND '192.168.255.255' OR toIPv4OrNull(destination_ip) BETWEEN '172.16.0.0' AND '172.31.255.255' OR toIPv6OrNull(destination_ip) BETWEEN 'fe80::' AND 'febf:ffff:ffff:ffff:ffff:ffff:ffff:ffff' OR toIPv6OrNull(destination_ip) BETWEEN '::1' AND '::1')))) OR (((threat_threat_matches_source_ip IS NOT NULL AND threat_threat_matches_source_ip != '') AND NOT (source_ip != '' AND (source_ip IN ('127.0.0.1') OR toIPv4OrNull(source_ip) BETWEEN '10.0.0.0' AND '10.255.255.255' OR toIPv4OrNull(source_ip) BETWEEN '192.168.0.0' AND '192.168.255.255' OR toIPv4OrNull(source_ip) BETWEEN '172.16.0.0' AND '172.31.255.255' OR toIPv6OrNull(source_ip) BETWEEN 'fe80::' AND 'febf:ffff:ffff:ffff:ffff:ffff:ffff:ffff' OR toIPv6OrNull(source_ip) BETWEEN '::1' AND '::1')))) OR (((threat_threat_matches_server_ip IS NOT NULL AND threat_threat_matches_server_ip != '') AND NOT (server_ip != '' AND (server_ip IN ('127.0.0.1') OR toIPv4OrNull(server_ip) BETWEEN '10.0.0.0' AND '10.255.255.255' OR toIPv4OrNull(server_ip) BETWEEN '192.168.0.0' AND '192.168.255.255' OR toIPv4OrNull(server_ip) BETWEEN '172.16.0.0' AND '172.31.255.255' OR toIPv6OrNull(server_ip) BETWEEN 'fe80::' AND 'febf:ffff:ffff:ffff:ffff:ffff:ffff:ffff' OR toIPv6OrNull(server_ip) BETWEEN '::1' AND '::1')))) OR (((threat_threat_matches_client_ip IS NOT NULL AND threat_threat_matches_client_ip != '') AND NOT (client_ip != '' AND (client_ip IN ('127.0.0.1') OR toIPv4OrNull(client_ip) BETWEEN '10.0.0.0' AND '10.255.255.255' OR toIPv4OrNull(client_ip) BETWEEN '192.168.0.0' AND '192.168.255.255' OR toIPv4OrNull(client_ip) BETWEEN '172.16.0.0' AND '172.31.255.255' OR toIPv6OrNull(client_ip) BETWEEN 'fe80::' AND 'febf:ffff:ffff:ffff:ffff:ffff:ffff:ffff' OR toIPv6OrNull(client_ip) BETWEEN '::1' AND '::1')))) OR (((threat_threat_matches_p IS NOT NULL AND threat_threat_matches_p != '') AND NOT ((threat_threat_matches_destination_ip IS NOT NULL AND threat_threat_matches_destination_ip != '') OR (threat_threat_matches_destination_ip_nat IS NOT NULL AND threat_threat_matches_destination_ip_nat != '') OR (threat_threat_matches_source_ip IS NOT NULL AND threat_threat_matches_source_ip != '') OR (threat_threat_matches_server_ip IS NOT NULL AND threat_threat_matches_server_ip != '') OR (threat_threat_matches_client_ip IS NOT NULL AND threat_threat_matches_client_ip != '')))) `

beperpg avatar Sep 20 '24 04:09 beperpg

update: I went back a few versions and 5.0 for example does not break the query above.

beperpg avatar Sep 23 '24 23:09 beperpg

I ran into the same issue with the following query:

SELECT
    ST_GeomFromText ('POLYHEDRALSURFACE Z (((12 0 10, 8 8 10, 8 10 20, 12 2 20, 12 0 10)), 
         ((8 8 10, 0 12 10, 0 14 20, 8 10 20, 8 8 10)), 
         ((0 12 10, -8 8 10, -8 10 20, 0 14 20, 0 12 10))
     )');

In this case the extra parentheses are required by PostGIS. I'm using the VSCode extension so I can't pinpoint the exact version that it stopped working but it should be somewhere between v5.2 and v5.5: https://github.com/bradymholt/psqlformat/compare/v1.16.0...v1.17.0.

placetobejohan avatar Jan 14 '25 14:01 placetobejohan

Version 5.6 adds a new option --redundant-parenthesis to disable automatic remove of redundant parenthesis.

darold avatar Mar 17 '25 13:03 darold

Version 5.6 adds a new option --redundant-parenthesis to disable automatic remove of redundant parenthesis.

Thank you! I'll try to update the VSCode extension to v5.6.

placetobejohan avatar Mar 21 '25 15:03 placetobejohan