implemented query hints (flatten) (fb-2124)

[paddyjok]

This PR implements query options hints that govern behavior for distinct and group by on set columns.

1. introduces syntax to support query hints on table source references ...from table_name with (hint(param))
2. introduces the flatten(column) hint
3. ensures distinct with and without flatten with a single set column ref works correctly
4. ensures group by with and without flatten with a single set column ref works correctly

[seebs]

looking at the CI failures, they look intentional in that we changed the semantics of group by. e.g., we would now need:

--- a/sql3/test/defs/defs_groupby.go
+++ b/sql3/test/defs/defs_groupby.go
@@ -225,7 +225,7 @@ var groupByTests = TableTest{
                },
                {
                        SQLs: sqls(
-                               "SELECT COUNT(*), is1 FROM groupby_test group by is1",
+                               "SELECT COUNT(*), is1 FROM groupby_test with(flatten(is1)) group by is1",
                        ),
                        ExpHdrs: hdrs(
                                hdr("", fldTypeInt),

[seebs]

We do need to fix the existing tests, because we've fixed group by semantics to match SQL semantics by default, which is intentional, but it does mean the existing tests will need updates. I'm fine with either direction of changing that (changing expected test results or adding the with(flatten(...)) to them).

[sonarcloud[bot]]

Kudos, SonarCloud Quality Gate passed!   

0 Bugs
0 Vulnerabilities
0 Security Hotspots
2 Code Smells

No Coverage information
8.2% Duplication