yamamoto-yuta/sql-cte-visualizer-js

SQL の CTE を可視化するツール

こちらの記事 の Python 実装を JavaScript で実装しなおしたもの。

→ ツールのページ（GitHub Pages）

詳細は こちらの記事 にまとめている。

sql-cte-visualizer-js's Issues

BigQuery で SELECT 句で IF(SPLIT()) に使用していた場合に parse が失敗する

一例:

SQL

WITH

  sample_table AS (
    SELECT "A#B#C" AS col1, "C#B#A" AS col2 UNION ALL
    SELECT "D#E#F" AS col1, "F#E#D" AS col2
  )

SELECT
  SPLIT(col1, "#")[SAFE_OFFSET(0)],
  IF(SPLIT(col1, "#")[SAFE_OFFSET(0)] = "A", true, false) -- ここの parser でエラーが出る
FROM
  sample_table

node-sql-parser のエラーメッセージ:

Expected "!=", "#", "%", "&&", ")", "*", "+", ",", "-", "--", "/", "/*", "<", "<=", "<>", "=", ">", ">=", "AND", "BETWEEN", "IN", "IS", "LIKE", "NOT", "ON", "OR", "OVER", "||", or [ \t\n\r] but "[" found. at {"start":{"offset":193,"line":10,"column":22},"end":{"offset":194,"line":10,"column":23}}

三重かっこがあると正しいCTEにならない

再現クエリ:

WITH

  cte1 AS (
    SELECT * FROM source1
  ),

  cte2 AS (
    SELECT * FROM source1
  ),

  cte3 AS (
    SELECT *, hoge(hoge(hoge())) FROM cte2
  )

SELECT * FROM cte3 JOIN cte1

SQL 環境を切り替えられるようにする

ここを可変にする。

sql-cte-visualizer-js/parseSQL.js

Line 5 in dd8de31

{ database: 'bigquery' }

BigQuery で FROM 句で UNNEST(SPLIT()) を使用した際に、無名のノードで可視化されてしまう

一例:

SQL:

WITH

  sample_table AS (
    SELECT "A#B#C" AS col1, "C#B#A" AS col2 UNION ALL
    SELECT "D#E#F" AS col1, "F#E#D" AS col2
  )

SELECT
  offset,
  col1,
  SPLIT(col2, "#")[SAFE_OFFSET(0)] AS col2,
FROM
  sample_table, UNNEST(SPLIT(col1, "#")) AS col1 WITH OFFSET AS offset

SVG: