Hi Andrii, Thanks for looking into this. Please find my comments inline:
Below are mostly minor notes for which clarification is needed.
Conceptual: are we sure that ORACLE mode should invalidate native MariaDB constructs? We need some consistency and in my understanding following should still work in Oracle mode: ELSE / ELSEIF % operation (if possible)
I'm not sure. Monty, what do you think?
2. MDEV-10343 sql_mode=ORACLE: Providing compatibility for basic SQL data types
following are valid build-in datatypes declaration in oracle which cause syntax error at the moment (needs to be documented or created another task):
these probably had to be implemented inside MDEV-10343
create or replace table t1(a binary_float); create or replace table t1(a binary_double); create or replace table t1(a long raw); create or replace table t1(a NCLOB); these need more effort or maybe just must be documented or separate task is needed create or replace table t1(a varchar(30 byte)); create or replace table t1(a varchar(30 char)); create or replace table t1(a timestamp with local time zone); create or replace table t1(a timestamp with time zone); create or replace table t1(a interval day); create or replace table t1(a interval year); create or replace table t1(a BFILE); create or replace table t1(a rowid); create or replace table t1(a urowid(5)); this should give an error (oracle will return ORA-02017: integer value required ) MariaDB [test]> create or replace table t1(a number(2.2)); Query OK, 0 rows affected (0.24 sec)
There is a task for TIMESTAMP WITH TIME ZONE: MDEV-10018 Timestamp with time zone For other data types, should we create one task for every data type?
3. MDEV-10411 Providing compatibility for basic PL/SQL constructs 3.6 Assignment operator It doesn't sound intuitive that one may write "wait_timeout:=a", but "a:=wait_timeout" shows syntax error.
begin declare a int; begin a:=wait_timeout; end; end;;
One needs to use @ when referring a system variable in the right side: a:=@wait_timeout; It's the same in the default mode: SET wait_timeount=100; but: SET xxx=@wait_timeout; Not sure if we should do anything with that.
3.7 Variable declarations Oracle allows NOT NULL in variable declaration. Maybe we should allow that syntax as well?
begin declare a NOT NULL int; begin a:=1; a:=NULL; end; end;;
I have created a task for this: MDEV-13078 NOT NULL routine variables
10. MDEV-10585 EXECUTE IMMEDIATE In ORACLE the command supports 'RETURNING INTO' clause, we should probably document the difference?
sql_stmt := 'UPDATE emp SET sal = 2000 WHERE empno = :1
RETURNING sal INTO :2';
EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary;
In Oracle, EXECUTE IMMEDIATE also has the "INTO" clause: sql_stmt := 'SELECT * FROM emp WHERE empno = :id'; EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id; Should we create two new tasks, for RETURNING INTO, and for INTO?
12. MDEV-10588 TRUNCATE TABLE t1 DROP|REUSE STORAGE Following clauses should be allowed in TRUNCATE command as well: [ PRESERVE MATERIALIZED VIEW LOG | PURGE MATERIALIZED VIEW LOG ]
Should we create a task to ignore this syntax?
18. MDEV-10577 sql_mode=ORACLE: %TYPE in variable declarations It is not clear what is expected to happen if table's column definition is altered after stored program creation.
Nothing. Data type resolution is done during execution.