[
https://issues.apache.org/jira/browse/PHOENIX-7955?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18097547#comment-18097547
]
Dávid Paksy commented on PHOENIX-7955:
--------------------------------------
The SQL grammar page, [CREATE TABLE
section|https://phoenix.apache.org/docs/grammar/#create-table] seems to confirm
the behavior when IF NOT EXISTS is used:
{quote}
Note that when using the IF NOT EXISTS clause, if a table already exists, then
no change will be made to it. Additionally, no validation is done to check
whether the existing table metadata matches the proposed table metadata. so
it's better to use DROP TABLE followed by CREATE TABLE if the table metadata
may be changing.
{quote}
Although it does not describe the case when CREATE TABLE is used without IF NOT
EXISTS.
> Create table if not exists changes existing table properties in HBase
> ---------------------------------------------------------------------
>
> Key: PHOENIX-7955
> URL: https://issues.apache.org/jira/browse/PHOENIX-7955
> Project: Phoenix
> Issue Type: Bug
> Components: core
> Reporter: Dávid Paksy
> Assignee: Dávid Paksy
> Priority: Major
>
> h2. Summary
> IF NOT EXISTS clause in create table failed to guard the existing table
> properties from being changed
> h2. Actual Behavior:
> The table configuration is mutated. The REGION_REPLICATION property is
> modified on the existing table, bypassing the safety check of the IF NOT
> EXISTS clause.
> h2. Expected Behavior:
> If the table exists, the entire statement should be skipped safely without
> modifying any structural or operational HBase properties.
> h2. Steps to Reproduce:
> 1. Create a standard Phoenix table with default properties.
> 2. Execute a CREATE TABLE IF NOT EXISTS statement on that same table, but
> append modified properties (e.g., set REGION_REPLICATION=2).
> 3. Check the underlying HBase table descriptor using the HBase shell
> (describe 'TABLE_NAME').
> h2. Steps to Reproduce (detailed):
> 1. Create table:
> {noformat}
> 0: jdbc:phoenix:node-1.example.com> CREATE TABLE IF NOT EXISTS TEST_PRTY (
> . . . . . . . . . . . . . . . . . . . . . . )> PRTY_ACNT_REL_KEY VARCHAR NOT
> NULL,
> . . . . . . . . . . . . . . . . . . . . . . )> PRTY_IDENT_NUM VARCHAR,
> . . . . . . . . . . . . . . . . . . . . . . )> ACNT_ID_NUM VARCHAR,
> . . . . . . . . . . . . . . . . . . . . . . )> PRTY_IDENT_TYP_CD VARCHAR,
> . . . . . . . . . . . . . . . . . . . . . . )> AGMT_IDENT_TYP_CD VARCHAR,
> . . . . . . . . . . . . . . . . . . . . . . )> REL_TYP_CD VARCHAR,
> . . . . . . . . . . . . . . . . . . . . . . )> ACNT_ID_TYP_CD VARCHAR,
> . . . . . . . . . . . . . . . . . . . . . . )> DATA_SRC_CD VARCHAR,
> . . . . . . . . . . . . . . . . . . . . . . )> DATA_SRC_QLFR VARCHAR,
> . . . . . . . . . . . . . . . . . . . . . . )> ACNT_DATA_SRC_CD VARCHAR,
> . . . . . . . . . . . . . . . . . . . . . . )> PRTY_ID_QUAL VARCHAR,
> . . . . . . . . . . . . . . . . . . . . . . )> RECORD_DELETED_FLAG TINYINT,
> . . . . . . . . . . . . . . . . . . . . . . )> START_DATE TIMESTAMP,
> . . . . . . . . . . . . . . . . . . . . . . )> LAST_UPDT_DATE TIMESTAMP,
> . . . . . . . . . . . . . . . . . . . . . . )> CONSTRAINT PK PRIMARY KEY
> (PRTY_ACNT_REL_KEY)
> . . . . . . . . . . . . . . . . . . . . . . )> )
> . . . . . . . . . . . . . . . . . . semicolon> SALT_BUCKETS=10,
> . . . . . . . . . . . . . . . . . . semicolon> COMPRESSION='SNAPPY',
> . . . . . . . . . . . . . . . . . . semicolon> VERSIONS=1,
> . . . . . . . . . . . . . . . . . . semicolon> REGION_REPLICATION=3;
> No rows affected (1.48 seconds)
> {noformat}
> 2. Then verify the table is visible in hbase shell
> {noformat}
> hbase:006:0> describe "TEST_PRTY"
> Table TEST_PRTY is ENABLED
> TEST_PRTY, \{TABLE_ATTRIBUTES => {REGION_REPLICATION => '3', coprocessor$1 =>
> '|org.apache.phoenix.coprocessor.ScanRegionObserver|805306366|',
> coprocessor$2 =>
> '|org.apache.phoenix.coprocessor.UngroupedAggregateRegionObserver|805306366|',
> coprocessor$3 =>
> '|org.apache.phoenix.coprocessor.GroupedAggregateRegionObserver|805306366|',
> coprocessor$4 =>
> '|org.apache.phoenix.coprocessor.ServerCachingEndpointImpl|805306366|',
> coprocessor$5 =>
> '|org.apache.phoenix.hbase.index.IndexRegionObserver|805306366|index.builder=org.apache.phoenix.index.PhoenixIndexBuilder,org.apache.hadoop.hbase.index.codec.class=org.apache.phoenix.index.PhoenixIndexCodec',
> METADATA => {'hbase.store.file-tracker.impl' => 'DEFAULT'}}}
> COLUMN FAMILIES DESCRIPTION
> {NAME => '0', INDEX_BLOCK_ENCODING => 'NONE', VERSIONS => '1',
> KEEP_DELETED_CELLS => 'FALSE', DATA_BLOCK_ENCODING => 'FAST_DIFF', TTL =>
> 'FOREVER', MIN_VERSIONS => '0', REPLICATION_SCOPE => '0', BLOOMFILTER =>
> 'ROW', IN_MEMORY => 'false', COMPRESSION => 'SNAPPY', BLOCKCACHE => 'true',
> BLOCKSIZE => '65536 B (64KB)'}
> 1 row(s)
> Quota is disabled
> Took 0.0876 seconds
> {noformat}
> 3. Now execute CREATE TABLE IF NOT EXISTS command with changed properties
> (ex: REGION_REPLICATION from 3 to 2):
> {noformat}
> 0: jdbc:phoenix:node-1.example.com> CREATE TABLE IF NOT EXISTS TEST_PRTY (
> . . . . . . . . . . . . . . . . . . . . . . )> PRTY_ACNT_REL_KEY VARCHAR NOT
> NULL,
> . . . . . . . . . . . . . . . . . . . . . . )> PRTY_IDENT_NUM VARCHAR,
> . . . . . . . . . . . . . . . . . . . . . . )> ACNT_ID_NUM VARCHAR,
> . . . . . . . . . . . . . . . . . . . . . . )> PRTY_IDENT_TYP_CD VARCHAR,
> . . . . . . . . . . . . . . . . . . . . . . )> AGMT_IDENT_TYP_CD VARCHAR,
> . . . . . . . . . . . . . . . . . . . . . . )> REL_TYP_CD VARCHAR,
> . . . . . . . . . . . . . . . . . . . . . . )> ACNT_ID_TYP_CD VARCHAR,
> . . . . . . . . . . . . . . . . . . . . . . )> DATA_SRC_CD VARCHAR,
> . . . . . . . . . . . . . . . . . . . . . . )> DATA_SRC_QLFR VARCHAR,
> . . . . . . . . . . . . . . . . . . . . . . )> ACNT_DATA_SRC_CD VARCHAR,
> . . . . . . . . . . . . . . . . . . . . . . )> PRTY_ID_QUAL VARCHAR,
> . . . . . . . . . . . . . . . . . . . . . . )> RECORD_DELETED_FLAG TINYINT,
> . . . . . . . . . . . . . . . . . . . . . . )> START_DATE TIMESTAMP,
> . . . . . . . . . . . . . . . . . . . . . . )> LAST_UPDT_DATE TIMESTAMP, --
> <-- Added missing comma here
> . . . . . . . . . . . . . . . . . . . . . . )> CONSTRAINT PK PRIMARY KEY
> (PRTY_ACNT_REL_KEY)
> . . . . . . . . . . . . . . . . . . . . . . )> ) -- <-- Removed premature
> semicolon here
> . . . . . . . . . . . . . . . . . . semicolon> SALT_BUCKETS=10,
> . . . . . . . . . . . . . . . . . . semicolon> COMPRESSION='SNAPPY',
> . . . . . . . . . . . . . . . . . . semicolon> VERSIONS=1,
> . . . . . . . . . . . . . . . . . . semicolon> REGION_REPLICATION=2;
> {noformat}
> -> Table properties changed - *NOK* (x)
> 4. We can see the changes in Hbase shell too
> {noformat}
> hbase:007:0> describe "TEST_PRTY"
> Table TEST_PRTY is ENABLED
> TEST_PRTY, \{TABLE_ATTRIBUTES => {REGION_REPLICATION => '2', coprocessor$1 =>
> '|org.apache.phoenix.coprocessor.ScanRegionObserver|805306366|',
> coprocessor$2 =>
> '|org.apache.phoenix.coprocessor.UngroupedAggregateRegionObserver|805306366|',
> coprocessor$3 =>
> '|org.apache.phoenix.coprocessor.GroupedAggregateRegionObserver|805306366|',
> coprocessor$4 =>
> '|org.apache.phoenix.coprocessor.ServerCachingEndpointImpl|805306366|',
> coprocessor$5 =>
> '|org.apache.phoenix.hbase.index.IndexRegionObserver|805306366|index.builder=org.apache.phoenix.index.PhoenixIndexBuilder,org.apache.hadoop.hbase.index.codec.class=org.apache.phoenix.index.PhoenixIndexCodec',
> METADATA => {'hbase.store.file-tracker.impl' => 'DEFAULT'}}}
> COLUMN FAMILIES DESCRIPTION
> {NAME => '0', INDEX_BLOCK_ENCODING => 'NONE', VERSIONS => '1',
> KEEP_DELETED_CELLS => 'FALSE', DATA_BLOCK_ENCODING => 'FAST_DIFF', TTL =>
> 'FOREVER', MIN_VERSIONS => '0', REPLICATION_SCOPE => '0', BLOOMFILTER =>
> 'ROW', IN_MEMORY => 'false', COMPRESSION => 'SNAPPY', BLOCKCACHE => 'true',
> BLOCKSIZE => '65536 B (64KB)'}
> 1 row(s)
> Quota is disabled
> {noformat}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
