[
https://issues.apache.org/jira/browse/CALCITE-1198?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Francis Chuang updated CALCITE-1198:
------------------------------------
Description:
To reproduce the problem:
1. Create a table containing date, time and timestamp columns using SquirrelSQL
or similiar:
{code}
CREATE TABLE test (id INTEGER PRIMARY KEY, tm TIME,dt DATE,tmstmp TIMESTAMP)
TRANSACTIONAL=false
{code}
2. Use curl to query the phoenix query server/avatica:
{code}
curl localhost:8765 -XPOST --data '{"request": "openConnection","connectionId":
"my-conn"}'
curl localhost:8765 -XPOST --data '{"request": "prepare","connectionId":
"my-conn","sql": "SELECT * FROM test","maxRowCount": 1}'
{code}
This is the result of the prepare request:
{code}
{
"response": "prepare",
"statement": {
"connectionId": "my-conn",
"id": 27,
"signature": {
"columns": [
{
"ordinal": 0,
"autoIncrement": false,
"caseSensitive": false,
"searchable": true,
"currency": false,
"nullable": 0,
"signed": true,
"displaySize": 40,
"label": "ID",
"columnName": "ID",
"schemaName": "",
"precision": 0,
"scale": 0,
"tableName": "TEST",
"catalogName": "",
"type": {
"type": "scalar",
"id": 4,
"name": "INTEGER",
"rep": "PRIMITIVE_INT"
},
"readOnly": true,
"writable": false,
"definitelyWritable": false,
"columnClassName": "java.lang.Integer"
},
{
"ordinal": 1,
"autoIncrement": false,
"caseSensitive": false,
"searchable": true,
"currency": false,
"nullable": 1,
"signed": false,
"displaySize": 23,
"label": "TM",
"columnName": "TM",
"schemaName": "",
"precision": 0,
"scale": 0,
"tableName": "TEST",
"catalogName": "",
"type": {
"type": "scalar",
"id": 92,
"name": "TIME",
"rep": "PRIMITIVE_INT"
},
"readOnly": true,
"writable": false,
"definitelyWritable": false,
"columnClassName": "java.sql.Time"
},
{
"ordinal": 2,
"autoIncrement": false,
"caseSensitive": false,
"searchable": true,
"currency": false,
"nullable": 1,
"signed": false,
"displaySize": 23,
"label": "DT",
"columnName": "DT",
"schemaName": "",
"precision": 0,
"scale": 0,
"tableName": "TEST",
"catalogName": "",
"type": {
"type": "scalar",
"id": 91,
"name": "DATE",
"rep": "PRIMITIVE_INT"
},
"readOnly": true,
"writable": false,
"definitelyWritable": false,
"columnClassName": "java.sql.Date"
},
{
"ordinal": 3,
"autoIncrement": false,
"caseSensitive": false,
"searchable": true,
"currency": false,
"nullable": 1,
"signed": false,
"displaySize": 40,
"label": "TMSTMP",
"columnName": "TMSTMP",
"schemaName": "",
"precision": 0,
"scale": 0,
"tableName": "TEST",
"catalogName": "",
"type": {
"type": "scalar",
"id": 93,
"name": "TIMESTAMP",
"rep": "PRIMITIVE_LONG"
},
"readOnly": true,
"writable": false,
"definitelyWritable": false,
"columnClassName": "java.sql.Timestamp"
}
],
"sql": "SELECT * FROM test",
"parameters": [],
"cursorFactory": {
"style": "LIST",
"clazz": null,
"fieldNames": null
},
"statementType": null
}
},
"rpcMetadata": {
"response": "rpcMetadata",
"serverAddress": "f826338-phoenix-server.f826338:8765"
}
}
{code}
In the results, {{time}} and {{date}} has a rep of {{PRIMITIVE_INT}} and
{{timestamp}} has a rep type of {{PRIMITIVE_LONG}}. I believe they should have
rep types of {{JAVA_SQL_TIME}}, {{JAVA_SQL_DATE}} and {{JAVA_SQL_TIMESTAMP}}
respectively.
A work around is to use the {{name}} property which contains the correct JDBC
name and check to see if it is one of {{TIME}}, {{DATE}} or {{TIMESTAMP}} and
manually set the correct rep type to decode.
was:
To reproduce the problem:
1. Create a table containing date, time and timestamp columns using SquirrelSQL
or similiar:
{code}
CREATE TABLE test (id INTEGER PRIMARY KEY, tm TIME,dt DATE,tmstmp TIMESTAMP)
TRANSACTIONAL=false
{code}
2. Use curl to query the phoenix query server/avatica:
{code}
curl localhost:8765 -XPOST --data '{"request": "openConnection","connectionId":
"my-conn"}'
curl localhost:8765 -XPOST --data '{"request": "prepare","connectionId":
"my-conn","sql": "SELECT * FROM test","maxRowCount": 1}'
{code}
This is the result of the prepare request:
{code}
{
"response": "prepare",
"statement": {
"connectionId": "my-conn",
"id": 27,
"signature": {
"columns": [
{
"ordinal": 0,
"autoIncrement": false,
"caseSensitive": false,
"searchable": true,
"currency": false,
"nullable": 0,
"signed": true,
"displaySize": 40,
"label": "ID",
"columnName": "ID",
"schemaName": "",
"precision": 0,
"scale": 0,
"tableName": "TEST",
"catalogName": "",
"type": {
"type": "scalar",
"id": 4,
"name": "INTEGER",
"rep": "PRIMITIVE_INT"
},
"readOnly": true,
"writable": false,
"definitelyWritable": false,
"columnClassName": "java.lang.Integer"
},
{
"ordinal": 1,
"autoIncrement": false,
"caseSensitive": false,
"searchable": true,
"currency": false,
"nullable": 1,
"signed": false,
"displaySize": 23,
"label": "TM",
"columnName": "TM",
"schemaName": "",
"precision": 0,
"scale": 0,
"tableName": "TEST",
"catalogName": "",
"type": {
"type": "scalar",
"id": 92,
"name": "TIME",
"rep": "PRIMITIVE_INT"
},
"readOnly": true,
"writable": false,
"definitelyWritable": false,
"columnClassName": "java.sql.Time"
},
{
"ordinal": 2,
"autoIncrement": false,
"caseSensitive": false,
"searchable": true,
"currency": false,
"nullable": 1,
"signed": false,
"displaySize": 23,
"label": "DT",
"columnName": "DT",
"schemaName": "",
"precision": 0,
"scale": 0,
"tableName": "TEST",
"catalogName": "",
"type": {
"type": "scalar",
"id": 91,
"name": "DATE",
"rep": "PRIMITIVE_INT"
},
"readOnly": true,
"writable": false,
"definitelyWritable": false,
"columnClassName": "java.sql.Date"
},
{
"ordinal": 3,
"autoIncrement": false,
"caseSensitive": false,
"searchable": true,
"currency": false,
"nullable": 1,
"signed": false,
"displaySize": 40,
"label": "TMSTMP",
"columnName": "TMSTMP",
"schemaName": "",
"precision": 0,
"scale": 0,
"tableName": "TEST",
"catalogName": "",
"type": {
"type": "scalar",
"id": 93,
"name": "TIMESTAMP",
"rep": "PRIMITIVE_LONG"
},
"readOnly": true,
"writable": false,
"definitelyWritable": false,
"columnClassName": "java.sql.Timestamp"
}
],
"sql": "SELECT * FROM test",
"parameters": [],
"cursorFactory": {
"style": "LIST",
"clazz": null,
"fieldNames": null
},
"statementType": null
}
},
"rpcMetadata": {
"response": "rpcMetadata",
"serverAddress": "f826338-phoenix-server.f826338:8765"
}
}
{code}
In the results, {time} and {date} has a rep of {PRIMITIVE_INT} and {timestamp}
has a rep type of {PRIMITIVE_LONG}. I believe they should have rep types of
{JAVA_SQL_TIME}, {JAVA_SQL_DATE} and {JAVA_SQL_TIMESTAMP} respectively.
A work around is to use the {name} property which contains the correct JDBC
name and check to see if it is one of {TIME}, {DATE} or {TIMESTAMP} and
manually set the correct rep type to decode.
> Wrong rep type returned for date, time and timestamps in column metadata
> ------------------------------------------------------------------------
>
> Key: CALCITE-1198
> URL: https://issues.apache.org/jira/browse/CALCITE-1198
> Project: Calcite
> Issue Type: Bug
> Components: avatica
> Affects Versions: avatica-1.7.1
> Environment: Phoenix 4.7.0
> Reporter: Francis Chuang
>
> To reproduce the problem:
> 1. Create a table containing date, time and timestamp columns using
> SquirrelSQL or similiar:
> {code}
> CREATE TABLE test (id INTEGER PRIMARY KEY, tm TIME,dt DATE,tmstmp TIMESTAMP)
> TRANSACTIONAL=false
> {code}
> 2. Use curl to query the phoenix query server/avatica:
> {code}
> curl localhost:8765 -XPOST --data '{"request":
> "openConnection","connectionId": "my-conn"}'
> curl localhost:8765 -XPOST --data '{"request": "prepare","connectionId":
> "my-conn","sql": "SELECT * FROM test","maxRowCount": 1}'
> {code}
> This is the result of the prepare request:
> {code}
> {
> "response": "prepare",
> "statement": {
> "connectionId": "my-conn",
> "id": 27,
> "signature": {
> "columns": [
> {
> "ordinal": 0,
> "autoIncrement": false,
> "caseSensitive": false,
> "searchable": true,
> "currency": false,
> "nullable": 0,
> "signed": true,
> "displaySize": 40,
> "label": "ID",
> "columnName": "ID",
> "schemaName": "",
> "precision": 0,
> "scale": 0,
> "tableName": "TEST",
> "catalogName": "",
> "type": {
> "type": "scalar",
> "id": 4,
> "name": "INTEGER",
> "rep": "PRIMITIVE_INT"
> },
> "readOnly": true,
> "writable": false,
> "definitelyWritable": false,
> "columnClassName": "java.lang.Integer"
> },
> {
> "ordinal": 1,
> "autoIncrement": false,
> "caseSensitive": false,
> "searchable": true,
> "currency": false,
> "nullable": 1,
> "signed": false,
> "displaySize": 23,
> "label": "TM",
> "columnName": "TM",
> "schemaName": "",
> "precision": 0,
> "scale": 0,
> "tableName": "TEST",
> "catalogName": "",
> "type": {
> "type": "scalar",
> "id": 92,
> "name": "TIME",
> "rep": "PRIMITIVE_INT"
> },
> "readOnly": true,
> "writable": false,
> "definitelyWritable": false,
> "columnClassName": "java.sql.Time"
> },
> {
> "ordinal": 2,
> "autoIncrement": false,
> "caseSensitive": false,
> "searchable": true,
> "currency": false,
> "nullable": 1,
> "signed": false,
> "displaySize": 23,
> "label": "DT",
> "columnName": "DT",
> "schemaName": "",
> "precision": 0,
> "scale": 0,
> "tableName": "TEST",
> "catalogName": "",
> "type": {
> "type": "scalar",
> "id": 91,
> "name": "DATE",
> "rep": "PRIMITIVE_INT"
> },
> "readOnly": true,
> "writable": false,
> "definitelyWritable": false,
> "columnClassName": "java.sql.Date"
> },
> {
> "ordinal": 3,
> "autoIncrement": false,
> "caseSensitive": false,
> "searchable": true,
> "currency": false,
> "nullable": 1,
> "signed": false,
> "displaySize": 40,
> "label": "TMSTMP",
> "columnName": "TMSTMP",
> "schemaName": "",
> "precision": 0,
> "scale": 0,
> "tableName": "TEST",
> "catalogName": "",
> "type": {
> "type": "scalar",
> "id": 93,
> "name": "TIMESTAMP",
> "rep": "PRIMITIVE_LONG"
> },
> "readOnly": true,
> "writable": false,
> "definitelyWritable": false,
> "columnClassName": "java.sql.Timestamp"
> }
> ],
> "sql": "SELECT * FROM test",
> "parameters": [],
> "cursorFactory": {
> "style": "LIST",
> "clazz": null,
> "fieldNames": null
> },
> "statementType": null
> }
> },
> "rpcMetadata": {
> "response": "rpcMetadata",
> "serverAddress": "f826338-phoenix-server.f826338:8765"
> }
> }
> {code}
> In the results, {{time}} and {{date}} has a rep of {{PRIMITIVE_INT}} and
> {{timestamp}} has a rep type of {{PRIMITIVE_LONG}}. I believe they should
> have rep types of {{JAVA_SQL_TIME}}, {{JAVA_SQL_DATE}} and
> {{JAVA_SQL_TIMESTAMP}} respectively.
> A work around is to use the {{name}} property which contains the correct JDBC
> name and check to see if it is one of {{TIME}}, {{DATE}} or {{TIMESTAMP}} and
> manually set the correct rep type to decode.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
