maria-developers team mailing list archive

[Alexander Barkov <bar@xxxxxxxxxxx>]

Hi Jérôme,

On 11/13/2017 05:20 PM, jerome brauge wrote:
> Hello Alexander,
> Simple and effective, as usual!
> Thank you very much.
> Jérôme.

Thanks. Pushed into bb-10.2-ext
(will be propagated to 10.3 later this week).

Can you also please have a look into one Oracle question below:

> 
>> -----Message d'origine-----
>> De : Alexander Barkov [mailto:bar@xxxxxxxxxxx]
>> Envoyé : lundi 13 novembre 2017 13:26
>> À : jerome brauge
>> Cc : MariaDB Developers (maria-developers@xxxxxxxxxxxxxxxxxxx)
>> Objet : Re: STRICT mode and Cast
>>
>> Hello Jerome,
>>
>>
>> On 11/10/2017 05:20 PM, jerome brauge wrote:
>>> Hello Alexander,
>>>
>>>
>>>
>>> To have a behavior the nearest possible to Oracle, I use
>>> STRICT_TRANS_TABLES sql_mode.
>>>
>>> But then I have a problem with cast to varchar that failed with
>>> "truncated incorrect value" .
>>>
>>>
>>>
>>> Ex:
>>>
>>> set sql_mode='ORACLE,STRICT_TRANS_TABLES';
>>>
>>> delimiter /
>>>
>>> CREATE or replace procedure p3(b1 integer)
>>>
>>> AS
>>>
>>>   strres VARCHAR(4);
>>>
>>> BEGIN
>>>
>>>   strres:=cast('123456' as char(4));
>>>
>>> END
>>>
>>> /
>>>
>>> call p3(-1)
>>>
>>> /
>>>
>>> -- Failed with : ERROR 1292 (22007): Truncated incorrect CHAR(4) value:
>>> '123456'
>>>
>>>
>>>
>>> But
>>>
>>>
>>>
>>> select cast('11111' as varchar(2)) from dual;
>>>
>>>
>>>
>>> just issue a warning, not an error.
>>
>> How can I get such warnings from Oracle?
>> For me the above query just truncates silently, without warnings.

So how can I get CAST return warnings?

Thanks!


>>
>>
>>>
>>>
>>>
>>> On Oracle, Sybase,SQLServer and DB2, cast a string to a shorter string
>>> never issue warnings nor error.
>>
>>
>> Thanks for noticing this.
>>
>>
>> I've filed a bug report for this:
>>
>> MDEV-14376 Explicit CAST(CHAR(N)) erroneously escalates warnings to
>> errors in STRICT_ALL_TABLES
>>
>>
>> The attached patch fixes this problem.
>> Do you see any problems with the patch?
>>
>> Thanks!
>>
>>>
>>>
>>>
>>> What do you think about ?
>>>
>>>
>>>
>>> Regards,
>>>
>>> Jérôme.
>>>