onsdag 30 juni 2010

Problems with subquery transformations

MySQL transforms IN predicates into nearly-equivalent EXISTS predicates, and uses a set of mechanisms to catch all special cases. This transformation, outlined below, also applies to NOT IN predicates since a NOT IN b parses to NOT a IN b. Expressions such as

SELECT * FROM table1 WHERE column1 IN (SELECT column2 FROM table2)

get transformed into

SELECT * FROM table1 WHERE EXISTS
( SELECT column2 FROM table2
WHERE column1 = column2 OR column2 IS NULL )

since the latter can be executed more efficiently. For instance an index can be used for accessing only the rows of table2 that will actually participate in the final result. The added WHERE condition is called a guarded condition, or trigger condition, both terms are used. In order to get correct result, the condition column1 = column2 must be deactivated if we are comparing against a NULL value for column1. This guarantees correct results for most cases but there are some additional cases to consider.

If the IN predicate is used directly in the WHERE clause (e.g. not as a sub-expression of IS UNKNOWN,) a NULL value is treated as false. There is a special property top_level_item of an expression node that tells the execution to filter out NULL values from the subquery. But there are problems with it.
  1. It does not propagate through NOT nodes (or any other nodes for that matter.)
  2. It is only applied to the WHERE and HAVING clauses. Since MySQL allows quantified predicates in the SELECT list - this is not in the SQL standard - an extra hack is needed to make it work. The solution is to add - when top_level_item is false - another guarded condition as HAVING clause. Remember that MySQL allows HAVING without GROUP BY - yet again extending the standard. Hence we end up with
SELECT * FROM table1 WHERE EXISTS
( SELECT column2
FROM table2
WHERE column1 = column2 OR column2 IS NULL
HAVING NOT column2 IS NULL )

This will filter out NULL values causing the result of the subquery to be empty.

1 and 2 together caused Bug#51070. For row valued predicands, e.g.

SELECT * FROM t WHERE (a, b) NOT IN (SELECT c, d FROM u)

there may be partial matches for any two rows. A partial match occurs when the corresponding positions in two rows are either equal, or at least one is NULL. The design above handles these cases just fine as long as NULL values come from the outer table. When there is a NULL value in the row from the inner table, however, it gets filtered out by the HAVING clause. Hence the subquery, now rewritten into

SELECT c, d FROM u WHERE c = a OR b = d
HAVING NOT c IS NULL AND NOT d IS NULL

yields an empty result and the value of IN is false, causing NOT IN to yield true.

49 kommentarer:

  1. nice blog good information about oracle fusion hcm trainings
    , i am already your reader now and i might frequently watch out for the new posts, once more hats off to you! thanks a ton over again, regards, oracle fusion hcm on line training among the oracle fusion hcm in Hyderabad. study room schooling in Hyderabad India for more information please check the site
    oracle all modules training in Kuwait

    SvaraRadera
  2. Thanks for sharing such a great information..Its really nice and informative. If you are looking for best Oracle Online Training is one of the leading Online Training institute.
    Oracle fusion financials training

    SvaraRadera
  3. Usually the blog you posted is very useful to us thanks for posting this blog.

    Power BI Training in Dallas

    Power BI Training in Dallas

    SvaraRadera
  4. Thanks for such a great article here. I was searching for something like this for quite a long time and at last I’ve found it on your blog. It was definitely interesting for me to read about their market situation nowadays. Well written article Thank You for Sharing with Us project management courses in chennai | pmp training class in chennai | pmp training fee | project management training certification | project management training in chennai | project management certification online |

    SvaraRadera
  5. Really useful information. Thank you so much for sharing.It will help everyone.Keep Post. RPA training in chennai | RPA training in Chennai with placement

    SvaraRadera
  6. Nice blog..! I really loved reading through this article. Thanks for sharing such a amazing post with us and keep blogging... angular 4 training in chennai | angularjs training in omr | best angularjs training institute in chennai | angularjs training in omr

    SvaraRadera
  7. Excellent blog I visit this blog it's really awesome. The important thing is that in this blog content written clearly and understandable. The content of information is very informative.
    Oracle Fusion HCM Online Training
    Oracle Fusion SCM Online Training
    Oracle Fusion Financials Online Training
    oracle fusion financials classroom training
    Workday HCM Online Training
    Oracle Fusion HCM Classroom Training

    SvaraRadera
  8. A befuddling web diary I visit this blog, it's incredibly grand. Strangely, in this present blog's substance made motivation behind fact and sensible. The substance of information is instructive
    Oracle Fusion Financials Online Training
    Oracle Fusion HCM Online Training
    Oracle Fusion SCM Online Training

    SvaraRadera
  9. Thanks for sharing an informative stuff keep rocking.
    Are you looking for Web developing company ;

    web development company in chennai
    <Best web design company in chennai

    SvaraRadera
  10. Such a nice blog, I really like what you write in this blog, I also have some relevant Information about if you want more information.

    Workday HCM Online Training

    SvaraRadera
  11. Thank you for sharing such a nice and interesting blog and really very helpful article

    Workday Online Training

    SvaraRadera
  12. Very nice post here and thanks for it .I always like and such a super contents of these post.Excellent and very cool idea and great content of different kinds of the valuable information.
    Data Science Online Training | Data Science Online Certification

    SvaraRadera
  13. Your article is very helpful.You can visit my website :showbox app

    SvaraRadera
  14. Extraordinary work in giving the correct substance the sensible explanation. The substance looks certifiable with considerable information. Incredible Work

    WhatsApp Plus Mod Apk

    MCPE

    SvaraRadera
  15. Thanks for sharing information
    "Pressure Vessel Design Course is one of the courses offered by Sanjary Academy in Hyderabad. We have offer professional
    Engineering Course like Piping Design Course,QA / QC Course,document Controller course,pressure Vessel Design Course,
    Welding Inspector Course, Quality Management Course, #Safety officer course."
    Piping Design Course
    Piping Design Course in India­
    Piping Design Course in Hyderabad
    Welding Inspector Course
    Quality Management Course
    Quality Management Course in india
    Safety officer course

    SvaraRadera
  16. Poker online situs terbaik yang kini dapat dimainkan seperti Bandar Poker yang menyediakan beberapa situs lainnya seperti http://62.171.128.49/hondaqq/ , kemudian http://62.171.128.49/gesitqq/, http://62.171.128.49/gelangqq/, dan http://62.171.128.49/seniqq. yang paling akhir yaitu http://62.171.128.49/pokerwalet/. Jangan lupa mendaftar di panenqq salam hoki

    SvaraRadera
  17. thanks for sharing this great article with us. keep posting.
    Vavoo Apk Latest Version 2020
    Watching movies and series on TV is a good way of spending free time. This helps people get rid of anxiety, depression, and tiredness. You feel fresh, relax and mentally alert if you watch drama and movies and other programs on TV. But this is not possible to keep TV everywhere along with you. Vavoo is the application that lets you watch your TV shows on your phone. You can easily search the movies, TV shows, and other programs in the app by using the search bar. Watch your favorite shows online or download them so that you can watch them later.
    visit and check

    SvaraRadera
  18. Thank you for taking the time to provide us with your valuable information. We strive to provide our candidates with excellent care
    http://glimtechnologies.com/inplant-training-coimbatore/
    http://glimtechnologies.com/ieee-projects-coimbatore/
    http://glimtechnologies.com/final-year-ieee-java-projects-coimbatore/
    http://glimtechnologies.com/final-year-ieee-dot-net-projects-coimbatore/
    http://glimtechnologies.com/final-year-ieee-big-data-projects-coimbatore/
    http://glimtechnologies.com/final-year-ieee-python-projects-coimbatore/
    http://glimtechnologies.com/final-year-ieee-hadoop-projects-coimbatore/

    SvaraRadera
  19. Great blog !It is best company for online job support from India
    http://onlinejobsupport.net/job-support/openspan-online-job-support/
    http://onlinejobsupport.net/job-support/uipath-online-job-support/
    http://onlinejobsupport.net/job-support/automation-anywhere-online-job-support/
    http://onlinejobsupport.net/job-support/salesforce-admin-online-job-support/
    http://onlinejobsupport.net/job-support/microsoft-azure-online-job-support/
    http://onlinejobsupport.net/job-support/data-science-online-job-support/
    http://onlinejobsupport.net/job-support/machine-learning-online-job-support/

    SvaraRadera
  20. It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...
    http://rexapparels.com/track-pants-manufacturers-in-tirupur-india/
    http://rexapparels.com/innerwear-manufacturers-in-tirupur-india/
    http://rexapparels.com/buying-office-in-tirupur-india/
    http://rexapparels.com/export-surplus-t-shirts-in-tirupur-india/
    http://rexapparels.com/t-shirt-manufacturer-in-tirupur-india/

    SvaraRadera
  21. This article content is really unique and amazing.This article really helpful and explained very well.So i am really thankful to you for sharing keep it up.. Also check out GBWHATSAPP APK DOWNLOAD

    SvaraRadera
  22. Thanks for sharing kvs shala darpan with us, guys! You are doing a great thing!

    SvaraRadera
  23. SSC Exam Result is a trending now in Bangladesh. SSC Examination was completed. All students are waiting to get their SSC Exam Result 2020. This year SSC Result 2020 will publish on 1st week in May. On the result day, after 12PM all students will get their SSC Exam Result 2020 from Bangladesh Education Board official website. Also Dakhil Result 2020 and SSC Vocational Result 2020 will publish on 6 May 2020

    SvaraRadera
  24. SSC Exam Result is trending now in Bangladesh. All SSC Examinee are waiting to get their SSC Result 2020. This year SSC Exam Result will publish on 1st week in May 2020. Students will collect their Result form Bangladesh Educational Board Official website. Also We will share SSC Result 2020 on our website. Students will collect their Result from our website. Candidates can collect their Dakhil Result 2020 and Vocational Result 2020 from Bangladesh Education Board official website.

    SvaraRadera
  25. SSC Exam Result 2020 in Bangladesh. SSC Result coming soon. All students are waiting to get their SSC Result 2020. This year SSC Result 2020 will publish on 06 May 2020. Students can collect their SSC Result 2020 from Bangladesh Education Board official website.

    SvaraRadera
  26. HSC Examination was completed few days ago. Now HSC Result is the trending topic in Bangladesh. All candidates will check their HSC Result 2020 from Bangladesh Educational official website at educationboardresults.gov.bd.

    SvaraRadera
  27. I would recommend my profile is important to me, I invite you to discuss this topic…

    gbwhatsappfree.com/gbwhatsapp-for-android

    SvaraRadera
  28. Cool stuff you have and you keep overhaul every one of us

    notepad++ plugins manager


    SvaraRadera
  29. The material and aggregation is excellent and telltale as comfortably.

    rice period test

    SvaraRadera