maria-developers team mailing list archive

[jerome brauge <j.brauge@xxxxxxxxxxx>]

Hello Alexander,
Thank for these news. 

1) without pragma, lots of
sql_lex.h(732): error C4099: 'LEX': type name first seen using 'struct' now seen using 'class'

2) Transform select : I' m new on Mariadb, but doing this kind of transformation on other sgbd that I know (oracle, sqlserver, Sybase, db2) is a performance killer ('or' operator is always complex for optimizer and to use indexes). 

For our application (who has more than 21000 stored procedures), some functions needs attention :
a) substr  (return null instead '' and if pos=0  act like if pos=1. On Mariadb, if pos=0 , result is '')

MariaDB [(none)]> select substr('abcd',0,3);
+--------------------+
| substr('abcd',0,3) |
+--------------------+
|                    |
+--------------------+

Oracle:
SQL> select substr('abcd',0,3) from dual;
SUB
---
abc


b) ltrim and rtrim : return null instead ''
c) instr : return null instead 1 if searched string is ''
d) length : return null instead 0 if searched string is ''

4) Sorry, I didn't find operator || in documentation.
But there is a problem : on Oracle, concat string with null  does not return null.

MariaDB [(none)]> select 'a'||null||'b';
+----------------+
| 'a'||null||'b' |
+----------------+
| NULL           |
+----------------+

Oracle: 
SQL>  select 'a'||null||'b' from dual;

'A
--
ab

Do you have any idea when the "GOTO" instruction will be implemented? 

Best regards,
Jérôme.


-----Message d'origine-----
De : Alexander Barkov [mailto:bar@xxxxxxxxxxx] 
Envoyé : mardi 20 décembre 2016 03:40
À : jerome brauge; MariaDB Developers
Objet : Re: MDEV-10142 - bb-10.2-compatibility

Hello Jerome,

On 12/19/2016 05:16 PM, jerome brauge wrote:
> Hello,
> To build this branch on windows (Visual Studio Express 2015), I have to add "#pragma warning( disable : 4099 )" in sql_lex.h.
> But I don't know if it's the right solution.

What happened without #pragma?



> 
> I have some questions:
>  - I read the comment of Michael Widenius on subtask MDEV-10574.
>    I think that it's important to change behavior of string functions whose returns a string (like rtrim, ltrim, substring, concat, ...) to return null instead of ''. 
>    Without this, we have to use UDF instead native functions and it's never good from a performance point of view.
>    Have you made a decision ?

The current plan is to do these transformations:

1. Transform Insert
- insert values ("") -> insert values (null)

2. Transform Select

- where v=x => (v <> "" and V=X)
- where v is null => (v="" or v is null)

We didn't plan to change functions yet. Thanks for bringing this up.
We'll discuss this.

> 
> - On Oracle, we can easily disable/enable triggers. I found an old request for this on jira : MDEV-7579. As you already manage order_action now, perhaps you can simply use negative orders to indicate disabled triggers.

Thanks. We'll also discuss this.
I'll let you know a few days later.

> 
> - do you have planning to implement operator || to concat strings ? 


This feature is available since early MySQL versions, just to make sure to set sql_mode:

> MariaDB [test]> SET sql_mode=ORACLE;
> Query OK, 0 rows affected (0.00 sec)
> 
> MariaDB [test]> SELECT 'a'||'b';
> +----------+
> | 'a'||'b' |
> +----------+
> | ab       |
> +----------+
> 1 row in set (0.00 sec)
> 
> Best regards,
> J. Brauge
>