$20
Why does a database view give different results than the raw SQL?

Can anyone tell me why MySql would do this?

This statement:

select count(*) as total    

    -> FROM motorcar_content c

    -> LEFT JOIN motorcar_issue it on c.issue_date = it.issue_date

    -> LEFT JOIN motorcar_blogs b on c.reference_link_1 = b.blog_id

    -> LEFT JOIN motorcar_content_type d on c.content_type=d.id

    -> LEFT JOIN motorcar_author_xref a on (c.id = a.content_id  and c.state = a.state)

    -> ;

gives me 12913 rows

So I turn this into a view:

CREATE VIEW motorcar_author_content_issue_blogs AS

SELECT

  c.id, c.textid, c.title, c.title_link, c.content_date, c.issue_date,

  c.protected AS protect, c.allow_comments, c.show_comments, c.content_hint,

  c.author1, c.author2, c.author3, c.author4, c.author_str,

  c.subtitle, c.abstract AS motorcar_abstract, c.direct_url,

  c.imgpath1, c.imgpath2, c.imgpath3, c.imgpath4, c.imgpath5,

  c.imgcaption1, c.imgcaption2, c.imgcaption3, c.imgcaption4, c.imgcaption5,

  c.content_type, c.live_date, c.issue_toc_id,

  c.reference_link_1, c.alt_info_1,

  c.toc_title, c.toc_subtitle,

  c.promo_text_1, c.promo_text_2,

  c.parent_content_id,

  b.blog_name, b.blog_id,

  a.author_id, a.state, a.content_type AS content_t,

  it.issue_text_date,

  d.literal, d.alias, d.cdaobject, d.cdaaction, d.channel, d.adsite,

d.adzone, d.indexable

  FROM motorcar_content c

  LEFT JOIN motorcar_issue it on c.issue_date = it.issue_date

  LEFT JOIN motorcar_blogs b on c.reference_link_1 = b.blog_id

  LEFT JOIN motorcar_content_type d on c.content_type=d.id

  LEFT JOIN motorcar_author_xref a on (c.id = a.content_id  and c.state =

a.state)

then I check to be sure that the view has all of the data of the above JOIN
statement:

select count(*) as total from motorcar_author_content_issue_blogs;

total: 12913

Great. So everything is perfect right now. This is what I expect.

But check this: if I do this:

Select * from motorcar_author_content_issue_blogs

WHERE content_type=17

AND STATE='W'

ORDER BY CONTENT_DATE DESC ;

I get 1403 rows.

if I do the whole JOIN statement, I should get the identical result:

SELECT

  c.id, c.textid, c.title, c.title_link, c.content_date, c.issue_date,

  c.protected AS protect, c.allow_comments, c.show_comments, c.content_hint,

  c.author1, c.author2, c.author3, c.author4, c.author_str,

  c.subtitle, c.abstract AS motorcar_abstract, c.direct_url,

  c.imgpath1, c.imgpath2, c.imgpath3, c.imgpath4, c.imgpath5,

  c.imgcaption1, c.imgcaption2, c.imgcaption3, c.imgcaption4, c.imgcaption5,

  c.content_type, c.live_date, c.issue_toc_id,

  c.reference_link_1, c.alt_info_1,

  c.toc_title, c.toc_subtitle,

  c.promo_text_1, c.promo_text_2,

  c.parent_content_id,

  b.blog_name, b.blog_id,

  a.author_id, a.state, a.content_type AS content_t,

  it.issue_text_date,

  d.literal, d.alias, d.cdaobject, d.cdaaction, d.channel, d.adsite,

d.adzone, d.indexable

  FROM motorcar_content c

  LEFT JOIN motorcar_issue it on c.issue_date = it.issue_date

  LEFT JOIN motorcar_blogs b on c.reference_link_1 = b.blog_id

  LEFT JOIN motorcar_content_type d on c.content_type=d.id

  LEFT JOIN motorcar_author_xref a on (c.id = a.content_id  and c.state =

a.state)

  WHERE c.content_type=17

  AND c.STATE='W'

  ORDER BY CONTENT_DATE DESC

I get 2422 rows! Crazy.

Why am I getting 1403 rows from the view but 2422 rows with the complete JOIN statement, when both are suppose to be making the same JOINs and the WHERE clause is the same?

Anyone run into this before?

Lawrence Krubner | 05/09/10 at 10:47am | Edit

joins, sql, views

(4) Possible Answers Submitted...

Last edited:
05/15/10
9:32pm
Oleg Butuzov says:
View versus Select

Your tables MyIsam?
http://bugs.mysql.com/bug.php?id=18237

----------

Select versus Join.

actualy my english is pretty bad to explain this. just run test case that i do. you will understand why you getting different number of the results.

View Attachment

Previous versions of this answer: 05/10/10 at 5:10am | 05/10/10 at 8:36am
- 05/15/10 9:30pm
  
  Lawrence Krubner says:
  Oleg, I do not think I'm dealing with that bug, but I like your test code. Thanks.
Last edited:
05/15/10
9:32pm
Marco Einöder says:
Usually the result must not the same if you make:

- a JOIN clause and then apply the WHERE filter to the result of the join
- apply the WHERE filter to the main table first and then apply the JOIN clause

The sentence may seem the same, but the pharse logic implemented in the SQL pharser may obtain different results, depending how it evalutates your SQL sentences.

Using "(" and ")" may help some times tor obligate a determinate evaluation sequence.

applying the WHERE clause on the view, should make the JOIN first and then the WHERE filter
applying the WHERE clause direct in the view, should make the WHERE clause first and then the JOIN.
Last edited:
05/15/10
9:32pm
Jarret Minkler says:
Try changing it to ...

HAVING
c.content_type=17
AND c.STATE='W'

instead of where
- 05/13/10 6:36pm
  
  Lawrence Krubner says:
  No dice.
  
  This:
```
Select * from motorcar_author_content_issue_blogs having  content_type=17  AND STATE='W'  ORDER BY CONTENT_DATE DESC ;
```
  Gets me 1435 rows.
- 05/14/10 9:35am
  
  Jarret Minkler says:
- 05/14/10 9:36am
  
  Jarret Minkler says:
  I meant in the full version that gives you the extra rows, you are getting a Cartesian somewhere.
- 05/15/10 9:31pm
  
  Lawrence Krubner says:
  Ah, I think you are right. 4 Left Joins with only 4 constraints runs a risk of that.
Last edited:
05/13/10
7:26pm
meinoder says:
Is difficult to say why without having access to the data, but, another aproximation:
take a look to the first and the second result you get.
try to compare both, searching for the records which are in the second one and not in the first.
If you get records repeated, there is a problem in the join clauses you made, perhaps you don't have included all the keys and it makes a product from the tablerecords.
Look in the second result if there are repeated records, then the problem is not from the where clause, but from the joins which are wrong.