Help Center> GaussDB> Distributed> Setting Encrypted Equality Queries> Sorting Encrypted Data on Clients (Lab Feature)
Updated on 2023-10-23 GMT+08:00
View PDF

Sorting Encrypted Data on Clients (Lab Feature)

The current feature is a lab feature. Contact Huawei technical support before using it.

The client decrypts and sorts the ciphertext returned by the query on the client. To enable encrypted data sorting on clients, use gsql to enable -C2 or set --enable-client-encryption to 2, and set enable_ ce to 2 by using JDBC connections.

Currently, the client supports the following sorting syntax:

1
2
3
4
5
6
7
8
 
SELECT [ DISTINCT ] { * | {colname [ [ AS ] output_name ] | [ SUM | MIN | MAX | COUNT | AVG ] ( colname ) [ [ AS ] output_name ] } [, ...] }
[ FROM tablename ]
[ [ INNER ] JOIN | LEFT [ OUTER ] JOIN | RIGHT [ OUTER ] JOIN | FULL [ OUTER ] JOIN | CROSS JOIN ] { tablename } ON condition { [ AND | OR ] condition } 
[ WHERE condition ]
[ GROUP BY {colname | output_name} [, ...] ]
[ ORDER BY { {colname | output_name} [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ]} [, ...] ]
[ LIMIT count ]
[ OFFSET start ];

Parameter Description

  • colname

    When colname is used as the input parameter of aggregate functions MAX, MIN, SUM, AVG, and COUNT, only column names can be directly referenced and column types cannot be converted.

  • output_name

    output_name is the alias of colname. GROUP BY and ORDER BY cannot be followed by the alias of an aggregate function.

  • WHERE clause

    condition is an equivalent condition connected by AND or OR, for example, colname=1, colname!=1, and colname IS NULL. It cannot contain expressions.

For details about other parameters, see section "SQL Reference > SQL Syntax > SELECT" in Developer Guide.

This version supports the ORDER BY, GROUP BY, DISTINCT, LIMIT, and OFFSET operations on encrypted columns, but does not support the following sorting scenarios:

  • DISTINCT ON, HAVING, and EXECUTE DIRECT clauses, functions, stored procedures, nested SQL statements, and views are not supported.
  • Only aggregate functions MAX, MIN, SUM, AVG, and COUNT can be used for sorting encrypted columns. The corresponding window functions are not supported. The five aggregate functions override the aggregate operations on the ciphertext on the client. You cannot use this method to call a server-defined aggregate function with the same name or use a schema name combined with a function name, for example, PG_CATALOG.MAX, to call a client aggregate function. The result of executing the SUM or AVG aggregate function on strings is 0.
  • If GROUP BY is followed by an encrypted column, the column name must be explicitly specified in the SELECT target column. The asterisk (*) cannot be used.
  • If ORDER BY or GROUP BY contains encrypted columns, LIMIT and OFFSET cannot be followed by any expression, for example, 2+2. In this example, replace it with 4.
  • The ORDER BY 1,2 syntax is not supported. The encrypted column name must be explicitly specified.
  • Encrypted strings can only be sorted in ASCII order, regardless of the character set on the server. Encrypted data of the floating-point or numeric type is calculated using the default precision on the client, regardless of the precision setting on the server.
  • Table names, column names, and aliases after GROUP BY and ORDER BY cannot contain special characters such as single quotation marks ('), for example, SELECT i2 as c FROM t1 as "'a" ORDER BY "'a".i2;.
  • Multiple queries sent in a PQexec call are not supported. Example: PQexec("PREPARE p1 select distinct i1 from t1 order by i1;EXECUTE p1;");
  • Columns with the same name are not verified in join scenarios. Otherwise, it may cause unexpected results. If two tables have identical column names, you are not advised to use JOIN for sorting on clients.

The ciphertext data returned by the server is decrypted on the client before being sorted, which consumes some resources on the client. It is recommended that a maximum of 100,000 data records be returned for each query.

The current version supports only a limited number of sorting scenarios. In other scenarios where no proper syntax is provided, unexpected results may occur. Therefore, you are not advised to sort encrypted columns in this case.

Querying and Sorting Data in an Encrypted Table

  1. Create a key. For details, see Using gsql to Operate an Encrypted Database and Using JDBC to Operate an Encrypted Database.
  2. Create an encrypted table.

    1
2
3
4
5
6
    		 
    gaussdb=# CREATE TABLE IF NOT EXISTS t1(
gaussdb=# id int, i1 int,
gaussdb=# i2 INT ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = cek1, ENCRYPTION_TYPE = DETERMINISTIC),
gaussdb=# i3 varchar(20) ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = cek1, ENCRYPTION_TYPE = DETERMINISTIC),
gaussdb=# i4 INT);
CREATE TABLE

  3. Insert data.

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
    		 
    gaussdb=# INSERT INTO t1 VALUES(2,2,200, 'two hundreds - 1', 10);
INSERT 0 1
gaussdb=# INSERT INTO t1 VALUES(3,7,300, 'three hundreds - 1', 11);
INSERT 0 1
gaussdb=# INSERT INTO t1 VALUES(4,6,400, 'four hundreds', 12);
INSERT 0 1
gaussdb=# INSERT INTO t1 VALUES(5,5,300, 'three hundreds - 2');
INSERT 0 1
gaussdb=# INSERT INTO t1 VALUES(6,4,200, 'two hundreds - 2');
INSERT 0 1
gaussdb=# INSERT INTO t1 VALUES(7,3,200, 'two hundreds - 3');
INSERT 0 1

  4. Perform sorting.

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
    		 
    gaussdb=# select sum(id), i2 from t1 group by i2 order by i2;
 sum | i2  
-----+-----
   1 | 100
  15 | 200
   8 | 300
   4 | 400
(4 rows)
gaussdb=# select min(id), i2 from t1 group by i2 order by i2;
 min | i2  
-----+-----
   1 | 100
   2 | 200
   3 | 300
   4 | 400
(4 rows)