Multiple instances of pg_typeof inside a GROUP BY clause result in a bogus "column does not exist" error

[antiguru]

What version of Materialize are you using?

materialized v0.9.5-dev 

How did you install Materialize?

Built from source

What was the issue?

materialize=> SELECT pg_typeof(operator), pg_typeof(worker), pg_typeof(count) FROM mz_arrangement_sharing;
Time: 5.231 ms
materialize=> SELECT pg_typeof(operator), pg_typeof(worker), pg_typeof(count) FROM mz_arrangement_sharing group by 1, 2, 3;
ERROR:  column "worker" does not exist
Time: 1.009 ms

[philip-stoev]

This issue is not specific to using GROUP BY with column numbers, it also happens with actual expressions in the GROUP BY column.

However, it seems to require the following to reproduce:

• the use of the pg_typeof function
• the GROUP BY should have two or more instances of that function.

materialize=> create table t1 (f1 integer, f2 integer);
CREATE TABLE

This fails:

materialize=> select pg_typeof(f1), pg_typeof(f2) from t1 group by pg_typeof(f1), pg_typeof(f2);
ERROR:  column "f2" does not exist

But all of those work:

1. One instance of pg_typeof works:

materialize=> select pg_typeof(f1), abs(f2) from t1 group by pg_typeof(f1), abs(f2);
 pg_typeof | abs 
-----------+-----
(0 rows)

materialize=> select abs(f1), pg_typeof(f2) from t1 group by abs(f1), pg_typeof(f2);
 abs | pg_typeof 
-----+-----------
(0 rows)

2. Use of other functions apart from pg_typeof also works:

materialize=> select abs(f1), abs(f2) from t1 group by abs(f1), abs(f2);
 abs | abs 
-----+-----
(0 rows)

[umanwizard]

@pH14 is taking a look

[pH14]

What I'm seeing so far --

When we're computing the GROUP BY plan, we intentionally drop repeated expressions:

materialize/src/sql/src/plan/query.rs

Lines 1638 to 1647 in cf095ba

	for group_expr in group_by {
	let (group_expr, expr) = plan_group_by_expr(ecx, group_expr, &projection)?;
	let new_column = group_key.len();
	// Repeated expressions in GROUP BY confuse name resolution later,
	// and dropping them doesn't change the result.
	if group_exprs
	.iter()
	.find(|existing_expr| **existing_expr == expr)
	.is_none()
	{

This causes us to omit generating and storing the ScopeItem for all but the first pg_typeof expression, later causing an error when we try to resolve the expression for each projection. The first lookup succeeds, but any subsequent one will fail and error out, since they have no corresponding expression:

materialize/src/sql/src/plan/query.rs

Lines 1745 to 1747 in cf095ba

	ExpandedSelectItem::Expr(expr) => plan_expr(ecx, &expr)
	.map_err(check_ungrouped_col)?
	.type_as_any(ecx)?,

The difference in behavior between pg_typeof and other functions like abs comes from the expression types they generate. pg_typeof becomes a Literal: Literal(Row{[String("bigint")]}, ColumnType { scalar_type: String, nullable: false }), whereas an abs becomes a function call CallUnary { func: AbsInt32(AbsInt32), expr: Column(ColumnRef { level: 0, column: 0 }) } that contains a column reference. The column references are different for each column, so they aren't deduped, and therefore the projection <-> expr mapping works as intended.

I tried a few quick naive fixes here, like removing the dedupping entirely, but those ran amuck of several assumptions about the output from the current GROUP BY handling. Will revisit this one next week

[umanwizard]

So, the issue is that pg_typeof(...) gets planned as a constant here , so repeated pg_typeof are considered the same expression, even though they don't have the same name...

Does this happen when grouping by any constant expressions multiple times?

[umanwizard]

(I'm not actually sure how to construct another example of expressions in SQL that are planned as constants and have the same constant value but different names.)

[umanwizard]

Okay list_ndims also has this property (requires experimental mode):

materialize=> select list_ndims(column2) from (values (list[1], list[2])) group by list_ndims(column1), list_ndims(column2);
ERROR:  column "column2" must appear in the GROUP BY clause or be used in an aggregate function