The VLDB Journal

, 18:1091 | Cite as

The trichotomy of HAVING queries on a probabilistic database

  • Christopher RéEmail author
  • Dan Suciu
Special Issue Paper


We study the evaluation of positive conjunctive queries with Boolean aggregate tests (similar to HAVING in SQL) on probabilistic databases. More precisely, we study conjunctive queries with predicate aggregates on probabilistic databases where the aggregation function is one of MIN, MAX, EXISTS, COUNT, SUM, AVG, or COUNT(DISTINCT) and the comparison function is one of =, ≠,≥,>,≤, or <. The complexity of evaluating a HAVING query depends on the aggregation function, α, and the comparison function, θ. In this paper, we establish a set of trichotomy results for conjunctive queries with HAVING predicates parametrized by (α, θ). For such queries (without self-joins), one of the following three statements is true: (1) the exact evaluation problem has \({\mathcal P}\) -time data complexity. In this case, we call the query safe. (2) The exact evaluation problem is \({{\sharp{\mathcal P}}}\) -hard, but the approximate evaluation problem has (randomized) \({{\mathcal P}}\) -time data complexity. More precisely, there exists an FPTRAS for the query. In this case, we call the query apx-safe. (3) The exact evaluation problem is \({{\sharp{\mathcal P}}}\) -hard, and the approximate evaluation problem is also hard. We call these queries hazardous. The precise definition of each class depends on the aggregate considered and the comparison function. Thus, we have queries that are (MAX,≥ )-safe, (COUNT,≤ )-apx-safe, (SUM,=)-hazardous, etc. Our trichotomy result is a significant extension of a previous dichotomy result for Boolean conjunctive queries into safe and not safe. For each of the three classes we present novel techniques. For safe queries, we describe an evaluation algorithm that uses random variables over semirings. For apx-safe queries, we describe an FPTRAS that relies on a novel algorithm for generating a random possible world satisfying a given condition. Finally, for hazardous queries we give novel proofs of hardness of approximation. The results for safe queries were previously announced (in Ré, C., Suciu, D. Efficient evaluation of. In: DBPL, pp. 186–200, 2007), but all other results are new.


Probabilistic databases Query evaluation Sampling algorithms Semirings Safe plans 


  1. 1.
    Andritsos, P., Fuxman, A., Miller, R.J.: Clean answers over dirty databases. In: ICDE (2006)Google Scholar
  2. 2.
    Antova, L., Jansen, T., Koch, C., Olteanu, D.: Fast and simple relational processing of uncertain data. In: ICDE, pp. 983–992 (2008)Google Scholar
  3. 3.
    Arenas, M., Bertossi, L., Chomicki, J., He, X., Raghavan, V., Spinrad, J.: Scalar aggregation in inconsistent databases. Theor. Comp. Sci. (2003)Google Scholar
  4. 4.
    Barbara D., Garcia-Molina H., Porter D.: The management of probabilistic data. IEEE Trans. Knowl. Data Eng. 4(5), 487–502 (1992)CrossRefGoogle Scholar
  5. 5.
    Bertossi, L., Chomicki, J., Cortes, A., Gutierrez, C.: Consistent answers from integrated data sources. In: International Conference on Flexible Query Answering Systems (2002)Google Scholar
  6. 6.
    Burdick D., Deshpande P.M., Jayram T.S., Ramakrishnan R., Vaithyanathan S.: Olap over uncertain and imprecise data. VLDB J. 16(1), 123–144 (2007)Google Scholar
  7. 7.
    Burdick, D., Deshpande, P., Jayram, T.S., Ramakrishnan, R., Vaithyanathan, S.: Olap over uncertain and imprecise data. In VLDB, pp. 970–981 (2005)Google Scholar
  8. 8.
    Cafarella, M.J., Ré, C., Suciu, D., Etzioni, O.: Structured querying of web text data: a technical challenge. In: CIDR, pp. 225–234. (2007)
  9. 9.
    Chaudhuri, S., Ganjam, K., Ganti, V., Motwani, R.: Robust and efficient fuzzy match for online data cleaning. In: ACM SIGMOD, San Diego, CA (2003)Google Scholar
  10. 10.
    Cheng, R., Kalashnikov, D., Prabhakar, S.: Evaluating probabilistic queries over imprecise data. In: Proceedings of SIGMOD03 (2003)Google Scholar
  11. 11.
    Cohen, S., Kimelfeld, B., Sagiv, Y.: Incorporating constraints in probabilistic xml. In: PODS, pp. 109–118 (2008)Google Scholar
  12. 12.
    Dalvi, N., Suciu, D.: Efficient query evaluation on probabilistic databases. In: VLDB, Toronto, Canada (2004)Google Scholar
  13. 13.
    Dalvi, N., Suciu, D.: Management of probabilisitic data: foundations and challenges. In: PODS, pp. 1–12 (2007)Google Scholar
  14. 14.
    Dalvi, N.N., Suciu, D.: The dichotomy of conjunctive queries on probabilistic structures. In: PODS, pp. 293–302 (2007)Google Scholar
  15. 15.
    Deshpande, A., Guestrin, C., Madden, S., Hellerstein, J., Hong, W.: Model-driven data acquisition in sensor networks (2004)Google Scholar
  16. 16.
    Dyer, M.E., Goldberg, L.A., Greenhill, C.S., Jerrum, M.: On the relative complexity of approximate counting problems. In: APPROX, pp. 108–119 (2000)Google Scholar
  17. 17.
    Dyer, M.E., Goldberg, L.A., Jerrum, M.: An approximation trichotomy for boolean #csp. CoRR, abs/0710.4272 (2007)Google Scholar
  18. 18.
    Fagin, R., Halpern, J.Y.: Reasoning about knowledge and probability. In: Vardi, M.Y. (ed.) Proceedings of the Second Conference on Theoretical Aspects of Reasoning about Knowledge, pp. 277–293. Morgan Kaufmann, San Francisco (1988)Google Scholar
  19. 19.
    Fuxman, A., Miller, R.J.: First-order query rewriting for inconsistent databases. In: ICDT, pp. 337–351 (2005)Google Scholar
  20. 20.
    Gradel, E., Gurevich, Yu., Hirch, C.: The complexity of query reliability. In: Symposium on Principles of Database Systems, pp. 227–234 (1998)Google Scholar
  21. 21.
    Green, T., Karvounarakis, G., Tannen, V.: Provenance semirings. In: PODS (2007)Google Scholar
  22. 22.
    Green, T.J., Tannen, V.: Models for incomplete and probabilistic information. IEEE Data Engineering Bulletin, vol 29 (2006)Google Scholar
  23. 23.
    Gupta, R., Sarawagi, S.: Curating probabilistic databases from information extraction models. In; Proceedings of the 32nd International Conference on Very Large Databases (VLDB) (2006)Google Scholar
  24. 24.
    Hernandez, M.A., Stolfo, S.J.: The merge/purge problem for large databases. In: SIGMOD Conference, pp. 127–138 (1995)Google Scholar
  25. 25.
    Jampani, R., Xu, F., Wu, M., Perez, L.L., Jermaine, C.M., Haas, P.J.: MCDB: a monte carlo approach to managing uncertain data. In: SIGMOD Conference, pp. 687–700 (2008)Google Scholar
  26. 26.
    Jayram, T.S., Kale, S., Vee, E.: Efficient aggregation algorithms for probabilistic data. In: SODA (2007)Google Scholar
  27. 27.
    Jayram, T.S., Krishnamurthy, R., Raghavan, S., Vaithyanathan, S., Zhu, H.: Avatar information extraction system. IEEE Data Engineering Bulletin, vol. 29(1), (2006)Google Scholar
  28. 28.
    Kanagal, B., Deshpande, A.: Online filtering, smoothing and probabilistic modeling of streaming data. In: ICDE, pp. 1160–1169, (2008)Google Scholar
  29. 29.
    Karp, R.M., Luby, M.: Monte–carlo algorithms for enumeration and reliability problems. In: FOCS, pp. 56–64, (1983)Google Scholar
  30. 30.
    Koch, C.: Approximating predicates and expressive queries on probabilistic databases. In: PODS, pp. 99–108 (2008)Google Scholar
  31. 31.
    Koch, C.: A compositional query algebra for second-order logic and uncertain databases. In: Proceedings of ICDT (2009)Google Scholar
  32. 32.
    Koch, C., Olteanu, D.: Conditioning probabilistic databases. In: VLDB (2008)Google Scholar
  33. 33.
    Lakshmanan, L., Leone, N., Ross, R., Subrahmanian, V.S.: Probview: a flexible probabilistic database system. ACM Trans. Database Syst. 22(3), (1997)Google Scholar
  34. 34.
    Lang S.: Algebra. Springer, Heidelberg (2002)zbMATHGoogle Scholar
  35. 35.
    Mansuri, I., Sarawagi, S.: A system for integrating unstructured data into relational databases. In: Proceedings of ICDE 2006 (2006)Google Scholar
  36. 36.
    Motwani R., Raghavan P.: Randomized Algorithms. Cambridge University Press, Cambridge (1997)Google Scholar
  37. 37.
    Murthy, R., Ikeda, R., Widom, J.: Making aggregation work in uncertain and probabilistic databases. Technical Report 2007-7, Stanford InfoLab, June 2007Google Scholar
  38. 38.
    Olteanu, D., Huang, J., Koch, C.: SPROUT: Lazy vs. eager query plans for tuple-independent probabilistic databases. In: Proc. of ICDE 2009 (2009)Google Scholar
  39. 39.
    Parag, A., Benjelloun, O., Sarma, A.D., Hayworth, C., Nabar, S., Sugihara, T., Widom, J.: Trio: a system for data uncertainty and lineage. In: VLDB (2006)Google Scholar
  40. 40.
    Ré C., Dalvi N., Suciu D.: Query evaluation on probabilistic databases. IEEE Data Eng. Bull. 29(1), 25–31 (2006)Google Scholar
  41. 41.
    Ré, C., Dalvi, N., Suciu, D.: Efficient top-k query evaluation on probabilistic data. In: Proceedings of ICDE (2007)Google Scholar
  42. 42.
    Ré, C., Suciu, D.: Materialized views in probabilsitic databases for information exchange and query optimization. In: VLDB (2007)Google Scholar
  43. 43.
    Ré, C., Suciu, D. Efficient evaluation of. In: DBPL, pp. 186–200 (2007)Google Scholar
  44. 44.
    Ross R., Subrahmanian V.S., Grant J.: Aggregate operators in probabilistic databases. J. ACM 52(1), 54–101 (2005)CrossRefMathSciNetGoogle Scholar
  45. 45.
    Sarma, A.D., Benjelloun, O., Halevy, A.Y., Widom, J.: Working models for uncertain data. In: Liu, L., Reuter, A., Whang, K.-Y., Zhang, J. (eds.) ICDE, p. 7. IEEE Computer Society, Los Alamitos (2006)Google Scholar
  46. 46.
    Sen, P., Deshpande, A.: Representing and querying correlated tuples in probabilistic databases. In: Proceedings of ICDE (2007)Google Scholar
  47. 47.
    Sinclair A., Jerrum M.: Approximate counting, uniform generation and rapidly mixing markov chains. Inf. Comput. 82(1), 93–133 (1989)zbMATHCrossRefMathSciNetGoogle Scholar
  48. 48.
    Soliman, M., Ilyas, I.F., Chang, K.C.-C.: Top-k query processing in uncertain databases. In: Proceedings of ICDE (2007)Google Scholar
  49. 49.
    Valiant L.G.: The complexity of enumeration and reliability problems. SIAM J. Comput. 8(3), 410–421 (1979)zbMATHCrossRefMathSciNetGoogle Scholar
  50. 50.
    Vardi, M.Y.: The complexity of relational query languages. In: Proceedings of 14th ACM SIGACT Symposium on the Theory of Computing, pp. 137–146, San Francisco, California (1982)Google Scholar
  51. 51.
    Widom, J.: Trio: a system for integrated management of data, accuracy, and lineage. In: CIDR, pp 262–276 (2005)Google Scholar
  52. 52.
    Winkler, W.E.: Improved decision rules in the fellegi-sunter model of record linkage. Technical report, Statistical Research Division, U.S. Census Bureau, Washington, DC (1993)Google Scholar
  53. 53.
    Winkler, W.E.: The state of record linkage and current research problems. Technical report, Statistical Research Division, US Bureau of the Census (1999)Google Scholar

Copyright information

© Springer-Verlag 2009

Authors and Affiliations

  1. 1.Department of Computer Science and EngineeringUniversity of WashingtonSeattleUSA

Personalised recommendations