[YSQL] Unexpected SELECT DISTINCT Behaviour with mixed primary key, non-primary key clauses or aggregate targets.

[vbalvida]

Jira Link: DB-6769

Description

We fail to retrieve certain rows with SELECT DISTINCT because we push the uniqueness constraint to lower layers without also pushing the required filters or aggregation functions past the de-duplication operator. Below we present one such example. See the comments for more such examples.

Example

Setup

CREATE TABLE t1(r1 INT, r2 INT, s INT, PRIMARY KEY(r1 ASC, r2 ASC));
INSERT INTO t1 (SELECT 1, i, i FROM GENERATE_SERIES(1, 1000) AS i);

Distinct Query

/*+Set(enable_hashagg false)*/ SELECT DISTINCT r1 FROM t1 WHERE s = 500 AND r1 <= 1000;

The expected behavior is to return one row with the value 1, but the current behavior does not yield any rows.

To further elaborate the issue, lets consider two more scenarios,

No clause on r1

/*+Set(enable_hashagg false)*/ SELECT DISTINCT r1 FROM t1 WHERE s = 1000;

This query returns the expected row since we do not use scan choices in this scenario.

No clause on s

/*+Set(enable_hashagg false)*/ SELECT DISTINCT r1 FROM t1 WHERE r1 <= 500;

This query returns the expected row since the scan choices module is aware of all the bounds.

However, scan choices is not aware of where clauses on the non-primary keys. Hence, in combination, the query behaves unexpectedly.

We just discussed an example involving a filter pushed down to the DocDB layer. There are more examples in comments where further processing on the postgres side makes it inaccurate to push down the uniqueness constraint to DocDB without also pushing down the surrounding constraints/functions.

Warning: Please confirm that this issue does not contain any sensitive information

• I confirm this issue does not contain any sensitive information.

[vbalvida]

Found another example that fails to return the correct results

With the following setup,

CREATE TABLE t2 (r1 INT, r2 INT, s timestamp, PRIMARY KEY(r1 ASC, r2 ASC));
INSERT INTO t2 VALUES (1, 1, NOW()), (1, 2, NOW() + INTERVAL '1 day');

the query

/*+Set(enable_hashagg false)*/ SELECT DISTINCT r1 FROM t2 WHERE s > now() AND r1 <= 1000;

Returns no rows when the query

/*+Set(enable_hashagg false)*/ SELECT r1 FROM t2 WHERE s > now() AND r1 <= 1000;

returns 1 row corresponding to the second tuple in the original table.

This particular query differs from the one present in the issue in that the filtering happens on the postgres side and not all filter clauses are pushed down to DocDB.

The above behavior has an notable implication. Avoiding pushing down DISTINCT past any filters (both on the DocDB side and the postgres side) is non-trivial. Instead, we approach the bug by being very apprehensive about when to use the prefix logic. This is not optimal and hence created #17741 for improvements increasing the applicability of prefix based skipping.

[vbalvida]

Found yet another interesting query that behaves unexpectedly

Setup

CREATE TABLE t(r1 INT, r2 INT, PRIMARY KEY(r1 ASC, r2 ASC));
INSERT INTO t (SELECT 1, i FROM GENERATE_SERIES(1, 1000) AS i);
SET yb_debug_log_docdb_requests TO true;

Query

/*+Set(enable_hashagg false)*/ SELECT DISTINCT COUNT(tableoid) FROM t WHERE r1 <= 1000 GROUP BY r1;

Returned result

 count 
-------
     1
(1 row)

Postgres result

 count
-------
  1000
(1 row)

DocDB log

{ READ active: 1 read_time: { read: <invalid> local_limit: <invalid> global_limit: <invalid> in_txn_limit: <invalid> serial_no: 0 } request: client: YQL_CLIENT_PGSQL stmt_id: 4698329376 schema_version: 0 targets { column_id: 10 } targets { column_id: -8 } column_refs { ids: 10 } is_forward_scan: 1 is_aggregate: 0 limit: 1024 return_paging_state: 1 ysql_catalog_version: 1 table_id: "000033f5000030008000000000004000" condition_expr { condition { op: QL_OP_AND operands { condition { op: QL_OP_LESS_THAN_EQUAL operands { column_id: 10 } operands { value { int32_value: 1000 } } } } } } upper_bound { key: "48800003E87E007E21" is_inclusive: 1 } col_refs { column_id: 10 attno: 1 } partition_key: "$~!" prefix_length: 1 size_limit: 0 }

Observe the length of the prefix. The prefix must encompass both columns of the row because the query involves an aggregation.

Additional Caveat

This issue manifests when we request a system column and not a regular column which might sound surprising. The truth is that the postgres optimization layer requests the complete tuple in situations where projecting the tuples is more expensive for the executor. This doesn't happen for system columns. More importantly, the optimization to return the whole tuple may not be accurate in our case since we transfer tuples over the network. Hence, the optimization maybe disabled in the future.

Conclusion

Ensuring that pushdown works with all aspects of SELECT DISTINCT is non-trivial because of the complexity of postgres grammar. Created #17801 for emergency scenarios for when we need to disable this particular feature.