Full outer join and count(*) questions

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?


1 Like

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 Like

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 Like

1 Like

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 Like

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 Like


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 Like

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 Like

Tanks for the testing @Larry_Parker,

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


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 Like