Open Source Oracle Compatible PostgreSQL.

Support for Hierarchical query. This patch adds the support for the hierarchical query by transforming a Hierarchical Select query on to a Common Table Expression (CTE). This implementation includes hierarchical query syntax and in addition to that following pseudo/special columns as well: - PRIOR - LEVEL - CONNECT_BY_ROOT - SYS_CONNECT_BY_PATH

Limitations:
Currently this feature has the following limitations.
- The additional columns can be used in most expression like function call, CASE
  statement and common expression, however there are few that are not supported
  like ROW, TYPECAST, COLLATE, GROUPING clauses etc.
-  an output column name may be needed in some cases where two or more column
   are identical like
    SELECT CONNECT_BY_ROOT col AS "col1", CONNECT_BY_ROOT col AS "col2" ....
- Indirection operator or '*' is not supported.
- Loop detection is not supported.

##Current Status In the document, the parameter (weekday) of the function (next_day) is noted as follows. 0 means Sunday. However, when the parameter (weekday) is set to 0, there is an error in execution. After verification, the integer value of the parameter (weekday) is 1 to 7, not 0 to 6, 1 represents Sunday, the document is incorrect. the value of Oracle is also 1 to 7.

Enhancement

Please confirm and revise the description in the document.

Neither the Package Spec nor the Package Body compiles out of the box. At least in my build from source, the data types of NUMBER and VARCHAR2 are not defined. Even if you switch to PG data types of VARCHAR & NUMERIC it is still not close to working. There are also typos and inconsistencies in both the Spec and Body that also cause it not to work.

Also, the code in the samples should use spaces for the indenting. It seems to use a horrible combination of spaces and tabs that makes a mess when copy/paste into an editor no matter what your tab settings are.

I know it's early days, but, this is important. It's a users first impression & first experience.

type varchar2 does not exist

IvorySQL 1.2

OS Version Centos7.6

Configuration options

$ /opt/ipg/bin/pg_config |grep CONFIGURE CONFIGURE = '--prefix=/opt/ipg' '--with-openssl' '--with-includes=/usr/include/openssl'

Current Behavior

postgres=# CREATE TABLE test(x INT, y VARCHAR2(100)); ERROR: type "varchar2" does not exist LINE 1: CREATE TABLE test(x INT, y VARCHAR2(100));

Expected behavior/code

create table success

Step to reproduce

CREATE TABLE test(x INT, y VARCHAR2(100));

Additional context that can be helpful for identifying the problem

…ts at initdb

The commit includes the contrib directory to default buid target IvorySQL src. Along with that it  adds a new file ivy_module.config to contrib directory, that can be used to list extensions and SQL scripts required to be executed by initdb.

ivy_module.config gets installed at '$INSTALL_DIR/share/postgresql/' and is read by initdb to install all extensions and scripts listed in that file.

Bug Report

Copying pg_config_os.h... Generating configuration headers... undefined symbol: PACKAGE_IVORYSQL_VERSION at src/include/pg_config.h line 795 at H:/VisualProject/vs2019/IvorySQL/IvorySQL-Ivory_REL_1_1/src/tools/msvc/Mkvcbuild.pm line 864.

IvorySQL Version

1.1

OS Version (uname -a)

Windows11 visual studio 2019

Configuration options ( config.status --config )

H:\VisualProject\vs2019\IvorySQL\IvorySQL-Ivory_REL_1_1\src\tools\msvc>perl build.pl DEBUG

Current Behavior

Expected behavior/code

Step to reproduce

Additional context that can be helpful for identifying the problem

Bug Report

NCHAR is affected setting nls_length_semantics=byte as well as CHAR and VARCHAR2. I think it is undesirable. It may be caused by the native PostgreSQL behavior that accepts type NCHAR as CHAR.

IvorySQL Version

1.0 and current master

OS Version / Configuration options

V1.0 on RHEL 8.x (install from rpm package) master on CentOS 6.x (source build with configure --enable-debug)

Current Behavior

ivdb1=# CREATE EXTENSION orafce ;
ivdb1=# SET search_path TO "$user", oracle, pg_catalog, public;
ivdb1=# SET nls_length_semantics TO byte;
ivdb1=# SELECT NCHAR(10) 'あいうえお';  -- 5 chars, UTF8 3byte char x 5 = 15 bytes
ERROR:  value too long for type character(10 byte)

Bug Report

1. When I set nls_length_semantics to byte, I get an error when I explicitly convert the string to char, varchar, varchar2.
2. nvarchar2 does not distinguish between byte and char.

IvorySQL Version

1.0

OS Version (uname -a)

Linux mypg01 3.10.0-693.21.1.el7.x86_64 #1 SMP Wed Mar 7 19:03:37 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux

Configuration options ( config.status --config )

set nls_length_semantics to byte ;

Current Behavior

select '我是一个中国人'::char(5); 2022-01-04 11:00:18.204 CST [3010] ERROR: value too long for type character(5 byte) 2022-01-04 11:00:18.204 CST [3010] STATEMENT: select '我是一个中国人'::char(5); ERROR: value too long for type character(5 byte)

create table test_12 (id int, a oracle.nvarchar2(5)); CREATE TABLE insert into test_12 values (1, 'abcde'); INSERT 0 1 insert into test_12 values (1, '少年张三丰'); 2022-01-04 14:27:14.075 CST [3010] ERROR: input value too long for type nvarchar2(5 byte) 2022-01-04 14:27:14.075 CST [3010] STATEMENT: insert into test_12 values (1, '少年张三丰'); ERROR: input value too long for type nvarchar2(5 byte)

Expected behavior/code

select '我是一个中国人'::char(5); bpchar

我 (1 row)

insert into test_12 values (1, '少年张三丰'); --expect succ

Step to reproduce

set nls_length_semantics to byte ; select '我是一个中国人'::char(5);

create table test_12 (id int, a oracle.nvarchar2(5)); insert into test_12 values (1, 'abcde'); insert into test_12 values (1, '少年张三丰');

Additional context that can be helpful for identifying the problem

1. Explicit conversion should output the most suitable string.
2. nvarchar2 does not distinguish between byte and char.

…ierarchical

query by transforming a Hierarchical Select query on to a Common Table Expression (CTE). This implementation includes hierarchical query syntax and in addition to that following pseudo/special columns as well:

• PRIOR
• LEVEL
• CONNECT_BY_ROOT
• SYS_CONNECT_BY_PATH

Enhancement

Creating a table with an Oracle compatible data type like Varchar2 requires schema qualification that is not very intuitive and also conflicts with the IvorySQL documentation. IvorySQL should allow using these types without schema qualification.

Current behavior

CREATE TABLE test_ora_typw(a varchar2);
ERROR:  type "varchar2" does not exist
LINE 1: CREATE TABLE test_ora_typw(a varchar2);
                                     ^
-- Schema qualifying VARCHAR2 with Orcale works 
CREATE TABLE test_ora_typw(a oracle.varchar2);  
CREATE TABLE  
postgres=# 

###Proposed enhancement

-- Without schema qualification should work
CREATE TABLE test_ora_typw(a varchar2);

Hierarchical Query

Hierarchical queries are very useful to operate on hierarchical data. The PostgreSQL does not support it and It would be a good feature to add into the IvorySQL compatible list.

The usual clauses for a hierarchical query support are:

{ 
CONNECT BY [ NOCYCLE ] condition [AND condition]... [ START WITH condition ] 
| START WITH condition CONNECT BY [ NOCYCLE ] condition [AND condition]...
}

CONNECT BY The query syntax starts with CONNECT BY keywords which define the hierarchical interdependency between parent and child rows. The results must be further qualified by specifying the PRIOR keyword in the condition part for CONNECT BY clause.

The PRIOR keyword is a unary operator which links the previous row with the current one. The keyword can be used on the left or the right hand side of the equality condition. In case a loop is detected in the results, an error is returned to the user and the query aborted.

START WITH This clause specified from which row to start the hierarchy.

NOCYCLE This clause instructs to return data even if a cycle exists.

Bug Report

Failed to execute connect by with subquery , like follow codes

select * from (select * from area) start with name = 'hangzhou' connect by  pid = prior id;

IvorySQL Version

2.1

OS Version (uname -a)

Linux mypc 3.10.0-1160.el7.x86_64 #1 SMP Mon Oct 19 16:18:59 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux

Configuration options ( config.status --config )

Current Behavior

Test SQL:

--  create table and build data
create table area(id int, name text, pid int);
insert into area values(1, 'china', 0);
insert into area values(101, 'zhejiang', 1);
insert into area values(102, 'jiangsu', 1);
insert into area values(103, 'heilongjiang', 1);
insert into area values(10101, 'hangzhou', 101);
insert into area values(10102, 'ningbo', 101);
insert into area values(10103, 'shaoxing', 101);
insert into area values(10104, 'wenzhou', 101);
insert into area values(1010101, 'binjiang', 10101);
insert into area values(1010102, 'shangcheng', 10101);
insert into area values(1010103, 'xihu', 10101);
insert into area values(1010101, 'gongshu', 10101);
-- select 
postgres@postgres=# select * from area ;
   id    |     name     |  pid  
---------+--------------+-------
       1 | china        |     0
     101 | zhejiang     |     1
     102 | jiangsu      |     1
     103 | heilongjiang |     1
   10101 | hangzhou     |   101
   10102 | ningbo       |   101
   10103 | shaoxing     |   101
   10104 | wenzhou      |   101
 1010101 | binjiang     | 10101
 1010102 | shangcheng   | 10101
 1010103 | xihu         | 10101
 1010101 | gongshu      | 10101
(12 rows)
--  normal 
postgres@postgres=# select * from area start with name = 'hangzhou' connect by  pid = prior id;
   id    |    name    |  pid  
---------+------------+-------
   10101 | hangzhou   |   101
 1010101 | binjiang   | 10101
 1010102 | shangcheng | 10101
 1010103 | xihu       | 10101
 1010101 | gongshu    | 10101
(5 rows)
--  failed with subquery 
postgres@postgres=# select * from (select * from area) start with name = 'hangzhou' connect by  pid = prior id;
server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
@!?> 

Expected behavior/code

postgres@postgres=# select * from (select * from area) start with name = 'hangzhou' connect by  pid = prior id;
   id    |    name    |  pid  
---------+------------+-------
   10101 | hangzhou   |   101
 1010101 | binjiang   | 10101
 1010102 | shangcheng | 10101
 1010103 | xihu       | 10101
 1010101 | gongshu    | 10101
(5 rows)

Step to reproduce

Additional context that can be helpful for identifying the problem

function transformHierarStmt in analyze.c has question of follow codes.

	rv = (RangeVar *) linitial(stmt->fromClause);            // not sure fromClause is RangeVar, maybe RangeSubSelect ......
	cte->ctename = psprintf("cte_%s", rv->relname);   // if rv is not RangeVar, maybe crash here.

detailed :

static Node *
transformHierarStmt(ParseState *pstate, SelectStmt *stmt)
{
	RangeVar   *rv;
	ParseState *ps = NULL;
	ListCell   *lc;
	CommonTableExpr *cte = makeNode(CommonTableExpr);
	WithClause *withClause = makeNode(WithClause);
	List	   *newCols = NIL;

	SelectStmt *ctequery = makeNode(SelectStmt);
	SelectStmt *ctequery_l = makeNode(SelectStmt);
	SelectStmt *ctequery_r = makeNode(SelectStmt);

	/* Setup the CTE subquery (anchor and recursive queries) */
	ctequery->op = SETOP_UNION;
	ctequery->all = true;
	ctequery->larg = (SelectStmt *) ctequery_l;
	ctequery->rarg = (SelectStmt *) ctequery_r;

	/* Buildup a CTE name and fill up other info */
	rv = (RangeVar *) linitial(stmt->fromClause);            // not sure fromClause is RangeVar, maybe RangeSubquery
	cte->ctename = psprintf("cte_%s", rv->relname);   // if rv is not RangeVar, maybe crash here.
	cte->ctequery = (Node *) ctequery;
	cte->location = -1;
	cte->aliascolnames = NIL;

	withClause->ctes = list_make1(cte);
	withClause->recursive = true;
	withClause->location = -1;

	/* transform targetlist */
	ps = make_parsestate(pstate);
	pstate->p_paramref_hook = ps->p_paramref_hook = NULL;

	ctequery_l->fromClause = copyObject(stmt->fromClause);
	ctequery_r->fromClause = copyObject(stmt->fromClause);
	transformFromClause(ps, ctequery_l->fromClause);

	ctequery_l->targetList = copyObject(stmt->targetList);
	ctequery_r->targetList = copyObject(stmt->targetList);

	/* transform left-hand operand of the cte subquery */
	newCols = transformHierarTargetList(ps, true, &ctequery_l->targetList, NULL);

	/* Build and fill in the CTE's inner select's target list */
	ctequery_l->intoClause = NULL;
	ctequery_l->whereClause = NULL;
	if (stmt->hierarClause != NULL &&
		stmt->hierarClause->startWith)
		ctequery_l->whereClause = copyObject(stmt->hierarClause->startWith->condition);

	/*
	 * make this recursive query's joining condition and adjust it as per
	 * prior keyword
	 */
	ctequery_r->whereClause = copyObject(stmt->hierarClause->connectBy->condition);

	newCols = list_concat_unique(newCols, fixupWhenAndSortClauses(ps, stmt, ctequery_r, rv->relname));

	/* transform right-hand operand of the cte subquery */
	transformHierarTargetList(ps, false, &ctequery_r->targetList, rv->relname);

	/*
	 * append pseudo columns list to the target list while avoiding the
	 * duplicity
	 */
	ctequery_l->targetList = list_concat_unique(ctequery_l->targetList, newCols);
	ctequery_r->targetList = list_concat_unique(ctequery_r->targetList, newCols);

	/* qualify the target list columns with appropriate relation */
	ctequery_r->targetList = qualifyTlistColumns(ps, ctequery_r->targetList, rv->relname);

	/* add CTE relation to the recursive query */
	ctequery_r->fromClause = lappend(ctequery_r->fromClause,
									 makeRangeVar(NULL, cte->ctename, -1));

	/*
	 * transform the target list of cte query. If there were any
	 * pseudocolumns, they have been replaced with appropriate values and an
	 * alias has been created if one was not given. we need to use add these
	 * column alias here to refer to them.
	 */
	pstate->p_cterqry = true;
	foreach(lc, stmt->targetList)
	{
		ResTarget  *res = (ResTarget *) lfirst(lc);

		res->val = resolvePseudoColumns(pstate, (Node *) res->val, res, NULL, NULL);
	}
	pstate->p_cterqry = false;

	/*
	 * The select statement is now part of cte, and it needs to refer to the
	 * cte in the from clause, instead of the actual table.
	 */
	rv->relname = pstrdup(cte->ctename);
	stmt->withClause = withClause;
	/* hierarClause is not needed anymore */
	stmt->hierarClause = NULL;

	return (Node *) stmt;
}

Bug Report

missing support function 1(16895,25) for attribute 1 of index "test_name_idx"

IvorySQL Version

All versions(1.1~1.5)

OS Version (uname -a)

Linux localhost.localdomain 4.18.0-80.el8.x86_64 #1 SMP Tue Jun 4 09:19:46 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux

Configuration options ( config.status --config )

./configure --prefix=/home/IVORYSQL --enable-debug --enable-cassert CFLAGS='-O0 -g' --enable-tap-tests

Current Behavior

postgres=# select * from test where name = 'test1'; 2022-12-05 13:54:05.240 CST [15604] ERROR: missing support function 1(16895,25) for attribute 1 of index "test_name_idx" 2022-12-05 13:54:05.240 CST [15604] STATEMENT: select * from test where name = 'test1'; ERROR: missing support function 1(16895,25) for attribute 1 of index "test_name_idx"

Expected behavior/code

select * from test where name = 'test1'; id | name
----+------- 1 | test1

Step to reproduce

set compatible_mode to oracle; create table test(id int, name varchar2(100)); insert into test select a,'test'||a from generate_series(1, 10000) a; create index on test(name); select * from test where name = 'test1';

Additional context that can be helpful for identifying the problem

Enhancement

我们团队目前在做数据库相关的研究，注意到 IvorySQL 和 HGDB 都是在 PG 开源的基础上更新的。由于后者不开源，因此想借这个 issue board 请问一下有关事务隔离级别的问题。

简单来说，我们在 HGDB 6 上发现了一个 wrikte-skew 的异常，这是否是被允许的？据我所知这个 bug 在 pg 12.4 上被修复，但是似乎在 HGDB 上还是存在的。

在 serializable 的隔离级别下运行。抽象来说，有 3 个事务：

• T1: W(1,1), R(3, null)
• T2: W(3,3), R(1, null)
• T3: W(3, 4) 事务执行的结果等价与任意一种串行执行的结果（否则 T1 和 T2 有一个事务的读操作不会读到空值）

Repair When creating a SEQUENCE, if the SEQUENCE name contains' package ', it is mistaken for creating a package statement under psql。 if the SEQUENCE name contains' function ' or 'procedure' is the same. e.g. CREATE SEQUENCE public.ecologypackageinfo_id_seq AS integer START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

Bug Report

If the logical identifier of procedure is "$xxxxxx$", creation fails. If it is $$, it is created. But in PostgreSQL 14.5, both of the above cases can be created successfully.

IvorySQL Version

All versions(1.1~1.5)

OS Version (uname -a)

Linux localhost.localdomain 3.10.0-862.el7.x86_64 #1 SMP Fri Apr 20 16:44:24 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux

Configuration options ( config.status --config )

compatible_mode = postgres

Current Behavior

Test SQL: The logical identifier of procedure is "$body$".

create or replace procedure proc_test_outpara(vid in integer, ov1 out varchar)
language plpgsql
as
$body$
declare
begin
    if vid = 1 then
        ov1 = 'OK';
    else
        ov1 = 'NG';
    end if;
end;
$body$;

Test result: IvorySQL 1.5: create fail

PostgreSQL 14.5: create success

Expected behavior

Consistent with PG14.5, the procedure with "$xxxxxx$" is also created successfully.