maria-developers team mailing list archive

[Sergei Golubchik <serg@xxxxxxxxxxx>]

Hi, Sergey!

On Mar 21, Sergey Vojtovich wrote:
> On Tue, Mar 18, 2014 at 05:25:59PM +0100, Sergei Golubchik wrote:
> > On Mar 11, Sergey Vojtovich wrote:
> > 
> > > - SHOW PROFILE is deprecated in 5.6, do we want to deprecate it too?
> > 
> > I'm not sure. It's being used too, although less than INSERT DELAYED,
> > but more than XML functions :)
> > 
> > Perhaps we can deprecate it in 10.1 or rewrite to use P_S...
> Ok, should I create jira task?

Yes, please!

> > > - YEAR(2) is deprecated in 5.6, do we want to deprecate it too?
> > >   Monty suggests that we shouldn't deprecate it. I find it Ok too.
> > >   Reasons for YEAR(2) deprecation are not obvious, relevant worklog is
> > >   private. Relevant revision comment says: "YEAR(2) is a subject to
> > >   deprecation since it has ill design."
> > 
> > I'd deprecate it - I agree about "ill design", it has lots of gotchas
> > that are literally impossible to fix. In some cases it seems to work,
> > but it's enough to change the query slightly - and it won't.
> OTOH it requires less storage, which is a pro. It looks well-defined, that
> is 0-69 are 2000-2069 and 70-99 are 1970-1999. Though I can't judge the design.
> Monty likes it. Should I create jira task?

The fundamental design flaw - only the Field itself knows that it's
YEAR(2), no item knows it. So, as long as you use YEAR(2) in any
expression - it loses its magic powers:

  MariaDB [test]> create table t1 (y2 year(2), y4 year(4));
  Query OK, 0 rows affected, 1 warning (0.00 sec)

  MariaDB [test]> insert t1 values (10, 2010);
  Query OK, 1 row affected (0.00 sec)

  MariaDB [test]> select * from t1;
  +------+------+
  | y2   | y4   |
  +------+------+
  |   10 | 2010 |
  +------+------+
  1 row in set (0.00 sec)

  MariaDB [test]> select y2 = 2010, y4=2010, y2=y4 from t1;
  +-----------+---------+-------+
  | y2 = 2010 | y4=2010 | y2=y4 |
  +-----------+---------+-------+
  |         1 |       1 |     1 |
  +-----------+---------+-------+
  1 row in set (0.00 sec)

  MariaDB [test]> select y2+0 = 2010, y4+0=2010, y2+0=y4+0 from t1;
  +-------------+-----------+-----------+
  | y2+0 = 2010 | y4+0=2010 | y2+0=y4+0 |
  +-------------+-----------+-----------+
  |           0 |         1 |         0 |
  +-------------+-----------+-----------+
  1 row in set (0.00 sec)

Think also of Item_cache, Item_ref, GROUP BY (with temp tables),
subqueries - I really don't know in what cases YEAR(2) works and in what
it doesn't.

> > > - NO_ENGINE_SUBSTITUTION is default mode in 5.6 now, do we want it
> > > be default too?
> > 
> > May be. What do you think?
> I'd say these days storage engines have way too different semantics. E.g. if
> user requests FEDERATED but gets MyISAM further queries will return unexpected
> results.
> 
> I believe it is a good idea to make it default too.

Okay. Old idea of SQL_MODE always was that empty set is always the
default. I mean, all sql-mode values were selected this way (e.g.
NO_ENGINE_SUBSTITUTION vs. ALLOW_ENGINE_SUBSTITUTION).

But I agree, we cannot keep this forever...

Regards,
Sergei