Interactive SQL 16 (dbisql) auto-complete feature is not working as expected

[ThiagoReis]

The iSQL v16.0.0 build 2076 is not working as iSQL v11.0.1 build 2742 used to do.

The auto-complete feature does not bring the table's owner before the table name when a table is chosen from the drop down menu.

The drop down menu is showing the table's owner as expected.

For example:

Step 1= select * from pro

Step 2= ctrl+space

Step 3= table admin.program is selected

iSQLv11 "SQL Statements" window is filled with "select * from admin.program"

iSQLv16 "SQL Statements" window is filled with "select * from program"

Is there a bug?

Any thoughts?!

Thanks!

[Breck_Carter]

Have you tried using the V11 dbisql with the V16 database?

[ThiagoReis]

Yes Breck, I'm using V11 dbisql right now. Thanks!

[former_SQLA_member1694940 · Accepted solution]

In version 11, the owner name for tables was added if the table's owner was different from the name of the current user, even if the owner was not necessary. For example, in the "demo" database, if you were logged in as "dba", the text completer always completes "cust" as "GROUPO.Customers", even though "Customers" is, in this case, sufficient. This results in the completed SQL being pedantically correct, but a bit noisy.

In version 12 and 16, the algorithm regarding adding owner names is different. The owner name is added automatically only if there is more than one table in the database with the given name. So, unless there is both a GROUPO.Customers and a MyUser.Customers, "cust" will be completed as "Customers" (without an explicit owner).

[ThiagoReis · Accepted solution]

Yes, I have confirmed this behavior too. But this is very awkward. The old behavior was very better. So, if it's not a bug, it's a downgrade. Thanks!

[VolkerBarth · Accepted solution]

Hm, I would not fully agree that the old behaviour was better but I guess several folks prefer to always qualify table names (even those that are currently named unambiguously) - you might not know if another user will name a table likewise and by that break your current "correct" schema...

---

IMHO, the auto-complete option "Show owner names" should be turned into a "Show and add owner names" feature or the like - then everyone could decide whether he prefers a "pedantically correct, but a bit noisy" script or a shorter one. The current "Show owner names" option seems a bit superfluous to me as the owner name is still shown in the list (as tool tip) even if "Show owner names" is not set but "Show tool tips" is set. I think it would be way more appropriate if one could decide whether the owner name is added in the text or not.

---

FWIW: I have to point out that I usually write scripts manually and do rarely make use of the auto-complete feature...

[ThiagoReis · Accepted solution]

I have more than 3000 tables to manage and sometimes when I have to quickly produce queries to check/correct data with 10 tables in a join, this new auto-completer is completely useless.

[VolkerBarth · Accepted solution]

Given Chris's explanation, I'm not sure why the v12/v16 auto-completer seems useless to you - if you are faced with lots of tables with identical table names and varying owner names, then it still should add the owner name, and otherwise, the lack of an owner name should not matter technically for ad-hoc queries, I'd think... - can you elaborate what makes this useless?

[ThiagoReis · Accepted solution]

The database has 10 different schemas (object owners) and lot of tables with same name. I use the DBA user. When I face a table like "admin.program", the auto-completer brings to my command window just the table name. I have to complete it with "admin." before the table name or I get an error as the database tries to find a DBA.program table. The v11 works better for me, bringing the full qualified name "admin.program".

[VolkerBarth · Accepted solution]

Hm, according to Chris's statement

The owner name is added automatically only if there is more than one table in the database with the given name

I had expected in your case (i.e. if there are several "program" tables for different owners), if you choose the fitting table owner and name (here "admin.program"), the auto-completer should add the owner name before the table name. If it does not do so, I'd call it a bug...

(Note: I have not tested that myself.)

[ThiagoReis · Accepted solution]

I´m sorry Volker, yes it´s true. It does bring the table owner if there is more than one table in the database with the given name, but in this case, the table program has only one owner, the user admin. If I run "select * from program" with DBA user, I still get an error. Any clue?

[VolkerBarth · Accepted solution]

Then the DBA user seems not to be member of the "admin" group (if that user is also a group) - to cite from the docs:

When referring to a database object, you require a prefix unless:

• You are the owner of the database object.
• The database object is owned by a group ID of which you are a member.

So, if "admin" is just a user, a different user trying to access "admin"'s database objects will always have to qualify them. - That's another reason an "Add owner name" option would be very helpful here:)

[ThiagoReis · Accepted solution]

Agreed. "Admin" is really just an user!!! Thank you!!! 🙂

[Former Member]

Late to the game but this appears to have been a deliberate change introduced in 12.0.1 and up and basically does not complete the text with owner if there is no possibility of ambiguity without one.

One description I found about this was this one

   The owner name is no longer added to completed table, view,
   or procedure names if there is only one object in the system catalog with the given name.

and is something that did not seem to make it into the official docs.

HTH

[VolkerBarth]

Yes, that fits Chris's statement.

However, as Thiago has pointed out in the comment thread below Chris's answer, unambiguity is not the only concern here, visibility is relevant, too, so it may still be required for users other than the object's owner to add the owner name just to be able to access it (besides the obvious required permission).

Therefore I think this change has been a disadvantage for those use cases.

[Former Member]

FWIW V17 documents that the owner is always going to be added if you are not the owner

So it appears that will be the intended behavior in new versions.

[reimer_pods]

To me this looks like a bug. In my test it didn't make any difference, whether "Show owner names" was checked in the Editor Options or not.

[VolkerBarth]

whether "Show owner names" was checked in the Editor Options or not

That part is not a bug IMHO, as the docs state:

Show owner names
This option controls whether an object's owner appears along with the object name in the text completion window. It does not control whether the owner name appears in the completed text.

However, I have not found an option that tells whether the owner name will be added to the completed text or not... (and IMHO the cited option could also control that, too...).

---

FWIW: The behaviour with 12.0.1.4216 seems equal to that of v16.