Arcpy Describe returns connected user as schema owner

[PhilLarkin1]

When returning the name of a Feature Class used to create a Query Layer, the schema name is incorrect. It matches the name of the user in the SDE connection file. 

In this example I'd expect to the see the name with the correct schema owner: dboExample. However I get userA.

If I create a Feature Layer with the same SDE connection the correct name is returned: assrsde.dboExample.vertical_units

This seems like a bug. Tested on 10.3 and Pro 2.3 arcpy.

input = "...\\userA_Staging_ASSRSDE.sde"

query = "SELECT pid_num as parcel, Shape FROM ASSRSDE.dboExample.Vertical_Units WHERE ISNULL(PID_NUM, '') <> ''"

parcel = arcpy.MakeQueryLayer_management(input_database=input,out_layer_name="memory\\VU",query=query)

print( arcpy.Describe(parcel).baseName.lower())

# returns: assrsde.userA.vertical_units‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[MarcoBoeringa]

I don't think this is a bug, but by design. The problem is, while your particular example of a Query Layer's SQL statement is relatively straightforward pulling just a small record set from a single Feature Class, in reality a SQL statement for a Query Layer can have many forms, it can be a statement with SUM and GROUP BY, JOIN data of multiple Feature Classes or other sub queries etc. Contrary to Feature Layers, that just reference a single Feature Class (although that might be a database view), there is no real single data source for a Query Layer, and as such, the schema is likely undefined as well.

[PhilLarkin1]

Thanks for your reply. That makes some sense. 

I guess I'll need to alias the table. Connecting as the schema owner just to get the correct name from a simple query is overkill. 

[PhilLarkin1]

I can't even alias my way out of this problem (example 1). Describe still returns the connected user as the schema owner. It even seems arbitrary which table name is returned (example 2).

1)

input = "...\\userA_Staging_ASSRSDE.sde"

query = """SELECT pid_num as parcel FROM ASSRSDE.dboExample.vertical_units "ASSRSDE.dboExample.vertical_units" """
parcel = arcpy.MakeQueryLayer_management(input_database=input,out_layer_name="memory\\VU",query=query)

print( arcpy.Describe(parcel).baseName)

# returns: 'ASSRSDE.userA.%vertical_units'

2) 

input = "...\\userA_Staging_ASSRSDE.sde"

query = "SELECT ta.pid_num as parcel FROM ASSRSDE.dboExample.tableA ta, ASSRSDE.dboExample.vertical_units"
parcel = arcpy.MakeQueryLayer_management(input_database=input,out_layer_name="memory\\VU",query=query)

print( arcpy.Describe(parcel).baseName)

# returns: 'ASSRSDE.userA.%vertical_unis'

[JoshuaBixby]

Likely unrelated, but your output layer name is odd.  Are you specifying "memory" in the name because you want the name "memory" in the layer name, or are you trying to create the layer in a specific workspace?  Layers and table views are created in-memory by default, you don't have to put the in-memory workspace in the name.  In fact, doing so might cause odd behavior.

[JoshuaBixby]

The results you are seeing are being driven by the connection properties on userA_Staging_ASSRSDE.sde. What are the connection properties in the connection file?

[PhilLarkin1]

HI Joshua -

The connection is SQL Server with Database Authentication.

[JoshuaBixby]

And I am guessing the database user is "userA".  At this point, I am more confused about what you are attempting to describe by using Describe the way you are rather than the results being returned.  What information are you trying to get from looking up the baseName property of a feature layer?

[PhilLarkin1]

I was expecting that arcpy.Describe would return the fully qualified name of the Query Layer. This would show the true schema owner, as opposed to the user that is connected. In the several examples i've shown my desired return from Describe().baseName:

ASSRSDE.dboExample.Vertical_Units

what I get is:

assrsde.userA.vertical_units <- User connected (Input_database parameter)

At the very least, if I am aliasing a table in the FROM clause it would be nice if that was honored as the baseName. 

[JoshuaBixby]

I ask the following in all seriousness, what is your idea of a "fully qualified name" for a Query Layer?  Is the fully qualified name the first table referenced in the SQL that defines the Query Layer?  Or, is the fully qualified name the name of the layer file on disk when/if the Query Layer is exported as a layer file?

I think this boils down to a difference between expectations and implementation, i.e., Esri's implementation of Arcpy Describe's baseName for feature layers and table views doesn't line up with what you would like to see.  That said, I don't think Esri's implementation is necessarily wrong, just poorly documented.

Looking at the Describe—ArcPy Functions | ArcGIS Desktop  documentation, baseName is a Describe object  property,  i.e., it is a generic property and not a property specific to feature layers or table views. 

Summary

The Describe function returns the following properties for all Describe objects.

Properties

Property	Explanation	Data Type
baseName (Read Only)	The file base name	String

So, what does "file base name" mean in the context of something like feature layers or table views that don't exist as a file?

For several Describe object properties (baseName, catalogPath, path) of feature layers and table views, Esri maps the properties to the properties of the underlying data source.  I personally disagree with this decision; but regardless of my personal view, Esri doesn't document this decision and it leads to confusion.

Query layers represent a unique kind of feature layer because the data source isn't a specific feature class or table, but an SQL statement.  When a feature layer references a feature class, the mapping of baseName to the underlying baseName of the data source is straightforward; however, that mapping gets much more complicated when an SQL statement can reference any number of feature classes or tables.

If you have specific suggestions/ideas on how baseName should be populated with query layers, the path forward at this point is to submit an ArcGIS Ideas‌ and a formal Enhancement Request through Esri Support.