search

exasol / pyexasol

Exasol Python driver with low overhead, fast HTTP transport and compression
MIT License
72 stars 39 forks source link

get_sys_columns((my_schema, my_table)) doesn't work if the connection's schema is defined #38

Closed tokybe closed 4 years ago

tokybe commented 4 years ago

Hi, Using get_sys_columns() with a tuple (my_schema, my_table), my_schema is ignored if the schema argument of the current connection is not the default one ('', no schema). I'm using version 0.6.4. Any idea?

littleK0i commented 4 years ago

@tokybe , hello.

Are you sure (my_schema, my_table) is a normal Python tuple? Not some sort of NamedTuple or tuple-like object.

Internally function uses the following code:

        if isinstance(object_name, tuple):
            schema = self.connection.format.default_format_ident_value(object_name[0])
            object_name = self.connection.format.default_format_ident_value(object_name[1])
        else:
            schema = self.connection.current_schema()
            object_name = self.connection.format.default_format_ident_value(object_name)

If normal tuple is not working, please attach an example and output with debug=True connection option. I'll check this out.

Thank you!

tokybe commented 4 years ago

Yes, it's a normal Python tuple and the desired schema is picked up correctly by the internal function, but the result is still from the schema declared in the connection init function. Here is an example:

Creating a table:

CREATE TABLE "TEST"."TEST_TABLE" ( 
    "ID" VARCHAR(45) UTF8,
    "CREATED_DATE" TIMESTAMP
);

And then running:

import pyexasol
import pprint

printer = pprint.PrettyPrinter(indent=4, width=140)

thistuple = ("TEST", "TEST_TABLE")
print(type(thistuple))
C = pyexasol.connect(
    dsn="localhost:8563", user="xxxx", password="xxxx", debug=True
)
cols = C.ext.get_sys_columns(thistuple)
printer.pprint(cols)
C = pyexasol.connect(
    dsn="localhost:8563", user="xxxx", password="xxxx", schema="RAW", debug=True
)
cols = C.ext.get_sys_columns(thistuple)
printer.pprint(cols)

The output:

<class 'tuple'>
2019-11-21 13:48:50.293 Connection attempt [127.0.0.1:8563]
2019-11-21 13:48:50.329 [WebSocket request #1]
{
    "command": "login",
    "protocolVersion": 1
}
2019-11-21 13:48:50.372 [WebSocket response #1]
{
    "status": "ok",
    "responseData": {
        "publicKeyPem": "xxxx",
        "publicKeyExponent": "010001",
        "publicKeyModulus": "xxxx"
    }
}
2019-11-21 13:48:50.405 [WebSocket request #2]
{
    "username": "xxxx",
    "password": "xxxx",
    "driverName": "PyEXASOL 0.6.4",
    "clientName": "PyEXASOL",
    "clientVersion": "0.6.4",
    "clientOs": "Darwin-19.0.0-x86_64-i386-64bit",
    "clientOsUsername": "xxxx",
    "clientRuntime": "Python 3.7.3",
    "useCompression": false,
    "attributes": {
        "currentSchema": "",
        "autocommit": true,
        "queryTimeout": 0,
        "snapshotTransactionsEnabled": false
    }
}
2019-11-21 13:48:50.526 [WebSocket response #2]
{
    "status": "ok",
    "responseData": {
        "protocolVersion": 1,
        "timeZone": "CET",
        "timeZoneBehavior": "INVALID SHIFT AMBIGUOUS ST",
        "sessionId": 1650819470898266962,
        "maxDataMessageSize": 67108864,
        "releaseVersion": "6.2.0",
        "databaseName": "DB1",
        "productName": "EXASolution",
        "maxIdentifierLength": 128,
        "maxVarcharLength": 2000000,
        "identifierQuoteString": "\""
    }
}
2019-11-21 13:48:50.526 [WebSocket request #3]
{
    "command": "getAttributes"
}
2019-11-21 13:48:50.629 [WebSocket response #3]
{
    "status": "ok",
    "attributes": {
        "datetimeFormat": "YYYY-MM-DD HH24:MI:SS.FF6",
        "dateFormat": "YYYY-MM-DD",
        "numericCharacters": ".,",
        "dateLanguage": "ENG",
        "queryTimeout": 0,
        "timezone": "CET",
        "timezoneBehavior": "INVALID SHIFT AMBIGUOUS ST",
        "defaultLikeEscapeCharacter": "\\",
        "autocommit": true,
        "compressionEnabled": false,
        "currentSchema": "",
        "snapshotTransactionsEnabled": false,
        "openTransaction": 0
    }
}
2019-11-21 13:48:50.630 [WebSocket request #4]
{
    "command": "execute",
    "sqlText": "SELECT c.column_name, c.column_type, c.column_maxsize, c.column_num_scale,\n                   c.column_is_nullable, c.column_is_distribution_key, c.column_default,\n                   c.column_comment, t.type_name\n            FROM EXA_ALL_COLUMNS c\n                JOIN EXA_SQL_TYPES t ON (c.column_type_id=t.type_id)\n            WHERE c.column_schema='TEST'\n                AND c.column_table='TEST_TABLE'\n            ORDER BY c.column_ordinal_position"
}
2019-11-21 13:48:50.687 [WebSocket response #4]
{
    "status": "ok",
    "responseData": {
        "results": [
            {
                "resultType": "resultSet",
                "resultSet": {
                    "numColumns": 9,
                    "numRows": 2,
                    "numRowsInMessage": 2,
                    "columns": [
                        {
                            "name": "COLUMN_NAME",
                            "dataType": {
                                "type": "VARCHAR",
                                "size": 128,
                                "characterSet": "UTF8"
                            }
                        },
                        {
                            "name": "COLUMN_TYPE",
                            "dataType": {
                                "type": "VARCHAR",
                                "size": 40,
                                "characterSet": "UTF8"
                            }
                        },
                        {
                            "name": "COLUMN_MAXSIZE",
                            "dataType": {
                                "type": "DECIMAL",
                                "precision": 18,
                                "scale": 0
                            }
                        },
                        {
                            "name": "COLUMN_NUM_SCALE",
                            "dataType": {
                                "type": "DECIMAL",
                                "precision": 18,
                                "scale": 0
                            }
                        },
                        {
                            "name": "COLUMN_IS_NULLABLE",
                            "dataType": {
                                "type": "BOOLEAN"
                            }
                        },
                        {
                            "name": "COLUMN_IS_DISTRIBUTION_KEY",
                            "dataType": {
                                "type": "BOOLEAN"
                            }
                        },
                        {
                            "name": "COLUMN_DEFAULT",
                            "dataType": {
                                "type": "VARCHAR",
                                "size": 2000,
                                "characterSet": "UTF8"
                            }
                        },
                        {
                            "name": "COLUMN_COMMENT",
                            "dataType": {
                                "type": "VARCHAR",
                                "size": 2000,
                                "characterSet": "UTF8"
                            }
                        },
                        {
                            "name": "TYPE_NAME",
                            "dataType": {
                                "type": "VARCHAR",
                                "size": 40,
                                "characterSet": "UTF8"
                            }
                        }
                    ],
                    "data": [
                        [
                            "ID",
                            "CREATED_DATE"
                        ],
                        [
                            "VARCHAR(45) UTF8",
                            "TIMESTAMP"
                        ],
                        [
                            45,
                            29
                        ],
                        [
                            null,
                            null
                        ],
                        [
                            true,
                            true
                        ],
                        [
                            false,
                            false
                        ],
                        [
                            null,
                            null
                        ],
                        [
                            null,
                            null
                        ],
                        [
                            "VARCHAR",
                            "TIMESTAMP"
                        ]
                    ]
                }
            }
        ],
        "numResults": 1
    }
}
[   {   'comment': None,
        'default': None,
        'distribution_key': False,
        'name': 'ID',
        'nulls': True,
        'scale': None,
        'size': 45,
        'sql_type': 'VARCHAR(45) UTF8',
        'type': 'VARCHAR'},
    {   'comment': None,
        'default': None,
        'distribution_key': False,
        'name': 'CREATED_DATE',
        'nulls': True,
        'scale': None,
        'size': 29,
        'sql_type': 'TIMESTAMP',
        'type': 'TIMESTAMP'}]
2019-11-21 13:48:50.690 Connection attempt [127.0.0.1:8563]
2019-11-21 13:48:50.719 [WebSocket request #1]
{
    "command": "login",
    "protocolVersion": 1
}
2019-11-21 13:48:50.756 [WebSocket response #1]
{
    "status": "ok",
    "responseData": {
        "publicKeyPem": "xxxx",
        "publicKeyExponent": "010001",
        "publicKeyModulus": "xxxx"
    }
}
2019-11-21 13:48:50.757 [WebSocket request #2]
{
    "username": "xxxx",
    "password": "xxxx",
    "driverName": "PyEXASOL 0.6.4",
    "clientName": "PyEXASOL",
    "clientVersion": "0.6.4",
    "clientOs": "Darwin-19.0.0-x86_64-i386-64bit",
    "clientOsUsername": "xxxx",
    "clientRuntime": "Python 3.7.3",
    "useCompression": false,
    "attributes": {
        "currentSchema": "RAW",
        "autocommit": true,
        "queryTimeout": 0,
        "snapshotTransactionsEnabled": false
    }
}
2019-11-21 13:48:50.820 [WebSocket response #2]
{
    "status": "ok",
    "responseData": {
        "protocolVersion": 1,
        "timeZone": "CET",
        "timeZoneBehavior": "INVALID SHIFT AMBIGUOUS ST",
        "sessionId": 1650819471279652848,
        "maxDataMessageSize": 67108864,
        "releaseVersion": "6.2.0",
        "databaseName": "DB1",
        "productName": "EXASolution",
        "maxIdentifierLength": 128,
        "maxVarcharLength": 2000000,
        "identifierQuoteString": "\""
    }
}
2019-11-21 13:48:50.820 [WebSocket request #3]
{
    "command": "getAttributes"
}
2019-11-21 13:48:50.859 [WebSocket response #3]
{
    "status": "ok",
    "attributes": {
        "datetimeFormat": "YYYY-MM-DD HH24:MI:SS.FF6",
        "dateFormat": "YYYY-MM-DD",
        "numericCharacters": ".,",
        "dateLanguage": "ENG",
        "queryTimeout": 0,
        "timezone": "CET",
        "timezoneBehavior": "INVALID SHIFT AMBIGUOUS ST",
        "defaultLikeEscapeCharacter": "\\",
        "autocommit": true,
        "compressionEnabled": false,
        "currentSchema": "RAW",
        "snapshotTransactionsEnabled": false,
        "openTransaction": 0
    }
}
2019-11-21 13:48:50.859 [WebSocket request #4]
{
    "command": "execute",
    "sqlText": "SELECT c.column_name, c.column_type, c.column_maxsize, c.column_num_scale,\n                   c.column_is_nullable, c.column_is_distribution_key, c.column_default,\n                   c.column_comment, t.type_name\n            FROM EXA_ALL_COLUMNS c\n                JOIN EXA_SQL_TYPES t ON (c.column_type_id=t.type_id)\n            WHERE c.column_schema='TEST'\n                AND c.column_table='TEST_TABLE'\n            ORDER BY c.column_ordinal_position"
}
2019-11-21 13:48:50.939 [WebSocket response #4]
{
    "status": "ok",
    "responseData": {
        "results": [
            {
                "resultType": "resultSet",
                "resultSet": {
                    "numColumns": 9,
                    "numRows": 0,
                    "numRowsInMessage": 0,
                    "columns": [
                        {
                            "name": "COLUMN_NAME",
                            "dataType": {
                                "type": "VARCHAR",
                                "size": 128,
                                "characterSet": "UTF8"
                            }
                        },
                        {
                            "name": "COLUMN_TYPE",
                            "dataType": {
                                "type": "VARCHAR",
                                "size": 40,
                                "characterSet": "UTF8"
                            }
                        },
                        {
                            "name": "COLUMN_MAXSIZE",
                            "dataType": {
                                "type": "DECIMAL",
                                "precision": 18,
                                "scale": 0
                            }
                        },
                        {
                            "name": "COLUMN_NUM_SCALE",
                            "dataType": {
                                "type": "DECIMAL",
                                "precision": 18,
                                "scale": 0
                            }
                        },
                        {
                            "name": "COLUMN_IS_NULLABLE",
                            "dataType": {
                                "type": "BOOLEAN"
                            }
                        },
                        {
                            "name": "COLUMN_IS_DISTRIBUTION_KEY",
                            "dataType": {
                                "type": "BOOLEAN"
                            }
                        },
                        {
                            "name": "COLUMN_DEFAULT",
                            "dataType": {
                                "type": "VARCHAR",
                                "size": 2000,
                                "characterSet": "UTF8"
                            }
                        },
                        {
                            "name": "COLUMN_COMMENT",
                            "dataType": {
                                "type": "VARCHAR",
                                "size": 2000,
                                "characterSet": "UTF8"
                            }
                        },
                        {
                            "name": "TYPE_NAME",
                            "dataType": {
                                "type": "VARCHAR",
                                "size": 40,
                                "characterSet": "UTF8"
                            }
                        }
                    ]
                }
            }
        ],
        "numResults": 1
    }
}
[]
littleK0i commented 4 years ago

@tokybe , oh, it's very interesting. Query looks good, but EXA_ALL_COLUMNS seems to be affected by currently opened schema, which is odd.

I've tested it with Exasol 6.0, and it did not reproduce. Maybe it's a bug specific for 6.2+.

Could you try calling .ext.get_columns() function instead of .ext.get_sys_columns()? https://github.com/badoo/pyexasol/blob/master/docs/REFERENCE.md#get_columns

Internally it uses different mechanism independent from system tables. Let's see if it works.

tokybe commented 4 years ago

It works fine with .ext.get_columns() but the reason I called ext.get_sys_columns() is to get the field comments.

littleK0i commented 4 years ago

@tokybe , I've tried to reproduce it once again using 6.2.0, but out of luck.

What are the permissions of user used for testing? Does this problem reproduce with root user (SYS)? Does user have access to view schema RAW?

Exasol introduced new privilege type for hide some schemas: https://www.exasol.com/support/browse/EXASOL-2474

Maybe it is related to described problem somehow.

tokybe commented 4 years ago

Hi @wildraid, the user used for testing is admin. Actually, I've tested with 6.2.0 and it did not reproduce. However our prod cluster is using 6.1.6, and it did reproduce there. So i think this is fixed in 6.2.0, we will upgrade soon.