Auto-coersing subqueries results when used with operators

[therapon]

Query

SELECT e.name AS employeeName
FROM hr.employeesNest AS e
WHERE ( SELECT COUNT(*)
        FROM e.projects AS p
        WHERE p.name LIKE '%security%') > 1

Data

{ 
  'hr': { 
      'employeesNest': <<
         { 
          'id': 3, 
          'name': 'Bob Smith', 
          'title': null, 
          'projects': [ { 'name': 'AWS Redshift Spectrum querying' },
                        { 'name': 'AWS Redshift security' },
                        { 'name': 'AWS Aurora security' }
                      ]
          },
          { 
              'id': 4, 
              'name': 'Susan Smith', 
              'title': 'Dev Mgr', 
              'projects': [] 
          },
          { 
              'id': 6, 
              'name': 'Jane Smith', 
              'title': 'Software Eng 2', 
              'projects': [ { 'name': 'AWS Redshift security' } ] 
          }
      >>
    }
}

REPL returns

   | FROM hr.employeesNest e
   | WHERE ( SELECT VALUE COUNT(*)
   | FROM e.projects AS p
   | WHERE p.name LIKE '%security%') > 1 
   | 
org.partiql.lang.eval.EvaluationException: COUNT(*) is not allowed in this context
	<UNKNOWN>: at line 3, column 22: <UNKNOWN>

	at org.partiql.lang.eval.ExceptionsKt.err(Exceptions.kt:56)
	at org.partiql.lang.eval.EvaluatingCompiler.compileCallAgg(EvaluatingCompiler.kt:1064)
	at org.partiql.lang.eval.EvaluatingCompiler.compileExprNode(EvaluatingCompiler.kt:242)
	at org.partiql.lang.eval.EvaluatingCompiler.access$compileExprNode(EvaluatingCompiler.kt:50)
	at org.partiql.lang.eval.EvaluatingCompiler$compileSelect$1.invoke(EvaluatingCompiler.kt:883)
	at org.partiql.lang.eval.EvaluatingCompiler$compileSelect$1.invoke(EvaluatingCompiler.kt:50)
	at org.partiql.lang.eval.EvaluatingCompiler.nestCompilationContext(EvaluatingCompiler.kt:71)
	at org.partiql.lang.eval.EvaluatingCompiler.compileSelect(EvaluatingCompiler.kt:723)
	at org.partiql.lang.eval.EvaluatingCompiler.compileExprNode(EvaluatingCompiler.kt:241)
	at org.partiql.lang.eval.EvaluatingCompiler.compileNAry(EvaluatingCompiler.kt:248)
	at org.partiql.lang.eval.EvaluatingCompiler.compileExprNode(EvaluatingCompiler.kt:233)
	at org.partiql.lang.eval.EvaluatingCompiler.compileQueryWithoutProjection(EvaluatingCompiler.kt:1211)
	at org.partiql.lang.eval.EvaluatingCompiler.access$compileQueryWithoutProjection(EvaluatingCompiler.kt:50)
	at org.partiql.lang.eval.EvaluatingCompiler$compileSelect$1.invoke(EvaluatingCompiler.kt:727)
	at org.partiql.lang.eval.EvaluatingCompiler$compileSelect$1.invoke(EvaluatingCompiler.kt:50)
	at org.partiql.lang.eval.EvaluatingCompiler.nestCompilationContext(EvaluatingCompiler.kt:71)
	at org.partiql.lang.eval.EvaluatingCompiler.compileSelect(EvaluatingCompiler.kt:723)
	at org.partiql.lang.eval.EvaluatingCompiler.compileExprNode(EvaluatingCompiler.kt:241)
	at org.partiql.lang.eval.EvaluatingCompiler.access$compileExprNode(EvaluatingCompiler.kt:50)
	at org.partiql.lang.eval.EvaluatingCompiler$compile$thunk$1.invoke(EvaluatingCompiler.kt:186)
	at org.partiql.lang.eval.EvaluatingCompiler$compile$thunk$1.invoke(EvaluatingCompiler.kt:50)
	at org.partiql.lang.eval.EvaluatingCompiler.nestCompilationContext(EvaluatingCompiler.kt:71)
	at org.partiql.lang.eval.EvaluatingCompiler.compile(EvaluatingCompiler.kt:185)
	at org.partiql.lang.CompilerPipelineImpl.compile(CompilerPipeline.kt:174)
	at org.partiql.lang.CompilerPipelineImpl.compile(CompilerPipeline.kt:164)
	at org.partiql.cli.Repl$executePartiQL$1.invoke(Repl.kt:237)
	at org.partiql.cli.Repl$executePartiQL$1.invoke(Repl.kt:105)
	at org.partiql.cli.Repl$executeTemplate$totalMs$1.invoke(Repl.kt:208)
	at org.partiql.cli.Repl$executeTemplate$totalMs$1.invoke(Repl.kt:105)
	at org.partiql.cli.Timer$DefaultImpls.timeIt(Repl.kt:95)
	at org.partiql.cli.Repl$1.timeIt(Repl.kt:111)
	at org.partiql.cli.Repl.executeTemplate(Repl.kt:207)
	at org.partiql.cli.Repl.executePartiQL(Repl.kt:233)
	at org.partiql.cli.Repl.run(Repl.kt:300)
	at org.partiql.cli.Main.runRepl(main.kt:154)
	at org.partiql.cli.Main.main(main.kt:139)
ERROR!

[therapon]

This behaviour might also be relevant for this issue.

Using the same data set as above the below query

SELECT e.name AS employeeName, 
       ( SELECT COUNT(*)
         FROM e.projects AS p
         WHERE p.name LIKE '%querying%'
       ) AS queryProjectsNum
FROM hr.employeesNest AS e

Should return

  {
    'employeeName': 'Bob Smith',
    'queryProjectsNum': 1
  },
  {
    'employeeName': 'Susan Smith',
    'queryProjectsNum': 0
  },
  {
    'employeeName': 'Jane Smith',
    'queryProjectsNum': 0
  }
>>
--- 

but instead we get

===' 
<<
  {
    'employeeName': 'Bob Smith',
    'queryProjectsNum': <<
      {
        '_1': 1
      }
    >>
  },
  {
    'employeeName': 'Susan Smith',
    'queryProjectsNum': <<
      {
        '_1': 0
      }
    >>
  },
  {
    'employeeName': 'Jane Smith',
    'queryProjectsNum': <<
      {
        '_1': 0
      }
    >>
  }
>>
--- 
OK! (18 ms)

queryProjectsNum is bound to a bag and not an integer

[johnedquinn]

While this may be addressed, a temporary work-around to unblock any potential users would be to cast the output of SELECT (which is a bag) to a list and grabbing the first element (since we know COUNT will always return a single value). Unblocked me while going through the tutorial.

SELECT
  e.name AS employeeName
FROM
  hr.employeesNest AS e
WHERE
  CAST ((SELECT VALUE ct FROM (SELECT COUNT(*) AS ct FROM e.projects AS p WHERE p.name LIKE '%security')) AS list)[0]  > 1;

This provides the correct result:

<<
  {
    'employeeName': 'Bob Smith'
  }
>>

A similar approach can be taken for the subsequent example. Just a work-around while this is figured out.