Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Autocompletion is case-sensitive with unquoted identifiers. #895

Closed
zilzila opened this issue Feb 13, 2020 · 2 comments
Closed

Autocompletion is case-sensitive with unquoted identifiers. #895

zilzila opened this issue Feb 13, 2020 · 2 comments
Labels
Milestone

Comments

@zilzila
Copy link

@zilzila zilzila commented Feb 13, 2020

Steps to reproduce this issue

  1. Enter SQL, with a table\view that has one or more capital letters;
  2. Try to get column list after SELECT Table_Name.;
  3. Then I get... nothing

Current behavior

After entering the dot, nothing happens

select
--  cfg.       -- The column list does not appear
    cfg.name
from
    pg_catalog.PG_CONFIG cfg
--  PG_CATALOG.pg_config cfg  -- Also does not work
--  PG_CATALOG.PG_CONFIG cfg  -- Also does not work
;

Expected behavior

The appearance of the column selection list

Possible solution

If the identifier is unquoted, then use ilike operator instead = or cast it to lowercase in SQL query to get the columns for the list, since in PostgreSQL all unquoted identifiers are stored in lowercase

Environment

  • HeidiSQL version:

10.3.0.5877 (and earlier also)

  • Database system and version:

PostgreSQL 12.1

  • Operating system:

Windows 7 x64

@zilzila

This comment has been minimized.

Copy link
Author

@zilzila zilzila commented Feb 13, 2020

With quoted identifiers autocomplete does not work also...

-- drop table if exists "CamelCase";
-- drop table if exists "lowercase";

create table "CamelCase" (id int);
create table "lowercase" (id int);
-- check tables
select
    *
from
    pg_catalog.pg_tables tbl
where
    tbl.tablename ilike 'CAMELcase' -- case-insensitive comparision
 or tbl.tablename ilike 'lowerCASE' -- case-insensitive comparision;
--    tbl.tablename ~* '^CAMELcase$' -- case-insensitive comparision (RegExp)
-- or tbl.tablename ~* '^lowerCASE$' -- case-insensitive comparision (RegExp);

изображение

select
--  cc.   -- list does not pop up
    cc.*  -- query works fine
from
    "CamelCase" cc;

select
--  lc.   -- list does not pop up
    lc.*  -- query works fine
from
    "lowercase" lc;
@zilzila

This comment has been minimized.

Copy link
Author

@zilzila zilzila commented Mar 7, 2020

In general, autocompletion does not always work in the table aliases and schema names.

Test configuration
-- drop schema if exists schema1;
-- drop schema if exists SCHEMA2;
create schema if not exists schema1;
create schema if not exists SCHEMA2;

-- schema1
create table schema1.schema1_table1
(
    schema1_table1_column1 int,
    SCHEMA1_TABLE1_COLUMN2 int
);
create table schema1.SCHEMA1_TABLE2
(
    schema1_table2_column1 int,
    SCHEMA1_TABLE2_COLUMN2 int
);

-- SCHEMA2
create table SCHEMA2.schema2_table1
(
    schema2_table1_column1 int,
    SCHEMA2_TABLE1_COLUMN2 int
);
create table SCHEMA2.SCHEMA2_TABLE2
(
    schema2_table2_column1 int,
    SCHEMA2_TABLE2_COLUMN2 int
);

MySQL identifier case sensitivity

Tests:

MySQL 5.6.20 Windows 7

lower_case_table_names = 1, case-insensitive file system

Table names are stored in lowercase on disk and name comparisons are not case-sensitive. MySQL converts all table names to lowercase on storage and lookup. This behavior also applies to database names and table aliases.

lower_case_table_names = 2, case-insensitive file system

Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement, but MySQL converts them to lowercase on lookup. Name comparisons are not case sensitive. This works only on file systems that are not case-sensitive! InnoDB table names and view names are stored in lowercase, as for lower_case_table_names=1.

select ;
select @@global.lower_case_table_names;

-- ============================================================================
-- Pick root of the connection -> no schema selected (optional)
-- ============================================================================

-- FieldIdent tests ---------------------------
-- lowercase schema
select schema1.;
select schema1.schema1_table1.;
select schema1.schema1_table2.;
select schema1.SCHEMA1_TABLE1.;
select schema1.SCHEMA1_TABLE2.;

select schema2.;
select schema2.schema2_table1.;
select schema2.schema2_table2.;
select schema2.SCHEMA2_TABLE1.;
select schema2.SCHEMA2_TABLE2.;

-- uppercase schema
select SCHEMA1.;
select SCHEMA1.schema1_table1.;
select SCHEMA1.schema1_table2.;
select SCHEMA1.SCHEMA1_TABLE1.;
select SCHEMA1.SCHEMA1_TABLE2.;

select SCHEMA2.;
select SCHEMA2.schema2_table1.;
select SCHEMA2.schema2_table2.;
select SCHEMA2.SCHEMA2_TABLE1.;
select SCHEMA2.SCHEMA2_TABLE2.;



-- TableIdent tests ---------------------------
select * from 
-- lowercase schema
select * from  schema1.;
select * from  schema2.;

-- uppercase schema
select * from  SCHEMA1.;
select * from  SCHEMA2.;


-- lowercase alias
select s1t1. from schema1.schema1_table1 s1t1;
select s1t2. from schema1.SCHEMA1_TABLE2 s1t2;

select s2t1. from SCHEMA2.schema2_table1 s2t1;
select s2t2. from SCHEMA2.SCHEMA2_TABLE2 s2t2;


-- uppercase alias
select s1t1. from schema1.schema1_table1 S1T1; -- does not work
select s1t2. from schema1.SCHEMA1_TABLE2 S1T2; -- does not work

select s2t1. from SCHEMA2.schema2_table1 S2T1; -- does not work
select s2t2. from SCHEMA2.SCHEMA2_TABLE2 S2T2; -- does not work


-- lowercase alias
select S1T1. from schema1.schema1_table1 s1t1; -- does not work
select S1T2. from schema1.SCHEMA1_TABLE2 s1t2; -- does not work

select S2T1. from SCHEMA2.schema2_table1 s2t1; -- does not work
select S2T2. from SCHEMA2.SCHEMA2_TABLE2 s2t2; -- does not work


-- uppercase alias
select S1T1. from schema1.schema1_table1 S1T1;
select S1T2. from schema1.SCHEMA1_TABLE2 S1T2;

select S2T1. from SCHEMA2.schema2_table1 S2T1;
select S2T2. from SCHEMA2.SCHEMA2_TABLE2 S2T2;



-- ============================================================================
-- Pick schema1 in DBTree
-- ============================================================================

-- FieldIdent tests ---------------------------
-- lowercase tables
select schema1_table1.;
select schema1_table2.;

-- uppercase tables
select SCHEMA1_TABLE1.;
select SCHEMA1_TABLE2.;


-- TableIdent tests ---------------------------
-- lowercase alias
select s1t1. from schema1_table1 s1t1;
select s1t2. from SCHEMA1_TABLE2 s1t2;

-- uppercase alias
select s1t1. from schema1_table1 S1T1; -- does not work
select s1t2. from SCHEMA1_TABLE2 S1T2; -- does not work

-- lowercase alias
select S1T1. from schema1_table1 s1t1; -- does not work
select S1T2. from SCHEMA1_TABLE2 s1t2; -- does not work

-- uppercase alias
select S1T1. from schema1_table1 S1T1;
select S1T2. from SCHEMA1_TABLE2 S1T2;


-- ============================================================================
-- Pick SCHEMA2 in DBTree
-- ============================================================================

-- FieldIdent tests ---------------------------
-- lowercase tables
select schema2_table1.;
select schema2_table2.;

-- uppercase tables
select SCHEMA2_TABLE1.;
select SCHEMA2_TABLE2.;


-- TableIdent tests ---------------------------
-- lowercase alias
select s2t1. from schema2_table1 s2t1;
select s2t2. from SCHEMA2_TABLE2 s2t2;

-- uppercase alias
select s2t1. from schema2_table1 S2T1; -- does not work
select s2t2. from SCHEMA2_TABLE2 S2T2; -- does not work


-- lowercase alias
select S2T1. from schema2_table1 s2t1; -- does not work
select S2T2. from SCHEMA2_TABLE2 s2t2; -- does not work

-- uppercase alias
select S2T1. from schema2_table1 S2T1;
select S2T2. from SCHEMA2_TABLE2 S2T2;
MariaDB 10.4.12 Linux

lower_case_table_names = 0, case-sensitive file system

Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement. Name comparisons are case sensitive. You should not set this variable to 0 if you are running MySQL on a system that has case-insensitive file names (such as Windows or macOS).

select ;
select @@global.lower_case_table_names;

-- ============================================================================
-- Pick root of the connection -> no schema selected (optional)
-- ============================================================================

-- FieldIdent tests ---------------------------
-- lowercase schema
select schema1.;
select schema1.schema1_table1.;
select schema1.schema1_table2.; -- Incorrect
select schema1.SCHEMA1_TABLE1.; -- Incorrect
select schema1.SCHEMA1_TABLE2.;

select schema2.;                -- Incorrect
select schema2.schema2_table1.;
select schema2.schema2_table2.;
select schema2.SCHEMA2_TABLE1.;
select schema2.SCHEMA2_TABLE2.;

-- uppercase schema
select SCHEMA1.;                -- Incorrect
select SCHEMA1.schema1_table1.;
select SCHEMA1.schema1_table2.;
select SCHEMA1.SCHEMA1_TABLE1.;
select SCHEMA1.SCHEMA1_TABLE2.;

select SCHEMA2.;
select SCHEMA2.schema2_table1.;
select SCHEMA2.schema2_table2.; -- Incorrect
select SCHEMA2.SCHEMA2_TABLE1.; -- Incorrect
select SCHEMA2.SCHEMA2_TABLE2.;



-- TableIdent tests ---------------------------
select * from 
-- lowercase schema
select * from  schema1.;
select * from  schema2.; -- Incorrect

-- uppercase schema
select * from  SCHEMA1.; -- Incorrect
select * from  SCHEMA2.;


-- lowercase alias
select s1t1. from schema1.schema1_table1 s1t1;
select s1t2. from schema1.SCHEMA1_TABLE2 s1t2;

select s2t1. from SCHEMA2.schema2_table1 s2t1;
select s2t2. from SCHEMA2.SCHEMA2_TABLE2 s2t2;


-- uppercase alias
select s1t1. from schema1.schema1_table1 S1T1;
select s1t2. from schema1.SCHEMA1_TABLE2 S1T2;

select s2t1. from SCHEMA2.schema2_table1 S2T1;
select s2t2. from SCHEMA2.SCHEMA2_TABLE2 S2T2;


-- lowercase alias
select S1T1. from schema1.schema1_table1 s1t1;
select S1T2. from schema1.SCHEMA1_TABLE2 s1t2;

select S2T1. from SCHEMA2.schema2_table1 s2t1;
select S2T2. from SCHEMA2.SCHEMA2_TABLE2 s2t2;


-- uppercase alias
select S1T1. from schema1.schema1_table1 S1T1;
select S1T2. from schema1.SCHEMA1_TABLE2 S1T2;

select S2T1. from SCHEMA2.schema2_table1 S2T1;
select S2T2. from SCHEMA2.SCHEMA2_TABLE2 S2T2;



-- ============================================================================
-- Pick schema1 in DBTree
-- ============================================================================

-- FieldIdent tests ---------------------------
-- lowercase tables
select schema1_table1.;
select schema1_table2.; -- Incorrect

-- uppercase tables
select SCHEMA1_TABLE1.; -- Incorrect
select SCHEMA1_TABLE2.;


-- TableIdent tests ---------------------------
-- lowercase alias
select s1t1. from schema1_table1 s1t1;
select s1t2. from SCHEMA1_TABLE2 s1t2;

-- uppercase alias
select s1t1. from schema1_table1 S1T1;
select s1t2. from SCHEMA1_TABLE2 S1T2;

-- lowercase alias
select S1T1. from schema1_table1 s1t1;
select S1T2. from SCHEMA1_TABLE2 s1t2;

-- uppercase alias
select S1T1. from schema1_table1 S1T1;
select S1T2. from SCHEMA1_TABLE2 S1T2;



-- ============================================================================
-- Pick SCHEMA2 in DBTree
-- ============================================================================

-- FieldIdent tests ---------------------------
-- lowercase tables
select schema2_table1.;
select schema2_table2.; -- Incorrect

-- uppercase tables
select SCHEMA2_TABLE1.; -- Incorrect
select SCHEMA2_TABLE2.;


-- TableIdent tests ---------------------------
-- lowercase alias
select s2t1. from schema2_table1 s2t1;
select s2t2. from SCHEMA2_TABLE2 s2t2;

-- uppercase alias
select s2t1. from schema2_table1 S2T1;
select s2t2. from SCHEMA2_TABLE2 S2T2;


-- lowercase alias
select S2T1. from schema2_table1 s2t1;
select S2T2. from SCHEMA2_TABLE2 s2t2;

-- uppercase alias
select S2T1. from schema2_table1 S2T1;
select S2T2. from SCHEMA2_TABLE2 S2T2;
PostgreSQL 12.1

Identifiers always stored in lowercase except when quoted, like lower_case_table_names = 1 in MySQL

select ;

-- ============================================================================
-- Pick root of the connection -> no schema selected (optional)
-- ============================================================================

-- FieldIdent tests ---------------------------
-- lowercase schema
select schema1.;
select schema1.schema1_table1.;
select schema1.schema1_table2.;
select schema1.SCHEMA1_TABLE1.;
select schema1.SCHEMA1_TABLE2.;

select schema2.;
select schema2.schema2_table1.;
select schema2.schema2_table2.;
select schema2.SCHEMA2_TABLE1.;
select schema2.SCHEMA2_TABLE2.;

-- uppercase schema
select SCHEMA1.;
select SCHEMA1.schema1_table1.; -- does not work
select SCHEMA1.schema1_table2.; -- does not work
select SCHEMA1.SCHEMA1_TABLE1.; -- does not work
select SCHEMA1.SCHEMA1_TABLE2.; -- does not work

select SCHEMA2.;
select SCHEMA2.schema2_table1.; -- does not work
select SCHEMA2.schema2_table2.; -- does not work
select SCHEMA2.SCHEMA2_TABLE1.; -- does not work
select SCHEMA2.SCHEMA2_TABLE2.; -- does not work



-- TableIdent tests ---------------------------
select * from 
-- lowercase schema
select * from  schema1.;
select * from  schema2.;

-- uppercase schema
select * from  SCHEMA1.;
select * from  SCHEMA2.;


-- lowercase alias
select s1t1. from schema1.schema1_table1 s1t1;
select s1t2. from schema1.SCHEMA1_TABLE2 s1t2;

select s2t1. from SCHEMA2.schema2_table1 s2t1; -- does not work
select s2t2. from SCHEMA2.SCHEMA2_TABLE2 s2t2; -- does not work


-- uppercase alias
select s1t1. from schema1.schema1_table1 S1T1; -- does not work
select s1t2. from schema1.SCHEMA1_TABLE2 S1T2; -- does not work

select s2t1. from SCHEMA2.schema2_table1 S2T1; -- does not work
select s2t2. from SCHEMA2.SCHEMA2_TABLE2 S2T2; -- does not work


-- lowercase alias
select S1T1. from schema1.schema1_table1 s1t1; -- does not work
select S1T2. from schema1.SCHEMA1_TABLE2 s1t2; -- does not work

select S2T1. from SCHEMA2.schema2_table1 s2t1; -- does not work
select S2T2. from SCHEMA2.SCHEMA2_TABLE2 s2t2; -- does not work


-- uppercase alias
select S1T1. from schema1.schema1_table1 S1T1;
select S1T2. from schema1.SCHEMA1_TABLE2 S1T2;

select S2T1. from SCHEMA2.schema2_table1 S2T1; -- does not work
select S2T2. from SCHEMA2.SCHEMA2_TABLE2 S2T2; -- does not work



-- ============================================================================
-- Pick schema1 in DBTree
-- ============================================================================

-- FieldIdent tests ---------------------------
-- lowercase tables
select schema1_table1.;
select schema1_table2.;

-- uppercase tables
select SCHEMA1_TABLE1.;
select SCHEMA1_TABLE2.;


-- TableIdent tests ---------------------------
-- lowercase alias
select s1t1. from schema1_table1 s1t1;
select s1t2. from SCHEMA1_TABLE2 s1t2;

-- uppercase alias
select s1t1. from schema1_table1 S1T1; -- does not work
select s1t2. from SCHEMA1_TABLE2 S1T2; -- does not work

-- lowercase alias
select S1T1. from schema1_table1 s1t1; -- does not work
select S1T2. from SCHEMA1_TABLE2 s1t2; -- does not work

-- uppercase alias
select S1T1. from schema1_table1 S1T1;
select S1T2. from SCHEMA1_TABLE2 S1T2;


-- ============================================================================
-- Pick SCHEMA2 in DBTree
-- ============================================================================

-- FieldIdent tests ---------------------------
-- lowercase tables
select schema2_table1.;
select schema2_table2.;

-- uppercase tables
select SCHEMA2_TABLE1.;
select SCHEMA2_TABLE2.;


-- TableIdent tests ---------------------------
-- lowercase alias
select s2t1. from schema2_table1 s2t1;
select s2t2. from SCHEMA2_TABLE2 s2t2;

-- uppercase alias
select s2t1. from schema2_table1 S2T1; -- does not work
select s2t2. from SCHEMA2_TABLE2 S2T2; -- does not work


-- lowercase alias
select S2T1. from schema2_table1 s2t1; -- does not work
select S2T2. from SCHEMA2_TABLE2 s2t2; -- does not work

-- uppercase alias
select S2T1. from schema2_table1 S2T1;
select S2T2. from SCHEMA2_TABLE2 S2T2;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

2 participants
You can’t perform that action at this time.