Full outer join and count(*) questions

Larry Parker

August 11, 2020 21:10

Looks like there is currently no support for FULL 0UTER JOIN. Are there plans for this in the future?

Also, SELECT COUNT(*) FROM my_table returns 0, but there are millions of rows in it. Any ideas on what could be wrong?

Thanks. -Larry

Comments

9 comments

Candido Dessanti

August 12, 2020 08:37
Hi @Larry_Parker,

Thank for joining the community forum.

About you questions. We are committed to make omnisci db as complete as possible so it's likely that full outer join will land in a future release, but I can't say exactly when, because the resources aren't unlimited.

About the count(*) that is returning 0 on a table containing records, I can day that really strange, because I am almost sure it's using just the Metadata to return the result. Can I ask you which verison/edition and how you installed the database and loaded the data?
1

Comment actions Permalink
Larry Parker

August 12, 2020 13:21
We installed omnisci /stable,now 5.3.2-20200803-a970373210 amd64 [installed], and I'm using omnisci-jdbc-5.3.2.jar via SQL Workbench/J.

Also, I can create a table in SQL Workbench, add a row, and see the results in their Database Explorer tab, but when I query the results through the same session that created the table and added the row, I do not see any results.
```
CREATE TABLE lparker_test
(
   id    INT        NOT NULL,
   name  varchar(10)   NOT NULL
);

insert into lparker_test
values (1, 'test');

select * from lparker_test
```
1

Comment actions Permalink
Larry Parker

August 12, 2020 13:23
1

Comment actions Permalink
Candido Dessanti

August 12, 2020 13:55
Are you getting those results also witb omnisql command line tool?

To use it you should log into the machine where you installed the database and launch the omnisql command that located on bin subdirectory where you installed the binaries
```
omnisql -p HyperInteractive
```
Should log you into database with user admin to the data as e omnisci (it look you are using this dabase in your tests).

I will check of there are some problems using sqlworkbench (I use dbeaver and Tableau with jdbc driver) ASAP
1

Comment actions Permalink
Larry Parker

August 12, 2020 14:16
Everything seems to be working from the omnisql command line tool, and seems to be working from Python as well using from pymapd import connect. Could I be running into a JDBC problem? I'll take SQL Workbench/J out of the loop next and write a Java test program to validate this.
1

Comment actions Permalink
Candido Dessanti

August 12, 2020 14:20
Well,

A generic jdbc error is unlikely; while I am mostly using a customized driver to get better performances with some Tableau expression, the core of the driver is the original one and I don't get any of those errors using dbeaver or squirrelsql, so probably it's a problem of the combo driver/sql workbench
1

Comment actions Permalink
Larry Parker

August 12, 2020 14:47
Confirmed that it's working in my Java program through JDBC, and also working properly in DBeaver. So certainly points to a SQL Workbench/J problem, but not sure what.
1

Comment actions Permalink
Candido Dessanti

August 12, 2020 17:23
Tanks for the testing @Larry_Parker,

I guess we have to talk with the guy that's developing that software to fix this bug.
1

Comment actions Permalink
Candido Dessanti

August 21, 2020 14:42
Hi @Larry_Parker,

I double checked the problem with Sql Workbench/J and it looks there isn't any problem between the tools and the driver that leads to empty results.

I have found a parameter that default to 0, so the query you run is instructed to return no rows;

so changing in the Data dispay section of your connection Settings, will fix the issue
1

Comment actions Permalink

Please sign in to leave a comment.

Comments

Didn't find what you were looking for?