Source

htsql-charset-option / 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'
    # Functional syntax
    - uri: /school.filter(code='ns')
    - uri: /department.filter(school.code={'art','la'})

  # 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
    # Functional syntax
    - uri: /school.select(name)
    - uri: /department.select(school.*, *)

  # 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))}
      ifndef: oracle # Oracle cannot handle EXISTS and an aggregate in
                     # the same SELECT clause.
    - 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
    # (broken due to changed selector semantics)
    - uri: /school.program{school, code, degree,
                           count(root(){(school?code='art').program.student})}
                           ?school~'a'
      skip: true
    - uri: /program.school{code, root().program{code, degree},
                           count(root(){(program?degree='ba').school.department})}
                           ?code~'a'
      skip: true
    - uri: /department{name,sum(course.credits)-,avg(course.credits)}?sum(course.credits)

  - 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
    # (broken due to changed selector semantics)
    - uri: /{count(school),this(){count(school)}?true(),this(){count(school)}?false()}
      skip: true
    - uri: /school{code,name,this(){code}?name!~'art',
                   root().school{code}?name!~'art'}
      skip: true
    - uri: /school{name,count(department),this(){count(department)}?name!~'art'}
      skip: true
    - uri: /school{name,exists(department),this(){exists(department)}?name!~'art'}
      skip: true
    # The `home()` function.
    # Empty selector
    - uri: /home()
      expect: 400
    - uri: /home(){2+2}
    - uri: /school.home(){2+2}
    - uri: /school.home().school{root().school.*, *}
    - uri: /(department?school='art').home().(department?school='la')
                {root().(department?school='art').*, *}
    - uri: /distinct(program{degree}?school='art').home().distinct(program{degree}?school='la')
                {root().distinct(program{degree}?school='art').*, *}
    - uri: /school{code,count(department)}
                ?count(department)>avg(home().school.count(department))

  - title: Table Expressions
    tests:
    - uri: /(school?code='art').department
    # An empty segment
    - uri: /{}
      expect: 400
    - uri: /school{}
      expect: 400

  - 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'
    - 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}
    - uri: /define(program.student:=student?is_active)
            .program{code,count(student)}?school='bus'
    - uri: /define(school.program.student:=student?is_active)
            .count((school?code='bus').program.student)
    - uri: /define(school.department.course:=course?credits>2,
                   school.department.course:=course?credits<5,
                   school.department:=department?code='acc')
            .school.department.course{department,no,title,credits}
    - uri: /define(double(x):=x*2){double(1),double(2),double(3)}
    - uri: /define(student.age(date):=(date(date)-date(dob))/365:round(1),
                   student.age():=age('2011-01-01'),
                   inactive_student:=student?!is_active)
            .inactive_student{name,dob,start_date,age(start_date),age()}
    - uri: /school{code,{count(student),
                         count(student?gender='m'),
                         count(student?gender='f')}
                        :where(student:=student?is_active)}
    - uri: /department.define(stats(c):={min(c.credits) :as min,max(c.credits) :as max})
                {stats(course), stats(course?no>=100&no<200)}?code='acc'

  - 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()
      skip: true # no longer works with new selector semantics, use `kernel(0)`
    - uri: /quotient(program, degree)?kernel(0)
    - uri: /quotient(program, degree){kernel()}?kernel()
      skip: true # no longer works with new selector semantics, use `kernel(0)`
    - uri: /quotient(program, degree){kernel()}?kernel(0)
    - uri: /quotient(program?false(), degree)
    - uri: /quotient(program?degree, degree)
    - uri: /quotient(program?false(), degree)?false()
    - uri: /quotient(program?degree, degree)?kernel()
      skip: true # no longer works with new selector semantics, use `kernel(0)`
    - uri: /quotient(program?degree, degree)?kernel(0)
    - 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()
      skip: true # no longer works with new selector semantics, use `kernel(0)`
    - uri: /school.quotient(program, degree)?kernel(0)
    - uri: /school.quotient(program, degree){kernel()}?kernel()
      skip: true # no longer works with new selector semantics, use `kernel(0)`
    - uri: /school.quotient(program, degree){kernel()}?kernel(0)
    - 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()
      skip: true # no longer works with new selector semantics, use `kernel(0)`
    - uri: /school.quotient(program?degree, degree)?kernel(0)
    - 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
      skip: true # no longer works with new selector semantics, use `kernel(0)`
    - uri: /quotient(course,credits)?kernel(0)>3
    - uri: /quotient(course,credits){kernel()-}
      skip: true # no longer works with new selector semantics, use `kernel(0)`
    - uri: /quotient(course,credits){kernel(0)-}
    - uri: /(quotient((department?code='stdart').course,credits)?kernel()>3)
                {*,count(complement())}
      skip: true # no longer works with new selector semantics, use `kernel(0)`
    - uri: /(quotient((department?code='stdart').course,credits)?kernel(0)>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)

    # 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()})
      skip: true
    - uri: /(school^{true()})
      skip: true
    # A bug `rewrite` is not applied to `base` of an ordered space.
    - uri: /((program?school='art')^degree).limit(1)