github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/ddl/mysql_ddl_4.sql (about) 1 -- testcase_4: create view 2 3 4 create database if not exists mysql_ddl_test_db_4; 5 use mysql_ddl_test_db_4; 6 7 DROP TABLE IF EXISTS `mysql_ddl_test_t41`; 8 /*!40101 SET @saved_cs_client = @@character_set_client */; 9 /*!50503 SET character_set_client = utf8mb4 */; 10 CREATE TABLE `mysql_ddl_test_t41` ( 11 `SEQID` int(10) NOT NULL AUTO_INCREMENT, 12 `SA_CONTRACT_LINE_ID` decimal(11,0) DEFAULT NULL, 13 `SA_CONTRACT_HEAD_ID` decimal(11,0) DEFAULT NULL, 14 `LINENO` decimal(11,0) DEFAULT NULL, 15 `ITEM_ID` decimal(11,0) DEFAULT NULL, 16 `ASSISTANT_UOM_ID` decimal(11,0) DEFAULT NULL, 17 `LENGTH` decimal(11,2) DEFAULT NULL, 18 `WIDTH` decimal(11,2) DEFAULT NULL, 19 `BASE_LEVEL_ID` decimal(11,0) DEFAULT NULL, 20 `BASE_PRICE` decimal(10,2) DEFAULT NULL, 21 `CONTRACT_PRICE` decimal(10,2) DEFAULT NULL, 22 `QTY_CONVERT` decimal(10,2) DEFAULT NULL, 23 `SMETER_PRICE` decimal(10,2) DEFAULT NULL, 24 `REMARK` varchar(255) DEFAULT NULL, 25 `CREATED_BY` varchar(255) DEFAULT NULL, 26 `CREATION_DATE` datetime DEFAULT NULL, 27 `LAST_UPDATED_BY` varchar(255) DEFAULT NULL, 28 `LAST_UPDATE_DATE` datetime DEFAULT NULL, 29 `ATTRIBUTE11` varchar(255) DEFAULT NULL, 30 `ATTRIBUTE21` varchar(255) DEFAULT NULL, 31 `ATTRIBUTE31` varchar(255) DEFAULT NULL, 32 `ATTRIBUTE41` varchar(255) DEFAULT NULL, 33 `ATTRIBUTE51` varchar(255) DEFAULT NULL, 34 `DEPOSIT_RATE` decimal(11,2) DEFAULT NULL, 35 `SUESYN` decimal(10,0) DEFAULT NULL, 36 `SREMARK` varchar(255) DEFAULT NULL, 37 `SINTIM` datetime DEFAULT NULL, 38 `SUPTIM` datetime DEFAULT NULL, 39 PRIMARY KEY (`SEQID`) USING BTREE, 40 KEY `SA_CONTRACT_LINE_IDX1` (`SA_CONTRACT_HEAD_ID`,`ITEM_ID`,`ASSISTANT_UOM_ID`,`LENGTH`,`WIDTH`,`BASE_LEVEL_ID`) USING BTREE, 41 KEY `SYS_C0042516` (`SA_CONTRACT_LINE_ID`) USING BTREE 42 ) ENGINE=InnoDB AUTO_INCREMENT=11994 DEFAULT CHARSET=utf8; 43 44 45 DROP TABLE IF EXISTS `mysql_ddl_test_t42`; 46 /*!40101 SET @saved_cs_client = @@character_set_client */; 47 /*!50503 SET character_set_client = utf8mb4 */; 48 CREATE TABLE `mysql_ddl_test_t42` ( 49 `SEQID` int(10) NOT NULL AUTO_INCREMENT, 50 `ORGANIZATION_ID` decimal(11,0) DEFAULT NULL, 51 `SA_CONTRACT_HEAD_ID` decimal(11,0) DEFAULT NULL, 52 `CONTRACT_NO` varchar(255) DEFAULT NULL, 53 `SIGN_DATE` datetime DEFAULT NULL, 54 `SIGN_ADDR` varchar(255) DEFAULT NULL, 55 `BASE_CURRENCY_ID` decimal(11,0) DEFAULT NULL, 56 `BEGINDATE` datetime DEFAULT NULL, 57 `ENDDATE` datetime DEFAULT NULL, 58 `FINAL_CUSTOMER_ID` decimal(11,0) DEFAULT NULL, 59 `TAX_RATE` double DEFAULT NULL, 60 `DUTY_PERSON_ID` decimal(11,0) DEFAULT NULL, 61 `DUTY_PERSON` varchar(255) DEFAULT NULL, 62 `DEAL_PERSON_ID` decimal(11,0) DEFAULT NULL, 63 `DEAL_PERSON` varchar(255) DEFAULT NULL, 64 `INNER_CUSTOMER_ID` decimal(11,0) DEFAULT NULL, 65 `SALECOMPANY_ID` decimal(11,0) DEFAULT NULL, 66 `ISVALID` decimal(11,0) DEFAULT NULL, 67 `NOTE` varchar(255) DEFAULT NULL, 68 `STAT` varchar(255) DEFAULT NULL, 69 `WFID` decimal(11,2) DEFAULT NULL, 70 `WFFLAG` decimal(11,2) DEFAULT NULL, 71 `PROCID` decimal(11,0) DEFAULT NULL, 72 `WFRIGHT` decimal(11,2) DEFAULT NULL, 73 `CREATED_BY` varchar(255) DEFAULT NULL, 74 `CREATION_DATE` datetime DEFAULT NULL, 75 `LAST_UPDATED_BY` varchar(255) DEFAULT NULL, 76 `LAST_UPDATE_DATE` datetime DEFAULT NULL, 77 `ATTRIBUTE1` varchar(255) DEFAULT NULL, 78 `ATTRIBUTE2` varchar(255) DEFAULT NULL, 79 `ATTRIBUTE3` varchar(255) DEFAULT NULL, 80 `ATTRIBUTE4` varchar(255) DEFAULT NULL, 81 `ATTRIBUTE5` varchar(255) DEFAULT NULL, 82 `SUESYN` decimal(10,0) DEFAULT NULL, 83 `SREMARK` varchar(255) DEFAULT NULL, 84 `SINTIM` datetime DEFAULT NULL, 85 `SUPTIM` datetime DEFAULT NULL, 86 PRIMARY KEY (`SEQID`) USING BTREE, 87 KEY `IDX_SA_CONTRACT_HEAD_ID` (`SA_CONTRACT_HEAD_ID`) USING BTREE 88 ) ENGINE=InnoDB AUTO_INCREMENT=1958 DEFAULT CHARSET=utf8; 89 90 DROP TABLE IF EXISTS `mysql_ddl_test_t43`; 91 /*!40101 SET @saved_cs_client = @@character_set_client */; 92 /*!50503 SET character_set_client = utf8mb4 */; 93 CREATE TABLE `mysql_ddl_test_t43` ( 94 `SEQID` int(10) NOT NULL AUTO_INCREMENT, 95 `PMS_CONTRACT_ITEM_ID` decimal(10,0) NOT NULL, 96 `PMS_CONTRACT_ID` decimal(10,0) NOT NULL, 97 `ITEM_ID` decimal(10,0) DEFAULT NULL, 98 `ITEM_CODE` varchar(128) DEFAULT NULL, 99 `ITEM_NAME` varchar(128) DEFAULT NULL, 100 `SPECS` varchar(32) DEFAULT NULL, 101 `COLOR_NUMBER` varchar(32) DEFAULT NULL, 102 `USE_AREA` decimal(18,8) DEFAULT NULL, 103 `QTY_BOX` decimal(18,8) DEFAULT NULL, 104 `QTY_SLICE` decimal(18,8) DEFAULT NULL, 105 `PRICE_SLICE` decimal(18,8) DEFAULT NULL, 106 `AMOUNT` decimal(18,2) DEFAULT NULL, 107 `REMARK` varchar(128) DEFAULT NULL, 108 `COST_PRICE` decimal(18,8) DEFAULT NULL, 109 `GROSS_PROFIT` decimal(18,8) DEFAULT NULL, 110 `PRICE_SQUARE` decimal(18,8) DEFAULT NULL, 111 `UOM_ID` decimal(10,0) DEFAULT NULL, 112 `UOM_NAME` varchar(16) DEFAULT NULL, 113 `COST` decimal(18,8) DEFAULT NULL, 114 `ITEM_AREA` decimal(10,0) DEFAULT NULL, 115 `CONVRATE` decimal(10,0) DEFAULT NULL, 116 `PRICE_BOX` decimal(18,8) DEFAULT NULL, 117 `BASE_LEVEL_ID` decimal(10,0) DEFAULT NULL, 118 `STANDARD_PRICE_SLICE` decimal(18,2) DEFAULT NULL, 119 `STANDARD_PRICE_SQUARE` decimal(18,2) DEFAULT NULL, 120 `SUESYN` decimal(10,0) DEFAULT NULL, 121 `SREMARK` varchar(255) DEFAULT NULL, 122 `SINTIM` datetime DEFAULT NULL, 123 `SUPTIM` datetime DEFAULT NULL, 124 PRIMARY KEY (`SEQID`) USING BTREE, 125 KEY `PK_PMS_CONTRACT_ITEM_ID` (`PMS_CONTRACT_ITEM_ID`) USING BTREE 126 ) ENGINE=InnoDB AUTO_INCREMENT=234835 DEFAULT CHARSET=utf8; 127 128 129 DROP TABLE IF EXISTS `mysql_ddl_test_t44`; 130 /*!40101 SET @saved_cs_client = @@character_set_client */; 131 /*!50503 SET character_set_client = utf8mb4 */; 132 CREATE TABLE `mysql_ddl_test_t44` ( 133 `SEQID` int(11) NOT NULL AUTO_INCREMENT, 134 `PMS_CONTRACT_ID` decimal(10,0) NOT NULL, 135 `CONTRACT_NO` varchar(128) NOT NULL, 136 `CONTRACT_NAME` varchar(128) NOT NULL, 137 `CONTRACT_TYPE` int(1) NOT NULL DEFAULT '1', 138 `PMS_PROJECT_ID` decimal(10,0) DEFAULT NULL, 139 `CUSTOMER_ID` decimal(10,0) DEFAULT NULL, 140 `CUSTOMER_CODE` varchar(32) DEFAULT NULL, 141 `CUSTOMER_NAME` varchar(128) DEFAULT NULL, 142 `CONTRACT_DATE` date DEFAULT NULL, 143 `CONTRACT_EFFECTIVE_START` date DEFAULT NULL, 144 `CONTRACT_EFFECTIVE_END` date DEFAULT NULL, 145 `CONTRACT_SEQ` varchar(32) DEFAULT NULL, 146 `SETTLEMENT_ID` decimal(10,0) DEFAULT NULL, 147 `SETTLEMENT_CODE` varchar(32) DEFAULT NULL, 148 `SETTLEMENT_NAME` varchar(128) DEFAULT NULL, 149 `TOTAL_AMOUNT` decimal(18,2) DEFAULT NULL, 150 `PAY_METHOD` decimal(18,2) DEFAULT NULL, 151 `COST_AMOUNT` decimal(18,2) DEFAULT NULL, 152 `MIN_AMOUNT` decimal(18,2) DEFAULT NULL, 153 `BAIL` decimal(18,2) DEFAULT NULL, 154 `BAIL_EXPIRE_DATE` datetime DEFAULT NULL, 155 `CONSTRUCTOR_NAME` varchar(64) DEFAULT NULL, 156 `CREDIT_CUSTOMER_ID` decimal(10,2) DEFAULT NULL, 157 `VERSION` decimal(10,2) DEFAULT NULL, 158 `REMARK` varchar(1000) DEFAULT NULL, 159 `CREATED_BY` varchar(32) DEFAULT NULL, 160 `CREATED_DATE` datetime DEFAULT NULL, 161 `PRODUCT_AMOUNT` decimal(18,2) DEFAULT NULL, 162 `CARRIAGE_AMOUNT` decimal(18,2) DEFAULT NULL, 163 `OTHER_AMOUNT` decimal(18,2) DEFAULT NULL, 164 `GROSS_PROFIT` decimal(18,2) DEFAULT NULL, 165 `DELIVERY_ADDRESS` varchar(256) DEFAULT NULL, 166 `WFID` decimal(10,2) NOT NULL DEFAULT '0.00', 167 `WFFLAG` decimal(10,2) DEFAULT NULL, 168 `WFRIGHT` decimal(10,2) DEFAULT NULL, 169 `PROCID` decimal(10,2) DEFAULT NULL, 170 `STAT` decimal(10,2) DEFAULT NULL, 171 `CREDIT_CUSTOMER_CODE` varchar(32) DEFAULT NULL, 172 `CREDIT_CUSTOMER_NAME` varchar(128) DEFAULT NULL, 173 `PROJECT_CODE` varchar(32) DEFAULT NULL, 174 `PROJECT_NAME` varchar(128) DEFAULT NULL, 175 `SETTLEMENT_METHOD` decimal(10,2) DEFAULT NULL, 176 `DEPT_ID` decimal(10,2) DEFAULT NULL, 177 `BASE_CURRENCY_ID` decimal(10,2) NOT NULL DEFAULT '1.00', 178 `SUESYN` decimal(10,2) DEFAULT NULL, 179 `SREMARK` varchar(255) DEFAULT NULL, 180 `SINTIM` datetime DEFAULT NULL, 181 `SUPTIM` datetime(6) DEFAULT NULL, 182 PRIMARY KEY (`SEQID`) USING BTREE, 183 UNIQUE KEY `PK_PMS_CONTRACT_ID` (`PMS_CONTRACT_ID`) USING BTREE 184 ) ENGINE=InnoDB AUTO_INCREMENT=6574 DEFAULT CHARSET=utf8; 185 186 187 DROP VIEW IF EXISTS mysql_ddl_test_v41; 188 CREATE VIEW `mysql_ddl_test_v41` AS 189 select 'U' AS `TYPE`,`b`.`ORGANIZATION_ID` AS `ORGANIZATION_ID`, 190 `a`.`SA_CONTRACT_HEAD_ID` AS `CONTRACT`,`a`.`ITEM_ID` AS `ITEM_ID`,`a`.`BASE_LEVEL_ID` AS `BASE_LEVEL_ID`,`a`.`CONTRACT_PRICE` AS `CONTRACT_PRICE` 191 from ( 192 `mysql_ddl_test_t41` `a` 193 left join `mysql_ddl_test_t42` `b` 194 on((`a`.`SA_CONTRACT_HEAD_ID` = `b`.`SA_CONTRACT_HEAD_ID`))) 195 where (now() between `b`.`BEGINDATE` and `b`.`ENDDATE`) 196 union 197 select 'P' AS `TYPE`,'1111' AS `ORGANIZATION_ID`,`a`.`PMS_CONTRACT_ID` AS `CONTRACT`,`a`.`ITEM_ID` AS `ITEM_ID`,`a`.`BASE_LEVEL_ID` AS `BASE_LEVEL_ID`,`a`.`PRICE_SLICE` AS `CONTRACT_PRICE` 198 from ( 199 `mysql_ddl_test_t43` `a` 200 left join `mysql_ddl_test_t44` `b` 201 on((`a`.`PMS_CONTRACT_ID` = `b`.`PMS_CONTRACT_ID`))) 202 where (now() between `b`.`CONTRACT_EFFECTIVE_START` and `b`.`CONTRACT_EFFECTIVE_END`) ; 203 204 205 SHOW CREATE VIEW mysql_ddl_test_v41; 206 207 -- @bvt:issue#moc 1229 208 DROP VIEW IF EXISTS mysql_ddl_test_v42; 209 /*!50001 CREATE ALGORITHM=UNDEFINED */ 210 /*!50013 DEFINER=`root`@`%` SQL SECURITY DEFINER */ 211 /*!50001 VIEW mysql_ddl_test_v41 AS select `a`.`QUERYID` AS `QUERYID`,floor(`a`.`ORGANIZATION_ID`) AS `ORGANIZATION_ID`,floor(`a`.`WAREHOUSE_ID`) AS `WAREHOUSE_ID`,`d`.`WAREHOUSE_CODE` AS `WAREHOUSE_CODE`,`d`.`WAREHOUSE_NAME` AS `WAREHOUSE_NAME`,`d`.`WAREHOUSE_PID` AS `WAREHOUSE_PID`,`h`.`WAREHOUSE_NAME` AS `WAREHOUSE_PNAME`,floor(`a`.`ITEM_ID`) AS `ITEM_ID`,`b`.`ITEM_CLASS3` AS `ITEM_CLASS3`,`b`.`ITEM_CODE` AS `ITNBR`,`b`.`ITEM_NAME` AS `ITDSC`,`a`.`COLORNUMBER` AS `COLORNUMBER`,`a`.`BASE_LEVEL_ID` AS `BASE_LEVEL_ID`,`e`.`LEVELNAME` AS `LEVELNAME`,`a`.`WIDTH` AS `PRDWID`,`a`.`LENGTH` AS `PRDLNG`,floor(`a`.`BASE_PADDR_ID`) AS `BASE_PADDR_ID`,`f`.`BASE_PADDR` AS `BASE_PADDR`,floor(`a`.`ASSISTANT_UOM_ID`) AS `ASSISTANT_UOM_ID`,floor(`c`.`UOM_ID`) AS `UOM_ID`,`c`.`UOM_NAME` AS `UOM_NAME`,floor(`a`.`INV_BATCH_ID`) AS `INV_BATCH_ID`,`a`.`QTY_CANDRAFIT` AS `QTY_CANDRAFIT`,`a`.`QTY_SUBSISTENCE` AS `QTY_SUBSISTENCE`,`a`.`QTY_PRESHIP` AS `QTY_PRESHIP`,`a`.`QTY_FREEZE` AS `QTY_FREEZE`,`a`.`QTY_ONHAND` AS `QTY_ONHAND`,`a`.`QTY_ONHAND2` AS `QTY_ONHAND2`,`a`.`INV_ITEM_ID` AS `INV_ITEM_ID`,`a`.`IS_PALLET` AS `IS_PALLET`,`a`.`INV_BATCH_CODE` AS `INV_BATCH_CODE`,`a`.`CUSTOMER_ID` AS `CUSTOMER_ID`,`a`.`CUSTOMER_CODE` AS `CUSTOMER_CODE`,`a`.`CUSTOMER_NAME` AS `CUSTOMER_NAME`,(`a`.`QTY_CANDRAFIT` - ifnull(`g`.`LOCKQTY`,0)) AS `USEFULQTY`,ifnull(`g`.`LOCKQTY`,0) AS `LOCKQTY`,`a`.`KULINGDAY` AS `KULINGDAY` from (((((((`skim`.`ces0020m` `a` join `skim`.`kaf_item` `b`) join `skim`.`kaf_uom` `c`) join `skim`.`kaf_warehouse` `d`) join `skim`.`kaf_base_level` `e`) left join `skim`.`kaf_base_paddr` `f` on((`a`.`BASE_PADDR_ID` = `f`.`BASE_PADDR_ID`))) left join (select sum(`skim`.`ces0021`.`LOCKQTY`) AS `LOCKQTY`,`skim`.`ces0021`.`ITNBR` AS `itnbr`,`skim`.`ces0021`.`WAREHOUSE_ID` AS `WAREHOUSE_ID`,`skim`.`ces0021`.`UOMID` AS `UOMID`,`skim`.`ces0021`.`COLORNUMBER` AS `COLORNUMBER`,`skim`.`ces0021`.`BASE_LEVEL_ID` AS `base_level_id`,`skim`.`ces0021`.`LENGTH` AS `LENGTH`,`skim`.`ces0021`.`WIDTH` AS `WIDTH`,`skim`.`ces0021`.`IS_PALLET` AS `IS_PALLET` from `skim`.`ces0021` where ((`skim`.`ces0021`.`VALIDYN` = 'Y') and (`skim`.`ces0021`.`LOCKTYPE` = '1')) group by `skim`.`ces0021`.`ITNBR`,`skim`.`ces0021`.`WAREHOUSE_ID`,`skim`.`ces0021`.`UOMID`,`skim`.`ces0021`.`COLORNUMBER`,`skim`.`ces0021`.`BASE_LEVEL_ID`,`skim`.`ces0021`.`LENGTH`,`skim`.`ces0021`.`WIDTH`,`skim`.`ces0021`.`IS_PALLET`) `g` on(((`b`.`ITEM_CODE` = `g`.`itnbr`) and (`a`.`WAREHOUSE_ID` = `g`.`WAREHOUSE_ID`) and (`a`.`ASSISTANT_UOM_ID` = `g`.`UOMID`) and (`a`.`COLORNUMBER` = `g`.`COLORNUMBER`) and (`a`.`BASE_LEVEL_ID` = `g`.`base_level_id`) and (`a`.`LENGTH` = `g`.`LENGTH`) and (`a`.`WIDTH` = `g`.`WIDTH`) and (`a`.`IS_PALLET` = `g`.`IS_PALLET`)))) left join `skim`.`kaf_warehouse` `h` on(((`d`.`WAREHOUSE_PID` = `h`.`WAREHOUSE_ID`) and (`d`.`ORGANIZATION_ID` = `h`.`ORGANIZATION_ID`)))) where ((`a`.`ITEM_ID` = `b`.`ITEM_ID`) and (`a`.`ASSISTANT_UOM_ID` = `c`.`UOM_ID`) and (`a`.`WAREHOUSE_ID` = `d`.`WAREHOUSE_ID`) and (`a`.`BASE_LEVEL_ID` = `e`.`BASE_LEVEL_ID`) and (`d`.`ACCOUNT_FLAG` = '1')) */; 212 SHOW CREATE VIEW mysql_ddl_test_v42; 213 -- @bvt:issue 214 215 drop database if exists mysql_ddl_test_db_4;