Next up, I want to highlight some of the default enabled modes that are worth knowing about.
So one of those is full group by or only full group by this mode controls which columns we can select
when we perform a group buy.
So it says reject queries when the select list refers to non aggregated columns.
So what that means is right now let's see, what is my mode look like?
Yeah, it's set to an empty string, so let's reset it or let's add it back into the mode.
We'll do set session and then we do SQL mode equals and then I'll say only full group VI.
So this should be enabled by default.
But because in the previous video I disabled everything, I had to add it back in.
And now it's the only mode that's enabled session, at least globally.
Different story.
So if we were to write a query that involves a group by like if I select from the series table, let's
do title and join series with reviews.
So title and rating from series Join with reviews on where the reviews dot series ID equal to the series
dot ID.
All right, so we know what this query does.
It's not a group buy nothing fancy looks good.
But now I'm going to do a group by right.
I'm going to group buy and then I'll group by title and then let's get the average rating.
OC.
And there we are.
We see what we expect the title and its average rating.
So what full group I are only for group I says is that I'm only allowed to reference or to select columns
that are either aggregate function columns like average counts or columns that I have already named
in the group by clause, like I'm grouping by title, therefore I can select title.
But if I wanted to just include rating, remember we talked about this when I first introduced group
by my SQL, doesn't know what rating I'm referring to necessarily because we've grouped by title.
So which rating?
There's maybe five different ratings.
So if I run this.
I get an error.
It says expression number two of your select list, which is rating.
This is one.
That's two is not in the group by clause and contains a non aggregate column.
So we broke the rules of this SQL mode only full group by mode.
So if I instead change that SQL mode, I'll just set it to an empty string.
Only four group Bye is gone now.
Our session SQL mode is just an empty string.
If I rerun this query here, it works.
But it is confusing because what is this really showing me?
I think it's just taking the first result from each group, the first rating.
But this is not averaging.
I'm grouping by title, but I'm looking at one rating from each group, which is weird.
So that is the same query that gave me an error before and did not work.
Let's just re-enable that one more time.
Only full group buys.
Only for group buy.
I think it is run that query and now it gives us an error.
We have to use some sort of aggregate function or we have to select columns that are part of the group
by clause.
That's why we can get title because we're grouping by title.
But rating is not an aggregate column and it's not part of the group buy.
So we get an error and that's because of this mode only full group by a couple of others that we should
highlight that are enabled by default.
Let's look at the global mode.
So select at at global is it SQL mode?
So there's only four group by strict trans tables error four divided by zero.
But then we've got these two No zero and date.
No zero date.
These have to do with well, zeros in dates.
You can read about them here.
If we look at no zero in date, it affects whether a date like this is valid.
Where we have a year, 2010 we have a month, but then the day is zero zero, which I guess you could
use to represent no day.
It's just a year and a month.
Same thing here.
2010 0001 There's no month present, so I don't know if it's worth even showing you examples of this
just for the sake of time, but that is what this mode controls.
If it is not enabled, then you can have zero parts in your dates and if it is enabled, it won't allow
you to have those zero parts in a date.
So if I tried to do do we have any date columns, I don't think we actually do right now.
So that's not going to be very helpful.
Let's do select date and then I'll provide a string like 2010 0100.
It let me do this.
No problem.
I'm just calling the date function and providing this string because once again, we look at our SQL
mode, we don't have anything in there.
I think we only have full group buys.
If I instead add it in.
Let's just verify it's not in there.
Select Session dot SQL mode.
That's all we have in there.
So I'm going to set session SQL mode to be equal to and then I can't remember the exact name of it.
Yeah.
Ngozi wrote in date.
Now, if I try and do this query right, where do you go?
I think I went past it.
Select date where we have a00 in the date.
This time we get a warning.
Our result is null.
And if I show warnings.
Oh, I need a semicolon there.
Try that again.
We see I'm getting the warning that I just generated there.
We'll try that again.
Let's run this query.
Select date now, show warnings, incorrect date, time value.
And that's because of this mode that I enabled.
Previously.
It was not enabled and so we had no trouble whatsoever.
Now, if we go back to the docs and re re reread what it says about how this mode works.
It says what we already know if the mode is not enabled.
Zero dates are permitted.
If it is enabled, you'll get a warning.
But then there's a third piece that says if this mode and strict mode are enabled, then you'll get
a full blown error.
So what is strict mode?
Well, strict mode is going to be enabled if we have either of these two modes enabled strict all tables
or strict trans tables and strict trans tables is enabled by default.
Currently, I actually turned it off because I set the SQL mode just to be only node zero in date.
So this means I'm getting a slightly different behavior than I would if I had strict mode enabled.
So strict mode also changes what happens with some of the other modes like we see right here.
So I'll show an example of this.
If I create a table, let's just have a very simple table called dates and it has a single column.
Oh, where'd you go create table dates?
It has a single column DX of type date.
If I insert into dates and then I insert dx and dx.
Dear Lord, insert insert values.
And then in here we'll do something like 2023 dash, I don't know, 11 zero zero.
That is a date with zero in it.
I get a warning right Show warnings OC warning if I select star from dates.
It did go in there as a full zero date, which is the anticipated behavior.
It says produce a warning insert 0000.
But if I enable strict mode which is enabled, as soon as I add in strict, I keep doing a space that
are strict trans tables.
Now, if I rerun this query to insert, I get an error where we have enabled strict mode and strict
trans tables governs the behavior for invalid inserts and updates.
What happens if we have a data validation that says this has to be a date but you provide an incorrect
date?
Well, long story short, it modifies other modes like Node zero and Date.
So that is important, not just because of this mode.
This is not that common if an issue to have zero dates, but also because if you're reading the docs
for some of these other modes, you might see it say something about if strict mode is enabled versus
not enabled, this will behave this way or this other way anyway.
That is it for modes I'm going to go ahead and reset.
The easiest way would just be to quit this session entirely.
Just quit and then log back in my SQL dash you I'll log in root dash p, put my password in there and
now if I do a select session SQL mode, everything has been restored.
But the other option as we saw, is just take that string and whatever pieces you want, whatever which
which of these settings you want to include, then you can do set session, SQL mode, or you could
even change the global mode.
But I usually don't mess with that if I'm just teaching this stuff because there's a reason these are
enabled by default.
All right.
So that's the discussion around modes.
Very exciting stuff.
I know.