1--echo #
2--echo # Test of function defaults for any server, including embedded.
3--echo #
4
5--echo #
6--echo # Function defaults run 1. No microsecond precision.
7--echo #
8let $current_timestamp=CURRENT_TIMESTAMP;
9let $now=NOW();
10let $timestamp=TIMESTAMP;
11let $datetime=DATETIME;
12source 'include/function_defaults.inc';
13
14--echo #
15--echo # Function defaults run 2. Six digits scale on seconds precision.
16--echo #
17let $current_timestamp=CURRENT_TIMESTAMP(6);
18let $now=NOW(6);
19let $timestamp=TIMESTAMP(6);
20let $datetime=DATETIME(6);
21source 'include/function_defaults.inc';
22
23#
24# MDEV-20403 Assertion `0' or Assertion `btr_validate_index(index, 0)' failed in row_upd_sec_index_entry or error code 126: Index is corrupted upon UPDATE with TIMESTAMP..ON UPDATE
25#
26
27# ON UPDATE NOW and indexed virtual columns
28create table t1 (t timestamp, i int, v timestamp as (t) virtual, key(v));
29insert t1 (t,i) values ('2006-03-01 23:59:59',1);
30update t1 set i = 2;
31check table t1;
32drop table t1;
33
34# ON UPDATE NOW and triggers
35create table t1 (t timestamp, i int);
36create trigger tr1 before update on t1 for each row set @new:=new.t;
37insert t1 (t,i) values ('2006-03-01 23:59:59', 1);
38update t1 set i = 2;
39select if(@new = t, 'correct', 'wrong') from t1;
40drop table t1;
41
42# triggers, virtual columns, multi-update
43create table t1 (i int, j int as (i));
44create trigger tr1 before update on t1 for each row set @new:=new.j;
45insert t1 (i) values (1);
46update t1, t1 as t2 set t1.i = 2;
47select if(@new = j, 'correct', 'wrong') from t1;
48drop table t1;
49
50# SET xxx=DEFAULT
51create table t1 (a int, b varchar(20) default 'foo');
52insert t1 values (1,'bla'),(2, 'bar');
53select * from t1;
54update t1 set b=default where a=1;
55select * from t1;
56drop table t1;
57
58# ON UPDATE NOW and SET xxx=DEFAULT
59create table t1 (
60  a int,
61  b timestamp default '2010-10-10 10:10:10' on update now(),
62  c varchar(100) default 'x');
63insert t1 (a) values (1),(2);
64select * from t1;
65set timestamp=unix_timestamp('2011-11-11 11-11-11');
66update t1 set b=default, c=default(b) where a=1;
67select * from t1;
68drop table t1;
69set timestamp=default;
70