You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ignite.apache.org by "Denis Magda (JIRA)" <ji...@apache.org> on 2018/02/14 20:03:00 UTC
[jira] [Created] (IGNITE-7711) Explain the case-sensitivity of
schema and table/cache
Denis Magda created IGNITE-7711:
-----------------------------------
Summary: Explain the case-sensitivity of schema and table/cache
Key: IGNITE-7711
URL: https://issues.apache.org/jira/browse/IGNITE-7711
Project: Ignite
Issue Type: Task
Components: documentation
Reporter: Denis Magda
Fix For: 2.5
Users don't get how to use table and schema name in Ignite:
[https://stackoverflow.com/questions/48723946/apache-ignite-querying-a-cache-through-an-sql-driver]
The confusion comes from the interpretation of term "case-insensitive". All SQL databases work this way:
1) If I create some object and define its name without quotes, a database will convert it to upper case (sometimes lower case). Then if I query this object without quotes, a query is also transformed to upper case. This is what I call "case-insensitivity" - it doesn't matter how you refer to this object, it works still.
CREATE TABLE myTable -> CREATE TABLE MYTABLE
SELECT FROM myTable -> SELECT FROM MYTABLE -> OK
SELECT FROM MyTable -> SELECT FROM MYTABLE -> OK
SELECT FROM "myTable" -> SELECT FROM myTable -> *{color:#ff0000}FAIL!{color}*
2) If I create an object with quotes, then the database doesn't convert it to upper case, and the original name in quotes is *the only way* to access the object:
CREATE TABLE "myTable" -> CREATE TABLE myTable
SELECT FROM myTable -> SELECT FROM MYTABLE -> *{color:#ff0000}FAIL!{color}*
SELECT FROM MyTable -> SELECT FROM MYTABLE -> *{color:#ff0000}FAIL!{color}*SELECT FROM "myTable" -> SELECT FROM myTable -> OK
Now back to Ignite case. Our schemas are case-sensitive. so the only way to access it is through quotes:
CREATE SCHEMA "cacheName"; // This is what Ignite does internally
SELECT * FROM "cacheName".myTable -> OK
SELECT * FROM cacheName.myTable -> *{color:#ff0000}FAIL!{color}*
Our users know this, and all their queries are written with schema names in quotation marks. Now consider what would happen should we change schema names to case-insensitive mode:
CREATE SCHEMA cacheName;
SELECT * FROM "cacheName".myTable -> *{color:#ff0000}FAIL, +all old quries start to fail!+{color}*
SELECT * FROM cacheName.myTable -> OK, now it works
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)