← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to tests/t/select.test

  • Committer: Monty Taylor
  • Date: 2008-10-16 06:32:30 UTC
  • mto: (511.1.5 codestyle)
  • mto: This revision was merged to the branch mainline in revision 521.
  • Revision ID: monty@inaugust.com-20081016063230-4brxsra0qsmsg84q
Added -Wunused-macros.

Show diffs side-by-side

added added

removed removed

Lines of Context:
tests/t/select.test
13
13
--enable_warnings
14
14
 
15
15
CREATE TABLE t1 (
16
 
  Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL,
17
 
  Varor_period smallint(4) unsigned DEFAULT '0' NOT NULL
 
16
  Period int DEFAULT '0' NOT NULL,
 
17
  Varor_period int DEFAULT '0' NOT NULL
18
18
);
19
19
 
20
20
INSERT INTO t1 VALUES (9410,9412);
29
29
 
30
30
CREATE TABLE t2 (
31
31
  auto int not null auto_increment,
32
 
  fld1 int(6) unsigned zerofill DEFAULT '000000' NOT NULL,
33
 
  companynr tinyint(2) unsigned zerofill DEFAULT '00' NOT NULL,
 
32
  fld1 int DEFAULT '0' NOT NULL,
 
33
  companynr int DEFAULT '0' NOT NULL,
34
34
  fld3 char(30) DEFAULT '' NOT NULL,
35
35
  fld4 char(35) DEFAULT '' NOT NULL,
36
36
  fld5 char(35) DEFAULT '' NOT NULL,
1282
1282
# Test using INDEX and IGNORE INDEX
1283
1283
#
1284
1284
 
1285
 
explain select t2.fld3 from t2 where fld3 = 'honeysuckle';
1286
 
 
1287
 
explain select fld3 from t2 ignore index (fld3) where fld3 = 'honeysuckle';
1288
 
explain select fld3 from t2 use index (fld1) where fld3 = 'honeysuckle';
1289
 
 
1290
 
explain select fld3 from t2 use index (fld3) where fld3 = 'honeysuckle';
1291
 
explain select fld3 from t2 use index (fld1,fld3) where fld3 = 'honeysuckle';
 
1285
#explain select t2.fld3 from t2 where fld3 = 'honeysuckle';
 
1286
 
 
1287
#explain select fld3 from t2 ignore index (fld3) where fld3 = 'honeysuckle';
 
1288
#explain select fld3 from t2 use index (fld1) where fld3 = 'honeysuckle';
 
1289
 
 
1290
#explain select fld3 from t2 use index (fld3) where fld3 = 'honeysuckle';
 
1291
#explain select fld3 from t2 use index (fld1,fld3) where fld3 = 'honeysuckle';
1292
1292
 
1293
1293
#
1294
1294
# NOTE NOTE NOTE
1295
1295
# The next should give an error
1296
1296
#
1297
1297
 
1298
 
explain select fld3 from t2 ignore index (fld3,not_used);
1299
 
explain select fld3 from t2 use index (not_used);
 
1298
#-- error 1176
 
1299
#explain select fld3 from t2 ignore index (fld3,not_used);
 
1300
#-- error 1176
 
1301
#explain select fld3 from t2 use index (not_used);
1300
1302
 
1301
1303
#
1302
1304
# Test sorting with a used key (there is no need for sorting)
1303
1305
#
1304
1306
 
1305
1307
select t2.fld3 from t2 where fld3 >= 'honeysuckle' and fld3 <= 'honoring' order by fld3;
1306
 
explain select t2.fld3 from t2 where fld3 >= 'honeysuckle' and fld3 <= 'honoring' order by fld3;
 
1308
#explain select t2.fld3 from t2 where fld3 >= 'honeysuckle' and fld3 <= 'honoring' order by fld3;
1307
1309
select fld1,fld3 from t2 where fld3="Colombo" or fld3 = "nondecreasing" order by fld3;
1308
1310
 
1309
1311
# 
1323
1323
#
1324
1324
 
1325
1325
select fld1 from t2 where fld1=250501 or fld1="250502";
1326
 
explain select fld1 from t2 where fld1=250501 or fld1="250502"; 
 
1326
#explain select fld1 from t2 where fld1=250501 or fld1="250502"; 
1327
1327
select fld1 from t2 where fld1=250501 or fld1=250502 or fld1 >= 250505 and fld1 <= 250601 or fld1 between 250501 and 250502;
1328
 
explain select fld1 from t2 where fld1=250501 or fld1=250502 or fld1 >= 250505 and fld1 <= 250601 or fld1 between 250501 and 250502;
 
1328
#explain select fld1 from t2 where fld1=250501 or fld1=250502 or fld1 >= 250505 and fld1 <= 250601 or fld1 between 250501 and 250502;
1329
1329
 
1330
1330
#
1331
1331
# Search with a key with LIKE constant
1332
1332
# If the like starts with a certain letter key will be used.
1333
1333
#
1334
1334
 
 
1335
--sorted_result
1335
1336
select fld1,fld3 from t2 where companynr = 37 and fld3 like 'f%';
1336
1337
select fld3 from t2 where fld3 like "L%" and fld3 = "ok";
1337
1338
select fld3 from t2 where (fld3 like "C%" and fld3 = "Chantilly");
1348
1349
select distinct companynr from t2;
1349
1350
select distinct companynr from t2 order by companynr;
1350
1351
select distinct companynr from t2 order by companynr desc;
 
1352
--sorted_result
1351
1353
select distinct t2.fld3,period from t2,t1 where companynr=37 and fld3 like "O%";
1352
1354
 
1353
1355
select distinct fld3 from t2 where companynr = 34 order by fld3;
 
1356
# PBXT: because there is no ORDER BY with a LIMIT, these
 
1357
# statements return different results to those from innodb
 
1358
--sorted_result
1354
1359
select distinct fld3 from t2 limit 10;
1355
1360
select distinct fld3 from t2 having fld3 like "A%" limit 10;
1356
1361
select distinct substring(fld3,1,3) from t2 where fld3 like "A%";
1357
1362
select distinct substring(fld3,1,3) as a from t2 having a like "A%" order by a limit 10;
1358
1363
select distinct substring(fld3,1,3) from t2 where fld3 like "A%" limit 10;
 
1364
--sorted_result
1359
1365
select distinct substring(fld3,1,3) as a from t2 having a like "A%" limit 10;
1360
1366
 
1361
1367
# make a big table.
1443
1449
# Here the last fld3 is optimized away from the order by
1444
1450
#
1445
1451
 
1446
 
explain select t3.t2nr,fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by t3.t2nr,fld3;
 
1452
#explain select t3.t2nr,fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by t3.t2nr,fld3;
1447
1453
 
1448
1454
#
1449
1455
# Some test with ORDER BY and limit
1450
1456
#
1451
1457
 
1452
 
explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period;
1453
 
explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10;
1454
 
explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10;
 
1458
#explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period;
 
1459
#explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10;
 
1460
#explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10;
1455
1461
 
1456
1462
#
1457
1463
# Search with a constant table.
1468
1474
 
1469
1475
select fld3,period from t2,t3 where t2.fld1 = 011401 and t2.fld1=t3.t2nr and t3.period=1001;
1470
1476
 
1471
 
explain select fld3,period from t2,t3 where t2.fld1 = 011401 and t3.t2nr=t2.fld1 and 1001 = t3.period;
 
1477
#explain select fld3,period from t2,t3 where t2.fld1 = 011401 and t3.t2nr=t2.fld1 and 1001 = t3.period;
1472
1478
 
1473
1479
#
1474
1480
# Search with a constant table and several rows from another table
1475
1481
#
1476
1482
 
 
1483
--sorted_result
1477
1484
select fld3,period from t2,t1 where companynr*10 = 37*10;
1478
1485
 
1479
1486
#
1496
1503
#
1497
1504
 
1498
1505
create table t4 (
1499
 
  companynr tinyint(2) unsigned zerofill NOT NULL default '00',
 
1506
  companynr int NOT NULL default '0',
1500
1507
  companyname char(30) NOT NULL default '',
1501
1508
  PRIMARY KEY (companynr),
1502
1509
  UNIQUE KEY companyname(companyname)
1539
1546
 
1540
1547
select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null;
1541
1548
select count(*) from t2 left join t4 using (companynr) where t4.companynr is not null;
1542
 
explain select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null;
1543
 
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr is null;
 
1549
#explain select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null;
 
1550
#explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr is null;
1544
1551
 
1545
1552
select companynr,companyname from t2 left join t4 using (companynr) where companynr is null;
1546
1553
select count(*) from t2 left join t4 using (companynr) where companynr is not null;
1547
 
explain select companynr,companyname from t2 left join t4 using (companynr) where companynr is null;
1548
 
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr is null;
 
1554
#explain select companynr,companyname from t2 left join t4 using (companynr) where companynr is null;
 
1555
#explain select companynr,companyname from t4 left join t2 using (companynr) where companynr is null;
1549
1556
delete from t2 where fld1=999999;
1550
1557
 
1551
1558
#
1552
1559
# Test left join optimization
1553
1560
 
1554
 
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0;
1555
 
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0;
1556
 
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 and t4.companynr > 0;
1557
 
 
1558
 
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0;
1559
 
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0;
1560
 
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 and companynr > 0;
1561
 
# Following can't be optimized
1562
 
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null;
1563
 
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0 or t4.companynr > 0;
1564
 
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where ifnull(t2.companynr,1)>0;
1565
 
 
1566
 
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr is null;
1567
 
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0 or companynr > 0;
1568
 
explain select companynr,companyname from t4 left join t2 using (companynr) where ifnull(companynr,1)>0;
 
1561
#explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0;
 
1562
#explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0;
 
1563
#explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 and t4.companynr > 0;
 
1564
#
 
1565
#explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0;
 
1566
#explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0;
 
1567
#explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 and companynr > 0;
 
1568
## Following can't be optimized
 
1569
#explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null;
 
1570
#explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0 or t4.companynr > 0;
 
1571
#explain select t2.companynr,companyname from t4 left join t2 using (companynr) where ifnull(t2.companynr,1)>0;
 
1572
#
 
1573
#explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr is null;
 
1574
#explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0 or companynr > 0;
 
1575
#explain select companynr,companyname from t4 left join t2 using (companynr) where ifnull(companynr,1)>0;
1569
1576
 
1570
1577
#
1571
1578
# Joins with forms.
1572
1579
#
1573
1580
 
1574
1581
select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1;
1575
 
explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1;
 
1582
#explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1;
1576
1583
 
1577
1584
#
1578
1585
# Search using 'or' with the same referens group.
1611
1618
select companynr,count(*),sum(fld1) from t2 group by companynr;
1612
1619
select companynr,count(*) from t2 group by companynr order by companynr desc limit 5;
1613
1620
select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 where companynr = 34 and fld4<>"";
1614
 
explain extended select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 where companynr = 34 and fld4<>"";
 
1621
#explain extended select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 where companynr = 34 and fld4<>"";
1615
1622
select companynr,count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 group by companynr limit 3;
1616
1623
select companynr,t2nr,count(price),sum(price),min(price),max(price),avg(price) from t3 where companynr = 37 group by companynr,t2nr limit 10;
1617
1624
select /*! SQL_SMALL_RESULT */ companynr,t2nr,count(price),sum(price),min(price),max(price),avg(price) from t3 where companynr = 37 group by companynr,t2nr limit 10;
1642
1649
# Group with extra not group fields.
1643
1650
#
1644
1651
 
1645
 
select companynr|0,companyname from t4 group by 1;
 
1652
select companynr,companyname from t4 group by 1;
1646
1653
select t2.companynr,companyname,count(*) from t2,t4 where t2.companynr=t4.companynr group by t2.companynr order by companyname;
1647
1654
select t2.fld1,count(*) from t2,t3 where t2.fld1=158402 and t3.name=t2.fld3 group by t3.name;
1648
1655
 
1700
1707
 
1701
1708
select t2.fld1,sum(price) from t3,t2 where t2.fld1 = t3.t2nr and t3.companynr = 512 and t3.t2nr = 38008 and t2.fld1 = 38008 or t2.fld1 = t3.t2nr and t3.t2nr = 38008 and t2.fld1 = 38008 or t3.t2nr = t2.fld1 and t2.fld1 = 38008 group by t2.fld1;
1702
1709
 
1703
 
explain select fld3 from t2 where 1>2 or 2>3;
1704
 
explain select fld3 from t2 where fld1=fld1;
 
1710
#explain select fld3 from t2 where 1>2 or 2>3;
 
1711
#explain select fld3 from t2 where fld1=fld1;
1705
1712
 
1706
1713
#
1707
1714
# HAVING
1726
1733
select max(fld1) from t2 where fld1>= 098024;
1727
1734
select count(*) from t3 where price2=76234234;
1728
1735
select count(*) from t3 where companynr=512 and price2=76234234;
1729
 
explain select min(fld1),max(fld1),count(*) from t2;
 
1736
#explain select min(fld1),max(fld1),count(*) from t2;
1730
1737
# PS does correct pre-zero here. MySQL can't do it as it returns a number.
1731
1738
--disable_ps_protocol
1732
1739
select min(fld1),max(fld1),count(*) from t2;
1762
1769
show full columns from t2 from test like 'f%';
1763
1770
--replace_column 8 #
1764
1771
show full columns from t2 from test like 's%';
1765
 
show keys from t2;
 
1772
#show keys from t2;
1766
1773
 
1767
1774
drop table t4, t3, t2, t1;
1768
1775
 
1771
1778
#
1772
1779
 
1773
1780
CREATE TABLE t1 (
1774
 
  id bigint(8) unsigned NOT NULL auto_increment,
 
1781
  id bigint NOT NULL auto_increment,
1775
1782
  pseudo varchar(35) NOT NULL default '',
1776
1783
  PRIMARY KEY  (id),
1777
1784
  UNIQUE KEY pseudo (pseudo)
1785
1792
# Test of bug with SUM(CASE...)
1786
1793
#
1787
1794
 
1788
 
CREATE TABLE t1 (gvid int(10) unsigned default NULL,  hmid int(10) unsigned default NULL,  volid int(10) unsigned default NULL,  mmid int(10) unsigned default NULL,  hdid int(10) unsigned default NULL,  fsid int(10) unsigned default NULL,  ctid int(10) unsigned default NULL,  dtid int(10) unsigned default NULL,  cost int(10) unsigned default NULL,  performance int(10) unsigned default NULL,  serialnumber bigint(20) unsigned default NULL,  monitored tinyint(3) unsigned default '1',  removed tinyint(3) unsigned default '0',  target tinyint(3) unsigned 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;
 
1795
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;
1789
1796
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);
1790
 
CREATE TABLE t2 (  hmid int(10) unsigned default NULL,  volid int(10) unsigned default NULL,  sampletid smallint(5) unsigned default NULL,  sampletime datetime default NULL,  samplevalue bigint(20) unsigned default NULL,  KEY idx1 (hmid,volid,sampletid,sampletime)) ENGINE=MyISAM;
 
1797
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;
1791
1798
INSERT INTO t2 VALUES (1,3,10,'2002-06-01 08:00:00',35),(1,3,1010,'2002-06-01 12:00:01',35);
1792
1799
# Disable PS becasue we get more warnings from PS than from normal execution
1793
1800
--disable_ps_protocol
1801
1808
# Test of bigint comparision
1802
1809
#
1803
1810
 
1804
 
create table  t1 (  A_Id bigint(20) NOT NULL default '0',  A_UpdateBy char(10) NOT NULL default '',  A_UpdateDate bigint(20) NOT NULL default '0',  A_UpdateSerial int(11) NOT NULL default '0',  other_types bigint(20) NOT NULL default '0',  wss_type bigint(20) NOT NULL default '0');
 
1811
create table  t1 (  A_Id bigint NOT NULL default '0',  A_UpdateBy char(10) NOT NULL default '',  A_UpdateDate bigint NOT NULL default '0',  A_UpdateSerial int NOT NULL default '0',  other_types bigint NOT NULL default '0',  wss_type bigint NOT NULL default '0');
1805
1812
INSERT INTO t1 VALUES (102935998719055004,'brade',1029359987,2,102935229116544068,102935229216544093);
1806
1813
select wss_type from t1 where wss_type ='102935229216544106';
1807
1814
select wss_type from t1 where wss_type ='102935229216544105';
1831
1838
select * from (t1 as t2 left join t1 as t3 using (a)) straight_join t1;
1832
1839
select * from t1 straight_join (t1 as t2 left join t1 as t3 using (a));
1833
1840
# inner join on
 
1841
--sorted_result
1834
1842
select * from (t1 as t2 left join t1 as t3 using (a)) inner join t1 on t1.a>1;
1835
1843
select * from t1 inner join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1;
1836
1844
# inner join using
1859
1867
select * from (t1 as t2 left join t1 as t3 using (a)) natural join t1;
1860
1868
drop table t1;
1861
1869
 
1862
 
CREATE TABLE t1 (  aa char(2),  id int(11) NOT NULL auto_increment,  t2_id int(11) NOT NULL default '0',  PRIMARY KEY  (id),  KEY replace_id (t2_id)) ENGINE=MyISAM;
 
1870
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;
1863
1871
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);
1864
 
CREATE TABLE t2 ( id int(11) NOT NULL auto_increment,  PRIMARY KEY  (id)) ENGINE=MyISAM;
 
1872
CREATE TABLE t2 ( id int NOT NULL auto_increment,  PRIMARY KEY  (id)) ENGINE=MyISAM;
1865
1873
INSERT INTO t2 VALUES (2517), (2518), (2519), (2520), (2521), (2522);
1866
1874
select * from t1, t2 WHERE t1.t2_id = t2.id and t1.t2_id > 0   order by t1.id   LIMIT 0, 5;
1867
1875
drop table t1,t2;
1879
1887
insert into t2 values (1);
1880
1888
insert into t4 values (1,1);
1881
1889
 
1882
 
explain select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3
1883
 
left join t4 on id3 = id4 where id2 = 1 or id4 = 1;
 
1890
#explain select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3 left join t4 on id3 = id4 where id2 = 1 or id4 = 1;
1884
1891
select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3
1885
1892
left join t4 on id3 = id4 where id2 = 1 or id4 = 1;
1886
1893
 
1910
1917
create table t2 (c integer,  d integer, index(c), index(d));
1911
1918
insert into t1 values (1,2), (2,2), (3,2), (4,2);
1912
1919
insert into t2 values (1,3), (2,3), (3,4), (4,4);
1913
 
explain select * from t1 left join t2 on a=c where d in (4);
 
1920
#explain select * from t1 left join t2 on a=c where d in (4);
1914
1921
select * from t1 left join t2 on a=c where d in (4);
1915
 
explain select * from t1 left join t2 on a=c where d = 4;
 
1922
#explain select * from t1 left join t2 on a=c where d = 4;
1916
1923
select * from t1 left join t2 on a=c where d = 4;
1917
1924
drop table t1, t2;
1918
1925
 
1921
1928
#
1922
1929
 
1923
1930
CREATE TABLE t1 (
1924
 
  i int(11) NOT NULL default '0',
 
1931
  i int NOT NULL default '0',
1925
1932
  c char(10) NOT NULL default '',
1926
1933
  PRIMARY KEY  (i),
1927
1934
  UNIQUE KEY c (c)
1968
1975
DROP TABLE t1;
1969
1976
 
1970
1977
#
1971
 
# Bug#7425 inconsistent sort order on unsigned columns result of substraction
 
1978
# Bug#7425 inconsistent sort order on columns result of substraction
1972
1979
#
1973
1980
 
1974
 
create table t1 (a int(11) unsigned, b int(11) unsigned);
 
1981
create table t1 (a int, b int);
1975
1982
insert into t1 values (1,0), (1,1), (1,2);
1976
1983
select a-b  from t1 order by 1;
1977
1984
select a-b , (a-b < 0)  from t1 order by 1;
1978
1985
select a-b as d, (a-b >= 0), b from t1 group by b having d >= 0;
1979
 
select cast((a - b) as unsigned) from t1 order by 1;
 
1986
select a - b from t1 order by 1;
1980
1987
drop table t1;
1981
1988
 
1982
1989
 
1983
1990
#
1984
1991
# Bug#8733 server accepts malformed query (multiply mentioned distinct)
1985
1992
#
1986
 
create table t1 (a int(11));
 
1993
create table t1 (a int);
1987
1994
select all all * from t1;
1988
1995
select distinct distinct * from t1;
1989
1996
--error 1221
1996
2003
# Test for BUG#10095
1997
2004
#
1998
2005
CREATE TABLE t1 (
1999
 
  kunde_intern_id int(10) unsigned NOT NULL default '0',
2000
 
  kunde_id int(10) unsigned NOT NULL default '0',
2001
 
  FK_firma_id int(10) unsigned NOT NULL default '0',
 
2006
  kunde_intern_id int NOT NULL default '0',
 
2007
  kunde_id int NOT NULL default '0',
 
2008
  FK_firma_id int NOT NULL default '0',
2002
2009
  aktuell enum('Ja','Nein') NOT NULL default 'Ja',
2003
2010
  vorname varchar(128) NOT NULL default '',
2004
2011
  nachname varchar(128) NOT NULL default '',
2054
2061
 
2055
2062
#
2056
2063
#
2057
 
# Test for Bug#8009, SELECT failed on bigint unsigned when using HEX
 
2064
# Test for Bug#8009, SELECT failed on bigint when using HEX
2058
2065
#
2059
2066
 
2060
 
CREATE TABLE t1 (b BIGINT(20) UNSIGNED NOT NULL, PRIMARY KEY (b));
2061
 
INSERT INTO t1 VALUES (0x8000000000000000);
2062
 
SELECT b FROM t1 WHERE b=0x8000000000000000;
 
2067
CREATE TABLE t1 (b BIGINT NOT NULL, PRIMARY KEY (b));
 
2068
INSERT INTO t1 VALUES (0x4000000000000000);
 
2069
SELECT b FROM t1 WHERE b=0x4000000000000000;
2063
2070
DROP TABLE t1;
2064
2071
 
2065
2072
#
2066
2073
# IN with outer join condition (BUG#9393)
2067
2074
#
2068
 
CREATE TABLE `t1` ( `gid` int(11) default NULL, `uid` int(11) default NULL);
 
2075
CREATE TABLE `t1` ( `gid` int default NULL, `uid` int default NULL);
2069
2076
 
2070
 
CREATE TABLE `t2` ( `ident` int(11) default NULL, `level` char(16) default NULL);
 
2077
CREATE TABLE `t2` ( `ident` int default NULL, `level` char(16) default NULL);
2071
2078
INSERT INTO `t2` VALUES (0,'READ');
2072
2079
 
2073
 
CREATE TABLE `t3` ( `id` int(11) default NULL, `name` char(16) default NULL);
 
2080
CREATE TABLE `t3` ( `id` int default NULL, `name` char(16) default NULL);
2074
2081
INSERT INTO `t3` VALUES (1,'fs');
2075
2082
 
2076
2083
select * from t3 left join t1 on t3.id = t1.uid, t2 where t2.ident in (0, t1.gid, t3.id, 0);
2079
2086
 
2080
2087
# Test for BUG#11700
2081
2088
CREATE TABLE t1 (
2082
 
  acct_id int(11) NOT NULL default '0',
2083
 
  profile_id smallint(6) default NULL,
 
2089
  acct_id int NOT NULL default '0',
 
2090
  profile_id int default NULL,
2084
2091
  UNIQUE KEY t1$acct_id (acct_id),
2085
2092
  KEY t1$profile_id (profile_id)
2086
2093
);
2087
2094
INSERT INTO t1 VALUES (132,17),(133,18);
2088
2095
 
2089
2096
CREATE TABLE t2 (
2090
 
  profile_id smallint(6) default NULL,
2091
 
  queue_id int(11) default NULL,
2092
 
  seq int(11) default NULL,
 
2097
  profile_id int default NULL,
 
2098
  queue_id int default NULL,
 
2099
  seq int default NULL,
2093
2100
  KEY t2$queue_id (queue_id)
2094
2101
);
2095
2102
INSERT INTO t2 VALUES (17,31,4),(17,30,3),(17,36,2),(17,37,1);
2096
2103
 
2097
2104
CREATE TABLE t3 (
2098
 
  id int(11) NOT NULL default '0',
2099
 
  qtype int(11) default NULL,
2100
 
  seq int(11) default NULL,
2101
 
  warn_lvl int(11) default NULL,
2102
 
  crit_lvl int(11) default NULL,
2103
 
  rr1 tinyint(4) NOT NULL default '0',
2104
 
  rr2 int(11) default NULL,
2105
 
  default_queue tinyint(4) NOT NULL default '0',
 
2105
  id int NOT NULL default '0',
 
2106
  qtype int default NULL,
 
2107
  seq int default NULL,
 
2108
  warn_lvl int default NULL,
 
2109
  crit_lvl int default NULL,
 
2110
  rr1 int NOT NULL default '0',
 
2111
  rr2 int default NULL,
 
2112
  default_queue int NOT NULL default '0',
2106
2113
  KEY t3$qtype (qtype),
2107
2114
  KEY t3$id (id)
2108
2115
);
2133
2140
# Bug #11521 Negative integer keys incorrectly substituted for 0 during
2134
2141
#            range analysis.
2135
2142
 
2136
 
create table t2 (a tinyint unsigned);
 
2143
create table t2 (a int);
2137
2144
create index t2i on t2(a);
2138
2145
insert into t2 values (0), (254), (255);
2139
 
explain select * from t2 where a > -1;
 
2146
#explain select * from t2 where a > -1;
2140
2147
select * from t2 where a > -1;
2141
2148
drop table t2;
2142
2149
 
2223
2230
CREATE TABLE t3 (a int, b int, PRIMARY KEY (a,b), KEY a (a), KEY b (b));
2224
2231
insert into t3 values (1,1),(1,2);
2225
2232
# must have "range checked" for t2
2226
 
explain select straight_join DISTINCT t2.a,t2.b, t1.c from t1, t3, t2 
2227
 
 where (t1.c=t2.a or (t1.c=t3.a and t2.a=t3.b)) and t1.b=556476786 and 
2228
 
       t2.b like '%%' order by t2.b limit 0,1;
 
2233
#explain select straight_join DISTINCT t2.a,t2.b, t1.c from t1, t3, t2 where (t1.c=t2.a or (t1.c=t3.a and t2.a=t3.b)) and t1.b=556476786 and t2.b like '%%' order by t2.b limit 0,1;
2229
2234
DROP TABLE t1,t2,t3;
2230
2235
 
2231
2236
#
2268
2273
INSERT INTO t1 VALUES (0.3762),(0.3845),(0.6158),(0.7941);
2269
2274
INSERT INTO t2 VALUES (1.3762),(1.3845),(1.6158),(1.7941);
2270
2275
 
2271
 
explain select max(key1) from t1 where key1 <= 0.6158;
2272
 
explain select max(key2) from t2 where key2 <= 1.6158;
2273
 
explain select min(key1) from t1 where key1 >= 0.3762;
2274
 
explain select min(key2) from t2 where key2 >= 1.3762;
2275
 
explain select max(key1), min(key2) from t1, t2
2276
 
where key1 <= 0.6158 and key2 >= 1.3762;
2277
 
explain select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5;
2278
 
explain select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5;
 
2276
#explain select max(key1) from t1 where key1 <= 0.6158;
 
2277
#explain select max(key2) from t2 where key2 <= 1.6158;
 
2278
#explain select min(key1) from t1 where key1 >= 0.3762;
 
2279
#explain select min(key2) from t2 where key2 >= 1.3762;
 
2280
#explain select max(key1), min(key2) from t1, t2 where key1 <= 0.6158 and key2 >= 1.3762;
 
2281
#explain select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5;
 
2282
#explain select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5;
2279
2283
 
2280
2284
select max(key1) from t1 where key1 <= 0.6158;
2281
2285
select max(key2) from t2 where key2 <= 1.6158;
2303
2307
# This test is here only to make sure that behavior is not changed in
2304
2308
# 4.1 and 5.0
2305
2309
#
2306
 
CREATE TABLE t1 (i BIGINT UNSIGNED NOT NULL);
 
2310
CREATE TABLE t1 (i BIGINT NOT NULL);
2307
2311
INSERT INTO t1 VALUES (10);
2308
2312
SELECT i='1e+01',i=1e+01, i in (1e+01,1e+01), i in ('1e+01','1e+01') FROM t1;
2309
2313
DROP TABLE t1;
2336
2340
#
2337
2341
 
2338
2342
CREATE TABLE t1 ( 
2339
 
K2C4 varchar(4) character set latin1 collate latin1_bin NOT NULL default '', 
2340
 
K4N4 varchar(4) character set latin1 collate latin1_bin NOT NULL default '0000', 
2341
 
F2I4 int(11) NOT NULL default '0' 
2342
 
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
2343
K2C4 varchar(4) collate utf8_bin NOT NULL default '', 
 
2344
K4N4 varchar(4) collate utf8_bin NOT NULL default '0000', 
 
2345
F2I4 int NOT NULL default '0' 
 
2346
) ENGINE=MyISAM;
2343
2347
 
2344
2348
INSERT INTO t1 VALUES 
2345
2349
('W%RT', '0100',  1), 
2428
2432
#
2429
2433
# Bug #11398 Bug in field_conv() results in wrong result of join with index
2430
2434
#
2431
 
create table t1 (f1 varchar(6) default NULL, f2 int(6) primary key not null);
 
2435
create table t1 (f1 varchar(6) default NULL, f2 int primary key not null);
2432
2436
create table t2 (f3 varchar(5) not null, f4 varchar(5) not null, UNIQUE KEY UKEY (f3,f4));
2433
2437
insert into t1 values (" 2", 2);
2434
2438
insert into t2 values (" 2", " one "),(" 2", " two ");
2450
2454
#           incorrect transformation to join ... on.
2451
2455
#
2452
2456
 
2453
 
create table t1 (a int(10), t1_val int(10));
2454
 
create table t2 (b int(10), t2_val int(10));
2455
 
create table t3 (a int(10), b int(10));
 
2457
create table t1 (a int, t1_val int);
 
2458
create table t2 (b int, t2_val int);
 
2459
create table t3 (a int, b int);
2456
2460
insert into t1 values (1,1),(2,2);
2457
2461
insert into t2 values (1,1),(2,2),(3,3);
2458
2462
insert into t3 values (1,1),(2,1),(3,1),(4,1);
2479
2483
# for base tables, search all nested join operands of natural joins.
2480
2484
#
2481
2485
 
2482
 
CREATE TABLE t1 (`id` TINYINT);
2483
 
CREATE TABLE t2 (`id` TINYINT);
2484
 
CREATE TABLE t3 (`id` TINYINT);
 
2486
CREATE TABLE t1 (`id` int);
 
2487
CREATE TABLE t2 (`id` int);
 
2488
CREATE TABLE t3 (`id` int);
2485
2489
INSERT INTO t1 VALUES (1),(2),(3);
2486
2490
INSERT INTO t2 VALUES (2);
2487
2491
INSERT INTO t3 VALUES (3);
2500
2504
# Bug #13067 JOIN xxx USING is case sensitive
2501
2505
#
2502
2506
 
2503
 
create table t1 (a int(10),b int(10));
2504
 
create table t2 (a int(10),b int(10));
 
2507
create table t1 (a int,b int);
 
2508
create table t2 (a int,b int);
2505
2509
insert into t1 values (1,10),(2,20),(3,30);
2506
2510
insert into t2 values (1,10);
2507
2511
# both queries should produce the same result
2549
2553
# nested right join.
2550
2554
#
2551
2555
 
2552
 
create table t1 (id int(11) not null default '0');
 
2556
create table t1 (id int not null default '0');
2553
2557
insert into t1 values (123),(191),(192);
2554
 
create table t2 (id char(16) character set utf8 not null);
 
2558
create table t2 (id char(16) not null);
2555
2559
insert into t2 values ('58013'),('58014'),('58015'),('58016');
2556
 
create table t3 (a_id int(11) not null, b_id char(16) character set utf8);
 
2560
create table t3 (a_id int not null, b_id char(16));
2557
2561
insert into t3 values (123,null),(123,null),(123,null),(123,null),(123,null),(123,'58013');
2558
2562
 
2559
2563
# both queries are equivalent
2708
2712
 
2709
2713
DROP TABLE t1,t2;
2710
2714
 
2711
 
#
2712
 
# Bug#18712: Truncation problem (needs just documenting and test
2713
 
# cases to prevent fixing this accidently. It is intended behaviour)
2714
 
#
2715
 
 
2716
 
CREATE TABLE t1 (i TINYINT UNSIGNED NOT NULL);
2717
 
INSERT t1 SET i = 0;
2718
 
UPDATE t1 SET i = -1;
2719
 
SELECT * FROM t1;
2720
 
UPDATE t1 SET i = CAST(i - 1 AS SIGNED);
2721
 
SELECT * FROM t1;
2722
 
UPDATE t1 SET i = i - 1;
2723
 
SELECT * FROM t1;
2724
 
DROP TABLE t1;
2725
 
 
2726
2715
# BUG#17379
2727
2716
 
2728
2717
create table t1 (a int);
2732
2721
analyze table t2;
2733
2722
select 'In next EXPLAIN, B.rows must be exactly 10:' Z;
2734
2723
 
2735
 
explain select * from t2 A, t2 B where A.a=5 and A.b=5 and A.C<5
2736
 
          and B.a=5 and B.b=A.e and (B.b =1 or B.b = 3 or B.b=5);
 
2724
#explain select * from t2 A, t2 B where A.a=5 and A.b=5 and A.C<5 and B.a=5 and B.b=A.e and (B.b =1 or B.b = 3 or B.b=5);
2737
2725
drop table t1, t2;
2738
2726
 
2739
2727
#
2758
2746
DROP TABLE t1, t2;
2759
2747
 
2760
2748
#
2761
 
# Bug #18895: BIT values cause joins to fail
 
2749
# Bug #18895: Now tests for int
2762
2750
#
2763
2751
create table t1 (
2764
 
    a int unsigned    not null auto_increment primary key,
2765
 
    b bit             not null,
2766
 
    c bit             not null
 
2752
    a int    not null auto_increment primary key,
 
2753
    b int             not null,
 
2754
    c int             not null
2767
2755
);
2768
2756
 
2769
2757
create table t2 (
2770
 
    a int unsigned    not null auto_increment primary key,
2771
 
    b bit             not null,
2772
 
    c int unsigned    not null,
 
2758
    a int    not null auto_increment primary key,
 
2759
    b int             not null,
 
2760
    c int    not null,
2773
2761
    d varchar(50)
2774
2762
);
2775
2763
 
2995
2983
SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null IS NULL;
2996
2984
SELECT COUNT(*) FROM t1 WHERE ID_better=1;
2997
2985
 
 
2986
# PBXT: does not return a consistent row count
 
2987
--replace_column 9 #
2998
2988
EXPLAIN SELECT * FROM t1
2999
2989
  WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ;
 
2990
--replace_column 9 #
3000
2991
EXPLAIN SELECT * FROM t1
3001
2992
  WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null=3 IS NULL ;
 
2993
--replace_column 9 #
3002
2994
EXPLAIN SELECT * FROM t1
3003
2995
  WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL;
3004
2996
 
3039
3031
  AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31";
3040
3032
 
3041
3033
DROP TABLE t1,t2;
3042
 
# Bug #22026: Warning when using IF statement and large unsigned bigint
 
3034
# Bug #22026: Warning when using IF statement and large bigint
3043
3035
#
3044
3036
 
3045
 
create table t1 (a bigint unsigned);
 
3037
create table t1 (a bigint);
3046
3038
insert into t1 values
3047
 
  (if(1, 9223372036854775808, 1)),
3048
 
  (case when 1 then 9223372036854775808 else 1 end),
3049
 
  (coalesce(9223372036854775808, 1));
 
3039
  (if(1, 92233720368547758, 1)),
 
3040
  (case when 1 then 92233720368547758 else 1 end),
 
3041
  (coalesce(92233720368547758, 1));
3050
3042
select * from t1;
3051
3043
drop table t1;
3052
3044
create table t1 select
3057
3049
drop table t1;
3058
3050
# Ensure we handle big values properly
3059
3051
select 
3060
 
  if(1, cast(1111111111111111111 as unsigned), 1) i,
3061
 
  case when 1 then cast(1111111111111111111 as unsigned) else 1 end c,
3062
 
  coalesce(cast(1111111111111111111 as unsigned), 1) co;
 
3052
  if(1, 1111111111111111111, 1) i,
 
3053
  case when 1 then 1111111111111111111 else 1 end c,
 
3054
  coalesce(1111111111111111111, 1) co;
3063
3055
 
3064
3056
#
3065
3057
# Bug #22971: indexes on text columns are ignored for ref accesses 
3203
3195
#
3204
3196
 
3205
3197
CREATE TABLE t1 (
3206
 
  c1 int(11) NOT NULL AUTO_INCREMENT,
 
3198
  c1 int NOT NULL AUTO_INCREMENT,
3207
3199
  c2 varchar(1000) DEFAULT NULL,
3208
 
  c3 bigint(20) DEFAULT NULL,
3209
 
  c4 bigint(20) DEFAULT NULL,
 
3200
  c3 bigint DEFAULT NULL,
 
3201
  c4 bigint DEFAULT NULL,
3210
3202
  PRIMARY KEY (c1)
3211
3203
);
3212
3204
 
3272
3264
# Bug #30666: Incorrect order when using range conditions on 2 tables or more
3273
3265
#
3274
3266
 
3275
 
CREATE TABLE t1 (c11 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY);
3276
 
CREATE TABLE t2 (c21 INT UNSIGNED NOT NULL, 
 
3267
CREATE TABLE t1 (c11 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
 
3268
CREATE TABLE t2 (c21 INT NOT NULL, 
3277
3269
                 c22 INT DEFAULT NULL, 
3278
3270
                 KEY(c21, c22));
3279
 
CREATE TABLE t3 (c31 INT UNSIGNED NOT NULL DEFAULT 0, 
 
3271
CREATE TABLE t3 (c31 INT NOT NULL DEFAULT 0, 
3280
3272
                 c32 INT DEFAULT NULL, 
3281
3273
                 c33 INT NOT NULL, 
3282
 
                 c34 INT UNSIGNED DEFAULT 0,
 
3274
                 c34 INT DEFAULT 0,
3283
3275
                 KEY (c33, c34, c32));
3284
3276
 
3285
3277
INSERT INTO t1 values (),(),(),(),();