[
https://issues.apache.org/jira/browse/PHOENIX-6202?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Swaroopa Kadam updated PHOENIX-6202:
------------------------------------
Description:
{code:java}
@Test
public void testGlobalAddColumns() throws Exception {
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
try (Connection conn = DriverManager.getConnection(getUrl(), props))
{
conn.setAutoCommit(true); String tableName = "TBL_" + generateUniqueName();
String idxName = "IND_" + generateUniqueName(); String baseTableDDL = "CREATE
TABLE " + tableName + " (PK1 VARCHAR NOT NULL, V1 VARCHAR, V2 CHAR(15)
CONSTRAINT NAME_PK PRIMARY KEY(PK1)) ";
conn.createStatement().execute(baseTableDDL);
String indexDDL = "CREATE INDEX " + idxName + " ON " + tableName + " (PK1)
include (V1, V2) ";
conn.createStatement().execute(indexDDL);
String upsert = "UPSERT INTO " + tableName + " (PK1, V1, V2) VALUES ('PK1',
'V1', 'V2')"; conn.createStatement().executeUpdate(upsert);
dumpTable(idxName); String alterTable = "ALTER TABLE " + tableName + " ADD V3
VARCHAR CASCADE INDEX ALL";
conn.createStatement().execute(alterTable); String upsert2 = "UPSERT INTO " +
tableName + " (PK1, V1, V2,V3) VALUES ('PK2', 'V1', 'V2', 'V3')";
conn.createStatement().executeUpdate(upsert2);
dumpTable(idxName);
String selectFromIndex = "SELECT PK1, V3, V1, V2 FROM " + tableName + " where
V1='V1' AND V2='V2'";
ResultSet rs = conn.createStatement().executeQuery("EXPLAIN " +
selectFromIndex);
}
}
public static void dumpTable(Table hTable) {
try {
Scan scan = new Scan();
scan.setRaw(true);
scan.setMaxVersions();
System.out.println("Table Name : " + hTable.getName().getNameAsString());
ResultScanner scanner = hTable.getScanner(scan);
for (Result result = scanner.next(); result != null; result = scanner.next()) {
for (Cell cell : result.rawCells())
{ String cellString = cell.toString(); System.out.println(cellString + " value
: " + Bytes.toStringBinary(CellUtil.cloneValue(cell))); }
}
} catch (Exception e) {
//ignore
}
}
{code}
output:
PK1/0:\x00\x00\x00\x00/1603217119002/Put/vlen=1/seqid=0 ****** value : \x01
PK1/0:\x80\x0B/1603217119002/Put/vlen=2/seqid=0 ****** value : V1
PK1/0:\x80\x0C/1603217119002/Put/vlen=15/seqid=0 ****** value : V2
PK2\x00V3/0:\x00\x00\x00\x00/1603217125595/Put/vlen=1/seqid=0 ****** value :
\x01
PK2\x00V3/0:\x80\x0B/1603217125595/Put/vlen=2/seqid=0 ****** value : V1
PK2\x00V3/0:\x80\x0C/1603217125595/Put/vlen=15/seqid=0 ****** value : V2
was:
{code:java}
@Test
public void testGlobalAddColumns() throws Exception {
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); try (Connection
conn = DriverManager.getConnection(getUrl(), props))
{ conn.setAutoCommit(true); String tableName = "TBL_" + generateUniqueName();
String idxName = "IND_" + generateUniqueName(); String baseTableDDL = "CREATE
TABLE " + tableName + " (PK1 VARCHAR NOT NULL, V1 VARCHAR, V2 CHAR(15)
CONSTRAINT NAME_PK PRIMARY KEY(PK1)) ";
conn.createStatement().execute(baseTableDDL);
String indexDDL = "CREATE INDEX " + idxName + " ON " + tableName + " (PK1)
include (V1, V2) ";
conn.createStatement().execute(indexDDL);
String upsert = "UPSERT INTO " + tableName + " (PK1, V1, V2) VALUES ('PK1',
'V1', 'V2')"; conn.createStatement().executeUpdate(upsert);
dumpTable(idxName); String alterTable = "ALTER TABLE " + tableName + " ADD V3
VARCHAR CASCADE INDEX ALL";
conn.createStatement().execute(alterTable); String upsert2 = "UPSERT INTO " +
tableName + " (PK1, V1, V2,V3) VALUES ('PK2', 'V1', 'V2', 'V3')";
conn.createStatement().executeUpdate(upsert2);
dumpTable(idxName);
String selectFromIndex = "SELECT PK1, V3, V1, V2 FROM " + tableName + " where
V1='V1' AND V2='V2'";
ResultSet rs = conn.createStatement().executeQuery("EXPLAIN " +
selectFromIndex);
}
}
public static void dumpTable(Table hTable) {
try {
Scan scan = new Scan();
scan.setRaw(true);
scan.setMaxVersions();
System.out.println("Table Name : " + hTable.getName().getNameAsString());
ResultScanner scanner = hTable.getScanner(scan);
for (Result result = scanner.next(); result != null; result = scanner.next()) {
for (Cell cell : result.rawCells())
{ String cellString = cell.toString(); System.out.println(cellString + " value
: " + Bytes.toStringBinary(CellUtil.cloneValue(cell))); }
}
} catch (Exception e) {
//ignore
}
}
{code}
output:
PK1/0:\x00\x00\x00\x00/1603217119002/Put/vlen=1/seqid=0 ****** value : \x01
PK1/0:\x80\x0B/1603217119002/Put/vlen=2/seqid=0 ****** value : V1
PK1/0:\x80\x0C/1603217119002/Put/vlen=15/seqid=0 ****** value : V2
PK2\x00V3/0:\x00\x00\x00\x00/1603217125595/Put/vlen=1/seqid=0 ****** value :
\x01
PK2\x00V3/0:\x80\x0B/1603217125595/Put/vlen=2/seqid=0 ****** value : V1
PK2\x00V3/0:\x80\x0C/1603217125595/Put/vlen=15/seqid=0 ****** value : V2
> New column in index gets added as PK with CASCADE INDEX
> -------------------------------------------------------
>
> Key: PHOENIX-6202
> URL: https://issues.apache.org/jira/browse/PHOENIX-6202
> Project: Phoenix
> Issue Type: Improvement
> Reporter: Swaroopa Kadam
> Assignee: Swaroopa Kadam
> Priority: Major
>
>
> {code:java}
> @Test
> public void testGlobalAddColumns() throws Exception {
> Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
> try (Connection conn = DriverManager.getConnection(getUrl(), props))
> {
> conn.setAutoCommit(true); String tableName = "TBL_" + generateUniqueName();
> String idxName = "IND_" + generateUniqueName(); String baseTableDDL = "CREATE
> TABLE " + tableName + " (PK1 VARCHAR NOT NULL, V1 VARCHAR, V2 CHAR(15)
> CONSTRAINT NAME_PK PRIMARY KEY(PK1)) ";
> conn.createStatement().execute(baseTableDDL);
> String indexDDL = "CREATE INDEX " + idxName + " ON " + tableName + " (PK1)
> include (V1, V2) ";
> conn.createStatement().execute(indexDDL);
> String upsert = "UPSERT INTO " + tableName + " (PK1, V1, V2) VALUES ('PK1',
> 'V1', 'V2')"; conn.createStatement().executeUpdate(upsert);
> dumpTable(idxName); String alterTable = "ALTER TABLE " + tableName + " ADD
> V3 VARCHAR CASCADE INDEX ALL";
> conn.createStatement().execute(alterTable); String upsert2 = "UPSERT INTO " +
> tableName + " (PK1, V1, V2,V3) VALUES ('PK2', 'V1', 'V2', 'V3')";
> conn.createStatement().executeUpdate(upsert2);
> dumpTable(idxName);
> String selectFromIndex = "SELECT PK1, V3, V1, V2 FROM " + tableName + " where
> V1='V1' AND V2='V2'";
> ResultSet rs = conn.createStatement().executeQuery("EXPLAIN " +
> selectFromIndex);
> }
> }
> public static void dumpTable(Table hTable) {
> try {
> Scan scan = new Scan();
> scan.setRaw(true);
> scan.setMaxVersions();
> System.out.println("Table Name : " + hTable.getName().getNameAsString());
> ResultScanner scanner = hTable.getScanner(scan);
> for (Result result = scanner.next(); result != null; result =
> scanner.next()) {
> for (Cell cell : result.rawCells())
> { String cellString = cell.toString(); System.out.println(cellString + "
> value : " + Bytes.toStringBinary(CellUtil.cloneValue(cell))); }
> }
> } catch (Exception e) {
> //ignore
> }
> }
> {code}
>
>
> output:
>
> PK1/0:\x00\x00\x00\x00/1603217119002/Put/vlen=1/seqid=0 ****** value : \x01
> PK1/0:\x80\x0B/1603217119002/Put/vlen=2/seqid=0 ****** value : V1
> PK1/0:\x80\x0C/1603217119002/Put/vlen=15/seqid=0 ****** value : V2
> PK2\x00V3/0:\x00\x00\x00\x00/1603217125595/Put/vlen=1/seqid=0 ****** value :
> \x01
> PK2\x00V3/0:\x80\x0B/1603217125595/Put/vlen=2/seqid=0 ****** value : V1
> PK2\x00V3/0:\x80\x0C/1603217125595/Put/vlen=15/seqid=0 ****** value : V2
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
