MAX_PT

Purpose

This function returns the maximum value of the partitioning key of a partition that contains data in a partitioned table.

Limitations and considerations

• You must have the SELECT privilege on the target table (MySQL mode checks privileges at the table level). If you do not have the required privilege, the call fails.
• The table name in the parameter must be a constant (a string literal that can be resolved during parsing). Variables or non-constant expressions are not supported as table name parameters.
• The queried table must be a partitioned table with a Range or List partitioning type.
• The queried table must be an internal table. External tables and DBLink tables are not supported.
• Only single-column partitioning keys are supported. Multi-column partitioning keys are not supported.
• Expression-based partitioning is not supported. If the partitioning key is not a direct reference to a single column but includes an expression in the partition definition, the MAX_PT function cannot be used.
• For a subpartitioned table, only the values of the top-level partitions are returned.
• If all partitions are empty, NULL is returned.
• If different partition values exist within the same partition, the result of MAX_PT is undefined, and a value from any row in the partition is returned.

Syntax

MAX_PT('[database_name.]table_name')

Parameters

• database_name: Optional. The name of the database where the table is located. If not specified, the default database of the current session is used. You can also use the 'db_name.table_name' format to specify both the database and table names in the first string parameter (separated by at most one .).
• table_name: The name of the table. The database and table names are resolved according to the case sensitivity and valid character rules of the current session and must match the names stored in the data dictionary (including case).

Return value

Returns the maximum value of the partitioning key of a partition that contains data, based on the max() comparison logic. The data type of the return value is the same as that of the partitioning key.

Examples

1. Create a partitioned table named test_tbl1.

   obclient> CREATE TABLE test_tbl1 (col1 INT, col2 INT, col3 INT)
       PARTITION BY LIST (col1) (
           PARTITION P0 VALUES IN (1),
           PARTITION P1 VALUES IN (2),
           PARTITION P2 VALUES IN (3),
           PARTITION P3 VALUES IN (4)
       );
   

2. Insert test data.

   obclient> INSERT INTO test_tbl1 VALUES
       (1,1,1),
       (2,2,2),
       (3,3,3),
       (4,4,4),
       (1,1,2),
       (2,2,2);
   

   The return result is as follows:

   Query OK, 6 rows affected
   Records: 6  Duplicates: 0  Warnings: 0
   

3. Query the maximum value of the partitioning key of a partition that contains data in the test_tbl1 table.

   obclient> SELECT MAX_PT('test_tbl1');
   

   The return result is as follows:

   +---------------------+
   | MAX_PT('test_tbl1') |
   +---------------------+
   |                   4 |
   +---------------------+
   1 row in set