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