[IGNITE-16322] Database object names case inconsisten between SQL and KV API

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 3.0.0-alpha3
• Fix Version/s: 3.0.0-alpha4
• Component/s: sql
• Labels: iep-87 ignite-3
• Ignite Flags: Docs Required, Release Notes Required
• Assignee: Taras Ledkov
• Reporter: Taras Ledkov
• Created: 18/Jan/22 12:43
• Updated: 04/May/22 16:08
• Resolved: 24/Jan/22 18:12

Description

Current naming of the database object is not clear for everyone.

Current behavior
CREATE TABLE mytable(id INT, val INT); -> creates PUBLIC.MYTABLE (ID, VAL)
ignite.tables().table("public.mytable"); -> fails with table not found
ignite.tables().table("PUBLIC.MYTABLE "); -> returns PUBLIC.MYTABLE
CREATE TABLE \"MyTable\" (id INT, val INT); -> creates PUBLIC.MyTable(ID, VAL)
ignite.tables().table("PUBLIC.MyTable"); -> returns PUBLIC.MyTable

Tuple / column behavior:
CREATE TABLE MyTable (id INT, \"Id\" INT, val INT); -> creates PUBLIC.MYTABLE (ID, Id, VAL)
tbl.get().value("id") -> fails with error: column not found
tbl.get().value("ID") -> returns ID column's value
tbl.get().value("Id") -> returns Id column's value

Proposed Fixes

1. Use case insensitive collation to compare and lookup database object
e.g.
CREATE TABLE MyTable (id INT, val INT); -> creates PUBLIC.MYTABLE (ID, VAL)
ignite.tables().table("public.mytable"); -> returns PUBLIC.MYTABLE
CREATE TABLE \"MyTable\" (id INT, val INT); -> fails with error: PUBLIC.MYTABLE already exists,

Tuple / column behavior:
CREATE TABLE MyTable (id INT, \"Id\" INT, val INT); -> fails with error: duplicate column name: ID
tbl.get().value("id") -> returns ID column's value
tbl.get().value("Id") -> returns ID column's value

2. Case sensitive collation for DB object names and parse string argument of the name passed through API
Use quotation for string values by API
CREATE TABLE MyTable (id INT, val INT); -> creates PUBLIC.MYTABLE
ignite.tables().table("public.mytable"); -> returns PUBLIC.MYTABLE
CREATE TABLE \"MyTable\" (id INT, val INT); -> creates PUBLIC.MyTable
ignite.tables().table("public.\"MyTable\""); -> returns PUBLIC.MyTable

Tuple / column behavior:
CREATE TABLE MyTable (id INT, \"Id\" INT, val INT); -> creates PUBLIC.MYTABLE (ID, Id, VAL)
Tuple tuple = ...
tuple.value("id") -> returns ID column's value
tuple.value("Id") -> returns ID column's value
tuple.value("\"Id\"") -> returns Id column's value

Issue Links

• blocks: IGNITE-16244 Update examples for Ignite 3
• is related to: IGNITE-16357 Add documentation for DB names format used by API calls
• links to: GitHub Pull Request #563
• links to: GitHub Pull Request #574