diff options
Diffstat (limited to '')
| -rw-r--r-- | mysql-test/main/func_extract.result | 882 |
1 files changed, 882 insertions, 0 deletions
diff --git a/mysql-test/main/func_extract.result b/mysql-test/main/func_extract.result index bebb8c71..dc71f6ae 100644 --- a/mysql-test/main/func_extract.result +++ b/mysql-test/main/func_extract.result @@ -590,3 +590,885 @@ Warning 1292 Truncated incorrect time value: '01:02:03/' Warning 1292 Truncated incorrect time value: '01:02:03/' Warning 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '01:02:03/' DROP TABLE t1; +# +# Start of 10.5 tests +# +# +# MDEV-33496 Out of range error in AVG(YEAR(datetime)) due to a wrong data type +# +CREATE FUNCTION select01() RETURNS TEXT RETURN 'SELECT ?, CAST(? AS UNSIGNED), CAST(? AS SIGNED), ABS(?), ROUND(?), -?, ROUND(?,-1), ?+0, ?+0.0, CONCAT(?), LEAST(?,?), COALESCE(?), COALESCE(?,CAST(1 AS SIGNED)), COALESCE(?,CAST(1 AS UNSIGNED)), @a:=?'; +CREATE FUNCTION select02() RETURNS TEXT RETURN 'SELECT AVG(?), MIN(?), MAX(?), GROUP_CONCAT(?)'; +CREATE TABLE t1 (a DATETIME(6)); +INSERT INTO t1 VALUES ('2001-12-31 10:20:30.999999'); +CREATE FUNCTION params(expr TEXT, count INT) RETURNS TEXT +BEGIN +RETURN CONCAT(expr, REPEAT(CONCAT(', ', expr), count-1)); +END; +$$ +CREATE PROCEDURE show_drop() +BEGIN +SELECT TABLE_NAME, COLUMN_TYPE, COLUMN_NAME +FROM INFORMATION_SCHEMA.COLUMNS +WHERE TABLE_SCHEMA='test' + AND TABLE_NAME IN ('t1e_nm','t2e_nm','t1f_nm','t2f_nm', +'t1e_ps','t1f_ps','t2e_ps','t2f_ps') +ORDER BY LEFT(TABLE_NAME, 2), ORDINAL_POSITION, TABLE_NAME; +FOR rec IN (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES +WHERE TABLE_SCHEMA='test' + AND TABLE_NAME IN ('t1e_nm','t2e_nm','t1f_nm','t2f_nm', +'t1e_ps','t1f_ps','t2e_ps','t2f_ps')) +DO +EXECUTE IMMEDIATE CONCAT('DROP TABLE ', rec.TABLE_NAME); +END FOR; +END; +$$ +CREATE PROCEDURE p1(unit VARCHAR(32)) +BEGIN +DECLARE do_extract BOOL DEFAULT unit NOT IN('DAYOFYEAR'); +DECLARE query01 TEXT DEFAULT +CONCAT('CREATE TABLE t2 AS ', select01(), ' FROM t1'); +DECLARE query02 TEXT DEFAULT +CONCAT('CREATE TABLE t2 AS ', select02(), ' FROM t1'); +IF (do_extract) +THEN +EXECUTE IMMEDIATE REPLACE(REPLACE(query01,'t2','t1e_nm'),'?', CONCAT('EXTRACT(',unit,' FROM a)')); +EXECUTE IMMEDIATE REPLACE(REPLACE(query02,'t2','t2e_nm'),'?', CONCAT('EXTRACT(',unit,' FROM a)')); +END IF; +EXECUTE IMMEDIATE REPLACE(REPLACE(query01,'t2','t1f_nm'),'?', CONCAT(unit,'(a)')); +EXECUTE IMMEDIATE REPLACE(REPLACE(query02,'t2','t2f_nm'),'?', CONCAT(unit,'(a)')); +END; +$$ + + +# EXTRACT(YEAR FROM expr) and YEAR(expr) are equivalent +CALL p1('YEAR'); +EXECUTE IMMEDIATE 'CREATE TABLE t1e_ps AS SELECT ?, CAST(? AS UNSIGNED), CAST(? AS SIGNED), ABS(?), ROUND(?), -?, ROUND(?,-1), ?+0, ?+0.0, CONCAT(?), LEAST(?,?), COALESCE(?), COALESCE(?,CAST(1 AS SIGNED)), COALESCE(?,CAST(1 AS UNSIGNED)), @a:=?' USING EXTRACT(YEAR FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(YEAR FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(YEAR FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(YEAR FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(YEAR FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(YEAR FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(YEAR FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(YEAR FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(YEAR FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(YEAR FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(YEAR FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(YEAR FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(YEAR FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(YEAR FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(YEAR FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(YEAR FROM TIMESTAMP'2001-12-13 10:20:30.999999'); +EXECUTE IMMEDIATE 'CREATE TABLE t1f_ps AS SELECT ?, CAST(? AS UNSIGNED), CAST(? AS SIGNED), ABS(?), ROUND(?), -?, ROUND(?,-1), ?+0, ?+0.0, CONCAT(?), LEAST(?,?), COALESCE(?), COALESCE(?,CAST(1 AS SIGNED)), COALESCE(?,CAST(1 AS UNSIGNED)), @a:=?' USING YEAR(TIMESTAMP'2001-12-13 10:20:30.999999'), YEAR(TIMESTAMP'2001-12-13 10:20:30.999999'), YEAR(TIMESTAMP'2001-12-13 10:20:30.999999'), YEAR(TIMESTAMP'2001-12-13 10:20:30.999999'), YEAR(TIMESTAMP'2001-12-13 10:20:30.999999'), YEAR(TIMESTAMP'2001-12-13 10:20:30.999999'), YEAR(TIMESTAMP'2001-12-13 10:20:30.999999'), YEAR(TIMESTAMP'2001-12-13 10:20:30.999999'), YEAR(TIMESTAMP'2001-12-13 10:20:30.999999'), YEAR(TIMESTAMP'2001-12-13 10:20:30.999999'), YEAR(TIMESTAMP'2001-12-13 10:20:30.999999'), YEAR(TIMESTAMP'2001-12-13 10:20:30.999999'), YEAR(TIMESTAMP'2001-12-13 10:20:30.999999'), YEAR(TIMESTAMP'2001-12-13 10:20:30.999999'), YEAR(TIMESTAMP'2001-12-13 10:20:30.999999'), YEAR(TIMESTAMP'2001-12-13 10:20:30.999999'); +EXECUTE IMMEDIATE 'CREATE TABLE t2e_ps AS SELECT AVG(?), MIN(?), MAX(?), GROUP_CONCAT(?)' USING EXTRACT(YEAR FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(YEAR FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(YEAR FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(YEAR FROM TIMESTAMP'2001-12-13 10:20:30.999999'); +EXECUTE IMMEDIATE 'CREATE TABLE t2f_ps AS SELECT AVG(?), MIN(?), MAX(?), GROUP_CONCAT(?)' USING YEAR(TIMESTAMP'2001-12-13 10:20:30.999999'), YEAR(TIMESTAMP'2001-12-13 10:20:30.999999'), YEAR(TIMESTAMP'2001-12-13 10:20:30.999999'), YEAR(TIMESTAMP'2001-12-13 10:20:30.999999'); +CALL show_drop; +TABLE_NAME COLUMN_TYPE COLUMN_NAME +t1e_nm int(5) EXTRACT(YEAR FROM a) +t1e_ps int(5) ? +t1f_nm int(5) YEAR(a) +t1f_ps int(5) ? +t1e_nm int(4) unsigned CAST(EXTRACT(YEAR FROM a) AS UNSIGNED) +t1e_ps int(4) unsigned CAST(? AS UNSIGNED) +t1f_nm int(4) unsigned CAST(YEAR(a) AS UNSIGNED) +t1f_ps int(4) unsigned CAST(? AS UNSIGNED) +t1e_nm int(5) CAST(EXTRACT(YEAR FROM a) AS SIGNED) +t1e_ps int(5) CAST(? AS SIGNED) +t1f_nm int(5) CAST(YEAR(a) AS SIGNED) +t1f_ps int(5) CAST(? AS SIGNED) +t1e_nm int(5) ABS(EXTRACT(YEAR FROM a)) +t1e_ps int(5) ABS(?) +t1f_nm int(5) ABS(YEAR(a)) +t1f_ps int(5) ABS(?) +t1e_nm int(5) ROUND(EXTRACT(YEAR FROM a)) +t1e_ps int(5) ROUND(?) +t1f_nm int(5) ROUND(YEAR(a)) +t1f_ps int(5) ROUND(?) +t1e_nm int(5) -EXTRACT(YEAR FROM a) +t1e_ps int(5) -? +t1f_nm int(5) -YEAR(a) +t1f_ps int(5) -? +t1e_nm int(6) ROUND(EXTRACT(YEAR FROM a),-1) +t1e_ps int(6) ROUND(?,-1) +t1f_nm int(6) ROUND(YEAR(a),-1) +t1f_ps int(6) ROUND(?,-1) +t1e_nm int(6) EXTRACT(YEAR FROM a)+0 +t1e_ps int(6) ?+0 +t1f_nm int(6) YEAR(a)+0 +t1f_ps int(6) ?+0 +t1e_nm decimal(6,1) EXTRACT(YEAR FROM a)+0.0 +t1e_ps decimal(6,1) ?+0.0 +t1f_nm decimal(6,1) YEAR(a)+0.0 +t1f_ps decimal(6,1) ?+0.0 +t1e_nm varchar(4) CONCAT(EXTRACT(YEAR FROM a)) +t1e_ps varchar(4) CONCAT(?) +t1f_nm varchar(4) CONCAT(YEAR(a)) +t1f_ps varchar(4) CONCAT(?) +t1e_nm int(5) LEAST(EXTRACT(YEAR FROM a),EXTRACT(YEAR FROM a)) +t1e_ps int(5) LEAST(?,?) +t1f_nm int(5) LEAST(YEAR(a),YEAR(a)) +t1f_ps int(5) LEAST(?,?) +t1e_nm int(5) COALESCE(EXTRACT(YEAR FROM a)) +t1e_ps int(5) COALESCE(?) +t1f_nm int(5) COALESCE(YEAR(a)) +t1f_ps int(5) COALESCE(?) +t1e_nm int(5) COALESCE(EXTRACT(YEAR FROM a),CAST(1 AS SIGNED)) +t1e_ps int(5) COALESCE(?,CAST(1 AS SIGNED)) +t1f_nm int(5) COALESCE(YEAR(a),CAST(1 AS SIGNED)) +t1f_ps int(5) COALESCE(?,CAST(1 AS SIGNED)) +t1e_nm decimal(4,0) COALESCE(EXTRACT(YEAR FROM a),CAST(1 AS UNSIGNED)) +t1e_ps decimal(4,0) COALESCE(?,CAST(1 AS UNSIGNED)) +t1f_nm decimal(4,0) COALESCE(YEAR(a),CAST(1 AS UNSIGNED)) +t1f_ps decimal(4,0) COALESCE(?,CAST(1 AS UNSIGNED)) +t1e_nm int(5) @a:=EXTRACT(YEAR FROM a) +t1e_ps int(5) @a:=? +t1f_nm int(5) @a:=YEAR(a) +t1f_ps int(5) @a:=? +t2e_nm decimal(8,4) AVG(EXTRACT(YEAR FROM a)) +t2e_ps decimal(8,4) AVG(?) +t2f_nm decimal(8,4) AVG(YEAR(a)) +t2f_ps decimal(8,4) AVG(?) +t2e_nm bigint(5) MIN(EXTRACT(YEAR FROM a)) +t2e_ps bigint(5) MIN(?) +t2f_nm bigint(5) MIN(YEAR(a)) +t2f_ps bigint(5) MIN(?) +t2e_nm bigint(5) MAX(EXTRACT(YEAR FROM a)) +t2e_ps bigint(5) MAX(?) +t2f_nm bigint(5) MAX(YEAR(a)) +t2f_ps bigint(5) MAX(?) +t2e_nm mediumtext GROUP_CONCAT(EXTRACT(YEAR FROM a)) +t2e_ps mediumtext GROUP_CONCAT(?) +t2f_nm mediumtext GROUP_CONCAT(YEAR(a)) +t2f_ps mediumtext GROUP_CONCAT(?) + + +# EXTRACT(QUARTER FROM expr) and QUARTER(expr) are equavalent +CALL p1('QUARTER'); +EXECUTE IMMEDIATE 'CREATE TABLE t1e_ps AS SELECT ?, CAST(? AS UNSIGNED), CAST(? AS SIGNED), ABS(?), ROUND(?), -?, ROUND(?,-1), ?+0, ?+0.0, CONCAT(?), LEAST(?,?), COALESCE(?), COALESCE(?,CAST(1 AS SIGNED)), COALESCE(?,CAST(1 AS UNSIGNED)), @a:=?' USING EXTRACT(QUARTER FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(QUARTER FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(QUARTER FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(QUARTER FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(QUARTER FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(QUARTER FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(QUARTER FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(QUARTER FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(QUARTER FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(QUARTER FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(QUARTER FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(QUARTER FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(QUARTER FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(QUARTER FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(QUARTER FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(QUARTER FROM TIMESTAMP'2001-12-13 10:20:30.999999'); +EXECUTE IMMEDIATE 'CREATE TABLE t1f_ps AS SELECT ?, CAST(? AS UNSIGNED), CAST(? AS SIGNED), ABS(?), ROUND(?), -?, ROUND(?,-1), ?+0, ?+0.0, CONCAT(?), LEAST(?,?), COALESCE(?), COALESCE(?,CAST(1 AS SIGNED)), COALESCE(?,CAST(1 AS UNSIGNED)), @a:=?' USING QUARTER(TIMESTAMP'2001-12-13 10:20:30.999999'), QUARTER(TIMESTAMP'2001-12-13 10:20:30.999999'), QUARTER(TIMESTAMP'2001-12-13 10:20:30.999999'), QUARTER(TIMESTAMP'2001-12-13 10:20:30.999999'), QUARTER(TIMESTAMP'2001-12-13 10:20:30.999999'), QUARTER(TIMESTAMP'2001-12-13 10:20:30.999999'), QUARTER(TIMESTAMP'2001-12-13 10:20:30.999999'), QUARTER(TIMESTAMP'2001-12-13 10:20:30.999999'), QUARTER(TIMESTAMP'2001-12-13 10:20:30.999999'), QUARTER(TIMESTAMP'2001-12-13 10:20:30.999999'), QUARTER(TIMESTAMP'2001-12-13 10:20:30.999999'), QUARTER(TIMESTAMP'2001-12-13 10:20:30.999999'), QUARTER(TIMESTAMP'2001-12-13 10:20:30.999999'), QUARTER(TIMESTAMP'2001-12-13 10:20:30.999999'), QUARTER(TIMESTAMP'2001-12-13 10:20:30.999999'), QUARTER(TIMESTAMP'2001-12-13 10:20:30.999999'); +EXECUTE IMMEDIATE 'CREATE TABLE t2e_ps AS SELECT AVG(?), MIN(?), MAX(?), GROUP_CONCAT(?)' USING EXTRACT(QUARTER FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(QUARTER FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(QUARTER FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(QUARTER FROM TIMESTAMP'2001-12-13 10:20:30.999999'); +EXECUTE IMMEDIATE 'CREATE TABLE t2f_ps AS SELECT AVG(?), MIN(?), MAX(?), GROUP_CONCAT(?)' USING QUARTER(TIMESTAMP'2001-12-13 10:20:30.999999'), QUARTER(TIMESTAMP'2001-12-13 10:20:30.999999'), QUARTER(TIMESTAMP'2001-12-13 10:20:30.999999'), QUARTER(TIMESTAMP'2001-12-13 10:20:30.999999'); +CALL show_drop; +TABLE_NAME COLUMN_TYPE COLUMN_NAME +t1e_nm int(2) EXTRACT(QUARTER FROM a) +t1e_ps int(2) ? +t1f_nm int(2) QUARTER(a) +t1f_ps int(2) ? +t1e_nm int(1) unsigned CAST(EXTRACT(QUARTER FROM a) AS UNSIGNED) +t1e_ps int(1) unsigned CAST(? AS UNSIGNED) +t1f_nm int(1) unsigned CAST(QUARTER(a) AS UNSIGNED) +t1f_ps int(1) unsigned CAST(? AS UNSIGNED) +t1e_nm int(2) CAST(EXTRACT(QUARTER FROM a) AS SIGNED) +t1e_ps int(2) CAST(? AS SIGNED) +t1f_nm int(2) CAST(QUARTER(a) AS SIGNED) +t1f_ps int(2) CAST(? AS SIGNED) +t1e_nm int(2) ABS(EXTRACT(QUARTER FROM a)) +t1e_ps int(2) ABS(?) +t1f_nm int(2) ABS(QUARTER(a)) +t1f_ps int(2) ABS(?) +t1e_nm int(2) ROUND(EXTRACT(QUARTER FROM a)) +t1e_ps int(2) ROUND(?) +t1f_nm int(2) ROUND(QUARTER(a)) +t1f_ps int(2) ROUND(?) +t1e_nm int(2) -EXTRACT(QUARTER FROM a) +t1e_ps int(2) -? +t1f_nm int(2) -QUARTER(a) +t1f_ps int(2) -? +t1e_nm int(3) ROUND(EXTRACT(QUARTER FROM a),-1) +t1e_ps int(3) ROUND(?,-1) +t1f_nm int(3) ROUND(QUARTER(a),-1) +t1f_ps int(3) ROUND(?,-1) +t1e_nm int(3) EXTRACT(QUARTER FROM a)+0 +t1e_ps int(3) ?+0 +t1f_nm int(3) QUARTER(a)+0 +t1f_ps int(3) ?+0 +t1e_nm decimal(3,1) EXTRACT(QUARTER FROM a)+0.0 +t1e_ps decimal(3,1) ?+0.0 +t1f_nm decimal(3,1) QUARTER(a)+0.0 +t1f_ps decimal(3,1) ?+0.0 +t1e_nm varchar(1) CONCAT(EXTRACT(QUARTER FROM a)) +t1e_ps varchar(1) CONCAT(?) +t1f_nm varchar(1) CONCAT(QUARTER(a)) +t1f_ps varchar(1) CONCAT(?) +t1e_nm int(2) LEAST(EXTRACT(QUARTER FROM a),EXTRACT(QUARTER FROM a)) +t1e_ps int(2) LEAST(?,?) +t1f_nm int(2) LEAST(QUARTER(a),QUARTER(a)) +t1f_ps int(2) LEAST(?,?) +t1e_nm int(2) COALESCE(EXTRACT(QUARTER FROM a)) +t1e_ps int(2) COALESCE(?) +t1f_nm int(2) COALESCE(QUARTER(a)) +t1f_ps int(2) COALESCE(?) +t1e_nm int(2) COALESCE(EXTRACT(QUARTER FROM a),CAST(1 AS SIGNED)) +t1e_ps int(2) COALESCE(?,CAST(1 AS SIGNED)) +t1f_nm int(2) COALESCE(QUARTER(a),CAST(1 AS SIGNED)) +t1f_ps int(2) COALESCE(?,CAST(1 AS SIGNED)) +t1e_nm decimal(1,0) COALESCE(EXTRACT(QUARTER FROM a),CAST(1 AS UNSIGNED)) +t1e_ps decimal(1,0) COALESCE(?,CAST(1 AS UNSIGNED)) +t1f_nm decimal(1,0) COALESCE(QUARTER(a),CAST(1 AS UNSIGNED)) +t1f_ps decimal(1,0) COALESCE(?,CAST(1 AS UNSIGNED)) +t1e_nm int(2) @a:=EXTRACT(QUARTER FROM a) +t1e_ps int(2) @a:=? +t1f_nm int(2) @a:=QUARTER(a) +t1f_ps int(2) @a:=? +t2e_nm decimal(5,4) AVG(EXTRACT(QUARTER FROM a)) +t2e_ps decimal(5,4) AVG(?) +t2f_nm decimal(5,4) AVG(QUARTER(a)) +t2f_ps decimal(5,4) AVG(?) +t2e_nm bigint(2) MIN(EXTRACT(QUARTER FROM a)) +t2e_ps bigint(2) MIN(?) +t2f_nm bigint(2) MIN(QUARTER(a)) +t2f_ps bigint(2) MIN(?) +t2e_nm bigint(2) MAX(EXTRACT(QUARTER FROM a)) +t2e_ps bigint(2) MAX(?) +t2f_nm bigint(2) MAX(QUARTER(a)) +t2f_ps bigint(2) MAX(?) +t2e_nm mediumtext GROUP_CONCAT(EXTRACT(QUARTER FROM a)) +t2e_ps mediumtext GROUP_CONCAT(?) +t2f_nm mediumtext GROUP_CONCAT(QUARTER(a)) +t2f_ps mediumtext GROUP_CONCAT(?) + + +# EXTRACT(MONTH FROM expr) and MONTH(expr) are equavalent +CALL p1('MONTH'); +EXECUTE IMMEDIATE 'CREATE TABLE t1e_ps AS SELECT ?, CAST(? AS UNSIGNED), CAST(? AS SIGNED), ABS(?), ROUND(?), -?, ROUND(?,-1), ?+0, ?+0.0, CONCAT(?), LEAST(?,?), COALESCE(?), COALESCE(?,CAST(1 AS SIGNED)), COALESCE(?,CAST(1 AS UNSIGNED)), @a:=?' USING EXTRACT(MONTH FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MONTH FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MONTH FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MONTH FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MONTH FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MONTH FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MONTH FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MONTH FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MONTH FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MONTH FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MONTH FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MONTH FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MONTH FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MONTH FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MONTH FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MONTH FROM TIMESTAMP'2001-12-13 10:20:30.999999'); +EXECUTE IMMEDIATE 'CREATE TABLE t1f_ps AS SELECT ?, CAST(? AS UNSIGNED), CAST(? AS SIGNED), ABS(?), ROUND(?), -?, ROUND(?,-1), ?+0, ?+0.0, CONCAT(?), LEAST(?,?), COALESCE(?), COALESCE(?,CAST(1 AS SIGNED)), COALESCE(?,CAST(1 AS UNSIGNED)), @a:=?' USING MONTH(TIMESTAMP'2001-12-13 10:20:30.999999'), MONTH(TIMESTAMP'2001-12-13 10:20:30.999999'), MONTH(TIMESTAMP'2001-12-13 10:20:30.999999'), MONTH(TIMESTAMP'2001-12-13 10:20:30.999999'), MONTH(TIMESTAMP'2001-12-13 10:20:30.999999'), MONTH(TIMESTAMP'2001-12-13 10:20:30.999999'), MONTH(TIMESTAMP'2001-12-13 10:20:30.999999'), MONTH(TIMESTAMP'2001-12-13 10:20:30.999999'), MONTH(TIMESTAMP'2001-12-13 10:20:30.999999'), MONTH(TIMESTAMP'2001-12-13 10:20:30.999999'), MONTH(TIMESTAMP'2001-12-13 10:20:30.999999'), MONTH(TIMESTAMP'2001-12-13 10:20:30.999999'), MONTH(TIMESTAMP'2001-12-13 10:20:30.999999'), MONTH(TIMESTAMP'2001-12-13 10:20:30.999999'), MONTH(TIMESTAMP'2001-12-13 10:20:30.999999'), MONTH(TIMESTAMP'2001-12-13 10:20:30.999999'); +EXECUTE IMMEDIATE 'CREATE TABLE t2e_ps AS SELECT AVG(?), MIN(?), MAX(?), GROUP_CONCAT(?)' USING EXTRACT(MONTH FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MONTH FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MONTH FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MONTH FROM TIMESTAMP'2001-12-13 10:20:30.999999'); +EXECUTE IMMEDIATE 'CREATE TABLE t2f_ps AS SELECT AVG(?), MIN(?), MAX(?), GROUP_CONCAT(?)' USING MONTH(TIMESTAMP'2001-12-13 10:20:30.999999'), MONTH(TIMESTAMP'2001-12-13 10:20:30.999999'), MONTH(TIMESTAMP'2001-12-13 10:20:30.999999'), MONTH(TIMESTAMP'2001-12-13 10:20:30.999999'); +CALL show_drop; +TABLE_NAME COLUMN_TYPE COLUMN_NAME +t1e_nm int(3) EXTRACT(MONTH FROM a) +t1e_ps int(3) ? +t1f_nm int(3) MONTH(a) +t1f_ps int(3) ? +t1e_nm int(2) unsigned CAST(EXTRACT(MONTH FROM a) AS UNSIGNED) +t1e_ps int(2) unsigned CAST(? AS UNSIGNED) +t1f_nm int(2) unsigned CAST(MONTH(a) AS UNSIGNED) +t1f_ps int(2) unsigned CAST(? AS UNSIGNED) +t1e_nm int(3) CAST(EXTRACT(MONTH FROM a) AS SIGNED) +t1e_ps int(3) CAST(? AS SIGNED) +t1f_nm int(3) CAST(MONTH(a) AS SIGNED) +t1f_ps int(3) CAST(? AS SIGNED) +t1e_nm int(3) ABS(EXTRACT(MONTH FROM a)) +t1e_ps int(3) ABS(?) +t1f_nm int(3) ABS(MONTH(a)) +t1f_ps int(3) ABS(?) +t1e_nm int(3) ROUND(EXTRACT(MONTH FROM a)) +t1e_ps int(3) ROUND(?) +t1f_nm int(3) ROUND(MONTH(a)) +t1f_ps int(3) ROUND(?) +t1e_nm int(3) -EXTRACT(MONTH FROM a) +t1e_ps int(3) -? +t1f_nm int(3) -MONTH(a) +t1f_ps int(3) -? +t1e_nm int(4) ROUND(EXTRACT(MONTH FROM a),-1) +t1e_ps int(4) ROUND(?,-1) +t1f_nm int(4) ROUND(MONTH(a),-1) +t1f_ps int(4) ROUND(?,-1) +t1e_nm int(4) EXTRACT(MONTH FROM a)+0 +t1e_ps int(4) ?+0 +t1f_nm int(4) MONTH(a)+0 +t1f_ps int(4) ?+0 +t1e_nm decimal(4,1) EXTRACT(MONTH FROM a)+0.0 +t1e_ps decimal(4,1) ?+0.0 +t1f_nm decimal(4,1) MONTH(a)+0.0 +t1f_ps decimal(4,1) ?+0.0 +t1e_nm varchar(2) CONCAT(EXTRACT(MONTH FROM a)) +t1e_ps varchar(2) CONCAT(?) +t1f_nm varchar(2) CONCAT(MONTH(a)) +t1f_ps varchar(2) CONCAT(?) +t1e_nm int(3) LEAST(EXTRACT(MONTH FROM a),EXTRACT(MONTH FROM a)) +t1e_ps int(3) LEAST(?,?) +t1f_nm int(3) LEAST(MONTH(a),MONTH(a)) +t1f_ps int(3) LEAST(?,?) +t1e_nm int(3) COALESCE(EXTRACT(MONTH FROM a)) +t1e_ps int(3) COALESCE(?) +t1f_nm int(3) COALESCE(MONTH(a)) +t1f_ps int(3) COALESCE(?) +t1e_nm int(3) COALESCE(EXTRACT(MONTH FROM a),CAST(1 AS SIGNED)) +t1e_ps int(3) COALESCE(?,CAST(1 AS SIGNED)) +t1f_nm int(3) COALESCE(MONTH(a),CAST(1 AS SIGNED)) +t1f_ps int(3) COALESCE(?,CAST(1 AS SIGNED)) +t1e_nm decimal(2,0) COALESCE(EXTRACT(MONTH FROM a),CAST(1 AS UNSIGNED)) +t1e_ps decimal(2,0) COALESCE(?,CAST(1 AS UNSIGNED)) +t1f_nm decimal(2,0) COALESCE(MONTH(a),CAST(1 AS UNSIGNED)) +t1f_ps decimal(2,0) COALESCE(?,CAST(1 AS UNSIGNED)) +t1e_nm int(3) @a:=EXTRACT(MONTH FROM a) +t1e_ps int(3) @a:=? +t1f_nm int(3) @a:=MONTH(a) +t1f_ps int(3) @a:=? +t2e_nm decimal(6,4) AVG(EXTRACT(MONTH FROM a)) +t2e_ps decimal(6,4) AVG(?) +t2f_nm decimal(6,4) AVG(MONTH(a)) +t2f_ps decimal(6,4) AVG(?) +t2e_nm bigint(3) MIN(EXTRACT(MONTH FROM a)) +t2e_ps bigint(3) MIN(?) +t2f_nm bigint(3) MIN(MONTH(a)) +t2f_ps bigint(3) MIN(?) +t2e_nm bigint(3) MAX(EXTRACT(MONTH FROM a)) +t2e_ps bigint(3) MAX(?) +t2f_nm bigint(3) MAX(MONTH(a)) +t2f_ps bigint(3) MAX(?) +t2e_nm mediumtext GROUP_CONCAT(EXTRACT(MONTH FROM a)) +t2e_ps mediumtext GROUP_CONCAT(?) +t2f_nm mediumtext GROUP_CONCAT(MONTH(a)) +t2f_ps mediumtext GROUP_CONCAT(?) + + +# EXTRACT(WEEK FROM expr) and WEEK(expr) are equavalent +CALL p1('WEEK'); +EXECUTE IMMEDIATE 'CREATE TABLE t1e_ps AS SELECT ?, CAST(? AS UNSIGNED), CAST(? AS SIGNED), ABS(?), ROUND(?), -?, ROUND(?,-1), ?+0, ?+0.0, CONCAT(?), LEAST(?,?), COALESCE(?), COALESCE(?,CAST(1 AS SIGNED)), COALESCE(?,CAST(1 AS UNSIGNED)), @a:=?' USING EXTRACT(WEEK FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(WEEK FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(WEEK FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(WEEK FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(WEEK FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(WEEK FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(WEEK FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(WEEK FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(WEEK FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(WEEK FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(WEEK FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(WEEK FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(WEEK FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(WEEK FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(WEEK FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(WEEK FROM TIMESTAMP'2001-12-13 10:20:30.999999'); +EXECUTE IMMEDIATE 'CREATE TABLE t1f_ps AS SELECT ?, CAST(? AS UNSIGNED), CAST(? AS SIGNED), ABS(?), ROUND(?), -?, ROUND(?,-1), ?+0, ?+0.0, CONCAT(?), LEAST(?,?), COALESCE(?), COALESCE(?,CAST(1 AS SIGNED)), COALESCE(?,CAST(1 AS UNSIGNED)), @a:=?' USING WEEK(TIMESTAMP'2001-12-13 10:20:30.999999'), WEEK(TIMESTAMP'2001-12-13 10:20:30.999999'), WEEK(TIMESTAMP'2001-12-13 10:20:30.999999'), WEEK(TIMESTAMP'2001-12-13 10:20:30.999999'), WEEK(TIMESTAMP'2001-12-13 10:20:30.999999'), WEEK(TIMESTAMP'2001-12-13 10:20:30.999999'), WEEK(TIMESTAMP'2001-12-13 10:20:30.999999'), WEEK(TIMESTAMP'2001-12-13 10:20:30.999999'), WEEK(TIMESTAMP'2001-12-13 10:20:30.999999'), WEEK(TIMESTAMP'2001-12-13 10:20:30.999999'), WEEK(TIMESTAMP'2001-12-13 10:20:30.999999'), WEEK(TIMESTAMP'2001-12-13 10:20:30.999999'), WEEK(TIMESTAMP'2001-12-13 10:20:30.999999'), WEEK(TIMESTAMP'2001-12-13 10:20:30.999999'), WEEK(TIMESTAMP'2001-12-13 10:20:30.999999'), WEEK(TIMESTAMP'2001-12-13 10:20:30.999999'); +EXECUTE IMMEDIATE 'CREATE TABLE t2e_ps AS SELECT AVG(?), MIN(?), MAX(?), GROUP_CONCAT(?)' USING EXTRACT(WEEK FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(WEEK FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(WEEK FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(WEEK FROM TIMESTAMP'2001-12-13 10:20:30.999999'); +EXECUTE IMMEDIATE 'CREATE TABLE t2f_ps AS SELECT AVG(?), MIN(?), MAX(?), GROUP_CONCAT(?)' USING WEEK(TIMESTAMP'2001-12-13 10:20:30.999999'), WEEK(TIMESTAMP'2001-12-13 10:20:30.999999'), WEEK(TIMESTAMP'2001-12-13 10:20:30.999999'), WEEK(TIMESTAMP'2001-12-13 10:20:30.999999'); +CALL show_drop; +TABLE_NAME COLUMN_TYPE COLUMN_NAME +t1e_nm int(3) EXTRACT(WEEK FROM a) +t1e_ps int(3) ? +t1f_nm int(3) WEEK(a) +t1f_ps int(3) ? +t1e_nm int(2) unsigned CAST(EXTRACT(WEEK FROM a) AS UNSIGNED) +t1e_ps int(2) unsigned CAST(? AS UNSIGNED) +t1f_nm int(2) unsigned CAST(WEEK(a) AS UNSIGNED) +t1f_ps int(2) unsigned CAST(? AS UNSIGNED) +t1e_nm int(3) CAST(EXTRACT(WEEK FROM a) AS SIGNED) +t1e_ps int(3) CAST(? AS SIGNED) +t1f_nm int(3) CAST(WEEK(a) AS SIGNED) +t1f_ps int(3) CAST(? AS SIGNED) +t1e_nm int(3) ABS(EXTRACT(WEEK FROM a)) +t1e_ps int(3) ABS(?) +t1f_nm int(3) ABS(WEEK(a)) +t1f_ps int(3) ABS(?) +t1e_nm int(3) ROUND(EXTRACT(WEEK FROM a)) +t1e_ps int(3) ROUND(?) +t1f_nm int(3) ROUND(WEEK(a)) +t1f_ps int(3) ROUND(?) +t1e_nm int(3) -EXTRACT(WEEK FROM a) +t1e_ps int(3) -? +t1f_nm int(3) -WEEK(a) +t1f_ps int(3) -? +t1e_nm int(4) ROUND(EXTRACT(WEEK FROM a),-1) +t1e_ps int(4) ROUND(?,-1) +t1f_nm int(4) ROUND(WEEK(a),-1) +t1f_ps int(4) ROUND(?,-1) +t1e_nm int(4) EXTRACT(WEEK FROM a)+0 +t1e_ps int(4) ?+0 +t1f_nm int(4) WEEK(a)+0 +t1f_ps int(4) ?+0 +t1e_nm decimal(4,1) EXTRACT(WEEK FROM a)+0.0 +t1e_ps decimal(4,1) ?+0.0 +t1f_nm decimal(4,1) WEEK(a)+0.0 +t1f_ps decimal(4,1) ?+0.0 +t1e_nm varchar(2) CONCAT(EXTRACT(WEEK FROM a)) +t1e_ps varchar(2) CONCAT(?) +t1f_nm varchar(2) CONCAT(WEEK(a)) +t1f_ps varchar(2) CONCAT(?) +t1e_nm int(3) LEAST(EXTRACT(WEEK FROM a),EXTRACT(WEEK FROM a)) +t1e_ps int(3) LEAST(?,?) +t1f_nm int(3) LEAST(WEEK(a),WEEK(a)) +t1f_ps int(3) LEAST(?,?) +t1e_nm int(3) COALESCE(EXTRACT(WEEK FROM a)) +t1e_ps int(3) COALESCE(?) +t1f_nm int(3) COALESCE(WEEK(a)) +t1f_ps int(3) COALESCE(?) +t1e_nm int(3) COALESCE(EXTRACT(WEEK FROM a),CAST(1 AS SIGNED)) +t1e_ps int(3) COALESCE(?,CAST(1 AS SIGNED)) +t1f_nm int(3) COALESCE(WEEK(a),CAST(1 AS SIGNED)) +t1f_ps int(3) COALESCE(?,CAST(1 AS SIGNED)) +t1e_nm decimal(2,0) COALESCE(EXTRACT(WEEK FROM a),CAST(1 AS UNSIGNED)) +t1e_ps decimal(2,0) COALESCE(?,CAST(1 AS UNSIGNED)) +t1f_nm decimal(2,0) COALESCE(WEEK(a),CAST(1 AS UNSIGNED)) +t1f_ps decimal(2,0) COALESCE(?,CAST(1 AS UNSIGNED)) +t1e_nm int(3) @a:=EXTRACT(WEEK FROM a) +t1e_ps int(3) @a:=? +t1f_nm int(3) @a:=WEEK(a) +t1f_ps int(3) @a:=? +t2e_nm decimal(6,4) AVG(EXTRACT(WEEK FROM a)) +t2e_ps decimal(6,4) AVG(?) +t2f_nm decimal(6,4) AVG(WEEK(a)) +t2f_ps decimal(6,4) AVG(?) +t2e_nm bigint(3) MIN(EXTRACT(WEEK FROM a)) +t2e_ps bigint(3) MIN(?) +t2f_nm bigint(3) MIN(WEEK(a)) +t2f_ps bigint(3) MIN(?) +t2e_nm bigint(3) MAX(EXTRACT(WEEK FROM a)) +t2e_ps bigint(3) MAX(?) +t2f_nm bigint(3) MAX(WEEK(a)) +t2f_ps bigint(3) MAX(?) +t2e_nm mediumtext GROUP_CONCAT(EXTRACT(WEEK FROM a)) +t2e_ps mediumtext GROUP_CONCAT(?) +t2f_nm mediumtext GROUP_CONCAT(WEEK(a)) +t2f_ps mediumtext GROUP_CONCAT(?) + + +# EXTRACT(DAY FROM expr) returns hours/24 and includes the sign for TIME +# DAY(expr) returns the DD part of CAST(expr AS DATETIME) +CALL p1('DAY'); +EXECUTE IMMEDIATE 'CREATE TABLE t1e_ps AS SELECT ?, CAST(? AS UNSIGNED), CAST(? AS SIGNED), ABS(?), ROUND(?), -?, ROUND(?,-1), ?+0, ?+0.0, CONCAT(?), LEAST(?,?), COALESCE(?), COALESCE(?,CAST(1 AS SIGNED)), COALESCE(?,CAST(1 AS UNSIGNED)), @a:=?' USING EXTRACT(DAY FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(DAY FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(DAY FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(DAY FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(DAY FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(DAY FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(DAY FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(DAY FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(DAY FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(DAY FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(DAY FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(DAY FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(DAY FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(DAY FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(DAY FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(DAY FROM TIMESTAMP'2001-12-13 10:20:30.999999'); +EXECUTE IMMEDIATE 'CREATE TABLE t1f_ps AS SELECT ?, CAST(? AS UNSIGNED), CAST(? AS SIGNED), ABS(?), ROUND(?), -?, ROUND(?,-1), ?+0, ?+0.0, CONCAT(?), LEAST(?,?), COALESCE(?), COALESCE(?,CAST(1 AS SIGNED)), COALESCE(?,CAST(1 AS UNSIGNED)), @a:=?' USING DAY(TIMESTAMP'2001-12-13 10:20:30.999999'), DAY(TIMESTAMP'2001-12-13 10:20:30.999999'), DAY(TIMESTAMP'2001-12-13 10:20:30.999999'), DAY(TIMESTAMP'2001-12-13 10:20:30.999999'), DAY(TIMESTAMP'2001-12-13 10:20:30.999999'), DAY(TIMESTAMP'2001-12-13 10:20:30.999999'), DAY(TIMESTAMP'2001-12-13 10:20:30.999999'), DAY(TIMESTAMP'2001-12-13 10:20:30.999999'), DAY(TIMESTAMP'2001-12-13 10:20:30.999999'), DAY(TIMESTAMP'2001-12-13 10:20:30.999999'), DAY(TIMESTAMP'2001-12-13 10:20:30.999999'), DAY(TIMESTAMP'2001-12-13 10:20:30.999999'), DAY(TIMESTAMP'2001-12-13 10:20:30.999999'), DAY(TIMESTAMP'2001-12-13 10:20:30.999999'), DAY(TIMESTAMP'2001-12-13 10:20:30.999999'), DAY(TIMESTAMP'2001-12-13 10:20:30.999999'); +EXECUTE IMMEDIATE 'CREATE TABLE t2e_ps AS SELECT AVG(?), MIN(?), MAX(?), GROUP_CONCAT(?)' USING EXTRACT(DAY FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(DAY FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(DAY FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(DAY FROM TIMESTAMP'2001-12-13 10:20:30.999999'); +EXECUTE IMMEDIATE 'CREATE TABLE t2f_ps AS SELECT AVG(?), MIN(?), MAX(?), GROUP_CONCAT(?)' USING DAY(TIMESTAMP'2001-12-13 10:20:30.999999'), DAY(TIMESTAMP'2001-12-13 10:20:30.999999'), DAY(TIMESTAMP'2001-12-13 10:20:30.999999'), DAY(TIMESTAMP'2001-12-13 10:20:30.999999'); +CALL show_drop; +TABLE_NAME COLUMN_TYPE COLUMN_NAME +t1e_nm int(3) EXTRACT(DAY FROM a) +t1e_ps int(3) ? +t1f_nm int(3) DAY(a) +t1f_ps int(3) ? +t1e_nm bigint(20) unsigned CAST(EXTRACT(DAY FROM a) AS UNSIGNED) +t1e_ps bigint(20) unsigned CAST(? AS UNSIGNED) +t1f_nm int(2) unsigned CAST(DAY(a) AS UNSIGNED) +t1f_ps int(2) unsigned CAST(? AS UNSIGNED) +t1e_nm int(3) CAST(EXTRACT(DAY FROM a) AS SIGNED) +t1e_ps int(3) CAST(? AS SIGNED) +t1f_nm int(3) CAST(DAY(a) AS SIGNED) +t1f_ps int(3) CAST(? AS SIGNED) +t1e_nm int(3) ABS(EXTRACT(DAY FROM a)) +t1e_ps int(3) ABS(?) +t1f_nm int(3) ABS(DAY(a)) +t1f_ps int(3) ABS(?) +t1e_nm int(3) ROUND(EXTRACT(DAY FROM a)) +t1e_ps int(3) ROUND(?) +t1f_nm int(3) ROUND(DAY(a)) +t1f_ps int(3) ROUND(?) +t1e_nm int(4) -EXTRACT(DAY FROM a) +t1e_ps int(4) -? +t1f_nm int(3) -DAY(a) +t1f_ps int(3) -? +t1e_nm int(4) ROUND(EXTRACT(DAY FROM a),-1) +t1e_ps int(4) ROUND(?,-1) +t1f_nm int(4) ROUND(DAY(a),-1) +t1f_ps int(4) ROUND(?,-1) +t1e_nm int(4) EXTRACT(DAY FROM a)+0 +t1e_ps int(4) ?+0 +t1f_nm int(4) DAY(a)+0 +t1f_ps int(4) ?+0 +t1e_nm decimal(4,1) EXTRACT(DAY FROM a)+0.0 +t1e_ps decimal(4,1) ?+0.0 +t1f_nm decimal(4,1) DAY(a)+0.0 +t1f_ps decimal(4,1) ?+0.0 +t1e_nm varchar(3) CONCAT(EXTRACT(DAY FROM a)) +t1e_ps varchar(3) CONCAT(?) +t1f_nm varchar(2) CONCAT(DAY(a)) +t1f_ps varchar(2) CONCAT(?) +t1e_nm int(3) LEAST(EXTRACT(DAY FROM a),EXTRACT(DAY FROM a)) +t1e_ps int(3) LEAST(?,?) +t1f_nm int(3) LEAST(DAY(a),DAY(a)) +t1f_ps int(3) LEAST(?,?) +t1e_nm int(3) COALESCE(EXTRACT(DAY FROM a)) +t1e_ps int(3) COALESCE(?) +t1f_nm int(3) COALESCE(DAY(a)) +t1f_ps int(3) COALESCE(?) +t1e_nm int(3) COALESCE(EXTRACT(DAY FROM a),CAST(1 AS SIGNED)) +t1e_ps int(3) COALESCE(?,CAST(1 AS SIGNED)) +t1f_nm int(3) COALESCE(DAY(a),CAST(1 AS SIGNED)) +t1f_ps int(3) COALESCE(?,CAST(1 AS SIGNED)) +t1e_nm decimal(2,0) COALESCE(EXTRACT(DAY FROM a),CAST(1 AS UNSIGNED)) +t1e_ps decimal(2,0) COALESCE(?,CAST(1 AS UNSIGNED)) +t1f_nm decimal(2,0) COALESCE(DAY(a),CAST(1 AS UNSIGNED)) +t1f_ps decimal(2,0) COALESCE(?,CAST(1 AS UNSIGNED)) +t1e_nm int(3) @a:=EXTRACT(DAY FROM a) +t1e_ps int(3) @a:=? +t1f_nm int(3) @a:=DAY(a) +t1f_ps int(3) @a:=? +t2e_nm decimal(6,4) AVG(EXTRACT(DAY FROM a)) +t2e_ps decimal(6,4) AVG(?) +t2f_nm decimal(6,4) AVG(DAY(a)) +t2f_ps decimal(6,4) AVG(?) +t2e_nm bigint(3) MIN(EXTRACT(DAY FROM a)) +t2e_ps bigint(3) MIN(?) +t2f_nm bigint(3) MIN(DAY(a)) +t2f_ps bigint(3) MIN(?) +t2e_nm bigint(3) MAX(EXTRACT(DAY FROM a)) +t2e_ps bigint(3) MAX(?) +t2f_nm bigint(3) MAX(DAY(a)) +t2f_ps bigint(3) MAX(?) +t2e_nm mediumtext GROUP_CONCAT(EXTRACT(DAY FROM a)) +t2e_ps mediumtext GROUP_CONCAT(?) +t2f_nm mediumtext GROUP_CONCAT(DAY(a)) +t2f_ps mediumtext GROUP_CONCAT(?) + + +# EXTRACT(HOUR FROM expr) returns hours%24 and includes the sign for TIME +# HOUR(expr) returns the hh part of CAST(expr AS DATETIME) +CALL p1('HOUR'); +EXECUTE IMMEDIATE 'CREATE TABLE t1e_ps AS SELECT ?, CAST(? AS UNSIGNED), CAST(? AS SIGNED), ABS(?), ROUND(?), -?, ROUND(?,-1), ?+0, ?+0.0, CONCAT(?), LEAST(?,?), COALESCE(?), COALESCE(?,CAST(1 AS SIGNED)), COALESCE(?,CAST(1 AS UNSIGNED)), @a:=?' USING EXTRACT(HOUR FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(HOUR FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(HOUR FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(HOUR FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(HOUR FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(HOUR FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(HOUR FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(HOUR FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(HOUR FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(HOUR FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(HOUR FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(HOUR FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(HOUR FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(HOUR FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(HOUR FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(HOUR FROM TIMESTAMP'2001-12-13 10:20:30.999999'); +EXECUTE IMMEDIATE 'CREATE TABLE t1f_ps AS SELECT ?, CAST(? AS UNSIGNED), CAST(? AS SIGNED), ABS(?), ROUND(?), -?, ROUND(?,-1), ?+0, ?+0.0, CONCAT(?), LEAST(?,?), COALESCE(?), COALESCE(?,CAST(1 AS SIGNED)), COALESCE(?,CAST(1 AS UNSIGNED)), @a:=?' USING HOUR(TIMESTAMP'2001-12-13 10:20:30.999999'), HOUR(TIMESTAMP'2001-12-13 10:20:30.999999'), HOUR(TIMESTAMP'2001-12-13 10:20:30.999999'), HOUR(TIMESTAMP'2001-12-13 10:20:30.999999'), HOUR(TIMESTAMP'2001-12-13 10:20:30.999999'), HOUR(TIMESTAMP'2001-12-13 10:20:30.999999'), HOUR(TIMESTAMP'2001-12-13 10:20:30.999999'), HOUR(TIMESTAMP'2001-12-13 10:20:30.999999'), HOUR(TIMESTAMP'2001-12-13 10:20:30.999999'), HOUR(TIMESTAMP'2001-12-13 10:20:30.999999'), HOUR(TIMESTAMP'2001-12-13 10:20:30.999999'), HOUR(TIMESTAMP'2001-12-13 10:20:30.999999'), HOUR(TIMESTAMP'2001-12-13 10:20:30.999999'), HOUR(TIMESTAMP'2001-12-13 10:20:30.999999'), HOUR(TIMESTAMP'2001-12-13 10:20:30.999999'), HOUR(TIMESTAMP'2001-12-13 10:20:30.999999'); +EXECUTE IMMEDIATE 'CREATE TABLE t2e_ps AS SELECT AVG(?), MIN(?), MAX(?), GROUP_CONCAT(?)' USING EXTRACT(HOUR FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(HOUR FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(HOUR FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(HOUR FROM TIMESTAMP'2001-12-13 10:20:30.999999'); +EXECUTE IMMEDIATE 'CREATE TABLE t2f_ps AS SELECT AVG(?), MIN(?), MAX(?), GROUP_CONCAT(?)' USING HOUR(TIMESTAMP'2001-12-13 10:20:30.999999'), HOUR(TIMESTAMP'2001-12-13 10:20:30.999999'), HOUR(TIMESTAMP'2001-12-13 10:20:30.999999'), HOUR(TIMESTAMP'2001-12-13 10:20:30.999999'); +CALL show_drop; +TABLE_NAME COLUMN_TYPE COLUMN_NAME +t1e_nm int(3) EXTRACT(HOUR FROM a) +t1e_ps int(3) ? +t1f_nm int(3) HOUR(a) +t1f_ps int(3) ? +t1e_nm bigint(20) unsigned CAST(EXTRACT(HOUR FROM a) AS UNSIGNED) +t1e_ps bigint(20) unsigned CAST(? AS UNSIGNED) +t1f_nm int(2) unsigned CAST(HOUR(a) AS UNSIGNED) +t1f_ps int(2) unsigned CAST(? AS UNSIGNED) +t1e_nm int(3) CAST(EXTRACT(HOUR FROM a) AS SIGNED) +t1e_ps int(3) CAST(? AS SIGNED) +t1f_nm int(3) CAST(HOUR(a) AS SIGNED) +t1f_ps int(3) CAST(? AS SIGNED) +t1e_nm int(3) ABS(EXTRACT(HOUR FROM a)) +t1e_ps int(3) ABS(?) +t1f_nm int(3) ABS(HOUR(a)) +t1f_ps int(3) ABS(?) +t1e_nm int(3) ROUND(EXTRACT(HOUR FROM a)) +t1e_ps int(3) ROUND(?) +t1f_nm int(3) ROUND(HOUR(a)) +t1f_ps int(3) ROUND(?) +t1e_nm int(4) -EXTRACT(HOUR FROM a) +t1e_ps int(4) -? +t1f_nm int(3) -HOUR(a) +t1f_ps int(3) -? +t1e_nm int(4) ROUND(EXTRACT(HOUR FROM a),-1) +t1e_ps int(4) ROUND(?,-1) +t1f_nm int(4) ROUND(HOUR(a),-1) +t1f_ps int(4) ROUND(?,-1) +t1e_nm int(4) EXTRACT(HOUR FROM a)+0 +t1e_ps int(4) ?+0 +t1f_nm int(4) HOUR(a)+0 +t1f_ps int(4) ?+0 +t1e_nm decimal(4,1) EXTRACT(HOUR FROM a)+0.0 +t1e_ps decimal(4,1) ?+0.0 +t1f_nm decimal(4,1) HOUR(a)+0.0 +t1f_ps decimal(4,1) ?+0.0 +t1e_nm varchar(3) CONCAT(EXTRACT(HOUR FROM a)) +t1e_ps varchar(3) CONCAT(?) +t1f_nm varchar(2) CONCAT(HOUR(a)) +t1f_ps varchar(2) CONCAT(?) +t1e_nm int(3) LEAST(EXTRACT(HOUR FROM a),EXTRACT(HOUR FROM a)) +t1e_ps int(3) LEAST(?,?) +t1f_nm int(3) LEAST(HOUR(a),HOUR(a)) +t1f_ps int(3) LEAST(?,?) +t1e_nm int(3) COALESCE(EXTRACT(HOUR FROM a)) +t1e_ps int(3) COALESCE(?) +t1f_nm int(3) COALESCE(HOUR(a)) +t1f_ps int(3) COALESCE(?) +t1e_nm int(3) COALESCE(EXTRACT(HOUR FROM a),CAST(1 AS SIGNED)) +t1e_ps int(3) COALESCE(?,CAST(1 AS SIGNED)) +t1f_nm int(3) COALESCE(HOUR(a),CAST(1 AS SIGNED)) +t1f_ps int(3) COALESCE(?,CAST(1 AS SIGNED)) +t1e_nm decimal(2,0) COALESCE(EXTRACT(HOUR FROM a),CAST(1 AS UNSIGNED)) +t1e_ps decimal(2,0) COALESCE(?,CAST(1 AS UNSIGNED)) +t1f_nm decimal(2,0) COALESCE(HOUR(a),CAST(1 AS UNSIGNED)) +t1f_ps decimal(2,0) COALESCE(?,CAST(1 AS UNSIGNED)) +t1e_nm int(3) @a:=EXTRACT(HOUR FROM a) +t1e_ps int(3) @a:=? +t1f_nm int(3) @a:=HOUR(a) +t1f_ps int(3) @a:=? +t2e_nm decimal(6,4) AVG(EXTRACT(HOUR FROM a)) +t2e_ps decimal(6,4) AVG(?) +t2f_nm decimal(6,4) AVG(HOUR(a)) +t2f_ps decimal(6,4) AVG(?) +t2e_nm bigint(3) MIN(EXTRACT(HOUR FROM a)) +t2e_ps bigint(3) MIN(?) +t2f_nm bigint(3) MIN(HOUR(a)) +t2f_ps bigint(3) MIN(?) +t2e_nm bigint(3) MAX(EXTRACT(HOUR FROM a)) +t2e_ps bigint(3) MAX(?) +t2f_nm bigint(3) MAX(HOUR(a)) +t2f_ps bigint(3) MAX(?) +t2e_nm mediumtext GROUP_CONCAT(EXTRACT(HOUR FROM a)) +t2e_ps mediumtext GROUP_CONCAT(?) +t2f_nm mediumtext GROUP_CONCAT(HOUR(a)) +t2f_ps mediumtext GROUP_CONCAT(?) + + +# EXTRACT(MINUTE FROM expr) includes the sign for TIME +# MINUTE(expr) returns the absolute value +CALL p1('MINUTE'); +EXECUTE IMMEDIATE 'CREATE TABLE t1e_ps AS SELECT ?, CAST(? AS UNSIGNED), CAST(? AS SIGNED), ABS(?), ROUND(?), -?, ROUND(?,-1), ?+0, ?+0.0, CONCAT(?), LEAST(?,?), COALESCE(?), COALESCE(?,CAST(1 AS SIGNED)), COALESCE(?,CAST(1 AS UNSIGNED)), @a:=?' USING EXTRACT(MINUTE FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MINUTE FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MINUTE FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MINUTE FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MINUTE FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MINUTE FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MINUTE FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MINUTE FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MINUTE FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MINUTE FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MINUTE FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MINUTE FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MINUTE FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MINUTE FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MINUTE FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MINUTE FROM TIMESTAMP'2001-12-13 10:20:30.999999'); +EXECUTE IMMEDIATE 'CREATE TABLE t1f_ps AS SELECT ?, CAST(? AS UNSIGNED), CAST(? AS SIGNED), ABS(?), ROUND(?), -?, ROUND(?,-1), ?+0, ?+0.0, CONCAT(?), LEAST(?,?), COALESCE(?), COALESCE(?,CAST(1 AS SIGNED)), COALESCE(?,CAST(1 AS UNSIGNED)), @a:=?' USING MINUTE(TIMESTAMP'2001-12-13 10:20:30.999999'), MINUTE(TIMESTAMP'2001-12-13 10:20:30.999999'), MINUTE(TIMESTAMP'2001-12-13 10:20:30.999999'), MINUTE(TIMESTAMP'2001-12-13 10:20:30.999999'), MINUTE(TIMESTAMP'2001-12-13 10:20:30.999999'), MINUTE(TIMESTAMP'2001-12-13 10:20:30.999999'), MINUTE(TIMESTAMP'2001-12-13 10:20:30.999999'), MINUTE(TIMESTAMP'2001-12-13 10:20:30.999999'), MINUTE(TIMESTAMP'2001-12-13 10:20:30.999999'), MINUTE(TIMESTAMP'2001-12-13 10:20:30.999999'), MINUTE(TIMESTAMP'2001-12-13 10:20:30.999999'), MINUTE(TIMESTAMP'2001-12-13 10:20:30.999999'), MINUTE(TIMESTAMP'2001-12-13 10:20:30.999999'), MINUTE(TIMESTAMP'2001-12-13 10:20:30.999999'), MINUTE(TIMESTAMP'2001-12-13 10:20:30.999999'), MINUTE(TIMESTAMP'2001-12-13 10:20:30.999999'); +EXECUTE IMMEDIATE 'CREATE TABLE t2e_ps AS SELECT AVG(?), MIN(?), MAX(?), GROUP_CONCAT(?)' USING EXTRACT(MINUTE FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MINUTE FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MINUTE FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MINUTE FROM TIMESTAMP'2001-12-13 10:20:30.999999'); +EXECUTE IMMEDIATE 'CREATE TABLE t2f_ps AS SELECT AVG(?), MIN(?), MAX(?), GROUP_CONCAT(?)' USING MINUTE(TIMESTAMP'2001-12-13 10:20:30.999999'), MINUTE(TIMESTAMP'2001-12-13 10:20:30.999999'), MINUTE(TIMESTAMP'2001-12-13 10:20:30.999999'), MINUTE(TIMESTAMP'2001-12-13 10:20:30.999999'); +CALL show_drop; +TABLE_NAME COLUMN_TYPE COLUMN_NAME +t1e_nm int(3) EXTRACT(MINUTE FROM a) +t1e_ps int(3) ? +t1f_nm int(3) MINUTE(a) +t1f_ps int(3) ? +t1e_nm bigint(20) unsigned CAST(EXTRACT(MINUTE FROM a) AS UNSIGNED) +t1e_ps bigint(20) unsigned CAST(? AS UNSIGNED) +t1f_nm int(2) unsigned CAST(MINUTE(a) AS UNSIGNED) +t1f_ps int(2) unsigned CAST(? AS UNSIGNED) +t1e_nm int(3) CAST(EXTRACT(MINUTE FROM a) AS SIGNED) +t1e_ps int(3) CAST(? AS SIGNED) +t1f_nm int(3) CAST(MINUTE(a) AS SIGNED) +t1f_ps int(3) CAST(? AS SIGNED) +t1e_nm int(3) ABS(EXTRACT(MINUTE FROM a)) +t1e_ps int(3) ABS(?) +t1f_nm int(3) ABS(MINUTE(a)) +t1f_ps int(3) ABS(?) +t1e_nm int(3) ROUND(EXTRACT(MINUTE FROM a)) +t1e_ps int(3) ROUND(?) +t1f_nm int(3) ROUND(MINUTE(a)) +t1f_ps int(3) ROUND(?) +t1e_nm int(4) -EXTRACT(MINUTE FROM a) +t1e_ps int(4) -? +t1f_nm int(3) -MINUTE(a) +t1f_ps int(3) -? +t1e_nm int(4) ROUND(EXTRACT(MINUTE FROM a),-1) +t1e_ps int(4) ROUND(?,-1) +t1f_nm int(4) ROUND(MINUTE(a),-1) +t1f_ps int(4) ROUND(?,-1) +t1e_nm int(4) EXTRACT(MINUTE FROM a)+0 +t1e_ps int(4) ?+0 +t1f_nm int(4) MINUTE(a)+0 +t1f_ps int(4) ?+0 +t1e_nm decimal(4,1) EXTRACT(MINUTE FROM a)+0.0 +t1e_ps decimal(4,1) ?+0.0 +t1f_nm decimal(4,1) MINUTE(a)+0.0 +t1f_ps decimal(4,1) ?+0.0 +t1e_nm varchar(3) CONCAT(EXTRACT(MINUTE FROM a)) +t1e_ps varchar(3) CONCAT(?) +t1f_nm varchar(2) CONCAT(MINUTE(a)) +t1f_ps varchar(2) CONCAT(?) +t1e_nm int(3) LEAST(EXTRACT(MINUTE FROM a),EXTRACT(MINUTE FROM a)) +t1e_ps int(3) LEAST(?,?) +t1f_nm int(3) LEAST(MINUTE(a),MINUTE(a)) +t1f_ps int(3) LEAST(?,?) +t1e_nm int(3) COALESCE(EXTRACT(MINUTE FROM a)) +t1e_ps int(3) COALESCE(?) +t1f_nm int(3) COALESCE(MINUTE(a)) +t1f_ps int(3) COALESCE(?) +t1e_nm int(3) COALESCE(EXTRACT(MINUTE FROM a),CAST(1 AS SIGNED)) +t1e_ps int(3) COALESCE(?,CAST(1 AS SIGNED)) +t1f_nm int(3) COALESCE(MINUTE(a),CAST(1 AS SIGNED)) +t1f_ps int(3) COALESCE(?,CAST(1 AS SIGNED)) +t1e_nm decimal(2,0) COALESCE(EXTRACT(MINUTE FROM a),CAST(1 AS UNSIGNED)) +t1e_ps decimal(2,0) COALESCE(?,CAST(1 AS UNSIGNED)) +t1f_nm decimal(2,0) COALESCE(MINUTE(a),CAST(1 AS UNSIGNED)) +t1f_ps decimal(2,0) COALESCE(?,CAST(1 AS UNSIGNED)) +t1e_nm int(3) @a:=EXTRACT(MINUTE FROM a) +t1e_ps int(3) @a:=? +t1f_nm int(3) @a:=MINUTE(a) +t1f_ps int(3) @a:=? +t2e_nm decimal(6,4) AVG(EXTRACT(MINUTE FROM a)) +t2e_ps decimal(6,4) AVG(?) +t2f_nm decimal(6,4) AVG(MINUTE(a)) +t2f_ps decimal(6,4) AVG(?) +t2e_nm bigint(3) MIN(EXTRACT(MINUTE FROM a)) +t2e_ps bigint(3) MIN(?) +t2f_nm bigint(3) MIN(MINUTE(a)) +t2f_ps bigint(3) MIN(?) +t2e_nm bigint(3) MAX(EXTRACT(MINUTE FROM a)) +t2e_ps bigint(3) MAX(?) +t2f_nm bigint(3) MAX(MINUTE(a)) +t2f_ps bigint(3) MAX(?) +t2e_nm mediumtext GROUP_CONCAT(EXTRACT(MINUTE FROM a)) +t2e_ps mediumtext GROUP_CONCAT(?) +t2f_nm mediumtext GROUP_CONCAT(MINUTE(a)) +t2f_ps mediumtext GROUP_CONCAT(?) + + +# EXTRACT(SECONDS FROM expr) includes the sign for TIME +# SECONDS(expr) returns the absolute value +CALL p1('SECOND'); +EXECUTE IMMEDIATE 'CREATE TABLE t1e_ps AS SELECT ?, CAST(? AS UNSIGNED), CAST(? AS SIGNED), ABS(?), ROUND(?), -?, ROUND(?,-1), ?+0, ?+0.0, CONCAT(?), LEAST(?,?), COALESCE(?), COALESCE(?,CAST(1 AS SIGNED)), COALESCE(?,CAST(1 AS UNSIGNED)), @a:=?' USING EXTRACT(SECOND FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(SECOND FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(SECOND FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(SECOND FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(SECOND FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(SECOND FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(SECOND FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(SECOND FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(SECOND FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(SECOND FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(SECOND FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(SECOND FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(SECOND FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(SECOND FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(SECOND FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(SECOND FROM TIMESTAMP'2001-12-13 10:20:30.999999'); +EXECUTE IMMEDIATE 'CREATE TABLE t1f_ps AS SELECT ?, CAST(? AS UNSIGNED), CAST(? AS SIGNED), ABS(?), ROUND(?), -?, ROUND(?,-1), ?+0, ?+0.0, CONCAT(?), LEAST(?,?), COALESCE(?), COALESCE(?,CAST(1 AS SIGNED)), COALESCE(?,CAST(1 AS UNSIGNED)), @a:=?' USING SECOND(TIMESTAMP'2001-12-13 10:20:30.999999'), SECOND(TIMESTAMP'2001-12-13 10:20:30.999999'), SECOND(TIMESTAMP'2001-12-13 10:20:30.999999'), SECOND(TIMESTAMP'2001-12-13 10:20:30.999999'), SECOND(TIMESTAMP'2001-12-13 10:20:30.999999'), SECOND(TIMESTAMP'2001-12-13 10:20:30.999999'), SECOND(TIMESTAMP'2001-12-13 10:20:30.999999'), SECOND(TIMESTAMP'2001-12-13 10:20:30.999999'), SECOND(TIMESTAMP'2001-12-13 10:20:30.999999'), SECOND(TIMESTAMP'2001-12-13 10:20:30.999999'), SECOND(TIMESTAMP'2001-12-13 10:20:30.999999'), SECOND(TIMESTAMP'2001-12-13 10:20:30.999999'), SECOND(TIMESTAMP'2001-12-13 10:20:30.999999'), SECOND(TIMESTAMP'2001-12-13 10:20:30.999999'), SECOND(TIMESTAMP'2001-12-13 10:20:30.999999'), SECOND(TIMESTAMP'2001-12-13 10:20:30.999999'); +EXECUTE IMMEDIATE 'CREATE TABLE t2e_ps AS SELECT AVG(?), MIN(?), MAX(?), GROUP_CONCAT(?)' USING EXTRACT(SECOND FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(SECOND FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(SECOND FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(SECOND FROM TIMESTAMP'2001-12-13 10:20:30.999999'); +EXECUTE IMMEDIATE 'CREATE TABLE t2f_ps AS SELECT AVG(?), MIN(?), MAX(?), GROUP_CONCAT(?)' USING SECOND(TIMESTAMP'2001-12-13 10:20:30.999999'), SECOND(TIMESTAMP'2001-12-13 10:20:30.999999'), SECOND(TIMESTAMP'2001-12-13 10:20:30.999999'), SECOND(TIMESTAMP'2001-12-13 10:20:30.999999'); +CALL show_drop; +TABLE_NAME COLUMN_TYPE COLUMN_NAME +t1e_nm int(3) EXTRACT(SECOND FROM a) +t1e_ps int(3) ? +t1f_nm int(3) SECOND(a) +t1f_ps int(3) ? +t1e_nm bigint(20) unsigned CAST(EXTRACT(SECOND FROM a) AS UNSIGNED) +t1e_ps bigint(20) unsigned CAST(? AS UNSIGNED) +t1f_nm int(2) unsigned CAST(SECOND(a) AS UNSIGNED) +t1f_ps int(2) unsigned CAST(? AS UNSIGNED) +t1e_nm int(3) CAST(EXTRACT(SECOND FROM a) AS SIGNED) +t1e_ps int(3) CAST(? AS SIGNED) +t1f_nm int(3) CAST(SECOND(a) AS SIGNED) +t1f_ps int(3) CAST(? AS SIGNED) +t1e_nm int(3) ABS(EXTRACT(SECOND FROM a)) +t1e_ps int(3) ABS(?) +t1f_nm int(3) ABS(SECOND(a)) +t1f_ps int(3) ABS(?) +t1e_nm int(3) ROUND(EXTRACT(SECOND FROM a)) +t1e_ps int(3) ROUND(?) +t1f_nm int(3) ROUND(SECOND(a)) +t1f_ps int(3) ROUND(?) +t1e_nm int(4) -EXTRACT(SECOND FROM a) +t1e_ps int(4) -? +t1f_nm int(3) -SECOND(a) +t1f_ps int(3) -? +t1e_nm int(4) ROUND(EXTRACT(SECOND FROM a),-1) +t1e_ps int(4) ROUND(?,-1) +t1f_nm int(4) ROUND(SECOND(a),-1) +t1f_ps int(4) ROUND(?,-1) +t1e_nm int(4) EXTRACT(SECOND FROM a)+0 +t1e_ps int(4) ?+0 +t1f_nm int(4) SECOND(a)+0 +t1f_ps int(4) ?+0 +t1e_nm decimal(4,1) EXTRACT(SECOND FROM a)+0.0 +t1e_ps decimal(4,1) ?+0.0 +t1f_nm decimal(4,1) SECOND(a)+0.0 +t1f_ps decimal(4,1) ?+0.0 +t1e_nm varchar(3) CONCAT(EXTRACT(SECOND FROM a)) +t1e_ps varchar(3) CONCAT(?) +t1f_nm varchar(2) CONCAT(SECOND(a)) +t1f_ps varchar(2) CONCAT(?) +t1e_nm int(3) LEAST(EXTRACT(SECOND FROM a),EXTRACT(SECOND FROM a)) +t1e_ps int(3) LEAST(?,?) +t1f_nm int(3) LEAST(SECOND(a),SECOND(a)) +t1f_ps int(3) LEAST(?,?) +t1e_nm int(3) COALESCE(EXTRACT(SECOND FROM a)) +t1e_ps int(3) COALESCE(?) +t1f_nm int(3) COALESCE(SECOND(a)) +t1f_ps int(3) COALESCE(?) +t1e_nm int(3) COALESCE(EXTRACT(SECOND FROM a),CAST(1 AS SIGNED)) +t1e_ps int(3) COALESCE(?,CAST(1 AS SIGNED)) +t1f_nm int(3) COALESCE(SECOND(a),CAST(1 AS SIGNED)) +t1f_ps int(3) COALESCE(?,CAST(1 AS SIGNED)) +t1e_nm decimal(2,0) COALESCE(EXTRACT(SECOND FROM a),CAST(1 AS UNSIGNED)) +t1e_ps decimal(2,0) COALESCE(?,CAST(1 AS UNSIGNED)) +t1f_nm decimal(2,0) COALESCE(SECOND(a),CAST(1 AS UNSIGNED)) +t1f_ps decimal(2,0) COALESCE(?,CAST(1 AS UNSIGNED)) +t1e_nm int(3) @a:=EXTRACT(SECOND FROM a) +t1e_ps int(3) @a:=? +t1f_nm int(3) @a:=SECOND(a) +t1f_ps int(3) @a:=? +t2e_nm decimal(6,4) AVG(EXTRACT(SECOND FROM a)) +t2e_ps decimal(6,4) AVG(?) +t2f_nm decimal(6,4) AVG(SECOND(a)) +t2f_ps decimal(6,4) AVG(?) +t2e_nm bigint(3) MIN(EXTRACT(SECOND FROM a)) +t2e_ps bigint(3) MIN(?) +t2f_nm bigint(3) MIN(SECOND(a)) +t2f_ps bigint(3) MIN(?) +t2e_nm bigint(3) MAX(EXTRACT(SECOND FROM a)) +t2e_ps bigint(3) MAX(?) +t2f_nm bigint(3) MAX(SECOND(a)) +t2f_ps bigint(3) MAX(?) +t2e_nm mediumtext GROUP_CONCAT(EXTRACT(SECOND FROM a)) +t2e_ps mediumtext GROUP_CONCAT(?) +t2f_nm mediumtext GROUP_CONCAT(SECOND(a)) +t2f_ps mediumtext GROUP_CONCAT(?) + + +# EXTRACT(MICROSECONDS FROM expr) includes the sign for TIME +# MICROSECONDS(expr) returns the absolute value +CALL p1('MICROSECOND'); +EXECUTE IMMEDIATE 'CREATE TABLE t1e_ps AS SELECT ?, CAST(? AS UNSIGNED), CAST(? AS SIGNED), ABS(?), ROUND(?), -?, ROUND(?,-1), ?+0, ?+0.0, CONCAT(?), LEAST(?,?), COALESCE(?), COALESCE(?,CAST(1 AS SIGNED)), COALESCE(?,CAST(1 AS UNSIGNED)), @a:=?' USING EXTRACT(MICROSECOND FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MICROSECOND FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MICROSECOND FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MICROSECOND FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MICROSECOND FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MICROSECOND FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MICROSECOND FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MICROSECOND FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MICROSECOND FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MICROSECOND FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MICROSECOND FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MICROSECOND FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MICROSECOND FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MICROSECOND FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MICROSECOND FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MICROSECOND FROM TIMESTAMP'2001-12-13 10:20:30.999999'); +EXECUTE IMMEDIATE 'CREATE TABLE t1f_ps AS SELECT ?, CAST(? AS UNSIGNED), CAST(? AS SIGNED), ABS(?), ROUND(?), -?, ROUND(?,-1), ?+0, ?+0.0, CONCAT(?), LEAST(?,?), COALESCE(?), COALESCE(?,CAST(1 AS SIGNED)), COALESCE(?,CAST(1 AS UNSIGNED)), @a:=?' USING MICROSECOND(TIMESTAMP'2001-12-13 10:20:30.999999'), MICROSECOND(TIMESTAMP'2001-12-13 10:20:30.999999'), MICROSECOND(TIMESTAMP'2001-12-13 10:20:30.999999'), MICROSECOND(TIMESTAMP'2001-12-13 10:20:30.999999'), MICROSECOND(TIMESTAMP'2001-12-13 10:20:30.999999'), MICROSECOND(TIMESTAMP'2001-12-13 10:20:30.999999'), MICROSECOND(TIMESTAMP'2001-12-13 10:20:30.999999'), MICROSECOND(TIMESTAMP'2001-12-13 10:20:30.999999'), MICROSECOND(TIMESTAMP'2001-12-13 10:20:30.999999'), MICROSECOND(TIMESTAMP'2001-12-13 10:20:30.999999'), MICROSECOND(TIMESTAMP'2001-12-13 10:20:30.999999'), MICROSECOND(TIMESTAMP'2001-12-13 10:20:30.999999'), MICROSECOND(TIMESTAMP'2001-12-13 10:20:30.999999'), MICROSECOND(TIMESTAMP'2001-12-13 10:20:30.999999'), MICROSECOND(TIMESTAMP'2001-12-13 10:20:30.999999'), MICROSECOND(TIMESTAMP'2001-12-13 10:20:30.999999'); +EXECUTE IMMEDIATE 'CREATE TABLE t2e_ps AS SELECT AVG(?), MIN(?), MAX(?), GROUP_CONCAT(?)' USING EXTRACT(MICROSECOND FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MICROSECOND FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MICROSECOND FROM TIMESTAMP'2001-12-13 10:20:30.999999'), EXTRACT(MICROSECOND FROM TIMESTAMP'2001-12-13 10:20:30.999999'); +EXECUTE IMMEDIATE 'CREATE TABLE t2f_ps AS SELECT AVG(?), MIN(?), MAX(?), GROUP_CONCAT(?)' USING MICROSECOND(TIMESTAMP'2001-12-13 10:20:30.999999'), MICROSECOND(TIMESTAMP'2001-12-13 10:20:30.999999'), MICROSECOND(TIMESTAMP'2001-12-13 10:20:30.999999'), MICROSECOND(TIMESTAMP'2001-12-13 10:20:30.999999'); +CALL show_drop; +TABLE_NAME COLUMN_TYPE COLUMN_NAME +t1e_nm int(7) EXTRACT(MICROSECOND FROM a) +t1e_ps int(7) ? +t1f_nm int(7) MICROSECOND(a) +t1f_ps int(7) ? +t1e_nm bigint(20) unsigned CAST(EXTRACT(MICROSECOND FROM a) AS UNSIGNED) +t1e_ps bigint(20) unsigned CAST(? AS UNSIGNED) +t1f_nm int(6) unsigned CAST(MICROSECOND(a) AS UNSIGNED) +t1f_ps int(6) unsigned CAST(? AS UNSIGNED) +t1e_nm int(7) CAST(EXTRACT(MICROSECOND FROM a) AS SIGNED) +t1e_ps int(7) CAST(? AS SIGNED) +t1f_nm int(7) CAST(MICROSECOND(a) AS SIGNED) +t1f_ps int(7) CAST(? AS SIGNED) +t1e_nm int(7) ABS(EXTRACT(MICROSECOND FROM a)) +t1e_ps int(7) ABS(?) +t1f_nm int(7) ABS(MICROSECOND(a)) +t1f_ps int(7) ABS(?) +t1e_nm int(7) ROUND(EXTRACT(MICROSECOND FROM a)) +t1e_ps int(7) ROUND(?) +t1f_nm int(7) ROUND(MICROSECOND(a)) +t1f_ps int(7) ROUND(?) +t1e_nm int(8) -EXTRACT(MICROSECOND FROM a) +t1e_ps int(8) -? +t1f_nm int(7) -MICROSECOND(a) +t1f_ps int(7) -? +t1e_nm int(8) ROUND(EXTRACT(MICROSECOND FROM a),-1) +t1e_ps int(8) ROUND(?,-1) +t1f_nm int(8) ROUND(MICROSECOND(a),-1) +t1f_ps int(8) ROUND(?,-1) +t1e_nm int(8) EXTRACT(MICROSECOND FROM a)+0 +t1e_ps int(8) ?+0 +t1f_nm int(8) MICROSECOND(a)+0 +t1f_ps int(8) ?+0 +t1e_nm decimal(8,1) EXTRACT(MICROSECOND FROM a)+0.0 +t1e_ps decimal(8,1) ?+0.0 +t1f_nm decimal(8,1) MICROSECOND(a)+0.0 +t1f_ps decimal(8,1) ?+0.0 +t1e_nm varchar(7) CONCAT(EXTRACT(MICROSECOND FROM a)) +t1e_ps varchar(7) CONCAT(?) +t1f_nm varchar(6) CONCAT(MICROSECOND(a)) +t1f_ps varchar(6) CONCAT(?) +t1e_nm int(7) LEAST(EXTRACT(MICROSECOND FROM a),EXTRACT(MICROSECOND FROM a)) +t1e_ps int(7) LEAST(?,?) +t1f_nm int(7) LEAST(MICROSECOND(a),MICROSECOND(a)) +t1f_ps int(7) LEAST(?,?) +t1e_nm int(7) COALESCE(EXTRACT(MICROSECOND FROM a)) +t1e_ps int(7) COALESCE(?) +t1f_nm int(7) COALESCE(MICROSECOND(a)) +t1f_ps int(7) COALESCE(?) +t1e_nm int(7) COALESCE(EXTRACT(MICROSECOND FROM a),CAST(1 AS SIGNED)) +t1e_ps int(7) COALESCE(?,CAST(1 AS SIGNED)) +t1f_nm int(7) COALESCE(MICROSECOND(a),CAST(1 AS SIGNED)) +t1f_ps int(7) COALESCE(?,CAST(1 AS SIGNED)) +t1e_nm decimal(6,0) COALESCE(EXTRACT(MICROSECOND FROM a),CAST(1 AS UNSIGNED)) +t1e_ps decimal(6,0) COALESCE(?,CAST(1 AS UNSIGNED)) +t1f_nm decimal(6,0) COALESCE(MICROSECOND(a),CAST(1 AS UNSIGNED)) +t1f_ps decimal(6,0) COALESCE(?,CAST(1 AS UNSIGNED)) +t1e_nm int(7) @a:=EXTRACT(MICROSECOND FROM a) +t1e_ps int(7) @a:=? +t1f_nm int(7) @a:=MICROSECOND(a) +t1f_ps int(7) @a:=? +t2e_nm decimal(10,4) AVG(EXTRACT(MICROSECOND FROM a)) +t2e_ps decimal(10,4) AVG(?) +t2f_nm decimal(10,4) AVG(MICROSECOND(a)) +t2f_ps decimal(10,4) AVG(?) +t2e_nm bigint(7) MIN(EXTRACT(MICROSECOND FROM a)) +t2e_ps bigint(7) MIN(?) +t2f_nm bigint(7) MIN(MICROSECOND(a)) +t2f_ps bigint(7) MIN(?) +t2e_nm bigint(7) MAX(EXTRACT(MICROSECOND FROM a)) +t2e_ps bigint(7) MAX(?) +t2f_nm bigint(7) MAX(MICROSECOND(a)) +t2f_ps bigint(7) MAX(?) +t2e_nm mediumtext GROUP_CONCAT(EXTRACT(MICROSECOND FROM a)) +t2e_ps mediumtext GROUP_CONCAT(?) +t2f_nm mediumtext GROUP_CONCAT(MICROSECOND(a)) +t2f_ps mediumtext GROUP_CONCAT(?) + + +# DAYOFYEAR +CALL p1('DAYOFYEAR'); +EXECUTE IMMEDIATE 'CREATE TABLE t1f_ps AS SELECT ?, CAST(? AS UNSIGNED), CAST(? AS SIGNED), ABS(?), ROUND(?), -?, ROUND(?,-1), ?+0, ?+0.0, CONCAT(?), LEAST(?,?), COALESCE(?), COALESCE(?,CAST(1 AS SIGNED)), COALESCE(?,CAST(1 AS UNSIGNED)), @a:=?' USING DAYOFYEAR(TIMESTAMP'2001-12-13 10:20:30.999999'), DAYOFYEAR(TIMESTAMP'2001-12-13 10:20:30.999999'), DAYOFYEAR(TIMESTAMP'2001-12-13 10:20:30.999999'), DAYOFYEAR(TIMESTAMP'2001-12-13 10:20:30.999999'), DAYOFYEAR(TIMESTAMP'2001-12-13 10:20:30.999999'), DAYOFYEAR(TIMESTAMP'2001-12-13 10:20:30.999999'), DAYOFYEAR(TIMESTAMP'2001-12-13 10:20:30.999999'), DAYOFYEAR(TIMESTAMP'2001-12-13 10:20:30.999999'), DAYOFYEAR(TIMESTAMP'2001-12-13 10:20:30.999999'), DAYOFYEAR(TIMESTAMP'2001-12-13 10:20:30.999999'), DAYOFYEAR(TIMESTAMP'2001-12-13 10:20:30.999999'), DAYOFYEAR(TIMESTAMP'2001-12-13 10:20:30.999999'), DAYOFYEAR(TIMESTAMP'2001-12-13 10:20:30.999999'), DAYOFYEAR(TIMESTAMP'2001-12-13 10:20:30.999999'), DAYOFYEAR(TIMESTAMP'2001-12-13 10:20:30.999999'), DAYOFYEAR(TIMESTAMP'2001-12-13 10:20:30.999999'); +EXECUTE IMMEDIATE 'CREATE TABLE t2f_ps AS SELECT AVG(?), MIN(?), MAX(?), GROUP_CONCAT(?)' USING DAYOFYEAR(TIMESTAMP'2001-12-13 10:20:30.999999'), DAYOFYEAR(TIMESTAMP'2001-12-13 10:20:30.999999'), DAYOFYEAR(TIMESTAMP'2001-12-13 10:20:30.999999'), DAYOFYEAR(TIMESTAMP'2001-12-13 10:20:30.999999'); +CALL show_drop; +TABLE_NAME COLUMN_TYPE COLUMN_NAME +t1f_nm int(4) DAYOFYEAR(a) +t1f_ps int(4) ? +t1f_nm int(3) unsigned CAST(DAYOFYEAR(a) AS UNSIGNED) +t1f_ps int(3) unsigned CAST(? AS UNSIGNED) +t1f_nm int(4) CAST(DAYOFYEAR(a) AS SIGNED) +t1f_ps int(4) CAST(? AS SIGNED) +t1f_nm int(4) ABS(DAYOFYEAR(a)) +t1f_ps int(4) ABS(?) +t1f_nm int(4) ROUND(DAYOFYEAR(a)) +t1f_ps int(4) ROUND(?) +t1f_nm int(4) -DAYOFYEAR(a) +t1f_ps int(4) -? +t1f_nm int(5) ROUND(DAYOFYEAR(a),-1) +t1f_ps int(5) ROUND(?,-1) +t1f_nm int(5) DAYOFYEAR(a)+0 +t1f_ps int(5) ?+0 +t1f_nm decimal(5,1) DAYOFYEAR(a)+0.0 +t1f_ps decimal(5,1) ?+0.0 +t1f_nm varchar(3) CONCAT(DAYOFYEAR(a)) +t1f_ps varchar(3) CONCAT(?) +t1f_nm int(4) LEAST(DAYOFYEAR(a),DAYOFYEAR(a)) +t1f_ps int(4) LEAST(?,?) +t1f_nm int(4) COALESCE(DAYOFYEAR(a)) +t1f_ps int(4) COALESCE(?) +t1f_nm int(4) COALESCE(DAYOFYEAR(a),CAST(1 AS SIGNED)) +t1f_ps int(4) COALESCE(?,CAST(1 AS SIGNED)) +t1f_nm decimal(3,0) COALESCE(DAYOFYEAR(a),CAST(1 AS UNSIGNED)) +t1f_ps decimal(3,0) COALESCE(?,CAST(1 AS UNSIGNED)) +t1f_nm int(4) @a:=DAYOFYEAR(a) +t1f_ps int(4) @a:=? +t2f_nm decimal(7,4) AVG(DAYOFYEAR(a)) +t2f_ps decimal(7,4) AVG(?) +t2f_nm bigint(4) MIN(DAYOFYEAR(a)) +t2f_ps bigint(4) MIN(?) +t2f_nm bigint(4) MAX(DAYOFYEAR(a)) +t2f_ps bigint(4) MAX(?) +t2f_nm mediumtext GROUP_CONCAT(DAYOFYEAR(a)) +t2f_ps mediumtext GROUP_CONCAT(?) +DROP TABLE t1; +DROP PROCEDURE p1; +DROP PROCEDURE show_drop; +DROP FUNCTION params; +DROP FUNCTION select01; +DROP FUNCTION select02; +# +# End of 10.5 tests +# |