GitHub user vovik0134 added a comment to the discussion: [Ideas] Description of
backward-incompatible changes between Greenplum 6 and Cloudberry in the
documentation.
The following backward-incompatible changes were identified in the behavior of
functions and operators.
## Changes in function and operator behavior
### Change in the behavior of set-returning functions
Starting with PostgreSQL kernel version 10, the behavior of Set Returning
Functions, such as `generate_series`, `unnest`, `regexp_split_to_table`,
`json_each`, and `json_array_elements`, changed in the `SELECT` list.
The difference can be seen in the following example.
Greenplum 6:
```sql
SELECT 'example' AS value, generate_series(1, 3), generate_series(1, 2);
value | generate_series | generate_series
---------+-----------------+-----------------
example | 1 | 1
example | 2 | 2
example | 3 | 1
example | 1 | 2
example | 2 | 1
example | 3 | 2
(6 rows)
```
Cloudberry:
```sql
SELECT 'example' AS value, generate_series(1, 3), generate_series(1, 2);
value | generate_series | generate_series
---------+-----------------+-----------------
example | 1 | 1
example | 2 | 2
example | 3 |
(3 rows)
```
**How to detect**
Check the application codebase for uses of Set Returning Functions in the
`SELECT` list.
**How to fix**
Replace calls to Set Returning Functions in the `SELECT` list with the
corresponding `CROSS JOIN LATERAL`.
### The return type of the `extract()` function has been changed to `numeric`
Starting with PostgreSQL kernel version 14, the `extract()` function returns a
value of type `numeric` instead of `double precision`.
Example for Greenplum 6:
```sql
SELECT pg_typeof(extract(epoch FROM now()));
pg_typeof
------------------
double precision
```
Example for Cloudberry:
```sql
SELECT pg_typeof(extract(epoch FROM now()));
pg_typeof
-----------
numeric
```
If an application expects `double precision`, its behavior may become
unpredictable.
**How to detect**
Check the application codebase for uses of the `extract()` function.
**How to fix**
Modify the application or the problematic queries by adding an explicit type
cast with `::double precision`.
### The behavior of the `to_number()` function has been fixed for correct
format handling
In Greenplum 6, the `to_number()` function “swallowed” extra characters:
```sql
SELECT to_number('1234', '9,999');
to_number
-----------
134
(1 row)
```
In Cloudberry, this behavior has been fixed:
```sql
SELECT to_number('1234', '9,999');
to_number
-----------
1234
(1 row)
```
**How to detect**
Check the application codebase for uses of the `to_number()` function and for
possible issues caused by “swallowing” extra characters.
**How to fix**
Adapt the application to the changed, correct behavior of `to_number()`.
### The evaluation order of some operators has changed
In Cloudberry, compared to Greenplum 6, the evaluation order of the `<=`, `>=`,
`<>`, `IS`, `NOT BETWEEN`, and other compound operators has changed.
For example, in Greenplum 6, the following query is invalid:
```sql
SELECT 1 <> 2 IS TRUE;
ERROR: argument of IS TRUE must be type boolean, not type integer
```
But in Cloudberry, the query works:
```sql
SELECT 1 <> 2 IS TRUE;
?column?
----------
t
(1 row)
```
**How to detect**
Check the application codebase for expressions with ambiguous operator
evaluation order.
**How to fix**
Explicitly specify the operator evaluation order using parentheses.
### Behavior of `SIMILAR TO` with `ESCAPE NULL` has changed
Starting with PostgreSQL kernel version 13, the behavior of the `SIMILAR TO ...
ESCAPE NULL` construct changed.
Example for Greenplum 6:
```sql
SELECT 'abc' SIMILAR TO 'abc' ESCAPE NULL;
?column?
----------
t
(1 row)
```
Example for Cloudberry:
```sql
SELECT 'abc' SIMILAR TO 'abc' ESCAPE NULL;
?column?
----------
(1 row)
```
**How to detect**
Check the application codebase for uses of the `ESCAPE NULL` construct.
**How to fix**
Replace `ESCAPE NULL` with `ESCAPE <some value>`.
GitHub link:
https://github.com/apache/cloudberry/discussions/1816#discussioncomment-17260264
----
This is an automatically sent email for [email protected].
To unsubscribe, please send an email to: [email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
