BUG - Query Returns Different # Columns

  • Thread starter Thread starter George Sexton
  • Start date Start date
G

George Sexton

Title: Queries Against View Can Return Different # Columns

Steps to Reproduce:

Using the MDB file from:

http://www.mhsoftware.com/bin/calendar.mdb

Execute the query vObjectSecurity, and then execute the query below:

select * from vObjectSecurity
where token='tok_ManageSecurity' AND
entity_type in (
select entity_type from candidatetokens where instance=1 and token='tok_Edit'
) and
not exists (
SELECT * FROM VObjectSecurity os2
where token='tok_Edit' AND
os2.secured_obj_id=VObjectSecurity.secured_obj_id AND
os2.secured_obj_type=VObjectSecurity.secured_obj_type AND
os2.entity_type=VObjectSecurity.entity_type AND
os2.entity_id=VObjectSecurity.entity_id
)

Observed Behavior:

In the simple query, the 8 defined columns of the view are returned. In
the complex query example, only 7 columns are returned. The entity_type
column is not returned.

Expected Behavior:

The number of returned columns should be invariant, and based on the
definition of the view and not be impacted by the where clause of a query.

Additional Information:

Access Version: 11.6355.6360 (Office 2003 SP1)
Windows XP SP2 w/ All Current Patches
 
Title: Queries Against View Can Return Different # Columns

Steps to Reproduce:

Using the MDB file from:
Click to expand...

Hmm... I looked at this some more. It appears the real issue is that the
query is invalid, and the query parser isn't detecting that.

In the query below, the field entity_type doesn't actually exist in
CandidateTokens and the sub-query

select entity_type from candidatetokens where instance=1 and
token='tok_Edit'

Before some wit tries to say it should be valid, the use of token in the
sub-query proves that the parser is treating candidatetokens as the sole
table source in the query. If it was not, the token= portion should
generate an ambiguous reference error.

select * from vObjectSecurity
where token='tok_ManageSecurity' AND
entity_type in (
select entity_type from candidatetokens where instance=1 and
token='tok_Edit' ) and
not exists (
SELECT * FROM VObjectSecurity os2
where token='tok_Edit' AND
os2.secured_obj_id=VObjectSecurity.secured_obj_id AND
os2.secured_obj_type=VObjectSecurity.secured_obj_type AND
os2.entity_type=VObjectSecurity.entity_type AND
os2.entity_id=VObjectSecurity.entity_id
)

The corrected version of the query should actually be:

select * from vObjectSecurity
where token='tok_ManageSecurity' AND
secured_obj_type in (
select object_type_id from candidatetokens where instance=1 and token='tok_Edit'
) and
not exists (
SELECT * FROM VObjectSecurity os2
where token='tok_Edit' AND
os2.secured_obj_id=VObjectSecurity.secured_obj_id AND
os2.secured_obj_type=VObjectSecurity.secured_obj_type AND
os2.entity_type=VObjectSecurity.entity_type AND
os2.entity_id=VObjectSecurity.entity_id
)
 
Back
Top