Why does the view and query differ in the results?

Question

I don’t understand why in the specified request only one record is returned from the view?
Given that the same code of this view returns two records with the same condition.

I strongly suspect that this is somehow related to the order of group by and where when using the view. I already had some similar problems here and here .

Structure:

articles(id, name)
authors(id, name)
jobs(id, name)
authors_connection(article_id, author_id, job_id)

links respectively article_id to the article table id author_id , author_id to authors.id , job_id to jobs.id

One article may have several authors. One author may have several places of work, but at the exit of the view there should be only one any. The view merges these separate tables into one authors table with one specified place of work.

why do you use grouping there at all if you do not have any aggregate functions?
This syntax probably works only in mysql, and in general does not determine which line will remain at the output, although probably the first of the group
The fact is that there may be several work places for one author, but I only need to take the first one.
somewhere you read from the documentation that the grouping is used in order to eliminate duplication and with its help you can get something first?
Duplication is excluded by other means, like extracting the first record (if there is any order).
@teran hmm, for some reason I always use group by to remove duplicates ... And what do you suggest?

Accepted Answer · 2018-06-28T09:09:55

Mysql violates the standard in terms of lax grouping.
In recent versions, it is also disabled by default.

Add all used fields to GROUP BY ( or remove GROUP BY altogether ) and get the same result (2 records) when prompted from View

 create view v_authors as select author_id as id, c.article_id, p.name, j.name as job_name from authors_connection c inner join articles a on a.id = c.article_id inner join authors p on p.id = c.author_id left join jobs j on j.id = c.job_id group by c.author_id, c.article_id, p.name, j.name; -- Тут

On the other hand, it is obvious that a query without view only works because the filter is applied before grouping .

Request:

 select * from( select author_id as id, c.article_id, p.name, j.name as job_name from authors_connection c inner join articles a on a.id = c.article_id inner join authors p on p.id = c.author_id left join jobs j on j.id = c.job_id group by c.author_id ) t where article_id = 3

also returns one record (most likely, it looks like UB).
Thus, it is necessary to take out the grouping from the view.
Or, in the process of grouping you will lose the data for which you are going to filter.

Comments are not intended for extended discussion; conversation moved to chat . - Yuriy SPb ♦

teran teran 18.1k one 13 thirty · Answer 2 · 2018-06-28T09:16:33

You should familiarize yourself with the @Mike answer regarding the use of group by

What happens in your requests.

If you run the query without where and group by :

 select author_id as id, c.article_id, p.name, j.name as job_name from authors_connection c inner join articles a on a.id = c.article_id inner join authors p on p.id = c.author_id left join jobs j on j.id = c.job_id;

then get a set of 4 lines: 3 articles of the first author and article # 3 of the second.

Further, when working with a view, you group by the author. As a result, you have 2 lines left. The second author remained there as he was with the third article, but the author’s group of the first one gives you an unspecified result. You had three lines of this author, and after grouping one remains. Which one of them remains - not defined, because grouping is generally done incorrectly. The first one left is, and this is probably a string with arcticle_id = 1 . Therefore, after filtering the view by article_id = 3 , the only line of the second author remained. If the stars are well positioned, this query may return 2 lines, if after the grouping there is not an arbitrary first line of the first author, but the third. The index will be there descending, or something else.

In the second case, of the full four lines, you first filter articles with id = 3 , leaving two lines of different authors. And now when grouping, all the same two different lines remain.

Yes, I wanted to say exactly about the location of the stars))

Answer 3 · 2018-06-28T10:15:48

There are almost no parameterized mappings in mysql.

However, there is a fairly well-known hack, with function and variable:

 create function param1() returns INTEGER NO SQL return @param1; create view v_authors as select author_id as id , c.article_id , min(p.name) name , min(j.name) as job_name from authors_connection c inner join articles a on a.id = c.article_id inner join authors p on p.id = c.author_id left join jobs j on j.id = c.job_id where c.article_id = param1() group by c.author_id, c.article_id;

Feeddle

Nichosé O_o Nefiga did not understand))))) What kind of magic is this?
It turns out that this function remembers the last assigned (???) result and always returns it?
Almost, the view depends on the function in which the current value of the variable is returned.
The flags in the function convince the engine to cache (? Hm) its result.
I am confused about DETERMINISTIC)) But it looks like it doesn’t affect anything here (mb, performance? To not access the variable for each line).

Why does the view and query differ in the results?

3 answers 3

More articles: