ORDER BY/GROUP BY Scenario Expansion

Availability

This feature is available since MogDB 5.0.6.

Introduction

This feature expands the scenarios supported by the query statement ORDER BY/GROUP BY clauses, making them compatible with some Oracle functionalities.

Benefits

Enhances compatibility with Oracle, reduces the migration cost for applications, and improves product usability.

Description

ORDER BY Scenario Compatibility

• Compatible with Oracle's use of ORDER BY, supporting data de-duplication through the DISTINCT keyword.
• Supports using numbers in ORDER BY to represent projection columns.
• Supports using constant strings in ORDER BY, which have no practical significance and do not affect the sorting result.
• Supports ORDER BY with multiple columns, expressions, and aggregate functions.

GROUP BY Scenario Compatibility

• Supports containing an empty string in GROUP BY.
• Supports using integer constants in GROUP BY, which have no practical significance and do not affect the aggregation result.
• Supports using strings and expressions in GROUP BY, which have no practical significance and do not affect the sorting or aggregation results.

The GUC parameter behavior_compat_options has a new option compat_sort_group_column, which is used to control the behavior of GROUP/ORDER BY. By default, the behavior is consistent with PG. After setting this parameter, the behavior is consistent with Oracle, and constants no longer affect the GROUP/ORDER BY result set. This parameter only takes effect when the value of the sql_compatibility parameter is A.

Constraints

Only supports A compatibility mode.

Example

MogDB=# CREATE TABLE t1(aid INT);
CREATE TABLE
MogDB=# CREATE TABLE t2(bid INT);
CREATE TABLE

MogDB=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT DISTINCT aid FROM t1 JOIN t2 ON aid = bid ORDER BY t1.aid;
                  QUERY PLAN
-----------------------------------------------
 Sort
   Output: t1.aid, t1.aid
   Sort Key: t1.aid
   ->  HashAggregate
         Output: t1.aid, t1.aid
         Group By Key: t1.aid, t1.aid
         ->  Hash Join
               Output: t1.aid, t1.aid
               Hash Cond: (t1.aid = t2.bid)
               ->  Seq Scan on public.t1
                     Output: t1.aid
               ->  Hash
                     Output: t2.bid
                     ->  Seq Scan on public.t2
                           Output: t2.bid
(15 rows)


MogDB=# set behavior_compat_options to compat_sort_group_column;
SET
MogDB=# EXPLAIN (VERBOSE ON, COSTS OFF) SELECT aid, bid, '', count(1) FROM t1 JOIN t2 ON aid = bid GROUP BY aid, bid, '';
                    QUERY PLAN
--------------------------------------------------
 HashAggregate
   Output: t1.aid, t2.bid, (NULL::text), count(1)
   Group By Key: t1.aid, t2.bid, NULL::text
   ->  Hash Join
         Output: t1.aid, t2.bid, NULL::text
         Hash Cond: (t1.aid = t2.bid)
         ->  Seq Scan on public.t1
               Output: t1.aid
         ->  Hash
               Output: t2.bid
               ->  Seq Scan on public.t2
                     Output: t2.bid
(12 rows)

Related Pages

behavior_compat_options, SELECT