2494
2494
ALTER TABLE t1 ADD PARTITION PARTITIONS 2;
2495
2495
SELECT * from t1 order by i;
2499
--echo # MDEV-5177: ha_partition and innodb index intersection produce fewer rows (MySQL Bug#70703)
2508
) partition by hash(pk) partitions 10;
2510
insert into t1 values (1,2,4); # both
2511
insert into t1 values (1,0,17); # left
2512
insert into t1 values (1,2,25); # both
2514
insert into t1 values (10,20,122);
2515
insert into t1 values (10,20,123);
2517
# Now, fill in some data so that the optimizer choses index_merge
2518
create table t2 (a int);
2519
insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
2521
insert into t1 select 1,2, 200 + A.a + 10*B.a + 100*C.a from t2 A, t2 B, t2 C;
2523
insert into t1 select 10+A.a + 10*B.a + 100*C.a + 1000*D.a,
2524
10+A.a + 10*B.a + 100*C.a + 1000*D.a,
2525
2000 + A.a + 10*B.a + 100*C.a + 1000*D.a
2526
from t2 A, t2 B, t2 C ,t2 D;
2528
# This should show index_merge, using intersect
2529
explain select * from t1 where a=1 and b=2 and pk between 1 and 999999 ;
2530
# 794 rows in output
2531
create temporary table t3 as
2532
select * from t1 where a=1 and b=2 and pk between 1 and 999 ;
2533
select count(*) from t3;
2536
# 802 rows in output
2537
create temporary table t3 as
2538
select * from t1 ignore index(a,b) where a=1 and b=2 and pk between 1 and 999 ;
2539
select count(*) from t3;
2545
--echo # MDEV-5555: Incorrect index_merge on BTREE indices
2549
id bigint(20) unsigned NOT NULL,
2550
id2 bigint(20) unsigned NOT NULL,
2551
dob date DEFAULT NULL,
2552
address char(100) DEFAULT NULL,
2553
city char(35) DEFAULT NULL,
2554
hours_worked_per_week smallint(5) unsigned DEFAULT NULL,
2555
weeks_worked_last_year tinyint(3) unsigned DEFAULT NULL,
2557
KEY address (address),
2559
KEY hours_worked_per_week (hours_worked_per_week),
2560
KEY weeks_worked_last_year (weeks_worked_last_year)
2561
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2562
PARTITION BY KEY (id) PARTITIONS 5;
2564
--echo # Insert some rows
2566
INSERT INTO t1 VALUES (123,123,'2001-04-14','address123','city123',40,51),
2567
(127,127,'1977-03-30','address127','city127',0,0),
2568
(131,131,'1985-07-29','address131','city131',50,52),
2569
(135,135,'1997-01-20','address135','city135',0,0),
2570
(139,139,'1963-04-27','address139','city139',27,52),
2571
(143,143,'1979-01-28','address143','city143',40,52),
2572
(147,147,'1985-08-28','address147','city147',0,0),
2573
(151,151,'1997-01-24','address151','city151',40,52),
2574
(156,156,'1975-02-19','address156','city156',46,52),
2575
(158,158,'1996-07-06','address158','city158',46,8),
2576
(164,164,'1925-12-30','address164','city164',0,0),
2577
(166,166,'2010-12-30','address166','city166',0,0),
2578
(172,172,'1996-08-15','address172','city172',0,0),
2579
(174,174,'2008-05-20','address174','city174',40,52),
2580
(180,180,'1969-09-05','address180','city180',25,52),
2581
(182,182,'1977-08-11','address182','city182',40,8),
2582
(188,188,'2012-03-29','address188','city188',0,0),
2583
(190,190,'1978-02-19','address190','city190',0,0),
2584
(215,215,'1982-02-07','address215','city215',40,36),
2585
(223,223,'2005-02-11','address223','city223',55,52),
2586
(247,247,'2005-07-02','address247','city247',30,51),
2587
(255,255,'1997-08-15','address255','city255',0,0),
2588
(2,2,'1973-05-05','address2','city2',25,52),
2589
(4,4,'2012-07-21','address4','city4',40,12),
2590
(6,6,'1982-07-15','address6','city6',0,0),
2591
(8,8,'1979-02-16','address8','city8',0,0),
2592
(10,10,'1955-10-06','address10','city10',60,52),
2593
(12,12,'1977-08-09','address12','city12',40,52),
2594
(14,14,'2005-03-28','address14','city14',0,0),
2595
(16,16,'1949-11-07','address16','city16',40,52),
2596
(18,18,'2012-01-04','address18','city18',0,0),
2597
(20,20,'2011-01-23','address20','city20',0,0),
2598
(22,22,'1954-10-14','address22','city22',20,52),
2599
(24,24,'2010-01-22','address24','city24',0,0),
2600
(26,26,'1999-08-15','address26','city26',0,0),
2601
(28,28,'1964-07-05','address28','city28',10,20),
2602
(30,30,'2004-10-13','address30','city30',0,0),
2603
(32,32,'2007-06-08','address32','city32',0,0),
2604
(34,34,'1977-02-23','address34','city34',40,52),
2605
(36,36,'2007-06-11','address36','city36',75,52),
2606
(38,38,'1932-04-12','address38','city38',0,0),
2607
(40,40,'1968-11-16','address40','city40',0,0),
2608
(42,42,'1996-10-01','address42','city42',38,52),
2609
(44,44,'1977-08-23','address44','city44',18,6),
2610
(46,46,'1978-11-23','address46','city46',0,0),
2611
(48,48,'1998-02-27','address48','city48',0,0),
2612
(50,50,'1923-09-08','address50','city50',40,52),
2613
(52,52,'1964-09-09','address52','city52',0,0),
2614
(55,55,'2001-01-27','address55','city55',40,40),
2615
(56,56,'1938-08-28','address56','city56',48,52),
2616
(59,59,'1967-12-19','address59','city59',40,52),
2617
(60,60,'1969-06-30','address60','city60',40,46),
2618
(63,63,'2002-04-05','address63','city63',0,0),
2619
(64,64,'1972-11-21','address64','city64',48,52),
2620
(67,67,'1988-04-04','address67','city67',0,0),
2621
(68,68,'1964-07-14','address68','city68',4,16),
2622
(71,71,'1998-03-09','address71','city71',0,0),
2623
(72,72,'1960-10-28','address72','city72',35,52),
2624
(75,75,'1968-04-14','address75','city75',40,52),
2625
(76,76,'1977-05-13','address76','city76',0,0),
2626
(79,79,'1982-12-19','address79','city79',0,0),
2627
(80,80,'1966-01-07','address80','city80',15,12),
2628
(83,83,'1947-02-09','address83','city83',10,18),
2629
(84,84,'1976-06-23','address84','city84',40,52),
2630
(87,87,'2000-10-24','address87','city87',25,4),
2631
(88,88,'2002-05-23','address88','city88',20,52),
2632
(91,91,'2000-11-28','address91','city91',60,52),
2633
(92,92,'1965-07-17','address92','city92',40,52),
2634
(95,95,'1977-09-16','address95','city95',30,52),
2635
(96,96,'1994-09-26','address96','city96',0,0),
2636
(99,99,'2008-02-19','address99','city99',0,0),
2637
(100,100,'1953-01-07','address100','city100',0,0),
2638
(103,103,'2010-12-29','address103','city103',0,0),
2639
(104,104,'1990-12-03','address104','city104',40,52),
2640
(107,107,'2003-10-27','address107','city107',0,0),
2641
(108,108,'1998-03-05','address108','city108',40,17),
2642
(111,111,'2002-10-18','address111','city111',0,0),
2643
(112,112,'1960-04-02','address112','city112',0,0),
2644
(115,115,'1989-05-28','address115','city115',40,52),
2645
(116,116,'1985-10-25','address116','city116',15,52),
2646
(119,119,'1974-04-15','address119','city119',0,0),
2647
(120,120,'1926-03-21','address120','city120',0,0),
2648
(157,157,'1972-03-23','address157','city157',0,0),
2649
(159,159,'2002-11-08','address159','city159',0,0),
2650
(165,165,'1998-07-10','address165','city165',0,0),
2651
(167,167,'1973-11-16','address167','city167',0,0),
2652
(173,173,'1966-06-26','address173','city173',0,0),
2653
(175,175,'1957-02-02','address175','city175',0,0),
2654
(181,181,'1964-11-16','address181','city181',45,26),
2655
(183,183,'1943-12-02','address183','city183',0,0),
2656
(189,189,'1986-06-30','address189','city189',0,0),
2657
(191,191,'2005-05-14','address191','city191',0,0),
2658
(196,196,'1961-03-23','address196','city196',0,0),
2659
(197,197,'1955-07-13','address197','city197',0,0),
2660
(198,198,'2006-11-26','address198','city198',0,0),
2661
(199,199,'1978-02-06','address199','city199',0,0),
2662
(208,208,'2012-04-13','address208','city208',48,52),
2663
(210,210,'1989-08-18','address210','city210',0,0),
2664
(211,211,'1982-08-17','address211','city211',40,52),
2665
(212,212,'1919-08-29','address212','city212',0,0),
2666
(213,213,'1987-03-25','address213','city213',0,0),
2667
(228,228,'1988-05-05','address228','city228',40,52),
2668
(229,229,'1936-10-15','address229','city229',0,0),
2669
(230,230,'1973-08-19','address230','city230',40,52),
2670
(231,231,'2002-06-18','address231','city231',50,52),
2671
(240,240,'2011-10-17','address240','city240',60,52),
2672
(242,242,'1981-07-24','address242','city242',0,0),
2673
(243,243,'1978-10-12','address243','city243',0,0),
2674
(244,244,'2003-01-15','address244','city244',0,0),
2675
(245,245,'1950-09-26','address245','city245',0,0),
2676
(125,125,'1939-08-02','address125','city125',28,32),
2677
(126,126,'1984-02-10','address126','city126',0,0),
2678
(129,129,'1992-01-20','address129','city129',0,0),
2679
(130,130,'1992-09-18','address130','city130',0,0),
2680
(133,133,'1996-05-07','address133','city133',24,20),
2681
(134,134,'1987-07-13','address134','city134',0,0),
2682
(137,137,'2004-03-10','address137','city137',0,0),
2683
(138,138,'1989-02-10','address138','city138',0,0),
2684
(141,141,'1970-03-21','address141','city141',0,0),
2685
(142,142,'1984-05-25','address142','city142',40,50),
2686
(145,145,'1959-05-24','address145','city145',0,0),
2687
(146,146,'1946-07-28','address146','city146',35,16),
2688
(149,149,'1993-09-16','address149','city149',0,0),
2689
(150,150,'1975-12-18','address150','city150',0,0),
2690
(153,153,'1993-12-20','address153','city153',0,0),
2691
(155,155,'1934-10-29','address155','city155',0,0),
2692
(161,161,'1969-11-04','address161','city161',50,50),
2693
(163,163,'1976-05-03','address163','city163',40,52),
2694
(169,169,'1982-12-19','address169','city169',0,0),
2695
(171,171,'1976-07-01','address171','city171',0,0),
2696
(177,177,'2002-11-16','address177','city177',0,0),
2697
(179,179,'1964-02-05','address179','city179',40,32),
2698
(185,185,'1981-02-06','address185','city185',0,0),
2699
(187,187,'1962-06-04','address187','city187',40,52),
2700
(216,216,'1996-05-21','address216','city216',48,52),
2701
(248,248,'1963-09-06','address248','city248',0,0),
2702
(256,256,'1966-07-14','address256','city256',0,0),
2703
(53,53,'1992-05-25','address53','city53',0,0),
2704
(57,57,'2003-11-12','address57','city57',25,20),
2705
(61,61,'1953-01-29','address61','city61',0,0),
2706
(65,65,'1975-05-02','address65','city65',10,10),
2707
(69,69,'1938-03-20','address69','city69',0,0),
2708
(73,73,'1969-05-05','address73','city73',0,0),
2709
(77,77,'1996-05-19','address77','city77',0,0),
2710
(81,81,'1985-06-22','address81','city81',0,0),
2711
(85,85,'2002-10-10','address85','city85',0,0),
2712
(89,89,'1958-06-16','address89','city89',0,0),
2713
(93,93,'1962-06-16','address93','city93',0,0),
2714
(97,97,'1964-10-08','address97','city97',0,0),
2715
(101,101,'1986-06-11','address101','city101',40,52),
2716
(105,105,'1999-05-14','address105','city105',40,45),
2717
(109,109,'2000-05-23','address109','city109',0,0),
2718
(113,113,'1960-08-03','address113','city113',8,15),
2719
(117,117,'1982-02-15','address117','city117',50,36),
2720
(121,121,'1998-10-18','address121','city121',24,52),
2721
(192,192,'1964-07-24','address192','city192',40,52),
2722
(193,193,'1973-05-03','address193','city193',0,0),
2723
(194,194,'1980-01-14','address194','city194',40,52),
2724
(195,195,'1975-07-15','address195','city195',45,52),
2725
(200,200,'2006-03-09','address200','city200',0,0),
2726
(201,201,'2008-05-20','address201','city201',3,28),
2727
(202,202,'2000-06-30','address202','city202',12,52),
2728
(203,203,'1992-07-08','address203','city203',50,52),
2729
(204,204,'1988-07-05','address204','city204',14,40),
2730
(205,205,'1950-10-29','address205','city205',0,0),
2731
(206,206,'1962-11-25','address206','city206',0,0),
2732
(207,207,'1946-06-03','address207','city207',0,0),
2733
(214,214,'1973-12-14','address214','city214',0,0),
2734
(217,217,'1945-11-06','address217','city217',40,36),
2735
(218,218,'2007-07-20','address218','city218',0,0),
2736
(219,219,'1979-10-05','address219','city219',0,0),
2737
(220,220,'1992-06-20','address220','city220',10,12),
2738
(221,221,'2007-03-26','address221','city221',50,52),
2739
(222,222,'1989-12-24','address222','city222',0,0),
2740
(224,224,'1975-07-14','address224','city224',0,0),
2741
(225,225,'1976-02-23','address225','city225',20,52),
2742
(226,226,'1974-06-22','address226','city226',0,0),
2743
(227,227,'2004-01-16','address227','city227',0,0),
2744
(232,232,'1958-01-01','address232','city232',0,0),
2745
(233,233,'1966-08-03','address233','city233',40,32),
2746
(234,234,'1975-10-22','address234','city234',40,52),
2747
(235,235,'1983-10-25','address235','city235',0,0),
2748
(236,236,'1974-03-07','address236','city236',0,0),
2749
(237,237,'1965-12-31','address237','city237',45,20),
2750
(238,238,'1971-10-16','address238','city238',0,0),
2751
(239,239,'1989-07-19','address239','city239',0,0),
2752
(246,246,'1960-07-08','address246','city246',0,0),
2753
(249,249,'1943-07-01','address249','city249',40,30),
2754
(250,250,'1983-10-15','address250','city250',30,52),
2755
(251,251,'1979-07-03','address251','city251',0,0),
2756
(252,252,'1985-10-04','address252','city252',15,4),
2757
(253,253,'1966-10-24','address253','city253',0,0),
2758
(254,254,'1956-02-02','address254','city254',0,0),
2759
(1,1,'2003-11-23','address1','city1',40,52),
2760
(3,3,'1938-01-23','address3','city3',0,0),
2761
(5,5,'2006-12-27','address5','city5',40,48),
2762
(7,7,'1969-04-09','address7','city7',0,0),
2763
(9,9,'2006-06-14','address9','city9',0,0),
2764
(11,11,'1999-01-12','address11','city11',40,52),
2765
(13,13,'1968-01-13','address13','city13',50,12),
2766
(15,15,'1960-04-11','address15','city15',0,0),
2767
(17,17,'2006-10-13','address17','city17',40,52),
2768
(19,19,'1950-08-19','address19','city19',0,0),
2769
(21,21,'2000-05-01','address21','city21',40,30),
2770
(23,23,'1952-06-09','address23','city23',40,52),
2771
(25,25,'1934-12-08','address25','city25',32,40),
2772
(27,27,'1995-04-19','address27','city27',40,45),
2773
(29,29,'1986-01-14','address29','city29',44,52),
2774
(31,31,'1978-04-19','address31','city31',10,20),
2775
(33,33,'1989-11-23','address33','city33',25,10),
2776
(35,35,'2012-01-02','address35','city35',8,48),
2777
(37,37,'2005-08-24','address37','city37',40,42),
2778
(39,39,'1973-11-02','address39','city39',40,52),
2779
(41,41,'2011-10-12','address41','city41',20,30),
2780
(43,43,'1960-12-24','address43','city43',0,0),
2781
(45,45,'1990-04-17','address45','city45',35,40),
2782
(47,47,'1964-04-02','address47','city47',0,0),
2783
(49,49,'1957-01-25','address49','city49',40,52),
2784
(51,51,'1970-10-20','address51','city51',0,0),
2785
(54,54,'1987-09-30','address54','city54',0,0),
2786
(58,58,'1975-05-07','address58','city58',0,0),
2787
(62,62,'1972-08-03','address62','city62',40,52),
2788
(66,66,'1995-11-04','address66','city66',0,0),
2789
(70,70,'1985-10-19','address70','city70',40,52),
2790
(74,74,'1969-06-09','address74','city74',0,0),
2791
(78,78,'2003-01-16','address78','city78',66,52),
2792
(82,82,'2012-04-29','address82','city82',50,30),
2793
(86,86,'2008-02-03','address86','city86',0,0),
2794
(90,90,'1973-05-15','address90','city90',35,12),
2795
(94,94,'1987-10-28','address94','city94',40,50),
2796
(98,98,'1973-06-10','address98','city98',65,50),
2797
(102,102,'2009-09-13','address102','city102',0,0),
2798
(106,106,'1986-07-03','address106','city106',0,0),
2799
(110,110,'1982-06-10','address110','city110',35,52),
2800
(114,114,'1963-10-08','address114','city114',48,52),
2801
(118,118,'1948-03-07','address118','city118',0,0),
2802
(122,122,'1997-12-19','address122','city122',0,0),
2803
(124,124,'1966-03-25','address124','city124',0,0),
2804
(128,128,'1968-08-13','address128','city128',0,0),
2805
(132,132,'1989-09-25','address132','city132',20,20),
2806
(136,136,'1993-09-02','address136','city136',0,0),
2807
(140,140,'1981-05-31','address140','city140',48,52),
2808
(144,144,'1960-09-15','address144','city144',0,0),
2809
(148,148,'1945-02-13','address148','city148',40,38),
2810
(152,152,'2010-11-13','address152','city152',20,52),
2811
(154,154,'1950-11-07','address154','city154',55,52),
2812
(160,160,'1981-01-17','address160','city160',0,0),
2813
(162,162,'2001-03-19','address162','city162',0,0),
2814
(168,168,'2003-03-28','address168','city168',0,0),
2815
(170,170,'1977-06-18','address170','city170',50,52),
2816
(176,176,'1967-04-15','address176','city176',30,50),
2817
(178,178,'1989-10-25','address178','city178',60,12),
2818
(184,184,'2004-04-21','address184','city184',0,0),
2819
(186,186,'1952-11-08','address186','city186',50,48),
2820
(209,209,'1943-03-15','address209','city209',40,30),
2821
(241,241,'1979-12-02','address241','city241',0,0),
2822
(257,257,'2010-03-06','address257','city257',40,47);
2825
select * from t1 where hours_worked_per_week = 40 and weeks_worked_last_year = 52 and dob < '1949-11-21';
2826
select * from t1 IGNORE INDEX(dob, weeks_worked_last_year, hours_worked_per_week) where hours_worked_per_week = 40 and weeks_worked_last_year = 52 and dob < '1949-11-21';
added
removed
mysql-test/t/partition.test
