Re: SQL Where In

On 26-Sep-2016 12:51 -0500, John Yeung wrote:

On 26-Sep-2016 11:26 -0500, CRPence wrote:

On Wed, 31-Aug-2016 at 14:12, (WalzCraft) Jerry Forss wrote:

[…] Some of the values will be MLMD, MLDM and MLD.

So scanning for a value of MLD may give a false positive.

With the very simple example shown, the issue of a false-positive
is easily overcome by coding the selection to the actual size; i.e.
as 4-byte elements, a 3-byte value would be searched as the
blank-padded value. […]

Your caveat of "With the very simple example shown" is a pretty
significant one. If the value you are searching for happens to not
need any padding, then you run into the same issue with the
searched-for value possibly straddling two elements. Someone already
gave the much-easier-to-read example of an array whose element size
is 3 characters and whose actual contents are 'ABC', 'DEF', and
'GHI'; with a search for 'CDE' (properly padded with zero blanks)
resulting in a false positive. […]

Correct. I cited the specific example given by Jerry; to show how that specific issue could be overcome, with that specific\contrived example data, merely to address the issue with a shorter-length value. Perhaps just /flogging a dead horse/, but I offer:

But that is why I also had prefaced that comment with a suggestion that "A set of sufficiently dissimilar fixed-width elements could still be processed without problems; for which an exhaustive test could be performed against a given set."; i.e. very poorly expressed, but meaning to allude that an effective /proof/ could be made for a given set [by testing every possible combination], that there is zero potential for false-positives whilst searching for any one of the values within the list.

The /very simple example/ was easily enough inferred to have no potential issues for false positives [IMO way too simple to justify showing an exhaustive test as proof], *except* when searching for the short\trimmed-value. And that false-positive was shown to be a potential, only when searched-for [improperly] as the shorter-value instead of searched-for as the fully-padded-to-element-size-value. Thus I was showing how the presumed\expressed difficulty with that specific failing example could be overcome; and more generally, avoid a similar problem more generally by use of proper padding.