~drizzle-trunk/drizzle/development

#

# Tests for the MICROSECOND() function

#

# The Drizzle MICROSECOND() function differs from the MySQL MICROSECOND()

# function in these ways:

#

#  * Does not accept invalid parameters.  This results in an error

#    in Drizzle.

#

# MICROSECOND() on a NULL should produce

# a NULL.

SELECT MICROSECOND(NULL);

# 

# Test improper argument list 

#

# 1 arg is required.

--error ER_PARSE_ERROR 

# Wrong parameter count...but unfortunately produces 1064 Syntax Error due to limitations of 

# the SQL parser, which considers MICROSECOND a keyword before being a function symbol

SELECT MICROSECOND();

--error ER_PARSE_ERROR

# Wrong parameter count...but unfortunately produces 1064 Syntax Error due to limitations of 

# the SQL parser, which considers MICROSECOND a keyword before being a function symbol

SELECT MICROSECOND(1, 0);

# 

# Test invalid dates passed to MICROSECOND

# produce an error, not a NULL or anything

# else...

#

--error ER_INVALID_DATETIME_VALUE

SELECT MICROSECOND("xxx");

# 

# Indy, bad dates!

#

# The following are all bad dates, with no possibility of interpreting

# the values as TIME-only components.

#

--error ER_INVALID_DATETIME_VALUE

SELECT MICROSECOND("0000-00-00"); # No 0000-00-00 dates!...

--error ER_INVALID_DATETIME_VALUE

SELECT MICROSECOND("0000-01-01"); # No zero year parts

--error ER_INVALID_DATETIME_VALUE

SELECT MICROSECOND("0001-00-01"); # No zero month parts

--error ER_INVALID_DATETIME_VALUE

SELECT MICROSECOND("0001-01-00"); # No zero day parts

--error ER_INVALID_DATETIME_VALUE

SELECT MICROSECOND("2000-02-30"); # No Feb 30th!

--error ER_INVALID_DATETIME_VALUE

SELECT MICROSECOND("1900-02-29"); # Not a leap MICROSECOND since not divisible evenly by 400...

--error ER_INVALID_DATETIME_VALUE

SELECT MICROSECOND('1976-15-15'); # No 15th month!

--error ER_INVALID_DATETIME_VALUE

SELECT MICROSECOND('23:59:70'); # No 70th second!

--error ER_INVALID_DATETIME_VALUE

SELECT MICROSECOND('23:70:59'); # No 70th minute!

--error ER_INVALID_DATETIME_VALUE

SELECT MICROSECOND('26:00:00'); # No 26th hour!

--error ER_INVALID_DATETIME_VALUE

SELECT MICROSECOND('26:00:00.9999999'); # Microseconds are 6 places, not 7

# A good date, which cannot be interpreted as a TIME component.  Should return 0.

SELECT MICROSECOND("2009-01-12");

# A good date, which should output 0 since no TIME component - Test of 2 digit year conversion...

SELECT MICROSECOND("70-12-31");

# A good date in the common USA format, should output 0 since no TIME component

SELECT MICROSECOND('07/31/2009');

# A good datetime, should output 0 since no sub-second component

SELECT MICROSECOND("2008-11-30 03:30:15");

# A good datetime, should output 0 since no sub-second component

SELECT MICROSECOND("2008-11-30T03:30:15");

# A good datetime, should output 123456

SELECT MICROSECOND("2008-11-30T03:30:15.123456");

# A good datetime, should output 0 since no sub-second component

SELECT MICROSECOND("20081130033015");

# A good datetime, should output 123456

SELECT MICROSECOND("20081130033015.123456");

# A good datetime, should output 123450

SELECT MICROSECOND("20081130033015.12345");

# A good datetime, should output 123400

SELECT MICROSECOND("20081130033015.1234");

# A good datetime, should output 123000

SELECT MICROSECOND("20081130033015.123");

# A good datetime, should output 120000

SELECT MICROSECOND("20081130033015.12");

# A good datetime, should output 100000

SELECT MICROSECOND("20081130033015.1");

# A good datetime, interpreted int->string, should output 0 since no sub-second component

SELECT MICROSECOND(20081130033015);

# A good datetime, interpreted int->string, should output 123456

SELECT MICROSECOND(20081130033015.123456);

# A good datetime, interpreted int->string, should output 123450

SELECT MICROSECOND(20081130033015.12345);

# A good datetime, interpreted int->string, should output 123400

SELECT MICROSECOND(20081130033015.1234);

# A good datetime, interpreted int->string, should output 123000

SELECT MICROSECOND(20081130033015.123);

# A good datetime, interpreted int->string, should output 120000

SELECT MICROSECOND(20081130033015.12);

# A good datetime, interpreted int->string, should output 100000

SELECT MICROSECOND(20081130033015.1);

# A good time (according to MySQL, not Jay Pipes...), should output 0 since no sub-second component

SELECT MICROSECOND(231115);

# A good time (according to MySQL, not Jay Pipes...), should output 0 since no sub-second component

SELECT MICROSECOND("231115");

# A good time, should output 0 since no sub-second component

SELECT MICROSECOND("23:59:59");

# A good time, should output 123456

SELECT MICROSECOND("23:59:59.123456");

# Now test field values of different types

USE test;

--disable_warnings

DROP TABLE IF EXISTS t1;

--enable_warnings

CREATE TABLE t1 (

  int_fld INT NOT NULL

, date_fld DATE NOT NULL

, datetime_fld DATETIME NOT NULL

, timestamp_fld TIMESTAMP NOT NULL

, char_fld CHAR(22) NOT NULL

, varchar_fld VARCHAR(22) NOT NULL

, text_fld TEXT NOT NULL

, blob_fld BLOB NOT NULL

);

# Should all output 0 since no current way to store microsecond :(

INSERT INTO t1 VALUES (

20071130

, "2007-11-30"

, "2007-11-30 16:30:19"

, "2007-11-30T16:30:19"

, "2007-11-30 16:30:19"

, "2007-11-30 16:30:19"

, "2007-11-30T16:30:19"

, "2007-11-30T16:30:19"

);

SELECT MICROSECOND(int_fld) FROM t1;

SELECT MICROSECOND(date_fld) FROM t1;

SELECT MICROSECOND(datetime_fld) FROM t1;

SELECT MICROSECOND(timestamp_fld) FROM t1;

SELECT MICROSECOND(char_fld) FROM t1;

SELECT MICROSECOND(varchar_fld) FROM t1;

SELECT MICROSECOND(text_fld) FROM t1;

SELECT MICROSECOND(blob_fld) FROM t1;

DROP TABLE t1;