PolarDB:Enable ORDER BY on non-selected columns in DISTINCT queries

Overview

PolarDB for PostgreSQL (Compatible with Oracle) supports the following Oracle SQL syntax when the polar_enable_distinct_orderby_new_column parameter is set to on.

SELECT DISTINCT NVL(a, 0)
FROM polar_dob_a
ORDER BY b
LIMIT 10;

Features

• Sorting by non-selected columns: allows the ORDER BY clause to reference columns not included in the SELECT list, such as column b in the preceding syntax.

• Oracle compatibility: supports Oracle SQL syntax, simplifying migration and adaptation.

Scenarios

• Scenarios where you need to sort deduplicated data by fields not included in the SELECT list, such as counting unique values and ordering them by business-related columns.

• Scenarios that require compatibility with Oracle query logic.

Limits

• This feature is supported only for PolarDB for PostgreSQL (Compatible with Oracle) 2.0 with revision version 2.0.14.17.33.0 or later.

  Note

  To view the version information of your cluster, go to the PolarDB console or execute the SHOW polardb_version; statement. If the revision version of your cluster does not meet the requirements, update it.

• The ORDER BY clause can reference columns not included in the SELECT list only when the polar_enable_distinct_orderby_new_column parameter is set to on.

• The sorting field must be an existing column in the table.

• Window functions or complex expressions cannot be used as sorting fields.

Usage notes

• Performance impacts

  • This feature may increase sorting overhead due to the need for temporary storage of values of non-selected columns.

  • For tables with large amounts of data, we recommend that you optimize the sorting columns by creating appropriate indexes.

• Compatibility warning

  If you execute the SQL syntax when the feature is disabled, an error ERROR: column "b" must appear in the GROUP BY clause or be used in an aggregate function will be returned.

Usage

Syntax

SELECT DISTINCT [column_expression]
FROM [table_name]
[WHERE conditions]
ORDER BY [new_column]
[LIMIT [offset,] row_count];

Parameters

Parameter configuration

PolarDB provides the polar_enable_distinct_orderby_new_column parameter to control whether the ORDER BY clause in DISTINCT queries can reference columns not listed in the SELECT list. Valid values:

• on: allows ORDER BY to reference columns not included in the SELECT clause in DISTINCT queries.

• off (default): does not allow ORDER BY to reference columns not included in the SELECT clause in DISTINCT queries.

The parameter can be configured to take effect at the global or session level:

• Enable globally: Set the polar_enable_distinct_orderby_new_column parameter to on in the PolarDB console.

• Enable for the current session: Execute the following statement via the command-line tool:

  SET polar_enable_distinct_orderby_new_column = on;

Example

1. Create a test table and insert test data into the table.

   CREATE TABLE test(a number, b number, c number);
   
   INSERT INTO test (a, b, c) VALUES
   (1, 2, 10.0),
   (2, 1, 20.0),
   (1, 2, 30.0),
   (3, 1, 40.0),
   (2, 4, 50.0);

2. Enable the syntax compatibility feature.

   SET polar_enable_distinct_orderby_new_column = on;

3. Perform queries by using the syntax.

   Basic usage

   SELECT DISTINCT nvl(a, 0) 
   FROM test 
   ORDER BY b 
   LIMIT 3;

   Sample result:

    a 
   ---
    2
    3
    1
   (3 rows)

   With WHERE condition

   SELECT DISTINCT nvl(b, 0)
   FROM test
   WHERE c = 50
   ORDER BY a
   LIMIT 3;

   Sample result:

    b 
   ---
    4
   (1 row)

   Multiple-field deduplication

   SELECT DISTINCT a, NVL(c, 0)
   FROM test
   ORDER BY b
   LIMIT 3;

   Sample result:

    a | nvl 
   ---+-----
    2 |  20
    3 |  40
    1 |  10
   (3 rows)