# Issue with Beekeeper Postgres client

**URL:** <https://community.cratedb.com/t/issue-with-beekeeper-postgres-client/1588>\
**Category:** CrateDB\
**Created:** [September 9, 2023, 7:21am UTC](https://community.cratedb.com/t/issue-with-beekeeper-postgres-client/1588 "2023-09-09T07:21:53Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![cocoa](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/cocoa/32/783_2.png) [@cocoa](https://community.cratedb.com/u/cocoa)\
**Post date:** [September 9, 2023, 7:21am UTC](https://community.cratedb.com/t/issue-with-beekeeper-postgres-client/1588/1 "2023-09-09T07:21:53Z")

</div>

Hello everyone,

I’m trying to use the application [Beekeper Studio community edition](https://www.beekeeperstudio.io) , but when I connect to CrateDB I get a connection error.

I access the ‘Doc’ schema as a ‘crate’ superuser.  
The default port is open.  
CrateDB runs as a cluster using docker. There is a container every host and all vista are connected with docker ‘host’ network.

`Error:`

```plaintext
Could not create execution plan from logical plan because of: Couldn't find (SELECT 1 FROM (el)) in SourceSymbols{inputs={typelem=INPUT(4), typnamespace=INPUT(2), nspname=INPUT(5), oid=INPUT(1), typrelid=INPUT(3), typname=INPUT(0), oid=INPUT(6)}, nonDeterministicFunctions={}}:
Eval[nspname AS schema, typname AS typename, oid AS typeid]
  └ Filter[((typrelid = 0) OR (SELECT (relkind = 'c') FROM (c)))]
    └ CorrelatedJoin[typname, oid, typnamespace, typrelid, typelem, nspname, oid, (SELECT (relkind = 'c') FROM (c)), (SELECT 1 FROM (el))]
      └ CorrelatedJoin[typname, oid, typnamespace, typrelid, typelem, nspname, oid, (SELECT (relkind = 'c') FROM (c))]
        └ Filter[(NOT EXISTS (SELECT 1 FROM (el)))]
          └ HashJoin[(oid = typnamespace)]
            ├ Rename[typname, oid, typnamespace, typrelid, typelem] AS t
            │ └ Collect[pg_catalog.pg_type | [typname, oid, typnamespace, typrelid, typelem] | true]
            └ Rename[nspname, oid] AS n
              └ Collect[pg_catalog.pg_namespace | [nspname, oid] | (NOT (nspname = ANY(['pg_catalog', 'information_schema'])))]
        └ SubPlan
          └ Eval[(relkind = 'c')]
            └ Rename[(relkind = 'c')] AS c
              └ Limit[2::bigint;0::bigint]
                └ Collect[pg_catalog.pg_class | [(relkind = 'c')] | (oid = _cast(typrelid, 'regclass'))]
      └ SubPlan
        └ Eval[1]
          └ Rename[1] AS el
            └ Limit[1;0]
              └ Collect[pg_catalog.pg_type | [1] | ((oid = typelem) AND (typarray = oid))]

```

---

<div class="post-metadata">

**Author:** ![proddata](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/proddata/32/1379_2.png) [@proddata](https://community.cratedb.com/u/proddata)\
**Post date:** [September 9, 2023, 7:42am UTC](https://community.cratedb.com/t/issue-with-beekeeper-postgres-client/1588/2 "2023-09-09T07:42:52Z")

</div>

This seems to be a bug in CrateDB with a combination of correlated subqueries and JOINs.

Beekeeper Studio is trying to run the following query:

```sql
SELECT n.nspname as schema, t.typname as typename, t.oid::int4 as typeid
      FROM pg_type t
      LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
      WHERE (t.typrelid = 0 OR (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c WHERE c.oid = t.typrelid))
      AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type el WHERE el.oid = t.typelem AND el.typarray = t.oid)
      AND n.nspname NOT IN ('pg_catalog', 'information_schema');

```

I opened a bug issue in the `crate/crate` repo:

> <https://github.com/crate/crate/issues/14671>
>
> \### CrateDB version
> 
> 5.4
> 
> \### CrateDB setup information
> 
> \_No response\_
> 
> \### Prob…lem description
> 
> When trying to connect to \[Beekeeper Studio\](https://github.com/beekeeper-studio/beekeeper-studio/releases/tag/v3.9.20) the connection fails. This seems to be related to a 🐛 connected to correlated subqueries.
> 
> the query Beekeeper is trying to run
> 
> \`\`\`sql
> SELECT n.nspname as schema, t.typname as typename, t.oid::int4 as typeid
> FROM pg\_type t
> LEFT JOIN pg\_catalog.pg\_namespace n ON n.oid = t.typnamespace
> WHERE (t.typrelid = 0 OR (SELECT c.relkind = 'c' FROM pg\_catalog.pg\_class c WHERE c.oid = t.typrelid))
> AND NOT EXISTS(SELECT 1 FROM pg\_catalog.pg\_type el WHERE el.oid = t.typelem AND el.typarray = t.oid)
> AND n.nspname NOT IN ('pg\_catalog', 'information\_schema');
> \`\`\` 
> 
> fails with 
> \`\`\`sql
> Couldn't create execution plan from logical plan because of: Couldn't find (SELECT 1 FROM (el)) in SourceSymbols{inputs={typelem=INPUT(4), typnamespace=INPUT(2), nspname=INPUT(5), oid=INPUT(1), typrelid=INPUT(3), typname=INPUT(0), oid=INPUT(6)}, nonDeterministicFunctions={}}:
> Eval\[nspname AS schema, typname AS typename, oid AS typeid\] (rows=0)
> └ Filter\[((typrelid = 0) OR (SELECT (relkind = 'c') FROM (c)))\] (rows=0)
> └ CorrelatedJoin\[typname, oid, typnamespace, typrelid, typelem, nspname, oid, (SELECT (relkind = 'c') FROM (c)), (SELECT 1 FROM (el))\] (rows=0)
> └ CorrelatedJoin\[typname, oid, typnamespace, typrelid, typelem, nspname, oid, (SELECT (relkind = 'c') FROM (c))\] (rows=0)
> └ Filter\[(NOT EXISTS (SELECT 1 FROM (el)))\] (rows=0)
> └ HashJoin\[(oid = typnamespace)\] (rows=unknown)
> ├ Rename\[typname, oid, typnamespace, typrelid, typelem\] AS t (rows=unknown)
> │ └ Collect\[pg\_catalog.pg\_type | \[typname, oid, typnamespace, typrelid, typelem\] | true\] (rows=unknown)
> └ Rename\[nspname, oid\] AS n (rows=unknown)
> └ Collect\[pg\_catalog.pg\_namespace | \[nspname, oid\] | (NOT (nspname = ANY(\['pg\_catalog', 'information\_schema'\])))\] (rows=unknown)
> └ SubPlan
> └ Eval\[(relkind = 'c')\] (rows=unknown)
> └ Rename\[(relkind = 'c')\] AS c (rows=unknown)
> └ Limit\[2::bigint;0::bigint\] (rows=unknown)
> └ Collect\[pg\_catalog.pg\_class | \[(relkind = 'c')\] | (oid = typrelid)\] (rows=unknown)
> └ SubPlan
> └ Eval\[1\] (rows=unknown)
> └ Rename\[1\] AS el (rows=unknown)
> └ Limit\[1;0\] (rows=unknown)
> └ Collect\[pg\_catalog.pg\_type | \[1\] | ((oid = typelem) AND (typarray = oid))\] (rows=unknown)
> \`\`\`
> 
> \### Steps to Reproduce
> 
> \`\`\`sql
> CREATE TABLE t01 (a TEXT);
> CREATE TABLE t02 (b TEXT);
> \`\`\`
> 
> \`\`\`sql
> SELECT \*
> FROM t01
> JOIN t02 ON t01.a = t02.b
> WHERE NOT EXISTS (SELECT 1 FROM t02 WHERE t01.a = t02.b);
> \`\`\`
> 
> \### Actual Result
> 
> \`\`\`
> SQLParseException\[Couldn't create execution plan from logical plan because of: Couldn't find (SELECT 1 FROM (doc.t02)) in SourceSymbols{inputs={b=INPUT(1), a=INPUT(0)}, nonDeterministicFunctions={}}:
> Eval\[a, b\] (rows=0)
> └ CorrelatedJoin\[a, b, (SELECT 1 FROM (doc.t02))\] (rows=0)
> └ Filter\[(NOT EXISTS (SELECT 1 FROM (doc.t02)))\] (rows=0)
> └ HashJoin\[(a = b)\] (rows=unknown)
> ├ Collect\[doc.t01 | \[a\] | true\] (rows=unknown)
> └ Collect\[doc.t02 | \[b\] | true\] (rows=unknown)
> └ SubPlan
> └ Eval\[1\] (rows=unknown)
> └ Limit\[1;0\] (rows=unknown)
> └ Collect\[doc.t02 | \[1\] | (b = a)\] (rows=unknown)\]
> \`\`\`
> 
> \### Expected Result
> 
> query works

* * *

If you are looking for an IDE to work with CrateDB you might want to look into [DBeaver](https://dbeaver.io/).

Thanks for reporting this 💙