maria-developers team mailing list archive

[Alexander Barkov <bar@xxxxxxxxxxx>]

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.