SQL Analysis relation's nodeLocation.line appears wrong number

[andreashimin]

I found the relation's nodeLocation.line mostly appears the wrong line number

Reference

According to the SQL string[] on the left side, the nodeLocation.line should be (index + 1) = 6 instead.

SQL for Analysis

SELECT
  "c"."City" AS "_City",
  "o"."OrderId" AS "_OrderId"
FROM
  "customers" AS "c"
  JOIN "orders" AS "o" ON "c"."Id" = "o"."CustomerId"
WHERE
  "o"."Status" = 'completed'

JsonString

"SELECT\n  \"c\".\"City\" AS \"_City\",\n  \"o\".\"OrderId\" AS \"_OrderId\"\nFROM\n  \"customers\" AS \"c\"\n  JOIN \"orders\" AS \"o\" ON \"c\".\"Id\" = \"o\".\"CustomerId\"\nWHERE\n  \"o\".\"Status\" = 'completed'"

Response

response.json

[goldmedal]

Hi @andreashimin. It makes sense to me.

  "relation": {
      "type": "INNER_JOIN",
      "left": {
          "type": "TABLE",
          "alias": "c",
          "tableName": "customers",
          "nodeLocation": {
              "line": 5,
              "column": 3
          }
      },
      "right": {
          "type": "TABLE",
          "alias": "o",
          "tableName": "orders",
          "nodeLocation": {
              "line": 6,
              "column": 8
          }
      },
      "criteria": "ON (\"c\".\"Id\" = \"o\".\"CustomerId\")",
      "exprSources": [
          {
              "expression": "o.CustomerId",
              "sourceDataset": "orders",
              "nodeLocation": {
                  "line": 6,
                  "column": 38
              }
          },
          {
              "expression": "c.Id",
              "sourceDataset": "customers",
              "nodeLocation": {
                  "line": 6,
                  "column": 27
              }
          }
      ],
      "nodeLocation": {
          "line": 5,
          "column": 3
      }
  },

For SQL structure, the first layer of INNER_JOIN means

  "customers" AS "c"
  JOIN "orders" AS "o" ON "c"."Id" = "o"."CustomerId"

So, the node location of this join is line 5, col 3. If you intend to get the location of the right table, you should use

  "right": {
      "type": "TABLE",
      "alias": "o",
      "tableName": "orders",
      "nodeLocation": {
          "line": 6,
          "column": 8
      }
  },

[andreashimin]

Hi @goldmedal,

hmm...I see, but since the AI service will return explained snippets from criteria,
UI needs to highlight ON (\"c\".\"Id\" = \"o\".\"CustomerId\") in this case.

Is it also correct if we get the nodeLocation from exprSources? 🤔
Will it have a problem if analysis with complex SQL?

[goldmedal]

hmm...I see, but since the AI service will return explained snippets from criteria, UI needs to highlight ON (\"c\".\"Id\" = \"o\".\"CustomerId\") in this case.

So, I think what you need is the location of criteria and not the location of INNER_JOIN, right?
Maybe we can provide this if you needed. I guess the format could be

{
   "criteria": {
          "expr": "ON (\"c\".\"Id\" = \"o\".\"CustomerId\")",
          "nodeLocation": {
                "line": 6,
                "column": 8
          }
   }
}

Is it also correct if we get the nodeLocation from exprSources? 🤔 Will it have a problem if analysis with complex SQL?

If you get the locations of exprSources, I believe they will point to expressions like \"c\".\"Id\" and \"o\".\"CustomerId\". This should be the general behavior regardless of the complexity of the SQL.

[andreashimin]

So, I think what you need is the location of criteria and not the location of INNER_JOIN, right? Maybe we can provide this if you needed. I guess the format could be

{
   "criteria": {
          "expr": "ON (\"c\".\"Id\" = \"o\".\"CustomerId\")",
          "nodeLocation": {
                "line": 6,
                "column": 8
          }
   }
}

Yeah, that would be appreciated if I could obtain the criteria's nodeLocation directly.