phoenix 双引号 单引号

phoenix双引号，单引号用起来有点怪怪的。

phoenix表字段，表名等，如果不加双引号，默认是大写的。

单引号加在值上面会被认为成列，例如：where后面条件值，upsert values里面值。phoenix对值类型审核还是挺严格的，VARCHAR肯定是要加单引号的。整形肯定不能加。

0: jdbc:phoenix:bigserver4:2181> !columns "tank_schema"."test2"
+------------+--------------+-------------+--------------+------------+------------+--------------+----------------+-----------------+-----------------+-----------+----------+-------------+------+
| TABLE_CAT  | TABLE_SCHEM  | TABLE_NAME  | COLUMN_NAME  | DATA_TYPE  | TYPE_NAME  | COLUMN_SIZE  | BUFFER_LENGTH  | DECIMAL_DIGITS  | NUM_PREC_RADIX  | NULLABLE  | REMARKS  | COLUMN_DEF  | SQL_ |
+------------+--------------+-------------+--------------+------------+------------+--------------+----------------+-----------------+-----------------+-----------+----------+-------------+------+
|            | tank_schema  | test2       | id           | -5         | BIGINT     | null         | null           | null            | null            | 0         |          |             | null |
|            | tank_schema  | test2       | pid          | -5         | BIGINT     | null         | null           | null            | null            | 1         |          |             | null |
|            | tank_schema  | test2       | name         | 12         | VARCHAR    | null         | null           | null            | null            | 1         |          |             | null |
+------------+--------------+-------------+--------------+------------+------------+--------------+----------------+-----------------+-----------------+-----------+----------+-------------+------+

0: jdbc:phoenix:bigserver4:2181> SELECT sequence_schema, sequence_name, start_with, increment_by, cache_size FROM SYSTEM."SEQUENCE";
+------------------+---------------------+-------------+---------------+-------------+
| SEQUENCE_SCHEMA  |    SEQUENCE_NAME    | START_WITH  | INCREMENT_BY  | CACHE_SIZE  |
+------------------+---------------------+-------------+---------------+-------------+
| tank_ns          | tank_test_sequence  | 100         | 1             | 100         |
| tank_schema      | tank_user_sequence  | 100000      | 2             | 1000        |
+------------------+---------------------+-------------+---------------+-------------+
2 rows selected (0.082 seconds)

0: jdbc:phoenix:bigserver4:2181> select * from "tank_schema"."test2";
+---------+------+-----------+
|   id    | pid  |   name    |
+---------+------+-----------+
| 100000  | 100  | tanktest  |
| 1       | 100  | tank1     |
+---------+------+-----------+
2 rows selected (0.055 seconds)

0: jdbc:phoenix:bigserver4:2181> UPSERT INTO "tank_schema"."test2" ("id", "pid", "name") VALUES ( NEXT VALUE FOR "tank_schema"."tank_user_sequence",102,'tanktank');
1 row affected (0.428 seconds)

0: jdbc:phoenix:bigserver4:2181> select * from "tank_schema"."test2";
+---------+------+-----------+
|   id    | pid  |   name    |
+---------+------+-----------+
| 100000  | 100  | tanktest  |
| 1       | 100  | tank1     |
| 102000  | 102  | tanktank  |
+---------+------+-----------+
3 rows selected (0.048 seconds)

0: jdbc:phoenix:bigserver4:2181> select * from "tank_schema"."test2" where "id"=1;
+-----+------+--------+
| id  | pid  |  name  |
+-----+------+--------+
| 1   | 100  | tank1  |
+-----+------+--------+
1 row selected (0.025 seconds)

0: jdbc:phoenix:bigserver4:2181> select * from "tank_schema"."test2" where "id"='1';    //id是整形的，加了单引号就是varchar
Error: ERROR 203 (22005): Type mismatch. BIGINT and VARCHAR for "id" = '1' (state=22005,code=203)
org.apache.phoenix.schema.TypeMismatchException: ERROR 203 (22005): Type mismatch. BIGINT and VARCHAR for "id" = '1'
	at org.apache.phoenix.schema.TypeMismatchException.newException(TypeMismatchException.java:53)
	at org.apache.phoenix.expression.ComparisonExpression.create(ComparisonExpression.java:133)

0: jdbc:phoenix:bigserver4:2181> select * from "tank_schema"."test2" where "name"="tank1";  //tank1加了双引号，而被误认为是列tank1了,而不是值
Error: ERROR 504 (42703): Undefined column. columnName=tank_schema.test2.tank1 (state=42703,code=504)
org.apache.phoenix.schema.ColumnNotFoundException: ERROR 504 (42703): Undefined column. columnName=tank_schema.test2.tank1
	at org.apache.phoenix.schema.PTableImpl.getColumnForColumnName(PTableImpl.java:828)
	at org.apache.phoenix.compile.FromCompiler$SingleTableColumnResolver.resolveColumn(FromCompiler.java:477)
	at org.apache.phoenix.compile.ExpressionCompiler.resolveColumn(ExpressionCompiler.java:372)
	at org.apache.phoenix.compile.WhereCompiler$WhereExpressionCompiler.resolveColumn(WhereCompiler.java:191)

0: jdbc:phoenix:bigserver4:2181> select * from "tank_schema"."test2" where id=100000;  //id没有加双引号，默认大写就变成了ID
Error: ERROR 504 (42703): Undefined column. columnName=tank_schema.test2.ID (state=42703,code=504)
org.apache.phoenix.schema.ColumnNotFoundException: ERROR 504 (42703): Undefined column. columnName=tank_schema.test2.ID
	at org.apache.phoenix.schema.PTableImpl.getColumnForColumnName(PTableImpl.java:828)

0: jdbc:phoenix:bigserver4:2181> delete from "tank_schema"."test2" where "name"='tank1';
1 row affected (0.069 seconds)

0: jdbc:phoenix:bigserver4:2181> select * from "tank_schema"."test2";
+---------+------+-----------+
|   id    | pid  |   name    |
+---------+------+-----------+
| 100000  | 100  | tanktest  |
| 102000  | 102  | tanktank  |
+---------+------+-----------+
2 rows selected (0.039 seconds)