htsql-sqlascii-patch / test / regress / input / translation.yaml

#
# Copyright (c) 2006-2011, Prometheus Research, LLC
# Authors: Clark C. Evans <cce@clarkevans.com>,
#          Kirill Simonov <xi@resolvent.net>
#

title: Edge Cases of HTSQL-to-SQL Translation
id: translation
tests:

# FIXME: update and refurbish!

- title: Random collection of tests
  tests:

  # Simple (non-aggregate) filters.
  - title: Simple filters
    tests:
    - uri: /school?code='ns'
    - uri: /department?school.code='ns'
    - uri: /department?school.code={'art','la'}
    - uri: /program?school.code='ns'&code='uchem'
    - uri: /course?credits=5
    - uri: /department?school
    # ENUM literal.
    - uri: /semester?season='fall'
    # Using the same term for a filter and a selector
    - uri: /program{school.name,title}?school.code='art'

  # Simple (non-aggregate) selectors.
  - title: Simple selectors
    tests:
    - uri: /school{name}
    - uri: /department{school.*,*}
    - uri: /department{school.name+' - '+name}
    # Empty selectors are broken
    - uri: /{}
      skip: true
    - uri: /school{}
      skip: true

  # Plural links and aggregates.
  - title: Aggregates
    tests:
    - uri: /exists(school)
    - uri: /count(school)
    - uri: /exists(school?exists(department))
    - uri: /count(school?exists(department))
    - uri: /{exists(school?!exists(department)),
             count(school?!exists(department))}
    - uri: /{count(course),min(course.credits),
                           max(course.credits),
                           avg(course.credits)}
    - uri: /{count(school),count(department),count(course)}
    - uri: /{count(department),count(department?exists(course))}
    - uri: /department{code,count(course{credits=3})}
    - uri: /department{code,count(course?credits=3)}
    - uri: /school{code,count(department.course{credits=3})}
    - uri: /school{code}?count(department.course{credits=3})=20
    - uri: /department?exists(course.credits=5)
    - uri: /department?every(course.credits=5)
    - uri: /department{code,min(course.credits),max(course.credits)}
    - uri: /department{code,avg(course.credits),
                            sum(course.credits)/count(course.credits)}
      skip: true
    - uri: /department?exists(course)
    - uri: /school?!exists(department)
    - uri: /school{*,count(department)}
    - uri: /school{*,count(department?exists(course))}
    # MSSQL does not allow the argument of an aggregate to contain
    # a subquery.
    - uri: /school{*,count(department.exists(course))}
      ifndef: mssql
    - uri: /school{code,count(department),count(program)}
    - uri: /school{code,exists(department),exists(program)}
      skip: true # broken until `reduce` is implemented
    # Aggregates sharing the same spaces.
    - uri: /department{sum(course.credits),count(course.credits),
                       avg(course.credits)}?exists(course)
    # Aggregates with non-native spaces.
    # Triggers a bug in the Postgresql optimizer
    # (see http://archives.postgresql.org/pgsql-bugs/2010-09/msg00265.php).
    - uri: /department{code,school.code,
                       count(school.program),school.count(program)}
    - uri: /department{code,school.code,
                       exists(school.program),school.exists(program)}
    # Ensure that aggregates are not lost during the reduction process
    - uri: /{count(school)&false(),count(school)|true()}
    - uri: /{count(school)==null()}
    # Reduction of aggregates to the scalar space.
    - uri: /this(){count(school)}
    - uri: /this(){count(school)}?false()
    - uri: /this(){count(school)}?exists(school)
    - uri: /this(){count(school)}?count(school)>1
    # Unmasking and bundling aggregate units
    - uri: /school{code,count(program)}?count(program)>3
    # Merging terms with parallel ties
    - uri: /school.program{school, code, degree,
                           count(root(){(school?code='art').program.student})}
                           ?school~'a'
    - uri: /program.school{code, root().program{code, degree},
                           count(root(){(program?degree='ba').school.department})}
                           ?code~'a'

  - title: Root, This, Direct and Fiber functions
    tests:
    # Cross joins.
    - uri: /{count(school)*count(department),count(school.fiber(department))}
    # Custom joins.
    - uri: /{count(school.department),
             count(school.fiber(department)?school=root().school.code),
             count(school.fiber(department,code,school.code))}
    - uri: /school{code,count(department)}?count(department)=max(fiber(school).count(department))
    - uri: /school.program{school,code,count(student)}?count(student)=max(fiber(program,school).count(student))
    # Lifting a unit to a dominating space.
    - uri: /{count(school),this(){count(school)}?true(),this(){count(school)}?false()}
    - uri: /school{code,name,this(){code}?name!~'art',
                   root().school{code}?name!~'art'}
    - uri: /school{name,count(department),this(){count(department)}?name!~'art'}
    - uri: /school{name,exists(department),this(){exists(department)}?name!~'art'}

  - title: Table Expressions
    tests:
    - uri: /(school?code='art').department

  - title: Assignments
    tests:
    - uri: /school.define(c:=department.course.credits)
                {code,min(c),max(c),sum(c),avg(c)}?exists(c)
    - uri: /school.define(program:=(program?degree='bs')
                                .define(student:=student?is_active))
                {code,count(program),count(program.student)}?code='eng'
    # FIXME?
    - uri: /school{code,count(program.student),
                   count((program.define(s:=student)?degree='bs').s),
                   count((program?degree='bs').define(s:=student).s)}?code='eng'
    - uri: /define(x:=2,y:=3,z:=x*y){x,y,z}
    - uri: /define(x:=2,x:=x+x,x:=x*x){x}

  - title: Projections
    tests:
    - uri: /quotient(program, degree)
    - uri: /quotient(program, degree){*}
    - uri: /quotient(program, degree){kernel()}
    - uri: /quotient(program, degree)?false()
    - uri: /quotient(program, degree){kernel()}?false()
    - uri: /quotient(program, degree)?kernel()
    - uri: /quotient(program, degree){kernel()}?kernel()
    - uri: /quotient(program?false(), degree)
    - uri: /quotient(program?degree, degree)
    - uri: /quotient(program?false(), degree)?false()
    - uri: /quotient(program?degree, degree)?kernel()
    - uri: /quotient(program, degree){kernel(),count(complement())}
      skip: true
    - uri: /quotient(program, degree){kernel(),count(complement().student)}
    - uri: /quotient(student, year(dob), gender)
    - uri: /quotient(student, {year(dob), gender})
    - uri: /quotient(student, {year(dob), gender}){kernel(),kernel(0),kernel(1)}
    - uri: /quotient(student, {year(dob), gender}){kernel(),count(complement())}
    - uri: /quotient(quotient(student, year(dob)).complement(), gender)
                {kernel(), count(complement()), count(complement().is_active)}
    - uri: /quotient(quotient(student, year(dob), gender), kernel(0))
                {kernel(), count(complement()), count(complement().complement()),
                 count(complement().complement().is_active)}
    - uri: /school.quotient(program, degree)
    - uri: /school.quotient(program, degree){*}
    - uri: /school.quotient(program, degree){kernel()}
    - uri: /school.quotient(program, degree)?false()
    - uri: /school.quotient(program, degree){kernel()}?false()
    - uri: /school.quotient(program, degree)?kernel()
    - uri: /school.quotient(program, degree){kernel()}?kernel()
    - uri: /school.quotient(program?false(), degree)
    - uri: /school.quotient(program?degree, degree)
    - uri: /school.quotient(program?false(), degree)?false()
    - uri: /school.quotient(program?degree, degree)?kernel()
    - uri: /school.quotient(program, degree){kernel(),count(complement())}
    - uri: /school.quotient(program, degree){kernel(),count(complement().student)}

    - uri: /school.program
            .quotient(student, year(dob), gender)
    - uri: /school.program
            .quotient(student, {year(dob), gender})
    - uri: /school.program
            .quotient(student, {year(dob), gender}){kernel(),kernel(0),kernel(1)}
    - uri: /school.program
            .quotient(student, {year(dob), gender}){kernel(),count(complement())}
    - uri: /school.program
            .quotient(quotient(student, year(dob)).complement(), gender)
                {kernel(), count(complement()), count(complement().is_active)}
    - uri: /school.program
            .quotient(quotient(student, year(dob), gender), kernel(0))
                {kernel(), count(complement()), count(complement().complement()),
                 count(complement().complement().is_active)}

    - uri: /quotient(program,degree){kernel(),count(complement())}?count(complement())>5
    - uri: /quotient(course,credits)?kernel()>3
    - uri: /quotient(course,credits){kernel()-}
    - uri: /(quotient((department?code='stdart').course,credits)?kernel()>3)
                {*,count(complement())}
    - uri: /quotient(school,count(program)){kernel(),count(complement())}
    - uri: /school.quotient(program, degree)
    - uri: /department.quotient(school.program, degree)
    - uri: /department.quotient((school?code='art').program, degree)
    - uri: /quotient(program,degree).complement()
    - uri: /quotient(program.sort(count(student)),degree).complement()
                {*, count(student)}
    - uri: /quotient(program,degree){*,exists(complement()),count(complement())}
    - uri: /school{code,count(program),count(quotient(program,degree))}
    - uri: /school{code,count(program),count(program^degree)}
    - uri: /(program^degree){*,count(^),count(^.student)}
    - uri: /(student^{year(dob),gender}){*,count(^),count(^?is_active)}
    - uri: /count(program^degree)
    - uri: /(school^{})
      skip: true
    - uri: /(school^{null()})
      skip: true
    - uri: /(school^{true()})
      skip: true
Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.