Fix for Postgres regex and like binary operators

[solontsev]

Closes #1776

Currently, when parsing expressions with regex and like operators, only literals are supported, while the following syntax is also valid (matching against an array of values using ALL or ANY):

... where a ~ any(array['x'])

or

select a ~ any(array['x'])

[iffyio]

I'm not sure I fully understood the fix, is the issue specific to the ALL and ANY functions? the current test doesn't seem to make changes to the list of operators being tested so that its not clear to me why this test fails without the fix in this PR

[solontsev]

Currently, when parsing expressions with regex and like operators, only literals are supported, while syntax like where a ~ any(array['x']) is also valid (matching against an array of values using ALL or ANY). I've added additional more complex expressions to the tests (fails with the current version) + allowed 8 more operators before the ALL and ANY functions in the parse_infix function.

datafusion-sqlparser-rs/src/parser/mod.rs

Lines 3481 to 3488 in 60acdc5

	| BinaryOperator::PGRegexMatch
	| BinaryOperator::PGRegexIMatch
	| BinaryOperator::PGRegexNotMatch
	| BinaryOperator::PGRegexNotIMatch
	| BinaryOperator::PGLikeMatch
	| BinaryOperator::PGILikeMatch
	| BinaryOperator::PGNotLikeMatch
	| BinaryOperator::PGNotILikeMatch

[iffyio]

• allowed 8 more operators before the ALL and ANY functions in the parse_infix function.

Are the added operators covered by the tests? if not could we add tests covering them?

[solontsev]

Are the added operators covered by the tests? if not could we add tests covering them?

Yeah, I've added the tests as well. Maybe it was not the best to test 2 cases during each cycle iteration, I've modified it a bit: a previous match against a single value, and added one more to test against an array of values. Does it look better now?

Also, to wrap up: The problem now, is that before ANY, ALL or SOME functions, parser allows only 6 basic binary operators (gt, lt, gteq, lteq, eq, noteq). But those 8 additional are also valid. So the fix basically just adds them to the allowlist and tests for match and like operators are modified to make sure, parser doesn't return an error in a valid cases. I haven't added exhaustive test cases, just chose ANY function for match tests and ALL function for like tests.

[iffyio]

LGTM! Thanks @solontsev!
cc @alamb