Function evaluation does not work for non-relational parameters in router planner
- Dominant language
- C
- Stars
- 12.8k
- Forks
- 794
- Avg merge
- 2d 14h
- Merged PRs (30d)
- 31
Description
Hit this issue while looking at https://github.com/citusdata/citus/issues/751.
Basically, router planner does not evaluate functions when constructing a worker query. This was not an issue before, but some power users may get into unexpected problems if their queries contain a function call that uses a instance state different between master and workers.
Repro steps.
say there is a table main_table(user_id int, ...)
Query
```sql
select * from main_table where substring('hello', 1, 1) = 'h';
```
gets translated to worker query after evaluating ```substring``` function into
```sql
SELECT user_id, ... FROM main_table_xxxxxx main_table WHERE true
```
However, when you write this query as
```sql
select * from main_table where substring('hello', 1, 1) = 'h' and user_id = 1;
```
The query becomes router plannable and sent to appropriate worker as
```sql
SELECT user_id, ... FROM public.main_table_xxxxxx main_table WHERE (("substring"('hello'::text, 1, 1) = 'h'::text) AND (user_id = 1))
```
We should be evaluating this query at master level.
This is causing a unexpected behavior when somebody writes a immutable function expecting a regclass argument. Consider a dummy function.
```sql
CREATE OR REPLACE FUNCTION someDummyFunction(regclass)
RETURNS text AS
$$
BEGIN
RETURN md5($1::text);
END;
$$ LANGUAGE 'plpgsql' IMMUTABLE;
```
The following query is a where true query and gets delivered to all shards after it is evaluated to true
```sql
select * from main_table where someDummyFunction('main_table') = md5('main_table');
```
However, the following is a where false query, it is correctly picked up by router planner since there is no shard to serve this query from. But, function inside the query is not evaluated and it gives error.
```sql
select * from main_table where someDummyFunction('main_table') = 'random value';
WARNING: relation "public.main_table" does not exist
ERROR: could not receive query results
```
Reason for this error is ```main_table``` does not exist at the target node, and ```regclass``` value is not available.
The same problem exists if the query is a real router query like
```sql
select * from main_table where someDummyFunction('main_table') = md5('main_table') and user_id = 1;
WARNING: relation "public.main_table" does not exist
WARNING: relation "public.main_table" does not exist
ERROR: could not receive query results
```
Now we have 2 WARNING messages coming from 2 replicas of the target table.
Side note: this behavior is observed when ```someDummyFunction``` is also created at workers. If it is not, we would get unknown function error.
Contributor guide
Assessment
This issue has not been assessed yet.