maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #10935
Re: MDEV-14013
Hello Jerome,
Thank you very much for you contribution!
On 10/11/2017 02:32 PM, jerome brauge wrote:
> Hello Alexander,
> Here is the patch for MDEV-14013, can you review it ?
The patch looks very good. I only found small things:
1. LEX::make_text_literal_concat()
thd->variables.character_set_client was used instread of
thd->variables.collation_connection
2. LEX::make_text_literal_concat() was somewhat hard to read.
I propose to change the logic to do:
if (item->type() == Item::NULL_ITEM)
{
}
instead of
if (item->type() != Item::NULL_ITEM ||
!(thd->variables.sql_mode & MODE_EMPTY_STRING_IS_NULL))
I think it looks clearer this way.
Also, I added some DBUG_ASSERTs to make more sure that a
non-Item_string item is not erroneously cast to Item_string.
3. Also, I found that it's easier to place the new methods into THD
instead of LEX.
Please find a patch with changes 1,2,3 attached.
Do you agree with these changes?
Also, can you please add some tests:
- SHOW CREATE VIEW into "Test in a view" in empty_string_literal.result.diff
- EXPLAIN EXTENDED for the affected grammar:
EXPLAIN EXTENDED SELECT '';
EXPLAIN EXTENDED SELECT _latin1'';
EXPLAIN EXTENDED SELECT N'';
EXPLAIN EXTENDED SELECT '' '';
Thanks!
>
> Thanks.
>
>
>> -----Message d'origine-----
>> De : Alexander Barkov [mailto:bar@xxxxxxxxxxx]
>> Envoyé : mercredi 11 octobre 2017 04:15
>> À : jerome brauge
>> Objet : Re: Question about MDEV-10574
>>
>> Hello Jerome,
>>
>>
>> On 10/10/2017 04:23 PM, jerome brauge wrote:
>>> Hello Alexander,
>>> For sql_mode EMPTY_STRING_IS_NULL, do you think that it must cover call
>> of statement with use of user variable initialized with an empty string ?
>>>
>>> Ex :
>>> SET sql_mode=default;
>>> SET @param='';
>>>
>>> SET sql_mode=EMPTY_STRING_IS_NULL;
>>> PREPARE test FROM 'select 1,?';
>>> EXECUTE test USING @param;
>>>
>>> Or
>>>
>>> delimiter /
>>> CREATE OR REPLACE PROCEDURE p1(IN a VARCHAR(50)) BEGIN
>>> IF a is null THEN
>>> SELECT 'IS NULL';
>>> ELSE
>>> select 'IS NOT NULL';
>>> END IF;
>>> END;
>>> /
>>> delimiter ;
>>> SET sql_mode=default;
>>> SET @a='';
>>>
>>> SET sql_mode=EMPTY_STRING_IS_NULL;
>>> EXECUTE IMMEDIATE 'CALL p1(?)' USING @a; CALL p1(@a);
>>>
>>> For me, it's a study case and we can forget it.
>>
>> I agree. Oracle doesn't have user variables.
>>
>>>
>>> Regards,
>>> Jérôme.
>>>
>>>> -----Message d'origine-----
>>>> De : Alexander Barkov [mailto:bar@xxxxxxxxxxx] Envoyé : samedi 7
>>>> octobre 2017 11:44 À : jerome brauge Objet : Re: Patch for MDEV-10574
>>>>
>>>> Hello Jerome,
>>>>
>>>>
>>>> On 10/06/2017 01:41 PM, jerome brauge wrote:
>>>>> Hello Alexander,
>>>>>
>>>>>> -----Message d'origine-----
>>>>>> De : Alexander Barkov [mailto:bar@xxxxxxxxxxx] Envoyé : jeudi 5
>>>>>> octobre 2017 21:26 À : jerome brauge Objet : Re: Patch for
>>>>>> MDEV-10574
>>>>>>
>>>>>> Hello Jerome,
>>>>>>
>>>>>>
>>>>>> On 10/05/2017 12:45 PM, jerome brauge wrote:
>>>>>>> Hello Alexander,
>>>>>>> Have you gone ahead on your reflection about nulls and empty strings
>> ?
>>>>>>
>>>>>> Yes, I think so.
>>>>>>
>>>>>> One addition: Monty suggested to rename flags to:
>>>>>> - EMPTY_STRING_IS_NULL for literals
>>>>>> - EMPTY_STRING_IS_NULL_RESULTS for functions.
>>>>>>
>>>>>> I'm fine with EMPTY_STRING_IS_NULL for literals, but think that
>>>>>> EMPTY_STRING_IS_NULL_FUNCTIONS should be more clear than
>>>>>> EMPTY_STRING_IS_NULL_RESULTS.
>>>>>> Which name do you prefer for functions?
>>>>>
>>>>> I think like you.
>>>>> For me, EMPTY_STRING_IS_NULL_RESULTS sounds like including
>>>> RESULTSET
>>>>> of select (which is perhaps included in point 4)
>>>>
>>>> Okey, let's go with _FUNCTIONS then.
>>>>
>>>>>
>>>>>>>
>>>>>>> Can I
>>>>>>> - redo my pull request for substring_oracle function
>>>>>>> - make a patch for first new sql_mode
>>>>>> (MODE_EMPTY_AS_NULL_LITERALS)
>>>>>>
>>>>>>
>>>>>> Yes, please. Let's move forward.
>>>>>>
>>>>>> Let's push the patch for substr_oracle first.
>>>>>> Let's keep it return empty strings for the cases when the
>>>>>> substring_length parameters is less than 1.
>>>>>> It will start automatically returning NULL when we add the new
>>>>>> sql_mode for functions.
>>>>>
>>>>> Pull request is done.
>>>>
>>>> I have merged it. Thanks!
>>>>
>>>>>
>>>>>> Also, let's go ahead with the part for literals.
>>>>>
>>>>> Ok, I will make a separate patch.
>>>>> Many thanks.
>>>>>
>>>>>>
>>>>>>
>>>>>> I have created MDEVs. Please use them in the commit comment and in
>>>>>> the
>>>>>> tests:
>>>>>>
>>>>>> MDEV-14012 sql_mode=Oracle: substr(): treat position 0 as position
>>>>>> 1
>>>>>> MDEV-14013 sql_mode=EMPTY_STRING_IS_NULL
>>>>>>
>>>>>>
>>>>>> Thanks!
>>>>>>
>>>>>>>
>>>>>>> Regards,
>>>>>>> Jérôme.
>>>>>>>
>>>>>>>
>>>>>>>> -----Message d'origine-----
>>>>>>>> De : Alexander Barkov [mailto:bar@xxxxxxxxxxx] Envoyé : mercredi
>>>>>>>> 27 septembre 2017 08:50 À : jerome brauge Objet : Re: Patch for
>>>>>>>> MDEV-10574
>>>>>>>>
>>>>>>>> Hello Jerome,
>>>>>>>>
>>>>>>>> I told to Monty yesterday.
>>>>>>>>
>>>>>>>> We were going to implement the following transformations:
>>>>>>>>
>>>>>>>>> We are considering to implement both of the following options
>>>>>>>>> (with a
>>>>>>>>> switch)
>>>>>>>>>
>>>>>>>>> Transform Insert
>>>>>>>>> - insert values ("") -> insert values (null)
>>>>>>>>>
>>>>>>>>> Transform Select
>>>>>>>>>
>>>>>>>>> where v=x => (v <> "" and V=X)
>>>>>>>>> where v is null => (v="" or v is null)
>>>>>>>>>
>>>>>>>>
>>>>>>>> See MDEV-10574 for details.
>>>>>>>>
>>>>>>>> Monty did not fully understand the idea of translating literals
>>>>>>>> and
>>>>>> functions
>>>>>>>> from empty strings to null. He asked if you can explain use cases
>>>>>>>> for these transformations.
>>>>>>>>
>>>>>>>> But we agreed that:
>>>>>>>>
>>>>>>>> 1. It's OK to have multiple NULL handling flags in sql_mode.
>>>>>>>>
>>>>>>>> 2. New flags will not be enabled in 10.3, neither in
>>>>>>>> sql_mode=DEFAULT,
>>>>>> nor in
>>>>>>>> sql_mode=ORACLE. So one will have to specify them explicitly:
>>>>>>>> set
>>>>>>>>
>>>>>>
>>>>
>> sql_mode='ORACLE,MODE_null_transfrormation_flag1,MODE_null_transfor
>>>>>>>> mation_flag2';
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> So I propose we introduce the following flags:
>>>>>>>>
>>>>>>>>
>>>>>>>> 1. MODE_EMPTY_AS_NULL_LITERALS - for literals and PS
>> parameters,
>>>> as
>>>>>> in
>>>>>>>> your patch.
>>>>>>>>
>>>>>>>> 2. MODE_EMPTY_AS_NULL_FUNCTIONS - for functions.
>>>>>>>> This should be done as a separate patch.
>>>>>>>> Note, your patch currently implements empty-to-null translation
>>>>>>>> only for Item_str_func descendants that use
>> make_empty_result().
>>>>>>>> This patch should be extended to cover Item_str_func descendants
>>>>>>>> which do not use make_empty_result(), as well as
>>>>>>>> all other Item_func_or_sum descendants
>>>>>>>> (which are not Item_str_func descendants).
>>>>>>>>
>>>>>>>>
>>>>>>>> 3. MODE_EMPTY_AS_NULL_UPDATES
>>>>>>>>
>>>>>>>> For INSERT and UPDATE transformation, as in MDEV:
>>>>>>>>
>>>>>>>> insert values ("") -> insert values (null)
>>>>>>>> update set a='' -> update set a=null;
>>>>>>>>
>>>>>>>>
>>>>>>>> 4. MODE_EMPTY_AS_NULL_PREDICATES
>>>>>>>>
>>>>>>>> For predicate transformation, as in MDEV:
>>>>>>>>
>>>>>>>> where v=x => (v <> "" and V=X)
>>>>>>>> where v is null => (v="" or v is null)
>>>>>>>>
>>>>>>>>
>>>>>>>> What do you think about this proposal with multiple flags?
>>>>>>>> Note, we can do N1 and N2 now, and add N3 and N4 and later.
>>>>>>>>
>>>>>>>> Also, can you please write a description why you need N1 and N2.
>>>>>>>> Monty thinks that N3 and N4 should cover most use cases.
>>>>>>>> So we need a good rationale for Monty and Sergei explaining why
>>>>>>>> we
>>>>>> adding
>>>>>>>> these flags N1 and N2.
>>>>>>>>
>>>>>>>>
>>>>>>>> Thanks!
>>>>>>>>
>>>>>>>>
>>>>>>>> On 09/26/2017 04:33 PM, jerome brauge wrote:
>>>>>>>>> Hi Alexander,
>>>>>>>>> I'm disappointed.
>>>>>>>>> Can we at least keep this part in sql_prepare :
>>>>>>>>>
>>>>>>>>> /*
>>>>>>>>> set_param_str_oracle : convert empty string to null value
>>>>>>>>> This allow to bind empty string in JDBC as a null value.
>>>>>>>>> Ex :
>>>>>>>>> String sel="select coalesce(?,'null param') from dual";
>>>>>>>>> PreparedStatement ps_sel = m_cnx.prepareStatement(sel);
>>>>>>>>> ps_sel.setString(1, "");
>>>>>>>>> ResultSet rs = ps_sel.executeQuery();
>>>>>>>>> while (rs.next())
>>>>>>>>> {
>>>>>>>>> System.out.println(rs.getString(1));
>>>>>>>>> }
>>>>>>>>> Returns : 'null param'
>>>>>>>>> */
>>>>>>>>> static void set_param_str_oracle(Item_param *param, uchar
>> **pos,
>>>>>> ulong
>>>>>>>>> len) {
>>>>>>>>> ulong length= get_param_length(pos, len);
>>>>>>>>> if (length == 0)
>>>>>>>>> param->set_null();
>>>>>>>>> else
>>>>>>>>> {
>>>>>>>>> if (length > len)
>>>>>>>>> length= len;
>>>>>>>>> param->set_str((const char *) *pos, length);
>>>>>>>>> *pos+= length;
>>>>>>>>> }
>>>>>>>>> }
>>>>>>>>>
>>>>>>>>> Regards,
>>>>>>>>> Jérôme.
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>> -----Message d'origine-----
>>>>>>>>>> De : Alexander Barkov [mailto:bar@xxxxxxxxxxx] Envoyé : mardi
>>>>>>>>>> 26 septembre 2017 13:34 À : jerome brauge Objet : Re: Patch for
>>>>>>>>>> MDEV-10574
>>>>>>>>>>
>>>>>>>>>> Hello Jérôme,
>>>>>>>>>>
>>>>>>>>>> I'm afraid we cannot accept this change at this point of time.
>>>>>>>>>>
>>>>>>>>>> The problem is that all around the code we assume that NULL is
>>>>>>>>>> not equal to ''.
>>>>>>>>>>
>>>>>>>>>> Changing '' to NULL only in the visible part of the iceberg
>>>>>>>>>> (such as in the parser and in
>>>>>>>>>> Item_str_func::make_empty_result())
>>>>>>>>>> will likely introduce various anomalies in fundamental things
>>>>>>>>>> like joins execution, optimizer, equality propagation, etc.
>>>>>>>>>>
>>>>>>>>>> Sorry, we don't have resources to dive into this topic deeper now.
>>>>>>>>>> Moreover, Oracle's documentation suggests not to reply on the
>>>>>>>>>> fact
>>>>>> that ''
>>>>>>>>>> and NULL are treated as same, as this can change in the future.
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> On 09/20/2017 03:52 PM, jerome brauge wrote:
>>>>>>>>>>> Hello Alexander,
>>>>>>>>>>> Here is a new version of this patch (minor optimizations)
>>>>>>>>>>>
>>>>>>>>>>> Regards,
>>>>>>>>>>> Jérôme.
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>> -----Message d'origine-----
>>>>>>>>>>>> De : Maria-developers [mailto:maria-developers-
>>>>>>>>>>>> bounces+j.brauge=qualiac.com@xxxxxxxxxxxxxxxxxxx] De la part
>>>> de
>>>>>>>>>>>> bounces+jerome
>>>>>>>>>>>> brauge
>>>>>>>>>>>> Envoyé : mardi 19 septembre 2017 16:09 À : 'Alexander Barkov'
>>>>>>>>>>>> Cc : MariaDB Developers
>>>>>>>>>>>> (maria-developers@xxxxxxxxxxxxxxxxxxx)
>>>>>>>>>>>> Objet : [Maria-developers] Patch for MDEV-10574
>>>>>>>>>>>>
>>>>>>>>>>>> Alexander,
>>>>>>>>>>>> Here is the patch I was talking about.
>>>>>>>>>>>> It not address all aspects of MDEV-10574, especially, empty
>>>>>>>>>>>> strings in table column are not view as null.
>>>>>>>>>>>> But as in oracle mode, no empty string in columns should be
>>>>>>>>>>>> created, an fully oracle application will never fall in this case.
>>>>>>>>>>>>
>>>>>>>>>>>> Regards,
>>>>>>>>>>>> Jérôme.
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>>> -----Message d'origine-----
>>>>>>>>>>>>> De : jerome brauge
>>>>>>>>>>>>> Envoyé : mardi 19 septembre 2017 14:13 À : 'Alexander
>> Barkov'
>>>>>>>>>>>>> Objet : RE: Oracle strings functions compatibility : substr
>>>>>>>>>>>>>
>>>>>>>>>>>>> Hello Alexander,
>>>>>>>>>>>>> Yes I'm aware.
>>>>>>>>>>>>> This point will be treated by my next patch which will also
>>>>>>>>>>>>> affect the others string functions (a part of MDEV-10574).
>>>>>>>>>>>>> I will refresh this new patch with the current development
>>>>>>>>>>>>> branch, and I submit to you.
>>>>>>>>>>>>> Regards.
>>>>>>>>>>>>> Jérôme.
>>>>>>>>>>>>>
>>>>>>>>>>>>>> -----Message d'origine----- De : Alexander Barkov
>>>>>>>>>>>>>> [mailto:bar@xxxxxxxxxxx] Envoyé :
>>>> mardi
>>>>>> 19
>>>>>>>>>>>>>> septembre 2017 13:16 À : jerome brauge Objet : Re: Oracle
>>>>>>>>>>>>>> strings functions compatibility : substr
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> Hi Jerome,
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> I noticed one more difference in SUBSTR:
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> SELECT SUBSTR('aaa',1,-1) FROM DUAL;
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> MariaDB returns an empty string.
>>>>>>>>>>>>>> Oracle returns NULL.
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> Here's Oracle's documentation:
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>
>>>>>>
>> https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions162.
>>>>>>>>>>>>>> ht
>>>>>>>>>>>>>> m
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> I think we should do this in the same patch, to avoid
>>>>>>>>>>>>>> behavior change in the future.
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> Would you like to try doing this additional change?
>>>>>>>>>>>>>> Or would you like me to make an incremental patch on top
>> of
>>>>>> yours?
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> Thanks!
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> On 09/19/2017 10:33 AM, jerome brauge wrote:
>>>>>>>>>>>>>>> Hello Alexander,
>>>>>>>>>>>>>>> It's done.
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>> Do you have news from Sergei for MDEV-12874, MDEV-
>> 13417
>>>>>> and
>>>>>>>>>>>> MDEV-
>>>>>>>>>>>>>> 13418 ?
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> I asked Sergei, awaiting for his answer. I'll let you know.
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>> Thank you very much.
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>> -----Message d'origine----- De : Alexander Barkov
>>>>>>>>>>>>>>>> [mailto:bar@xxxxxxxxxxx] Envoyé : lundi
>>>>>>>>>>>>>>>> 18 septembre 2017 18:12 À : jerome brauge Objet : Re:
>>>>>>>>>>>>>>>> Oracle strings functions compatibility : substr
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>> Hello Jerome,
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>> On 09/18/2017 05:14 PM, jerome brauge wrote:
>>>>>>>>>>>>>>>>> Hello Alexander,
>>>>>>>>>>>>>>>>> I don't understand how I've missed such an obvious
>>>> solution !
>>>>>>>>>>>>>>>>> Here is the new patch.
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>> Thanks. Now it looks simpler.
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>> Can you please do one small thing:
>>>>>>>>>>>>>>>> move get_position() from "public:" to "protected:".
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>> Ok to make a pull request after this change.
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>> Thank you very much!
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>> Regards.
>>>>>>>>>>>>>>>>> Jérôme.
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>> -----Message d'origine----- De : Alexander Barkov
>>>>>>>>>>>>>>>>>> [mailto:bar@xxxxxxxxxxx] Envoyé :
>>>>>> lundi
>>>>>>>>>>>>>>>>>> 18 septembre 2017 11:30 À : jerome brauge Objet : Re:
>>>>>> Oracle
>>>>>>>>>>>>>>>>>> strings functions compatibility : substr
>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>> Hello Jerome,
>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>> On 08/17/2017 04:43 PM, jerome brauge wrote:
>>>>>>>>>>>>>>>>>>> Hello Alexander,
>>>>>>>>>>>>>>>>>>> Here is a patch for function SUBSTR in
>> sql_mode=oracle
>>>>>>>>>>>>>>>>>>> (If position is 0,
>>>>>>>>>>>>>>>>>> then it is treated as 1).
>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>> I'm thinking of how to get a smaller patch.
>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>> Wouldn't it be possible to introduce:
>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>> virtual longlong get_position();
>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>> longlong Item_func_substr::get_position() {
>>>>>>>>>>>>>>>>>> return args[1]->val_int(); }
>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>> longlong Item_func_substr_oracle::get_position()
>>>>>>>>>>>>>>>>>> {
>>>>>>>>>>>>>>>>>> longlong pos= args[1]->val_int();
>>>>>>>>>>>>>>>>>> return pos == 0 ? 1 : pos; }
>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>> and just replace all calls for args[1]->val_int() to
>>>>>> get_position()
>>>>>>>> ?
>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>> Thanks!
>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>> Regards,
>>>>>>>>>>>>>>>>>>> Jérôme.
>>>>>>>>>>>>>>>>>>>
diff --git a/mysql-test/include/empty_string_literal.inc b/mysql-test/include/empty_string_literal.inc
new file mode 100644
index 0000000..0b977c5
--- /dev/null
+++ b/mysql-test/include/empty_string_literal.inc
@@ -0,0 +1,106 @@
+SET SESSION character_set_connection=latin2;
+SET SESSION character_set_client=cp1250;
+
+--echo #
+--echo # Test litteral
+--echo #
+SET sql_mode=@mode;
+select @@sql_mode;
+SELECT '',CHARSET(''), null, CHARSET(null), CAST(null as char(10)), CHARSET(CAST(null as char(10))), 'x', CHARSET('x');
+SELECT CHARSET(NULLIF('','')),NULLIF('','');
+SET sql_mode=default;
+SELECT '',CHARSET(''), null, CHARSET(null), CAST(null as char(10)), CHARSET(CAST(null as char(10))), 'x', CHARSET('x');
+SELECT CHARSET(NULLIF('','')),NULLIF('','');
+
+--echo #
+--echo # Test NCHAR litteral
+--echo #
+SET sql_mode=@mode;
+SELECT N'',CHARSET(N''), N'x', CHARSET(N'x');
+SELECT CHARSET(NULLIF(N'',N'')),NULLIF(N'',N'');
+SET sql_mode=default;
+SELECT N'',CHARSET(N''), N'x', CHARSET(N'x');
+SELECT CHARSET(NULLIF(N'',N'')),NULLIF(N'',N'');
+
+--echo #
+--echo # Test CHARSET prefix litteral
+--echo #
+SET sql_mode=@mode;
+SELECT _cp1250 '',CHARSET(_cp1250 ''), _cp1250 'x', CHARSET(_cp1250 'x');
+SELECT CHARSET(NULLIF(_cp1250 '',_cp1250 '')),NULLIF(_cp1250 '',_cp1250 '');
+SET sql_mode=default;
+SELECT _cp1250 '',CHARSET(_cp1250 ''), _cp1250 'x', CHARSET(_cp1250 'x');
+SELECT CHARSET(NULLIF(_cp1250 '',_cp1250 '')),NULLIF(_cp1250 '',_cp1250 '');
+
+
+SET sql_mode=@mode;
+
+--echo #
+--echo # Test litteral concat
+--echo #
+SELECT 'a' 'b';
+SELECT 'a' '';
+SELECT '' 'b';
+SELECT '' '';
+SELECT '' 'b' 'c';
+SELECT '' '' 'c';
+SELECT 'a' '' 'c';
+SELECT 'a' '' '';
+SELECT '' '' '';
+
+SELECT '' '' '',CHARSET('' '' '');
+SELECT _latin1'' '' '',CHARSET(_latin1'' '' '');
+SELECT N'' '' '',CHARSET(N'' '' '');
+
+--echo #
+--echo # UNION - implicit group by
+--echo #
+SELECT 1, null
+UNION
+SELECT 1 , ''
+ORDER BY 1;
+
+SELECT 1, null
+UNION
+SELECT 1 , N''
+ORDER BY 1;
+
+SELECT 1, null
+UNION
+SELECT 1 , _cp1250 ''
+ORDER BY 1;
+
+SELECT NULLIF(_cp1250 '',_cp1250 '')
+UNION
+SELECT NULLIF(N'',N'');
+
+--error ER_CANT_AGGREGATE_2COLLATIONS
+SELECT 1 , _latin2 ''
+UNION
+SELECT 1 , _cp1250 '';
+
+SELECT 1, null
+UNION
+SELECT 1 , ''
+UNION
+SELECT 1 , N'';
+
+CREATE TABLE t1 (c1 INT,c2 VARCHAR(10));
+INSERT INTO t1 VALUES (1,'one');
+INSERT INTO t1 VALUES (1,'');
+INSERT INTO t1 VALUES (1,null);
+
+--echo #
+--echo # Test in a view
+--echo #
+CREATE VIEW v1
+ AS SELECT c1, c2
+ FROM t1
+ UNION
+ SELECT c1 , ''
+ FROM t1
+ ORDER BY 1,2;
+SELECT * FROM v1;
+
+DROP VIEW v1;
+DROP TABLE t1;
\ No newline at end of file
diff --git a/mysql-test/r/empty_string_literal.result b/mysql-test/r/empty_string_literal.result
new file mode 100644
index 0000000..1a4f34b
--- /dev/null
+++ b/mysql-test/r/empty_string_literal.result
@@ -0,0 +1,158 @@
+USE test;
+#
+# MDEV-14013 : sql_mode=EMPTY_STRING_IS_NULL
+#
+set @mode='EMPTY_STRING_IS_NULL';
+SET SESSION character_set_connection=latin2;
+SET SESSION character_set_client=cp1250;
+#
+# Test litteral
+#
+SET sql_mode=@mode;
+select @@sql_mode;
+@@sql_mode
+EMPTY_STRING_IS_NULL
+SELECT '',CHARSET(''), null, CHARSET(null), CAST(null as char(10)), CHARSET(CAST(null as char(10))), 'x', CHARSET('x');
+NULL CHARSET('') NULL CHARSET(null) CAST(null as char(10)) CHARSET(CAST(null as char(10))) x CHARSET('x')
+NULL latin2 NULL binary NULL latin2 x latin2
+SELECT CHARSET(NULLIF('','')),NULLIF('','');
+CHARSET(NULLIF('','')) NULLIF('','')
+latin2 NULL
+SET sql_mode=default;
+SELECT '',CHARSET(''), null, CHARSET(null), CAST(null as char(10)), CHARSET(CAST(null as char(10))), 'x', CHARSET('x');
+ CHARSET('') NULL CHARSET(null) CAST(null as char(10)) CHARSET(CAST(null as char(10))) x CHARSET('x')
+ latin2 NULL binary NULL latin2 x latin2
+SELECT CHARSET(NULLIF('','')),NULLIF('','');
+CHARSET(NULLIF('','')) NULLIF('','')
+latin2 NULL
+#
+# Test NCHAR litteral
+#
+SET sql_mode=@mode;
+SELECT N'',CHARSET(N''), N'x', CHARSET(N'x');
+NULL CHARSET(N'') x CHARSET(N'x')
+NULL utf8 x utf8
+SELECT CHARSET(NULLIF(N'',N'')),NULLIF(N'',N'');
+CHARSET(NULLIF(N'',N'')) NULLIF(N'',N'')
+utf8 NULL
+SET sql_mode=default;
+SELECT N'',CHARSET(N''), N'x', CHARSET(N'x');
+ CHARSET(N'') x CHARSET(N'x')
+ utf8 x utf8
+SELECT CHARSET(NULLIF(N'',N'')),NULLIF(N'',N'');
+CHARSET(NULLIF(N'',N'')) NULLIF(N'',N'')
+utf8 NULL
+#
+# Test CHARSET prefix litteral
+#
+SET sql_mode=@mode;
+SELECT _cp1250 '',CHARSET(_cp1250 ''), _cp1250 'x', CHARSET(_cp1250 'x');
+NULL CHARSET(_cp1250 '') x CHARSET(_cp1250 'x')
+NULL cp1250 x cp1250
+SELECT CHARSET(NULLIF(_cp1250 '',_cp1250 '')),NULLIF(_cp1250 '',_cp1250 '');
+CHARSET(NULLIF(_cp1250 '',_cp1250 '')) NULLIF(_cp1250 '',_cp1250 '')
+cp1250 NULL
+SET sql_mode=default;
+SELECT _cp1250 '',CHARSET(_cp1250 ''), _cp1250 'x', CHARSET(_cp1250 'x');
+ CHARSET(_cp1250 '') x CHARSET(_cp1250 'x')
+ cp1250 x cp1250
+SELECT CHARSET(NULLIF(_cp1250 '',_cp1250 '')),NULLIF(_cp1250 '',_cp1250 '');
+CHARSET(NULLIF(_cp1250 '',_cp1250 '')) NULLIF(_cp1250 '',_cp1250 '')
+cp1250 NULL
+SET sql_mode=@mode;
+#
+# Test litteral concat
+#
+SELECT 'a' 'b';
+a
+ab
+SELECT 'a' '';
+a
+a
+SELECT '' 'b';
+b
+b
+SELECT '' '';
+NULL
+NULL
+SELECT '' 'b' 'c';
+b
+bc
+SELECT '' '' 'c';
+c
+c
+SELECT 'a' '' 'c';
+a
+ac
+SELECT 'a' '' '';
+a
+a
+SELECT '' '' '';
+NULL
+NULL
+SELECT '' '' '',CHARSET('' '' '');
+NULL CHARSET('' '' '')
+NULL latin2
+SELECT _latin1'' '' '',CHARSET(_latin1'' '' '');
+NULL CHARSET(_latin1'' '' '')
+NULL latin1
+SELECT N'' '' '',CHARSET(N'' '' '');
+NULL CHARSET(N'' '' '')
+NULL utf8
+#
+# UNION - implicit group by
+#
+SELECT 1, null
+UNION
+SELECT 1 , ''
+ORDER BY 1;
+1 NULL
+1 NULL
+SELECT 1, null
+UNION
+SELECT 1 , N''
+ORDER BY 1;
+1 NULL
+1 NULL
+SELECT 1, null
+UNION
+SELECT 1 , _cp1250 ''
+ORDER BY 1;
+1 NULL
+1 NULL
+SELECT NULLIF(_cp1250 '',_cp1250 '')
+UNION
+SELECT NULLIF(N'',N'');
+NULLIF(_cp1250 '',_cp1250 '')
+NULL
+SELECT 1 , _latin2 ''
+UNION
+SELECT 1 , _cp1250 '';
+ERROR HY000: Illegal mix of collations (latin2_general_ci,IGNORABLE) and (cp1250_general_ci,IGNORABLE) for operation 'UNION'
+SELECT 1, null
+UNION
+SELECT 1 , ''
+UNION
+SELECT 1 , N'';
+1 NULL
+1 NULL
+CREATE TABLE t1 (c1 INT,c2 VARCHAR(10));
+INSERT INTO t1 VALUES (1,'one');
+INSERT INTO t1 VALUES (1,'');
+INSERT INTO t1 VALUES (1,null);
+#
+# Test in a view
+#
+CREATE VIEW v1
+AS SELECT c1, c2
+FROM t1
+UNION
+SELECT c1 , ''
+ FROM t1
+ORDER BY 1,2;
+SELECT * FROM v1;
+c1 c2
+1 NULL
+1 one
+DROP VIEW v1;
+DROP TABLE t1;
diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result
index 00ec9d9..01a7099 100644
--- a/mysql-test/r/information_schema.result
+++ b/mysql-test/r/information_schema.result
@@ -659,7 +659,7 @@ proc body longblob
proc definer char(141)
proc created timestamp
proc modified timestamp
-proc sql_mode set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH')
+proc sql_mode set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL')
proc comment text
proc character_set_client char(32)
proc collation_connection char(32)
diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result
index 1137466..b1d6996 100644
--- a/mysql-test/r/mysqld--help.result
+++ b/mysql-test/r/mysqld--help.result
@@ -1084,7 +1084,8 @@ The following options may be given as the first argument:
NO_ZERO_DATE, ALLOW_INVALID_DATES,
ERROR_FOR_DIVISION_BY_ZERO, TRADITIONAL,
NO_AUTO_CREATE_USER, HIGH_NOT_PRECEDENCE,
- NO_ENGINE_SUBSTITUTION, PAD_CHAR_TO_FULL_LENGTH
+ NO_ENGINE_SUBSTITUTION, PAD_CHAR_TO_FULL_LENGTH,
+ EMPTY_STRING_IS_NULL
--stack-trace Print a symbolic stack trace on failure
(Defaults to on; use --skip-stack-trace to disable.)
--standard-compliant-cte
diff --git a/mysql-test/r/sql_mode.result b/mysql-test/r/sql_mode.result
index 7c881fd..c6c2414 100644
--- a/mysql-test/r/sql_mode.result
+++ b/mysql-test/r/sql_mode.result
@@ -475,8 +475,8 @@ set sql_mode=16384+(65536*4);
select @@sql_mode;
@@sql_mode
REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_TABLE_OPTIONS,ANSI
-set sql_mode=2147483648*2;
-ERROR 42000: Variable 'sql_mode' can't be set to the value of '4294967296'
+set sql_mode=2147483648*2*2;
+ERROR 42000: Variable 'sql_mode' can't be set to the value of '8589934592'
select @@sql_mode;
@@sql_mode
REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_TABLE_OPTIONS,ANSI
diff --git a/mysql-test/r/system_mysql_db.result b/mysql-test/r/system_mysql_db.result
index b88497d..7ba77e6 100644
--- a/mysql-test/r/system_mysql_db.result
+++ b/mysql-test/r/system_mysql_db.result
@@ -214,7 +214,7 @@ proc CREATE TABLE `proc` (
`definer` char(141) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`created` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
- `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') NOT NULL DEFAULT '',
+ `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL') NOT NULL DEFAULT '',
`comment` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
@@ -239,7 +239,7 @@ event CREATE TABLE `event` (
`ends` datetime DEFAULT NULL,
`status` enum('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL DEFAULT 'ENABLED',
`on_completion` enum('DROP','PRESERVE') NOT NULL DEFAULT 'DROP',
- `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') NOT NULL DEFAULT '',
+ `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL') NOT NULL DEFAULT '',
`comment` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`originator` int(10) unsigned NOT NULL,
`time_zone` char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM',
diff --git a/mysql-test/r/system_mysql_db_fix40123.result b/mysql-test/r/system_mysql_db_fix40123.result
index b88497d..7ba77e6 100644
--- a/mysql-test/r/system_mysql_db_fix40123.result
+++ b/mysql-test/r/system_mysql_db_fix40123.result
@@ -214,7 +214,7 @@ proc CREATE TABLE `proc` (
`definer` char(141) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`created` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
- `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') NOT NULL DEFAULT '',
+ `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL') NOT NULL DEFAULT '',
`comment` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
@@ -239,7 +239,7 @@ event CREATE TABLE `event` (
`ends` datetime DEFAULT NULL,
`status` enum('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL DEFAULT 'ENABLED',
`on_completion` enum('DROP','PRESERVE') NOT NULL DEFAULT 'DROP',
- `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') NOT NULL DEFAULT '',
+ `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL') NOT NULL DEFAULT '',
`comment` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`originator` int(10) unsigned NOT NULL,
`time_zone` char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM',
diff --git a/mysql-test/r/system_mysql_db_fix50030.result b/mysql-test/r/system_mysql_db_fix50030.result
index 14905ab..ad74cf6 100644
--- a/mysql-test/r/system_mysql_db_fix50030.result
+++ b/mysql-test/r/system_mysql_db_fix50030.result
@@ -214,7 +214,7 @@ proc CREATE TABLE `proc` (
`definer` char(141) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`created` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
- `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') NOT NULL DEFAULT '',
+ `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL') NOT NULL DEFAULT '',
`comment` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
@@ -239,7 +239,7 @@ event CREATE TABLE `event` (
`ends` datetime DEFAULT NULL,
`status` enum('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL DEFAULT 'ENABLED',
`on_completion` enum('DROP','PRESERVE') NOT NULL DEFAULT 'DROP',
- `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') NOT NULL DEFAULT '',
+ `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL') NOT NULL DEFAULT '',
`comment` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`originator` int(10) unsigned NOT NULL,
`time_zone` char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM',
diff --git a/mysql-test/r/system_mysql_db_fix50117.result b/mysql-test/r/system_mysql_db_fix50117.result
index b88497d..7ba77e6 100644
--- a/mysql-test/r/system_mysql_db_fix50117.result
+++ b/mysql-test/r/system_mysql_db_fix50117.result
@@ -214,7 +214,7 @@ proc CREATE TABLE `proc` (
`definer` char(141) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`created` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
- `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') NOT NULL DEFAULT '',
+ `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL') NOT NULL DEFAULT '',
`comment` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
@@ -239,7 +239,7 @@ event CREATE TABLE `event` (
`ends` datetime DEFAULT NULL,
`status` enum('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL DEFAULT 'ENABLED',
`on_completion` enum('DROP','PRESERVE') NOT NULL DEFAULT 'DROP',
- `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') NOT NULL DEFAULT '',
+ `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL') NOT NULL DEFAULT '',
`comment` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`originator` int(10) unsigned NOT NULL,
`time_zone` char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM',
diff --git a/mysql-test/suite/compat/oracle/r/empty_string_literal.result b/mysql-test/suite/compat/oracle/r/empty_string_literal.result
new file mode 100644
index 0000000..49b7f44
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/r/empty_string_literal.result
@@ -0,0 +1,158 @@
+USE test;
+#
+# MDEV-14013 : sql_mode=EMPTY_STRING_IS_NULL
+#
+set @mode='ORACLE,EMPTY_STRING_IS_NULL';
+SET SESSION character_set_connection=latin2;
+SET SESSION character_set_client=cp1250;
+#
+# Test litteral
+#
+SET sql_mode=@mode;
+select @@sql_mode;
+@@sql_mode
+PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,EMPTY_STRING_IS_NULL
+SELECT '',CHARSET(''), null, CHARSET(null), CAST(null as char(10)), CHARSET(CAST(null as char(10))), 'x', CHARSET('x');
+NULL CHARSET('') NULL CHARSET(null) CAST(null as char(10)) CHARSET(CAST(null as char(10))) x CHARSET('x')
+NULL latin2 NULL binary NULL latin2 x latin2
+SELECT CHARSET(NULLIF('','')),NULLIF('','');
+CHARSET(NULLIF('','')) NULLIF('','')
+latin2 NULL
+SET sql_mode=default;
+SELECT '',CHARSET(''), null, CHARSET(null), CAST(null as char(10)), CHARSET(CAST(null as char(10))), 'x', CHARSET('x');
+ CHARSET('') NULL CHARSET(null) CAST(null as char(10)) CHARSET(CAST(null as char(10))) x CHARSET('x')
+ latin2 NULL binary NULL latin2 x latin2
+SELECT CHARSET(NULLIF('','')),NULLIF('','');
+CHARSET(NULLIF('','')) NULLIF('','')
+latin2 NULL
+#
+# Test NCHAR litteral
+#
+SET sql_mode=@mode;
+SELECT N'',CHARSET(N''), N'x', CHARSET(N'x');
+NULL CHARSET(N'') x CHARSET(N'x')
+NULL utf8 x utf8
+SELECT CHARSET(NULLIF(N'',N'')),NULLIF(N'',N'');
+CHARSET(NULLIF(N'',N'')) NULLIF(N'',N'')
+utf8 NULL
+SET sql_mode=default;
+SELECT N'',CHARSET(N''), N'x', CHARSET(N'x');
+ CHARSET(N'') x CHARSET(N'x')
+ utf8 x utf8
+SELECT CHARSET(NULLIF(N'',N'')),NULLIF(N'',N'');
+CHARSET(NULLIF(N'',N'')) NULLIF(N'',N'')
+utf8 NULL
+#
+# Test CHARSET prefix litteral
+#
+SET sql_mode=@mode;
+SELECT _cp1250 '',CHARSET(_cp1250 ''), _cp1250 'x', CHARSET(_cp1250 'x');
+NULL CHARSET(_cp1250 '') x CHARSET(_cp1250 'x')
+NULL cp1250 x cp1250
+SELECT CHARSET(NULLIF(_cp1250 '',_cp1250 '')),NULLIF(_cp1250 '',_cp1250 '');
+CHARSET(NULLIF(_cp1250 '',_cp1250 '')) NULLIF(_cp1250 '',_cp1250 '')
+cp1250 NULL
+SET sql_mode=default;
+SELECT _cp1250 '',CHARSET(_cp1250 ''), _cp1250 'x', CHARSET(_cp1250 'x');
+ CHARSET(_cp1250 '') x CHARSET(_cp1250 'x')
+ cp1250 x cp1250
+SELECT CHARSET(NULLIF(_cp1250 '',_cp1250 '')),NULLIF(_cp1250 '',_cp1250 '');
+CHARSET(NULLIF(_cp1250 '',_cp1250 '')) NULLIF(_cp1250 '',_cp1250 '')
+cp1250 NULL
+SET sql_mode=@mode;
+#
+# Test litteral concat
+#
+SELECT 'a' 'b';
+a
+ab
+SELECT 'a' '';
+a
+a
+SELECT '' 'b';
+b
+b
+SELECT '' '';
+NULL
+NULL
+SELECT '' 'b' 'c';
+b
+bc
+SELECT '' '' 'c';
+c
+c
+SELECT 'a' '' 'c';
+a
+ac
+SELECT 'a' '' '';
+a
+a
+SELECT '' '' '';
+NULL
+NULL
+SELECT '' '' '',CHARSET('' '' '');
+NULL CHARSET('' '' '')
+NULL latin2
+SELECT _latin1'' '' '',CHARSET(_latin1'' '' '');
+NULL CHARSET(_latin1'' '' '')
+NULL latin1
+SELECT N'' '' '',CHARSET(N'' '' '');
+NULL CHARSET(N'' '' '')
+NULL utf8
+#
+# UNION - implicit group by
+#
+SELECT 1, null
+UNION
+SELECT 1 , ''
+ORDER BY 1;
+1 NULL
+1 NULL
+SELECT 1, null
+UNION
+SELECT 1 , N''
+ORDER BY 1;
+1 NULL
+1 NULL
+SELECT 1, null
+UNION
+SELECT 1 , _cp1250 ''
+ORDER BY 1;
+1 NULL
+1 NULL
+SELECT NULLIF(_cp1250 '',_cp1250 '')
+UNION
+SELECT NULLIF(N'',N'');
+NULLIF(_cp1250 '',_cp1250 '')
+NULL
+SELECT 1 , _latin2 ''
+UNION
+SELECT 1 , _cp1250 '';
+ERROR HY000: Illegal mix of collations (latin2_general_ci,IGNORABLE) and (cp1250_general_ci,IGNORABLE) for operation 'UNION'
+SELECT 1, null
+UNION
+SELECT 1 , ''
+UNION
+SELECT 1 , N'';
+1 NULL
+1 NULL
+CREATE TABLE t1 (c1 INT,c2 VARCHAR(10));
+INSERT INTO t1 VALUES (1,'one');
+INSERT INTO t1 VALUES (1,'');
+INSERT INTO t1 VALUES (1,null);
+#
+# Test in a view
+#
+CREATE VIEW v1
+AS SELECT c1, c2
+FROM t1
+UNION
+SELECT c1 , ''
+ FROM t1
+ORDER BY 1,2;
+SELECT * FROM v1;
+c1 c2
+1 NULL
+1 one
+DROP VIEW v1;
+DROP TABLE t1;
diff --git a/mysql-test/suite/compat/oracle/t/empty_string_literal.test b/mysql-test/suite/compat/oracle/t/empty_string_literal.test
new file mode 100644
index 0000000..3c612f7
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/t/empty_string_literal.test
@@ -0,0 +1,8 @@
+USE test;
+--echo #
+--echo # MDEV-14013 : sql_mode=EMPTY_STRING_IS_NULL
+--echo #
+
+set @mode='ORACLE,EMPTY_STRING_IS_NULL';
+
+--source include/empty_string_literal.inc
diff --git a/mysql-test/suite/funcs_1/r/is_columns_mysql.result b/mysql-test/suite/funcs_1/r/is_columns_mysql.result
index d021c73..21ffae9 100644
--- a/mysql-test/suite/funcs_1/r/is_columns_mysql.result
+++ b/mysql-test/suite/funcs_1/r/is_columns_mysql.result
@@ -60,7 +60,7 @@ def mysql event modified 9 '0000-00-00 00:00:00' NO timestamp NULL NULL NULL NUL
def mysql event name 2 '' NO char 64 192 NULL NULL NULL utf8 utf8_general_ci char(64) PRI select,insert,update,references NEVER NULL
def mysql event on_completion 14 'DROP' NO enum 8 24 NULL NULL NULL utf8 utf8_general_ci enum('DROP','PRESERVE') select,insert,update,references NEVER NULL
def mysql event originator 17 NULL NO int NULL NULL 10 0 NULL NULL NULL int(10) unsigned select,insert,update,references NEVER NULL
-def mysql event sql_mode 15 '' NO set 494 1482 NULL NULL NULL utf8 utf8_general_ci set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') select,insert,update,references NEVER NULL
+def mysql event sql_mode 15 '' NO set 515 1545 NULL NULL NULL utf8 utf8_general_ci set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL') select,insert,update,references NEVER NULL
def mysql event starts 11 NULL YES datetime NULL NULL NULL NULL 0 NULL NULL datetime select,insert,update,references NEVER NULL
def mysql event status 13 'ENABLED' NO enum 18 54 NULL NULL NULL utf8 utf8_general_ci enum('ENABLED','DISABLED','SLAVESIDE_DISABLED') select,insert,update,references NEVER NULL
def mysql event time_zone 18 'SYSTEM' NO char 64 64 NULL NULL NULL latin1 latin1_swedish_ci char(64) select,insert,update,references NEVER NULL
@@ -151,7 +151,7 @@ def mysql proc returns 10 NULL NO longblob 4294967295 4294967295 NULL NULL NULL
def mysql proc security_type 8 'DEFINER' NO enum 7 21 NULL NULL NULL utf8 utf8_general_ci enum('INVOKER','DEFINER') select,insert,update,references NEVER NULL
def mysql proc specific_name 4 '' NO char 64 192 NULL NULL NULL utf8 utf8_general_ci char(64) select,insert,update,references NEVER NULL
def mysql proc sql_data_access 6 'CONTAINS_SQL' NO enum 17 51 NULL NULL NULL utf8 utf8_general_ci enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA') select,insert,update,references NEVER NULL
-def mysql proc sql_mode 15 '' NO set 494 1482 NULL NULL NULL utf8 utf8_general_ci set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') select,insert,update,references NEVER NULL
+def mysql proc sql_mode 15 '' NO set 515 1545 NULL NULL NULL utf8 utf8_general_ci set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL') select,insert,update,references NEVER NULL
def mysql proc type 3 NULL NO enum 9 27 NULL NULL NULL utf8 utf8_general_ci enum('FUNCTION','PROCEDURE') PRI select,insert,update,references NEVER NULL
def mysql procs_priv Db 2 '' NO char 64 192 NULL NULL NULL utf8 utf8_bin char(64) PRI select,insert,update,references NEVER NULL
def mysql procs_priv Grantor 6 '' NO char 141 423 NULL NULL NULL utf8 utf8_bin char(141) MUL select,insert,update,references NEVER NULL
@@ -391,7 +391,7 @@ NULL mysql event starts datetime NULL NULL NULL NULL datetime
NULL mysql event ends datetime NULL NULL NULL NULL datetime
3.0000 mysql event status enum 18 54 utf8 utf8_general_ci enum('ENABLED','DISABLED','SLAVESIDE_DISABLED')
3.0000 mysql event on_completion enum 8 24 utf8 utf8_general_ci enum('DROP','PRESERVE')
-3.0000 mysql event sql_mode set 494 1482 utf8 utf8_general_ci set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH')
+3.0000 mysql event sql_mode set 515 1545 utf8 utf8_general_ci set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL')
3.0000 mysql event comment char 64 192 utf8 utf8_bin char(64)
NULL mysql event originator int NULL NULL NULL NULL int(10) unsigned
1.0000 mysql event time_zone char 64 64 latin1 latin1_swedish_ci char(64)
@@ -482,7 +482,7 @@ NULL mysql innodb_table_stats sum_of_other_index_sizes bigint NULL NULL NULL NUL
3.0000 mysql proc definer char 141 423 utf8 utf8_bin char(141)
NULL mysql proc created timestamp NULL NULL NULL NULL timestamp
NULL mysql proc modified timestamp NULL NULL NULL NULL timestamp
-3.0000 mysql proc sql_mode set 494 1482 utf8 utf8_general_ci set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH')
+3.0000 mysql proc sql_mode set 515 1545 utf8 utf8_general_ci set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL')
1.0000 mysql proc comment text 65535 65535 utf8 utf8_bin text
3.0000 mysql proc character_set_client char 32 96 utf8 utf8_bin char(32)
3.0000 mysql proc collation_connection char 32 96 utf8 utf8_bin char(32)
diff --git a/mysql-test/suite/sys_vars/r/sql_mode_basic.result b/mysql-test/suite/sys_vars/r/sql_mode_basic.result
index 1bd35c0..3bd8133 100644
--- a/mysql-test/suite/sys_vars/r/sql_mode_basic.result
+++ b/mysql-test/suite/sys_vars/r/sql_mode_basic.result
@@ -362,12 +362,12 @@ SET @@global.sql_mode = 500000;
SELECT @@global.sql_mode;
@@global.sql_mode
REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,POSTGRESQL,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,MYSQL323,MYSQL40,ANSI,HIGH_NOT_PRECEDENCE
-SET @@global.sql_mode = 4294967295;
+SET @@global.sql_mode = 8589934591;
SELECT @@global.sql_mode;
@@global.sql_mode
-REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,IGNORE_BAD_TABLE_OPTIONS,ONLY_FULL_GROUP_BY,NO_UNSIGNED_SUBTRACTION,NO_DIR_IN_CREATE,POSTGRESQL,ORACLE,MSSQL,DB2,MAXDB,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,MYSQL323,MYSQL40,ANSI,NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,HIGH_NOT_PRECEDENCE,NO_ENGINE_SUBSTITUTION,PAD_CHAR_TO_FULL_LENGTH
-SET @@global.sql_mode = 4294967296;
-ERROR 42000: Variable 'sql_mode' can't be set to the value of '4294967296'
+REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,IGNORE_BAD_TABLE_OPTIONS,ONLY_FULL_GROUP_BY,NO_UNSIGNED_SUBTRACTION,NO_DIR_IN_CREATE,POSTGRESQL,ORACLE,MSSQL,DB2,MAXDB,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,MYSQL323,MYSQL40,ANSI,NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,HIGH_NOT_PRECEDENCE,NO_ENGINE_SUBSTITUTION,PAD_CHAR_TO_FULL_LENGTH,EMPTY_STRING_IS_NULL
+SET @@global.sql_mode = 8589934592;
+ERROR 42000: Variable 'sql_mode' can't be set to the value of '8589934592'
SET @@global.sql_mode = 0.4;
ERROR 42000: Incorrect argument type to variable 'sql_mode'
'#---------------------FN_DYNVARS_152_08----------------------#'
diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
index bc822f5..72032a8 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
@@ -4375,7 +4375,7 @@ VARIABLE_COMMENT Sets the sql mode
NUMERIC_MIN_VALUE NULL
NUMERIC_MAX_VALUE NULL
NUMERIC_BLOCK_SIZE NULL
-ENUM_VALUE_LIST REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,IGNORE_BAD_TABLE_OPTIONS,ONLY_FULL_GROUP_BY,NO_UNSIGNED_SUBTRACTION,NO_DIR_IN_CREATE,POSTGRESQL,ORACLE,MSSQL,DB2,MAXDB,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,MYSQL323,MYSQL40,ANSI,NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,HIGH_NOT_PRECEDENCE,NO_ENGINE_SUBSTITUTION,PAD_CHAR_TO_FULL_LENGTH
+ENUM_VALUE_LIST REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,IGNORE_BAD_TABLE_OPTIONS,ONLY_FULL_GROUP_BY,NO_UNSIGNED_SUBTRACTION,NO_DIR_IN_CREATE,POSTGRESQL,ORACLE,MSSQL,DB2,MAXDB,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,MYSQL323,MYSQL40,ANSI,NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,HIGH_NOT_PRECEDENCE,NO_ENGINE_SUBSTITUTION,PAD_CHAR_TO_FULL_LENGTH,EMPTY_STRING_IS_NULL
READ_ONLY NO
COMMAND_LINE_ARGUMENT REQUIRED
VARIABLE_NAME SQL_NOTES
diff --git a/mysql-test/suite/sys_vars/t/sql_mode_basic.test b/mysql-test/suite/sys_vars/t/sql_mode_basic.test
index ad1c722..1730af5 100644
--- a/mysql-test/suite/sys_vars/t/sql_mode_basic.test
+++ b/mysql-test/suite/sys_vars/t/sql_mode_basic.test
@@ -307,11 +307,11 @@ SELECT @@global.sql_mode;
SET @@global.sql_mode = 500000;
SELECT @@global.sql_mode;
-SET @@global.sql_mode = 4294967295;
+SET @@global.sql_mode = 8589934591;
SELECT @@global.sql_mode;
--Error ER_WRONG_VALUE_FOR_VAR
-SET @@global.sql_mode = 4294967296;
+SET @@global.sql_mode = 8589934592;
# use of decimal values
diff --git a/mysql-test/t/empty_string_literal.test b/mysql-test/t/empty_string_literal.test
new file mode 100644
index 0000000..71e98d8
--- /dev/null
+++ b/mysql-test/t/empty_string_literal.test
@@ -0,0 +1,8 @@
+USE test;
+--echo #
+--echo # MDEV-14013 : sql_mode=EMPTY_STRING_IS_NULL
+--echo #
+
+set @mode='EMPTY_STRING_IS_NULL';
+
+--source include/empty_string_literal.inc
diff --git a/mysql-test/t/sql_mode.test b/mysql-test/t/sql_mode.test
index 97a694f..31e38db 100644
--- a/mysql-test/t/sql_mode.test
+++ b/mysql-test/t/sql_mode.test
@@ -264,7 +264,7 @@ select @@sql_mode;
set sql_mode=16384+(65536*4);
select @@sql_mode;
--error 1231
-set sql_mode=2147483648*2; # that mode does not exist
+set sql_mode=2147483648*2*2; # that mode does not exist
select @@sql_mode;
#
diff --git a/scripts/mysql_system_tables.sql b/scripts/mysql_system_tables.sql
index 7b61416..edc4817 100644
--- a/scripts/mysql_system_tables.sql
+++ b/scripts/mysql_system_tables.sql
@@ -80,7 +80,7 @@ CREATE TABLE IF NOT EXISTS time_zone_transition_type ( Time_zone_id int unsign
CREATE TABLE IF NOT EXISTS time_zone_leap_second ( Transition_time bigint signed NOT NULL, Correction int signed NOT NULL, PRIMARY KEY TranTime (Transition_time) ) engine=MyISAM CHARACTER SET utf8 comment='Leap seconds information for time zones';
-CREATE TABLE IF NOT EXISTS proc (db char(64) collate utf8_bin DEFAULT '' NOT NULL, name char(64) DEFAULT '' NOT NULL, type enum('FUNCTION','PROCEDURE') NOT NULL, specific_name char(64) DEFAULT '' NOT NULL, language enum('SQL') DEFAULT 'SQL' NOT NULL, sql_data_access enum( 'CONTAINS_SQL', 'NO_SQL', 'READS_SQL_DATA', 'MODIFIES_SQL_DATA') DEFAULT 'CONTAINS_SQL' NOT NULL, is_deterministic enum('YES','NO') DEFAULT 'NO' NOT NULL, security_type enum('INVOKER','DEFINER') DEFAULT 'DEFINER' NOT NULL, param_list blob NOT NULL, returns longblob NOT NULL, body longblob NOT NULL, definer char(141) collate utf8_bin DEFAULT '' NOT NULL, created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', sql_mode set( 'REAL_AS_FLOAT', 'PIPES_AS_CONCAT', 'ANSI_QUOTES', 'IGNORE_SPACE', 'IGNORE_BAD_TABLE_OPTIONS', 'ONLY_FULL_GROUP_BY', 'NO_UNSIGNED_SUBTRACTION', 'NO_DIR_IN_CREATE', 'POSTGRESQL', 'ORACLE', 'MSSQL', 'DB2', 'MAXDB', 'NO_KEY_OPTIONS', 'NO_TABLE_OPTIONS', 'NO_FIELD_OPTIONS', 'MYSQL323', 'MYSQL40', 'ANSI', 'NO_AUTO_VALUE_ON_ZERO', 'NO_BACKSLASH_ESCAPES', 'STRICT_TRANS_TABLES', 'STRICT_ALL_TABLES', 'NO_ZERO_IN_DATE', 'NO_ZERO_DATE', 'INVALID_DATES', 'ERROR_FOR_DIVISION_BY_ZERO', 'TRADITIONAL', 'NO_AUTO_CREATE_USER', 'HIGH_NOT_PRECEDENCE', 'NO_ENGINE_SUBSTITUTION', 'PAD_CHAR_TO_FULL_LENGTH') DEFAULT '' NOT NULL, comment text collate utf8_bin NOT NULL, character_set_client char(32) collate utf8_bin, collation_connection char(32) collate utf8_bin, db_collation char(32) collate utf8_bin, body_utf8 longblob, PRIMARY KEY (db,name,type)) engine=MyISAM character set utf8 comment='Stored Procedures';
+CREATE TABLE IF NOT EXISTS proc (db char(64) collate utf8_bin DEFAULT '' NOT NULL, name char(64) DEFAULT '' NOT NULL, type enum('FUNCTION','PROCEDURE') NOT NULL, specific_name char(64) DEFAULT '' NOT NULL, language enum('SQL') DEFAULT 'SQL' NOT NULL, sql_data_access enum( 'CONTAINS_SQL', 'NO_SQL', 'READS_SQL_DATA', 'MODIFIES_SQL_DATA') DEFAULT 'CONTAINS_SQL' NOT NULL, is_deterministic enum('YES','NO') DEFAULT 'NO' NOT NULL, security_type enum('INVOKER','DEFINER') DEFAULT 'DEFINER' NOT NULL, param_list blob NOT NULL, returns longblob NOT NULL, body longblob NOT NULL, definer char(141) collate utf8_bin DEFAULT '' NOT NULL, created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', sql_mode set( 'REAL_AS_FLOAT', 'PIPES_AS_CONCAT', 'ANSI_QUOTES', 'IGNORE_SPACE', 'IGNORE_BAD_TABLE_OPTIONS', 'ONLY_FULL_GROUP_BY', 'NO_UNSIGNED_SUBTRACTION', 'NO_DIR_IN_CREATE', 'POSTGRESQL', 'ORACLE', 'MSSQL', 'DB2', 'MAXDB', 'NO_KEY_OPTIONS', 'NO_TABLE_OPTIONS', 'NO_FIELD_OPTIONS', 'MYSQL323', 'MYSQL40', 'ANSI', 'NO_AUTO_VALUE_ON_ZERO', 'NO_BACKSLASH_ESCAPES', 'STRICT_TRANS_TABLES', 'STRICT_ALL_TABLES', 'NO_ZERO_IN_DATE', 'NO_ZERO_DATE', 'INVALID_DATES', 'ERROR_FOR_DIVISION_BY_ZERO', 'TRADITIONAL', 'NO_AUTO_CREATE_USER', 'HIGH_NOT_PRECEDENCE', 'NO_ENGINE_SUBSTITUTION', 'PAD_CHAR_TO_FULL_LENGTH', 'EMPTY_STRING_IS_NULL') DEFAULT '' NOT NULL, comment text collate utf8_bin NOT NULL, character_set_client char(32) collate utf8_bin, collation_connection char(32) collate utf8_bin, db_collation char(32) collate utf8_bin, body_utf8 longblob, PRIMARY KEY (db,name,type)) engine=MyISAM character set utf8 comment='Stored Procedures';
CREATE TABLE IF NOT EXISTS procs_priv ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(80) binary DEFAULT '' NOT NULL, Routine_name char(64) COLLATE utf8_general_ci DEFAULT '' NOT NULL, Routine_type enum('FUNCTION','PROCEDURE') NOT NULL, Grantor char(141) DEFAULT '' NOT NULL, Proc_priv set('Execute','Alter Routine','Grant') COLLATE utf8_general_ci DEFAULT '' NOT NULL, Timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (Host,Db,User,Routine_name,Routine_type), KEY Grantor (Grantor) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Procedure privileges';
@@ -101,7 +101,7 @@ PREPARE stmt FROM @str;
EXECUTE stmt;
DROP PREPARE stmt;
-CREATE TABLE IF NOT EXISTS event ( db char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', name char(64) CHARACTER SET utf8 NOT NULL default '', body longblob NOT NULL, definer char(141) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', execute_at DATETIME default NULL, interval_value int(11) default NULL, interval_field ENUM('YEAR','QUARTER','MONTH','DAY','HOUR','MINUTE','WEEK','SECOND','MICROSECOND','YEAR_MONTH','DAY_HOUR','DAY_MINUTE','DAY_SECOND','HOUR_MINUTE','HOUR_SECOND','MINUTE_SECOND','DAY_MICROSECOND','HOUR_MICROSECOND','MINUTE_MICROSECOND','SECOND_MICROSECOND') default NULL, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, modified TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00', last_executed DATETIME default NULL, starts DATETIME default NULL, ends DATETIME default NULL, status ENUM('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL default 'ENABLED', on_completion ENUM('DROP','PRESERVE') NOT NULL default 'DROP', sql_mode set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') DEFAULT '' NOT NULL, comment char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', originator INTEGER UNSIGNED NOT NULL, time_zone char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM', character_set_client char(32) collate utf8_bin, collation_connection char(32) collate utf8_bin, db_collation char(32) collate utf8_bin, body_utf8 longblob, PRIMARY KEY (db, name) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT 'Events';
+CREATE TABLE IF NOT EXISTS event ( db char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', name char(64) CHARACTER SET utf8 NOT NULL default '', body longblob NOT NULL, definer char(141) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', execute_at DATETIME default NULL, interval_value int(11) default NULL, interval_field ENUM('YEAR','QUARTER','MONTH','DAY','HOUR','MINUTE','WEEK','SECOND','MICROSECOND','YEAR_MONTH','DAY_HOUR','DAY_MINUTE','DAY_SECOND','HOUR_MINUTE','HOUR_SECOND','MINUTE_SECOND','DAY_MICROSECOND','HOUR_MICROSECOND','MINUTE_MICROSECOND','SECOND_MICROSECOND') default NULL, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, modified TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00', last_executed DATETIME default NULL, starts DATETIME default NULL, ends DATETIME default NULL, status ENUM('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL default 'ENABLED', on_completion ENUM('DROP','PRESERVE') NOT NULL default 'DROP', sql_mode set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL') DEFAULT '' NOT NULL, comment char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', originator INTEGER UNSIGNED NOT NULL, time_zone char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM', character_set_client char(32) collate utf8_bin, collation_connection char(32) collate utf8_bin, db_collation char(32) collate utf8_bin, body_utf8 longblob, PRIMARY KEY (db, name) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT 'Events';
SET @create_innodb_table_stats="CREATE TABLE IF NOT EXISTS innodb_table_stats (
database_name VARCHAR(64) NOT NULL,
diff --git a/scripts/mysql_system_tables_fix.sql b/scripts/mysql_system_tables_fix.sql
index 2a826bb..10e2dcf 100644
--- a/scripts/mysql_system_tables_fix.sql
+++ b/scripts/mysql_system_tables_fix.sql
@@ -448,7 +448,8 @@ ALTER TABLE proc MODIFY name char(64) DEFAULT '' NOT NULL,
'NO_AUTO_CREATE_USER',
'HIGH_NOT_PRECEDENCE',
'NO_ENGINE_SUBSTITUTION',
- 'PAD_CHAR_TO_FULL_LENGTH'
+ 'PAD_CHAR_TO_FULL_LENGTH',
+ 'EMPTY_STRING_IS_NULL'
) DEFAULT '' NOT NULL,
DEFAULT CHARACTER SET utf8;
@@ -572,7 +573,8 @@ ALTER TABLE event MODIFY sql_mode
'NO_AUTO_CREATE_USER',
'HIGH_NOT_PRECEDENCE',
'NO_ENGINE_SUBSTITUTION',
- 'PAD_CHAR_TO_FULL_LENGTH'
+ 'PAD_CHAR_TO_FULL_LENGTH',
+ 'EMPTY_STRING_IS_NULL'
) DEFAULT '' NOT NULL AFTER on_completion;
ALTER TABLE event MODIFY name char(64) CHARACTER SET utf8 NOT NULL default '';
diff --git a/sql/event_data_objects.cc b/sql/event_data_objects.cc
index 4df9817..7b08d17 100644
--- a/sql/event_data_objects.cc
+++ b/sql/event_data_objects.cc
@@ -431,7 +431,7 @@ Event_job_data::load_from_row(THD *thd, TABLE *table)
definer_host.str= strmake_root(&mem_root, ptr + 1, len);
definer_host.length= len;
- sql_mode= (ulong) table->field[ET_FIELD_SQL_MODE]->val_int();
+ sql_mode= (sql_mode_t) table->field[ET_FIELD_SQL_MODE]->val_int();
DBUG_RETURN(FALSE);
}
@@ -637,7 +637,7 @@ Event_timed::load_from_row(THD *thd, TABLE *table)
else
comment.length= 0;
- sql_mode= (ulong) table->field[ET_FIELD_SQL_MODE]->val_int();
+ sql_mode= (sql_mode_t) table->field[ET_FIELD_SQL_MODE]->val_int();
DBUG_RETURN(FALSE);
}
diff --git a/sql/partition_info.cc b/sql/partition_info.cc
index 31e48a8..cc6553f 100644
--- a/sql/partition_info.cc
+++ b/sql/partition_info.cc
@@ -2347,7 +2347,7 @@ bool partition_info::fix_column_value_functions(THD *thd,
{
uchar *val_ptr;
uint len= field->pack_length();
- ulonglong save_sql_mode;
+ sql_mode_t save_sql_mode;
bool save_got_warning;
if (!(column_item= get_column_item(column_item,
diff --git a/sql/sp.cc b/sql/sp.cc
index c8b9e02..a275781 100644
--- a/sql/sp.cc
+++ b/sql/sp.cc
@@ -173,7 +173,8 @@ TABLE_FIELD_TYPE proc_table_fields[MYSQL_PROC_FIELD_COUNT] =
"'ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES',"
"'STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES',"
"'ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER',"
- "'HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH')") },
+ "'HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH',"
+ "'EMPTY_STRING_IS_NULL')") },
{ NULL, 0 }
},
{
@@ -688,7 +689,7 @@ Sp_handler::db_find_routine(THD *thd,
// Get additional information
modified= table->field[MYSQL_PROC_FIELD_MODIFIED]->val_int();
created= table->field[MYSQL_PROC_FIELD_CREATED]->val_int();
- sql_mode= (ulong) table->field[MYSQL_PROC_FIELD_SQL_MODE]->val_int();
+ sql_mode= (sql_mode_t) table->field[MYSQL_PROC_FIELD_SQL_MODE]->val_int();
creation_ctx= Stored_routine_creation_ctx::load_from_db(thd, name, table);
diff --git a/sql/sp_head.cc b/sql/sp_head.cc
index 645703d..a6377b3 100644
--- a/sql/sp_head.cc
+++ b/sql/sp_head.cc
@@ -994,7 +994,7 @@ sp_head::execute(THD *thd, bool merge_da_on_success)
sp_rcontext *ctx= thd->spcont;
bool err_status= FALSE;
uint ip= 0;
- ulonglong save_sql_mode;
+ sql_mode_t save_sql_mode;
bool save_abort_on_warning;
Query_arena *old_arena;
/* per-instruction arena */
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index b40ee63..d6ba762 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -2389,9 +2389,11 @@ bool THD::convert_string(String *s, CHARSET_INFO *from_cs, CHARSET_INFO *to_cs)
}
-Item_string *THD::make_string_literal(const char *str, size_t length,
- uint repertoire)
+Item *THD::make_string_literal(const char *str, size_t length,
+ uint repertoire)
{
+ if (!length && (variables.sql_mode & MODE_EMPTY_STRING_IS_NULL))
+ return new (mem_root) Item_null(this, 0, variables.collation_connection);
if (!charset_is_collation_connection &&
(repertoire != MY_REPERTOIRE_ASCII ||
!my_charset_is_ascii_based(variables.collation_connection)))
@@ -2409,6 +2411,57 @@ Item_string *THD::make_string_literal(const char *str, size_t length,
}
+Item *THD::make_string_literal_nchar(const Lex_string_with_metadata_st &str)
+{
+ DBUG_ASSERT(my_charset_is_ascii_based(national_charset_info));
+ if (!str.length && (variables.sql_mode & MODE_EMPTY_STRING_IS_NULL))
+ return new (mem_root) Item_null(this, 0, national_charset_info);
+
+ return new (mem_root) Item_string(this, str.str, str.length,
+ national_charset_info,
+ DERIVATION_COERCIBLE,
+ str.repertoire());
+}
+
+
+Item *THD::make_string_literal_charset(const Lex_string_with_metadata_st &str,
+ CHARSET_INFO *cs)
+{
+ if (!str.length && (variables.sql_mode & MODE_EMPTY_STRING_IS_NULL))
+ return new (mem_root) Item_null(this, 0, cs);
+ return new (mem_root) Item_string_with_introducer(this,
+ str.str, str.length, cs);
+}
+
+
+Item *THD::make_string_literal_concat(Item *item, const LEX_CSTRING &str)
+{
+ if (item->type() == Item::NULL_ITEM)
+ {
+ DBUG_ASSERT(variables.sql_mode & MODE_EMPTY_STRING_IS_NULL);
+ if (str.length)
+ {
+ CHARSET_INFO *cs= variables.collation_connection;
+ uint repertoire= my_string_repertoire(cs, str.str, str.length);
+ return new (mem_root) Item_string(this, str.str, str.length, cs,
+ DERIVATION_COERCIBLE, repertoire);
+ }
+ return item;
+ }
+
+ DBUG_ASSERT(item->type() == Item::STRING_ITEM);
+ DBUG_ASSERT(item->basic_const_item());
+ static_cast<Item_string*>(item)->append(str.str, str.length);
+ if (!(item->collation.repertoire & MY_REPERTOIRE_EXTENDED))
+ {
+ // If the string has been pure ASCII so far, check the new part.
+ CHARSET_INFO *cs= variables.collation_connection;
+ item->collation.repertoire|= my_string_repertoire(cs, str.str, str.length);
+ }
+ return item;
+}
+
+
/*
Update some cache variables when character set changes
*/
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 96a3cc3..432a174 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -139,6 +139,7 @@ enum enum_binlog_row_image {
#define MODE_HIGH_NOT_PRECEDENCE (1ULL << 29)
#define MODE_NO_ENGINE_SUBSTITUTION (1ULL << 30)
#define MODE_PAD_CHAR_TO_FULL_LENGTH (1ULL << 31)
+#define MODE_EMPTY_STRING_IS_NULL (1ULL << 32)
/* Bits for different old style modes */
#define OLD_MODE_NO_DUP_KEY_WARNINGS_WITH_IGNORE (1 << 0)
@@ -3473,14 +3474,17 @@ class THD :public Statement,
@param length - length of the string
@param repertoire - the repertoire of the string
*/
- Item_string *make_string_literal(const char *str, size_t length,
- uint repertoire);
- Item_string *make_string_literal(const Lex_string_with_metadata_st &str)
+ Item *make_string_literal(const char *str, size_t length,
+ uint repertoire);
+ Item *make_string_literal(const Lex_string_with_metadata_st &str)
{
uint repertoire= str.repertoire(variables.character_set_client);
return make_string_literal(str.str, str.length, repertoire);
}
-
+ Item *make_string_literal_nchar(const Lex_string_with_metadata_st &str);
+ Item *make_string_literal_charset(const Lex_string_with_metadata_st &str,
+ CHARSET_INFO *cs);
+ Item *make_string_literal_concat(Item *item1, const LEX_CSTRING &str);
void add_changed_table(TABLE *table);
void add_changed_table(const char *key, long key_length);
CHANGED_TABLE_LIST * changed_table_dup(const char *key, long key_length);
diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index 9de0a6d..793f9fa 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -725,13 +725,36 @@ void set_param_date(Item_param *param, uchar **pos, ulong len)
#endif /*!EMBEDDED_LIBRARY*/
-static void set_param_str(Item_param *param, uchar **pos, ulong len)
+static void set_param_str_or_null(Item_param *param, uchar **pos, ulong len,
+ bool empty_string_is_null)
{
ulong length= get_param_length(pos, len);
- if (length > len)
- length= len;
- param->set_str((const char *)*pos, length);
- *pos+= length;
+ if (length == 0 && empty_string_is_null)
+ param->set_null();
+ else
+ {
+ if (length > len)
+ length= len;
+ param->set_str((const char *) *pos, length);
+ *pos+= length;
+ }
+}
+
+
+static void set_param_str(Item_param *param, uchar **pos, ulong len)
+{
+ set_param_str_or_null(param, pos, len, false);
+}
+
+
+/*
+ set_param_str_empty_is_null : bind empty string as null value
+ when sql_mode=MODE_EMPTY_STRING_IS_NULL
+*/
+static void set_param_str_empty_is_null(Item_param *param, uchar **pos,
+ ulong len)
+{
+ set_param_str_or_null(param, pos, len, true);
}
@@ -806,7 +829,10 @@ static void setup_one_conversion_function(THD *thd, Item_param *param,
param->value.cs_info.final_character_set_of_str_value=
String::needs_conversion(0, fromcs, tocs, &dummy_offset) ?
tocs : fromcs;
- param->set_param_func= set_param_str;
+
+ param->set_param_func=
+ (thd->variables.sql_mode & MODE_EMPTY_STRING_IS_NULL) ?
+ set_param_str_empty_is_null : set_param_str;
/*
Exact value of max_length is not known unless data is converted to
charset of connection, so we have to set it later.
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index e6cebaa..3505d10 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -13850,36 +13850,18 @@ text_literal:
}
| NCHAR_STRING
{
- DBUG_ASSERT(my_charset_is_ascii_based(national_charset_info));
- $$= new (thd->mem_root) Item_string(thd, $1.str, $1.length,
- national_charset_info,
- DERIVATION_COERCIBLE,
- $1.repertoire());
- if ($$ == NULL)
+ if (!($$= thd->make_string_literal_nchar($1)))
MYSQL_YYABORT;
}
| UNDERSCORE_CHARSET TEXT_STRING
{
- $$= new (thd->mem_root) Item_string_with_introducer(thd, $2.str,
- $2.length, $1);
- if ($$ == NULL)
+ if (!($$= thd->make_string_literal_charset($2, $1)))
MYSQL_YYABORT;
}
| text_literal TEXT_STRING_literal
{
- Item_string* item= (Item_string*) $1;
- item->append($2.str, $2.length);
- if (!(item->collation.repertoire & MY_REPERTOIRE_EXTENDED))
- {
- /*
- If the string has been pure ASCII so far,
- check the new part.
- */
- CHARSET_INFO *cs= thd->variables.collation_connection;
- item->collation.repertoire|= my_string_repertoire(cs,
- $2.str,
- $2.length);
- }
+ if (!($$= thd->make_string_literal_concat($1, $2)))
+ MYSQL_YYABORT;
}
;
diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy
index 7f8da10..9dbad20 100644
--- a/sql/sql_yacc_ora.yy
+++ b/sql/sql_yacc_ora.yy
@@ -13913,36 +13913,18 @@ text_literal:
}
| NCHAR_STRING
{
- DBUG_ASSERT(my_charset_is_ascii_based(national_charset_info));
- $$= new (thd->mem_root) Item_string(thd, $1.str, $1.length,
- national_charset_info,
- DERIVATION_COERCIBLE,
- $1.repertoire());
- if ($$ == NULL)
+ if (!($$= thd->make_string_literal_nchar($1)))
MYSQL_YYABORT;
}
| UNDERSCORE_CHARSET TEXT_STRING
{
- $$= new (thd->mem_root) Item_string_with_introducer(thd, $2.str,
- $2.length, $1);
- if ($$ == NULL)
+ if (!($$= thd->make_string_literal_charset($2, $1)))
MYSQL_YYABORT;
}
| text_literal TEXT_STRING_literal
{
- Item_string* item= (Item_string*) $1;
- item->append($2.str, $2.length);
- if (!(item->collation.repertoire & MY_REPERTOIRE_EXTENDED))
- {
- /*
- If the string has been pure ASCII so far,
- check the new part.
- */
- CHARSET_INFO *cs= thd->variables.collation_connection;
- item->collation.repertoire|= my_string_repertoire(cs,
- $2.str,
- $2.length);
- }
+ if (!($$= thd->make_string_literal_concat($1, $2)))
+ MYSQL_YYABORT;
}
;
diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc
index eb154d2..a399ccd 100644
--- a/sql/sys_vars.cc
+++ b/sql/sys_vars.cc
@@ -3084,7 +3084,7 @@ static const char *sql_mode_names[]=
"STRICT_ALL_TABLES", "NO_ZERO_IN_DATE", "NO_ZERO_DATE",
"ALLOW_INVALID_DATES", "ERROR_FOR_DIVISION_BY_ZERO", "TRADITIONAL",
"NO_AUTO_CREATE_USER", "HIGH_NOT_PRECEDENCE", "NO_ENGINE_SUBSTITUTION",
- "PAD_CHAR_TO_FULL_LENGTH",
+ "PAD_CHAR_TO_FULL_LENGTH", "EMPTY_STRING_IS_NULL",
0
};
diff --git a/tests/mysql_client_test.c b/tests/mysql_client_test.c
index d47ed5e..ae5c70d 100644
--- a/tests/mysql_client_test.c
+++ b/tests/mysql_client_test.c
@@ -19634,6 +19634,229 @@ static void test_mdev12579()
myquery(rc);
}
+/* Test test_mdev14013 sql_mode=EMPTY_STRING_IS_NULL */
+
+static void test_mdev14013()
+{
+ MYSQL *lmysql;
+ MYSQL_STMT *stmt1;
+ MYSQL_BIND my_bind[2];
+ MYSQL_RES *result;
+ char str_data[20];
+ unsigned int count;
+ int rc;
+ char query[MAX_TEST_QUERY_LENGTH];
+
+ myheader("test_mdev14013");
+
+ if (!opt_silent)
+ fprintf(stdout, "\n Establishing a test connection ...");
+ if (!(lmysql= mysql_client_init(NULL)))
+ {
+ myerror("mysql_client_init() failed");
+ exit(1);
+ }
+ if (!(mysql_real_connect(lmysql, opt_host, opt_user,
+ opt_password, current_db, opt_port,
+ opt_unix_socket, 0)))
+ {
+ myerror("connection failed");
+ exit(1);
+ }
+ mysql_options(lmysql, MYSQL_OPT_RECONNECT, &my_true);
+ if (!opt_silent)
+ fprintf(stdout, "OK");
+
+ /* set AUTOCOMMIT to ON*/
+ mysql_autocommit(lmysql, TRUE);
+
+ strmov(query, "SET SQL_MODE= \"EMPTY_STRING_IS_NULL\"");
+ if (!opt_silent)
+ fprintf(stdout, "\n With %s", query);
+ rc= mysql_query(mysql, query);
+ myquery(rc);
+
+ rc= mysql_query(lmysql, "DROP TABLE IF EXISTS test_mdev14013");
+ myquery(rc);
+
+ rc= mysql_query(lmysql, "CREATE TABLE test_mdev14013(id int, val varchar(10))");
+ myquery(rc);
+
+ strmov(query, "INSERT INTO test_mdev14013(id,val) VALUES(?,?)");
+ stmt1= mysql_simple_prepare(mysql, query);
+ check_stmt(stmt1);
+
+ verify_param_count(stmt1, 2);
+
+ /*
+ We need to bzero bind structure because mysql_stmt_bind_param checks all
+ its members.
+ */
+ bzero((char*) my_bind, sizeof(my_bind));
+
+ my_bind[0].buffer= (void *)&count;
+ my_bind[0].buffer_type= MYSQL_TYPE_LONG;
+ count= 100;
+
+ strcpy(str_data,"");
+ my_bind[1].buffer_type= MYSQL_TYPE_STRING;
+ my_bind[1].buffer= (char *) str_data;
+ my_bind[1].buffer_length= strlen(str_data);
+
+ rc= mysql_stmt_bind_param(stmt1, my_bind);
+
+ rc= mysql_stmt_execute(stmt1);
+ check_execute(stmt1, rc);
+
+ verify_st_affected_rows(stmt1, 1);
+
+ rc= mysql_stmt_close(stmt1);
+
+ strmov(query, "SET SQL_MODE= default");
+ if (!opt_silent)
+ fprintf(stdout, "\n With %s\n", query);
+ rc= mysql_query(mysql, query);
+ myquery(rc);
+
+ strmov(query, "INSERT INTO test_mdev14013(id,val) VALUES(?,?)");
+ stmt1= mysql_simple_prepare(mysql, query);
+ check_stmt(stmt1);
+
+ count= 200;
+ rc= mysql_stmt_bind_param(stmt1, my_bind);
+
+ rc= mysql_stmt_execute(stmt1);
+ check_execute(stmt1, rc);
+
+ verify_st_affected_rows(stmt1, 1);
+
+ rc= mysql_stmt_close(stmt1);
+ if (!opt_silent)
+ fprintf(stdout, "\n test_mdev14013(x) returned: %d", rc);
+ DIE_UNLESS( rc == 0);
+
+ rc= mysql_query(mysql, "SELECT id, val FROM test_mdev14013 order by id");
+ myquery(rc);
+
+ result= mysql_store_result(mysql);
+ mytest(result);
+
+ rc= my_process_result_set(result);
+ DIE_UNLESS(rc == 2);
+ mysql_free_result(result);
+
+ rc= mysql_query(mysql, "SELECT id, val FROM test_mdev14013 where val is null");
+ myquery(rc);
+
+ result= mysql_store_result(mysql);
+ mytest(result);
+
+ rc= my_process_result_set(result);
+ DIE_UNLESS(rc == 1);
+ mysql_free_result(result);
+
+ myquery(mysql_query(mysql, "drop table test_mdev14013"));
+ mysql_close(lmysql);
+}
+
+static void test_mdev14013_1()
+{
+ MYSQL *lmysql;
+ MYSQL_STMT *stmt1;
+ MYSQL_BIND my_bind[3];
+ char str_data[3][255];
+ int rc;
+ char query[MAX_TEST_QUERY_LENGTH];
+
+ myheader("test_mdev14013_1");
+
+ if (!opt_silent)
+ fprintf(stdout, "\n Establishing a test connection ...");
+ if (!(lmysql= mysql_client_init(NULL)))
+ {
+ myerror("mysql_client_init() failed");
+ exit(1);
+ }
+ if (!(mysql_real_connect(lmysql, opt_host, opt_user,
+ opt_password, current_db, opt_port,
+ opt_unix_socket, 0)))
+ {
+ myerror("connection failed");
+ exit(1);
+ }
+ mysql_options(lmysql, MYSQL_OPT_RECONNECT, &my_true);
+ if (!opt_silent)
+ fprintf(stdout, "OK");
+
+ /* set AUTOCOMMIT to ON*/
+ mysql_autocommit(lmysql, TRUE);
+
+ strmov(query, "SET SQL_MODE= \"EMPTY_STRING_IS_NULL\"");
+ if (!opt_silent)
+ fprintf(stdout, "\n With %s", query);
+ rc= mysql_query(mysql, query);
+ myquery(rc);
+
+ rc= mysql_query(mysql,
+ "CREATE OR REPLACE PROCEDURE test_mdev14013_p1("
+ " IN i1 VARCHAR(255) , "
+ " INOUT io1 VARCHAR(255), "
+ " OUT o2 VARBINARY(255)) "
+ "BEGIN "
+ " SET o2 = concat(concat(coalesce(i1,'i1 is null'),' - '),coalesce(i1,'io1 is null')); "
+ "END");
+ myquery(rc);
+
+ strmov(query, "CALL test_mdev14013_p1(?, ?, ?)");
+ stmt1= mysql_simple_prepare(mysql, query);
+ check_stmt(stmt1);
+
+ /* Init PS-parameters. */
+
+ bzero((char *) my_bind, sizeof (my_bind));
+
+ strcpy(str_data[0],"");
+ my_bind[0].buffer_type= MYSQL_TYPE_STRING;
+ my_bind[0].buffer= (char *) str_data[0];
+ my_bind[0].buffer_length= strlen(str_data[0]);
+
+ strcpy(str_data[1],"");
+ my_bind[1].buffer_type= MYSQL_TYPE_STRING;
+ my_bind[1].buffer= (char *) str_data[1];
+ my_bind[1].buffer_length= strlen(str_data[1]);
+
+ strcpy(str_data[2],"");
+ my_bind[2].buffer_type= MYSQL_TYPE_STRING;
+ my_bind[2].buffer= (char *) str_data[2];
+ my_bind[2].buffer_length= strlen(str_data[2]);
+
+ /* Bind parameters. */
+
+ rc= mysql_stmt_bind_param(stmt1, my_bind);
+ check_execute(stmt1, rc);
+ /* Execute */
+
+ rc= mysql_stmt_execute(stmt1);
+ check_execute(stmt1, rc);
+
+ my_bind[0].buffer_length= sizeof(str_data[0]);
+ my_bind[1].buffer_length= sizeof(str_data[1]);
+
+ mysql_stmt_bind_result(stmt1, my_bind);
+ rc= mysql_stmt_fetch(stmt1);
+
+ if (!opt_silent)
+ fprintf(stdout,"\nstr_data[1]=%s\n",str_data[1]);
+
+ DIE_UNLESS(strcmp(str_data[1], "i1 is null - io1 is null") == 0);
+
+ rc= mysql_stmt_close(stmt1);
+ DIE_UNLESS( rc == 0);
+
+ myquery(mysql_query(mysql, "drop procedure test_mdev14013_p1"));
+ mysql_close(lmysql);
+}
+
static struct my_tests_st my_tests[]= {
{ "disable_query_logs", disable_query_logs },
@@ -19914,6 +20137,8 @@ static struct my_tests_st my_tests[]= {
{ "test_big_packet", test_big_packet },
{ "test_prepare_analyze", test_prepare_analyze },
{ "test_mdev12579", test_mdev12579 },
+ { "test_mdev14013", test_mdev14013 },
+ { "test_mdev14013_1", test_mdev14013_1 },
{ 0, 0 }
};
Follow ups
