bug in DataAccess model - wrong column names

there is abug with wrong fields mapping in models.py:
https://github.com/irods/python-irodsclient/blob/main/irods/models.py#L174C1-L180C1


    name = Column(String, "COL_DATA_ACCESS_NAME", 701)
    token_namespace = Column(String, "COL_DATA_TOKEN_NAMESPACE", 702)
    user_id = Column(Integer, "COL_DATA_ACCESS_USER_ID", 703)
    data_id = Column(Integer, "COL_DATA_ACCESS_DATA_ID", 704)

those should be without "COL_" prefix according to the iquest attrs and genquery queries failures: no column COL_DATA_ACCESS_USER_ID/COL_DATA_ACCESS_NAME found

Also user_id = Column(Integer fails in case of upper() function in sql as it integer not string processed in the query.
We were able to mitigate this problem by overwriting model with the following code:

from irods.models import DataAccess as iRODSDA
from irods.column import Column, Integer, String
class DataAccess(iRODSDA):
    type = Column(Integer, "DATA_ACCESS_TYPE", 700)
    name = Column(String, "DATA_ACCESS_NAME", 701)
    token_namespace = Column(**String**, "DATA_TOKEN_NAMESPACE", 702) # here we also mitigate Integer to avoid upper() sql bug
    user_id = Column(String, "DATA_ACCESS_USER_ID", 703)
    data_id = Column(Integer, "DATA_ACCESS_DATA_ID", 704)

ah, very good.

one note... your new class has the upper() fix/comment on the token_namespace... but it should be on user_id?

I put a comment in a wrong line, you are right. But the real mitigation is in a right place i think:
user_id = Column(String, "DATA_ACCESS_USER_ID", 703)
String instead of Integer in current code model:
user_id = Column(Integer, "COL_DATA_ACCESS_USER_ID", 703)
At least query works on iRODS side then, but probably the proper core fix will be better

right - okay.

the columns around that line are not consistent in either direction, so we'll investigate a bit more.

one update about the issue:
the main problem is when the DataAccess model is used in query Criterion: Criterion('=', DataAccess.name , permission) - then the actual string form the Model is used in the query fails with no column name found.
but when it is used for select list session.query(DataAccess) and filter is on correct columns - then all works and as apparently mapping is done via Id no the string name. Then it returns back the right model mapping for python by id and everything works.

i see. so we are overloading the access model (and maybe all models?) with two different methods to read/interrogate it.

@a-tsygan

I'm hesitant to change the type of a column, especially for ID fields, in a minor release - as it could be a breaking change for some. Granted, it doesn't come up as a problem for the query process itself, more follow-on code in an application that makes assumptions regarding the type of a fetched column.

Also: I am afraid I have not been able to reproduce "no column found" directly from an instance of using DataAccess in a PRC genquery. To this point, I found something interesting: I can for example modify the DataObject model by changing the column having integer value 403 to have the column name "XYZ" instead of "DATA_NAME", and yet I will not experience a bad query from it. This seems to be so because the XML protocol to the iRODS server communicates fields by their integer numbers rather than their names.

@a-tsygan , would it be possible for you to give examples of code written for the Python client, where errors occur from the incorrect column names or types that you've identified in the issue description? Also, do the errors you've seen (such as "no column found") appear in the irods log or in the cllient's error dumps?

Mainly because, in short, without the ability to reproduce the problems directly, I would not be able to write a proper test, let alone effectively troubleshoot the column type / name issues you've reported here.

@a-tsygan

    the main problem is when the DataAccess model is used in query Criterion: **Criterion('=', DataAccess.name , permission)** - then the actual string form the Model is used in the query fails with no column name found.

@a-tsygan - I tried the following query

l=list(s.query(DataAccess).filter(Criterion('=',DataAccess.name,'own')))

and it worked for me without error, so I'm not sure how you did it to receive the "no column found" error - ?

@d-w-moore

NB: we are using iRODS version 4.3.4
@d-w-moore at the moment I must partly agree with you, but partly disagree as i was trying to reproduce issue and was failing but suddenly hit the problem i saw and reported as a bug. I am not sure if that all can be related to a caching of a python classes, but with docker rebuild it should not influence... My current idea that while i had a mitigation in the code - the import order was fixing or hiding the bug: Here is the error i see when i use pure DataAccess module :

findDataObjectsByName

    RDMSLog.log_print(f"{result[DataObject.name]}:{result[Collection.name]}:{result[DataAccess.name]}:{result[DataAccess.user_id]}?")

                                                                             ~~~~~~^^^^^^^^^^^^^^^^^

KeyError: <irods.column.Column 701 COL_DATA_ACCESS_NAME>

This is already on fetch. So the query executes correctly (in case sensitive mode) (in case insensitive it fails, please see below.

The query object (case insensitive mode):

[(<irods.column.Column 403 DATA_NAME>, 403, 'DATA_NAME', 1), (<irods.column.Column 501 COLL_NAME>, 501, 'COLL_NAME', 1), (<irods.column.Column 701 COL_DATA_ACCESS_NAME>, 701, 'COL_DATA_ACCESS_NAME', 1), (<irods.column.Column 703 COL_DATA_ACCESS_USER_ID>, 703, 'COL_DATA_ACCESS_USER_ID', 1)]

[{'type': 'Criterion', 'column': None, 'operator': None, 'value': '%<filename>%', 'dict': {'op': 'like', 'query_key': <irods.column.Column 403 DATA_NAME>, 'value': '%<filename>%'}}, {'type': 'Criterion', 'column': None, 'operator': None, 'value': '<directory name>%', 'dict': {'op': 'like', 'query_key': <irods.column.Column 502 COLL_PARENT_NAME>, 'value': '<directory name>%'}}, {'type': 'Criterion', 'column': None, 'operator': None, 'value': 'own', 'dict': {'op': '=', 'query_key': <irods.column.Column 701 COL_DATA_ACCESS_NAME>, 'value': 'own'}}, {'type': 'Criterion', 'column': None, 'operator': None, 'value': '10038', 'dict': {'op': '=', 'query_key': <irods.column.Column 703 COL_DATA_ACCESS_USER_ID>, 'value': '10038'}}]

The principal python code (it is prototype code for DataAccess use to check only 'own' permission):

criterion build:

def cr_coll_in_path(p_inpath):
    l_criterion = []
    if (p_inpath):
        # Build search path
        l_criterion.append(Criterion('like', Collection.parent_name , p_inpath + "%"))
    return l_criterion

def cr_obj_permission(p_permission, p_user_id):
    l_criterion = []
    print(p_user_id, flush=True)
    l_permission = p_permission.lower()
    if (l_permission != 'any'):
        # Build search path
        l_criterion.append(Criterion('=', DataAccess.name , l_permission))
        l_criterion.append(Criterion("=", DataAccess.user_id, f"{p_user_id}"))
        #l_criterion.append(In(DataAccessLocal.user_id, [f"{p_user_id}", "1111", "123345"]))
    return l_criterion

search function (with current debug).

def findDataObjectsByName(    p_connector_token
                            , p_search_string
                            , p_inpath
                            , p_caseInsensitive = False
                            , p_created_by_flag = False
                            , p_created_by_val = ""
                            , p_created_between_flag = False
                            , p_created_start_date = ""
                            , p_created_end_date = ""
                            , p_modified_between_flag = False
                            , p_modified_start_date = ""
                            , p_modified_end_date = ""
                            , p_limit = g_default_query_limit
                            ):

    #RDMSLog.log_print(f"<DEBUG> findDataObjectsByName search:call: search_string = {p_search_string}, (p_created_by_flag, p_created_by) = ({p_created_by_flag}, {p_created_by_val})")
    #RDMSLog.log_print(f"<DEBUG> findDataObjectsByName search:call: (p_created_between_flag, p_created_start_date, p_created_end_date) = ({p_created_between_flag}, {p_created_start_date}, {p_created_end_date})")
    #RDMSLog.log_print(f"<DEBUG> findDataObjectsByName search:call: (p_modified_between_flag, p_modified_start_date, p_modified_end_date) = ({p_modified_between_flag}, {p_modified_start_date}, {p_modified_end_date})")
    l_connection = RugIRODSConnector(p_connector_token)
    l_session = l_connection.getSession()

    # list of criterions
    l_criterions = []

    # Search on collection names
    l_query = Query(l_session, DataObject.name, Collection.name, DataAccess.name, DataAccess.user_id)
    l_query.case_sensitive = p_caseInsensitive

    # add name
    if isSearchAllowed(p_search_string):
        c = Criterion('like', DataObject.name, get_like_condition_string_all(p_search_string))
        l_criterions.append(c)

    # build extra criterions
    l_criterions = l_criterions + build_all_search_criterions1_obj(
                                                  p_inpath
                                                , p_created_by_flag
                                                , p_created_by_val
                                                , p_created_between_flag
                                                , p_created_start_date
                                                , p_created_end_date
                                                , p_modified_between_flag
                                                , p_modified_start_date
                                                , p_modified_end_date
                                                , l_session
                                              )
    # add to the query before execution
    for cr in l_criterions:
        RDMSLog.log_print("criteria obj")
        RDMSLog.log_print(f"{cr.value}")
        RDMSLog.log_print(f"{cr.query_key}")
        l_query.filter(cr)

    l_query.continue_index(irods_query_get_default_continue_index_val())
    l_query.limit(p_limit)
    RDMSLog.log_print("l_query")
    RDMSLog.log_print(f"{l_query.columns}")
    RDMSLog.log_print(f"{l_query.criteria}")

    # OrderedDict of columns
    print([
        (column, column.icat_id, column.icat_key, value)
        for column, value in l_query.columns.items()
    ], flush=True)

    # List of Criterion objects
    print([
        {
            "type": type(c).__name__,
            "column": getattr(c, "column", None),
            "operator": getattr(c, "operator", None),
            "value": getattr(c, "value", None),
            "dict": vars(c),
        }
        for c in l_query.criteria
    ], flush=True)

    # Execute
    resultSet = l_query.execute()

    # Collect the results
    outPutObject = RugIRODSRestMsgSearchUtil()
    for result in l_query.get_results():
        RDMSLog.log_print(f"{result[DataObject.name]}:{result[Collection.name]}:{result[DataAccess.name]}:{result[DataAccess.user_id]}?")
        outPutObject.l_dataObjectList.append(result[Collection.name] + '/' + result[DataObject.name])
        outPutObject.l_foundObjectsOrCollections.append(result[Collection.name] + '/' + result[DataObject.name])

    l_session.cleanup()

    RDMSLog.log_print(f"<DEBUG>: l_count= {len(outPutObject.l_dataObjectList)} Files found")

    return outPutObject

What I was clearly able to reproduce issue with case insensitive mode - the problem of upper() usage when using DataAccess.user_id and at the same time I think my mitigation of setting type from Integer to String does not really work.

[(<irods.column.Column 403 DATA_NAME>, 403, 'DATA_NAME', 1), (<irods.column.Column 501 COLL_NAME>, 501, 'COLL_NAME', 1), (<irods.column.Column 701 COL_DATA_ACCESS_NAME>, 701, 'COL_DATA_ACCESS_NAME', 1), (<irods.column.Column 703 COL_DATA_ACCESS_USER_ID>, 703, 'COL_DATA_ACCESS_USER_ID', 1)]

[{'type': 'Criterion', 'column': None, 'operator': None, 'value': '%<filename>%', 'dict': {'op': 'like', 'query_key': <irods.column.Column 403 DATA_NAME>, 'value': '%<filename>%'}}, {'type': 'Criterion', 'column': None, 'operator': None, 'value': '<directory name>%', 'dict': {'op': 'like', 'query_key': <irods.column.Column 502 COLL_PARENT_NAME>, 'value': '<directory name>%'}}, {'type': 'Criterion', 'column': None, 'operator': None, 'value': 'OWN', 'dict': {'op': '=', 'query_key': <irods.column.Column 701 COL_DATA_ACCESS_NAME>, 'value': 'OWN'}}, {'type': 'Criterion', 'column': None, 'operator': None, 'value': '10038', 'dict': {'op': '=', 'query_key': <irods.column.Column 703 COL_DATA_ACCESS_USER_ID>, 'value': '10038'}}]

.... findDataObjectsByName

    resultSet = l_query.execute()

                ^^^^^^^^^^^^^^^^^

  File "/usr/local/lib/python3.12/site-packages/irods/query.py", line 206, in execute

    result_message = conn.recv()

                     ^^^^^^^^^^^

  File "/usr/local/lib/python3.12/site-packages/irods/connection.py", line 188, in recv

    raise exc

irods.exception.CAT_SQL_ERR: None

from the iRODS log:

2026-10-18 01:04:47.214 CEST [29695] ERROR:  function upper(bigint) does not exist at character 357

2026-10-18 01:04:47.214 CEST [29695] HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

2026-10-18 01:04:47.214 CEST [29695] STATEMENT:  select distinct R_DATA_MAIN.data_name ,R_COLL_MAIN.coll_name ,r_data_tokn_accs.token_name ,r_data_access.user_id  from  R_DATA_MAIN , R_COLL_MAIN , R_TOKN_MAIN r_data_tokn_accs , R_OBJT_ACCESS r_data_access  where upper (R_DATA_MAIN.data_name ) like $1  AND upper (R_COLL_MAIN.parent_coll_name ) like $2  AND upper (r_data_tokn_accs.token_name ) = $3  AND upper (r_data_access.user_id ) = $4  AND r_data_access.access_type_id = r_data_tokn_accs.token_id  AND R_COLL_MAIN.coll_id = R_DATA_MAIN.coll_id  AND R_DATA_MAIN.data_id = r_data_access.object_id  AND R_DATA_MAIN.data_id in (select object_id from R_OBJT_ACCESS OA, R_USER_GROUP UG, R_USER_MAIN UM, R_TOKN_MAIN TM where UM.user_name=$5 and UM.zone_name=$6 and UM.user_type_name!='rodsgroup' and UM.user_id = UG.user_id and UG.group_user_id = OA.user_id and OA.object_id = R_DATA_MAIN.data_id and OA.access_type_id >= TM.token_id and TM.token_namespace ='access_type' and TM.token_name = 'read_object') AND R_COLL_MAIN.coll_id in (select object_id from R_OBJT_ACCESS OA, R_USER_GROUP UG, R_USER_MAIN UM, R_TOKN_MAIN TM where UM.user_name=$7 and UM.zone_name=$8 and UM.user_type_name!='rodsgroup' and UM.user_id = UG.user_id and UG.group_user_id = OA.user_id and OA.object_id = R_COLL_MAIN.coll_id and OA.access_type_id >= TM.token_id and TM.token_namespace ='access_type' and TM.token_name = 'read_object') order by R_COLL_MAIN.coll_name, R_DATA_MAIN.data_name

@a-tsygan

@a-tsygan Ok, good enough, thanks! I may split this issue and just change the internal column_names by stripping off "COL_" for this milestone, since in a minor release it shouldn't matter. For the column type changes, I still think we can and should wait for the 4.0 release, for reasons already stated, so for that I'll make another issue.

@a-tsygan

@a-tsygan It's evident now that the problem of UPPER(...) being applied to integer fields is an acknowledged server bug. See: irods/irods#8222
You can reproduce that bug from this iCommands query:

iquest uppercase "SELECT DATA_ACCESS_NAME WHERE DATA_ACCESS_USER_ID != '0'"

Or the equivalent:

q = sessionObject.query(DataAccess.name,case_sensitive=False).filter(
DataAccess.user_id != '0')

And, as I believe you've also seen, changing the type of the user_id field in the Python client doesn't address the problem.

@a-tsygan

I've also gone through the Python client code exhaustively and have not seen any way in which the icat_key (which holds the column names such as "COL_DATA_ACCESS_NAME", for example) is instrumental in interfacing to the server unless the very much niche Keyword class is used - of which there only seem to be two members, values corresponding to 'chksum' and 'dataType', and I don't even know what these are used for! Do you use them anywhere in your applicaion? If no, then I am still not seeing where the 'COL_' prefixes are creating errors. Granted they may be thought of as technically incorrect, and may be inconsistent with the representation of other database columns in the Python iRODS Client, but this seems a different matter altogether.

In any case I'd suggest putting this off to another milestone, due to the uncertainty in the nature of the bug in the Python client code, if indeed there is any. @a-tsygan , if you have some minimal code excerpt you can provide in which an incorrect column name gets flagged as an error as a result of its execution - it would be a great help for me to see it.

Navigation Menu

Repository navigation

bug in DataAccess model - wrong column names #841

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

bug in DataAccess model - wrong column names #841

Description

Activity

trel commented on Oct 14, 2026

a-tsygan commented on Oct 14, 2026

trel commented on Oct 14, 2026

a-tsygan commented on Oct 15, 2026

trel commented on Oct 15, 2026

d-w-moore commented on Oct 16, 2026

d-w-moore commented on Oct 17, 2026

a-tsygan commented on Oct 17, 2026

d-w-moore commented on Oct 19, 2026

d-w-moore commented on Oct 20, 2026

d-w-moore commented on Oct 20, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions