Merge gluh@gw:/home/bk/mysql-4.1

into gluh.mysql.r18.ru:/home/gluh/mysql-4.1.DTFUNC


sql/field.cc:
  Auto merged
sql/item_create.cc:
  Auto merged
sql/mysql_priv.h:
  Auto merged
sql/protocol.cc:
  Auto merged
sql/sql_yacc.yy:
  Auto merged

2 files changed, 291 insertions, 0 deletions

diff --git a/mysql-test/r/func_sapdb.result b/mysql-test/r/func_sapdb.result
new file mode 100644
index 00000000000..5c044d00726
--- /dev/null
+++ b/mysql-test/r/func_sapdb.result
@@ -0,0 +1,194 @@
+drop table if exists t1, test;
+select extract(DAY_MICROSECOND FROM "1999-01-02 10:11:12.000123");
+extract(DAY_MICROSECOND FROM "1999-01-02 10:11:12.000123")
+2101112000123
+select extract(HOUR_MICROSECOND FROM "1999-01-02 10:11:12.000123");
+extract(HOUR_MICROSECOND FROM "1999-01-02 10:11:12.000123")
+101112000123
+select extract(MINUTE_MICROSECOND FROM "1999-01-02 10:11:12.000123");
+extract(MINUTE_MICROSECOND FROM "1999-01-02 10:11:12.000123")
+1112000123
+select extract(SECOND_MICROSECOND FROM "1999-01-02 10:11:12.000123");
+extract(SECOND_MICROSECOND FROM "1999-01-02 10:11:12.000123")
+12000123
+select extract(MICROSECOND FROM "1999-01-02 10:11:12.000123");
+extract(MICROSECOND FROM "1999-01-02 10:11:12.000123")
+123
+select date_format("1997-12-31 23:59:59.000002", "%f");
+date_format("1997-12-31 23:59:59.000002", "%f")
+000002
+select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000 99:99:99.999999" DAY_MICROSECOND);
+date_add("1997-12-31 23:59:59.000002",INTERVAL "10000 99:99:99.999999" DAY_MICROSECOND)
+2025-05-23 04:40:39.000001
+select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000:99:99.999999" HOUR_MICROSECOND);
+date_add("1997-12-31 23:59:59.000002",INTERVAL "10000:99:99.999999" HOUR_MICROSECOND)
+1999-02-21 17:40:39.000001
+select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000:99.999999" MINUTE_MICROSECOND);
+date_add("1997-12-31 23:59:59.000002",INTERVAL "10000:99.999999" MINUTE_MICROSECOND)
+1998-01-07 22:41:39.000001
+select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000.999999" SECOND_MICROSECOND);
+date_add("1997-12-31 23:59:59.000002",INTERVAL "10000.999999" SECOND_MICROSECOND)
+1998-01-01 02:46:40.000001
+select date_add("1997-12-31 23:59:59.000002",INTERVAL "999999" MICROSECOND);
+date_add("1997-12-31 23:59:59.000002",INTERVAL "999999" MICROSECOND)
+1998-01-01 00:00:00.000001
+select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1 1:1:1.000002" DAY_MICROSECOND);
+date_sub("1998-01-01 00:00:00.000001",INTERVAL "1 1:1:1.000002" DAY_MICROSECOND)
+1997-12-30 22:58:58.999999
+select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1:1.000002" HOUR_MICROSECOND);
+date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1:1.000002" HOUR_MICROSECOND)
+1997-12-31 22:58:58.999999
+select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1.000002" MINUTE_MICROSECOND);
+date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1.000002" MINUTE_MICROSECOND)
+1997-12-31 23:58:58.999999
+select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1.000002" SECOND_MICROSECOND);
+date_sub("1998-01-01 00:00:00.000001",INTERVAL "1.000002" SECOND_MICROSECOND)
+1997-12-31 23:59:58.999999
+select date_sub("1998-01-01 00:00:00.000001",INTERVAL "000002" MICROSECOND);
+date_sub("1998-01-01 00:00:00.000001",INTERVAL "000002" MICROSECOND)
+1997-12-31 23:59:59.999999
+select adddate("1997-12-31 23:59:59.000001", 10);
+adddate("1997-12-31 23:59:59.000001", 10)
+1998-01-10 23:59:59.000001
+select subdate("1997-12-31 23:59:59.000001", 10);
+subdate("1997-12-31 23:59:59.000001", 10)
+1997-12-21 23:59:59.000001
+select datediff("1997-12-31 23:59:59.000001","1997-12-30");
+datediff("1997-12-31 23:59:59.000001","1997-12-30")
+1
+select datediff("1997-11-31 23:59:59.000001","1997-12-31");
+datediff("1997-11-31 23:59:59.000001","1997-12-31")
+-30
+select datediff("1997-11-31 23:59:59.000001",null);
+datediff("1997-11-31 23:59:59.000001",null)
+NULL
+select weekofyear("1997-11-31 23:59:59.000001");
+weekofyear("1997-11-31 23:59:59.000001")
+49
+select makedate(1997,1);
+makedate(1997,1)
+1997-01-01
+select makedate(1997,0);
+makedate(1997,0)
+NULL
+select addtime("1997-12-31 23:59:59.999999", "1 1:1:1.000002");
+addtime("1997-12-31 23:59:59.999999", "1 1:1:1.000002")
+1998-01-02 01:01:01.000001
+select subtime("1997-12-31 23:59:59.000001", "1 1:1:1.000002");
+subtime("1997-12-31 23:59:59.000001", "1 1:1:1.000002")
+1997-12-30 22:58:57.999999
+select addtime("1997-12-31 23:59:59.999999", "1998-01-01 01:01:01.999999");
+addtime("1997-12-31 23:59:59.999999", "1998-01-01 01:01:01.999999")
+NULL
+select subtime("1997-12-31 23:59:59.999999", "1998-01-01 01:01:01.999999");
+subtime("1997-12-31 23:59:59.999999", "1998-01-01 01:01:01.999999")
+NULL
+select subtime("01:00:00.999999", "02:00:00.999998");
+subtime("01:00:00.999999", "02:00:00.999998")
+-00:59:59.999999
+select subtime("02:01:01.999999", "01:01:01.999999");
+subtime("02:01:01.999999", "01:01:01.999999")
+01:00:00.000000
+select timediff("1997-01-01 23:59:59.000001","1995-12-31 23:59:59.000002");
+timediff("1997-01-01 23:59:59.000001","1995-12-31 23:59:59.000002")
+8807:59:59.999999
+select timediff("1997-12-31 23:59:59.000001","1997-12-30 01:01:01.000002");
+timediff("1997-12-31 23:59:59.000001","1997-12-30 01:01:01.000002")
+46:58:57.999999
+select timediff("1997-12-30 23:59:59.000001","1997-12-31 23:59:59.000002");
+timediff("1997-12-30 23:59:59.000001","1997-12-31 23:59:59.000002")
+-23:59:59.999999
+select timediff("1997-12-31 23:59:59.000001","23:59:59.000001");
+timediff("1997-12-31 23:59:59.000001","23:59:59.000001")
+NULL
+select timediff("2000:01:01 00:00:00", "2000:01:01 00:00:00.1");
+timediff("2000:01:01 00:00:00", "2000:01:01 00:00:00.1")
+-00:00:00.000001
+select maketime(10,11,12);
+maketime(10,11,12)
+10:11:12
+select maketime(25,11,12);
+maketime(25,11,12)
+25:11:12
+select maketime(-25,11,12);
+maketime(-25,11,12)
+-25:11:12
+select timestamp("2001-12-01", "01:01:01.999999");
+timestamp("2001-12-01", "01:01:01.999999")
+2001-12-01 01:01:01.999999
+select timestamp("2001-13-01", "01:01:01.000001");
+timestamp("2001-13-01", "01:01:01.000001")
+NULL
+select timestamp("2001-12-01", "25:01:01");
+timestamp("2001-12-01", "25:01:01")
+2001-12-02 01:01:01
+select day("1997-12-31 23:59:59.000001");
+day("1997-12-31 23:59:59.000001")
+31
+select date("1997-12-31 23:59:59.000001");
+date("1997-12-31 23:59:59.000001")
+1997-12-31
+select date("1997-13-31 23:59:59.000001");
+date("1997-13-31 23:59:59.000001")
+NULL
+select time("1997-12-31 23:59:59.000001");
+time("1997-12-31 23:59:59.000001")
+23:59:59.000001
+select time("1997-12-31 25:59:59.000001");
+time("1997-12-31 25:59:59.000001")
+NULL
+select microsecond("1997-12-31 23:59:59.000001");
+microsecond("1997-12-31 23:59:59.000001")
+1
+create table t1 
+select makedate(1997,1) as f1,
+addtime(cast("1997-12-31 23:59:59.000001" as datetime), "1 1:1:1.000002") as f2,
+addtime(cast("23:59:59.999999" as time) , "1 1:1:1.000002") as f3,
+timediff("1997-12-31 23:59:59.000001","1997-12-30 01:01:01.000002") as f4,
+timediff("1997-12-30 23:59:59.000001","1997-12-31 23:59:59.000002") as f5,
+maketime(10,11,12) as f6,
+timestamp("2001-12-01", "01:01:01") as f7,
+date("1997-12-31 23:59:59.000001") as f8,
+time("1997-12-31 23:59:59.000001") as f9;
+describe t1;
+Field	Type	Null	Key	Default	Extra
+f1	date			0000-00-00	
+f2	datetime			0000-00-00 00:00:00	
+f3	time			00:00:00	
+f4	time			00:00:00	
+f5	time			00:00:00	
+f6	time			00:00:00	
+f7	datetime			0000-00-00 00:00:00	
+f8	date			0000-00-00	
+f9	time			00:00:00	
+select * from t1;
+f1	f2	f3	f4	f5	f6	f7	f8	f9
+1997-01-01	1998-01-02 01:01:00	49:01:01	46:58:57	-23:59:59	10:11:12	2001-12-01 01:01:01	1997-12-31	23:59:59
+create table test(t1 datetime, t2 time, t3 time, t4 datetime);
+insert into test values 
+('2001-01-01 01:01:01', '01:01:01', null, '2001-02-01 01:01:01'),
+('2001-01-01 01:01:01', '-01:01:01', '-23:59:59', "1997-12-31 23:59:59.000001"),
+('1997-12-31 23:59:59.000001', '-23:59:59', '-01:01:01', '2001-01-01 01:01:01'),
+('2001-01-01 01:01:01', '01:01:01', '-1 01:01:01', null),
+('2001-01-01 01:01:01', null, '-1 01:01:01', null),
+(null, null, null, null),
+('2001-01-01 01:01:01', '01:01:01', '1 01:01:01', '2001-01-01 01:01:01');
+SELECT ADDTIME(t1,t2) As ttt, ADDTIME(t2, t3) As qqq from test;
+ttt	qqq
+2001-01-01 02:02:02	NULL
+2001-01-01 00:00:00	-25:01:00
+1997-12-31 00:00:00	-25:01:00
+2001-01-01 02:02:02	-24:00:00
+NULL	NULL
+NULL	NULL
+2001-01-01 02:02:02	26:02:02
+SELECT TIMEDIFF(t1,t4) As ttt, TIMEDIFF(t2, t3) As qqq from test;
+ttt	qqq
+-744:00:00	NULL
+26305:01:02	22:58:58
+-26305:01:02	-22:58:58
+NULL	26:02:02
+NULL	NULL
+NULL	NULL
+00:00:00	-24:00:00
+drop table t1, test;
diff --git a/mysql-test/t/func_sapdb.test b/mysql-test/t/func_sapdb.test
new file mode 100644
index 00000000000..05e1f20fe1e
--- /dev/null
+++ b/mysql-test/t/func_sapdb.test
@@ -0,0 +1,97 @@
+--disable_warnings
+drop table if exists t1, test;
+--enable_warnings
+
+
+#
+# time functions
+#
+select extract(DAY_MICROSECOND FROM "1999-01-02 10:11:12.000123");
+select extract(HOUR_MICROSECOND FROM "1999-01-02 10:11:12.000123");
+select extract(MINUTE_MICROSECOND FROM "1999-01-02 10:11:12.000123");
+select extract(SECOND_MICROSECOND FROM "1999-01-02 10:11:12.000123");
+select extract(MICROSECOND FROM "1999-01-02 10:11:12.000123");
+select date_format("1997-12-31 23:59:59.000002", "%f");
+
+select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000 99:99:99.999999" DAY_MICROSECOND);
+select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000:99:99.999999" HOUR_MICROSECOND);
+select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000:99.999999" MINUTE_MICROSECOND);
+select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000.999999" SECOND_MICROSECOND);
+select date_add("1997-12-31 23:59:59.000002",INTERVAL "999999" MICROSECOND);
+
+select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1 1:1:1.000002" DAY_MICROSECOND);
+select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1:1.000002" HOUR_MICROSECOND);
+select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1.000002" MINUTE_MICROSECOND);
+select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1.000002" SECOND_MICROSECOND);
+select date_sub("1998-01-01 00:00:00.000001",INTERVAL "000002" MICROSECOND);
+
+#Date functions
+select adddate("1997-12-31 23:59:59.000001", 10);
+select subdate("1997-12-31 23:59:59.000001", 10);
+
+select datediff("1997-12-31 23:59:59.000001","1997-12-30");
+select datediff("1997-11-31 23:59:59.000001","1997-12-31");
+select datediff("1997-11-31 23:59:59.000001",null);
+
+select weekofyear("1997-11-31 23:59:59.000001");
+
+select makedate(1997,1);
+select makedate(1997,0);
+
+#Time functions
+
+select addtime("1997-12-31 23:59:59.999999", "1 1:1:1.000002");
+select subtime("1997-12-31 23:59:59.000001", "1 1:1:1.000002");
+select addtime("1997-12-31 23:59:59.999999", "1998-01-01 01:01:01.999999");
+select subtime("1997-12-31 23:59:59.999999", "1998-01-01 01:01:01.999999");
+select subtime("01:00:00.999999", "02:00:00.999998");
+select subtime("02:01:01.999999", "01:01:01.999999");
+
+select timediff("1997-01-01 23:59:59.000001","1995-12-31 23:59:59.000002");
+select timediff("1997-12-31 23:59:59.000001","1997-12-30 01:01:01.000002");
+select timediff("1997-12-30 23:59:59.000001","1997-12-31 23:59:59.000002");
+select timediff("1997-12-31 23:59:59.000001","23:59:59.000001");
+select timediff("2000:01:01 00:00:00", "2000:01:01 00:00:00.1");
+
+select maketime(10,11,12);
+select maketime(25,11,12);
+select maketime(-25,11,12);
+
+#Extraction functions
+select timestamp("2001-12-01", "01:01:01.999999");
+select timestamp("2001-13-01", "01:01:01.000001");
+select timestamp("2001-12-01", "25:01:01");
+select day("1997-12-31 23:59:59.000001");
+select date("1997-12-31 23:59:59.000001");
+select date("1997-13-31 23:59:59.000001");
+select time("1997-12-31 23:59:59.000001");
+select time("1997-12-31 25:59:59.000001");
+select microsecond("1997-12-31 23:59:59.000001");
+
+create table t1 
+select makedate(1997,1) as f1,
+   addtime(cast("1997-12-31 23:59:59.000001" as datetime), "1 1:1:1.000002") as f2,
+   addtime(cast("23:59:59.999999" as time) , "1 1:1:1.000002") as f3,
+   timediff("1997-12-31 23:59:59.000001","1997-12-30 01:01:01.000002") as f4,
+   timediff("1997-12-30 23:59:59.000001","1997-12-31 23:59:59.000002") as f5,
+   maketime(10,11,12) as f6,
+   timestamp("2001-12-01", "01:01:01") as f7,
+   date("1997-12-31 23:59:59.000001") as f8,
+   time("1997-12-31 23:59:59.000001") as f9;
+describe t1;
+select * from t1;
+
+create table test(t1 datetime, t2 time, t3 time, t4 datetime);
+insert into test values 
+('2001-01-01 01:01:01', '01:01:01', null, '2001-02-01 01:01:01'),
+('2001-01-01 01:01:01', '-01:01:01', '-23:59:59', "1997-12-31 23:59:59.000001"),
+('1997-12-31 23:59:59.000001', '-23:59:59', '-01:01:01', '2001-01-01 01:01:01'),
+('2001-01-01 01:01:01', '01:01:01', '-1 01:01:01', null),
+('2001-01-01 01:01:01', null, '-1 01:01:01', null),
+(null, null, null, null),
+('2001-01-01 01:01:01', '01:01:01', '1 01:01:01', '2001-01-01 01:01:01');
+
+SELECT ADDTIME(t1,t2) As ttt, ADDTIME(t2, t3) As qqq from test;
+SELECT TIMEDIFF(t1,t4) As ttt, TIMEDIFF(t2, t3) As qqq from test;
+
+drop table t1, test;