Skip to main content

Differing results in subqueries with WHERE

  • April 5, 2018
  • 2 replies
  • 12 views
Bryan_H
Forum|alt.badge.img+2

We are wrapping a POC with a new logo and their DB engineer found the following behavior. They are trying to use a subquery in WHERE clause with a limit to restrict the size of the result set. When the subquery is run standalone and the results are copied into the WHERE clause, it returns rows. When the subquery is copied into the WHERE clause, no results are returned. Some testing reveals that the subquery returns different results depending whether it runs standalone or as a subquery and we'd like to understand why. It would be great if someone suggested a workaround such that the scenarios return the same results as the customer expects, or explain why differing result sets are returned. Here is the use case:

Funny thing is when I tried
(
select uniquePn from intellidata.parameterValues WHERE parameterValueId IN (101, 15)
INTERSECT
select uniquePn from intellidata.parameterValues WHERE parameterValueId = 19
INTERSECT
select uniquePn from intellidata.parameterValues WHERE parameterValueId = 41
) limit 3

It returns

uniquePn

T543V107K006ATS015WAFL
T543D107M016ATS035WAFL
T520D227M004ANE006

SELECT DISTINCT up.uniquePn, up.mfg, up.leafDefId, up.series, up.superAlias, up.obsoleteDate, '' alias, 0 aliasNumber, pv.parameterValueId
FROM intellidata.uniqueParts up
LEFT OUTER JOIN intellidata.parameterValues pv on up.uniquePn = pv.uniquePn
WHERE up.uniquePn IN ('T543V107K006ATS015WAFL', 'T543D107M016ATS035WAFL', 'T520D227M004ANE006')
AND up.mfg IN ( 'KEMET')
AND up.leafDefId IN ( 354, 356, 357, 362, 363, 364, 447)

And above query returns some data. But when I tried

SELECT DISTINCT up.uniquePn, up.mfg, up.leafDefId, up.series, up.superAlias, up.obsoleteDate, '' alias, 0 aliasNumber, pv.parameterValueId
FROM intellidata.uniqueParts up
LEFT OUTER JOIN intellidata.parameterValues pv on up.uniquePn = pv.uniquePn
WHERE up.uniquePn IN ((
select uniquePn from intellidata.parameterValues WHERE parameterValueId IN (101, 15)
INTERSECT
select uniquePn from intellidata.parameterValues WHERE parameterValueId = 19
INTERSECT
select uniquePn from intellidata.parameterValues WHERE parameterValueId = 41
) limit 3)
AND up.mfg IN ( 'KEMET')
AND up.leafDefId IN ( 354, 356, 357, 362, 363, 364, 447)

It returns no results

    2 replies

    Bryan_H
    Forum|alt.badge.img+2
    • Bryan_H
    • Author
    • Participating Frequently
    • April 5, 2018

    OK, after considering these queries and reading the execution plans, it looks like the first query can return results in natural order using a projection ordered by that WHERE clause while the subquery orders the results to match the join condition. This optimizes the join but returns a different 3 results in the limit. As an aside, I tried a few other limits and I am surprised that the limit 3 worked. It appears to be random luck since only a tiny fraction of records match the where clause in the outer query.

    Jim_Knicely
    Forum|alt.badge.img+2
    • Jim_Knicely
    • Participating Frequently
    • April 5, 2018

    If you want to guarantee order add an ORDER BY clause:

    (
select uniquePn from intellidata.parameterValues WHERE parameterValueId IN (101, 15) 
INTERSECT 
select uniquePn from intellidata.parameterValues WHERE parameterValueId = 19 
INTERSECT 
select uniquePn from intellidata.parameterValues WHERE parameterValueId = 41 
) order by 1 limit 3;
    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement