soar

soar copied to clipboard

Please search the existing issues for relevant feature requests, add upvotes to pre-existing requests.

Feature Description

When I use JSON syntax or not correct SQL content, the result is only "OK", suggest that tips like "Syntax is not support now" or "SQL syntax is not correct", not only "OK"

Use Case(s)

SQL content is : 1.JSON Syntax

SELECT
  tb_trade_full_info  -> '$.orders[0]' AS  orderInfo0,
  tb_trade_full_info  -> '$.orders[1]' AS  orderInfo1
FROM
  taobao_trade_info
WHERE
  trade_id  = "240812994726235655"

And the result is "OK" 2. Syntax is not correct

WHERE
  trade_id  = "240812994726235655"

And the result is "OK" too

@helloworlde please also give table create info.

• table create sql

CREATE TABLE taobao_trade_info (
  id                 INT(11) NOT NULL AUTO_INCREMENT,
  trade_id           VARCHAR(100) DEFAULT NULL COMMENT '淘宝交易号',
  tb_trade_full_info JSON         DEFAULT NULL COMMENT '订单详情json',
  order_info_id      INT(11)      DEFAULT NULL COMMENT '订单id',
  status             INT(3)       DEFAULT '0' COMMENT '订单有效状态 1有效 0无效',
  add_time           DATETIME     DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_trade_id(trade_id),
  KEY idx_order_info_id(order_info_id)
);

• One of data

INSERT INTO cc_95vintage.taobao_trade_info (id, trade_id, tb_trade_full_info, order_info_id, status, add_time)
VALUES (2827, '240812994726235655',
        '{"tid": 240812994726235655, "type": "fixed", "title": "title", "orders": [{"cid": 50010850, "num": 1, "oid": 240812994727235655, "price": "1139.00", "skuId": "3924553507693", "title": "2018新款Pom&Co浅蓝侧开叉改良旗袍礼服8-9新", "numIid": 574480111504, "oidStr": "240812994727235655", "status": "WAIT_SELLER_SEND_GOODS", "payment": "599.00", "picPath": "https://img.alicdn.com/bao/uploaded/i2/3599678600/0.jpg", "outerIid": "20875B", "totalFee": "599.00", "adjustFee": "0.00", "buyerRate": false, "isDaixiao": false, "orderFrom": "WAP,WAP", "isOversold": false, "nrOuterIid": "20875B", "outerSkuId": "48582", "sellerRate": false, "sellerType": "C", "discountFee": "540.00", "snapshotUrl": "n:240812994727235655_1", "refundStatus": "NO_REFUND", "divideOrderFee": "599.00", "skuPropertiesName": "颜色分类:蓝色;尺码:M"}, {"cid": 1623, "num": 1, "oid": 240812994728235655, "price": "598.00", "skuId": "3927291454130", "title": "2018新款JEMO DRESSING弹力乱麻半身裙 咖红色8-9新", "numIid": 574713147948, "oidStr": "240812994728235655", "status": "WAIT_SELLER_SEND_GOODS", "payment": "99.00", "picPath": "https://img.alicdn.com/bao/uploaded/i2/3599678600/0.jpg", "outerIid": "11444B", "totalFee": "99.00", "adjustFee": "0.00", "buyerRate": false, "isDaixiao": false, "orderFrom": "WAP,WAP", "isOversold": false, "nrOuterIid": "11444B", "outerSkuId": "23319", "sellerRate": false, "sellerType": "C", "discountFee": "499.00", "snapshotUrl": "n:240812994728235655_1", "refundStatus": "NO_REFUND", "divideOrderFee": "99.00", "skuPropertiesName": "颜色分类:褐色;尺码:L"}], "status": "WAIT_SELLER_SEND_GOODS", "tidStr": "240812994726235655", "created": 1539498295000, "payTime": 1539498300000, "payment": "698.00", "postFee": "0.00", "isShShip": false, "modified": 1539498301000, "totalFee": "1737.00", "adjustFee": "0.00", "buyerNick": "buyerNick", "buyerRate": false, "couponFee": 0, "tradeFrom": "WAP,WAP", "sellerFlag": 0, "sellerNick": "sellerNick", "sellerRate": false, "buyerCodFee": "0.00", "discountFee": "0.00", "receiverZip": "311200", "serviceTags": [{"orderId": "240812994726235655", "logisticServiceTagList": [{"serviceTag": "origAreaId=330109;consignDate=48", "serviceType": "TB_CONSIGN_DATE"}, {"serviceTag": "lgType=-4", "serviceType": "FAST"}]}, {"orderId": "240812994727235655", "logisticServiceTagList": [{"serviceTag": "consignDate=48", "serviceType": "TB_CONSIGN_DATE"}]}, {"orderId": "240812994728235655", "logisticServiceTagList": [{"serviceTag": "consignDate=48", "serviceType": "TB_CONSIGN_DATE"}]}], "serviceType": "", "buyerOpenUid": "buyerOpenUid", "receiverCity": "receiverCity", "receiverName": "receiverName", "receiverTown": "receiverTown", "shippingType": "express", "creditCardFee": "698.00", "nrShopGuideId": "", "receiverState": "receiverState", "receiverMobile": "receiverMobile", "nrShopGuideName": "", "receivedPayment": "0.00", "receiverAddress": "receiverAddress", "receiverCountry": "", "promotionDetails": [{"id": 240812994728235655, "discountFee": "499.00", "promotionId": "PKSNmultidz1350894312-6645170708_42820099715", "promotionDesc": "心动价:省499.00元", "promotionName": "心动价"}, {"id": 240812994727235655, "discountFee": "540.00", "promotionId": "PKSNmultidz1350894312-6645170708_42448245898", "promotionDesc": "心动价:省540.00元", "promotionName": "心动价"}], "receiverDistrict": "receiverDistrict", "platformSubsidyFee": "0.00"}',
        3661122, 1, '2018-10-14 14:25:01');

• My query sql

SELECT tb_trade_full_info -> '$.orders[0]' AS orderInfo0,
  tb_trade_full_info -> '$.orders[1]' AS orderInfo1
FROM taobao_trade_info
WHERE trade_id = '240812994726235655';

MySQL version is 5.7

We will add a new heuristic rule, explain SOAR will not give suggestion about JSON data type query.

This heuristic rule should be Level 0.