Bug with VIEW and PROJECT that SQL SELECT has extra Fields from Keys?

1

I think I found a bug, but I am not sure. Might be found earlier but I never noticed before.

I have this view defined:

ViewARS             VIEW(ARules)
                      PROJECT(ARS:ARuleID)
                      PROJECT(ARS:Sequence)
                      PROJECT(ARS:ObjectID)
                      JOIN(OBJ:OBJ_pkey,ARS:ObjectID)
                        PROJECT(OBJ:ObjectID)
                        PROJECT(OBJ:Naam)
                        PROJECT(OBJ:Nummer)
                      END
                    END

When I do a trace on a Set/Loop/Next there is this output in the trace:

SELECT  A.ARuleID, A.KLANTID, A.Sequence, A.WERKWIJZEID, A.OBJECTID, B.ObjectID, B.KLANTID, B.NAAM, B.NUMMER, B.OBJECTSOORTID, B.MATERIAALSOORTID, B.RUIMTEID, B.MACHINECODE 
FROM  {oj dbo.ARules A LEFT OUTER JOIN dbo.Objecten B ON  A.OBJECTID= B.ObjectID }  
WHERE ...

To my surprise there are more fields in the generated SELECT statement then I defined in my view structure. Not all the fields of the tables are added but only the fields that are also present in a key defined in the dictionary.

For example I only have three fields of the table Objecten in my view structure. In the Select statement there are five more (B alias). And these five are used in one or more keys that I have in the dictionary. Same happens for the A alias.

Has anyone see this before? Or is this expected behaviour?

Clarion 11 build 13630
MS SQL

2

This is expected, or least has always worked that way.
Columns from KEYs, especially primary keys, are often added to the SELECT list of columns.

3

Hmm that is a pity. I gives unnecessary I/O traffic especially when those “extra” fields once fetched seem not to be used in the trace.

4

It’s not a bug, its by design. It is discussed in detail here;

https://capesoft.com/docs/Driverkit/ClarionObjectBasedDrivers.htm#propnokeyproject

You can also see prop:nokeyproject in the help.

PS ; it wont shock you to discover the new drivers offer more flexibility here, but i think you can live with the restrictions of the old driver and prop:nokeyproject

4 Likes
5

OK not a bug, but still a bit weird. I would expect that when using the PROJECT statement it will only select that fields. But I must admit after carefully reading the help it says in a vague manner that other fields can still be retrieved.

But good to know there is a way to prevent this.