Multiple joins?

[devfacet]

Is there a support for multiple joins? I couldn't make it work and want make sure whether it's supported or not.

[cznic]

For example

$ ql 'create table a (i int, key int)'
$ ql 'create table b (i int, key int)'
$ ql 'create table c (i int)'
$ ql 'insert into c values(100)'
$ ql 'insert into b select 200, id() from c'
$ ql 'insert into a select 300, id() from b'
$ ql -fld 'select * from a, b, c where a.key == id(b) && b.key == id(c)'
"a.i", "a.key", "b.i", "b.key", "c.i"
300, 2, 200, 1, 100
$ 

[devfacet]

Sorry for not being more specific. I was talking about {LEFT,RIGHT,FULL} [OUTER] JOIN

for example;

SELECT
  product.key,
  category.name,
  condition.name
FROM
  product
LEFT JOIN category ON category.key = product.catkey
LEFT JOIN condition ON condition.key = product.condkey;

"product.key", "category.name", "condition.name"
1               foo              bar
2               hello            <nil>
3               <nil>            baz
4               <nil>            <nil>

[devfacet]

fyi - currently I'm using temporary tables in a transaction block. Basically joining tables one by one and it's little bit extra work.

[cznic]

The documented grammar does not allow nesting of the join clause. That should be fixed (PRs weclome 😄).

AFAICT, something like this should work

drop table if exists category;
drop table if exists condition;
drop table if exists product;

create table category (key int, name string);
create table condition (key int, name string);
create table product (key int, catkey int, condkey int);

insert into category values (1, "foo"), (2, "hello");
insert into condition values (1, "bar"), (2, "baz");
insert into product values (1, 1, 1), (2, 2, null), (3, null, 2), (4, null, null);

select *
from
  (select
   product.key as product_key,
   category.name as category_name,
   product.condkey as product_condkey
   from
     product
   left join category on category.key == product.catkey)
left join condition on condition.key == product_condkey;

but it does not work

$ ql -fld < join.ql 
"", "", "", "condition.key", "condition.name"
2016/09/14 12:14:38 unknown field product_condkey
$ 

The problem seems to be that the inner join does not output the proper field names so the outer join fails. I will fill a separate issue for this.

[devfacet]

Yes I tired the way you tired and it didn't worked. I thought it wasn't supported. That's why I'm joining them one by one by creating temporary tables. Unfortunately I'm quite busy for a PR atm. But I will keep it in my mind and try it when I have a chance. Sorry about that :(

[gernest]

@cznic @devfacet I would like to look into this. Is this still an I issue.

If thee is already an ongoing effort, I will be more than happy to help. Unfortunate I suck at SQL so, insight and direction where to look for stuff will be highly appreciated.

Thanks.

[gernest]

gentle ping @cznic

can yuo please tell me what is the expected result from this ?

drop table if exists category;
drop table if exists condition;
drop table if exists product;

create table category (key int, name string);
create table condition (key int, name string);
create table product (key int, catkey int, condkey int);

insert into category values (1, "foo"), (2, "hello");
insert into condition values (1, "bar"), (2, "baz");
insert into product values (1, 1, 1), (2, 2, null), (3, null, 2), (4, null, null);

select *
from
  (select
   product.key as product_key,
   category.name as category_name,
   product.condkey as product_condkey
   from
     product
   left join category on category.key == product.catkey)
left join condition on condition.key == product_condkey;

I'm looking at this issue

[cznic]

can yuo please tell me what is the expected result from this ?

I'm not completely sure, unfortunately. Can you please try what, with required modifications, the results are in some other RDBMS? We would then at least know what the test cases should test against...

[gernest]

@cznic no problem . I think I found out a way to solve this.

I fixed the fields now i get

"product_key", "category_name", "product_condkey", "condition.key", "condition.name"

I will cleanup and write more details after more tests

[gernest]

another gentle ping @cznic @devfacet

On my dev branch

$ ql -fld < join.ql
"product_key", "category_name", "product_condkey", "condition.key", "condition.name"
4, <nil>, <nil>, <nil>, <nil>
3, <nil>, 2, 2, "baz"
2, "hello", <nil>, <nil>, <nil>
1, "foo", 1, 1, "bar"

contents of join.ql

drop table if exists category;
drop table if exists condition;
drop table if exists product;

create table category (key int, name string);
create table condition (key int, name string);
create table product (key int, catkey int, condkey int);

insert into category values (1, "foo"), (2, "hello");
insert into condition values (1, "bar"), (2, "baz");
insert into product values (1, 1, 1), (2, 2, null), (3, null, 2), (4, null, null);

select *
from
  (select
   product.key as product_key,
   category.name as category_name,
   product.condkey as product_condkey
   from
     product
   left join category on category.key == product.catkey)
left join condition on condition.key == product_condkey;

[devfacet]

@gernest : After this issue (as well as some other performance issues) I gave up this package and decided to use go-sqlite3 package . The only annoying thing with go-sqlite3 is that it is a cgo package. That being said @cznic is working on something else may solve that issue. See https://github.com/cznic/sqlite

If you need an embedded SQL database in Go (for production purposes) then either use https://github.com/mattn/go-sqlite3 or try https://github.com/cznic/ql (I'll try it at one point.)

[gernest]

@devfacet thanks for the recommendation. At the moment I would like to improve ql hopefully it will be production ready one day.