[PHOENIX-2422] Differentiate between null and empty string

• Type: Improvement
• Status: Reopened
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 4.5.2
• Labels: char null phoenix string

Environment

Sending Avatica requests to Phoenix 4.5.2 using Telerik Fiddler or any REST test tool

• Assignee: Unassigned
• Reporter: Kevin Liew
• Created: 16/Nov/15 21:04
• Updated: 22/Nov/15 05:09

Description

Create a table with a character column (use the JDBC driver or bulk import tool)

DROP TABLE IF EXISTS SEN.Char_Table;
CREATE TABLE IF NOT EXISTS SEN.Char_Table(
	KeyColumn VARCHAR(255) PRIMARY KEY,
	Column1 CHAR(100));

Upsert an empty string using Avatica wire protocol

request: {
  "request": "prepareAndExecute",
  "connectionId": "bed590dc-04d0-f1c2-16cd-a20107271BA9",
  "sql": "UPSERT INTO SEN.CHAR_TABLE(KeyColumn, Column1) VALUES('ConversionTestKey', '')",
  "maxRowCount": -1
}

Query the inserted data

request: {
  "request": "prepareAndExecute",
  "connectionId": "bed590dc-04d0-f1c2-16cd-a20107271BA9",
  "sql": "SELECT RTRIM(Column1) FROM SEN.CHAR_TABLE WHERE RTRIM(KeyColumn) = 'ConversionTestKey'",
  "maxRowCount": -1
}

Actual result:

{
  "response": "Service$ExecuteResponse",
  "results": [
    {
      "response": "resultSet",
      "connectionId": "bed590dc-04d0-f1c2-16cd-a20107271BA9",
      "statementId": 371048010,
      "ownStatement": true,
      "signature": {
        "columns": [
          {
            "ordinal": 0,
            "autoIncrement": false,
            "caseSensitive": false,
            "searchable": true,
            "currency": false,
            "nullable": 1,
            "signed": false,
            "displaySize": 255,
            "label": "RTRIM(COLUMN1)",
            "columnName": "RTRIM(COLUMN1)",
            "schemaName": "",
            "precision": 255,
            "scale": 0,
            "tableName": "SEN.CHAR_TABLE",
            "catalogName": "",
            "type": {
              "type": "scalar",
              "id": 12,
              "name": "VARCHAR",
              "rep": "STRING"
            },
            "readOnly": true,
            "writable": false,
            "definitelyWritable": false,
            "columnClassName": "java.lang.String"
          }
        ],
        "sql": null,
        "parameters": [
          
        ],
        "cursorFactory": {
          "style": "LIST",
          "clazz": null,
          "fieldNames": null
        }
      },
      "firstFrame": {
        "offset": 0,
        "done": true,
        "rows": [
          [
            null
          ]
        ]
      },
      "updateCount": -1
    }
  ]
}

Expected result: results.firstFrame.rows[0] should contain an empty string, not 'null'