yuga/query_orderby_sql-99.bnf.md

## query_orderby_sql-99.bnf.md

      
    Raw
  

              query_orderby_sql-99.bnf.md
            
          
    UNIONとORDER BYの組み合わせ

relational-record-examples の sql/6.4.1a.sh に対応するHaskellコードの説明を書いていて、何だか嘘を書いている気分になったので調査してみた。ちなみに書いていた説明は以下のようなもの。英語は雰囲気です。
-- | sql/6.4.1a.sh
--
-- MySQL allows the syntax of UNION that has an order clause at the
-- last of query. SQLite does not.
--
-- Unfortunately, HRR cannot detect as type error this kind of conflict in
-- SQL syntax that each RDBMS supports. You must be careful when writing
-- codes for such ambiguous rule.
--
-- In addition, HRR put a select statement having an order clause into
-- parentheses. Generated SQL has different meaning with the handwritten
-- SQL below. Such query cannot be expressed directly with EDSL of HRR.
--
-- Handwritten SQL:
--
-- @
--   SELECT emp_id, assigned_branch_id
--   FROM LEARNINGSQL.employee
--   WHERE title = 'Teller'
--   UNION
--   SELECT open_emp_id, open_branch_id
--   FROM LEARNINGSQL.account
--   WHERE product_cd = 'SAV'
--   ORDER BY open_emp_id
-- @
--
-- Generated SQL:
--
-- @
--   SELECT ALL T0.emp_id AS f0, T0.assigned_branch_id AS f1 FROM
--   MAIN.employee T0 WHERE (T0.title = 'Teller') UNION SELECT ALL
--   T1.open_emp_id AS f0, T1.open_branch_id AS f1 FROM MAIN.account T1
--   WHERE (T1.product_cd = 'SAV')
-- @
--
union_6_4_1a_Flat :: Relation () (Maybe Int64, Maybe Int64)
union_6_4_1a_Flat = relation (do
    e  <- query employee
    wheres $ e ! Employee.title' .=. just (value "Teller")
    return $ just (e ! Employee.empId') >< e ! Employee.assignedBranchId'
  ) `union` relation (do
    a  <- query account
    wheres $ a ! Account.productCd' .=. value "SAV"
    -- asc $ a ! Account.openEmpId'
    return $ a ! Account.openEmpId' >< a ! Account.openBranchId'
  )

-- |
-- If you want your query to be portable, place a order clouse outside of
-- the union relation.
--
-- Generated SQL:
--
-- @
--   SELECT ALL T2.f0 AS f0, T2.f1 AS f1 FROM (SELECT ALL T0.emp_id AS f0,
--   T0.assigned_branch_id AS f1 FROM MAIN.employee T0 WHERE (T0.title
--   = 'Teller') UNION SELECT ALL T1.open_emp_id AS f0, T1.open_branch_id
--   AS f1 FROM MAIN.account T1 WHERE (T1.product_cd = 'SAV')) T2 ORDER BY
--   T2.f0 ASC
-- @
--
union_6_4_1a_Nest :: Relation () (Maybe Int64, Maybe Int64)
union_6_4_1a_Nest = relation $ do
  ea <- query $ employee_6_4_1a `union` account_6_4_1a
  asc $ ea ! fst'
  return ea

employee_6_4_1a :: Relation () (Maybe Int64, Maybe Int64)
employee_6_4_1a = relation $ do
  e  <- query employee
  wheres $ e ! Employee.title' .=. just (value "Teller")
  return $ just (e ! Employee.empId') >< e ! Employee.assignedBranchId'

account_6_4_1a :: Relation () (Maybe Int64, Maybe Int64)
account_6_4_1a = relation $ do
  a  <- query account
  wheres $ a ! Account.productCd' .=. value "SAV"
  return $ a ! Account.openEmpId' >< a ! Account.openBranchId'
最初に

SQLite does not.

とか書いているけどいきなり間違っている。以下の実行例のように問題なく動く。
sqlite> SELECT ALL T0.emp_id AS f0, T0.assigned_branch_id AS f1 FROM MAIN.employee T0 WHERE (T0.title = 'Teller') UNION SELECT ALL T1.open_emp_id AS f0, T1.open_branch_id AS f1 FROM MAIN.account T1 WHERE (T1.product_cd = 'SAV') ORDER BY T1.open_emp_id ASC;
1|1
7|1
8|1
9|1
10|2
11|2
12|2
14|3
15|3
16|4
17|4
18|4
自分としては、本から抽出したSQLはMySQL専用で他のRDBMSではダメなのだという立脚点で説明を書こうとしていたのだけれど、実際にはSQLiteでもSQL Serverでも問題がない。
そこで標準SQLのBNFをWebから探して追いかけてみた。すると本にあるSQLも標準SQLとしても"あり"と思われる（以下に再掲）。
SELECT emp_id, assigned_branch_id
FROM LEARNINGSQL.employee
WHERE title = 'Teller'
UNION
SELECT open_emp_id, open_branch_id
FROM LEARNINGSQL.account
WHERE product_cd = 'SAV'  -- ここまで <query expression>
ORDER BY open_emp_id      -- ここは <order by clause>
BNFからではORDER BY句で指定可能な名前に曖昧さが残るが、上記の場合、以下と異なり結果全体にソートの影響があるはず。
SELECT emp_id, assigned_branch_id
FROM LEARNINGSQL.employee
WHERE title = 'Teller'
UNION
(SELECT open_emp_id, open_branch_id
 FROM LEARNINGSQL.account
 WHERE product_cd = 'SAV'
 ORDER BY open_emp_id)
なぜこういう考え違いをしたかというと、現状HRRでは１つめのSQLそのままの形のEDSL表現はできないからだ。ascなりdescをつけると２つめのSQLになる（ちなみにこれはSQLiteが受け付けない）。1つめと同じ結果を得たい場合は、haskellコードのコメントに記述したようにネストしたEDSL表現にしなければならない。
結論としては、HRRの制限なので、そういう風に説明しないといけないなと思ったところで、休日が終了です。
SQL99 BNF 抜粋

http://savage.net.au/SQL/sql-99.bnf.html
<direct SQL statement>    ::=
         <directly executable statement> <semicolon>
<directly executable statement>    ::= 
         <direct SQL data statement>
     |   <SQL schema statement>
     |   <SQL transaction statement>
     |   <SQL connection statement>
     |   <SQL session statement>
     |   <direct implementation-defined statement>
<direct SQL data statement>    ::= 
         <delete statement: searched>
     |   <direct select statement: multiple rows>
     |   <insert statement>
     |   <update statement: searched>
     |   <temporary table declaration>     
<direct select statement: multiple rows>    ::=
         <query expression> [ <order by clause> ]

<query expression>    ::=   [ <with clause> ] <query expression body>
<query expression body>    ::=   <non-join query expression> | <joined table>
<non-join query expression>    ::= 
         <non-join query term>
     |   <query expression body> UNION [ ALL | DISTINCT ] [ <corresponding spec> ] <query term>
     |   <query expression body> EXCEPT [ ALL | DISTINCT ] [ <corresponding spec> ] <query term>
<non-join query term>    ::= 
         <non-join query primary>
     |   <query term> INTERSECT [ ALL | DISTINCT ] [ <corresponding spec> ] <query primary>
<non-join query primary>    ::= 
         <simple table>
     |   <left paren> <non-join query expression> <right paren>
<simple table>    ::=
         <query specification>
     |   <table value constructor>
     |   <explicit table>
<query specification>    ::=
         SELECT [ <set quantifier> ] <select list> <table expression>
<table expression>    ::=
         <from clause> [ <where clause> ] [ <group by clause> ] [ <having clause> ]
<query term>    ::=   <non-join query term> | <joined table>

<order by clause>    ::=   ORDER BY <sort specification list>
<sort specification list>    ::=   <sort specification> [ { <comma> <sort specification> }... ]
<sort specification> modified per ISO 9075:1999/Cor.1:2000(E)
<sort specification>    ::=   <sort key> [ <ordering specification> ]
<sort key>    ::=   <value expression>
<ordering specification>    ::=   ASC | DESC