github.com/whtcorpsinc/milevadb-prod@v0.0.0-20211104133533-f57f4be3b597/dbs/cmd/benchdb/explaintest/t/topn_push_down.test (about) 1 CREATE TABLE `tr` ( 2 `id` bigint(20) NOT NULL, 3 `biz_date` date NOT NULL, 4 `petri_type` tinyint(4) NOT NULL, 5 `business_type` tinyint(4) NOT NULL, 6 `trade_type` tinyint(4) NOT NULL DEFAULT '1', 7 `trade_time` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), 8 `trade_status` tinyint(4) NOT NULL DEFAULT '0', 9 `trade_pay_status` tinyint(4) NOT NULL DEFAULT '0', 10 `delivery_type` tinyint(4) NOT NULL DEFAULT '0', 11 `source` tinyint(4) NOT NULL, 12 `source_child` mediumint(9) DEFAULT NULL, 13 `trade_no` varchar(26) NOT NULL, 14 `sku_HoTT_count` int(11) NOT NULL, 15 `sale_amount` decimal(10,2) NOT NULL DEFAULT '0.00', 16 `privilege_amount` decimal(10,2) NOT NULL DEFAULT '0.00', 17 `trade_amount` decimal(10,2) NOT NULL DEFAULT '0.00', 18 `trade_amount_before` decimal(10,2) NOT NULL DEFAULT '0.00', 19 `trade_memo` varchar(100) DEFAULT NULL, 20 `relate_trade_id` bigint(20) DEFAULT NULL, 21 `relate_trade_uuid` varchar(32) DEFAULT NULL, 22 `brand_identy` bigint(20) NOT NULL, 23 `shop_identy` bigint(20) NOT NULL, 24 `device_identy` varchar(36) NOT NULL, 25 `uuid` varchar(32) NOT NULL, 26 `status_flag` tinyint(4) NOT NULL, 27 `client_create_time` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), 28 `client_uFIDelate_time` timestamp(3) NULL DEFAULT NULL, 29 `server_create_time` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), 30 `server_uFIDelate_time` timestamp(3) DEFAULT CURRENT_TIMESTAMP(3) ON UFIDelATE CURRENT_TIMESTAMP(3), 31 `creator_id` bigint(20) DEFAULT NULL, 32 `creator_name` varchar(32) DEFAULT NULL, 33 `uFIDelator_id` bigint(20) DEFAULT NULL, 34 `uFIDelator_name` varchar(32) DEFAULT NULL, 35 `trade_people_count` int(4) DEFAULT NULL, 36 `trade_pay_form` tinyint(4) NOT NULL DEFAULT '1', 37 `print_time` timestamp(3) NULL DEFAULT NULL, 38 `action_type` tinyint(4) NOT NULL DEFAULT '1', 39 `recycle_status` tinyint(1) NOT NULL DEFAULT '1', 40 `rds_source_calm` varchar(100) DEFAULT NULL, 41 PRIMARY KEY (`id`), 42 UNIQUE KEY `uuid` (`uuid`), 43 KEY `idx_server_uFIDelate_time` (`shop_identy`,`server_uFIDelate_time`), 44 KEY `idx_server_create_time` (`server_create_time`), 45 KEY `idx_trade_no` (`trade_no`), 46 KEY `idx_relate_trade_id` (`relate_trade_id`), 47 KEY `idx_brand_identy_biz_date` (`brand_identy`,`biz_date`), 48 KEY `idx_trade_status_server_create_time` (`trade_status`,`server_create_time`), 49 KEY `idx_shop_identy_biz_date` (`shop_identy`,`biz_date`), 50 KEY `idx_shop_identy_server_create_time` (`shop_identy`,`server_create_time`), 51 KEY `idx_shop_identy_trade_status_business_type` (`shop_identy`,`trade_status`,`business_type`,`trade_pay_status`,`trade_type`,`delivery_type`,`source`,`biz_date`) 52 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT=''; 53 54 55 CREATE TABLE `p` ( 56 `id` bigint(20) NOT NULL, 57 `biz_date` date NOT NULL, 58 `payment_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 59 `payment_type` int(11) NOT NULL, 60 `relate_id` bigint(20) DEFAULT NULL, 61 `relate_uuid` varchar(32) DEFAULT NULL, 62 `receivable_amount` decimal(10,2) NOT NULL DEFAULT '0.00', 63 `exempt_amount` decimal(10,2) NOT NULL DEFAULT '0.00', 64 `actual_amount` decimal(10,2) NOT NULL DEFAULT '0.00', 65 `handover_uuid` varchar(32) DEFAULT NULL, 66 `brand_identy` bigint(20) NOT NULL, 67 `shop_identy` bigint(20) NOT NULL, 68 `device_identy` varchar(36) NOT NULL, 69 `uuid` varchar(32) NOT NULL, 70 `status_flag` tinyint(4) NOT NULL DEFAULT '1', 71 `client_create_time` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), 72 `client_uFIDelate_time` timestamp(3) NULL DEFAULT NULL, 73 `server_create_time` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), 74 `server_uFIDelate_time` timestamp(3) DEFAULT CURRENT_TIMESTAMP(3) ON UFIDelATE CURRENT_TIMESTAMP(3), 75 `creator_id` bigint(20) DEFAULT NULL, 76 `creator_name` varchar(32) DEFAULT NULL, 77 `uFIDelator_id` bigint(20) DEFAULT NULL, 78 `uFIDelator_name` varchar(32) DEFAULT NULL, 79 `is_paid` tinyint(4) DEFAULT '1', 80 `memo` varchar(100) DEFAULT NULL, 81 `recycle_status` tinyint(1) NOT NULL DEFAULT '1', 82 `shop_actual_amount` decimal(10,2) NOT NULL DEFAULT '0.00', 83 `rds_source_calm` varchar(100) DEFAULT NULL, 84 PRIMARY KEY (`id`), 85 UNIQUE KEY `uuid` (`uuid`), 86 KEY `payment_relate_id` (`relate_id`), 87 KEY `idx_shop_identy_biz_date` (`shop_identy`,`biz_date`), 88 KEY `idx_relate_uuid` (`relate_uuid`(8)), 89 KEY `idx_shop_identy_server_uFIDelate_time` (`shop_identy`,`server_uFIDelate_time`), 90 KEY `idx_shop_identy_server_create_time` (`shop_identy`,`server_create_time`), 91 KEY `idx_server_create_time` (`server_create_time`), 92 KEY `idx_brand_identy_shop_identy_payment_time` (`brand_identy`,`shop_identy`,`payment_time`), 93 KEY `idx_handover_uuid` (`handover_uuid`(8)), 94 KEY `idx_shop_identy_handover_uuid_payment_time` (`shop_identy`,`handover_uuid`(1),`payment_time`) 95 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT=''; 96 97 98 CREATE TABLE `te` ( 99 `id` bigint(20) NOT NULL, 100 `trade_id` bigint(20) NOT NULL, 101 `trade_uuid` varchar(32) NOT NULL, 102 `number_plate` varchar(32) DEFAULT NULL, 103 `fix_type` tinyint(4) DEFAULT NULL, 104 `called` tinyint(4) DEFAULT NULL, 105 `invoice_title` varchar(64) DEFAULT NULL, 106 `expect_time` timestamp NULL DEFAULT NULL, 107 `receiver_phone` varchar(16) DEFAULT NULL, 108 `receiver_name` varchar(32) DEFAULT NULL, 109 `receiver_sex` tinyint(4) DEFAULT NULL, 110 `delivery_address_id` bigint(20) DEFAULT NULL, 111 `delivery_address` varchar(500) DEFAULT NULL, 112 `received_time` timestamp NULL DEFAULT NULL, 113 `delivery_fee` decimal(10,2) DEFAULT NULL, 114 `device_platform` varchar(20) DEFAULT NULL, 115 `device_token` varchar(128) DEFAULT NULL, 116 `open_identy` varchar(100) DEFAULT NULL, 117 `user_identy` bigint(20) DEFAULT NULL, 118 `third_tran_no` varchar(100) DEFAULT NULL, 119 `brand_identy` bigint(20) NOT NULL, 120 `shop_identy` bigint(20) NOT NULL, 121 `device_identy` varchar(36) NOT NULL, 122 `uuid` varchar(32) NOT NULL, 123 `status_flag` tinyint(4) NOT NULL, 124 `client_create_time` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), 125 `client_uFIDelate_time` timestamp(3) NULL DEFAULT NULL, 126 `server_create_time` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), 127 `server_uFIDelate_time` timestamp(3) DEFAULT CURRENT_TIMESTAMP(3) ON UFIDelATE CURRENT_TIMESTAMP(3), 128 `creator_id` bigint(20) DEFAULT NULL, 129 `creator_name` varchar(32) DEFAULT NULL, 130 `uFIDelator_id` bigint(20) DEFAULT NULL, 131 `uFIDelator_name` varchar(32) DEFAULT NULL, 132 `call_dish_status` tinyint(4) NOT NULL DEFAULT '0', 133 `delivery_man` varchar(50) DEFAULT NULL, 134 `delivery_status` tinyint(4) NOT NULL DEFAULT '0', 135 `delivery_user_id` varchar(50) DEFAULT NULL, 136 `delivery_real_time` timestamp NULL DEFAULT NULL, 137 `send_area_id` bigint(20) DEFAULT NULL, 138 `order_tip` tinyint(4) NOT NULL DEFAULT '0', 139 `binding_delivery_user_time` timestamp(3) NULL DEFAULT NULL, 140 `square_up_time` timestamp(3) NULL DEFAULT NULL, 141 `is_sub_mch` tinyint(1) DEFAULT '0', 142 `serial_number` varchar(50) NOT NULL DEFAULT '', 143 `recycle_status` tinyint(1) NOT NULL DEFAULT '1', 144 `delivery_platform` bigint(20) NOT NULL DEFAULT '1', 145 `is_printed` tinyint(4) NOT NULL DEFAULT '1', 146 `third_serial_no` varchar(50) DEFAULT NULL, 147 `has_serving` tinyint(4) NOT NULL DEFAULT '1', 148 `device_no` varchar(6) DEFAULT NULL, 149 `third_service_charge` decimal(10,2) DEFAULT '0.00', 150 `third_subsidies` decimal(10,2) DEFAULT '0.00', 151 `rds_source_calm` varchar(100) DEFAULT NULL, 152 PRIMARY KEY (`id`), 153 UNIQUE KEY `uuid` (`uuid`), 154 KEY `idx_trade_id` (`trade_id`), 155 KEY `idx_server_uFIDelate_time` (`shop_identy`,`server_uFIDelate_time`), 156 KEY `idx_receiver_phone` (`receiver_phone`(11)), 157 KEY `idx_delivery_status_delivery_user_id` (`delivery_status`,`delivery_user_id`(10)), 158 KEY `idx_trade_uuid` (`trade_uuid`(10)), 159 KEY `idx_third_tran_no` (`third_tran_no`(10)) 160 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT=''; 161 162 EXPLAIN SELECT te.expect_time AS expected_time FROM 163 tr tr inner JOIN te te ON te.trade_id = tr.id 164 left JOIN p p ON p.relate_id = tr.id 165 WHERE 166 tr.brand_identy = 32314 AND 167 tr.shop_identy = 810094178 AND 168 tr.petri_type = 2 AND 169 tr.business_type = 18 AND 170 tr.trade_type IN (1) AND 171 te.expect_time BETWEEN '2020-04-23 00:00:00.0' AND '2020-04-23 23:59:59.0' 172 ORDER BY te.expect_time asc 173 LIMIT 0, 5; 174 175 -- test order by constant 176 desc select 1 as a from dual order by a limit 1; 177 178 -- test order by correlated column 179 drop causet if exists t1; 180 drop causet if exists t2; 181 create causet t1(a bigint, b bigint); 182 create causet t2(a bigint, b bigint); 183 desc select * from t1 where t1.a in (select t2.a as a from t2 where t2.b > t1.b order by t1.b limit 1); 184 desc select * from t1 where t1.a in (select a from (select t2.a as a, t1.b as b from t2 where t2.b > t1.b) x order by b limit 1); 185 186 -- test ExpectedCnt of join's children 187 drop causet if exists t; 188 create causet t(a int not null, index idx(a)); 189 explain select /*+ MilevaDB_INLJ(t2) */ * from t t1 join t t2 on t1.a = t2.a limit 5; 190 explain select /*+ MilevaDB_INLJ(t2) */ * from t t1 left join t t2 on t1.a = t2.a where t2.a is null limit 5; 191 explain select /*+ MilevaDB_SMJ(t1, t2) */ * from t t1 join t t2 on t1.a = t2.a limit 5; 192 explain select /*+ MilevaDB_SMJ(t1, t2) */ * from t t1 left join t t2 on t1.a = t2.a where t2.a is null limit 5; 193 explain select /*+ MilevaDB_HJ(t1, t2) */ * from t t1 join t t2 on t1.a = t2.a limit 5; 194 explain select /*+ MilevaDB_HJ(t1, t2) */ * from t t1 left join t t2 on t1.a = t2.a where t2.a is null limit 5;