Dear
we have found a bug in postgres 14.2.
select count(*) from wat.owl_v ov;the error is: org.jkiss.dbeaver.model.sql.DBSQLException: SQL Error [XX000]: ERROR: variable not found in subplan target list at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:133) at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:513) at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$0(SQLQueryJob.java:444) at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:171) at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:431) at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:816) at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:3435) at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:118) at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:171) at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:116) at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:4711) at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:105) at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63) Caused by: org.postgresql.util.PSQLException: ERROR: variable not found in subplan target list at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2552) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2284) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:322) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401) at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:322) at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:308) at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:284) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:279) at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:330) at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:130) ... 12 more
the DDL of the table is: CREATE TABLE wat.owl_v ( gid serial NOT NULL, waterlicha varchar(254) NULL, owl_naam varchar(254) NULL, orde varchar(254) NULL, gsd varchar(50) NULL, bekken_nr numeric(10) NULL, bekken_naa varchar(254) NULL, cat_code numeric(10) NULL, cat_naam varchar(254) NULL, typ_code varchar(254) NULL, type_naam varchar(254) NULL, statuut_co varchar(10) NULL, statuut_na varchar(254) NULL, asz varchar(20) NULL, lengte numeric NULL, geldig_van date NULL, geldig_tot date NULL, geom gis.geometry(multipolygon, 31370) NULL, vmm_vhaw_id int4 NULL, CONSTRAINT owl_v_pkey PRIMARY KEY (gid) ); CREATE INDEX owl_v_geom_idx ON wat.owl_v USING gist (geom); CREATE INDEX owl_v_vmm_vhaw_id ON wat.owl_v USING btree (vmm_vhaw_id); the most strange on the thing is, when we change the * into any of the fields of the table, We don't get an error.
I think the problem is something with multipolygon, because we have another table quite the same but with multilinestring and the query works without any problem CREATE TABLE wat.owl_l ( gid serial NOT NULL, waterlicha varchar(254) NULL, owl_naam varchar(254) NULL, orde varchar(254) NULL, sgd varchar(50) NULL, bekken_nr numeric(10) NULL, bekken_naa varchar(254) NULL, cat_code numeric(10) NULL, cat_naam varchar(254) NULL, type_code varchar(254) NULL, type_naam varchar(254) NULL, statuut_co varchar(10) NULL, statuut_na varchar(254) NULL, asz varchar(20) NULL, geldig_van date NULL, geldig_tot date NULL, lengte numeric NULL, geom gis.geometry(multilinestring, 31370) NULL, vmm_vhaw_id int4 NULL, CONSTRAINT owl_l_pkey PRIMARY KEY (gid) ); CREATE INDEX owl_l_geom_idx ON wat.owl_l USING gist (geom); CREATE INDEX owl_l_vmm_vhaw_id ON wat.owl_l USING btree (vmm_vhaw_id);
Kind regardsDavid Van D