341
341
create table t1 (f1 date, f2 datetime, f3 varchar(20));
342
342
create table t2 as select coalesce(f1,f1) as f4 from t1;
344
Field Type Null Key Default Extra
344
Field Type Null Default Default_is_NULL On_Update
346
346
create table t3 as select coalesce(f1,f2) as f4 from t1;
348
Field Type Null Key Default Extra
348
Field Type Null Default Default_is_NULL On_Update
349
f4 DATETIME TRUE TRUE
350
350
create table t4 as select coalesce(f2,f2) as f4 from t1;
352
Field Type Null Key Default Extra
352
Field Type Null Default Default_is_NULL On_Update
353
f4 DATETIME TRUE TRUE
354
354
create table t5 as select coalesce(f1,f3) as f4 from t1;
356
Field Type Null Key Default Extra
357
f4 varbinary(80) YES NULL
356
Field Type Null Default Default_is_NULL On_Update
358
358
create table t6 as select coalesce(f2,f3) as f4 from t1;
360
Field Type Null Key Default Extra
361
f4 varbinary(80) YES NULL
360
Field Type Null Default Default_is_NULL On_Update
362
362
create table t7 as select coalesce(makedate(1997,1),f2) as f4 from t1;
364
Field Type Null Key Default Extra
364
Field Type Null Default Default_is_NULL On_Update
365
f4 DATETIME TRUE TRUE
366
366
create table t8 as select coalesce(cast('01-01-01' as datetime),f2) as f4
369
Field Type Null Key Default Extra
369
Field Type Null Default Default_is_NULL On_Update
370
f4 DATETIME TRUE TRUE
371
371
create table t9 as select case when 1 then cast('01-01-01' as date)
372
372
when 0 then cast('01-01-01' as date) end as f4 from t1;
374
Field Type Null Key Default Extra
374
Field Type Null Default Default_is_NULL On_Update
376
376
create table t10 as select case when 1 then cast('01-01-01' as datetime)
377
377
when 0 then cast('01-01-01' as datetime) end as f4 from t1;
379
Field Type Null Key Default Extra
379
Field Type Null Default Default_is_NULL On_Update
380
f4 DATETIME TRUE TRUE
381
381
create table t11 as select if(1, cast('01-01-01' as datetime),
382
382
cast('01-01-01' as date)) as f4 from t1;
384
Field Type Null Key Default Extra
384
Field Type Null Default Default_is_NULL On_Update
386
386
create table t12 as select least(cast('01-01-01' as datetime),
387
387
cast('01-01-01' as date)) as f4 from t1;
389
Field Type Null Key Default Extra
389
Field Type Null Default Default_is_NULL On_Update
391
391
create table t13 as select ifnull(cast('01-01-01' as datetime),
392
392
cast('01-01-01' as date)) as f4 from t1;
394
Field Type Null Key Default Extra
394
Field Type Null Default Default_is_NULL On_Update
396
396
drop tables t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13;
397
397
###################################################################
398
398
create table t1 (a int);