Hello, my name is Pedro, I make you an engineer at Quick House.
I want to talk to you about a topic that probably could let us make it think for a while.
That's something it might not be easy, which is fuzzing databases.
I want to click house recently, but has been already used right and many things already happened.
I just don't want to ask first if any of you know Quick House project.
Okay, a few, okay, thanks.
For the you that don't know, Quick House is an open source, an open source database within C++.
It could not be very fast for an open code queries and aggregations.
You can see and can scale very well for one node to many to hundreds.
And you have customers running, oh sorry, running with the petabytes of data.
But what you want to focus here today is about testing databases.
Because they are not as easy as you might think about.
First, I want to just talk about fuzzer for a bit.
I've been doing mostly fuzzing in Quick House for this past few months.
And they have been at a certain topic, as you know, for all these years.
Many fields in computer science, inequality, but also databases.
And we had Quick House.
We also have been, of course, starting at a lot over the years.
With many fuzzers, with all kinds of strategies to find all kinds of issues in Quick House.
There are a few here.
A few of them probably already know, like the SQL answer, which was, you need to find the wrong results.
There are also, like, FAL with fuzzer that I know to do coverage testing, to try to find the paths and find the issues with that.
And the others, let's know that it's a diffuser that we've developed in Quick House ourselves.
And there's this new one, it's about for self-scalters house.
That it's about in the past few months that we was able to find lots of new issues.
And all these fuzzers, they are not perfect.
They can find some issues very easily, others not that easy.
And this is what the point of this presentation that there's not perfect fuzzer to find other issues in that database.
But why? Well, other databases are very complex.
They have to do lots of things from query, optimization and started.
They have to guarantee that data always always there.
So even if the crash happens and it starts everything must be there, running as smooth as possible.
And there are the many other things databases.
They are known to have many interface languages.
But SQL, which one is Clickhouse users, is the most known one.
And it's also quite extensive and it has some complexity in there.
And there are other things, if you run the fuzzer.
That's, we had to make sure that the state is kept between queries,
because that database has a catalog with tables, columns and data.
And you must know that if you are going to find something, you want to make sure to make sure that you are always targeting something.
And testing new code paths there.
And it makes more difficult databases.
They have to run for a very long time, like days of a year, that interruption.
And you also have to vary performance through time.
And it makes difficult to type to detect these things like in a more, I'll say, automated way through a fuzzer.
So what can we do to test database with a fuzzer?
Well, you can think about many things.
You can start to think about, okay, we have SQL.
We can try to generate SQL queries, but how many ways you can do this.
And also, it makes sure that we are fuzzing something that exists there in the database,
like catalog with tables and columns, et cetera, many types.
They are, they are many things that we have to consider.
And it makes difficult to make this, I can run them away with a fuzzer, because fuzzer is run the most away.
And also, as we cannot make it very strict, because the fuzzer makes very strict what can it generate.
You can reduce the mind of the impossible, you can target.
And the issue we could pursue is possibly fine.
And also, don't forget that not all the issues are just point crashes.
We had to find fine, find wrong results, performance issues, and et cetera.
So things are more difficult to find.
So there are many fuzzer out there that we tried, also, in quick, also, and we have running on our CI.
There are some micro-fuzzer that I told, that was testing.
But I think it's another slow to generate inputs, and it takes some time,
and then it takes some time, and we have to keep in track in mind.
And we have to keep it, it makes it a little slow.
But I want to just want to show here the SD fuzzer that we developed, by the quick house of yours back before I joined.
But it basically does, like it takes the inputs from queries, from clients, or a test case where you have from quick house.
And mutates the syntax 3 to create new combinations and possibly fine new issues.
So I have an example here, from the SD fuzzer, you create a table there.
You are running an client, and you fight the query.
And you'll simply start the limitations of the query.
You have here a select from the table, and you can see you can start adding a distinct clause.
You could change the work clause.
And now I'm not working towards one clause, or change the type of query to explain something like that.
It starts by mutating.
But this is a rather slow, because there's one fundamental reason that we have to give the input first to the fuzzer.
And then it starts doing its presentations, and try to find new new issues.
But you try it by itself by randomly, and also this is kind of slow that you will start from that input always.
And you will start mutating it.
You know, you know, you are not sure if this input is probably the right query to find the next book in quick house.
So, let's then go back to a little.
And let's try to design the fuzzer ourselves.
Well, because we are engineers and we do these things.
And I have such an example query here from the tps25, probably all of you know.
Just to give some works here.
We have a select with a from very many tables.
Then you have here a work clause with joining the tables.
Plus, we have some filtering calls that in dates.
And then you have a group by with the nine gates.
So we must follow the grouping rules, and then follow by the end.
You can see already here that you have considered here.
If you go completely randomly, we can get a lot of errors.
Like if you miss this diagram or rules.
And that's many of these fuzzer still fail on this.
So, let's then design one.
Let's then think about something.
I have a simple example here.
Okay, the most valuable one here.
You have a probability here.
For more probabilities, you can either create a table, insert into it, do it, alter or drop,
or run a query most of the time.
It's probably what you want.
This one's nice to start with.
But there's already a few issues here.
Probably you can already see if someone wants to try to give
or write a comment on this.
I don't know.
If not, I can already tell.
There are issues here.
First, we have a probability to add a drop statement at five percent.
So, at 20 statements, a drop statement will be generated.
Which means like a table, probably could not pass like more than 20 statements.
It's statements which is bad.
And also, you have to cut the complexity of a, or I think, quite a table.
That can be more complex with a drop, because it comes with many types.
And it can have expressions that are for constraints and everything.
It can be more complex to handle.
So, there's a chance that there will be many drops, successful ones.
And if you create, create ones instead.
And we probably end with no tables in the catalog, which is bad.
And that doesn't happen in real life.
And also, the reads could, many of the reads could be recomputedly.
And the inserts could be complex, because the inserts must follow the rules of the table.
And you can end up with empty tables most of the time,
which also doesn't happen a lot in real life.
So, it's already some issues on this.
And we have to think about in a way to avoid these issues.
And try to have a balance between, by this completely random,
and by more real life.
So, I think there was something missing on NLC either, and click also.
I can meet bus house to try to fix this gap here.
And this is what it does for now.
25, 4,000 lines of code, which probably is a bit much to a further.
Lots of issues found, more than 100, and lots of people busy fixing them.
Probably, it's already a good start.
And we start this for a few months.
And we're able to find many issues and click house.
So, what it does exactly?
I could try to show the demo here, but probably there's no time to do it.
So, I'm going to stick with the presentation.
You can find this with a book post, but I wrote about this.
So, the bus house in action just starts by creating some tables.
I want you a number to try to be a more random,
and try to create a more square task possible.
And then test regenerative queries, try to find the issues.
And make sure there's, I was in a number of tables in the catalog.
You want to keep out with the dropping them.
Make sure some stay for very long to make sure that we have something to test on.
Also, set the meat on the query size.
You don't want to run it out of manual access queries,
because the combinations are many.
There are lots of chances that the query is 100 anything useful.
And try to try to hack better in search to make sure that the tables have something.
And also, don't the session see it just,
if you want to do this to write it, because some of the issues are not deterministic.
And they are more difficult to produce.
I'm going to try manually, so sometimes we just run the session again.
So, what does house generates?
Well, this is simple for a larger query.
It's still not very constant to be serious for query five.
Okay, I have a few jokes here, because I also do that in a new JSON type,
that we have for regards.
And so we can have more than five years than the table, table column,
and then sub column in JSON.
So it makes a bit more complex.
These are other queries.
Sometimes I can also reduce some small ones.
But I can start here some more causal patterns,
like we try to find that to make sure all the columns use in the query,
use in the form course, so that we must correct as possible.
Try some time, also, for something like the group I boost,
make sure we have correct semantics.
So we have about to find more issues.
What have found?
Yeah, as I said, what have issues?
So issues, crashes, often, like semantics and faults,
and find the other things that are probably those the easiest to find.
There are also the logical errors, which are kind of the certifications,
including cows, or basically I can insertion,
like in a query plan or something else.
You must have certain then eight fails.
A few wrong results.
There are a few ways I'm going to talk in a minute,
how to detect them.
There were a few oil and kills,
and there are some issues.
It's kind of a case about memory management,
but that's not a big issue.
And of course, the queries that gets stuck forever,
never end, maybe.
Because there's a lot of text or bad loop that never ends.
They're also not at common, but also happens.
So what I do to find wrong results?
There are many ways, many ways, a few, I mean, to find them.
Some probably are more than the others.
There's something as simple as that, as dumping a table,
and read back again, and compare the content.
It sounds a bit too simple,
but I was able to find a lot of issues,
mostly in the data formats,
because we include houses, portals, data formats,
like parka, CSV, arrow, and all these formats.
And the other things I could run in a query article,
this was going to start at a few hours ago.
We can do something like, for example,
sweat, count, from a query, with a paddicate,
and compare it.
The number of rows returned by that query,
with a select sum, with a data paddicate,
and compare it, then the number of rows at the first query,
with the result of the sum of the second one,
was also able to find a few issues there.
There are more things,
more simple, like, running at a query,
different settings, like,
but probably it's been enabled or not,
enabled or disabled, external sorting,
things like that,
but also we have to find a few issues.
And there are probably this one,
that probably not my people,
to do think about it,
I compare the results in that database.
Compareding the other click-out versions,
it's probably the easier way,
because they have exactly the same SQL I write,
but if you want to start working with other databases,
like my SQL repository has,
it starts become a bit more difficult,
because the SQL language, as you know,
is not very fine in some ways,
and some results may not be the same between the databases,
and that's fine.
It's just all day design.
But how's this?
Okay, nice.
You have found a solution to click-out,
that's great.
But yeah, but it also has issues,
like, directly other versions.
And the first thing,
probably, as you can see from that,
as I was clearly,
that the combinations start to add a lot,
with the other lots of things.
Well, what click-out is, like,
created for large projects,
with support many things,
by supporting many types,
from integers, to strings,
to nested types, like arrays,
and key and vowels.
There are also now the new JSON type.
Oops, sorry for this.
This is, I...
Okay, sorry.
And one aspect of this.
This is a new JSON type,
which is, like, the next talk.
We won't have another room about it.
And yes, they have many things,
like, many functions and settings,
more than 1,000 of each,
easily.
And that's really stacked up.
And they also have many,
many, many table engines,
basically engines.
It's like, it finds all the table behaves.
The most common one,
basically, is like a table that
emerges huge parts of the time.
So basically,
we insert lots of
huge chunks of data into tables
and merge it over time.
That's the merge tree, in basic sense.
And there are some variations that
to do, like, some merge tree,
that to do, like, some aggregation combinations on it,
but I was for a waiter.
And also, not more things,
even, like, reading for my three,
and even, with table,
Mexico, that tables.
So you have a lot of things.
And, another more,
there are some other settings
that can tune in tables.
And you can also think about
the quick hours in a multi-note setup.
Like, you also spot the application of these things.
It becomes more complicated
if you want to first these things.
And,
both house,
then,
gets some issues from this.
Good combinations still,
many of the queries fail.
I'll say, like, more than a hundred percent easily.
The biggest issue here,
are these type combinations that are not checking them.
I'm comparing a string with an integer,
or with the top of something like that.
It's, yeah, it's error.
They cannot do that, obviously.
Yeah, I could do that checks for this,
but it becomes more complex,
even to two-handle.
And you can see the code by these already,
as some lines.
Also, if I want to do something like,
formance is also difficult.
You have to read tables,
to make sure, like,
using a lot of memory to make sure that,
you see, to find these formance issues.
But it becomes difficult,
because some queries can be completely random,
like, for sports,
and then, yeah, it's obviously, yeah.
And there's nothing you can do about it.
And then,
there could be some fast positives,
for articles.
For example,
I cannot imagine my,
to move some part of a query,
and that query could trigger the runtime error,
but the part could not move,
and, of course,
they give different results,
but success results,
but yeah, that has expected.
And there are other things that I,
so far, I don't change probabilities,
like, of the actions,
so maybe you can change these around time.
And also,
I'm already working the grammar of the queries,
so I cannot generate a query things,
or those updated queries,
that some of these more,
some of the,
or the first or two,
I quite see first or something.
But,
that is a very, quite good.
I already find many issues,
and kick out.
And as I said,
this is more like a for complement to it.
And,
both of these,
there are even more questions
that you can think about the first.
All about running clients in parallel,
like,
that they have hundreds of clients running at the same time.
How are going to synchronize the algorithm?
Like, how they are going to do?
You have said,
make sure that it doesn't scroll down the further,
and don't get out of computation between them.
What about fuzzing the server side?
Like, the way to start the server,
or clashing of starting it,
is another thing.
And if you have like more than one node,
it comes in more complex to think about.
Then there are other things,
like,
size of the tables,
and the queries,
or many columns,
so they have in a table like 1,000 columns.
Some customers have these many lifetables,
or lots of columns.
And then it's like,
I also check for their messages.
Sometimes they are a legitimate,
rigid method,
but it depends on the case.
It's difficult to track this.
And there's also a thing that you should think about,
like,
for like,
all on that table should say in the catalog,
because also probably you want to test another table with another combination.
That might be more likely to bring it issue.
So maybe you want to swap at some point,
but for a long,
if you don't know,
the other things that we can't find.
So,
so what's the conclusion?
The conclusion is obvious,
like,
I want to be able to find other issues,
like,
wanting a fuzzer.
There's still,
many things that you can think about there.
And not so fuzzers usually have the issue that,
yeah,
you find some of the issues,
and then after some time,
it stops finding new issues.
So we have to think about them,
and chasing over the time.
I could also add more features to my house,
to counter this,
but then the cause base becomes quite complex to handle this.
And I can tell you that fuzzers can also have issues on them,
and debugging fuzzers is also kind of weird experience,
because we see many queries running there.
And then there's something you've expected to generate,
but it never happens,
but you don't know,
maybe because it's very red to happen,
or really there's an issue that it never generates.
And it becomes a bit of nightmare to debug this.
And also,
yeah,
features,
we add more combinations,
and then,
let's likely,
to have queries succeed in,
or even,
the color case that has a bug,
it becomes a bit of responsibility to find,
because they are more,
the main is larger than there's more things to find.
So,
what's the solution?
Try to use as much as you can.
Like,
with fuzzers,
different techniques,
to test,
different invariants of other ways.
Try to find a way to,
like,
or calls to find more issues,
like,
I can do that at least before.
And,
you can try,
also try to share,
like,
because between fuzzers to another,
if they are,
it's in the same language as event easier.
For example,
you can use,
both the house,
part of the query generation in STF,
to upload the mutate, for example.
You can do things,
or use both house,
output,
for STF,
and rate it mutate,
and see if you can find anything else.
We can try to,
to,
our discriminations between them,
to, for even,
to increase your chances of finding.
So,
the solution,
I probably would like to,
to have,
is,
ever see,
I crowded with fuzzers,
right,
not.
We have all these different techniques here.
FAL,
or a fuzzer,
should I say
that you sometimes
read those things,
and if you please write them out,
like,
that's for example,
for sky'da.
C amigos meet, that's very non,
to messment whereresh lasted.
Also, something I have to dare,
Sico-holoster for correctness,
uch,
there's this one,
p-stress.
That is not probably Martinat,
not well known by these,
with non- patteredels,
also like,
But it's nice to have it running for hours,
hours and see what happens, like,
I think that you found after 20 hours, it's fun,
how to debug that, you know, is able to find that.
And then you have some other things,
others like SD Fuzzle and the house,
for now to fill this gap and up for a find,
other many issues.
We hope, but there will always be something
that you'll never find.
So what I talk to now, I post a book post,
on our company blog, like the last week,
guys something, I remember.
So we can always go check there,
and there's only sites that I write there.
Yes, I have all the posts about other Fuzzles,
and I am done.
So that's what I want to talk.
But before I leave, I just want to say that we
had because we people tonight,
you're going to have a small dinner.
Here are these address.
We invite everyone to join us.
I think there's going to be some snacks there,
like beer and waffles, as far as I know.
So you're only invited.
So see you there, you can also talk about anything
nerdy related, so it's always fun to be there.
So thank you, everyone.
Now we have time for questions, if you want.
Thank you.
Two questions, okay.
Yeah, anyone?
No, okay, one, one.
One of the questions, first time we had to select one.
Yes.
Three.
I do doing this crossing from your development process,
and it comes, I also like crossing new PRs that are coming in.
I'm doing a part of critical development.
Yes, we also have like new features being added.
Some of these features, I can add in them easily.
Some other problem, not that easy.
So you have pens, because criticals now,
yes, many people working on it.
So there are actually a few people doing QY.
So that's, yeah, usually keep the nerdy.
And my questions are there?
Yes, and I don't want you to talk about
the last thing, should we use one of these nightly films,
or if you do something that you'd like to see,
because it doesn't matter to question.
Because it doesn't matter to question.
Can we find that error that is publicly related to the mic?
How do you know that we're talking about to use that?
Yes, actually, I can tell, sorry.
Actually, I can tell, I have a discussion with my manager about this,
like a few months back, actually.
For me, I, for me, I would say,
more in the nightly field and write issues on the fly.
Actually, we can click on, for now, we have running up
as part of a monthly quest.
And then if we develop our finds in an issue,
first finds an issue, we create an issue for there,
anyone to look, but for me, you can start the pipeline, yes.
And see, because sometimes they don't find,
because they run for short time.
So sometimes you can be safe.
Most, I, I do.
No more questions, no more time.
No more time.
Thank you.