1
00:00:00,120 --> 00:00:04,830
Next up, I want to highlight some of the default enabled modes that are worth knowing about.

2
00:00:05,010 --> 00:00:12,690
So one of those is full group by or only full group by this mode controls which columns we can select

3
00:00:12,690 --> 00:00:14,250
when we perform a group buy.

4
00:00:14,340 --> 00:00:20,150
So it says reject queries when the select list refers to non aggregated columns.

5
00:00:20,160 --> 00:00:27,000
So what that means is right now let's see, what is my mode look like?

6
00:00:27,240 --> 00:00:33,180
Yeah, it's set to an empty string, so let's reset it or let's add it back into the mode.

7
00:00:33,450 --> 00:00:45,060
We'll do set session and then we do SQL mode equals and then I'll say only full group VI.

8
00:00:47,400 --> 00:00:49,320
So this should be enabled by default.

9
00:00:49,320 --> 00:00:53,340
But because in the previous video I disabled everything, I had to add it back in.

10
00:00:54,060 --> 00:00:58,170
And now it's the only mode that's enabled session, at least globally.

11
00:00:58,170 --> 00:00:59,060
Different story.

12
00:00:59,070 --> 00:01:05,610
So if we were to write a query that involves a group by like if I select from the series table, let's

13
00:01:05,610 --> 00:01:08,550
do title and join series with reviews.

14
00:01:08,550 --> 00:01:21,330
So title and rating from series Join with reviews on where the reviews dot series ID equal to the series

15
00:01:21,330 --> 00:01:22,080
dot ID.

16
00:01:22,470 --> 00:01:25,080
All right, so we know what this query does.

17
00:01:25,080 --> 00:01:29,880
It's not a group buy nothing fancy looks good.

18
00:01:29,880 --> 00:01:31,700
But now I'm going to do a group by right.

19
00:01:31,710 --> 00:01:39,060
I'm going to group buy and then I'll group by title and then let's get the average rating.

20
00:01:40,320 --> 00:01:40,950
OC.

21
00:01:43,530 --> 00:01:44,550
And there we are.

22
00:01:44,580 --> 00:01:48,060
We see what we expect the title and its average rating.

23
00:01:49,110 --> 00:01:56,670
So what full group I are only for group I says is that I'm only allowed to reference or to select columns

24
00:01:56,670 --> 00:02:04,590
that are either aggregate function columns like average counts or columns that I have already named

25
00:02:04,590 --> 00:02:09,300
in the group by clause, like I'm grouping by title, therefore I can select title.

26
00:02:09,630 --> 00:02:15,660
But if I wanted to just include rating, remember we talked about this when I first introduced group

27
00:02:15,660 --> 00:02:22,620
by my SQL, doesn't know what rating I'm referring to necessarily because we've grouped by title.

28
00:02:22,860 --> 00:02:24,210
So which rating?

29
00:02:24,210 --> 00:02:26,460
There's maybe five different ratings.

30
00:02:26,460 --> 00:02:27,930
So if I run this.

31
00:02:29,490 --> 00:02:30,510
I get an error.

32
00:02:30,540 --> 00:02:33,540
It says expression number two of your select list, which is rating.

33
00:02:33,690 --> 00:02:34,290
This is one.

34
00:02:34,290 --> 00:02:39,500
That's two is not in the group by clause and contains a non aggregate column.

35
00:02:39,510 --> 00:02:44,820
So we broke the rules of this SQL mode only full group by mode.

36
00:02:44,940 --> 00:02:50,400
So if I instead change that SQL mode, I'll just set it to an empty string.

37
00:02:52,050 --> 00:02:54,020
Only four group Bye is gone now.

38
00:02:54,030 --> 00:02:56,280
Our session SQL mode is just an empty string.

39
00:02:56,370 --> 00:03:01,110
If I rerun this query here, it works.

40
00:03:01,110 --> 00:03:04,910
But it is confusing because what is this really showing me?

41
00:03:04,920 --> 00:03:08,820
I think it's just taking the first result from each group, the first rating.

42
00:03:08,820 --> 00:03:10,500
But this is not averaging.

43
00:03:10,500 --> 00:03:16,830
I'm grouping by title, but I'm looking at one rating from each group, which is weird.

44
00:03:17,370 --> 00:03:22,020
So that is the same query that gave me an error before and did not work.

45
00:03:22,110 --> 00:03:24,210
Let's just re-enable that one more time.

46
00:03:24,300 --> 00:03:26,370
Only full group buys.

47
00:03:27,500 --> 00:03:28,490
Only for group buy.

48
00:03:28,490 --> 00:03:32,060
I think it is run that query and now it gives us an error.

49
00:03:32,060 --> 00:03:39,860
We have to use some sort of aggregate function or we have to select columns that are part of the group

50
00:03:39,860 --> 00:03:40,370
by clause.

51
00:03:40,370 --> 00:03:43,190
That's why we can get title because we're grouping by title.

52
00:03:43,190 --> 00:03:47,000
But rating is not an aggregate column and it's not part of the group buy.

53
00:03:47,000 --> 00:03:53,960
So we get an error and that's because of this mode only full group by a couple of others that we should

54
00:03:53,960 --> 00:03:55,970
highlight that are enabled by default.

55
00:03:55,970 --> 00:03:57,380
Let's look at the global mode.

56
00:03:57,380 --> 00:04:02,420
So select at at global is it SQL mode?

57
00:04:03,350 --> 00:04:07,730
So there's only four group by strict trans tables error four divided by zero.

58
00:04:07,730 --> 00:04:10,040
But then we've got these two No zero and date.

59
00:04:10,040 --> 00:04:11,390
No zero date.

60
00:04:11,420 --> 00:04:14,390
These have to do with well, zeros in dates.

61
00:04:14,390 --> 00:04:15,830
You can read about them here.

62
00:04:16,070 --> 00:04:22,100
If we look at no zero in date, it affects whether a date like this is valid.

63
00:04:22,100 --> 00:04:29,030
Where we have a year, 2010 we have a month, but then the day is zero zero, which I guess you could

64
00:04:29,030 --> 00:04:30,770
use to represent no day.

65
00:04:30,770 --> 00:04:32,810
It's just a year and a month.

66
00:04:33,530 --> 00:04:34,340
Same thing here.

67
00:04:34,340 --> 00:04:41,270
2010 0001 There's no month present, so I don't know if it's worth even showing you examples of this

68
00:04:41,270 --> 00:04:46,100
just for the sake of time, but that is what this mode controls.

69
00:04:46,100 --> 00:04:52,910
If it is not enabled, then you can have zero parts in your dates and if it is enabled, it won't allow

70
00:04:52,910 --> 00:04:55,010
you to have those zero parts in a date.

71
00:04:55,010 --> 00:05:00,650
So if I tried to do do we have any date columns, I don't think we actually do right now.

72
00:05:01,580 --> 00:05:03,710
So that's not going to be very helpful.

73
00:05:04,340 --> 00:05:12,440
Let's do select date and then I'll provide a string like 2010 0100.

74
00:05:12,950 --> 00:05:14,150
It let me do this.

75
00:05:14,150 --> 00:05:14,810
No problem.

76
00:05:14,810 --> 00:05:19,940
I'm just calling the date function and providing this string because once again, we look at our SQL

77
00:05:19,940 --> 00:05:22,430
mode, we don't have anything in there.

78
00:05:22,820 --> 00:05:24,860
I think we only have full group buys.

79
00:05:25,070 --> 00:05:26,870
If I instead add it in.

80
00:05:26,870 --> 00:05:28,400
Let's just verify it's not in there.

81
00:05:28,400 --> 00:05:31,850
Select Session dot SQL mode.

82
00:05:31,880 --> 00:05:33,230
That's all we have in there.

83
00:05:33,230 --> 00:05:41,300
So I'm going to set session SQL mode to be equal to and then I can't remember the exact name of it.

84
00:05:41,300 --> 00:05:41,510
Yeah.

85
00:05:41,540 --> 00:05:42,920
Ngozi wrote in date.

86
00:05:46,180 --> 00:05:50,930
Now, if I try and do this query right, where do you go?

87
00:05:50,950 --> 00:05:52,180
I think I went past it.

88
00:05:52,660 --> 00:05:55,420
Select date where we have a00 in the date.

89
00:05:55,450 --> 00:05:57,000
This time we get a warning.

90
00:05:57,010 --> 00:05:58,480
Our result is null.

91
00:05:58,480 --> 00:06:00,250
And if I show warnings.

92
00:06:01,300 --> 00:06:03,010
Oh, I need a semicolon there.

93
00:06:03,040 --> 00:06:03,910
Try that again.

94
00:06:05,370 --> 00:06:09,430
We see I'm getting the warning that I just generated there.

95
00:06:09,450 --> 00:06:10,590
We'll try that again.

96
00:06:10,740 --> 00:06:12,120
Let's run this query.

97
00:06:12,120 --> 00:06:16,980
Select date now, show warnings, incorrect date, time value.

98
00:06:16,980 --> 00:06:20,280
And that's because of this mode that I enabled.

99
00:06:20,280 --> 00:06:20,940
Previously.

100
00:06:20,940 --> 00:06:24,600
It was not enabled and so we had no trouble whatsoever.

101
00:06:24,750 --> 00:06:29,640
Now, if we go back to the docs and re re reread what it says about how this mode works.

102
00:06:29,940 --> 00:06:32,830
It says what we already know if the mode is not enabled.

103
00:06:32,850 --> 00:06:34,380
Zero dates are permitted.

104
00:06:34,380 --> 00:06:36,350
If it is enabled, you'll get a warning.

105
00:06:36,360 --> 00:06:42,030
But then there's a third piece that says if this mode and strict mode are enabled, then you'll get

106
00:06:42,030 --> 00:06:43,410
a full blown error.

107
00:06:43,650 --> 00:06:45,810
So what is strict mode?

108
00:06:46,140 --> 00:06:53,940
Well, strict mode is going to be enabled if we have either of these two modes enabled strict all tables

109
00:06:53,940 --> 00:06:58,710
or strict trans tables and strict trans tables is enabled by default.

110
00:06:58,920 --> 00:07:05,420
Currently, I actually turned it off because I set the SQL mode just to be only node zero in date.

111
00:07:05,430 --> 00:07:10,370
So this means I'm getting a slightly different behavior than I would if I had strict mode enabled.

112
00:07:10,380 --> 00:07:16,770
So strict mode also changes what happens with some of the other modes like we see right here.

113
00:07:17,070 --> 00:07:18,390
So I'll show an example of this.

114
00:07:18,390 --> 00:07:25,410
If I create a table, let's just have a very simple table called dates and it has a single column.

115
00:07:25,440 --> 00:07:28,800
Oh, where'd you go create table dates?

116
00:07:28,800 --> 00:07:31,470
It has a single column DX of type date.

117
00:07:32,160 --> 00:07:38,210
If I insert into dates and then I insert dx and dx.

118
00:07:38,430 --> 00:07:41,640
Dear Lord, insert insert values.

119
00:07:41,640 --> 00:07:47,400
And then in here we'll do something like 2023 dash, I don't know, 11 zero zero.

120
00:07:47,400 --> 00:07:49,260
That is a date with zero in it.

121
00:07:50,010 --> 00:07:57,840
I get a warning right Show warnings OC warning if I select star from dates.

122
00:07:58,350 --> 00:08:03,030
It did go in there as a full zero date, which is the anticipated behavior.

123
00:08:03,030 --> 00:08:05,520
It says produce a warning insert 0000.

124
00:08:06,000 --> 00:08:11,970
But if I enable strict mode which is enabled, as soon as I add in strict, I keep doing a space that

125
00:08:11,970 --> 00:08:14,400
are strict trans tables.

126
00:08:16,020 --> 00:08:22,800
Now, if I rerun this query to insert, I get an error where we have enabled strict mode and strict

127
00:08:22,800 --> 00:08:28,050
trans tables governs the behavior for invalid inserts and updates.

128
00:08:28,050 --> 00:08:32,820
What happens if we have a data validation that says this has to be a date but you provide an incorrect

129
00:08:32,820 --> 00:08:33,309
date?

130
00:08:33,330 --> 00:08:38,789
Well, long story short, it modifies other modes like Node zero and Date.

131
00:08:38,789 --> 00:08:41,970
So that is important, not just because of this mode.

132
00:08:41,970 --> 00:08:47,160
This is not that common if an issue to have zero dates, but also because if you're reading the docs

133
00:08:47,160 --> 00:08:52,710
for some of these other modes, you might see it say something about if strict mode is enabled versus

134
00:08:52,710 --> 00:08:57,900
not enabled, this will behave this way or this other way anyway.

135
00:08:57,960 --> 00:09:02,880
That is it for modes I'm going to go ahead and reset.

136
00:09:03,060 --> 00:09:06,180
The easiest way would just be to quit this session entirely.

137
00:09:06,360 --> 00:09:16,950
Just quit and then log back in my SQL dash you I'll log in root dash p, put my password in there and

138
00:09:16,950 --> 00:09:25,260
now if I do a select session SQL mode, everything has been restored.

139
00:09:25,260 --> 00:09:30,880
But the other option as we saw, is just take that string and whatever pieces you want, whatever which

140
00:09:30,960 --> 00:09:36,930
which of these settings you want to include, then you can do set session, SQL mode, or you could

141
00:09:36,930 --> 00:09:38,310
even change the global mode.

142
00:09:38,310 --> 00:09:43,620
But I usually don't mess with that if I'm just teaching this stuff because there's a reason these are

143
00:09:43,620 --> 00:09:44,850
enabled by default.

144
00:09:45,210 --> 00:09:45,540
All right.

145
00:09:45,540 --> 00:09:47,250
So that's the discussion around modes.

146
00:09:47,250 --> 00:09:48,480
Very exciting stuff.

147
00:09:48,480 --> 00:09:49,020
I know.