COALESCE

7 files changed, 58 insertions, 1 deletions

diff --git a/doc/manual.tex b/doc/manual.tex
index 84b300e7..03d29701 100644
--- a/doc/manual.tex
+++ b/doc/manual.tex
@@ -1709,6 +1709,15 @@ $$\begin{array}{l}
   \hspace{.1in} \to \mt{sql\_exp} \; \mt{tables} \; \mt{agg} \; \mt{exps} \; (\mt{option} \; \mt{t}) \to \mt{sql\_exp} \; \mt{tables} \; \mt{agg} \; \mt{exps} \; \mt{bool}
 \end{array}$$
 
+As another way of dealing with null values, there is also a restricted form of the standard \cd{COALESCE} function.
+$$\begin{array}{l}
+  \mt{val} \; \mt{sql\_coalesce} : \mt{tables} ::: \{\{\mt{Type}\}\} \to \mt{agg} ::: \{\{\mt{Type}\}\} \to \mt{exps} ::: \{\mt{Type}\} \\
+  \hspace{.1in} \to \mt{t} ::: \mt{Type} \\
+  \hspace{.1in} \to \mt{sql\_exp} \; \mt{tables} \; \mt{agg} \; \mt{exps} \; (\mt{option} \; \mt{t}) \\
+  \hspace{.1in} \to \mt{sql\_exp} \; \mt{tables} \; \mt{agg} \; \mt{exps} \; \mt{t} \\
+  \hspace{.1in} \to \mt{sql\_exp} \; \mt{tables} \; \mt{agg} \; \mt{exps} \; \mt{t}
+\end{array}$$
+
 We have generic nullary, unary, and binary operators.
 $$\begin{array}{l}
   \mt{con} \; \mt{sql\_nfunc} :: \mt{Type} \to \mt{Type} \\
@@ -2140,6 +2149,7 @@ $$\begin{array}{rrcll}
   &&& \ell & \textrm{primitive type literals} \\
   &&& \mt{NULL} & \textrm{null value (injection of $\mt{None}$)} \\
   &&& E \; \mt{IS} \; \mt{NULL} & \textrm{nullness test} \\
+  &&& \mt{COALESCE}(E, E) & \textrm{take first non-null value} \\
   &&& n & \textrm{nullary operators} \\
   &&& u \; E & \textrm{unary operators} \\
   &&& E \; b \; E & \textrm{binary operators} \\
diff --git a/lib/ur/basis.urs b/lib/ur/basis.urs
index 73ee8e2b..f21faf38 100644
--- a/lib/ur/basis.urs
+++ b/lib/ur/basis.urs
@@ -474,6 +474,12 @@ val sql_is_null : tables ::: {{Type}} -> agg ::: {{Type}} -> exps ::: {Type}
                   -> sql_exp tables agg exps (option t)
                   -> sql_exp tables agg exps bool
 
+val sql_coalesce : tables ::: {{Type}} -> agg ::: {{Type}} -> exps ::: {Type}
+                  -> t ::: Type
+                  -> sql_exp tables agg exps (option t)
+                  -> sql_exp tables agg exps t
+                  -> sql_exp tables agg exps t
+
 val sql_if_then_else : tables ::: {{Type}} -> agg ::: {{Type}} -> exps ::: {Type}
                        -> t ::: Type
                        -> sql_exp tables agg exps bool
diff --git a/src/monoize.sml b/src/monoize.sml
index e570b4cb..d18b4d2a 100644
--- a/src/monoize.sml
+++ b/src/monoize.sml
@@ -2808,6 +2808,28 @@ fun monoExp (env, st, fm) (all as (e, loc)) =
              (L.ECApp (
               (L.ECApp (
                (L.ECApp (
+                (L.EFfi ("Basis", "sql_coalesce"), _), _),
+                _), _),
+               _), _),
+              _), _)) =>
+            let
+                val s = (L'.TFfi ("Basis", "string"), loc)
+                fun sc s = (L'.EPrim (Prim.String s), loc)
+            in
+                ((L'.EAbs ("x1", s, (L'.TFun (s, s), loc),
+                           (L'.EAbs ("x1", s, s,
+                                     strcat [sc "COALESCE(",
+                                             (L'.ERel 1, loc),
+                                             sc ",",
+                                             (L'.ERel 0, loc),
+                                             sc ")"]), loc)), loc),
+                 fm)
+            end
+
+          | (L.ECApp (
+             (L.ECApp (
+              (L.ECApp (
+               (L.ECApp (
                 (L.EFfi ("Basis", "sql_if_then_else"), _), _),
                 _), _),
                _), _),
diff --git a/src/urweb.grm b/src/urweb.grm
index db8b6294..8e3fad90 100644
--- a/src/urweb.grm
+++ b/src/urweb.grm
@@ -244,7 +244,7 @@ fun tnamesOf (e, _) =
  | TRUE | FALSE | CAND | OR | NOT
  | COUNT | AVG | SUM | MIN | MAX
  | ASC | DESC
- | INSERT | INTO | VALUES | UPDATE | SET | DELETE | NULL | IS
+ | INSERT | INTO | VALUES | UPDATE | SET | DELETE | NULL | IS | COALESCE
  | CURRENT_TIMESTAMP
  | NE | LT | LE | GT | GE
  | CCONSTRAINT | UNIQUE | CHECK | PRIMARY | FOREIGN | KEY | ON | NO | ACTION | RESTRICT | CASCADE | REFERENCES
@@ -1881,6 +1881,14 @@ sqlexp : TRUE                           (sql_inject (EVar (["Basis"], "True", In
                                          in
                                              (EApp (e, sqlexp), loc)
                                          end)
+       | COALESCE LPAREN sqlexp COMMA sqlexp RPAREN
+                                        (let
+                                             val loc = s (COALESCEright, sqlexp2right)
+                                             val e = (EVar (["Basis"], "sql_coalesce", Infer), loc)
+                                             val e = (EApp (e, sqlexp1), loc)
+                                         in
+                                             (EApp (e, sqlexp2), loc)
+                                         end)
        | fname LPAREN sqlexp RPAREN     (let
                                              val loc = s (fnameleft, RPARENright)
 
diff --git a/src/urweb.lex b/src/urweb.lex
index a989d933..8e8b0a12 100644
--- a/src/urweb.lex
+++ b/src/urweb.lex
@@ -499,6 +499,7 @@ xint = x[0-9a-fA-F][0-9a-fA-F];
 <INITIAL> "DELETE"    => (Tokens.DELETE (pos yypos, pos yypos + size yytext));
 <INITIAL> "NULL"      => (Tokens.NULL (pos yypos, pos yypos + size yytext));
 <INITIAL> "IS"        => (Tokens.IS (pos yypos, pos yypos + size yytext));
+<INITIAL> "COALESCE"  => (Tokens.COALESCE (pos yypos, pos yypos + size yytext));
 
 <INITIAL> "CONSTRAINT"=> (Tokens.CCONSTRAINT (pos yypos, pos yypos + size yytext));
 <INITIAL> "UNIQUE"    => (Tokens.UNIQUE (pos yypos, pos yypos + size yytext));
diff --git a/tests/coalesce.ur b/tests/coalesce.ur
new file mode 100644
index 00000000..5ee8cf19
--- /dev/null
+++ b/tests/coalesce.ur
@@ -0,0 +1,6 @@
+table t : { A : option int }
+
+fun main () : transaction page =
+    queryX (SELECT COALESCE(t.A, 13)
+            FROM t)
+    (fn r => <xml>{[r.1]},</xml>)
diff --git a/tests/coalesce.urp b/tests/coalesce.urp
new file mode 100644
index 00000000..7d7decee
--- /dev/null
+++ b/tests/coalesce.urp
@@ -0,0 +1,4 @@
+database dbname=test
+sql coalesce.sql
+
+coalesce