SQL: Remaining "no corresponding grouping element for non-aggregate" errors

[philrz]

The fix in #6388 addressed most of the ~176k incidents of the "no corresponding grouping element for non-aggregate" errors from sqllogictests originally tracked in #5973. However, now that a full re-run of all the sqllogictests has been completed with the benefit of that fix there's still 979 queries that produce that error. It's unclear at first glance if these are just corner cases that can be addressed with the same approach used in #6388 or if they present a unique challenge.

Details

Repro is with super commit a814e1b.

The attached bundle groupby_slt_good_8_repro.tgz contains 75 such queries from the 8th "random groupby" sqllogictest set along with test data. As with all the "random" queries, these are "fuzzy" in nature and therefore look unrealistic in some ways. Just run the following and it will show you each query producing the error:

$ super -version
Version: a814e1b66

$ sh repro.sh 
no corresponding grouping element for non-aggregate "+tab0.col2" at line 1, column 8:
SELECT + tab0.col2, tab0.col2 FROM tab0 GROUP BY col2;
       ~~~~~~~~~~~
no corresponding grouping element for non-aggregate "col2_1" at line 1, column 12:
SELECT ALL col2, col2 AS col2 FROM tab2 GROUP BY col2;
           ~~~~
...

There's plenty of repetition in the style of these queries so I won't enumerate them all. But to my eyes, these first two actually seem representative of what may be two buckets of queries:

Simple math putting + / - before column names

That first one shown above is an example of this, and I'm optimistic that it may indeed just be a variation of the kind of queries shown in the original #5973 repro. For example, DuckDB happens to give the +-led column a unique name in the output.

$ duckdb -c "SELECT + tab0.col2, tab0.col2 FROM tab0.parquet GROUP BY col2;"
┌──────────────┬───────┐
│ +(tab0.col2) │ col2  │
│    int64     │ int64 │
├──────────────┼───────┤
│           24 │    24 │
│           38 │    38 │
│           79 │    79 │
└──────────────┴───────┘

What Would be Duplicate Columns

The second one shown above seems like it falls into this bucket, evidenced by the trailing _1 in the error message. Indeed, DuckDB uses the same column name twice in the output, and I know this already presents a challenge for us (#5977). I suspect that if we let the _1 column appear in the output that would be ok (at least for now) but it would be an improvement if we didn't reject the query.

$ duckdb -c "SELECT ALL col2, col2 AS col2 FROM tab2.parquet GROUP BY col2;"
┌───────┬───────┐
│ col2  │ col2  │
│ int64 │ int64 │
├───────┼───────┤
│    58 │    58 │
│    79 │    79 │
│    87 │    87 │
└───────┴───────┘

[mattnibs]

I took a look at this and this one is blocked by the work to switch the scoping precedence in select statements where in is matched against first then out.

[philrz]

Verified in super commit 517f23d.

Per the changes in #6495, by adding the pragma pg=true as in the attached repro-pragma.sh, now all these queries run successfully without triggering the error message we saw previously.

$ super -version
Version: 517f23d7c

$ sh repro-pragma.sh 
{"+tab0.col2":38,col2:38}
{"+tab0.col2":79,col2:79}
{"+tab0.col2":24,col2:24}
{col2:87,col2_1:87}
{col2:79,col2_1:79}
{col2:58,col2_1:58}
{"+col2":8,col2:8}
{"+col2":45,col2:45}
{"+col2":71,col2:71}
{"-col2":-87,col2:-87}
{"-col2":-79,col2:-79}
{"-col2":-58,col2:-58}
{col0:-22,col1:22}
{col0:-28,col1:28}
{col0:-82,col1:82}
{col1:61,col1_1:61}
{col1:59,col1_1:59}
{col1:41,col1_1:41}
{col0:22,col0_1:22}
{col0:28,col0_1:28}
{col0:82,col0_1:82}
{"-col2":-8,col2:8}
{"-col2":-45,col2:45}
{"-col2":-71,col2:71}
{"-col2":-45,col2:45}
{"-col2":-71,col2:71}
{"-col2":-8,col2:8}
{"-col1":-6,col1:6}
{"-col1":-57,col1:57}
{"-col1":-44,col1:44}
{col1:61,col1_1:61}
{col1:59,col1_1:59}
{col1:41,col1_1:41}
{col0:22,col0_1:22}
{col0:28,col0_1:28}
{col0:82,col0_1:82}
{"--col2":87,col2:87}
{"--col2":79,col2:79}
{"--col2":58,col2:58}
{"+col0":82,col0:82}
{"+col0":22,col0:22}
{"+col0":28,col0:28}
{col1:87,col2:87}
{col1:79,col2:79}
{col1:58,col2:58}
{col0:83,col1:83}
{col0:26,col1:26}
{col0:43,col1:43}
{col1:0,col0:0}
{col1:81,col0:-6561}
{"-col1":-6,col1:6}
{"-col1":-57,col1:57}
{"-col1":-44,col1:44}
{col2:8,"+col2":8}
{col2:45,"+col2":45}
{col2:71,"+col2":71}
{col1:-15,col0:15}
{col1:-91,col0:91}
{col1:-92,col0:92}
{col0:22,"-col0*+-col0":484}
{col0:28,"-col0*+-col0":784}
{col0:82,"-col0*+-col0":6724}
{col0:22,"-col0":-22}
{col0:28,"-col0":-28}
{col0:82,"-col0":-82}
{col0:26,col0_1:26}
{col0:43,col0_1:43}
{col0:83,col0_1:83}
{col0:83,col1:-166}
{col0:26,col1:-52}
{col0:43,col1:-86}
{col1:15,col0:15}
{col1:91,col0:91}
{col1:92,col0:92}
{col1:0,col0:0}
{col1:-81,col0:81}
{col1:83,"-col1":0}
{col1:26,"-col1":0}
{col1:43,"-col1":-81}
{col0:15,col1:-225}
{col0:91,col1:-8281}
{col0:92,col1:-8464}
{col0:43,"col0*col0":1849}
{col0:83,"col0*col0":6889}
{col0:26,"col0*col0":676}
{"+col0":91,col0:91}
{"+col0":92,col0:92}
{"+col0":15,col0:15}
{col2:26,col0:26}
{col2:43,col0:43}
{col2:83,col0:83}
{col2:-6972,col0:83}
{col2:-702,col0:26}
{col2:-1892,col0:43}
{col0:-83,col0_1:83}
{col0:-26,col0_1:26}
{col0:-43,col0_1:43}
{col1:6,col1_1:6}
{col1:57,col1_1:57}
{col1:44,col1_1:44}
{col1:0,"+col1":0}
{col1:81,"+col1":81}
{col2:8,"-col2/--col2":-1}
{col2:45,"-col2/--col2":-1}
{col2:71,"-col2/--col2":-1}
{col1:61,col1_1:61}
{col1:59,col1_1:59}
{col1:41,col1_1:41}
{col1:61,"+col1":61}
{col1:59,"+col1":59}
{col1:41,"+col1":41}
{col0:22,col0_1:22}
{col0:28,col0_1:28}
{col0:82,col0_1:82}
{col1:0,"-col1":0}
{col1:81,"-col1":-81}
{col2:-12,col1:6}
{col2:-114,col1:57}
{col2:-88,col1:44}
{"-col0":-82,col0:82}
{"-col0":-22,col0:22}
{"-col0":-28,col0:28}
{col0:22,col0_1:22}
{col0:28,col0_1:28}
{col0:82,col0_1:82}
{"-+col0":-26,col0:26}
{"-+col0":-43,col0:43}
{"-+col0":-83,col0:83}
{col2:87,col2_1:87}
{col2:79,col2_1:79}
{col2:58,col2_1:58}
{col0:-6,col1:6}
{col0:-57,col1:57}
{col0:-44,col1:44}
{"+col1":0,col1:0}
{"+col1":81,col1:81}
{col0:22,col0_1:22}
{col0:28,col0_1:28}
{col0:82,col0_1:82}
{col2:43,col0:43}
{col2:83,col0:83}
{col2:26,col0:26}
{col2:61,col1:61}
{col2:59,col1:59}
{col2:41,col1:41}
{col1:24,col2:24}
{col1:38,col2:38}
{col1:79,col2:79}
{col1:6,col1_1:6}
{col1:57,col1_1:57}
{col1:44,col1_1:44}
{col1:6,"+col1":6}
{col1:57,"+col1":57}
{col1:44,"+col1":44}
{"+-col0":-91,col0:91}
{"+-col0":-92,col0:92}
{"+-col0":-15,col0:15}
{col0:-8,col2:8}
{col0:-45,col2:45}
{col0:-71,col2:71}
{col2:87,col0:87}
{col2:79,col0:79}
{col2:58,col0:58}
{col2:79,col0:79}
{col2:24,col0:24}
{col2:38,col0:38}
{col2:59,col1:59}
{col2:41,col1:41}
{col2:61,col1:61}
{"-col2":-38,col2:-38}
{"-col2":-79,col2:-79}
{"-col2":-24,col2:-24}
{col0:-83,col2:-83}
{col0:-26,col2:-26}
{col0:-43,col2:-43}
{col1:81,"col1*col1++col1++col1":6723}
{col1:0,"col1*col1++col1++col1":0}
{col2:22,col0:22}
{col2:28,col0:28}
{col2:82,col0:82}
{col1:0,col1_1:0}
{col1:81,col1_1:81}
{col2:38,col2_1:38}
{col2:79,col2_1:79}
{col2:24,col2_1:24}
{col1:-1444,col2:-38}
{col1:-6241,col2:-79}
{col1:-576,col2:-24}
{col1:6,col1_1:6}
{col1:57,col1_1:57}
{col1:44,col1_1:44}
{col1:-83,col0:83}
{col1:-26,col0:26}
{col1:-43,col0:43}
{col0:-8,col2:-8}
{col0:-45,col2:-45}
{col0:-71,col2:-71}
{col1:90,col0:57}
{col1:142,col0:44}
{col1:16,col0:6}
{col2:8,col0:-8}
{col2:45,col0:-45}
{col2:71,col0:-71}

The subject of whether all these results match the ones from Postgres will be confirmed with a general re-run of the sqllogictests, but we've addressed the problem that motivated the opening of this issue so I'm closing it.

Thanks @mccanne!