1. Kirill Simonov
  2. htsql-charset-option

Commits

Kirill Simonov  committed b850dc1

Added `distinct()` function.

`distinct(table{kernel})` is equivalent to `(table^kernel)`.

  • Participants
  • Parent commits c3f8354
  • Branches default

Comments (0)

Files changed (9)

File src/htsql/tr/bind.py

View file
     def __call__(self):
         base = self.state.bind(self.syntax.branch)
         elements = []
-        recipies = expand(base, is_hard=True)
+        recipies = expand(base)
         if recipies is None:
             elements.append(base)
             base = None
         elements = []
         for rbranch in self.syntax.rbranches:
             element = self.state.bind(rbranch)
-            recipies = expand(element)
+            recipies = expand(element, is_hard=False)
             if recipies is not None:
                 for syntax, recipe in recipies:
                     if not isinstance(syntax, (IdentifierSyntax, GroupSyntax)):
 
     def __call__(self):
         # Get all public descendants in the current lookup context.
-        recipies = expand(self.state.base, is_hard=True)
+        recipies = expand(self.state.base)
         if recipies is None:
             raise BindError("unable to resolve a wildcard",
                             self.syntax.mark)

File src/htsql/tr/fn/bind.py

View file
                 elif len(value) == 1:
                     [value] = value
                     bound_value = self.state.bind(value)
-                    recipies = expand(bound_value)
+                    recipies = expand(bound_value, is_hard=False)
                     if slot.is_mandatory and (recipies is not None and
                                               not recipies):
                         raise BindError("missing argument %s" % name,
         self.state.push_base(seed_binding)
         for expression in kernel:
             expression = self.state.bind(expression)
-            recipies = expand(expression)
+            recipies = expand(expression, is_hard=False)
             if recipies is not None:
                 for syntax, recipe in recipies:
                     bind = BindByRecipe(recipe, syntax, self.state)
     named('^')
 
 
+class BindDistinct(BindMacro):
+
+    named('distinct')
+    signature = UnarySig
+
+    def expand(self, op):
+        seed = self.state.bind(op)
+        recipies = expand(seed, is_hard=False)
+        if recipies is None:
+            raise BindError("a selector is required", op.mark)
+        kernel = []
+        for syntax, recipe in recipies:
+            bind = BindByRecipe(recipe, syntax, self.state)
+            kernel.append(bind())
+        return QuotientBinding(self.state.base, seed, kernel, self.syntax)
+
+
 class BindKernel(BindMacro):
 
     named('kernel')
                 index = int(index.value)
             except ValueError:
                 raise BindError("expected an integer value", index.mark)
-        recipies = expand(self.state.base, is_hard=True)
+        recipies = expand(self.state.base)
         if recipies is None:
             raise BindError("expected a quotient context", self.syntax.mark)
         if index is not None:
         elements = []
         for op in ops:
             element = self.state.bind(op)
-            recipies = expand(element)
+            recipies = expand(element, is_hard=False)
             if recipies is not None:
                 for syntax, recipe in recipies:
                     if not isinstance(syntax, (IdentifierSyntax, GroupSyntax)):
         bindings = []
         for item in order:
             binding = self.state.bind(item)
-            recipies = expand(binding)
+            recipies = expand(binding, is_hard=False)
             if recipies is None:
                 domain = coerce(binding.domain)
                 if domain is None:
     polarity = None
 
     def correlate(self, op):
-        recipies = expand(op)
+        recipies = expand(op, is_hard=False)
         plural_base = None
         if recipies is not None:
             if len(recipies) != 1:
     hint = """base.count(p) -> the number of p such that p = TRUE"""
 
     def correlate(self, op):
-        recipies = expand(op)
+        recipies = expand(op, is_hard=False)
         plural_base = None
         if recipies is not None:
             if len(recipies) != 1:
     codomain = UntypedDomain()
 
     def correlate(self, op):
-        recipies = expand(op)
+        recipies = expand(op, is_hard=False)
         plural_base = None
         if recipies is not None:
             if len(recipies) != 1:
     polarity = None
 
     def correlate(self, op):
-        recipies = expand(op)
+        recipies = expand(op, is_hard=False)
         plural_base = None
         if recipies is not None:
             if len(recipies) != 1:

File src/htsql/tr/lookup.py

View file
 """
 
 
+from ..util import Clonable
 from ..mark import EmptyMark
 from ..adapter import Adapter, adapts, adapts_many
 from ..context import context
     return name
 
 
-class Probe(object):
-    pass
+class Probe(Clonable):
+
+    def __init__(self):
+        pass
 
 
 class AttributeProbe(Probe):
 
 class ExpansionProbe(Probe):
 
-    def __init__(self, is_hard=False):
+    def __init__(self, is_soft=True, is_hard=True):
+        self.is_soft = is_soft
         self.is_hard = is_hard
 
 
     adapts(SelectionBinding, ExpansionProbe)
 
     def __call__(self):
+        if not self.probe.is_soft:
+            return lookup(self.binding.base, self.probe)
+        return self.itemize()
+
+    def itemize(self):
         for element in self.binding.elements:
             syntax = element.syntax
             recipe = BindingRecipe(element)
         return lookup(self.binding.seed, self.probe)
 
 
+class ExpandComplement(Lookup):
+
+    adapts(ComplementBinding, ExpansionProbe)
+
+    def __call__(self):
+        if not self.probe.is_hard:
+            return None
+        probe = self.probe.clone(is_soft=False)
+        return lookup(self.binding.seed, probe)
+
+
 class LookupAttributeInAlias(Lookup):
 
     adapts(AliasBinding, AttributeProbe)
     return lookup(binding, probe)
 
 
-def expand(binding, is_hard=False):
-    probe = ExpansionProbe(is_hard=is_hard)
+def expand(binding, is_soft=True, is_hard=True):
+    probe = ExpansionProbe(is_soft=is_soft, is_hard=is_hard)
     bindings = lookup(binding, probe)
     if bindings is not None:
         bindings = list(bindings)

File test/regress/input/translation.yaml

View file
     - uri: /(program^degree){*,count(^),count(^.student)}
     - uri: /(student^{year(dob),gender}){*,count(^),count(^?is_active)}
     - uri: /count(program^degree)
+
+    # The `distinct()` function.
+    - uri: /distinct(program{degree}){*,count(^),count(^.student)}
+    - uri: /distinct(program{degree}?school={'art','la'}){*,count(^),count(^.student)}
+    - uri: /distinct(program{degree}){*,count(^),count(^.student)}?kernel(0)
+    - uri: /(school?code={'art','la'}).distinct(program{degree}){*,count(^),count(^.student)}
+    - uri: /distinct(program{degree}?school={'art','la'}).^
+
+    # Known regressions.
     - uri: /(school^{})
       skip: true
     - uri: /(school^{null()})

File test/regress/output/mssql.yaml

View file
                FROM (SELECT 1 AS [^]
                      FROM [ad].[program] AS [program]
                      GROUP BY [program].[degree]) AS [program]
+          - uri: /distinct(program{degree}){*,count(^),count(^.student)}
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            body: |2
+               | distinct(program{degree})            |
+              -+--------------------------------------+-
+               | degree | count(^) | count(^.student) |
+              -+--------+----------+------------------+-
+               |        |        1 |                0 |
+               | ba     |        9 |                6 |
+               | bs     |       12 |               20 |
+               | ct     |        4 |                6 |
+               | ma     |        6 |                5 |
+               | ms     |        5 |                9 |
+               | pb     |        1 |                5 |
+               | ph     |        1 |                0 |
+                                               (8 rows)
+
+               ----
+               /distinct(program{degree}){*,count(^),count(^.student)}
+               SELECT [program_1].[degree],
+                      COALESCE([program_2].[count], 0),
+                      COALESCE([student].[count], 0)
+               FROM (SELECT [program].[degree]
+                     FROM [ad].[program] AS [program]
+                     GROUP BY [program].[degree]) AS [program_1]
+                    LEFT OUTER JOIN (SELECT COUNT(1) AS [count],
+                                            [program].[degree]
+                                     FROM [ad].[program] AS [program]
+                                     GROUP BY [program].[degree]) AS [program_2]
+                                    ON ((CASE WHEN ([program_1].[degree] = [program_2].[degree]) OR ([program_1].[degree] IS NULL AND [program_2].[degree] IS NULL) THEN 1 ELSE 0 END) <> 0)
+                    LEFT OUTER JOIN (SELECT COUNT(1) AS [count],
+                                            [program].[degree]
+                                     FROM [ad].[program] AS [program]
+                                          INNER JOIN [ed].[student] AS [student]
+                                                     ON (([program].[school] = [student].[school]) AND ([program].[code] = [student].[program]))
+                                     GROUP BY [program].[degree]) AS [student]
+                                    ON ((CASE WHEN ([program_1].[degree] = [student].[degree]) OR ([program_1].[degree] IS NULL AND [student].[degree] IS NULL) THEN 1 ELSE 0 END) <> 0)
+               ORDER BY 1 ASC
+          - uri: /distinct(program{degree}?school={'art','la'}){*,count(^),count(^.student)}
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            body: |2
+               | distinct(program{degree}?school={'art','la'}) |
+              -+-----------------------------------------------+-
+               | degree    | count(^)    | count(^.student)    |
+              -+-----------+-------------+---------------------+-
+               | ba        |           7 |                   6 |
+               | ct        |           1 |                   1 |
+               | ma        |           3 |                   3 |
+               | pb        |           1 |                   5 |
+                                                        (4 rows)
+
+               ----
+               /distinct(program{degree}?school={'art','la'}){*,count(^),count(^.student)}
+               SELECT [program_1].[degree],
+                      COALESCE([program_2].[count], 0),
+                      COALESCE([student].[count], 0)
+               FROM (SELECT [program].[degree]
+                     FROM [ad].[program] AS [program]
+                     WHERE ([program].[school] IN ('art', 'la'))
+                     GROUP BY [program].[degree]) AS [program_1]
+                    LEFT OUTER JOIN (SELECT COUNT(1) AS [count],
+                                            [program].[degree]
+                                     FROM [ad].[program] AS [program]
+                                     WHERE ([program].[school] IN ('art', 'la'))
+                                     GROUP BY [program].[degree]) AS [program_2]
+                                    ON ((CASE WHEN ([program_1].[degree] = [program_2].[degree]) OR ([program_1].[degree] IS NULL AND [program_2].[degree] IS NULL) THEN 1 ELSE 0 END) <> 0)
+                    LEFT OUTER JOIN (SELECT COUNT(1) AS [count],
+                                            [program].[degree]
+                                     FROM [ad].[program] AS [program]
+                                          INNER JOIN [ed].[student] AS [student]
+                                                     ON (([program].[school] = [student].[school]) AND ([program].[code] = [student].[program]))
+                                     WHERE ([program].[school] IN ('art', 'la'))
+                                     GROUP BY [program].[degree]) AS [student]
+                                    ON ((CASE WHEN ([program_1].[degree] = [student].[degree]) OR ([program_1].[degree] IS NULL AND [student].[degree] IS NULL) THEN 1 ELSE 0 END) <> 0)
+               ORDER BY 1 ASC
+          - uri: /distinct(program{degree}){*,count(^),count(^.student)}?kernel(0)
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            body: |2
+               | distinct(program{degree})            |
+              -+--------------------------------------+-
+               | degree | count(^) | count(^.student) |
+              -+--------+----------+------------------+-
+               | ba     |        9 |                6 |
+               | bs     |       12 |               20 |
+               | ct     |        4 |                6 |
+               | ma     |        6 |                5 |
+               | ms     |        5 |                9 |
+               | pb     |        1 |                5 |
+               | ph     |        1 |                0 |
+                                               (7 rows)
+
+               ----
+               /distinct(program{degree}){*,count(^),count(^.student)}?kernel(0)
+               SELECT [program_1].[degree],
+                      COALESCE([program_2].[count], 0),
+                      COALESCE([student].[count], 0)
+               FROM (SELECT [program].[degree]
+                     FROM [ad].[program] AS [program]
+                     GROUP BY [program].[degree]) AS [program_1]
+                    LEFT OUTER JOIN (SELECT COUNT(1) AS [count],
+                                            [program].[degree]
+                                     FROM [ad].[program] AS [program]
+                                     GROUP BY [program].[degree]) AS [program_2]
+                                    ON ((CASE WHEN ([program_1].[degree] = [program_2].[degree]) OR ([program_1].[degree] IS NULL AND [program_2].[degree] IS NULL) THEN 1 ELSE 0 END) <> 0)
+                    LEFT OUTER JOIN (SELECT COUNT(1) AS [count],
+                                            [program].[degree]
+                                     FROM [ad].[program] AS [program]
+                                          INNER JOIN [ed].[student] AS [student]
+                                                     ON (([program].[school] = [student].[school]) AND ([program].[code] = [student].[program]))
+                                     GROUP BY [program].[degree]) AS [student]
+                                    ON ((CASE WHEN ([program_1].[degree] = [student].[degree]) OR ([program_1].[degree] IS NULL AND [student].[degree] IS NULL) THEN 1 ELSE 0 END) <> 0)
+               WHERE (NULLIF([program_1].[degree], '') IS NOT NULL)
+               ORDER BY 1 ASC
+          - uri: /(school?code={'art','la'}).distinct(program{degree}){*,count(^),count(^.student)}
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            body: |2
+               | distinct(program{degree})            |
+              -+--------------------------------------+-
+               | degree | count(^) | count(^.student) |
+              -+--------+----------+------------------+-
+               | ba     |        2 |                1 |
+               | pb     |        1 |                5 |
+               | ba     |        5 |                5 |
+               | ct     |        1 |                1 |
+               | ma     |        3 |                3 |
+                                               (5 rows)
+
+               ----
+               /(school?code={'art','la'}).distinct(program{degree}){*,count(^),count(^.student)}
+               SELECT [program_1].[degree],
+                      COALESCE([program_2].[count], 0),
+                      COALESCE([student].[count], 0)
+               FROM [ad].[school] AS [school]
+                    INNER JOIN (SELECT [program].[degree],
+                                       [program].[school]
+                                FROM [ad].[program] AS [program]
+                                GROUP BY [program].[school], [program].[degree]) AS [program_1]
+                               ON ([school].[code] = [program_1].[school])
+                    LEFT OUTER JOIN (SELECT COUNT(1) AS [count],
+                                            [program].[school],
+                                            [program].[degree]
+                                     FROM [ad].[program] AS [program]
+                                     GROUP BY [program].[school], [program].[degree]) AS [program_2]
+                                    ON (([program_1].[school] = [program_2].[school]) AND ((CASE WHEN ([program_1].[degree] = [program_2].[degree]) OR ([program_1].[degree] IS NULL AND [program_2].[degree] IS NULL) THEN 1 ELSE 0 END) <> 0))
+                    LEFT OUTER JOIN (SELECT COUNT(1) AS [count],
+                                            [program].[school],
+                                            [program].[degree]
+                                     FROM [ad].[program] AS [program]
+                                          INNER JOIN [ed].[student] AS [student]
+                                                     ON (([program].[school] = [student].[school]) AND ([program].[code] = [student].[program]))
+                                     GROUP BY [program].[school], [program].[degree]) AS [student]
+                                    ON (([program_1].[school] = [student].[school]) AND ((CASE WHEN ([program_1].[degree] = [student].[degree]) OR ([program_1].[degree] IS NULL AND [student].[degree] IS NULL) THEN 1 ELSE 0 END) <> 0))
+               WHERE ([school].[code] IN ('art', 'la'))
+               ORDER BY [school].[code] ASC, 1 ASC
+          - uri: /distinct(program{degree}?school={'art','la'}).^
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            body: |2
+               | program{degree}?school={'art','la'}                                    |
+              -+------------------------------------------------------------------------+-
+               | school | code     | title                           | degree | part_of |
+              -+--------+----------+---------------------------------+--------+---------+-
+               | art    | uhist    | Bachelor of Arts in Art History | ba     |         |
+               | art    | ustudio  | Bachelor of Arts in Studio Art  | ba     |         |
+               | la     | uengl    | Bachelor of Arts in English     | ba     | gengl   |
+               | la     | uhist    | Bachelor of Arts in History     | ba     |         |
+               | la     | upolisci | Bachelor of Arts in Political   | ba     |         |
+               :        :          : Science                         :        :         :
+               | la     | upsych   | Bachelor of Arts in Psychology  | ba     |         |
+               | la     | uspan    | Bachelor of Arts in Spanish     | ba     |         |
+               | la     | psciwri  | Science Writing                 | ct     |         |
+               | la     | gengl    | Master of Arts in English       | ma     |         |
+               | la     | glang    | Master of Arts in Modern        | ma     |         |
+               :        :          : Languages                       :        :         :
+               | la     | gscitch  | Master of Arts in Science       | ma     |         |
+               :        :          : Teaching                        :        :         :
+               | art    | gart     | Post Baccalaureate in Art       | pb     |         |
+               :        :          : History                         :        :         :
+                                                                                (12 rows)
+
+               ----
+               /distinct(program{degree}?school={'art','la'}).^
+               SELECT [program_2].[school],
+                      [program_2].[code],
+                      [program_2].[title],
+                      [program_2].[degree],
+                      [program_2].[part_of]
+               FROM (SELECT [program].[degree]
+                     FROM [ad].[program] AS [program]
+                     WHERE ([program].[school] IN ('art', 'la'))
+                     GROUP BY [program].[degree]) AS [program_1]
+                    INNER JOIN (SELECT [program].[school],
+                                       [program].[code],
+                                       [program].[title],
+                                       [program].[degree],
+                                       [program].[part_of]
+                                FROM [ad].[program] AS [program]
+                                WHERE ([program].[school] IN ('art', 'la'))) AS [program_2]
+                               ON ((CASE WHEN ([program_1].[degree] = [program_2].[degree]) OR ([program_1].[degree] IS NULL AND [program_2].[degree] IS NULL) THEN 1 ELSE 0 END) <> 0)
+               ORDER BY [program_1].[degree] ASC, 1 ASC, 2 ASC
           - uri: /((program?school='art')^degree).limit(1)
             status: 200 OK
             headers:

File test/regress/output/mysql.yaml

View file
                FROM (SELECT TRUE AS `^`
                      FROM `htsql_regress`.`program` AS `program`
                      GROUP BY `program`.`degree`) AS `program`
+          - uri: /distinct(program{degree}){*,count(^),count(^.student)}
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            body: |2
+               | distinct(program{degree})            |
+              -+--------------------------------------+-
+               | degree | count(^) | count(^.student) |
+              -+--------+----------+------------------+-
+               |        |        1 |                0 |
+               | ba     |        9 |                6 |
+               | bs     |       12 |               20 |
+               | ct     |        4 |                6 |
+               | ma     |        6 |                5 |
+               | ms     |        5 |                9 |
+               | pb     |        1 |                5 |
+               | ph     |        1 |                0 |
+                                               (8 rows)
+
+               ----
+               /distinct(program{degree}){*,count(^),count(^.student)}
+               SELECT `program_1`.`degree`,
+                      COALESCE(`program_2`.`count`, 0),
+                      COALESCE(`student`.`count`, 0)
+               FROM (SELECT `program`.`degree`
+                     FROM `htsql_regress`.`program` AS `program`
+                     GROUP BY 1) AS `program_1`
+                    LEFT OUTER JOIN (SELECT COUNT(TRUE) AS `count`,
+                                            `program`.`degree`
+                                     FROM `htsql_regress`.`program` AS `program`
+                                     GROUP BY 2) AS `program_2`
+                                    ON (`program_1`.`degree` <=> `program_2`.`degree`)
+                    LEFT OUTER JOIN (SELECT COUNT(TRUE) AS `count`,
+                                            `program`.`degree`
+                                     FROM `htsql_regress`.`program` AS `program`
+                                          INNER JOIN `htsql_regress`.`student` AS `student`
+                                                     ON ((`program`.`school` = `student`.`school`) AND (`program`.`code` = `student`.`program`))
+                                     GROUP BY 2) AS `student`
+                                    ON (`program_1`.`degree` <=> `student`.`degree`)
+               ORDER BY 1 ASC
+          - uri: /distinct(program{degree}?school={'art','la'}){*,count(^),count(^.student)}
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            body: |2
+               | distinct(program{degree}?school={'art','la'}) |
+              -+-----------------------------------------------+-
+               | degree    | count(^)    | count(^.student)    |
+              -+-----------+-------------+---------------------+-
+               | ba        |           7 |                   6 |
+               | ct        |           1 |                   1 |
+               | ma        |           3 |                   3 |
+               | pb        |           1 |                   5 |
+                                                        (4 rows)
+
+               ----
+               /distinct(program{degree}?school={'art','la'}){*,count(^),count(^.student)}
+               SELECT `program_1`.`degree`,
+                      COALESCE(`program_2`.`count`, 0),
+                      COALESCE(`student`.`count`, 0)
+               FROM (SELECT `program`.`degree`
+                     FROM `htsql_regress`.`program` AS `program`
+                     WHERE (`program`.`school` IN ('art', 'la'))
+                     GROUP BY 1) AS `program_1`
+                    LEFT OUTER JOIN (SELECT COUNT(TRUE) AS `count`,
+                                            `program`.`degree`
+                                     FROM `htsql_regress`.`program` AS `program`
+                                     WHERE (`program`.`school` IN ('art', 'la'))
+                                     GROUP BY 2) AS `program_2`
+                                    ON (`program_1`.`degree` <=> `program_2`.`degree`)
+                    LEFT OUTER JOIN (SELECT COUNT(TRUE) AS `count`,
+                                            `program`.`degree`
+                                     FROM `htsql_regress`.`program` AS `program`
+                                          INNER JOIN `htsql_regress`.`student` AS `student`
+                                                     ON ((`program`.`school` = `student`.`school`) AND (`program`.`code` = `student`.`program`))
+                                     WHERE (`program`.`school` IN ('art', 'la'))
+                                     GROUP BY 2) AS `student`
+                                    ON (`program_1`.`degree` <=> `student`.`degree`)
+               ORDER BY 1 ASC
+          - uri: /distinct(program{degree}){*,count(^),count(^.student)}?kernel(0)
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            body: |2
+               | distinct(program{degree})            |
+              -+--------------------------------------+-
+               | degree | count(^) | count(^.student) |
+              -+--------+----------+------------------+-
+               | ba     |        9 |                6 |
+               | bs     |       12 |               20 |
+               | ct     |        4 |                6 |
+               | ma     |        6 |                5 |
+               | ms     |        5 |                9 |
+               | pb     |        1 |                5 |
+               | ph     |        1 |                0 |
+                                               (7 rows)
+
+               ----
+               /distinct(program{degree}){*,count(^),count(^.student)}?kernel(0)
+               SELECT `program_1`.`degree`,
+                      COALESCE(`program_2`.`count`, 0),
+                      COALESCE(`student`.`count`, 0)
+               FROM (SELECT `program`.`degree`
+                     FROM `htsql_regress`.`program` AS `program`
+                     GROUP BY 1) AS `program_1`
+                    LEFT OUTER JOIN (SELECT COUNT(TRUE) AS `count`,
+                                            `program`.`degree`
+                                     FROM `htsql_regress`.`program` AS `program`
+                                     GROUP BY 2) AS `program_2`
+                                    ON (`program_1`.`degree` <=> `program_2`.`degree`)
+                    LEFT OUTER JOIN (SELECT COUNT(TRUE) AS `count`,
+                                            `program`.`degree`
+                                     FROM `htsql_regress`.`program` AS `program`
+                                          INNER JOIN `htsql_regress`.`student` AS `student`
+                                                     ON ((`program`.`school` = `student`.`school`) AND (`program`.`code` = `student`.`program`))
+                                     GROUP BY 2) AS `student`
+                                    ON (`program_1`.`degree` <=> `student`.`degree`)
+               WHERE (NULLIF(`program_1`.`degree`, '') IS NOT NULL)
+               ORDER BY 1 ASC
+          - uri: /(school?code={'art','la'}).distinct(program{degree}){*,count(^),count(^.student)}
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            body: |2
+               | distinct(program{degree})            |
+              -+--------------------------------------+-
+               | degree | count(^) | count(^.student) |
+              -+--------+----------+------------------+-
+               | ba     |        2 |                1 |
+               | pb     |        1 |                5 |
+               | ba     |        5 |                5 |
+               | ct     |        1 |                1 |
+               | ma     |        3 |                3 |
+                                               (5 rows)
+
+               ----
+               /(school?code={'art','la'}).distinct(program{degree}){*,count(^),count(^.student)}
+               SELECT `program_1`.`degree`,
+                      COALESCE(`program_2`.`count`, 0),
+                      COALESCE(`student`.`count`, 0)
+               FROM `htsql_regress`.`school` AS `school`
+                    INNER JOIN (SELECT `program`.`degree`,
+                                       `program`.`school`
+                                FROM `htsql_regress`.`program` AS `program`
+                                GROUP BY 2, 1) AS `program_1`
+                               ON (`school`.`code` = `program_1`.`school`)
+                    LEFT OUTER JOIN (SELECT COUNT(TRUE) AS `count`,
+                                            `program`.`school`,
+                                            `program`.`degree`
+                                     FROM `htsql_regress`.`program` AS `program`
+                                     GROUP BY 2, 3) AS `program_2`
+                                    ON ((`program_1`.`school` = `program_2`.`school`) AND (`program_1`.`degree` <=> `program_2`.`degree`))
+                    LEFT OUTER JOIN (SELECT COUNT(TRUE) AS `count`,
+                                            `program`.`school`,
+                                            `program`.`degree`
+                                     FROM `htsql_regress`.`program` AS `program`
+                                          INNER JOIN `htsql_regress`.`student` AS `student`
+                                                     ON ((`program`.`school` = `student`.`school`) AND (`program`.`code` = `student`.`program`))
+                                     GROUP BY 2, 3) AS `student`
+                                    ON ((`program_1`.`school` = `student`.`school`) AND (`program_1`.`degree` <=> `student`.`degree`))
+               WHERE (`school`.`code` IN ('art', 'la'))
+               ORDER BY `school`.`code` ASC, 1 ASC
+          - uri: /distinct(program{degree}?school={'art','la'}).^
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            body: |2
+               | program{degree}?school={'art','la'}                                    |
+              -+------------------------------------------------------------------------+-
+               | school | code     | title                           | degree | part_of |
+              -+--------+----------+---------------------------------+--------+---------+-
+               | art    | uhist    | Bachelor of Arts in Art History | ba     |         |
+               | art    | ustudio  | Bachelor of Arts in Studio Art  | ba     |         |
+               | la     | uengl    | Bachelor of Arts in English     | ba     | gengl   |
+               | la     | uhist    | Bachelor of Arts in History     | ba     |         |
+               | la     | upolisci | Bachelor of Arts in Political   | ba     |         |
+               :        :          : Science                         :        :         :
+               | la     | upsych   | Bachelor of Arts in Psychology  | ba     |         |
+               | la     | uspan    | Bachelor of Arts in Spanish     | ba     |         |
+               | la     | psciwri  | Science Writing                 | ct     |         |
+               | la     | gengl    | Master of Arts in English       | ma     |         |
+               | la     | glang    | Master of Arts in Modern        | ma     |         |
+               :        :          : Languages                       :        :         :
+               | la     | gscitch  | Master of Arts in Science       | ma     |         |
+               :        :          : Teaching                        :        :         :
+               | art    | gart     | Post Baccalaureate in Art       | pb     |         |
+               :        :          : History                         :        :         :
+                                                                                (12 rows)
+
+               ----
+               /distinct(program{degree}?school={'art','la'}).^
+               SELECT `program_2`.`school`,
+                      `program_2`.`code`,
+                      `program_2`.`title`,
+                      `program_2`.`degree`,
+                      `program_2`.`part_of`
+               FROM (SELECT `program`.`degree`
+                     FROM `htsql_regress`.`program` AS `program`
+                     WHERE (`program`.`school` IN ('art', 'la'))
+                     GROUP BY 1) AS `program_1`
+                    INNER JOIN (SELECT `program`.`school`,
+                                       `program`.`code`,
+                                       `program`.`title`,
+                                       `program`.`degree`,
+                                       `program`.`part_of`
+                                FROM `htsql_regress`.`program` AS `program`
+                                WHERE (`program`.`school` IN ('art', 'la'))) AS `program_2`
+                               ON (`program_1`.`degree` <=> `program_2`.`degree`)
+               ORDER BY `program_1`.`degree` ASC, 1 ASC, 2 ASC
           - uri: /((program?school='art')^degree).limit(1)
             status: 200 OK
             headers:

File test/regress/output/oracle.yaml

View file
                FROM (SELECT 1 AS "^"
                      FROM "HTSQL_REGRESS"."PROGRAM" "PROGRAM"
                      GROUP BY "PROGRAM"."DEGREE") "PROGRAM"
+          - uri: /distinct(program{degree}){*,count(^),count(^.student)}
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            body: |2
+               | distinct(program{degree})            |
+              -+--------------------------------------+-
+               | degree | count(^) | count(^.student) |
+              -+--------+----------+------------------+-
+               |        |        1 |                0 |
+               | ba     |        9 |                6 |
+               | bs     |       12 |               20 |
+               | ct     |        4 |                6 |
+               | ma     |        6 |                5 |
+               | ms     |        5 |                9 |
+               | pb     |        1 |                5 |
+               | ph     |        1 |                0 |
+                                               (8 rows)
+
+               ----
+               /distinct(program{degree}){*,count(^),count(^.student)}
+               SELECT "PROGRAM_1"."DEGREE",
+                      COALESCE("PROGRAM_2"."count", 0),
+                      COALESCE("STUDENT"."count", 0)
+               FROM (SELECT "PROGRAM"."DEGREE"
+                     FROM "HTSQL_REGRESS"."PROGRAM" "PROGRAM"
+                     GROUP BY "PROGRAM"."DEGREE") "PROGRAM_1"
+                    LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
+                                            "PROGRAM"."DEGREE"
+                                     FROM "HTSQL_REGRESS"."PROGRAM" "PROGRAM"
+                                     GROUP BY "PROGRAM"."DEGREE") "PROGRAM_2"
+                                    ON ((CASE WHEN ("PROGRAM_1"."DEGREE" = "PROGRAM_2"."DEGREE") OR ("PROGRAM_1"."DEGREE" IS NULL AND "PROGRAM_2"."DEGREE" IS NULL) THEN 1 ELSE 0 END) <> 0)
+                    LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
+                                            "PROGRAM"."DEGREE"
+                                     FROM "HTSQL_REGRESS"."PROGRAM" "PROGRAM"
+                                          INNER JOIN "HTSQL_REGRESS"."STUDENT" "STUDENT"
+                                                     ON (("PROGRAM"."SCHOOL" = "STUDENT"."SCHOOL") AND ("PROGRAM"."CODE" = "STUDENT"."PROGRAM"))
+                                     GROUP BY "PROGRAM"."DEGREE") "STUDENT"
+                                    ON ((CASE WHEN ("PROGRAM_1"."DEGREE" = "STUDENT"."DEGREE") OR ("PROGRAM_1"."DEGREE" IS NULL AND "STUDENT"."DEGREE" IS NULL) THEN 1 ELSE 0 END) <> 0)
+               ORDER BY 1 ASC NULLS FIRST
+          - uri: /distinct(program{degree}?school={'art','la'}){*,count(^),count(^.student)}
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            body: |2
+               | distinct(program{degree}?school={'art','la'}) |
+              -+-----------------------------------------------+-
+               | degree    | count(^)    | count(^.student)    |
+              -+-----------+-------------+---------------------+-
+               | ba        |           7 |                   6 |
+               | ct        |           1 |                   1 |
+               | ma        |           3 |                   3 |
+               | pb        |           1 |                   5 |
+                                                        (4 rows)
+
+               ----
+               /distinct(program{degree}?school={'art','la'}){*,count(^),count(^.student)}
+               SELECT "PROGRAM_1"."DEGREE",
+                      COALESCE("PROGRAM_2"."count", 0),
+                      COALESCE("STUDENT"."count", 0)
+               FROM (SELECT "PROGRAM"."DEGREE"
+                     FROM "HTSQL_REGRESS"."PROGRAM" "PROGRAM"
+                     WHERE ("PROGRAM"."SCHOOL" IN ('art', 'la'))
+                     GROUP BY "PROGRAM"."DEGREE") "PROGRAM_1"
+                    LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
+                                            "PROGRAM"."DEGREE"
+                                     FROM "HTSQL_REGRESS"."PROGRAM" "PROGRAM"
+                                     WHERE ("PROGRAM"."SCHOOL" IN ('art', 'la'))
+                                     GROUP BY "PROGRAM"."DEGREE") "PROGRAM_2"
+                                    ON ((CASE WHEN ("PROGRAM_1"."DEGREE" = "PROGRAM_2"."DEGREE") OR ("PROGRAM_1"."DEGREE" IS NULL AND "PROGRAM_2"."DEGREE" IS NULL) THEN 1 ELSE 0 END) <> 0)
+                    LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
+                                            "PROGRAM"."DEGREE"
+                                     FROM "HTSQL_REGRESS"."PROGRAM" "PROGRAM"
+                                          INNER JOIN "HTSQL_REGRESS"."STUDENT" "STUDENT"
+                                                     ON (("PROGRAM"."SCHOOL" = "STUDENT"."SCHOOL") AND ("PROGRAM"."CODE" = "STUDENT"."PROGRAM"))
+                                     WHERE ("PROGRAM"."SCHOOL" IN ('art', 'la'))
+                                     GROUP BY "PROGRAM"."DEGREE") "STUDENT"
+                                    ON ((CASE WHEN ("PROGRAM_1"."DEGREE" = "STUDENT"."DEGREE") OR ("PROGRAM_1"."DEGREE" IS NULL AND "STUDENT"."DEGREE" IS NULL) THEN 1 ELSE 0 END) <> 0)
+               ORDER BY 1 ASC NULLS FIRST
+          - uri: /distinct(program{degree}){*,count(^),count(^.student)}?kernel(0)
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            body: |2
+               | distinct(program{degree})            |
+              -+--------------------------------------+-
+               | degree | count(^) | count(^.student) |
+              -+--------+----------+------------------+-
+               | ba     |        9 |                6 |
+               | bs     |       12 |               20 |
+               | ct     |        4 |                6 |
+               | ma     |        6 |                5 |
+               | ms     |        5 |                9 |
+               | pb     |        1 |                5 |
+               | ph     |        1 |                0 |
+                                               (7 rows)
+
+               ----
+               /distinct(program{degree}){*,count(^),count(^.student)}?kernel(0)
+               SELECT "PROGRAM_1"."DEGREE",
+                      COALESCE("PROGRAM_2"."count", 0),
+                      COALESCE("STUDENT"."count", 0)
+               FROM (SELECT "PROGRAM"."DEGREE"
+                     FROM "HTSQL_REGRESS"."PROGRAM" "PROGRAM"
+                     GROUP BY "PROGRAM"."DEGREE") "PROGRAM_1"
+                    LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
+                                            "PROGRAM"."DEGREE"
+                                     FROM "HTSQL_REGRESS"."PROGRAM" "PROGRAM"
+                                     GROUP BY "PROGRAM"."DEGREE") "PROGRAM_2"
+                                    ON ((CASE WHEN ("PROGRAM_1"."DEGREE" = "PROGRAM_2"."DEGREE") OR ("PROGRAM_1"."DEGREE" IS NULL AND "PROGRAM_2"."DEGREE" IS NULL) THEN 1 ELSE 0 END) <> 0)
+                    LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
+                                            "PROGRAM"."DEGREE"
+                                     FROM "HTSQL_REGRESS"."PROGRAM" "PROGRAM"
+                                          INNER JOIN "HTSQL_REGRESS"."STUDENT" "STUDENT"
+                                                     ON (("PROGRAM"."SCHOOL" = "STUDENT"."SCHOOL") AND ("PROGRAM"."CODE" = "STUDENT"."PROGRAM"))
+                                     GROUP BY "PROGRAM"."DEGREE") "STUDENT"
+                                    ON ((CASE WHEN ("PROGRAM_1"."DEGREE" = "STUDENT"."DEGREE") OR ("PROGRAM_1"."DEGREE" IS NULL AND "STUDENT"."DEGREE" IS NULL) THEN 1 ELSE 0 END) <> 0)
+               WHERE ("PROGRAM_1"."DEGREE" IS NOT NULL)
+               ORDER BY 1 ASC NULLS FIRST
+          - uri: /(school?code={'art','la'}).distinct(program{degree}){*,count(^),count(^.student)}
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            body: |2
+               | distinct(program{degree})            |
+              -+--------------------------------------+-
+               | degree | count(^) | count(^.student) |
+              -+--------+----------+------------------+-
+               | ba     |        2 |                1 |
+               | pb     |        1 |                5 |
+               | ba     |        5 |                5 |
+               | ct     |        1 |                1 |
+               | ma     |        3 |                3 |
+                                               (5 rows)
+
+               ----
+               /(school?code={'art','la'}).distinct(program{degree}){*,count(^),count(^.student)}
+               SELECT "PROGRAM_1"."DEGREE",
+                      COALESCE("PROGRAM_2"."count", 0),
+                      COALESCE("STUDENT"."count", 0)
+               FROM "HTSQL_REGRESS"."SCHOOL" "SCHOOL"
+                    INNER JOIN (SELECT "PROGRAM"."DEGREE",
+                                       "PROGRAM"."SCHOOL"
+                                FROM "HTSQL_REGRESS"."PROGRAM" "PROGRAM"
+                                GROUP BY "PROGRAM"."SCHOOL", "PROGRAM"."DEGREE") "PROGRAM_1"
+                               ON ("SCHOOL"."CODE" = "PROGRAM_1"."SCHOOL")
+                    LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
+                                            "PROGRAM"."SCHOOL",
+                                            "PROGRAM"."DEGREE"
+                                     FROM "HTSQL_REGRESS"."PROGRAM" "PROGRAM"
+                                     GROUP BY "PROGRAM"."SCHOOL", "PROGRAM"."DEGREE") "PROGRAM_2"
+                                    ON (("PROGRAM_1"."SCHOOL" = "PROGRAM_2"."SCHOOL") AND ((CASE WHEN ("PROGRAM_1"."DEGREE" = "PROGRAM_2"."DEGREE") OR ("PROGRAM_1"."DEGREE" IS NULL AND "PROGRAM_2"."DEGREE" IS NULL) THEN 1 ELSE 0 END) <> 0))
+                    LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
+                                            "PROGRAM"."SCHOOL",
+                                            "PROGRAM"."DEGREE"
+                                     FROM "HTSQL_REGRESS"."PROGRAM" "PROGRAM"
+                                          INNER JOIN "HTSQL_REGRESS"."STUDENT" "STUDENT"
+                                                     ON (("PROGRAM"."SCHOOL" = "STUDENT"."SCHOOL") AND ("PROGRAM"."CODE" = "STUDENT"."PROGRAM"))
+                                     GROUP BY "PROGRAM"."SCHOOL", "PROGRAM"."DEGREE") "STUDENT"
+                                    ON (("PROGRAM_1"."SCHOOL" = "STUDENT"."SCHOOL") AND ((CASE WHEN ("PROGRAM_1"."DEGREE" = "STUDENT"."DEGREE") OR ("PROGRAM_1"."DEGREE" IS NULL AND "STUDENT"."DEGREE" IS NULL) THEN 1 ELSE 0 END) <> 0))
+               WHERE ("SCHOOL"."CODE" IN ('art', 'la'))
+               ORDER BY "SCHOOL"."CODE" ASC, 1 ASC NULLS FIRST
+          - uri: /distinct(program{degree}?school={'art','la'}).^
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            body: |2
+               | program{degree}?school={'art','la'}                                    |
+              -+------------------------------------------------------------------------+-
+               | school | code     | title                           | degree | part_of |
+              -+--------+----------+---------------------------------+--------+---------+-
+               | art    | uhist    | Bachelor of Arts in Art History | ba     |         |
+               | art    | ustudio  | Bachelor of Arts in Studio Art  | ba     |         |
+               | la     | uengl    | Bachelor of Arts in English     | ba     | gengl   |
+               | la     | uhist    | Bachelor of Arts in History     | ba     |         |
+               | la     | upolisci | Bachelor of Arts in Political   | ba     |         |
+               :        :          : Science                         :        :         :
+               | la     | upsych   | Bachelor of Arts in Psychology  | ba     |         |
+               | la     | uspan    | Bachelor of Arts in Spanish     | ba     |         |
+               | la     | psciwri  | Science Writing                 | ct     |         |
+               | la     | gengl    | Master of Arts in English       | ma     |         |
+               | la     | glang    | Master of Arts in Modern        | ma     |         |
+               :        :          : Languages                       :        :         :
+               | la     | gscitch  | Master of Arts in Science       | ma     |         |
+               :        :          : Teaching                        :        :         :
+               | art    | gart     | Post Baccalaureate in Art       | pb     |         |
+               :        :          : History                         :        :         :
+                                                                                (12 rows)
+
+               ----
+               /distinct(program{degree}?school={'art','la'}).^
+               SELECT "PROGRAM_2"."SCHOOL",
+                      "PROGRAM_2"."CODE",
+                      "PROGRAM_2"."TITLE",
+                      "PROGRAM_2"."DEGREE",
+                      "PROGRAM_2"."PART_OF"
+               FROM (SELECT "PROGRAM"."DEGREE"
+                     FROM "HTSQL_REGRESS"."PROGRAM" "PROGRAM"
+                     WHERE ("PROGRAM"."SCHOOL" IN ('art', 'la'))
+                     GROUP BY "PROGRAM"."DEGREE") "PROGRAM_1"
+                    INNER JOIN (SELECT "PROGRAM"."SCHOOL",
+                                       "PROGRAM"."CODE",
+                                       "PROGRAM"."TITLE",
+                                       "PROGRAM"."DEGREE",
+                                       "PROGRAM"."PART_OF"
+                                FROM "HTSQL_REGRESS"."PROGRAM" "PROGRAM"
+                                WHERE ("PROGRAM"."SCHOOL" IN ('art', 'la'))) "PROGRAM_2"
+                               ON ((CASE WHEN ("PROGRAM_1"."DEGREE" = "PROGRAM_2"."DEGREE") OR ("PROGRAM_1"."DEGREE" IS NULL AND "PROGRAM_2"."DEGREE" IS NULL) THEN 1 ELSE 0 END) <> 0)
+               ORDER BY "PROGRAM_1"."DEGREE" ASC NULLS FIRST, 1 ASC, 2 ASC
           - uri: /((program?school='art')^degree).limit(1)
             status: 200 OK
             headers:

File test/regress/output/pgsql.yaml

View file
                FROM (SELECT TRUE AS "^"
                      FROM "ad"."program" AS "program"
                      GROUP BY "program"."degree") AS "program"
+          - uri: /distinct(program{degree}){*,count(^),count(^.student)}
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            body: |2
+               | distinct(program{degree})            |
+              -+--------------------------------------+-
+               | degree | count(^) | count(^.student) |
+              -+--------+----------+------------------+-
+               |        |        1 |                0 |
+               | ba     |        9 |                6 |
+               | bs     |       12 |               20 |
+               | ct     |        4 |                6 |
+               | ma     |        6 |                5 |
+               | ms     |        5 |                9 |
+               | pb     |        1 |                5 |
+               | ph     |        1 |                0 |
+                                               (8 rows)
+
+               ----
+               /distinct(program{degree}){*,count(^),count(^.student)}
+               SELECT "program_1"."degree",
+                      COALESCE("program_2"."count", 0),
+                      COALESCE("student"."count", 0)
+               FROM (SELECT "program"."degree"
+                     FROM "ad"."program" AS "program"
+                     GROUP BY 1) AS "program_1"
+                    LEFT OUTER JOIN (SELECT COUNT(TRUE) AS "count",
+                                            "program"."degree"
+                                     FROM "ad"."program" AS "program"
+                                     GROUP BY 2) AS "program_2"
+                                    ON ("program_1"."degree" IS NOT DISTINCT FROM "program_2"."degree")
+                    LEFT OUTER JOIN (SELECT COUNT(TRUE) AS "count",
+                                            "program"."degree"
+                                     FROM "ad"."program" AS "program"
+                                          INNER JOIN "ed"."student" AS "student"
+                                                     ON (("program"."school" = "student"."school") AND ("program"."code" = "student"."program"))
+                                     GROUP BY 2) AS "student"
+                                    ON ("program_1"."degree" IS NOT DISTINCT FROM "student"."degree")
+               ORDER BY 1 ASC NULLS FIRST
+          - uri: /distinct(program{degree}?school={'art','la'}){*,count(^),count(^.student)}
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            body: |2
+               | distinct(program{degree}?school={'art','la'}) |
+              -+-----------------------------------------------+-
+               | degree    | count(^)    | count(^.student)    |
+              -+-----------+-------------+---------------------+-
+               | ba        |           7 |                   6 |
+               | ct        |           1 |                   1 |
+               | ma        |           3 |                   3 |
+               | pb        |           1 |                   5 |
+                                                        (4 rows)
+
+               ----
+               /distinct(program{degree}?school={'art','la'}){*,count(^),count(^.student)}
+               SELECT "program_1"."degree",
+                      COALESCE("program_2"."count", 0),
+                      COALESCE("student"."count", 0)
+               FROM (SELECT "program"."degree"
+                     FROM "ad"."program" AS "program"
+                     WHERE ("program"."school" IN ('art', 'la'))
+                     GROUP BY 1) AS "program_1"
+                    LEFT OUTER JOIN (SELECT COUNT(TRUE) AS "count",
+                                            "program"."degree"
+                                     FROM "ad"."program" AS "program"
+                                     WHERE ("program"."school" IN ('art', 'la'))
+                                     GROUP BY 2) AS "program_2"
+                                    ON ("program_1"."degree" IS NOT DISTINCT FROM "program_2"."degree")
+                    LEFT OUTER JOIN (SELECT COUNT(TRUE) AS "count",
+                                            "program"."degree"
+                                     FROM "ad"."program" AS "program"
+                                          INNER JOIN "ed"."student" AS "student"
+                                                     ON (("program"."school" = "student"."school") AND ("program"."code" = "student"."program"))
+                                     WHERE ("program"."school" IN ('art', 'la'))
+                                     GROUP BY 2) AS "student"
+                                    ON ("program_1"."degree" IS NOT DISTINCT FROM "student"."degree")
+               ORDER BY 1 ASC NULLS FIRST
+          - uri: /distinct(program{degree}){*,count(^),count(^.student)}?kernel(0)
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            body: |2
+               | distinct(program{degree})            |
+              -+--------------------------------------+-
+               | degree | count(^) | count(^.student) |
+              -+--------+----------+------------------+-
+               | ba     |        9 |                6 |
+               | bs     |       12 |               20 |
+               | ct     |        4 |                6 |
+               | ma     |        6 |                5 |
+               | ms     |        5 |                9 |
+               | pb     |        1 |                5 |
+               | ph     |        1 |                0 |
+                                               (7 rows)
+
+               ----
+               /distinct(program{degree}){*,count(^),count(^.student)}?kernel(0)
+               SELECT "program_1"."degree",
+                      COALESCE("program_2"."count", 0),
+                      COALESCE("student"."count", 0)
+               FROM (SELECT "program"."degree"
+                     FROM "ad"."program" AS "program"
+                     GROUP BY 1) AS "program_1"
+                    LEFT OUTER JOIN (SELECT COUNT(TRUE) AS "count",
+                                            "program"."degree"
+                                     FROM "ad"."program" AS "program"
+                                     GROUP BY 2) AS "program_2"
+                                    ON ("program_1"."degree" IS NOT DISTINCT FROM "program_2"."degree")
+                    LEFT OUTER JOIN (SELECT COUNT(TRUE) AS "count",
+                                            "program"."degree"
+                                     FROM "ad"."program" AS "program"
+                                          INNER JOIN "ed"."student" AS "student"
+                                                     ON (("program"."school" = "student"."school") AND ("program"."code" = "student"."program"))
+                                     GROUP BY 2) AS "student"
+                                    ON ("program_1"."degree" IS NOT DISTINCT FROM "student"."degree")
+               WHERE (NULLIF("program_1"."degree", '') IS NOT NULL)
+               ORDER BY 1 ASC NULLS FIRST
+          - uri: /(school?code={'art','la'}).distinct(program{degree}){*,count(^),count(^.student)}
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            body: |2
+               | distinct(program{degree})            |
+              -+--------------------------------------+-
+               | degree | count(^) | count(^.student) |
+              -+--------+----------+------------------+-
+               | ba     |        2 |                1 |
+               | pb     |        1 |                5 |
+               | ba     |        5 |                5 |
+               | ct     |        1 |                1 |
+               | ma     |        3 |                3 |
+                                               (5 rows)
+
+               ----
+               /(school?code={'art','la'}).distinct(program{degree}){*,count(^),count(^.student)}
+               SELECT "program_1"."degree",
+                      COALESCE("program_2"."count", 0),
+                      COALESCE("student"."count", 0)
+               FROM "ad"."school" AS "school"
+                    INNER JOIN (SELECT "program"."degree",
+                                       "program"."school"
+                                FROM "ad"."program" AS "program"
+                                GROUP BY 2, 1) AS "program_1"
+                               ON ("school"."code" = "program_1"."school")
+                    LEFT OUTER JOIN (SELECT COUNT(TRUE) AS "count",
+                                            "program"."school",
+                                            "program"."degree"
+                                     FROM "ad"."program" AS "program"
+                                     GROUP BY 2, 3) AS "program_2"
+                                    ON (("program_1"."school" = "program_2"."school") AND ("program_1"."degree" IS NOT DISTINCT FROM "program_2"."degree"))
+                    LEFT OUTER JOIN (SELECT COUNT(TRUE) AS "count",
+                                            "program"."school",
+                                            "program"."degree"
+                                     FROM "ad"."program" AS "program"
+                                          INNER JOIN "ed"."student" AS "student"
+                                                     ON (("program"."school" = "student"."school") AND ("program"."code" = "student"."program"))
+                                     GROUP BY 2, 3) AS "student"
+                                    ON (("program_1"."school" = "student"."school") AND ("program_1"."degree" IS NOT DISTINCT FROM "student"."degree"))
+               WHERE ("school"."code" IN ('art', 'la'))
+               ORDER BY "school"."code" ASC, 1 ASC NULLS FIRST
+          - uri: /distinct(program{degree}?school={'art','la'}).^
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            body: |2
+               | program{degree}?school={'art','la'}                                    |
+              -+------------------------------------------------------------------------+-
+               | school | code     | title                           | degree | part_of |
+              -+--------+----------+---------------------------------+--------+---------+-
+               | art    | uhist    | Bachelor of Arts in Art History | ba     |         |
+               | art    | ustudio  | Bachelor of Arts in Studio Art  | ba     |         |
+               | la     | uengl    | Bachelor of Arts in English     | ba     | gengl   |
+               | la     | uhist    | Bachelor of Arts in History     | ba     |         |
+               | la     | upolisci | Bachelor of Arts in Political   | ba     |         |
+               :        :          : Science                         :        :         :
+               | la     | upsych   | Bachelor of Arts in Psychology  | ba     |         |
+               | la     | uspan    | Bachelor of Arts in Spanish     | ba     |         |
+               | la     | psciwri  | Science Writing                 | ct     |         |
+               | la     | gengl    | Master of Arts in English       | ma     |         |
+               | la     | glang    | Master of Arts in Modern        | ma     |         |
+               :        :          : Languages                       :        :         :
+               | la     | gscitch  | Master of Arts in Science       | ma     |         |
+               :        :          : Teaching                        :        :         :
+               | art    | gart     | Post Baccalaureate in Art       | pb     |         |
+               :        :          : History                         :        :         :
+                                                                                (12 rows)
+
+               ----
+               /distinct(program{degree}?school={'art','la'}).^
+               SELECT "program_2"."school",
+                      "program_2"."code",
+                      "program_2"."title",
+                      "program_2"."degree",
+                      "program_2"."part_of"
+               FROM (SELECT "program"."degree"
+                     FROM "ad"."program" AS "program"
+                     WHERE ("program"."school" IN ('art', 'la'))
+                     GROUP BY 1) AS "program_1"
+                    INNER JOIN (SELECT "program"."school",
+                                       "program"."code",
+                                       "program"."title",
+                                       "program"."degree",
+                                       "program"."part_of"
+                                FROM "ad"."program" AS "program"
+                                WHERE ("program"."school" IN ('art', 'la'))) AS "program_2"
+                               ON ("program_1"."degree" IS NOT DISTINCT FROM "program_2"."degree")
+               ORDER BY "program_1"."degree" ASC NULLS FIRST, 1 ASC, 2 ASC
           - uri: /((program?school='art')^degree).limit(1)
             status: 200 OK
             headers:

File test/regress/output/sqlite.yaml

View file
                FROM (SELECT 1 AS "^"
                      FROM "program" AS "program"
                      GROUP BY "program"."degree") AS "program"
+          - uri: /distinct(program{degree}){*,count(^),count(^.student)}
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            body: |2
+               | distinct(program{degree})            |
+              -+--------------------------------------+-
+               | degree | count(^) | count(^.student) |
+              -+--------+----------+------------------+-
+               |        |        1 |                0 |
+               | ba     |        9 |                6 |
+               | bs     |       12 |               20 |
+               | ct     |        4 |                6 |
+               | ma     |        6 |                5 |
+               | ms     |        5 |                9 |
+               | pb     |        1 |                5 |
+               | ph     |        1 |                0 |
+                                               (8 rows)
+
+               ----
+               /distinct(program{degree}){*,count(^),count(^.student)}
+               SELECT "program_1"."degree",
+                      COALESCE("program_2"."count", 0),
+                      COALESCE("student"."count", 0)
+               FROM (SELECT "program"."degree"
+                     FROM "program" AS "program"
+                     GROUP BY 1) AS "program_1"
+                    LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
+                                            "program"."degree"
+                                     FROM "program" AS "program"
+                                     GROUP BY 2) AS "program_2"
+                                    ON (CASE WHEN ("program_1"."degree" = "program_2"."degree") OR ("program_1"."degree" IS NULL AND "program_2"."degree" IS NULL) THEN 1 ELSE 0 END)
+                    LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
+                                            "program"."degree"
+                                     FROM "program" AS "program"
+                                          INNER JOIN "student" AS "student"
+                                                     ON (("program"."school" = "student"."school") AND ("program"."code" = "student"."program"))
+                                     GROUP BY 2) AS "student"
+                                    ON (CASE WHEN ("program_1"."degree" = "student"."degree") OR ("program_1"."degree" IS NULL AND "student"."degree" IS NULL) THEN 1 ELSE 0 END)
+               ORDER BY 1 ASC
+          - uri: /distinct(program{degree}?school={'art','la'}){*,count(^),count(^.student)}
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            body: |2
+               | distinct(program{degree}?school={'art','la'}) |
+              -+-----------------------------------------------+-
+               | degree    | count(^)    | count(^.student)    |
+              -+-----------+-------------+---------------------+-
+               | ba        |           7 |                   6 |
+               | ct        |           1 |                   1 |
+               | ma        |           3 |                   3 |
+               | pb        |           1 |                   5 |
+                                                        (4 rows)
+
+               ----
+               /distinct(program{degree}?school={'art','la'}){*,count(^),count(^.student)}
+               SELECT "program_1"."degree",
+                      COALESCE("program_2"."count", 0),
+                      COALESCE("student"."count", 0)
+               FROM (SELECT "program"."degree"
+                     FROM "program" AS "program"
+                     WHERE ("program"."school" IN ('art', 'la'))
+                     GROUP BY 1) AS "program_1"
+                    LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
+                                            "program"."degree"
+                                     FROM "program" AS "program"
+                                     WHERE ("program"."school" IN ('art', 'la'))
+                                     GROUP BY 2) AS "program_2"
+                                    ON (CASE WHEN ("program_1"."degree" = "program_2"."degree") OR ("program_1"."degree" IS NULL AND "program_2"."degree" IS NULL) THEN 1 ELSE 0 END)
+                    LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
+                                            "program"."degree"
+                                     FROM "program" AS "program"
+                                          INNER JOIN "student" AS "student"
+                                                     ON (("program"."school" = "student"."school") AND ("program"."code" = "student"."program"))
+                                     WHERE ("program"."school" IN ('art', 'la'))
+                                     GROUP BY 2) AS "student"
+                                    ON (CASE WHEN ("program_1"."degree" = "student"."degree") OR ("program_1"."degree" IS NULL AND "student"."degree" IS NULL) THEN 1 ELSE 0 END)
+               ORDER BY 1 ASC
+          - uri: /distinct(program{degree}){*,count(^),count(^.student)}?kernel(0)
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            body: |2
+               | distinct(program{degree})            |
+              -+--------------------------------------+-
+               | degree | count(^) | count(^.student) |
+              -+--------+----------+------------------+-
+               | ba     |        9 |                6 |
+               | bs     |       12 |               20 |
+               | ct     |        4 |                6 |
+               | ma     |        6 |                5 |
+               | ms     |        5 |                9 |
+               | pb     |        1 |                5 |
+               | ph     |        1 |                0 |
+                                               (7 rows)
+
+               ----
+               /distinct(program{degree}){*,count(^),count(^.student)}?kernel(0)
+               SELECT "program_1"."degree",
+                      COALESCE("program_2"."count", 0),
+                      COALESCE("student"."count", 0)
+               FROM (SELECT "program"."degree"
+                     FROM "program" AS "program"
+                     GROUP BY 1) AS "program_1"
+                    LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
+                                            "program"."degree"
+                                     FROM "program" AS "program"
+                                     GROUP BY 2) AS "program_2"
+                                    ON (CASE WHEN ("program_1"."degree" = "program_2"."degree") OR ("program_1"."degree" IS NULL AND "program_2"."degree" IS NULL) THEN 1 ELSE 0 END)
+                    LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
+                                            "program"."degree"
+                                     FROM "program" AS "program"
+                                          INNER JOIN "student" AS "student"
+                                                     ON (("program"."school" = "student"."school") AND ("program"."code" = "student"."program"))
+                                     GROUP BY 2) AS "student"
+                                    ON (CASE WHEN ("program_1"."degree" = "student"."degree") OR ("program_1"."degree" IS NULL AND "student"."degree" IS NULL) THEN 1 ELSE 0 END)
+               WHERE (NULLIF("program_1"."degree", '') IS NOT NULL)
+               ORDER BY 1 ASC
+          - uri: /(school?code={'art','la'}).distinct(program{degree}){*,count(^),count(^.student)}
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            body: |2
+               | distinct(program{degree})            |
+              -+--------------------------------------+-
+               | degree | count(^) | count(^.student) |
+              -+--------+----------+------------------+-
+               | ba     |        2 |                1 |
+               | pb     |        1 |                5 |
+               | ba     |        5 |                5 |
+               | ct     |        1 |                1 |
+               | ma     |        3 |                3 |
+                                               (5 rows)
+
+               ----
+               /(school?code={'art','la'}).distinct(program{degree}){*,count(^),count(^.student)}
+               SELECT "program_1"."degree",
+                      COALESCE("program_2"."count", 0),
+                      COALESCE("student"."count", 0)
+               FROM "school" AS "school"
+                    INNER JOIN (SELECT "program"."degree",
+                                       "program"."school"
+                                FROM "program" AS "program"
+                                GROUP BY 2, 1) AS "program_1"
+                               ON ("school"."code" = "program_1"."school")
+                    LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
+                                            "program"."school",
+                                            "program"."degree"
+                                     FROM "program" AS "program"
+                                     GROUP BY 2, 3) AS "program_2"
+                                    ON (("program_1"."school" = "program_2"."school") AND (CASE WHEN ("program_1"."degree" = "program_2"."degree") OR ("program_1"."degree" IS NULL AND "program_2"."degree" IS NULL) THEN 1 ELSE 0 END))
+                    LEFT OUTER JOIN (SELECT COUNT(1) AS "count",
+                                            "program"."school",
+                                            "program"."degree"
+                                     FROM "program" AS "program"
+                                          INNER JOIN "student" AS "student"
+                                                     ON (("program"."school" = "student"."school") AND ("program"."code" = "student"."program"))
+                                     GROUP BY 2, 3) AS "student"
+                                    ON (("program_1"."school" = "student"."school") AND (CASE WHEN ("program_1"."degree" = "student"."degree") OR ("program_1"."degree" IS NULL AND "student"."degree" IS NULL) THEN 1 ELSE 0 END))
+               WHERE ("school"."code" IN ('art', 'la'))
+               ORDER BY "school"."code" ASC, 1 ASC
+          - uri: /distinct(program{degree}?school={'art','la'}).^
+            status: 200 OK
+            headers:
+            - [Content-Type, text/plain; charset=UTF-8]
+            body: |2
+               | program{degree}?school={'art','la'}                                    |
+              -+------------------------------------------------------------------------+-
+               | school | code     | title                           | degree | part_of |
+              -+--------+----------+---------------------------------+--------+---------+-
+               | art    | uhist    | Bachelor of Arts in Art History | ba     |         |
+               | art    | ustudio  | Bachelor of Arts in Studio Art  | ba     |         |
+               | la     | uengl    | Bachelor of Arts in English     | ba     | gengl   |
+               | la     | uhist    | Bachelor of Arts in History     | ba     |         |
+               | la     | upolisci | Bachelor of Arts in Political   | ba     |         |
+               :        :          : Science                         :        :         :
+               | la     | upsych   | Bachelor of Arts in Psychology  | ba     |         |
+               | la     | uspan    | Bachelor of Arts in Spanish     | ba     |         |
+               | la     | psciwri  | Science Writing                 | ct     |         |
+               | la     | gengl    | Master of Arts in English       | ma     |         |
+               | la     | glang    | Master of Arts in Modern        | ma     |         |
+               :        :          : Languages                       :        :         :
+               | la     | gscitch  | Master of Arts in Science       | ma     |         |
+               :        :          : Teaching                        :        :         :
+               | art    | gart     | Post Baccalaureate in Art       | pb     |         |
+               :        :          : History                         :        :         :
+                                                                                (12 rows)
+
+               ----
+               /distinct(program{degree}?school={'art','la'}).^
+               SELECT "program_2"."school",
+                      "program_2"."code",
+                      "program_2"."title",
+                      "program_2"."degree",
+                      "program_2"."part_of"
+               FROM (SELECT "program"."degree"
+                     FROM "program" AS "program"
+                     WHERE ("program"."school" IN ('art', 'la'))
+                     GROUP BY 1) AS "program_1"
+                    INNER JOIN (SELECT "program"."school",
+                                       "program"."code",
+                                       "program"."title",
+                                       "program"."degree",
+                                       "program"."part_of"
+                                FROM "program" AS "program"
+                                WHERE ("program"."school" IN ('art', 'la'))) AS "program_2"
+                               ON (CASE WHEN ("program_1"."degree" = "program_2"."degree") OR ("program_1"."degree" IS NULL AND "program_2"."degree" IS NULL) THEN 1 ELSE 0 END)
+               ORDER BY "program_1"."degree" ASC, 1 ASC, 2 ASC
           - uri: /((program?school='art')^degree).limit(1)
             status: 200 OK
             headers: