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.

93 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. 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
  6. 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
  7. 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
  8. Your article is very helpful.You can visit my website :showbox app

    SvaraRadera
  9. 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
  10. 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
  11. 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
  12. 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
  13. 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
  14. 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
  15. Thanks for sharing kvs shala darpan with us, guys! You are doing a great thing!

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

    gbwhatsappfree.com/gbwhatsapp-for-android

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

    rice period test

    SvaraRadera
  18. movies123. وف تشعر بالاثارة عندما تسجل الهدف

    SvaraRadera
  19. movies123 family and country Saudi Arabia. Her passport was

    SvaraRadera
  20. This is also a very good post which I really enjoyed reading. It is not every day that I have the possibility to see something like this..


    download gb whatsapp app

    SvaraRadera
  21. Providing the right information how to use databse queruies. Thank for nice blog. In case someone want to know about IT training courses in Rohtak.
    https://merientinfotech.com/course/digital-marketing
    https://merientinfotech.com/course/php/
    https://merientinfotech.com/course/html/
    https://merientinfotech.com/course/wordpress/
    https://merientinfotech.com/course/seo/
    https://merientinfotech.com/course/c-language/
    https://merientinfotech.com/course/cpp-langauge/
    https://merientinfotech.com/course/basic-computer/.

    SvaraRadera
  22. I have been using this app xTorrent Apk : and downloaded and gambling it frequently.

    SvaraRadera
  23. great java tips At SynergisticIT we offer the best java training california

    SvaraRadera
  24. Hi, I have read your blog and this really good. I am gonna bookmark it. Looking forward for the next article. Fashion Designer Boutique in Mumbai

    SvaraRadera
  25. Nice & Informative Blog !
    you may encounter various issues in QuickBooks that can create an unwanted interruption in your work. To alter such problems, call us at QuickBooks Customer Service 1-(855) 729-7482 and get immediate technical services for QuickBooks in less time.

    SvaraRadera
  26. Den här kommentaren har tagits bort av skribenten.

    SvaraRadera
  27. Thanks for the blog loaded with so many information. Stopping by your blog helped me to get what I was looking for. 먹튀폴리스

    SvaraRadera
  28. nice article your article help me a lot in my work thanks.
    Merient Infotech is a best IT Training Institute

    Merient Infotech is a popular IT training institute in Rohtak.
    we are providing basic to Advance level classes under our Experienced Teachers.
    And we are also offer a free trail class.
    Students can experience the easiest technics of teaching by our teacher.

    Services we are providing:-
    CSS, JAVA, Web designing, Digital Marketing course, PHP, C++, Web development and Python etc.

    Merient Infotech
    Digital Marketing
    Web designing
    Web development
    C programming
    C++ Program
    Python programming

    SvaraRadera
  29. Great work ! It's help me a lot in my work.
    Merient Infotech provides best IT training in Rohtak. We specialize in preparing the Professionals in the field of Digital Marketing, Web Development, Web Designing, MS-Office, Basic to Advance Programming Language like PHP, Python, JavaScript, HTML, CSS and jQuery.
    Merient Infotech
    Digital Marketing
    PHP
    Python
    Web Development
    Web Designing

    SvaraRadera
  30. Very good points you wrote here..Great stuff…I think you’ve made some truly interesting points.
    먹튀검증

    SvaraRadera
  31. Catmouse Apk CATMOUSE APK is the third-party online streaming application for the latest movies. It is a wonderful application that is providing an enormous number of online streaming videos for the audience.

    SvaraRadera
  32. interesting blog. liked it alot and this helped me alot. but Forest mod apk have changed my life. it have build my self disciplined.

    SvaraRadera
  33. Dynamic CRM can be used for monitoring the client activity including the product inquiry and order placement. lwc interview questions

    SvaraRadera
  34. i have enjoy after read article
    thanks for sharing excellent information if you are looking any type QuickBooks Solution contact at team at
    quickbooks customer service

    SvaraRadera
  35. Great Article you can useful provide information about quickbooks solution at
    quickbooks phone number

    SvaraRadera
  36. After Read Your Blog I m very impress. keep sharing it and you have also get more information about QuickBooks issue solution at quickbooks phone number

    SvaraRadera
  37. I'm glad I could help by giving my two cents on this post. It's very insightful and helpful for those who are looking into making changes in their lives, as well! Keep up the good work-I'll be waiting (and checking) with interest every time you release another new blog article or video update :)
    octopath traveler crack pc

    SvaraRadera
  38. Data Science Online Courses
    Inventateq now offers data science online training so that you can learn data science from your home. Taking a Data Science online course will provide you with the most effective method of learning and techniques for analyzing actionable data. Learning Data Science online is a good way to improve your experience with online learning. Get certified with India's top-ranked faculty while taking the fastest-growing Data Science course online.
    inventateq.com/online-data-science-training-courses.php

    SvaraRadera

  39. Great Post with valuable information. I am glad that I have visited this site. Share more updates.
    Automation
    openspan

    SvaraRadera
  40. SAP SD Training in Chennai
    SAP FICO Training in Chennai
    SAP ARIBA Training in Chennai
    SAP ABAP Training in Chennai
    SAP HR Training in Chennai
    SAP HANA Training in Chennai
    SAP SCM Training in Chennai
    SAP BO Training in Chennai
    SAP PP Training in Chennai

    SvaraRadera
  41. excellent blog thanks for information
    Deepam Oil

    SvaraRadera
  42. Nice post, Thanks for the useful content. It’s hard to find nowadays to know about the basics but you did it so much well. I would love to see more about it. Please share with my blog Whatsapp Plus

    SvaraRadera
  43. Hello your post is very helpful, thanks for wonderful information sharing. When you go about Find information and tips on Coffee Culture Around the World.
    Hello your post is very helpful, thanks for wonderful information sharing. When you go about Find information and tips on left breast
    Hello your post is very helpful, thanks for wonderful information sharing. When you go about Find information and tips on tongue scraper
    Hello your post is very helpful, thanks for wonderful information sharing. When you go about Find information and tips on blogger outreach
    Hello your post is very helpful, thanks for wonderful information sharing. When you go about Find information and tips on thick toenail
    Hello your post is very helpful, thanks for wonderful information sharing. When you go about Find information and tips on High da guest post
    Hello your post is very helpful, thanks for wonderful information sharing. When you go about Find information and tips on Women's Unstitched Summer Lawn Collection
    Hello your post is very helpful, thanks for wonderful information sharing. When you go about Find information and tips on Kamar Dard Ka ilaj

    SvaraRadera
  44. GBWhastapp is full of features. I'm sure you won't choose others if you install it now. Check out the interesting features of gbwhatsapp here.

    SvaraRadera
  45. The fashion of blouse back neck designs catalogs changes every year. Let's take a look at some of the most beautiful saree blouse neck designs

    SvaraRadera
  46. This is the best ever whatsapp and i liked this app for more download Live NetTv and this is not only a single app you can download RedBox Tv there is you can also Chinese Year 2022 this is the greate MSNBC Live News for you

    SvaraRadera
  47. The ilike PDF Watermark Crack Remover is a reliable and easy-to-use PDF watermark removal software that enables you to quickly remove watermarks from PDF documents. ilike PDF Watermark Remover is very easy to remove watermarks from PDF files. PDF Watermark Remover software offers you the fast, safe and efficient solution.

    SvaraRadera
  48. The passmark performance test 10 1 build 1005 makes it easy to test your computer and compare its performance against benchmark statistics and other users’ PCs. Benchmarking your PC is important in tuning its performance and critical for overclocking. PassMark PerformanceTest 10 Crack runs a battery of sophisticated benchmarks and returns some expert-level data.

    SvaraRadera
  49. to know more about automated service desk and help desk with rezolve.ai, click here: https://bit.ly/3ttcaGK

    SvaraRadera
  50. I am preparing for the most prestigious exam https://newminimilitia.com/

    SvaraRadera
  51. Thank you for such an amazing discussion. huntingsky.com

    SvaraRadera
  52. Benchmarking your PC is important in tuning its performance and critical for overclocking. PassMark PerformanceTest 10 Crack runs a battery of sophisticated benchmarks and returns some expert-level data. damon ps2 pro apk download

    SvaraRadera
  53. To know about automated AI help desk and service desk with rezolve.ai, vsit their website. To know more click here:
    microsoft teams employee service desk

    SvaraRadera
  54. To know about AI automated help desk and service desk at MS teams, check our website and schedule your demo. You can also read blogs how automated help desk and service desk is changing everything in the workplace. To know more click here:
    conversational ai employee service desk

    SvaraRadera
  55. IMBMS is amazing Glutathione store in India, which are the main reason behind its popularity among customers. But before buying this product, you must make sure whether the ingredients present in the vita glow night cream are suitable for your skin or not. There are some ingredients, which may cause allergy to you and your skin and if you have sensitive skin then it may result in complications. So it is recommended to take the advice of a doctor before using any such cream. If you are not able to find a good doctor who can guide you regarding your skin then researching on the internet would be beneficial for you to find a good night cream.
    imbms

    SvaraRadera
  56. This WhatsApp Apk is the substitute for the original WhatsApp, having some additional features than jtwhatsapp that the original one doesn’t have. A group of developers added some extra features like changing themes, download WhatsApp status, hiding double tick, and many more. This version has more tight privacy features, and you don’t need to pay for it. You don’t need to uninstall the original WhatsApp when going to use GB WhatsApp Apk GB Apk Whatsapp. Both can be used in parallel, which means you can run two WhatsApp accounts on a single device.

    SvaraRadera

  57. It’s clearly an exceptional and very useful fact. Thanks for sharing this helpful data with us. CEA Aviation is one of the Best DGCA Ground Classes in India. It gives admission to all of the aspirants seeking to start with Ground classes. You can enrol for Best Ground training for DGCA checks with us. Once you're clear with DGCA exam Counsellor Desk will assist you in choosing the best flight academy overseas.

    SvaraRadera
  58. I like this blog and I also like to share a game blog which is related to mini militia version visit here mini-militia.com to know more

    SvaraRadera
  59. Are you looking for a way to watch and download your favorite movies at the same time? Look no further than Downloadhub, where you can get access to all the latest releases from genres like Family, Action, Adventure and more in one convenient place! Dive into this article to know more about the unique perks of downloadhub movies download hindi.

    SvaraRadera
  60. This post is very Amazing. This information is very helpful.
    katmovie

    SvaraRadera
  61. We make sure that our partners are able to delight their customers and deliver as per the commitments. This residential solar installation achieved by fast turnaround time, least revisions, a database of AJH & utility requirements for most accurate permit plan set creation and vast network of PE stamp partners. We have in-depth knowledge of handling Residential Permit Planset, commercial, shops/ independent buildings & carports. Heaven is your go-to solar design partner for sales proposals and PV permit designs !

    SvaraRadera
  62. We at Mytanfarma help you to realize three ultimate goals :
    PROFIT to customer - Managed farmland in Bangalore offers the potential for higher rates of return due to the scope for appreciation in the value of land, tax-free agricultural income, and value enhancement of the land through forestry and plantation crops. Horticultural crops can provide seasonal income, while timber can fetch handsome returns as a long-term benefit.

    SvaraRadera
  63. Struggling with fraction addition? Our Adding Fractions Calculator simplifies the process, making math discussions a breeze!. Master fraction arithmetic effortlessly with our Adding Fractions Calculator. Click hereto get the tool.

    SvaraRadera