SQL Still problematic

[coleman-rik]

Description

Running just a single statement in in DBeaver is problematic.

Even though:
SQL Processing Statement delimiter is ;
Ignore native delimiter is unchecked
Blank line is statement delimiter is set to Never
Remove trailing query delimiter is unchecked

placing my cursor instaide of a complete statement and pressing "Execute SQL Query" will occationally try to run every statement in the SQL Editor with disasterous results.

I had thought that switching:
Blank line is statement delimiter: from the default of Smart, to Never had fixed the issue, but apparently all doing that accomplished was to reduce the occurance not eliminate it entirely.

The only way to ensure that only a single statement is run is to select the entire statement.

This is a marked regression from the behavior of previous versions,
hopefully this can be corrected soon.

DBeaver Version

Community Edition 24.0.4

Operating System

Windows 10

Database and driver

PostgreSQL 16

Steps to reproduce

Have an SQL Editor window with many sql statements

place the cursor (symbolized by {c} in the example below) inside a single statement
press the orange arrow (Execute SQL Query)
sometimes it will run the specified query
other times it will run all queries in that editor

ex:
select * from foo;
select yaba, daba, doo {c} from flintstones;
insert into baz set fld1 = 'something' where fld2 > 10;
delete from important_table where entered < '2023-01-01';

since the cursor {c} is in the select from flinstones statement that's the only statement that should be run.

Additional context

No response

[E1izabeth]

I can't reproduce your issue on the provided query script neither with Blank line is statement delimiter set to Smart, nor to Never on DBeaver 24.0.4.

[E1izabeth]

Could you open Outline view and show it to me?
It can be opened here by clicking this button

[coleman-rik]

Is this what you wanted?

[E1izabeth]

I don't see something wrong here. Outline shows that statements are correctly separated.
About the second screenshot I could say that lines 1040-1091 does have any features allowing statement delimiter logic to treat them as independent queries to separate from the surrounding parts of the script. So it's not valid to keep them here while using smart mode. Either comment them out, or switch Blank line is statement delimiter to Always, so that it would be enough to have a blank line to separate them - this way DBeaver will assume any text fragment surrounded with empty lines as a separate statement, preventing undesired behavior in this case.

[coleman-rik]

@E1izabeth I often have multiple statements with no black spaces between them, see example below.
I expect Dbeaver to only use the delimiters to, well, delimite commands. If I place my cursor within the third line, I expect that only the third line will be executed.

Dbeaver should, and used to, only run the SQL statement that my cursor was within.

How do I get back to that behavior?
thanks,
rik.
For example:

-- create tables
create table rxnorm.rxnatomarchive_20240205 as table rxnorm.rxnatomarchive;
create table rxnorm.rxnconso_20240205 as table rxnorm.rxnconso;
create table rxnorm.rxncui_20240205 as table rxnorm.rxncui;
-- create the indicies
create index if not exists x_rxnconso_str on rxnorm.rxnconso (str);
create index if not exists x_rxnconso_rxcui on rxnorm.rxnconso (rxcui);
create index if not exists x_rxnatomarchive_rxcui on rxnorm.rxnatomarchive (rxcui);
create index if not exists x_rxnatomarchive_merged_to on rxnorm.rxnatomarchive (merged_to_rxcui);
-- drop tables
drop table if exists rxnorm.rxnatomarchive_20240102;
drop table if exists rxnorm.rxnconso_20240102;
drop table if exists rxnorm.rxncui_20240102;
drop table if exists rxnorm.rxncuichanges_20240102;

Or:

-- list roles
select usename as username,

  case 
     when usesuper then 
	   cast('superuser' as pg_catalog.text)
     else 
	    cast('No user' AS pg_catalog.text)
  end user_attribute

from pg_catalog.pg_user
order by username asc;

[E1izabeth]

Thank you for more examples. All of them works right for me on version 24.0.4. We have a bug in 24.0.3, but now it's fixed. I cannot reproduce the behavior you're describing.

[E1izabeth]

You can change this setting to Always. It should help

[philosaur]

This seems pretty clearly related to the bug I reported here. I'm attaching an additional screenshot in hopes of clarifying that "Blank line as a statement delimiter" has no effect under certain conditions. Also, the outline view (which I just learned about, thanks to this post) even shows that the parser doesn't correctly parse these as separate statements.

[E1izabeth]

#30136 (comment)