Merken

Nulls Make Things Easier?

Zitierlink des Filmsegments
Embed Code

Automatisierte Medienanalyse

Beta
Erkannte Entitäten
Sprachtranskript
thank you very much for coming my name is Bruce marginal unwanted posters core team members I work for enterprise debate many of you who have been here before already know many things but I have the same anyway for those who were not here I this presentation along with about 30 others is that that website right here and in fact this website enabled on my website about an hour ago so if you wanna follow along you work very well able to download the PDF and follow along as I speak unfortunately there will be a recording of this so I'm excited about that I once the recording comes up hopefully in e-mail somebody tell me about it and then how we can I can link to it from my web page you'll see some not only do I have 30 or 40 presentations on there but also they're all many of them have recordings next the so you little tabernae can open it up you can see the recordings where spoken word giving each talk that where repeat stuff which is not that familiar with viewing of this presentation is fairly unusual but what this presentation actually is is inspired by a all blog a series of blog posts that I put out about 3 months ago relating to the handling of moles have any of you read any of those blog posts great OK so for those of you who have read the blog post there's going to be kind of some additional analysis of some of the issues that I think some of its cultural be interesting but but but I will admit that many much of the content is directly from the blog post what's really interesting about the about doing it the way I did it was that it was a series of of 11 blog posts that's been probably 3 weeks and what was really neat was I got feedback as I was doing it because of course I use discussed so people can come in weblogs you might have noticed the keynote from discussed this morning but that's not why I chose it actually which is the solution of and and effectively all what was neat was I was able to kind of in from the the wall and to the content as i got comments from the people who were basically commenting on blogs so this is sort of of a combination had anyone here actually comment on the blog sounds that made up this series because then you would be sort of an author of a new I'm somebody Piaget so let's talk about this particular blog post this to presentation but the reason I was inspired to do it is because having somebody who's on the posters mailing list all the time there is a continual kind of difficulty in working with Knowles and in fact that inspired you know the title which is a question you know Knowles make things easier you know basically this is supposed to help me like you you try to help me with this but a lot of people very frustrated about the way no it's a if the fiery confusing I will say up front is confusing and as we get to the end I think will start to see sort of a pattern of what's emerging but the bottom line is the pattern that emerged is basically a I can get it but there is no overall arching single system that the the you can up sort of place upon all the States here's the 1 factor need to remember about moles and everything else to form the place doesn't work that way all I'm even explain why it doesn't work that way but let's get started and kind to get to get going up any questions before we get started again i do take a questions from the audience of those you have you were not in my tutorial so among I'm itself but here don't what's all that we have and
all of that in English but no means nothing but it basically just means where there is sort of a confusion how to represent that right as a whole is it is it nothing is blank slide right from the start of how you describe nothing with something because you have to use something that going right of N and in English you know pick people don't get so confused OK all this could concept moles in various procedural languages as tools not procedural language it's a declarative language but in procedural languages you do have the the concept of particularly in based languages job being 1 of the better known ones of and square C + + and another C drive languages you have a constant the null when you're assigning a value to a point that doesn't lead to anything and that's pretty easy to understand you got a planar it's not a database of planar and you pointing to something that doesn't exist and that's pretty easy to describe pretty gold by random lots little arrows next on the arrow doesn't go anywhere right that's pretty easy to get where it becomes confusing
frankly is is indeed data because of like a planar where you know that we know the value 2 point from every location of with the nothing OK in did you don't you are dealing with point you're dealing with the container you're dealing with the container that normally holds all value right you have winners and SQL so you have a container it normally contains all value and now we have the challenge of representing no value or nothing OK and that's probably the 1st thing that that kind is is is the Bender again when you're dealing with pointers it's kind of easy to say it points something was nothing that's pretty when you have a container and you have to represent that containers containing nothing in a container how do you do that well and in fact we do that we do that with notes and the way it was restored all stores all those internally in fact is very similar to the way you'd expect but we don't actually store like for example integer and the integer field is not images normally 4 bytes imposed respirator 8 bytes and you will begin with the values and all we don't store 4 bytes with 40 bytes we have a separate area in the world which identifies which is the bits which are the road sort columns and all which the field is not so if you have 3 integer columns in the table and you looking a specific role in the middle 1 is not all we store the value for field 1 column 1 in the row we store the value for column 3 in row and then we have a bit map over here which is by the way slot number 2 would assert that they are high in that bit indicates that that 2nd field that 2nd column for that role does have a value there so even in post-stressed idea of representing nothing internally we have to create a separate bit-mapped to handle this informants reasons for that of course if you have a role that's its with this it's mostly Knowles which does happen we actually talked about this a little bit on Tuesday when we have the Torah effectively you know looking at an bit there with a thousand bits you not looking a thousand people were effect of what while we need this thing why we need this thing in SQL that we call multi-well effectively as much as people bellyache about it it's hard if you don't have to have so as much confusion as I'm going to show you in the future slides we look at your saying why did they do that why is you'd have had why might have to learn about moles have you been sort of surprised by no behavior in this gap OK and a lot hands there OK so the question is why we surprised why can't we make not products right of the bottom line is we need to represent this don't have a value that nothing in the data what because if you don't have it it becomes a problem to represented data and usually if you don't have a data value you can put like single quotes a zero-length string and that's enough that's like I a have a value here it's not the same as in all but I if we could probably get away with it and not and never been invented knows in the in the 1st place or take maybe I'm not sure we could have but but if we everything was a strain we might be got away in a school that notes but we're becomes a really problem is all other fields right everything string of numerics but what you put in a field if you don't you know where you put the field you don't wanna value in like I don't know I'm sure many of you work with databases that didn't have Knowles and you start to get into the special value minus 100 my understanding on 1 minus 1 0 is a different special value in every field and then we have to to encode encode the specialize in your application and you have to remember that the special life field is this as a special value for this year was that and obviously that doesn't work well OK but not only is it a problem with your application but the database that know the special value it doesn't know that a minus 19 and every year my signed under review see because available mothers underlying right of but there is actually some behavioral changes that we need to represent in SQL when we're specifying something we don't know and again you can see that propagating the futurist updates the same problem but if you don't know the data you don't want be field if you don't have no where you put their you know 1900 January 1st 1970 you know that all these crazy things that you have to do if you don't have Knowles and that's why we have to bite the bullet that's what we had to get the males OK so and where and when do for the rest of the presentation is busy explain how animals getting your database how they behave in various situations they give you an understanding so you don't get surprised us so you can cut a walk away and say you know I wish we have moles we do need nulls here's how they behave in these situations I have 1 that is an application of right is a good question can forget any questions anybody has about sort groundwork certain sort of the concepts that I cover right so that so let's look at let's look at why we would use nulls like abstractly what possible purposes but I have 4 and probably the most popular 1 is the unknown values so you're inserting a well you don't have information about all the field you want but you wanna get part of the data and this is very common in data entry you know you you for another person's 1st and last name you don't know the birthday and you come back later Philidor well do we don't want to put all 101 1900 in there because maybe 130 years old right or or some practical value that we don't have to conscript who were system looking for the logical place to put it on the logical thing to use for non values in no the 2nd 1 is inapplicable values these values hidden or unknown what effectively but don't apply to this well it's not like I don't know the value it's like I'm never going to fill that value is because the value doesn't exist in a machine example next slide because the first 1 is empty placeholders in these are cases where you're doing some SQL U S 4 0 World part of role and the bad just supply and we want to put the k minus 1 0 1909 river and all that and I will argue that some have said that the inner and this is kind of a global work but some said that we should have vision 3 types of null SQL if there is anybody heard that before haven't yet but so this is a very interesting idea and and and the reason it gets a little wonky is because the behavior of each you might expect for a value that's unknown might be different than a value you expect in SQL to were to the go for what a logical way to handle it would be different for when the football that guy and know I'm getting 1 way had the space for some people are OK with that you kind of see that when you're overloading mobile purposes on a single value then all often by definition we can generate a a predictable behavior freeze for this that that plane on which a lot of very unpredictable cases in the future for so here
is an example I think takes it home for me I don't know why where her this example for this example I've always used but ever seen words came from that I think about acadia remember OK with the idea that you've got an employee table and you got a sparse field OK I don't know why attractors fascinating maybe for invitations to the Christmas but I don't want OK you had a column of the sisterhood the the spells or is that I at on the light but let's suppose you have a spouse feel that CELP P that really are my previous as a tutorial finding that very funny and you should because there is no hope going this the whole little and so nearly brushing by it is enough to send it wanting to have so I think that employee not sparse so here's the 3 examples of how we might use in all in this particular field the first one is we don't know the spouse's name you know the employers just tired we didn't ask him the name can fill it out on the former who knows the could read it would read the form right we couldn't read the name on the format a great example folded into the their stuff we can't read it rational way for them to the electric hallmark would just wanna get data and then we'll come back later to put specint kind of what we would put all in the in the spouse field for that the core is we don't knows that 2nd case would be the employee is not married and therefore have that you can see this is a different concept right we know we're not coming back at least not in the next week to put the sparse and because of the magnets we focus on the 3rd case and this is the key and the placeholders we do in
our June lying on the and on this in the employee table and we don't have a low there OK I'm not I'm sure many of you have no without joint but effectively in joined when there's a column that doesn't match and you gotta put something there you put in all OK so there's a real users of null different values the use of of moles that that that that we really are representing 3 different cases of we don't want the value of any question right so let's get into
needed by the 1st thing don't use nulls if you don't have to to and I know there's some people who do have kind of gone over the deep end with nulls and usually if you're using those too much you start to get a lot of unpredictable behavior that we talk about here I even mention this 2 days ago we talked about the not no constraint on the columns and that a if you don't want all Borel Arnold the call and frankly I would sing and and we had a discussion what percentage called probably could be not null but maybe you know 80 per your columns probably should be not just don't you don't when only in like customer name if you're the customer name you probably shouldn't be entering into the system right we can get away without an employee's spouse feel just fine but if you don't know the name of employee don't men and and you start to get into some constraints of preventing the notes OK so an example I have here is actually VW's applies to take off and not so and suppose there what is virtually no picture of this not quite you know grinding pliers problem on Google and I would love somebody to me but that's the only work on on so the point is that noses to be used only in special circumstances they're not really to be used when you can avoid using it and because of some of the behaviors of people OK
I warning of this is actually from C. Date and again I think having greater who gave a talk previously I actually talked about the about of card and and how the the focus should be free nulls here's the other sort of father SQL and he's basically comes back and saying I don't use nails although it's a little new once there on anybody's bodies being really honest and writer is this opinion that knows at least as currently defined in this store for more trouble than the word and should be avoided at this layer very strange inconsistent data can be rich source of error confusion so if you haven't been confused by it's not it's not you OK it's moles that's not not your fault that you're confused but at these basic he's basically saying I I will urge you not to use them but which is but I will be seemed contradictory because I will talk at I think of knows a drop if used properly it works but abuse it can ruin everything about policies old and when you can and use them properly when you have that's that's actually Joe Celko who actually spoke a problem and Austin oppose residents very well known as fueled by some as tools for smart is the bookies for everything is very large that you know this is really weird it's not a great system but we have to live with it for the reason of the pliers thing but it's it's reason reported so
I'm not a lot of queries in this text always done is highlight the party should look at in red so everyone's is remember this picture just fine so when I go to this picture everyone's
like up and see where I'm looking up so that's achievable looking for the red so effectively let's walk through some queries here and let's look at exactly what type behaviors on talking about and simply least get a mental framework of how that Knowles behave in various circumstances OK any questions about that OK so 1st call 1st got 1st thing you can do you can select a a literal null value to select null work just fine as you can see nothing shows up there which is maybe not good but what you can do and the SQL is you can ask is to to show you know with a certain strain and I've done this throughout the rest of the presentation because frankly showing you nothing really doesn't help you OK the classic it's it's confusing like we don't have enough confusion we gotta add that so effectively here when you select all I get a null string and pulling that's and it's not it's not 5 it's not 6 characters it's really an older to OK so if you see that no inferences that which you can
but OK so 1st example of how we create table called null test we insert the a literal mall into the field of again assuming we don't know the value of value applied and then we do a select from the table we literally see that that no here from nothing surprising that put in all and we don't know 2nd case up
here on inserting into the same table and here is the 1st case were be implicitly added they could take you've seen in all of you know the but when you look at the output you can see below to also no just like 1 to 1
explicitly add all that is and always there and then this 1 does not know how to do it I actually supplied the columns in the table I want to insert so the
table has columns x and y super created with me
but I'm told the system I only 1 answered and column what the system when for value for called why not and that's exactly what so here we've actually implicitly and Mr. fill who ever this year the robberies common no OK so anyway filled had the idea of crude showing this I think it was a common it suggested in the things that no actually that might be that
that that is a mistake it shouldn't be there so I'll fix it but the
that's actually from previous presentation about because it the week that you have to know exactly exactly also let's get let's I talk to you about the ability to specify not known in your fields OK bases anyone doing here I'm saying that my new table cannot all the 1st field and cannot on the 2nd field that don't walk out moles from getting into inserted I'm not to that of prevented from explicitly getting out and prevent it from implicitly getting there really with me on this OK so by blocking those at the the 2 columns explicitly or implicitly died as 1 that start to look
at what happens when you have and also we talked about getting those into the system implicitly explicitly let's talk about like what happened how you added manipulate and all went the tried add 1 . 0 4 3 on adding 1 twin all on the result is not so it is 1 is 0 it's not because an unknown plus 1 is still and on area and this is a bit confusing but that's common makes sense OK and you have to sort of prepare yourself for that I wanna show you some other cases will later on word starts to really get crazy just kind of makes sense but we to extrapolate this is some other cases it starts decreasing at what waterfront concatenate unknown with I don't get in a dozen act as a zero-length string it's not a and the same thing if I can catch it the other way it's also so you're where it's going to be all right and this kind of makes sense if you start to create a sentence in your concatenated stuff together 1 of the fuses not all from open comes not very surprising since right all here is a little different case of 1st set things up here so creating a table called a test which is increment test OK I insert 30 40 and all into some kind of prepping like Table of that of then I add 1 to it so OK 30 becomes 31 40 becomes 41 and knows the MLE or follows what we talked
about here right with that let's talk a
little bit about the about comparisons of this is
this is operations concatenated increment its peer pressure I am getting there and it will be crazy yes it's really not ready but I have to do it i so if I start looking comparisons on holes we start to get some interesting data lot so if I say is no legal 1 will that's easy that's null and notice it's not full it's not and that's where you start to get into another attribute twist the passage of moles is that when you normally think of comparisons you think of Boolean logic Boolean being true or false when your strong look at Knowles you have it when you do with SQL every comparison is actually a three-valued logic is true to be false it can be or not and there is some very unusual behavior that you start to see in the later queries were that no unknowns doesn't even behave like like wow OK so it appears common effect on is the world 1 no is equal to 0 and strange no it might display is a zero-length string to the user but it's not the same zero-length string there's no equal moles right now here's where you started use restored to get the twiddle passages all light right so there's no legal no actually we don't know all because of an old 1 mole could come from someplace a little chromosome comparable results it could be 2 rows are both knowledge equal with are right about cholera logic that they're having you a gets about yes so this is comparing literal value and also some other little over value all right this kind of really crazy come again as I said there's some extrapolation starts to go and when I said
no plus 1 is null all I said we're going see a case where you
start extrapolate that out it starts cotta weird is no less than 0 plus 1 well you know I like the math class on null less than 0 plus 1 is probably true unless the 2 different if you from different places at which point I don't know OK and that's the way that these the fact that the astrocytic committee says is there's 2 moles there comes different places in different rows there aren't maintenance servicing all on because again if you think of adding rows you recall the represents weight it also think on 1 pound on 5 pounds and 10 pounds employed different parts right where they have to those that have no weights are all that means the part part of 1st row might be thousand found the most economically 2000 value know OK so that the concept here is it because I've got to all I can assume that they're the same value as I would if the 2 weights were 10 thousand point out I know the this that know those values are right so even something logically you would think would be true comes out is not but were unknown
so all query comparisons of here's word starts to get interesting select wall and 1 where true that's the return or select 1 where false that's not going to return anything that's not in all well that's nothing select 1 where null which nothing and in fact somebody or are you I think this might have been a common I can't remember somebody said the where clause should be called like the sure clause like are you sure it matches right because effectively I effectively we're returning where only returns a result 1 true false and not on all the years we get three-valued logic thing going on so I could
take a look at the end it were kind of case are true and all is not old because we don't know if the null is true or not that kind of makes sense on select not null the inverse the opposite of null is of course not right I had a little surprising there I guess we don't know the true false so we don't know if it's true or false nodded and therefore it's on right know operator
comparisons again coming back here to look at some of the row comparison so
willing combining in my mind would combine these
tasks OK this operator test that we went through and the
where clause test that we went through and I wanna show you
combine the 2 now back to my test Table 30 40 in all very easy to understand right 3 the 3 rows all all of those rights greater than 0 30 40 that makes sense although they're less than 0 and nothing there no rosy nothing nothing we don't know that because know is we don't know all all rows the less is 0 or greater than equal 0 now that's a real tough 1 not to say and all emulsion match but it because because again this returns and all this returns in all the result is a novel because of the where clause were closest I only return true and can assert yeah yeah it's coupling 0 time 0 times I would think of as multiplication with 0 but as soon as you multiply you a billion ng will what 0 0 right so when you start to change these things together any these results the return NULL ecologists which pollutes the whole expression and this expression right how long it is a how much we try kind of narrow it down and just it's just part which just the whole and and 1 on 1 of the criticisms of nulls is that the fact that the the null has a tendency to put itself way beyond the comments in and out and and just starts to spill out all over the place and and and has a very negative were free surprising consequence near you might not suspected of In
other cases and give me all the values like 10 x easy give me all the values that are 10 110 or 10 and right so you can where you not nominals there have again that that sort of pollution effects across expressions of knowledge where it starts to get a little confusing is that not in clause and when the kind of show you example of expressed doubt so if I'm doing comparisons and 1 is 2 and 1 is a really well that's true because 1 is isn't and 1 isn't very bright or cracked by doing this every comparison wise into wise 3 and 1 is not all that I get all because I don't know if if that isn't what might be 1 so if we if we convert back OK so I say selected a in null I get what I would think of as a null because effectively that's not and we don't know but I say a is not being a world a all again I get enough so what what a lot of times you have to take away is that a lot of fuel well might in clause I understand all that but prevent that from matching but my nodding clause will that shouldn't have a problem they think of it as the negative so I get all the words that are in a certain set and all resident not in a certain sense and use to results together should be the full set that's in my mind that's when thinking right i got evidence in this group I get everything that you know I'm getting evidence and then everything is not in a combined these 2 I should get the original group and that what I'm saying with with Knowles bad is no longer true because Mills mean that I might have some things that are in the group and don't know it and all nite in the group and no it's not and they don't show up and a lot of time you spend time volume queries from why I'm missing rows a lot of moles the corpus of
lower seen in example cell is a in a were not now this 1 actually works because we're saying is in a in a result in a or know what's in the yet so would not we're look over here we know this matches we like OK true that we're go have not in his whereabouts everybody and I'm not get a list the hands bit but not in always is a thing that gets people not with so is a not in a were not in knowledge from this 1 works because you know that a is in there and so you can return false you're good there is a and B on all I don't know here's the 1 that kills everybody is a in the war I know I don't know and a lot of times people do sets and also give me everything that is in you know I don't give you everything that's like less than 100 whatever and then all of a sudden there's always Rosa don't show up and they kept their wives because the guy also kind of in fact pollutes the whole system so I could have 20 b is over here have thousands arose assumes they get this thing and not and just as I don't know that does return anything so it really conjecture really but
let's expand at a query is a would be worth a equal than all of this is that the bottom 2 queries expanded is a in B. or is a in all don't know is in a not in b and is a not a know again I don't know this is this is where you start to see the 1st but OK
of explicit no comparison somebody already talked about this I show example up here no equals no remember that that's like is no knowing equal returns I don't know if you need to test for and all you probably are explicitly going need to use a special clause not equal in all that is not OK is not all is not no yes this is where mapping now my knowledge to into the Boolean world so I get my null out of 3 lots 3 valued logic world and is no basically pulls it into the Boolean world when I know my results can be true or false right same thing would not it is known that in his slight no is not know this for this keyword is the keyword here is is not so he is is not something you can use on it so now you know it's always is not the question of and and again this pulls it out so for example if we could when we're trying to do something
like not in a lot of times what you will end up doing is is burning on is not known passed in your yourself to prevent this
type a comparison so what will do is will say yes like anonymously where column is not null and the prevented Knowles from propagating up into this result set and therefore the not in will properly evaluate by extracting out here I'm saying so for example if you want you want all your customers who were not in Pennsylvania OK but you would say maybe were selected or your customers you out for whatever reason if you have some customers that on all not really really bad because it's like more customers not its list of areas is null so therefore supplement to a lot of what people do the is not null and the prevent that is not knowledge from the knowledge from getting up into the target list and being seen by the is not null and notes on on during the you know I'm to you prevent is the notion and getting up the Coriolis therefore your you're is all is your in or you're not in now behaves logic that that you might have been able to work and in that
I don't think performance really ever enters into the use of of what is known is not all it's war on of a desire to exclude Knowles from from polluting out in the rest of your comparisons that's really where I think the winners on because they showed you with concatenation and all these other things and not in in this and and and so forth a lot of times the wave signal propagates it's this sort of polluting things that sort of monsters spread uncertainty through the whole expression or even up into the op at query and logical who know I've got some data that i don't want process OK all of it is people just people say you know prior of buddhist exclude anything that has a knowledge field they're working with and that really is the filter that prevents it from going out and up and causing to have unpredictable behavior search now that but is not unique it's there but it's the behavior of the OK right on so
I if we go down to this test it starts to make a little more sense were kind of green things together here so we have a 3 rows in the test because we can say select store where X is null and I'm not pulling out just the null rows OK and now selecting because when x is not known on pulling out the non rows so as I said before word you think of your you think of a result set as here's all the ones that are in here of all the ones that are not in combined that should be my full set you should probably have a 3rd version which says here from a wondering here's royals the not in and here's all my and and know what think of having a 3rd set of values which combined with the 1st 2 will give you your original set because that's what I have a lot of was application programs they develop positives and then give me all the native American was a meal and you know the negatives and you know the non-negative right 0 and positive right everything bounding the whole set and I have to queries would say all the negatives of this with all the all the non-negative so 0 and positive 1 this work I would cost somewhere around like willing and the site I got a bit I don't have not known that column so therefore about all the not field analysis of my have to be at the 1st block which is going to say give me all of the known world right field and do something which and that's why I think a lot of concern you saw earlier in the quotes he was saying adult use right I mean to be careful when you use them because now all of a sudden you're logic has to be yet enough think when you're doing that queries housing over and behave in all these situations but he's you don't care in everyone in Pennsylvania you don't want you don't care if they're not in Pennsylvania the not the null data does not in Pennsylvania right in most cases you don't care it usually a transfer of the result set into 2 parts like give me all Pennsylvania people in everyone who is in Pennsylvania that's when you usually get caught right because there is that set it is an intensity or not in Pennsylvania it all yet all of which was to be a really want 1 had how you have to do with that we have to break the story of the calls and all of which you this
all I'm actually got to get I am I actually have some command specifically for that case but we have 1 more slide virtuous lots
of that so on another
member told about and all is
null and all of that there's another clause that we have which is a little difficult this thing this things a
little hard to get your head around but it allows you to test for whether a value is eager to see the different from were all and it returns true for example I'm saying is not all is distinct from what is it with you will you what it means is is it is it is it true or is it just a null and both of those Indistinct land on different from the 1 record of stars have actually I think that the story about using a 1 year to here so that I had my colleges that should and on that on the fact have say is 1 different than to guess it's kind is no different from 1 yesterday OK is not all that different from the all no it's not of that so what this allows you do is instead of doing inequality comparison with the value you know equals 1 which obviously returns NULL if I use is distinct from its effectively saying or you not equal to that value and if you know your so much OK so I'll realize I don't like the fact this is the negative so distinct from is effectively and not equals of and in fact what you
really crazy when you're reading not distinct from which is effectively equals so I don't like that I don't know if I could afford a better solution although to effectively allows us to do comparisons between Knowles and pretend we're doing not between Knowles and potentially non OK where we say and not equal or equal within all returns false OK so he needs to do quality on quality comparison not is not always knows testing of all-weather that's pretty easy really need to do a comparison between column and something else OK distinct and not to stay on are actually pretty nice to use all of the negativity really throws me from this is that that we kind got because I got stuck on
here is an example so long inserted columns all 1 1 2 3 a and all all OK and I say is X which is the 1st call on distinct from why now 1 is this not distinct from and all what would the out was not seen from 1 so we return true is distinct from 3 so we don't return it is not all not distinct from all I know it's like true and I know it's it taken from the analog times on but effectively it allows us to again map that all behavior into a Boolean behavior which is only individual rights to think of it as mapping that what we normally return all 4 equality comparisons into a true or false so that you can work with particularly this useful for joint because you know in your kappa taking so it's an older history not equipped with joining 2 columns and some of them might be in all this distinct from can be very helpful if I am not that if you look at the grammar of island think we haven't is operator it's like is it actually have to be those keywords in that order yet whether it is optional not distinct is optional not all of its ruling there is no ears alone without you know I guess you could say it is for a it's for an old through behavior checking makes sense there atoms here here you can see is true yea EASO your right hand on the blue so maybe it's only for Boolean maybe because you right is both of these return Booleans is null and you can say false is true that work so you're right is I guess is operated on that its own little thing but you're right images using the doesn't recognize but let's take a look at watering I worry is a case where I think we did we basically I sort discard a lot of rules we talked about already and we make up new ones of so here we have a table that has and knowledge to a 1 and and all it looks just like this because I want you know is saying is that these nulls are now all the way to the end and we talked about moles equaling all they don't write was returned all all but when you order them all and sundry crucial match right of and the reason they do that is because they they really couldn't do anything else right to randomly sprinkling themselves in the ordering really wasn't do anyone any good so effectively you just have a fall back and so what's the practical behavior that would make the most sense would have put them all together here are only go each other and we found that the people and beaten to heads all this time OK but I think technically what they would say is that you get you get the unknowns at the end right so the unknown just seem to come on the same thing we can actually specify the nose the go 1st and we can control and surveillance of and I similar to
index is we we throw the baby out a little bit or maybe not I don't know I refuse to work for the heading into crazy land here so if I create a unique index I connection but 1 moles in that unique infectious fine OK because they don't eat which others therefore they're not unique I think right arms here I can obviously but also on I think the reason is it work with only around 1 Nolan unique field there is some yeah there's some other databases that doesn't add member of we get this theory requires us to do this so we break the standard that I don't know of we just thought it made the most sense to modes with the use of because the idea that that 1 only when the nobles making sense of the are kind of unique if you look at some and
I promised to bend your brain on this 1 I again are you sort of get a practical behavior that may not match what you saw before work so and here we have a table on putting in 7 8 and all K I test very easy understand but emerges 1 a bunch aggregates on this count store count and a field made some max and average but when you cannot store accounts regardless of the values in the wrong place you get 3 because 3 rows when you specify a field in the Chao parentheses then we only count nominal values of convenient so in fact we have to do here because the only to nominal values of the not very of a very every obvious that that's what it would do but you just have to put broaden your brain on counts store and column actually have these different behaviors on so that's pretty clear well 15 well in not really good 7 plus a plus is not all right we talked about that in the earlier slides but and it's further out to discard that right I would argue the justice count of the look at moles in the field solemn and min and max and the average don't look at moles in field theory and just have to word there so this song is 15 it's the sum of the nominal values the minimax make perfect sense the average again when you're only looking at 2 rows so their average then it's sort of jobs or with the knowledge there really is a 7 . 4 it something else and it really is not to be my mind but I guess returning for the field this would make a lot sense like his we have the state is not old over the place right it's of each of the 4 what was on the undivided right here on Monday and dividing by always just takes me to a whole new level of what's also interesting about you delete the table images 1 this integral the table I start to get reasonable values they get counted 0 no rose count both of them were 0 I get a solid knowledge of 0 which kind of surprises me but not and I think they did that because the min and the max and average problem the minimax particularly would have to been all you could guess what a max of the world's would be right so we just kind of course the words backwards ritual moles of fears of Z obvious alone I'm open so that it also puts rose back
in their generic but to no value rose in the in the table on and around the same query of the with the with group bar and this is where you know were really out space here OK because as I said before and all never equals and all but you know group by it's like a let's go you know world altogether results OK so just as the added had is weird behavior for Knowles
group also role those equal each other a video of so much my takeaways don't assume that it was knows where behave just as the you typed out the solemn with the you know saying which is not the same all it has some sort of super behavior and governing normals which we could understand he'll
were annoying also get there OK but but wait when I grew by moles are no good
citizens again and they all equal to each other right this makes no sense at all and I don't pretend to make sense of all that the Committee wouldn't it make sense this sort of pointless so well we think is the most reasonable it can you know it's quite inconsistent even from the previous slide this
yes internally that have uses an alternate that work and then sort of put in in the
same pocket yeah so that the rows the 70 it's pretty easy but if I do counts for this is really really interesting if I don't tell online all rows they get 10 do but if I do it can help with the accident I get 0 because remember the count with the field and then ignores the nulls even though the group bias current processes and also right arm and I have a solemn of knowledge makes sense and minimax people know at that point at all
mapping also strange this is with the German green shortest about and dates and things again there's some cases where knowledge of just a propagate how we concatenate something that all you know this is driving me crazy right we do have a few commands was called call last OK all I would call does is basing its as if it basically allows you to have as many using a very simple example but it allows you have many rows as many parameters as you want and will return the 1st non no parameter crazy definition frankly I it's like the non it's like this thing being not equal to it's like FOS melodramas as you want and I'll give you the 1st normal 1 hour wine 1 1 1 2 but they support WormBase doing uses they the 1st fields no 0 that's pretty stupid if the 1st field molten that string I not good OK that's what we use for a we could say if here's were were were were we got a n a g and and all in a table on Wednesday instead of returning acts on return coalescent X and and a lot applicable not unknown in this case I know no means not applicable only meowing understand I in this case I'm saying no you all if you do the cause you really get x enough to get the color if you want to call you have to duplicate the coal the numerical and then compare that to restrict yes if you need call as the problem is the way that SQL is executed it's everything from the from down and then the target list so the names are typically not accessible only at the end of the query and there's some cases where that's not true that a clean water by you can I think reference the colony in the specified but in the where clause in the from college you don't have access to the means of the A-list pose rested someone but typically those names are really for display only and kind of also for a group by and were about and again I'm not proud of that behavior and think was defined that way by the standards of work on stuff but it's again a very consistent behavior I think because I tell people well the populace is only evaluate the end but then you can mention that in the process I believe you can mention the the the the aliases in water by which you can you mention in the work crazy but not proud but I think we did the best we could just south of here I'm getting anyway in my field instead and also this would be will be used for by date of here's another cases might exactly wanna concatenate OK 3 fields and I and this 1 if there's if there's a knowledge is what I just 1 single quotes so I would normally put the column here the column 7 0 I have a column and I'd say if that column is null give 0 is 0 like string cages disappear there was no justice disappear great and this 1 is kind of a fixed for the previous 1 so remember I said that when you do a song on call if it's no you get 0 you get like so busy what I'm saying is if you do a song and the son returns NULL I was 0 there because it allows you to kind of whack around this
thing so you don't get in all year for right so among color
combining Allison Cole world
of mapping strings and all this is the opposite so instead of taking a null and mapping into a strange Almanac strange to in all so here I'm saying knowledge if x isn't all all all the warmth returned all if X is equal to that strain from now on instead of taking all the morning strings out on testing for strings and having those come out OK so this is the number of moles in string out is coalesced strain in moles there is no way I still can't remember some something of the basic what I'm saying here's I'm saying it x is not but if X is a return and all in fact I'm doing that right here about you specific in and this is the US combined swung taking and all converting it to take in all converted the NASA and then NASA is gonna match that's referring all so doing both in and out this doesn't illustrate of confusion things just like to get yeah that's the quality of
life of yeah yeah you will look you could argue the problem was that there should never be Knowles and I call my son in the 1st place yes he deftly could be that the the set words in United Arab join which would you can with that work that's restart its image a little bit to
wrap up all of these we also support role expressions again there's some pretty unusual behavior here but is a novel image or a nolle yes it is is an entity into during all no it isn't but is a not all inside of an integer rain all it is not OK but getting a legal crazier we have rather expressions but is well and that is in the row and all yes is a row containing all in all it is is working to and all it is is rotating the null and non all all it is not but is there so contain all and non not all it is not OK but and then 1 word to not no that's true so effectively the behavior it is if there's only knows and there we can return true we we know what it is is only nominals in there we were can return true contested vision makes a return for this pathway this
that that would that would come to clean it up and another case where if you have an empty table in your select clause that effectively return and all well I know that's kind of weird but every it's assumed that every select clause in part it was from 1 well if it doesn't return any roads we will affect throw in all there and this is a surprising behavior I think in something somebody had had no response so
I think get it no you don't know and it's really it's really a crazy mix of behaviors and Jeff Davis use you will see walking around very nice guy but but he says you know you had a blog posts I pull this out of there he said well that makes sense when you see an image with the original they look systematic in your brain quickly see the pattern extrapolates what might happen in other situations often that extrapolation is wrong because no semantics are mix of behavior I think the best way to think about what all is as a friend in some monster of several philosophies and systems stitched together by spurious series of special cases and I think I have added illustrated that here and I wish there was a systematic way I could presented to you but for practical reasons and because of the way standard is applied it is a Frankenstein of behaviors are but hopefully this presentation is helping to I'd better that not to feel like you've missed the boat somehow on and you realize that when you see nulls in different circumstances you can now sort of matter how you would think that would be in and with that
there's nothing left to say so thank you very much of the actual few the actual series of blog posts is right at this URL so if you want to share this pain with your friends and you can actually look at the original block 11 blog post with text of that explains it to you of course this entire presentation is that that you are l right here so long as they were published late on the frame of the questions we did take some during the class and I thank you for that and we want to use
Randverteilung
Relationale Datenbank
Rückkopplung
Web Site
Web log
Schaltnetz
Kombinatorische Gruppentheorie
Web-Seite
Computeranimation
Datenhaltung
Datensatz
Mustersprache
Inhalt <Mathematik>
Analytische Fortsetzung
Gerade
Analysis
Autorisierung
Fehlermeldung
Softwareentwickler
Reihe
Mailing-Liste
Physikalisches System
Quick-Sort
Teilbarkeit
Rechter Winkel
Hilfesystem
Speicherabzug
Wort <Informatik>
Unternehmensarchitektur
Aggregatzustand
Bit
Punkt
Formale Sprache
Gewichtete Summe
Kartesische Koordinaten
Computer
Kardinalzahl
Dicke
Raum-Zeit
Ähnlichkeitsgeometrie
Computeranimation
Eins
Prognoseverfahren
Prozess <Informatik>
Randomisierung
Punkt
Maschinelles Sehen
Gerade
Freier Parameter
Datenhaltung
Biprodukt
Zeiger <Informatik>
Algorithmische Programmiersprache
Ebener Graph
Rechenschieber
Datenfeld
Emulation
Rechter Winkel
Ganze Zahl
Deklarative Programmiersprache
Information
Zeichenkette
Tabelle <Informatik>
Ebene
Mathematisierung
Gefrieren
Zahlenbereich
Kombinatorische Gruppentheorie
Virtuelle Maschine
Datensatz
Datentyp
Zeitrichtung
Speicher <Informatik>
Zeiger <Informatik>
Bildgebendes Verfahren
Soundverarbeitung
Videospiel
Physikalisches System
Quick-Sort
Zeichenkette
Mapping <Computergraphik>
Quadratzahl
Flächeninhalt
Mereologie
Subtraktion
Matching <Graphentheorie>
Freier Parameter
Computeranimation
Metropolitan area network
Bildschirmmaske
Datenfeld
Rechter Winkel
Reelle Zahl
Dateiformat
Wort <Informatik>
Speicherabzug
Repellor
Schlüsselverwaltung
Innerer Punkt
Tabelle <Informatik>
Nebenbedingung
Chipkarte
Server
Punkt
HIP <Kommunikationsprotokoll>
Extrempunkt
Computeranimation
Open Source
Vorhersagbarkeit
MIDI <Musikelektronik>
Tropfen
Speicher <Informatik>
Zeiger <Informatik>
Fehlermeldung
Elektronischer Programmführer
No-Free-Lunch-Theorem
Systemaufruf
Physikalisches System
Quellcode
Zeiger <Informatik>
Fokalpunkt
Quick-Sort
Rechter Winkel
Dimensionsanalyse
Wort <Informatik>
Innerer Punkt
Fehlermeldung
URN
Inferenz <Künstliche Intelligenz>
Rohdaten
Klassische Physik
Abfrage
Systemaufruf
Kombinatorische Gruppentheorie
Framework <Informatik>
Computeranimation
Datensatz
Trennschärfe <Statistik>
Datentyp
Zeiger <Informatik>
Hilfesystem
Zeichenkette
Tabelle <Informatik>
Softwaretest
Retrievalsprache
Softwaretest
Ganze Zahl
Datenfeld
Computeranimation
Funktion <Mathematik>
Tabelle <Informatik>
Tabelle <Informatik>
Retrievalsprache
Fehlermeldung
Nebenbedingung
Speicher <Informatik>
Physikalisches System
Computeranimation
Datensatz
Metropolitan area network
Datenfeld
Ganze Zahl
Softwaretest
Elektronischer Fingerabdruck
Stützpunkt <Mathematik>
Tabelle <Informatik>
Tabelle <Informatik>
Resultante
Softwaretest
Ganze Zahl
Flächeninhalt
Rechter Winkel
Wort <Informatik>
Physikalisches System
Primzahlzwillinge
Computeranimation
Tabelle <Informatik>
Zeichenkette
Tabelle <Informatik>
Resultante
Soundverarbeitung
Nichtlinearer Operator
Bit
Mathematische Logik
Datentyp
Extrapolation
Abfrage
Paarvergleich
Extrempunkt
Mathematische Logik
Computeranimation
Druckverlauf
Datensatz
Ganze Zahl
Differenzkern
Rechter Winkel
Dreiwertige Logik
Boolesche Algebra
Zeichenkette
Attributierte Grammatik
Softwarewartung
Subtraktion
Datensatz
Mathematische Logik
Datentyp
Punkt
Gewicht <Mathematik>
Klasse <Mathematik>
Mathematisierung
Mereologie
Gewichtete Summe
Varianz
Computeranimation
Resultante
Retrievalsprache
Nichtlinearer Operator
Dreiwertige Logik
Inverse
Paarvergleich
Abfrage
Wort <Informatik>
Paarvergleich
Computeranimation
Softwaretest
Resultante
Retrievalsprache
Nichtlinearer Operator
Datentyp
Mathematische Logik
Matching <Graphentheorie>
PASS <Programm>
Paarvergleich
Paarvergleich
Nichtlinearer Operator
Computeranimation
Task
Datensatz
Arithmetischer Ausdruck
Multiplikation
Rechter Winkel
Mereologie
Zeiger <Informatik>
Soundverarbeitung
Resultante
Multiplikation
Gruppenkeim
PASS <Programm>
Zellularer Automat
Mailing-Liste
Paarvergleich
Physikalisches System
Quick-Sort
Computeranimation
Datensatz
Arithmetischer Ausdruck
Datensatz
Menge
Ruhmasse
Wort <Informatik>
Spezifisches Volumen
Mapping <Computergraphik>
Resultante
Retrievalsprache
Rechter Winkel
Paarvergleich
Abfrage
Paarvergleich
Mathematische Logik
Computeranimation
Datensatz
Resultante
Retrievalsprache
Multiplikation
Flächeninhalt
Menge
Datentyp
PASS <Programm>
Mailing-Liste
Paarvergleich
Computeranimation
Monster-Gruppe
Arithmetischer Ausdruck
Datenfeld
Prozess <Physik>
Rechter Winkel
Wellenlehre
Paarvergleich
PASS <Programm>
Abfrage
Vorhersagbarkeit
Paarvergleich
Quick-Sort
Computeranimation
Resultante
Web Site
Bit
Mathematische Logik
Ortsoperator
Versionsverwaltung
Wärmeübergang
Mathematische Logik
Computeranimation
Eins
Datensatz
Negative Zahl
Optimierung
Speicher <Informatik>
Drei
Analysis
Softwaretest
Konvexe Hülle
Green-Funktion
Paarvergleich
Systemaufruf
Abfrage
p-Block
Rechenschieber
Datenfeld
Menge
Rechter Winkel
Mereologie
Wort <Informatik>
Datensatz
Subtraktion
Mathematische Logik
Ungleichung
Paarvergleich
Paarvergleich
Computeranimation
Zustandsmaschine
Formale Grammatik
Ordinalzahl
Kappa-Koeffizient
Computeranimation
Eins
Negative Zahl
Softwaretest
Ganze Zahl
Ordnung <Mathematik>
Zeiger <Informatik>
Bildgebendes Verfahren
Analogieschluss
Schreib-Lese-Kopf
Softwaretest
Tabelle <Informatik>
Nichtlinearer Operator
Matching <Graphentheorie>
Paarvergleich
Schlussregel
Paarvergleich
Mapping <Computergraphik>
Differenzkern
Rechter Winkel
Boolesche Algebra
Ordnung <Mathematik>
Tabelle <Informatik>
Lesen <Datenverarbeitung>
Mittelwert
Bit
Gewichtete Summe
Extrempunkt
Gewichtete Summe
Extrempunkt
Zählen
Physikalische Theorie
Computeranimation
Übergang
Metropolitan area network
Datensatz
Ganze Zahl
Perfekte Gruppe
Mittelwert
Prozess <Informatik>
Zählen
Speicher <Informatik>
Bildgebendes Verfahren
Tabelle <Informatik>
Einfach zusammenhängender Raum
Automatische Indexierung
ATM
Feldtheorie
Finite-Elemente-Methode
Datenhaltung
Eindeutigkeit
Indexberechnung
Quick-Sort
Datenfeld
Einheit <Mathematik>
Ungleichung
Rechter Winkel
Automatische Indexierung
Wort <Informatik>
Tabelle <Informatik>
Standardabweichung
Aggregatzustand
Mittelwert
Gewichtete Summe
Gruppenkeim
Abfrage
Extrempunkt
Raum-Zeit
Quick-Sort
Computeranimation
Homepage
Videokonferenz
Gruppenkeim
Vererbungshierarchie
Zählen
Normalvektor
Ordnung <Mathematik>
Tabelle <Informatik>
Mittelwert
Punkt
Prozess <Physik>
Gewichtete Summe
Gruppenkeim
CAM
Zählen
Extrempunkt
Quick-Sort
Computeranimation
Homepage
Rechenschieber
Metropolitan area network
Datensatz
Ganze Zahl
Datenfeld
Gruppenkeim
COM
Rechter Winkel
Zählen
Ordnung <Mathematik>
Zeiger <Informatik>
Mittelwert
Aliasing
Prozess <Physik>
Wasserdampftafel
Datensichtgerät
Ausbreitungsfunktion
Gewichtete Summe
Extrempunkt
Computeranimation
Metropolitan area network
Datensatz
Zählen
Ordnung <Mathematik>
Parametersystem
Schmelze
Physikalischer Effekt
Green-Funktion
Systemaufruf
Abfrage
Mailing-Liste
Arithmetisches Mittel
Mapping <Computergraphik>
Zeichenkette
Datenfeld
Gruppenkeim
Rechter Winkel
Ruhmasse
Kantenfärbung
Simulation
Tabelle <Informatik>
Zeichenkette
Standardabweichung
Zeichenkette
Softwaretest
Mapping <Computergraphik>
Matching <Graphentheorie>
Rechter Winkel
Gewichtete Summe
Computeranimation
Normalvektor
Zeichenkette
Videospiel
Bit
Konvexe Hülle
Gewichtete Summe
PASS <Programm>
Regulärer Ausdruck
Computeranimation
Datensatz
Zeichenkette
Arithmetischer Ausdruck
Datensatz
Ganze Zahl
Wort <Informatik>
Maschinelles Sehen
Bildgebendes Verfahren
Nominalskaliertes Merkmal
Retrievalsprache
Subtraktion
Web log
Extrapolation
Kombinatorische Gruppentheorie
Interrupt <Informatik>
Computeranimation
Formale Semantik
Monster-Gruppe
Systemprogrammierung
Mailing-Liste
Ganze Zahl
Softwaretest
Trennschärfe <Statistik>
Mustersprache
Endogene Variable
Mixed Reality
Monster-Gruppe
Zeiger <Informatik>
Bildgebendes Verfahren
Tabelle <Informatik>
Reihe
Physikalisches System
Extrapolation
Datensatz
Reihe
Mereologie
Ablöseblase
Standardabweichung
Tabelle <Informatik>
Web log
Rahmenproblem
Klasse <Mathematik>
Reihe
URL
p-Block
Kombinatorische Gruppentheorie
Computeranimation
Web log
Kombinatorische Gruppentheorie

Metadaten

Formale Metadaten

Titel Nulls Make Things Easier?
Serientitel PGCon 2013
Anzahl der Teile 25
Autor Momjian, Bruce
Mitwirkende Heroku (Sponsor)
Lizenz CC-Namensnennung - keine kommerzielle Nutzung - Weitergabe unter gleichen Bedingungen 3.0 Unported:
Sie dürfen das Werk bzw. den Inhalt zu jedem legalen und nicht-kommerziellen Zweck nutzen, verändern und in unveränderter oder veränderter Form vervielfältigen, verbreiten und öffentlich zugänglich machen, sofern Sie den Namen des Autors/Rechteinhabers in der von ihm festgelegten Weise nennen und das Werk bzw. diesen Inhalt auch in veränderter Form nur unter den Bedingungen dieser Lizenz weitergeben
DOI 10.5446/19052
Herausgeber PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
Erscheinungsjahr 2013
Sprache Englisch
Produktionsort Ottawa, Canada

Technische Metadaten

Dauer 1:04:09

Inhaltliche Metadaten

Fachgebiet Informatik
Abstract Nulls are a very useful but also very error-prone relational database feature. This talk is designed to help applications developers better manage their use of Nulls. It covers the use of NULLs in relational databases, with a focus on Postgres behaviour. It covers three-value logic, comparing nulls, mapping nulls to strings, indexing nulls, and aggregates.

Ähnliche Filme

Loading...