Panic when attempting to join results from named queries

[philrz]

As a user, I tried to convert the CTEs in #6616 into named queries and use them in my join, but it caused a panic. If needed, the input data can be downloaded from https://super-issues.s3.us-east-2.amazonaws.com/super-6616/bench3.bsup.

$ super -version
Version: v0.1.0-15-gf891891be

$ super -c "
let room_use = (
  SELECT dow,
         hr,
         device_name,
         AVG(motions) AS in_use
  FROM (
    SELECT dt,
           dow,
           hr,
           device_name,
           COUNT(*) AS motions
    FROM (
      SELECT CAST(log_time AS DATE) AS dt,
             EXTRACT(DOW FROM log_time) AS dow,
             EXTRACT(HOUR FROM log_time) AS hr,
             device_name
      FROM 'bench3.bsup'
      WHERE device_name LIKE 'room%'
        AND event_type = 'motion_start'
        AND log_time >= TIMESTAMP '2019-09-01 00:00:00'
    ) AS r
    WHERE dow IN (1,2,3,4,5)
      AND hr BETWEEN 9 AND 16
    GROUP BY dt,
             dow,
             hr,
             device_name
  ) AS s
  GROUP BY dow,
           hr,
           device_name
  | put left_key := {device_name, in_use}
)

let min_room_use = (
  SELECT device_name,
        MIN(in_use) AS in_use
        FROM room_use
        GROUP BY device_name
  | put right_key := {device_name, in_use}
)

SELECT min_room_use.device_name,
       ru.dow,
       ru.hr,
       min_room_use.in_use
FROM room_use AS ru
JOIN min_room_use
  ON ru.left_key = min_room_use.right_key
ORDER BY min_room_use.device_name, ru.dow, ru.hr;"

panic: runtime error: index out of range [0] with length 0

goroutine 1 [running]:
github.com/brimdata/super/compiler/optimizer.firstThisPathComponent.func1({{0x12510087, 0x8}, {0x0, 0x0, 0x0}})
	/Users/phil/work/super/compiler/optimizer/join.go:167 +0x105
github.com/brimdata/super/compiler/optimizer.walkT[...]({0x12a818e0, 0xc0001f7650?, 0x10c9933f?}, 0xc00051d500?)
	/Users/phil/work/super/compiler/optimizer/optimizer.go:876 +0x275
github.com/brimdata/super/compiler/optimizer.walkT[...]({0x129cd660, 0xc0001f7650?, 0x12a818e0?}, 0xc00051d500?)
	/Users/phil/work/super/compiler/optimizer/optimizer.go:868 +0x12b
github.com/brimdata/super/compiler/optimizer.walkT[...]({0x129cd3e0, 0xc0001f76a0?, 0x18?}, 0xc00051d500?)
	/Users/phil/work/super/compiler/optimizer/optimizer.go:868 +0x12b
github.com/brimdata/super/compiler/optimizer.walkT[...]({0x12ad1be0, 0xc0001f7680?, 0xc00051c628?}, 0xc00051d500?)
	/Users/phil/work/super/compiler/optimizer/optimizer.go:871 +0x39c
github.com/brimdata/super/compiler/optimizer.walkT[...]({0x129808a0, 0xc0001f7680?, 0x199?}, 0xc00051d500?)
	/Users/phil/work/super/compiler/optimizer/optimizer.go:868 +0x12b
github.com/brimdata/super/compiler/optimizer.walkT[...]({0x129cd560, 0xc000468f60?, 0x10c99373?}, 0xc00051d500?)
	/Users/phil/work/super/compiler/optimizer/optimizer.go:868 +0x12b
github.com/brimdata/super/compiler/optimizer.walkT[...]({0x128bfba0, 0xc0001f76c0?, 0xc00051c8e8?}, 0xc00051d500?)
	/Users/phil/work/super/compiler/optimizer/optimizer.go:865 +0x40f
github.com/brimdata/super/compiler/optimizer.walkT[...]({0x12a813e0, 0xc0001f76b0?, 0x12c404f0?}, 0xc00051d500?)
	/Users/phil/work/super/compiler/optimizer/optimizer.go:871 +0x39c
github.com/brimdata/super/compiler/optimizer.walkT[...]({0x129811a0, 0xc0001f76b0?, 0x12ad1be0?}, 0xc00051d500?)
	/Users/phil/work/super/compiler/optimizer/optimizer.go:868 +0x12b
github.com/brimdata/super/compiler/optimizer.walkT[...]({0x129cd3e0, 0xc000468f70?, 0xc000b676c8?}, 0xc00051d500?)
	/Users/phil/work/super/compiler/optimizer/optimizer.go:868 +0x12b
github.com/brimdata/super/compiler/optimizer.walkT[...]({0x128bfb60, 0xc0001f76f0?, 0xc00051cce8?}, 0xc00051d500?)
	/Users/phil/work/super/compiler/optimizer/optimizer.go:865 +0x40f
github.com/brimdata/super/compiler/optimizer.walkT[...]({0x12a81ac0, 0xc0001f76e0?, 0x5ae933e8?}, 0xc00051d500?)
	/Users/phil/work/super/compiler/optimizer/optimizer.go:871 +0x39c
github.com/brimdata/super/compiler/optimizer.walkT[...]({0x129818c0, 0xc0001f76e0?, 0x128bfb20?}, 0xc00051d500?)
	/Users/phil/work/super/compiler/optimizer/optimizer.go:868 +0x12b
github.com/brimdata/super/compiler/optimizer.walkT[...]({0x129cd4e0, 0xc000330820?, 0x12807100?}, 0xc00051d500?)
	/Users/phil/work/super/compiler/optimizer/optimizer.go:868 +0x12b
github.com/brimdata/super/compiler/optimizer.walkT[...]({0x129122e0, 0xc0001fb278?, 0x10c379f4?}, 0xc00051d500?)
	/Users/phil/work/super/compiler/optimizer/optimizer.go:865 +0x40f
github.com/brimdata/super/compiler/optimizer.walkT[...]({0x12ad22a0, 0xc0001fb260?, 0x12ad22a0?}, 0xc00051d500?)
	/Users/phil/work/super/compiler/optimizer/optimizer.go:871 +0x39c
github.com/brimdata/super/compiler/optimizer.walkT[...]({0x12981620, 0xc0001fb260?, 0x10c9933f?}, 0xc00051d500?)
	/Users/phil/work/super/compiler/optimizer/optimizer.go:868 +0x12b
github.com/brimdata/super/compiler/optimizer.walkT[...]({0x129cd3e0, 0xc0001fb2a0?, 0x12a818e0?}, 0xc00051d500?)
	/Users/phil/work/super/compiler/optimizer/optimizer.go:868 +0x12b
github.com/brimdata/super/compiler/optimizer.walkT[...]({0x12ad1b20, 0xc0001fb290?, 0xc00051d468?}, 0xc00051d500?)
	/Users/phil/work/super/compiler/optimizer/optimizer.go:871 +0x39c
github.com/brimdata/super/compiler/optimizer.walkT[...]({0x129807e0, 0xc0001fb290?, 0xc000600808?}, 0xc00051d500?)
	/Users/phil/work/super/compiler/optimizer/optimizer.go:868 +0x12b
github.com/brimdata/super/compiler/optimizer.firstThisPathComponent({0x12c57ea0?, 0xc0001fb290?})
	/Users/phil/work/super/compiler/optimizer/join.go:163 +0x9f
github.com/brimdata/super/compiler/optimizer.equiJoinKeyExprs({0x12c57ec0?, 0xc0005cd800?}, {0x1250ab86, 0x4}, {0x1250b77a, 0x5})
	/Users/phil/work/super/compiler/optimizer/join.go:141 +0xc5
github.com/brimdata/super/compiler/optimizer.replaceJoinWithHashJoin.func1({0x12c57c00, 0xc0006fe8c0})
	/Users/phil/work/super/compiler/optimizer/join.go:90 +0x11c
github.com/brimdata/super/compiler/optimizer.walkT[...]({0x129cd4e0, 0xc000001510?, 0xc00051d7c0?}, 0x12c404f0?)
	/Users/phil/work/super/compiler/optimizer/optimizer.go:876 +0x192
github.com/brimdata/super/compiler/optimizer.walkT[...]({0x129122e0, 0xc000b61f68?, 0x129122e0?}, 0x12c404f0?)
	/Users/phil/work/super/compiler/optimizer/optimizer.go:865 +0x2a9
github.com/brimdata/super/compiler/optimizer.replaceJoinWithHashJoin({0xc000001500?, 0x7?, 0x8?})
	/Users/phil/work/super/compiler/optimizer/join.go:83 +0x58
github.com/brimdata/super/compiler/optimizer.(*Optimizer).Optimize(0xc00051d8c0, 0xc00035ec30)
	/Users/phil/work/super/compiler/optimizer/optimizer.go:140 +0x90
github.com/brimdata/super/compiler.Optimize({0x12c73340?, 0xc00046f9e0?}, 0xc00035ec30, 0xc0001cb280?, 0xc)
	/Users/phil/work/super/compiler/package.go:31 +0x67
github.com/brimdata/super/compiler.CompileWithAST(0xc00046f9e0, 0x10dc7c45?, 0xc0001cb280, 0x1, 0xc, {0x0, 0x0, 0x0})
	/Users/phil/work/super/compiler/package.go:70 +0xa5
github.com/brimdata/super/compiler.(*compiler).NewQuery(0x12c733e8?, 0xc00038cb90?, 0xc00054d9f0?, {0x0?, 0x90?, 0x12bedb20?}, 0xc00046f4d0?)
	/Users/phil/work/super/compiler/compiler.go:42 +0x45
github.com/brimdata/super/runtime.CompileQuery({0x12c733e8?, 0xc00038cb90?}, 0xc00013b560, {0x12c603a0, 0xc000114110}, 0xc00046ce00, {0x0, 0x0, 0x0})
	/Users/phil/work/super/runtime/compiler.go:34 +0x10f
github.com/brimdata/super/cmd/super/root.(*Command).Run(0xc0003866c8, {0xc00003e8f0, 0x0, 0x0})
	/Users/phil/work/super/cmd/super/root/command.go:95 +0x551
github.com/brimdata/super/pkg/charm.path.run({0xc000088968, 0x1, 0x1}, {0xc00003e8f0, 0x0, 0x0?})
	/Users/phil/work/super/pkg/charm/path.go:11 +0x7b
github.com/brimdata/super/pkg/charm.(*Spec).Exec(0x13e0b2c0, {0xc00003e8e0, 0x2, 0x2})
	/Users/phil/work/super/pkg/charm/charm.go:74 +0x1fa
main.main()
	/Users/phil/work/super/cmd/super/main.go:39 +0x5b

[mccanne]

Can repro with

let min_room_use = (
   SELECT 1 as right_key
)
SELECT 1
FROM ( SELECT 1 as left_key) AS ru
JOIN min_room_use
  ON ru.left_key = min_room_use.right_key

The problem is min_room_use is getting bound to the named query as an expression subquery instead of the SQL table in the ON expression. That's easy to fix but I also wonder if the optimizer should guard against the panic it is causing.