maria-developers team mailing list archive

[jerome brauge <j.brauge@xxxxxxxxxxx>]

Hello Alexander,
Simple and effective, as usual!
Thank you very much.
Jérôme.

> -----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.
> 
> 
> >
> >
> >
> > 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.
> >