1. Version Introduction
[Release date: November 17, 2023]
IvorySQL 3.0 is based on PostgreSQL 16.0 and includes various fixes from PostgreSQL 16.0, for more detailed updates and bug fixes in PostgreSQL 16.0, see the official PostgreSQL 16.0 Release Notes.
2. Updates on Version 3.0 vs 2.3
IvorySQL 3.0 signifies a major shift in its architectural design, diverging from the operational patterns established in version 2.3. It’s crucial to recognize that certain features present in version 2.3 have not been incorporated into the new version. The following is an overview highlighting the primary distinctions in features between these two iterations:
Functional modules |
function |
IvorySQL-2.3 |
IvorySQL-3.0 |
Built-in data types |
char |
not support |
support |
varchar |
support |
support |
|
varchar2 |
support |
support |
|
number |
not support |
support |
|
binary_float |
not support |
support |
|
binary_double |
not support |
support |
|
date |
support |
support |
|
timestamp |
not support |
support |
|
timestamp with time zone |
not support |
support |
|
timestamp with local time zone |
not support |
support |
|
interval year to month |
not support |
support |
|
interval day to second |
not support |
support |
|
raw |
not support |
support |
|
long |
not support |
support |
|
Built-in functions |
char |
not support |
support |
sysdate |
support |
support |
|
systimestamp |
support |
support |
|
add_months |
support |
support |
|
last_day |
support |
support |
|
next_day |
support |
support |
|
months_between |
support |
support |
|
current_date |
not support |
support |
|
current_timestamp |
not support |
support |
|
new_time |
support |
support |
|
tz_offset |
not support |
support |
|
trunc |
support |
support |
|
instr |
not support |
support |
|
instrb |
not support |
support |
|
substr |
not support |
support |
|
substrb |
support |
support |
|
trim |
not support |
support |
|
ltrim |
not support |
support |
|
rtrim |
not support |
support |
|
length |
not support |
support |
|
lengthb |
not support |
support |
|
rawtohex |
not support |
support |
|
replace |
not support |
support |
|
regexp_replace |
not support |
support |
|
regexp_substr |
not support |
support |
|
regexp_instr |
not support |
support |
|
regexp_like |
not support |
support |
|
to_number |
support |
support |
|
to_char |
support |
support |
|
to_date |
support |
support |
|
to_timestamp |
support |
support |
|
to_timestamp_tz |
support |
support |
|
to_yminterval |
not support |
support |
|
to_dsinterval |
support |
support |
|
numtodsinterval |
support |
support |
|
numtoyminterval |
support |
support |
|
localtimestamp |
not support |
support |
|
new_time |
not support |
support |
|
from_tz |
support |
support |
|
sys_extract_utc |
support |
support |
|
sessiontimezone |
support |
support |
|
hextoraw |
not support |
support |
|
uid |
not support |
support |
|
USERENV |
not support |
support |
|
NLS parameter |
NLS_LENGTH_SEMANTICS |
not support |
support |
NLS_DATE_FORMAT |
not support |
support |
|
NLS_TIMESTAMP_FORMAT |
not support |
support |
|
NLS_TIMESTAMP_TZ_FORMAT |
not support |
support |
|
Function |
Syntax compatibility is supported, and the OUT parameter is supported |
support |
support |
Procedure |
Syntax compatibility is supported, and the OUT parameter is supported |
support |
support |
Anonymous block |
Syntax compatibility is supported, and the OUT parameter is supported |
not support |
support |
Nested subroutines |
Nested stored procedures, functions, and so on are supported |
not support |
support |
Merge |
The Merge function of PG and the Merge function compatible with Oracle syntax are supported |
not support |
support |
q` |
Compatible escapes are supported |
support |
support |
Keyword processing |
Supports the processing of keywords in the database |
not support |
support |
Object case conversion |
All uppercase plus double quotation marks are converted to lowercase |
not support |
support |
All lowercase plus double quotation marks are converted to uppercase |
not support |
support |
|
The mixed case plus double quotation marks remain the same |
not support |
support |
|
Without double quotation marks (default), all are lowercase |
not support |
support |
|
Search Path |
In compatibility mode, the default search is sys mode, and then the pg_catalog mode |
not support |
support |
Empty strings |
Oracle-compatible conversion of empty strings to NULL is supported |
not support |
support |
Lexical parser separation |
Part of the 3.0 framework |
not support |
support |
package |
support |
not support |
|
Globally unique indexes |
support |
support |
|
GUC Switch to oracle or pg |
support |
support |
|
Hierarchical queries |
support |
not support |
|
NANVL |
support |
not support |
|
GREATEST |
support |
not support |
|
LEAST |
support |
not support |
|
ADD_DAYS_TO_TIMESTAMP |
support |
not support |
|
DAYS_BETWEEN |
support |
not support |
|
DAYS_BETWEEN_TMTZ |
support |
not support |
|
DBTIMEZONE |
support |
not support |
|
TO_MULTI_BYTE |
support |
not support |
|
TO_SINGLE_BYTE |
support |
not support |
|
INTERVAL_TO_SECONDS |
support |
not support |
|
HEX_TO_DECIMAL |
support |
not support |
|
TO_BINARY_DOUBLE |
support |
not support |
|
TO_BINARY_FLOAT |
support |
not support |
|
BIN_TO_NUM |
support |
not support |
4. Enhancements
4.1. IvorySQL core framework
-
Add dual parser functionality to support different database parsers Problem details
-
Added dual ports to support different database port numbers Problem details
-
Add initdb -m to support postgres mode or oracle mode Problem details
4.2. Compatible with SQL
-
Compatible with Oracle Merge Command Problem details
-
Compatible with Oracle Q escaping Problem details
-
Compatible with oracle like Problem details
4.3. Compatible with PL/SQL
-
Addresses an issue with PL/SQL creation functions/stored procedures Problem details
-
Compatible with Oracle anonymous blocks Problem details
-
Creating a function or procedure in SQL parser supports nested subprocedures Problem details
-
Nested child processes and functions IS/AS do not need to be declared Problem details
4.4. Others
-
Add meson compilation to action Problem details
-
Support for meson compilation Problem details
-
Add compatible test cases Problem details
-
Add contrib regression Problem details
-
Compatible with btree_gist indexes Problem details
-
Compatible with btree_gin indexes Problem details
-
Add Oracle data type GIN indexing operations Problem details
-
Add the Oracle data type Gist Index Operation Problem details
-
Compatible with Oracle built-in data types and built-in functions Problem details
-
Add the plisql extension Problem details
Description: For more information about the new features, please refer to the feature list in this document center
5. Fixed issue
-
After compiling with meson, the initdb execution fails Problem details
-
The operator result for a character type null value is incorrect Problem details
-
An error occurred while restoring the backup Problem details
-
ivorysql_ora some test cases fail Problem details
-
The NLS parameter specifies that under the three relationships between the ff precision and the accuracy specified in the table, the data processing exceeding the length is inconsistent Problem details
-
The data processing after special symbols appear in the data in DD HH.MI and SS AM in the date format is inconsistent with Oracle Problem details
-
For the date format, there are problems with the digit verification of each part Problem details
-
NLS related parameter verification issues Problem details
-
Solve the problem that the NLS parameter is set to 12-hour clock, and the default rule for completing AM/PM keywords is inconsistent with Oracle Problem details
-
The problem that the DEFAULTED field value in the xx_arguments view of a function/stored procedure created with a default value is N Problem details
-
Functions/stored procedures without permissions can be viewed in all_procedures/all_arguments/all_source views Problem details
-
When the self-incrementing column type is number type and the precision is specified, when a null value is inserted by default on null, it is not the inserted concrete sequence value, but the inserted null value Problem details
6. Source Code
IvorySQL contains 2 main code repositories, the database IvorySQL code bin, and the IvorySQL web bin.
-
IvorySQL code bin: https://github.com/IvorySQL/IvorySQL
-
IvorySQL web bin: https://github.com/IvorySQL/Ivory-www
7. Contributors
The following individuals have contributed to this release as patch authors, committers, reviewers, testers, or issue reporters.
-
IvorySQL Pro development & testing team
-
Yang Tan
-
Jie Wang
-
Shuainan Mu
-
Hongyuan Zhang
-
Cary Huang
-
Grant Zhou
-
David Zhang
-
Shoubo Wang
-
Jiao Ren
-
Zheng Liu
-
Zhekai Xiao
-
Huajian Jin
-
Lily Wang
-
Jinzhou Song
-
Leo X.M. Zeng
-
Shaoan Yan
-
M.Imran Zaheer
-
Yunhe Xu
-
Hao Wang
-
Miss Dong
-
Weibo Han