Query engine renders catalog number "" as "0" for CatalogNumberNumeric formatter

[benanhalt]

This is a backend bug.

$ curl 'http://localhost:8000/stored_query/ephemeral/' -H 'User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101 Firefox/91.0' -H 'Accept: application/json' -H 'Accept-Language: en-US,en;q=0.5' --compressed -H 'Referer: http://localhost:8000/specify/query/3/' -H 'Content-Type: application/json' -H 'X-CSRFToken: dM8uDEoLbw8OTs3odILXHLA9kX3fe3fOZsv1Dwe2cSoLhUoe0qkjdYuVTWlGgfDT' -H 'Origin: http://localhost:8000' -H 'Connection: keep-alive' -H 'Cookie: csrftoken=dM8uDEoLbw8OTs3odILXHLA9kX3fe3fOZsv1Dwe2cSoLhUoe0qkjdYuVTWlGgfDT; collection=4; language=en-us; sessionid=6ezlay4vrlg1mhlh2xd7g40ps31jfjtp' -H 'Sec-Fetch-Dest: empty' -H 'Sec-Fetch-Mode: cors' -H 'Sec-Fetch-Site: same-origin' -H 'Pragma: no-cache' -H 'Cache-Control: no-cache' --data-raw '{"id":3,"contextname":"CollectionObject","contexttableid":1,"countonly":false,"formatauditrecids":false,"isfavorite":true,"name":"Number Tags Query","ordinal":0,"remarks":null,"searchsynonymy":false,"selectdistinct":false,"smushed":false,"sqlstr":null,"timestampcreated":"2012-10-22T10:17:29","timestampmodified":"2012-10-22T10:17:29","version":5,"createdbyagent":"/api/specify/agent/1514/","modifiedbyagent":null,"specifyuser":"/api/specify/specifyuser/2/","reports":"/api/specify/spreport/?query=3","resource_uri":"/api/specify/spquery/3/","fields":[{"tablelist":"1","stringid":"1.collectionobject.catalogNumber","fieldname":"catalogNumber","isrelfld":false,"sorttype":1,"position":0,"isdisplay":true,"operstart":8,"startvalue":"","isnot":false}],"limit":40,"offset":0}'
{"results": [[5327, "0"], [6846, "1"],

Versus

$ curl 'http://localhost:8000/api/specify/collectionobject/5327/' -H 'User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101 Firefox/91.0' -H 'Accept: application/json' -H 'Accept-Language: en-US,en;q=0.5' --compressed -H 'Referer: http://localhost:8000/specify/view/collectionobject/5327/' -H 'Content-Type: undefined' -H 'Connection: keep-alive' -H 'Cookie: csrftoken=dM8uDEoLbw8OTs3odILXHLA9kX3fe3fOZsv1Dwe2cSoLhUoe0qkjdYuVTWlGgfDT; collection=4; language=en-us; sessionid=6ezlay4vrlg1mhlh2xd7g40ps31jfjtp' -H 'Sec-Fetch-Dest: empty' -H 'Sec-Fetch-Mode: cors' -H 'Sec-Fetch-Site: same-origin' -H 'Pragma: no-cache' -H 'Cache-Control: no-cache'
{"id": 5327, "altcatalognumber": null, "availability": null, "catalognumber": "", "catalogeddate": "2012-03-30",

[acwhite211]

I was able to recreate the issue in the main branch. When creating a collection object with a null catalog ID, the QB returns that collection object's catalog number as 0.

Here's the results from testing the exact curl request mentioned in the issue on v7.11.2:

curl 'http://localhost:8000/stored_query/ephemeral/' \
  -H 'User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101 Firefox/91.0' \
  -H 'Accept: application/json' \
  -H 'Accept-Language: en-US,en;q=0.5' \
  --compressed \
  -H 'Referer: http://localhost:8000/specify/query/3/' \
  -H 'Content-Type: application/json' \
  -H 'X-CSRFToken: 29l4fiyc5iSYqQBC8FMRy28HXliwsJTK' \
  -H 'Origin: http://localhost:8000' \
  -H 'Connection: keep-alive' \
  -H 'Cookie: csrftoken=29l4fiyc5iSYqQBC8FMRy28HXliwsJTK; collection=4; language=en-us; sessionid=nocivhbh3gm2vractjsebe8oodtusaor' \
  -H 'Sec-Fetch-Dest: empty' \
  -H 'Sec-Fetch-Mode: cors' \
  -H 'Sec-Fetch-Site: same-origin' \
  -H 'Pragma: no-cache' \
  -H 'Cache-Control: no-cache' \
  --data-raw '{
    "id": 3,
    "contextname": "CollectionObject",
    "contexttableid": 1,
    "countonly": false,
    "formatauditrecids": false,
    "isfavorite": true,
    "name": "Number Tags Query",
    "ordinal": 0,
    "remarks": null,
    "searchsynonymy": false,
    "selectdistinct": false,
    "smushed": false,
    "sqlstr": null,
    "timestampcreated": "2012-10-22T10:17:29",
    "timestampmodified": "2012-10-22T10:17:29",
    "version": 5,
    "createdbyagent": "/api/specify/agent/1514/",
    "modifiedbyagent": null,
    "specifyuser": "/api/specify/specifyuser/2/",
    "reports": "/api/specify/spreport/?query=3",
    "resource_uri": "/api/specify/spquery/3/",
    "fields": [
      {
        "tablelist": "1",
        "stringid": "1.collectionobject.catalogNumber",
        "fieldname": "catalogNumber",
        "isrelfld": false,
        "sorttype": 1,
        "position": 0,
        "isdisplay": true,
        "operstart": 8,
        "startvalue": "",
        "isnot": false
      }
    ],
    "limit": 40,
    "offset": 0
  }'

=>

{"results": [[6846, "1"], [6847, "2"], [6848, "3"], [6849, "4"], [6850, "5"], [6851, "6"], [6852, "7"], [6853, "8"], [6854, "9"], [6855, "10"], [6856, "11"], [6857, "12"], [6858, "13"], [6859, "14"], [6860, "15"], [6861, "16"], [6862, "17"], [6863, "18"], [6864, "19"], [6865, "20"], [6866, "21"], [6867, "22"], [6868, "23"], [6869, "24"], [6870, "25"], [6871, "26"], [6872, "27"], [6873, "28"], [6874, "29"], [6875, "30"], [6876, "31"], [6877, "32"], [6878, "33"], [6879, "34"], [6880, "35"], [6881, "36"], [6882, "37"], [6883, "38"], [6884, "39"], [6885, "40"]]}

vs

curl 'http://localhost:8000/api/specify/collectionobject/5327/' \
  -H 'User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101 Firefox/91.0' \
  -H 'Accept: application/json' \
  -H 'Accept-Language: en-US,en;q=0.5' \
  --compressed \
  -H 'Referer: http://localhost:8000/specify/view/collectionobject/5327/' \
  -H 'Content-Type: undefined' \
  -H 'Connection: keep-alive' \
  -H 'Cookie: csrftoken=29l4fiyc5iSYqQBC8FMRy28HXliwsJTK; collection=4; language=en-us; sessionid=nocivhbh3gm2vractjsebe8oodtusaor' \
  -H 'Sec-Fetch-Dest: empty' \
  -H 'Sec-Fetch-Mode: cors' \
  -H 'Sec-Fetch-Site: same-origin' \
  -H 'Pragma: no-cache' \
  -H 'Cache-Control: no-cache'

=>

{"cojo": null, "id": 5327, "altcatalognumber": null, "availability": null, "catalognumber": "000041148", "catalogeddate": "2012-03-30", "catalogeddateprecision": 1, "catalogeddateverbatim": null, "collectionmemberid": 4, "countamt": 1, "date1": null, "date1precision": null, "deaccessioned": false, "description": null, "embargoreason": null, "embargoreleasedate": null, "embargoreleasedateprecision": null, "embargostartdate": null, "embargostartdateprecision": null, "fieldnumber": null, "guid": "db71520c-1ed3-11e3-bfac-90b11c41863e", "integer1": null, "integer2": null, "inventorydate": null, "inventorydateprecision": null, "modifier": null, "name": null, "notifications": null, "number1": null, "number2": null, "numberofduplicates": null, "objectcondition": null, "ocr": null, "projectnumber": null, "remarks": null, "reservedinteger3": null, "reservedinteger4": null, "reservedtext": null, "reservedtext2": null, "reservedtext3": null, "restrictions": null, "sgrstatus": null, "text1": null, "text2": null, "text3": null, "text4": null, "text5": null, "text6": null, "text7": null, "text8": null, "timestampcreated": "2012-03-30T02:51:54", "timestampmodified": "2012-10-25T15:59:34", "totalvalue": null, "uniqueidentifier": null, "version": 2, "visibility": 0, "yesno1": null, "yesno2": null, "yesno3": null, "yesno4": null, "yesno5": null, "yesno6": null, "accession": "/api/specify/accession/580/", "agent1": null, "appraisal": null, "cataloger": "/api/specify/agent/1514/", "collectingevent": "/api/specify/collectingevent/1049/", "collection": "/api/specify/collection/4/", "collectionobjectattribute": {"id": 40695, "bottomdistance": null, "collectionmemberid": 4, "date1": null, "date1precision": null, "direction": null, "distanceunits": null, "integer1": null, "integer10": null, "integer2": null, "integer3": null, "integer4": null, "integer5": null, "integer6": null, "integer7": null, "integer8": null, "integer9": null, "number1": null, "number10": null, "number11": null, "number12": null, "number13": null, "number14": null, "number15": null, "number16": null, "number17": null, "number18": null, "number19": null, "number2": null, "number20": null, "number21": null, "number22": null, "number23": null, "number24": null, "number25": null, "number26": null, "number27": null, "number28": null, "number29": null, "number3": null, "number30": null, "number31": null, "number32": null, "number33": null, "number34": null, "number35": null, "number36": null, "number37": null, "number38": null, "number39": null, "number4": null, "number40": null, "number41": null, "number42": null, "number5": null, "number6": null, "number7": null, "number8": null, "number9": null, "positionstate": null, "remarks": null, "text1": null, "text10": null, "text11": "103mm SL", "text12": null, "text13": null, "text14": null, "text15": null, "text16": null, "text17": null, "text18": null, "text19": null, "text2": null, "text20": null, "text21": null, "text22": null, "text23": null, "text24": null, "text25": null, "text26": null, "text27": null, "text28": null, "text29": null, "text3": null, "text30": null, "text31": null, "text32": null, "text33": null, "text34": null, "text35": null, "text36": null, "text37": null, "text38": null, "text39": null, "text4": null, "text40": null, "text5": null, "text6": null, "text7": null, "text8": null, "text9": null, "timestampcreated": "2012-03-30T02:51:54", "timestampmodified": null, "topdistance": null, "version": 0, "yesno1": null, "yesno10": null, "yesno11": null, "yesno12": null, "yesno13": null, "yesno14": null, "yesno15": null, "yesno16": null, "yesno17": null, "yesno18": null, "yesno19": null, "yesno2": null, "yesno20": null, "yesno3": null, "yesno4": null, "yesno5": null, "yesno6": null, "yesno7": null, "yesno8": null, "yesno9": null, "agent1": null, "createdbyagent": "/api/specify/agent/1/", "modifiedbyagent": null, "collectionobjects": "/api/specify/collectionobject/?collectionobjectattribute=40695", "resource_uri": "/api/specify/collectionobjectattribute/40695/"}, "container": null, "containerowner": null, "createdbyagent": "/api/specify/agent/1/", "embargoauthority": null, "fieldnotebookpage": null, "inventorizedby": null, "modifiedbyagent": "/api/specify/agent/2151/", "paleocontext": null, "visibilitysetby": null, "collectionobjecttype": "/api/specify/collectionobjecttype/1/", "componentParent": null, "collectionobjectattachments": [{"id": 3758, "collectionmemberid": 4, "ordinal": 0, "remarks": null, "timestampcreated": "2012-10-25T15:58:56", "timestampmodified": "2012-10-25T15:58:56", "version": 0, "attachment": {"id": 3758, "attachmentlocation": "sp68149011782445641734.att.JPG", "attachmentstorageconfig": null, "capturedevice": null, "copyrightdate": null, "copyrightholder": null, "credit": null, "dateimaged": null, "filecreateddate": null, "guid": "92c514b4-a6e7-462d-b1d7-2e4b912d3d82", "ispublic": true, "license": null, "licenselogourl": null, "metadatatext": null, "mimetype": "image/jpeg", "origfilename": "C:\\COLLECTIONS\\DATABASE IMAGES\\To Add\\Aquarium tissue vouchers\\T3404.JPG", "remarks": null, "scopeid": 4, "scopetype": 0, "subjectorientation": null, "subtype": null, "tableid": 1, "timestampcreated": "2012-10-25T15:58:56", "timestampmodified": "2012-10-25T15:58:56", "title": "T3404", "type": null, "version": 3, "visibility": null, "attachmentimageattribute": null, "createdbyagent": "/api/specify/agent/2151/", "creator": null, "modifiedbyagent": null, "visibilitysetby": null, "accessionattachments": "/api/specify/accessionattachment/?attachment=3758", "agentattachments": "/api/specify/agentattachment/?attachment=3758", "metadata": "/api/specify/attachmentmetadata/?attachment=3758", "tags": "/api/specify/attachmenttag/?attachment=3758", "borrowattachments": "/api/specify/borrowattachment/?attachment=3758", "collectingeventattachments": "/api/specify/collectingeventattachment/?attachment=3758", "collectingtripattachments": "/api/specify/collectingtripattachment/?attachment=3758", "collectionobjectattachments": "/api/specify/collectionobjectattachment/?attachment=3758", "conservdescriptionattachments": "/api/specify/conservdescriptionattachment/?attachment=3758", "conserveventattachments": "/api/specify/conserveventattachment/?attachment=3758", "dnasequenceattachments": "/api/specify/dnasequenceattachment/?attachment=3758", "dnasequencingrunattachments": "/api/specify/dnasequencingrunattachment/?attachment=3758", "deaccessionattachments": "/api/specify/deaccessionattachment/?attachment=3758", "disposalattachments": "/api/specify/disposalattachment/?attachment=3758", "exchangeinattachments": "/api/specify/exchangeinattachment/?attachment=3758", "exchangeoutattachments": "/api/specify/exchangeoutattachment/?attachment=3758", "fieldnotebookattachments": "/api/specify/fieldnotebookattachment/?attachment=3758", "fieldnotebookpageattachments": "/api/specify/fieldnotebookpageattachment/?attachment=3758", "fieldnotebookpagesetattachments": "/api/specify/fieldnotebookpagesetattachment/?attachment=3758", "giftattachments": "/api/specify/giftattachment/?attachment=3758", "loanattachments": "/api/specify/loanattachment/?attachment=3758", "localityattachments": "/api/specify/localityattachment/?attachment=3758", "permitattachments": "/api/specify/permitattachment/?attachment=3758", "preparationattachments": "/api/specify/preparationattachment/?attachment=3758", "referenceworkattachments": "/api/specify/referenceworkattachment/?attachment=3758", "repositoryagreementattachments": "/api/specify/repositoryagreementattachment/?attachment=3758", "storageattachments": "/api/specify/storageattachment/?attachment=3758", "taxonattachments": "/api/specify/taxonattachment/?attachment=3758", "treatmenteventattachments": "/api/specify/treatmenteventattachment/?attachment=3758", "spdatasetattachments": "/api/specify/spdatasetattachment/?attachment=3758", "resource_uri": "/api/specify/attachment/3758/"}, "collectionobject": "/api/specify/collectionobject/5327/", "createdbyagent": "/api/specify/agent/2151/", "modifiedbyagent": null, "resource_uri": "/api/specify/collectionobjectattachment/3758/"}], "collectionobjectattrs": [], "collectionobjectcitations": [], "collectionobjectproperties": [], "leftsiderels": [{"id": 5604, "text1": null, "text2": null, "timestampcreated": "2012-08-16T13:24:25", "timestampmodified": null, "version": 0, "collectionreltype": "/api/specify/collectionreltype/1/", "createdbyagent": "/api/specify/agent/1/", "leftside": "/api/specify/collectionobject/5327/", "modifiedbyagent": null, "rightside": "/api/specify/collectionobject/51400/", "resource_uri": "/api/specify/collectionrelationship/5604/"}], "rightsiderels": [{"id": 5603, "text1": null, "text2": null, "timestampcreated": "2012-10-25T15:59:26", "timestampmodified": "2012-10-25T15:59:26", "version": 0, "collectionreltype": "/api/specify/collectionreltype/1/", "createdbyagent": "/api/specify/agent/2151/", "leftside": "/api/specify/collectionobject/51400/", "modifiedbyagent": null, "rightside": "/api/specify/collectionobject/5327/", "resource_uri": "/api/specify/collectionrelationship/5603/"}], "conservdescriptions": [], "dnasequences": [], "determinations": [{"id": 38561, "addendum": null, "alternatename": null, "collectionmemberid": 4, "confidence": null, "determineddate": "2000-04-07", "determineddateprecision": 1, "featureorbasis": null, "guid": "df0aa5ee-1ed3-11e3-bfac-90b11c41863e", "integer1": null, "integer2": null, "integer3": null, "integer4": null, "integer5": null, "iscurrent": true, "method": null, "nameusage": null, "number1": null, "number2": null, "number3": null, "number4": null, "number5": null, "qualifier": null, "remarks": null, "subspqualifier": null, "text1": null, "text2": null, "text3": null, "text4": null, "text5": null, "text6": null, "text7": null, "text8": null, "timestampcreated": "2012-03-30T14:51:54", "timestampmodified": "2012-04-19T16:32:27", "typestatusname": null, "varqualifier": null, "version": 1, "yesno1": false, "yesno2": null, "yesno3": null, "yesno4": null, "yesno5": null, "collectionobject": "/api/specify/collectionobject/5327/", "createdbyagent": "/api/specify/agent/1/", "determiner": "/api/specify/agent/162/", "modifiedbyagent": "/api/specify/agent/1514/", "preferredtaxon": "/api/specify/taxon/12027/", "taxon": "/api/specify/taxon/12027/", "determinationcitations": [], "determiners": [], "resource_uri": "/api/specify/determination/38561/"}], "exsiccataitems": [], "otheridentifiers": [], "preparations": [{"id": 38008, "barcode": null, "collectionmemberid": 4, "countamt": 1, "date1": null, "date1precision": null, "date2": null, "date2precision": null, "date3": null, "date3precision": null, "date4": null, "date4precision": null, "description": null, "guid": "394f154e-eb8d-11e9-b886-bc305ba00e24", "integer1": null, "integer2": null, "number1": null, "number2": null, "prepareddate": null, "prepareddateprecision": 1, "remarks": null, "reservedinteger3": null, "reservedinteger4": null, "samplenumber": null, "status": null, "storagelocation": null, "text1": null, "text10": null, "text11": null, "text12": null, "text13": null, "text2": "Jar", "text3": null, "text4": null, "text5": null, "text6": null, "text7": null, "text8": null, "text9": null, "timestampcreated": "2012-03-30T14:51:54", "timestampmodified": "2019-10-10T13:38:55", "version": 1, "yesno1": null, "yesno2": null, "yesno3": null, "alternatestorage": null, "collectionobject": "/api/specify/collectionobject/5327/", "createdbyagent": "/api/specify/agent/1/", "modifiedbyagent": "/api/specify/agent/1/", "preptype": "/api/specify/preptype/2/", "preparationattribute": null, "preparedbyagent": null, "storage": null, "conservdescriptions": "/api/specify/conservdescription/?preparation=38008", "disposalpreparations": "/api/specify/disposalpreparation/?preparation=38008", "exchangeinpreps": "/api/specify/exchangeinprep/?preparation=38008", "exchangeoutpreps": "/api/specify/exchangeoutprep/?preparation=38008", "giftpreparations": "/api/specify/giftpreparation/?preparation=38008", "loanpreparations": "/api/specify/loanpreparation/?preparation=38008", "materialsamples": [], "preparationattachments": [], "preparationattrs": [], "preparationproperties": [], "resource_uri": "/api/specify/preparation/38008/", "actualCountAmt": 1, "isonloan": false, "isongift": false, "isondisposal": false, "isonexchangeout": false, "isonexchangein": false}], "treatmentevents": [], "voucherrelationships": [], "absoluteages": [], "relativeages": [], "resource_uri": "/api/specify/collectionobject/5327/", "actualTotalCountAmt": 1, "totalCountAmt": 1, "currentdetermination": "/api/specify/determination/38561/", "isMemberOfCOG": false}%                                                                                                                                                                                    

[acwhite211]

The issue arises in the sql query that gets generated. The catalognumber field is casted to a decimal.

SELECT
	collectionobject.`CollectionObjectID`,
	CAST(collectionobject.`CatalogNumber` AS DECIMAL(65)) AS `CatalogNumber`
FROM
	collectionobject
WHERE
	collectionobject.`CollectionID` = 4
	AND (collectionobject.`CatalogNumber` = ''
		OR collectionobject.`CatalogNumber` IS NULL)
LIMIT 0, 40;

=>

|CollectionObjectID|CatalogNumber|
|------------------|-------------|
|51,736            |0            |

If you take out the casting function, then you get null return instead of 0

SELECT
	collectionobject.`CollectionObjectID`,
	collectionobject.`CatalogNumber` AS `CatalogNumber`
FROM
	collectionobject
WHERE
	collectionobject.`CollectionID` = 4
	AND (collectionobject.`CatalogNumber` = ''
		OR collectionobject.`CatalogNumber` IS NULL)
LIMIT 0, 40;

=>

|CollectionObjectID|CatalogNumber|
|------------------|-------------|
|51,736            |             |

We need this casting for a lot of our fields in the QB, so we would need to make a special condition to avoid casting on the collection object's catalog number field (if that's something we want to actually implement).

The query that we probably would want generated by the QB would be this:

SELECT
	collectionobject.`CollectionObjectID`,
	CASE
		WHEN collectionobject.`CatalogNumber` IS NULL
		OR collectionobject.`CatalogNumber` = '' THEN ''
		ELSE CAST(collectionobject.`CatalogNumber` AS DECIMAL(65))
	END AS `CatalogNumber`
FROM
	collectionobject
WHERE
	collectionobject.`CollectionID` = 4
	AND (collectionobject.`CatalogNumber` = ''
		OR collectionobject.`CatalogNumber` IS NULL)
LIMIT 0, 40;

=>

|CollectionObjectID|CatalogNumber|
|------------------|-------------|
|51,736            |             |

[grantfitzsimmons]

Wait, does #7469 fix this issue? @acwhite211