BUG

Incorrect query results when data contains many wide columns

Last Published: April 25, 2020

Description

When service data contain very wide columns, such as many columns of type VARCHAR(8000), a SPATIALQUERY may return records successfully, but only for a limited number of such fields. Beyond that number, the Spatial server returns '0' records. This is an incorrect result and is being investigated as a bug.

For example, a query with a valid ‘where’ clause and subfields="#ID# #SHAPE#" returns N records. It will continue to return N records as more columns are added to ‘subfields’. This is an example of the request:

Code:
<ARCXML version="1.1">
<REQUEST>
<GET_FEATURES featurelimit="25" beginrecord="0" outputmode="xml" geometry="false" envelope="true" >
<LAYER id="0" />
<SPATIALQUERY subfields="#ID# #SHAPE# FIELD_1 FIELD_2 WIDEFIELD_3 " where="TEST.TEST.DISTRICT.TEAMS > 2" >
</SPATIALQUERY>
</GET_FEATURES>
</REQUEST>
</ARCXML>

This is the output from the QueryServer:

Code:
<ARCXML version=”1.1”>
<RESPONSE>
<FEATURES>
<FEATURECOUNT count=”5” hasmore=”false” />
</FEATURES>
</RESPONSE>
</ARCXML>


However, a limit will be reached where the query works with 10 subfields columns, but it fails with 11+ columns in the list. Here is an example of the request:

Code:
<ARCXML version="1.1">
<REQUEST>
<GET_FEATURES featurelimit="25" beginrecord="0" outputmode="xml" geometry="false" envelope="true" >
<LAYER id="0" />
<SPATIALQUERY subfields="#ID# #SHAPE# FIELD_1 FIELD_2 WIDEFIELD_3 WIDEFIELD_4 WIDEFIELD_5 WIDEFIELD_6" where="TEST.TEST.DISTRICT.TEAMS > 2" >
</SPATIALQUERY>
</GET_FEATURES>
</REQUEST>
</ARCXML>

And this is the reponse:

Code:
<ARCXML version=”1.1”>
<RESPONSE>
<FEATURES>
<FEATURECOUNT count=”0” hasmore=”false” />
</FEATURES>
</RESPONSE>
</ARCXML>


Note:
A SPATIALQUERY with subfields=”” or subfields=”#ALL#" also returns 0 records.

Cause

Although the created tables use columns of extreme width, it is also possible that certain view-creation transactions and other SQL commands (such as 'replace'), may inadvertently cause columns to become the varchar(8000) data type.

Workaround

Remove or alter such columns so that they are only as wide as necessary for the anticipated record values.

    Article ID:000008308

    Software:
    • Legacy Products

    Receive notifications and find solutions for new or common issues

    Get summarized answers and video solutions from our new AI chatbot.

    Download the Esri Support App

    Discover more on this topic

    Get help from ArcGIS experts

    Contact technical support

    Download the Esri Support App

    Go to download options