Complex CTE usage

[alexggordon]

Hello,

Just a quick question/maybe a bug with CTEs. Stupid example, but I put together a quick script demonstrating the (multiple) possible issue(s). Basically--after making a CTE, I'd like to do a custom join on it, or a UNION would work--but it appears neither of them are currently fully supported, and both result in some weird output/errors. See the below script for a demonstration.

I was wondering if either UNION or a join on a CTE is supposed to be supported?

#!/bin/sh

echo "id,first_name,last_name\n1,alex,gordon\n2,someone,else\n" > test1.csv
echo "id,owner_id,make,model\n1,1,ford,explorer\n2,1,ford,focus" > test2.csv
echo "id,owner_id,dog_name,dog_breed\n1,1,rusty,labrador\n2,2,oreo,shelty" > test3.csv

# passes validations, query output is maybe a bug?
# +----+------------+-----------+-----------+----------+---+---+
# | id | first_name | last_name | the_count | owner_id |   |   |
# +----+------------+-----------+-----------+----------+---+---+
# |  1 | 'alex'     | 'gordon'  |         2 |        1 | 1 | 1 |
# |  1 | 'alex'     | 'gordon'  |         2 |        1 | 1 | 2 |
# |  2 | 'someone'  | 'else'    |         2 |        1 | 1 | 1 |
# |  2 | 'someone'  | 'else'    |         2 |        1 | 1 | 2 |
# +----+------------+-----------+-----------+----------+---+---+
octosql "
WITH
car_counts_by_owner as (
	select count(*) as the_count, owner_id from test2.csv
	group by owner_id
),
dog_counts_by_owner as (
	select count(*) as the_count, owner_id from test3.csv
	group by owner_id
),

select *
from test1.csv
join car_counts_by_owner
join dogs_by_owner
group by id
" || true


# does not work
#   Error: typecheck error: unknown variable: 'ccbo.owner_id'
octosql "
WITH
car_counts_by_owner as (
	select count(*) as the_count, owner_id from test2.csv
	group by owner_id
),
dog_counts_by_owner as (
	select count(*) as the_count, owner_id from test3.csv
	group by owner_id
),

select *
from test1.csv
join car_counts_by_owner ccbo on test1.id = ccbo.owner_id
join dog_counts_by_owner dcbo on test1.id = dcbo.owner_id
group by id
" || true

# does not work
#   Error: couldn't parse query: couldn't parse underlying select in WITH statement: unsupported select &{Type:union Left:0x140003cede0 Right:0x140003cee10 OrderBy:[] Limit:<nil> Lock:} of type *sqlparser.Union
octosql "
WITH
car_counts_by_owner as (
	select count(*) as the_count, owner_id from test2.csv
	group by owner_id
),
dog_counts_by_owner as (
	select count(*) as the_count, owner_id from test3.csv
	group by owner_id
),

(select * from car_counts_by_owner)
union
(select * from dog_counts_by_owner)
" || true

rm test1.csv
rm test2.csv
rm test3.csv

Thanks for your great work on this project!