Wanting a few peoples take this.
The query generated by this fairly simple BAQ generates tsql in the show plan like this
select [EntityGLC].[Key1] [EntityGLC_Key1],[GLCntrlAcct].[SegValue1] [GLCntrlAcct_SegValue1],[GLCntrlAcct].[SegValue2] [GLCntrlAcct_SegValue2],[GLCntrlAcct].[SegValue3] [GLCntrlAcct_SegValue3],[GLCntrlAcct].[GLCTAcctNum] [GLCntrlAcct_GLCTAcctNum]
from [Erp].[EntityGLC]
inner join [Erp].[GLCntrlAcct] on (([GLCntrlAcct].[Company] = [EntityGLC].[Company] And [GLCntrlAcct].[GLControlCode] = [EntityGLC].[GLControlCode] And [GLCntrlAcct].[GLControlType] = [EntityGLC].[GLControlType]) and ([GLCntrlAcct].[GLCTAcctNum] = 1 and [GLCntrlAcct].[GLControlType] = N'DIVISION'))
where ( [EntityGLC].[Key1] = @Plant and [EntityGLC].[RelatedToFile] = @Plant ) and ( [EntityGLC].[Company] is null or [EntityGLC].[Company] = N'' or [EntityGLC].[Company] = @CurrentCompany )
Note the @Plant parameters
However the criteria on the BAQ shows clearly show that one is a parameter name Plant and the other one is a constant with a value of Plant.
So either I have been staring to long at the screen or there is something unusual with using the word “Plant”
Interestingly with the both criteria I get no results, with just the first I do get results. A similar query in TSQL works…
Interestingly using double quotes on the criteria value it works.
Another interesting point changing the parameter from Plant to Site and taking off the quotes on the constant also works…
In BAQ’s calculated fields editor, everything listed under ‘BAQ constants’ can be accessed with parameter syntax. Constants.CompanyID == @CompanyID. I don’t see “plant” in that list though. That doesn’t mean you’re not colliding with something hardcoded though, I wouldn’t bet any money that BAQ constants is the limit of this behavior.
It looks like the constant value and the parameter name being the same thing trips it up. I chose a different class, name the parameter that class name even though I used it tied to plant, and it derped out.
I guess I’m weird but that’s a fun bug! As long as you know to expect it anyway. Bugs that reveal a bit of what’s going on behind the scenes are neat.
Extending on the issue:
If you switch the parameter and constant positions it’s still borked.
If you change the AND to OR it’s still borked no matter what the values are, as long as the constant value == the parameter name.
If you add another parameter with a name different from the value in your WHERE constant it’s still borked even if you never use the colliding parameter. Interesting!
If you drop the colliding parameter and feed the same value to both the parameter and constant condition, all is fine.
If you change your WHERE constant to a string in an expression everything seems like it’s fine, although I only spent a minute or two poking at it.
Seems like this might be constrained to the constant option. Maybe it’s being parameterized in code on its way to SQL text, naively using the constant value as a reference?
