1790
1788
# Test of bug with SUM(CASE...)
1793
CREATE TEMPORARY TABLE t1 (gvid int default NULL, hmid int default NULL, volid int default NULL, mmid int default NULL, hdid int default NULL, fsid int default NULL, ctid int default NULL, dtid int default NULL, cost int default NULL, performance int default NULL, serialnumber bigint default NULL, monitored int default '1', removed int default '0', target int default '0', dt_modified timestamp NOT NULL, name varchar(255) default NULL, description varchar(255) default NULL, UNIQUE KEY hmid (hmid,volid)) ENGINE=MyISAM;
1791
CREATE TABLE t1 (gvid int default NULL, hmid int default NULL, volid int default NULL, mmid int default NULL, hdid int default NULL, fsid int default NULL, ctid int default NULL, dtid int default NULL, cost int default NULL, performance int default NULL, serialnumber bigint default NULL, monitored int default '1', removed int default '0', target int default '0', dt_modified timestamp NOT NULL, name varchar(255) binary default NULL, description varchar(255) default NULL, UNIQUE KEY hmid (hmid,volid)) ENGINE=MyISAM;
1794
1792
INSERT INTO t1 VALUES (200001,2,1,1,100,1,1,1,0,0,0,1,0,1,20020425060057,'\\\\ARKIVIO-TESTPDC\\E$',''),(200002,2,2,1,101,1,1,1,0,0,0,1,0,1,20020425060057,'\\\\ARKIVIO-TESTPDC\\C$',''),(200003,1,3,2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,0,1,20020425060427,'c:',NULL);
1795
CREATE TEMPORARY TABLE t2 ( hmid int default NULL, volid int default NULL, sampletid int default NULL, sampletime datetime default NULL, samplevalue bigint default NULL, KEY idx1 (hmid,volid,sampletid,sampletime)) ENGINE=MyISAM;
1793
CREATE TABLE t2 ( hmid int default NULL, volid int default NULL, sampletid int default NULL, sampletime datetime default NULL, samplevalue bigint default NULL, KEY idx1 (hmid,volid,sampletid,sampletime)) ENGINE=MyISAM;
1796
1794
INSERT INTO t2 VALUES (1,3,10,'2002-06-01 08:00:00',35),(1,3,1010,'2002-06-01 12:00:01',35);
1797
--error 1686 # bad datetime
1795
# Disable PS becasue we get more warnings from PS than from normal execution
1796
--disable_ps_protocol
1798
1797
SELECT a.gvid, (SUM(CASE b.sampletid WHEN 140 THEN b.samplevalue ELSE 0 END)) as the_success,(SUM(CASE b.sampletid WHEN 141 THEN b.samplevalue ELSE 0 END)) as the_fail,(SUM(CASE b.sampletid WHEN 142 THEN b.samplevalue ELSE 0 END)) as the_size,(SUM(CASE b.sampletid WHEN 143 THEN b.samplevalue ELSE 0 END)) as the_time FROM t1 a, t2 b WHERE a.hmid = b.hmid AND a.volid = b.volid AND b.sampletime >= 'wrong-date-value' AND b.sampletime < 'wrong-date-value' AND b.sampletid IN (140, 141, 142, 143) GROUP BY a.gvid;
1798
--enable_ps_protocol
1799
1799
# Testing the same select with NULL's instead of invalid datetime values
1800
1800
SELECT a.gvid, (SUM(CASE b.sampletid WHEN 140 THEN b.samplevalue ELSE 0 END)) as the_success,(SUM(CASE b.sampletid WHEN 141 THEN b.samplevalue ELSE 0 END)) as the_fail,(SUM(CASE b.sampletid WHEN 142 THEN b.samplevalue ELSE 0 END)) as the_size,(SUM(CASE b.sampletid WHEN 143 THEN b.samplevalue ELSE 0 END)) as the_time FROM t1 a, t2 b WHERE a.hmid = b.hmid AND a.volid = b.volid AND b.sampletime >= NULL AND b.sampletime < NULL AND b.sampletid IN (140, 141, 142, 143) GROUP BY a.gvid;
1801
1801
DROP TABLE t1,t2;
1863
1863
select * from (t1 as t2 left join t1 as t3 using (a)) natural join t1;
1866
CREATE TEMPORARY TABLE t1 ( aa char(2), id int NOT NULL auto_increment, t2_id int NOT NULL default '0', PRIMARY KEY (id), KEY replace_id (t2_id)) ENGINE=MyISAM;
1866
CREATE TABLE t1 ( aa char(2), id int NOT NULL auto_increment, t2_id int NOT NULL default '0', PRIMARY KEY (id), KEY replace_id (t2_id)) ENGINE=MyISAM;
1867
1867
INSERT INTO t1 VALUES ("1",8264,2506),("2",8299,2517),("3",8301,2518),("4",8302,2519),("5",8303,2520),("6",8304,2521),("7",8305,2522);
1868
CREATE TEMPORARY TABLE t2 ( id int NOT NULL auto_increment, PRIMARY KEY (id)) ENGINE=MyISAM;
1868
CREATE TABLE t2 ( id int NOT NULL auto_increment, PRIMARY KEY (id)) ENGINE=MyISAM;
1869
1869
INSERT INTO t2 VALUES (2517), (2518), (2519), (2520), (2521), (2522);
1870
1870
select * from t1, t2 WHERE t1.t2_id = t2.id and t1.t2_id > 0 order by t1.id LIMIT 0, 5;
1871
1871
drop table t1,t2;
3301
3296
# Bug#31800: Date comparison fails with timezone and slashes for greater
3302
3297
# than comparison
3305
# @TODO Commenting all str_to_date out for now...should move to a plugin.
3307
3300
# On DATETIME-like literals with trailing garbage, BETWEEN fudged in a
3308
3301
# DATETIME comparator, while greater/less-than used bin-string comparisons.
3309
3302
# Should correctly be compared as DATE or DATETIME, but throw a warning:
3311
#select str_to_date('2007-10-09','%Y-%m-%d') between '2007/10/01 00:00:00 GMT'
3312
# and '2007/10/20 00:00:00 GMT';
3313
#select str_to_date('2007-10-09','%Y-%m-%d') > '2007/10/01 00:00:00 GMT-6';
3314
#select str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/2000:00:00 GMT-6';
3304
select str_to_date('2007-10-09','%Y-%m-%d') between '2007/10/01 00:00:00 GMT'
3305
and '2007/10/20 00:00:00 GMT';
3306
select str_to_date('2007-10-09','%Y-%m-%d') > '2007/10/01 00:00:00 GMT-6';
3307
select str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/2000:00:00 GMT-6';
3316
3309
# We have all we need -- and trailing garbage:
3317
3310
# (leaving out a leading zero in first example to prove it's a
3318
3311
# value-comparison, not a string-comparison!)
3319
#select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-1 00:00:00 GMT-6';
3320
#select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 x00:00:00 GMT-6';
3321
#select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:00:00 GMT-6';
3322
#select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:x00:00 GMT-6';
3312
select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-1 00:00:00 GMT-6';
3313
select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 x00:00:00 GMT-6';
3314
select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:00:00 GMT-6';
3315
select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:x00:00 GMT-6';
3323
3316
# no time at all:
3324
#select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 x12:34:56 GMT-6';
3317
select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 x12:34:56 GMT-6';
3325
3318
# partial time:
3326
#select str_to_date('2007-10-01 12:34:00','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6';
3319
select str_to_date('2007-10-01 12:34:00','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6';
3327
3320
# fail, different second part:
3328
#select str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6';
3321
select str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6';
3329
3322
# correct syntax, no trailing nonsense -- this one must throw no warning:
3330
#select str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34:56';
3323
select str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34:56';
3331
3324
# no warning, but failure (different hour parts):
3332
#select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 12:00:00';
3325
select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 12:00:00';
3334
#select str_to_date('2007-10-01 12','%Y-%m-%d %H') = '2007-10-01 12:00:00';
3327
select str_to_date('2007-10-01 12','%Y-%m-%d %H') = '2007-10-01 12:00:00';
3335
3328
# succeed, but warn for "trailing garbage" (":34"):
3336
#select str_to_date('2007-10-01 12:34','%Y-%m-%d %H') = '2007-10-01 12:00:00';
3329
select str_to_date('2007-10-01 12:34','%Y-%m-%d %H') = '2007-10-01 12:00:00';
3337
3330
# invalid date (Feb 30) succeeds
3338
#select str_to_date('2007-02-30 12:34','%Y-%m-%d %H:%i') = '2007-02-30 12:34';
3331
select str_to_date('2007-02-30 12:34','%Y-%m-%d %H:%i') = '2007-02-30 12:34';
3339
3332
# 0-day for both, just works in default SQL mode.
3340
#select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34';
3333
select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34';
3341
3334
# 0-day, succeed
3342
#select str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01 00:00:00'
3343
# and '2007/10/20 00:00:00';
3344
#select str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20';
3345
#select str_to_date('','%Y-%m-%d') between '2007/10/01' and '2007/10/20';
3346
#select str_to_date('','%Y-%m-%d %H:%i') = '2007-10-01 12:34';
3347
#select str_to_date(NULL,'%Y-%m-%d %H:%i') = '2007-10-01 12:34';
3348
#select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '';
3335
select str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01 00:00:00'
3336
and '2007/10/20 00:00:00';
3337
select str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20';
3338
select str_to_date('','%Y-%m-%d') between '2007/10/01' and '2007/10/20';
3339
select str_to_date('','%Y-%m-%d %H:%i') = '2007-10-01 12:34';
3340
select str_to_date(NULL,'%Y-%m-%d %H:%i') = '2007-10-01 12:34';
3341
select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '';
3350
#select str_to_date('1','%Y-%m-%d') = '1';
3351
#select str_to_date('1','%Y-%m-%d') = '1';
3352
#select str_to_date('','%Y-%m-%d') = '';
3343
select str_to_date('1','%Y-%m-%d') = '1';
3344
select str_to_date('1','%Y-%m-%d') = '1';
3345
select str_to_date('','%Y-%m-%d') = '';
3354
3347
# these three should work!
3355
#select str_to_date('1000-01-01','%Y-%m-%d') between '0000-00-00' and NULL;
3356
#select str_to_date('1000-01-01','%Y-%m-%d') between NULL and '2000-00-00';
3357
#select str_to_date('1000-01-01','%Y-%m-%d') between NULL and NULL;
3348
select str_to_date('1000-01-01','%Y-%m-%d') between '0000-00-00' and NULL;
3349
select str_to_date('1000-01-01','%Y-%m-%d') between NULL and '2000-00-00';
3350
select str_to_date('1000-01-01','%Y-%m-%d') between NULL and NULL;
3358
3351
###########################################################################
3454
3447
SELECT a FROM t1 ORDER BY a LIMIT 2,4294967296;
3455
3448
SELECT a FROM t1 ORDER BY a LIMIT 2,4294967297;
3459
# MySQL Bug#33546: Slowdown on re-evaluation of constant expressions.
3461
CREATE TABLE t1 (a INT);
3462
INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
3463
CREATE TABLE t2 (b INT);
3464
INSERT INTO t2 VALUES (2);
3465
SELECT * FROM t1 WHERE a = 1 + 1;
3466
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a = 1 + 1;
3467
SELECT * FROM t1 HAVING a = 1 + 1;
3468
EXPLAIN EXTENDED SELECT * FROM t1 HAVING a = 1 + 1;
3469
SELECT * FROM t1, t2 WHERE a = b + (1 + 1);
3470
EXPLAIN EXTENDED SELECT * FROM t1, t2 WHERE a = b + (1 + 1);
3471
SELECT * FROM t2 LEFT JOIN t1 ON a = b + 1;
3472
EXPLAIN EXTENDED SELECT * FROM t2 LEFT JOIN t1 ON a = b + 1;
3473
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a > UNIX_TIMESTAMP('2009-03-10 00:00:00');