« back to all changes in this revision
Viewing changes to mysql-test/r/subselect.result
- browse files at revision 67
- compare with another revision
- compare with revision 1219
- download diff
- download tarball
- view history from revision 67
- Committer: Brian Aker
- Date: 2008-07-06 03:24:37 UTC
- Revision ID: brian@tangent.org-20080706032437-gfnpob6l1tjccisl
First pass for removing mediumint (3 byte INT type).
- files modified:
- mysql-test/r/information_schema.result
added
removed
mysql-test/r/subselect.result
377
377
ERROR 21000: Subquery returns more than 1 row
378
378
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8;
379
379
CREATE TABLE `t1` (
380
`topic` mediumint(8) unsigned NOT NULL default '0',
380
`topic` bigint(8) unsigned NOT NULL default '0',
381
381
`date` date NOT NULL default '0000-00-00',
382
382
`pseudo` varchar(35) character set latin1 NOT NULL default '',
383
383
PRIMARY KEY (`pseudo`,`date`,`topic`),
387
387
('43506','2002-10-02','joce'),('40143','2002-08-03','joce');
388
388
EXPLAIN EXTENDED SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
389
389
id select_type table type possible_keys key key_len ref rows filtered Extra
390
1 SIMPLE t1 index NULL PRIMARY 43 NULL 2 100.00 Using where; Using index
390
1 SIMPLE t1 index NULL PRIMARY 48 NULL 2 100.00 Using where; Using index
391
391
Warnings:
392
392
Note 1003 select distinct "test"."t1"."date" AS "date" from "test"."t1" where ("test"."t1"."date" = '2002-08-03')
393
393
EXPLAIN EXTENDED SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03');
394
394
id select_type table type possible_keys key key_len ref rows filtered Extra
395
395
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
396
2 SUBQUERY t1 index NULL PRIMARY 43 NULL 2 100.00 Using where; Using index
396
2 SUBQUERY t1 index NULL PRIMARY 48 NULL 2 100.00 Using where; Using index
397
397
Warnings:
398
398
Note 1003 select (select distinct "test"."t1"."date" AS "date" from "test"."t1" where ("test"."t1"."date" = '2002-08-03')) AS "(SELECT DISTINCT date FROM t1 WHERE date='2002-08-03')"
399
399
SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
411
411
ERROR 21000: Subquery returns more than 1 row
412
412
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1);
413
413
id select_type table type possible_keys key key_len ref rows filtered Extra
414
1 PRIMARY t1 index NULL topic 3 NULL 2 100.00 Using index
414
1 PRIMARY t1 index NULL topic 8 NULL 2 100.00 Using index
415
415
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
416
416
3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
417
417
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
419
419
Note 1003 select 1 AS "1" from "test"."t1" where 1
420
420
drop table t1;
421
421
CREATE TABLE `t1` (
422
`numeropost` mediumint(8) unsigned NOT NULL auto_increment,
422
`numeropost` bigint(8) unsigned NOT NULL auto_increment,
423
423
`maxnumrep` int(10) unsigned NOT NULL default '0',
424
424
PRIMARY KEY (`numeropost`),
425
425
UNIQUE KEY `maxnumrep` (`maxnumrep`)
427
427
INSERT INTO t1 (numeropost,maxnumrep) VALUES (40143,1),(43506,2);
428
428
CREATE TABLE `t2` (
429
429
`mot` varchar(30) NOT NULL default '',
430
`topic` mediumint(8) unsigned NOT NULL default '0',
430
`topic` bigint(8) unsigned NOT NULL default '0',
431
431
`date` date NOT NULL default '0000-00-00',
432
432
`pseudo` varchar(35) NOT NULL default '',
433
433
PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`)
491
491
joce 43506 2002-10-22 joce 0
492
492
drop table t1,t2;
493
493
CREATE TABLE `t1` (
494
`numeropost` mediumint(8) unsigned NOT NULL auto_increment,
494
`numeropost` bigint(8) unsigned NOT NULL auto_increment,
495
495
`maxnumrep` int(10) unsigned NOT NULL default '0',
496
496
PRIMARY KEY (`numeropost`),
497
497
UNIQUE KEY `maxnumrep` (`maxnumrep`)
516
516
ERROR 21000: Subquery returns more than 1 row
517
517
drop table t1;
518
518
CREATE TABLE `t1` (
519
`numeropost` mediumint(8) unsigned NOT NULL default '0',
519
`numeropost` bigint(8) unsigned NOT NULL default '0',
520
520
`numreponse` int(10) unsigned NOT NULL auto_increment,
521
521
`pseudo` varchar(35) NOT NULL default '',
522
522
PRIMARY KEY (`numeropost`,`numreponse`),
536
536
id select_type table type possible_keys key key_len ref rows filtered Extra
537
537
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
538
538
Warnings:
539
Note 1003 select max("test"."t1"."numreponse") AS "MAX(numreponse)" from "test"."t1" where ("test"."t1"."numeropost" = '1')
539
Note 1003 select max("test"."t1"."numreponse") AS "MAX(numreponse)" from "test"."t1" where multiple equal(1, "test"."t1"."numeropost")
540
540
EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');
541
541
id select_type table type possible_keys key key_len ref rows filtered Extra
542
1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using index
542
1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 12 const,const 1 100.00 Using index
543
543
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
544
544
Warnings:
545
Note 1003 select '3' AS "numreponse" from "test"."t1" where (('1' = '1'))
545
Note 1003 select '3' AS "numreponse" from "test"."t1" where 1
546
546
drop table t1;
547
547
CREATE TABLE t1 (a int(1));
548
548
INSERT INTO t1 VALUES (1);
984
984
drop table t1;
985
985
CREATE TABLE `t1` (
986
986
`mot` varchar(30) character set latin1 NOT NULL default '',
987
`topic` mediumint(8) unsigned NOT NULL default '0',
987
`topic` bigint(8) unsigned NOT NULL default '0',
988
988
`date` date NOT NULL default '0000-00-00',
989
989
`pseudo` varchar(35) character set latin1 NOT NULL default '',
990
990
PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`),
993
993
) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
994
994
CREATE TABLE `t2` (
995
995
`mot` varchar(30) character set latin1 NOT NULL default '',
996
`topic` mediumint(8) unsigned NOT NULL default '0',
996
`topic` bigint(8) unsigned NOT NULL default '0',
997
997
`date` date NOT NULL default '0000-00-00',
998
998
`pseudo` varchar(35) character set latin1 NOT NULL default '',
999
999
PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`),
1001
1001
KEY `topic` (`topic`)
1002
1002
) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
1003
1003
CREATE TABLE `t3` (
1004
`numeropost` mediumint(8) unsigned NOT NULL auto_increment,
1004
`numeropost` bigint(8) unsigned NOT NULL auto_increment,
1005
1005
`maxnumrep` int(10) unsigned NOT NULL default '0',
1006
1006
PRIMARY KEY (`numeropost`),
1007
1007
UNIQUE KEY `maxnumrep` (`maxnumrep`)
1109
1109
Oceania Sydney 3276207
1110
1110
drop table t1, t2;
1111
1111
CREATE TABLE `t1` (
1112
`id` mediumint(8) unsigned NOT NULL auto_increment,
1112
`id` bigint(8) unsigned NOT NULL auto_increment,
1113
1113
`pseudo` varchar(35) character set latin1 NOT NULL default '',
1114
1114
PRIMARY KEY (`id`),
1115
1115
UNIQUE KEY `pseudo` (`pseudo`)
1208
1208
3 1
1209
1209
drop table t1, t2;
1210
1210
CREATE TABLE `t1` (
1211
`id` mediumint(8) unsigned NOT NULL auto_increment,
1211
`id` bigint(8) unsigned NOT NULL auto_increment,
1212
1212
`pseudo` varchar(35) NOT NULL default '',
1213
1213
`email` varchar(60) NOT NULL default '',
1214
1214
PRIMARY KEY (`id`),
1512
1512
Warnings:
1513
1513
Note 1003 select "test"."t3"."a" AS "a" from "test"."t3" where <not>(("test"."t3"."a" <= <max>(select max("test"."t2"."b") AS "max(b)" from "test"."t2" group by "test"."t2"."a")))
1514
1514
drop table t2, t3;
1515
CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL auto_increment, `taskid` bigint(20) NOT NULL default '0', `dbid` int(11) NOT NULL default '0', `create_date` datetime NOT NULL default '0000-00-00 00:00:00', `last_update` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`id`)) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=3 ;
1515
CREATE TABLE `t1` ( `id` bigint(9) NOT NULL auto_increment, `taskid` bigint(20) NOT NULL default '0', `dbid` int(11) NOT NULL default '0', `create_date` datetime NOT NULL default '0000-00-00 00:00:00', `last_update` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`id`)) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=3 ;
1516
1516
INSERT INTO `t1` (`id`, `taskid`, `dbid`, `create_date`,`last_update`) VALUES (1, 1, 15, '2003-09-29 10:31:36', '2003-09-29 10:31:36'), (2, 1, 21, now(), now());
1517
1517
CREATE TABLE `t2` (`db_id` int(11) NOT NULL auto_increment,`name` varchar(200) NOT NULL default '',`primary_uid` smallint(6) NOT NULL default '0',`secondary_uid` smallint(6) NOT NULL default '0',PRIMARY KEY (`db_id`),UNIQUE KEY `name_2` (`name`)) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=2147483647;
1518
1518
INSERT INTO `t2` (`db_id`, `name`, `primary_uid`, `secondary_uid`) VALUES (18, 'Not Set 1', 0, 0),(19, 'Valid', 1, 2),(20, 'Valid 2', 1, 2),(21, 'Should Not Return', 1, 2),(26, 'Not Set 2', 0, 0),(-1, 'ALL DB\'S', 0, 0);
1519
CREATE TABLE `t3` (`taskgenid` mediumint(9) NOT NULL auto_increment,`dbid` int(11) NOT NULL default '0',`taskid` int(11) NOT NULL default '0',`mon` tinyint(4) NOT NULL default '1',`tues` tinyint(4) NOT NULL default '1',`wed` tinyint(4) NOT NULL default '1',`thur` tinyint(4) NOT NULL default '1',`fri` tinyint(4) NOT NULL default '1',`sat` tinyint(4) NOT NULL default '0',`sun` tinyint(4) NOT NULL default '0',`how_often` smallint(6) NOT NULL default '1',`userid` smallint(6) NOT NULL default '0',`active` tinyint(4) NOT NULL default '1',PRIMARY KEY (`taskgenid`)) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=2 ;
1519
CREATE TABLE `t3` (`taskgenid` bigint(9) NOT NULL auto_increment,`dbid` int(11) NOT NULL default '0',`taskid` int(11) NOT NULL default '0',`mon` tinyint(4) NOT NULL default '1',`tues` tinyint(4) NOT NULL default '1',`wed` tinyint(4) NOT NULL default '1',`thur` tinyint(4) NOT NULL default '1',`fri` tinyint(4) NOT NULL default '1',`sat` tinyint(4) NOT NULL default '0',`sun` tinyint(4) NOT NULL default '0',`how_often` smallint(6) NOT NULL default '1',`userid` smallint(6) NOT NULL default '0',`active` tinyint(4) NOT NULL default '1',PRIMARY KEY (`taskgenid`)) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=2 ;
1520
1520
INSERT INTO `t3` (`taskgenid`, `dbid`, `taskid`, `mon`, `tues`,`wed`, `thur`, `fri`, `sat`, `sun`, `how_often`, `userid`, `active`) VALUES (1,-1, 1, 1, 1, 1, 1, 1, 0, 0, 1, 0, 1);
1521
1521
CREATE TABLE `t4` (`task_id` smallint(6) NOT NULL default '0',`description` varchar(200) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1;
1522
1522
INSERT INTO `t4` (`task_id`, `description`) VALUES (1, 'Daily Check List'),(2, 'Weekly Status');
1596
1596
`id_cns` tinyint(3) unsigned NOT NULL default '0',
1597
1597
`tipo` enum('','UNO','DUE') NOT NULL default '',
1598
1598
`anno_dep` smallint(4) unsigned zerofill NOT NULL default '0000',
1599
`particolare` mediumint(8) unsigned NOT NULL default '0',
1600
`generale` mediumint(8) unsigned NOT NULL default '0',
1599
`particolare` bigint(8) unsigned NOT NULL default '0',
1600
`generale` bigint(8) unsigned NOT NULL default '0',
1601
1601
`bis` tinyint(3) unsigned NOT NULL default '0',
1602
1602
PRIMARY KEY (`id`),
1603
1603
UNIQUE KEY `idx_cns_gen_anno` (`anno_dep`,`id_cns`,`generale`,`particolare`),
Loggerhead is a web-based interface for Breezy
Version: 2.0.1