autocomplete with quoted schemas does not complete

[immetoo2]

Found some bugs the web console, given a setup with;

create schema "test";
create table "test"."foobar" ("id" int);

Using mem/file h2 without extra url options.

The autocomplete of the query editor has the following misbehaviours;

• Does not autocomplete the tables in a quoted schema.

  Example:
  Input: select * from "test".
  Expected: list of tables in schema

• After pretyping the search needle and selecting the quoted schema the pretyped
  text does not get replaced.

  Example:
  Input: select * from te
  Output: select * from teest".
  Expected: select * from "test".
  Note: Works when selecting a none-quoted schema or autocompleting without search needle.

• Incorrect table name autocompleting from none excisting schema leads to illegal query

  Example:
  Input: SELECT * FROM test.
  Output: SELECT * FROM test."foobar"
  Expected: No autocompleting because there is no schema test.
  SQL error:
  Schema "TEST" not found; SQL statement:
  SELECT * FROM test."foobar" [90079-192] 90079/90079

[manticore-projects]

Closing this as stale, since this does not appear to have a priority.
Please provide or sponsor a PR when you are very interested in this feature.

[katzyn]

Actually this is a very annoying bug.

[manticore-projects]

I have created an isolated test case for this. Two questions please:

1. Quoting vs case sensitivity: I believe, default is Upper Case spelling in H2:

Shall we suggest the table TEST (upper case object name) using quotes in SELECT * from "te (explicit lower case spelling)?

2. Are we going to suggesting only strict matches or any close, approximated matches

[katzyn]

Another related issue: #3372

[immetoo2]

For a bit of background, i've found this bug when trying to
make Hibernate,Liquibase working with H2,MSSQL,Postgresql.
The problem was default automatic case adjustment of DB's.
When using full quoting on all SQL, it worked as a workaround
to force every DB+code to keep their hand of my SQL.

Casing is also only a thing in a small subset of languages.
So toUpperCase and toLowerCase should only be used if only
LATIN users are supported.

For example chinese is fixed case;

CREATE SCHEMA "教會";
CREATE TABLE "教會"."仙上主天" ("祈禱" INT);

I can't read chinese so nowdays i use a subset of InuktitutC to write micro-SQL;

CREATE SCHEMA "ᔆʸᔆᐪᓫᔿ";
CREATE TABLE "ᔆʸᔆᐪᓫᔿ"."ᒄᒃᓑᔆᓫᣗ" (
		"ᑊᑊᐣ" BIGINT NOT NULL AUTO_INCREMENT,
		"ᣕᣔᔿᓫ" VARCHAR(100) NOT NULL,
		"ᓑᔆᓫᣗᣕᣔᔿᓫ" VARCHAR(45) NOT NULL,
		"ᣖᣔᔆᔆᒡᒢᐤᣗᒄ" VARCHAR(256) NOT NULL,
		"ᓫᔿᣔᑊᒻ" VARCHAR(200) NOT NULL,
		"ᔆᐪᣔᐪᓑᔆ" VARCHAR(45) NOT NULL,
		PRIMARY KEY ("ᑊᑊᐣ"))
	;
CREATE UNIQUE INDEX "ᔆʸᔆᐪᓫᔿ"."ᒄᒃᓑᔆᓫᣗᐨᓑᑊᐣᕁᐨᓑᔆᓫᣗᣕᣔᔿᓫ" ON "ᔆʸᔆᐪᓫᔿ"."ᒄᒃᓑᔆᓫᣗ" ("ᓑᔆᓫᣗᣕᣔᔿᓫ" ASC);
CREATE INDEX "ᔆʸᔆᐪᓫᔿ"."ᒄᒃᓑᔆᓫᣗᐨᑊᑊᐣᕁᐨᔆᐪᣔᐪᓑᔆ" ON "ᔆʸᔆᐪᓫᔿ"."ᒄᒃᓑᔆᓫᣗ" ("ᔆᐪᣔᐪᓑᔆ" ASC);

Or full mixed SQL;

CREATE SCHEMA "ꕢꕌꔳꕺꖃꕯꕮꕊ";
CREATE TABLE "ꕢꕌꔳꕺꖃꕯꕮꕊ"."𑀳𑁂𑀮𑀺𑀉𑁄𑀤𑁄𑀭𑁂𑀡 𑀪𑀸𑀕" ("للَّٰهِilLצسُو" INT);

Hebrew SQL; (note: reversed table/schema rendering)

CREATE SCHEMA "יהוה";
CREATE TABLE "יהוה"."ױזךיךזװןיןיןןזױז" ("יזזןיזךױױזזױ" INT);

What about a test case with full DB in egyptian hieroglyphs ?

CREATE SCHEMA "𓂀𓃭𓇋𓁹𓅱𓉢";
CREATE TABLE "𓂀𓃭𓇋𓁹𓅱𓉢"."𓌭𓀟" ("𓅓𓅱𓋊𓇋𓇋𓆗𓇚" INT);

And since the china white space is added, we have 16 spaces to
encoding HEX-data in whitespace....

//	public static final OctoBitConverter<String> HEX_CCP = new OctoConvStringHexUtf8(new String[] {
//			"\u3000","\u205F","\u202F","\u200A","\u2009","\u2008","\u2007","\u2006",
//			"\u2005","\u2004","\u2003","\u2002","\u2001","\u2000","\u00A0","\u0020"
//	});

CREATE SCHEMA "       　                ";
CREATE TABLE "       　                "."           　      " ("         " INT);

Multiple whitespace namespaces and tables/etc is a really funny test case for UI.

[manticore-projects]

I feel like we are digressing here.
The main question stands: if the object name is TEST, would we like to expect that the input "tes" or "Tes will return suggestions? Do we want to see strict suggestions or approximate suggestions?

[immetoo2]

How can a DB give approximate suggestions ?, SQL is strict, the following example is valid;

CREATE SCHEMA "test";
CREATE SCHEMA "Test";
CREATE SCHEMA "TEst";
CREATE SCHEMA "TESt";
CREATE SCHEMA "TEST";

[manticore-projects]

@katzyn:
This timer based condition sentence.stopIfRequired(); is always true when debugging. I wonder if the is a Global variable/switch in H2 indicating a debugging session, which I can use? If not, can we create one?

Something like this, just at a more central place:

 public void stopIfRequired() {
        if (java.lang.management.ManagementFactory.getRuntimeMXBean().
                                                  getInputArguments().toString().indexOf("-agentlib:jdwp") > 0) {
            System.out.println("Debugging session detected");
        } else if (System.nanoTime() - stopAtNs > 0L) {
            throw new IllegalStateException();
        }
    }

For the time being, I just skip that line of course.

[katzyn]

I think it will be safer to change timeout in your copy of sources.

[manticore-projects]

@katzyn: Sorry for bothering you but i need a decision please.
The code below fixes the problem for any matches of quoted schemas. When schema = "TEST_SCHEMA" then TEST_SCHEMA, test_schema and "TEST_SCHEMA" will be found, but also "test_schema" or "Test_Schema" etc. which is not supposed to match when quoted.

I wonder if we want to remove the toUpper() here? (pardon my confusion at first, I missed the toUpper).

            for (DbSchema schema: schemas) {
                String name = StringUtils.toUpperEnglish(schema.name);
                String quotedName = "\"" + StringUtils.toUpperEnglish(schema.quotedName) + "\"";
                if (up.startsWith(name)) {
                    if (best == null || name.length() > best.length()) {
                        best = name;
                        bestSchema = schema;
                    }
                } else if (up.startsWith(quotedName)) {
                    if (best == null || name.length() > best.length()) {
                        best = quotedName;
                        bestSchema = schema;
                    }
                } else if (s.length() == 0 || name.startsWith(up) || quotedName.startsWith(up) ) {
                    if (s.length() < name.length()) {
                        sentence.add(name, name.substring(s.length()), type);
                        sentence.add(schema.quotedName + ".",
                                schema.quotedName.substring(s.length()) + ".",
                                Sentence.CONTEXT);
                    }
                }
            }

[katzyn]

Good code completion should be able to find candidates with characters in any case.
TEST, test, "TEST", U&"T\0045ST" etc. is the same identifier (with default settings), "Test" is different, but it is reasonable to suggest it as well if Te, TE, "TE, or "Te was typed.

Anyway, the main problem here is different.

Code completion can find a table named "test" in default schema after TABLE PUBLIC., but it is unable to find this table after TABLE "PUBLIC".. Even the . is not suggested after quoted identifier. I don't see how toUpperEngish() can be related to this.

[manticore-projects]

Good code completion should be able to find candidates with characters in any case. TEST, test, "TEST", U&"T\0045ST" etc. is the same identifier (with default settings), "Test" is different, but it is reasonable to suggest it as well if Te, TE, "TE, or "Te was typed.

Thanks, this is what I was looking for.

Anyway, the main problem here is different.

Code completion can find a table named "test" in default schema after TABLE PUBLIC., but it is unable to find this table after TABLE "PUBLIC".. Even the . is not suggested after quoted identifier. I don't see how toUpperEngish() can be related to this.

Yes, clearly understood and solved as shown above.
I am wrapping up the PR.