MySQL Bugs: #13480: Query Results depends on the fields retrieved in a query having 2 views

Bug #13480	Query Results depends on the fields retrieved in a query having 2 views
Submitted:	26 Sep 2005 11:56	Modified:	26 Sep 2005 14:59
Reporter:	ANGEL MARTIN	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.12	OS:	Windows (WINDOWS XP)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
I've got a query with three tables. Two of the tables are using a view. The result of the query (number of results returned) dependes on the fields that are retrieved in the query.

I'll show you the sentences, the results of the sentences, and create table and create view of the tables involved at the queries.

SENTENCES:
--------------

SELECT ABNAME,ABTELF,RAFILA,RALOCA,RADESC,RADATR,RADATE,RAUSER,RAFLG1,RAFLG2,RADTOV,RAPDTO,PVIMPO,RACOPR,ABCABO
  FROM PFRSVAB,PFABONA,PFPREEV
 WHERE RATIPO='AP' AND RACICL='9999999' AND RAFUNC='MYSQL1 ' AND RAZONA='AA1' AND ABCABO=RAABON
   AND RADATI IS NULL AND PVRCTO=RATIPO AND PVCICL=RACICL AND PVFUNC=RAFUNC AND PVZONA=RAZONA
   AND PVCOTA=RACOTA AND PVCOPR=RACOPR
 ORDER BY ABNAME,RAFILA,RALOCA;

SELECT ABNAME,ABTELF,RAFILA,RALOCA,RADESC,RADATR,RADATE,RAUSER,RAFLG1,RAFLG2,RADTOV,RAPDTO,PVIMPO,RACOPR,ABCABO
  FROM MYSRSVAB,MYSABONA,PFPREEV
 WHERE RATIPO='AP' AND RACICL='9999999' AND RAFUNC='MYSQL1 ' AND RAZONA='AA1' AND ABCABO=RAABON
   AND RADATI IS NULL AND PVRCTO=RATIPO AND PVCICL=RACICL AND PVFUNC=RAFUNC AND PVZONA=RAZONA
   AND PVCOTA=RACOTA AND PVCOPR=RACOPR
 ORDER BY ABNAME,RAFILA,RALOCA;

SELECT ABNAME,ABTELF,RAFILA,RALOCA,RADESC,RADATR,RADATE,RAUSER,RAFLG1,RAFLG2,RADTOV,RAPDTO,PVIMPO,RACOPR,ABCABO
  FROM MYSRSVAB,MYSABONA,PFPREEV
 WHERE RATIPO='AP' AND RACICL='9999999' AND RAFUNC='MYSQL1 ' AND RAZONA='AA1' AND ABCABO=RAABON
   AND RADATI IS NULL AND PVRCTO=RATIPO AND PVCICL=RACICL AND PVFUNC=RAFUNC AND PVZONA=RAZONA
   AND PVCOTA=RACOTA
 ORDER BY ABNAME,RAFILA,RALOCA;

SELECT ABNAME,ABTELF,RAFILA,RALOCA,RADESC,RADATR,RADATE,RAUSER,RAFLG1,RAFLG2,RADTOV,RAPDTO,PVIMPO,RACOPR,RACOTA,PVCOTA,PVCOPR,ABCABO
  FROM MYSRSVAB A,MYSABONA,PFPREEV
 WHERE RATIPO='AP' AND RACICL='9999999' AND RAFUNC='MYSQL1 ' AND RAZONA='AA1' AND ABCABO=RAABON
   AND RADATI IS NULL AND PVRCTO=RATIPO AND PVCICL=RACICL AND PVFUNC=RAFUNC AND PVZONA=RAZONA
   AND PVCOTA=RACOTA
 ORDER BY ABNAME,RAFILA,RALOCA;

RESULTS OF THE QUERIES (IN ORDER OF THE PREVIOUS SENTENCES)
-----------------------------------------------------------------------------------
ABNAME           ABTELF RAFILA RALOCA RADESC RADATR     RADATE     RAUSER     RAFLG1 RAFLG2 RADTOV RAPDTO PVIMPO RACOPR ABCABO 
---------------- ------ ------ ------ ------ ---------- ---------- ---------- ------ ------ ------ ------ ------ ------ ------ 
ICSOFTWARE MYSQL        3      1             2005-09-26 2005-09-28 MYSQL      S      S      99     0      1500   1      119    
ICSOFTWARE MYSQL        3      2             2005-09-26 2005-09-28 MYSQL      S      S      99     0      1500   1      119    
ICSOFTWARE MYSQL        3      3             2005-09-26 2005-09-28 MYSQL      S      S      99     0      1500   1      119    
ICSOFTWARE MYSQL        3      4             2005-09-26 2005-09-28 MYSQL      S      S      99     0      1500   1      119    
 
4 Row(s) affected 
 

ABNAME ABTELF RAFILA RALOCA RADESC RADATR     RADATE     RAUSER RAFLG1 RAFLG2 RADTOV RAPDTO PVIMPO RACOPR ABCABO 
------ ------ ------ ------ ------ ---------- ---------- ------ ------ ------ ------ ------ ------ ------ ------ 
 
0 Row(s) affected 
 

ABNAME           ABTELF RAFILA RALOCA RADESC RADATR     RADATE     RAUSER     RAFLG1 RAFLG2 RADTOV RAPDTO PVIMPO RACOPR ABCABO 
---------------- ------ ------ ------ ------ ---------- ---------- ---------- ------ ------ ------ ------ ------ ------ ------ 
ICSOFTWARE MYSQL        3      1             2005-09-26 2005-09-28 MYSQL      S      S      99     0      1200   1      119    
ICSOFTWARE MYSQL        3      1             2005-09-26 2005-09-28 MYSQL      S      S      99     0      1500   1      119    
ICSOFTWARE MYSQL        3      1             2005-09-26 2005-09-28 MYSQL      S      S      99     0      1000   1      119    
ICSOFTWARE MYSQL        3      2             2005-09-26 2005-09-28 MYSQL      S      S      99     0      1200   1      119    
ICSOFTWARE MYSQL        3      2             2005-09-26 2005-09-28 MYSQL      S      S      99     0      1500   1      119    
ICSOFTWARE MYSQL        3      2             2005-09-26 2005-09-28 MYSQL      S      S      99     0      1000   1      119    
ICSOFTWARE MYSQL        3      3             2005-09-26 2005-09-28 MYSQL      S      S      99     0      1500   1      119    
ICSOFTWARE MYSQL        3      3             2005-09-26 2005-09-28 MYSQL      S      S      99     0      1000   1      119    
ICSOFTWARE MYSQL        3      3             2005-09-26 2005-09-28 MYSQL      S      S      99     0      1200   1      119    
ICSOFTWARE MYSQL        3      4             2005-09-26 2005-09-28 MYSQL      S      S      99     0      1500   1      119    
ICSOFTWARE MYSQL        3      4             2005-09-26 2005-09-28 MYSQL      S      S      99     0      1000   1      119    
ICSOFTWARE MYSQL        3      4             2005-09-26 2005-09-28 MYSQL      S      S      99     0      1200   1      119    
 
12 Row(s) affected 
 

ABNAME ABTELF RAFILA RALOCA RADESC RADATR     RADATE     RAUSER RAFLG1 RAFLG2 RADTOV RAPDTO PVIMPO RACOPR RACOTA PVCOTA PVCOPR ABCABO 
------ ------ ------ ------ ------ ---------- ---------- ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ 
 
0 Row(s) affected 
 

The problem is at the sentencies 2 and 4. The 2 sentence have two views MYSABONA and MYSRSVAB. The same sentencies (number 1) without views return the correct result.
The sentence 4 has no results compared to sentence 3, even both of them have views, but sentence 4 has 3 more fields compared to sentence 3.

I've got on my computer a sqldump of these three tables, if you need them to check the problem.

Thanks in advance for tour cooperation and help.
Best regards

ANGEL MARTIN

How to repeat:
Every time I execute the sentencies.

Dump of the table pfabona

Attachment: pfabona.zip (application/zip, text), 5.80 KiB.

dump of the table pfpreev

Attachment: pfpreev.zip (application/zip, text), 196.70 KiB.

dump of the table pfrsvab

Attachment: pfrsvab.zip (application/zip, text), 26.54 KiB.

Sentences to test the problem

Attachment: SENTENCES.TXT (text/plain), 2.78 KiB.

Sentences results of the problem

Attachment: SENTENCES_RESULTS.TXT (text/plain), 9.66 KiB.

views of the tables

Attachment: VIEWS.TXT (text/plain), 8.43 KiB.

Necessary table for the view

Attachment: pflradm.zip (application/zip, text), 1.12 KiB.

I was unable to create the view due to error:
ERROR 1146 (42S02): Table 'v63.pflradm' doesn't exist

I've already sent to you the table PFLRADM.

Sorry, becuase I forgot to sent it in the original message.

If you need further information about the problem, please let my know.

Best regards

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Thank you for the feedback and bug report I was able to repeat
with server 5.0.12, but testing with a server built from source
the issue not exist anymore:
c:\mysql\bin>mysql -uroot v63
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.14-rc-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT
    -> ABNAME,ABTELF,RAFILA,RALOCA,RADESC,RADATR,RADATE,RAUSER,RAFLG1,RAFLG2,
    -> RADTOV,RAPDTO,PVIMPO,RACOPR,ABCABO
    -> FROM PFRSVAB,PFABONA,PFPREEV
    -> WHERE RATIPO='AP' AND RACICL='9999999' AND RAFUNC='MYSQL1 ' AND RAZONA='AA1'
    -> AND ABCABO=RAABON
    -> AND RADATI IS NULL AND PVRCTO=RATIPO AND PVCICL=RACICL AND PVFUNC=RAFUNC AND
    -> PVZONA=RAZONA
    -> AND PVCOTA=RACOTA AND PVCOPR=RACOPR
    -> ORDER BY ABNAME,RAFILA,RALOCA;

4 rows in set (0.33 sec)

mysql>
mysql> SELECT
    -> ABNAME,ABTELF,RAFILA,RALOCA,RADESC,RADATR,RADATE,RAUSER,RAFLG1,RAFLG2,RADTOV,
    -> RAPDTO,PVIMPO,RACOPR,ABCABO
    -> FROM MYSRSVAB,MYSABONA,PFPREEV
    -> WHERE RATIPO='AP' AND RACICL='9999999' AND RAFUNC='MYSQL1 ' AND RAZONA='AA1'
    -> AND ABCABO=RAABON
    -> AND RADATI IS NULL AND PVRCTO=RATIPO AND PVCICL=RACICL AND PVFUNC=RAFUNC AND
    -> PVZONA=RAZONA
    -> AND PVCOTA=RACOTA AND PVCOPR=RACOPR
    -> ORDER BY ABNAME,RAFILA,RALOCA;
+------------------+--------+--------+--------+--------+------------+------------+------------+--------+--------+--------+--------+--------+--------+--------+
| ABNAME           | ABTELF | RAFILA | RALOCA | RADESC | RADATR     | RADATE     | RAUSER     | RAFLG1 | RAFLG2 | RADTOV | RAPDTO | PVIMPO | RACOPR | ABCABO |
+------------------+--------+--------+--------+--------+------------+------------+------------+--------+--------+--------+--------+--------+--------+--------+
| ICSOFTWARE MYSQL |        |      3 |      1 |        | 2005-09-26 | 2005-09-28 | MYSQL      | S      | S      | 99     |      0 |   1500 | 1      | 119    |
| ICSOFTWARE MYSQL |        |      3 |      2 |        | 2005-09-26 | 2005-09-28 | MYSQL      | S      | S      | 99     |      0 |   1500 | 1      | 119    |
| ICSOFTWARE MYSQL |        |      3 |      3 |        | 2005-09-26 | 2005-09-28 | MYSQL      | S      | S      | 99     |      0 |   1500 | 1      | 119    |
| ICSOFTWARE MYSQL |        |      3 |      4 |        | 2005-09-26 | 2005-09-28 | MYSQL      | S      | S      | 99     |      0 |   1500 | 1      | 119    |
+------------------+--------+--------+--------+--------+------------+------------+------------+--------+--------+--------+--------+--------+--------+--------+
4 rows in set, 2 warnings (0.01 sec)

mysql>
mysql> SELECT
    -> ABNAME,ABTELF,RAFILA,RALOCA,RADESC,RADATR,RADATE,RAUSER,RAFLG1,RAFLG2,RADTOV,
    -> RAPDTO,PVIMPO,RACOPR,ABCABO
    -> FROM MYSRSVAB,MYSABONA,PFPREEV
    -> WHERE RATIPO='AP' AND RACICL='9999999' AND RAFUNC='MYSQL1 ' AND RAZONA='AA1'
    -> AND ABCABO=RAABON
    ->    AND RADATI IS NULL AND PVRCTO=RATIPO AND PVCICL=RACICL AND PVFUNC=RAFUNC AND
    -> PVZONA=RAZONA
    ->    AND PVCOTA=RACOTA
    ->  ORDER BY ABNAME,RAFILA,RALOCA;

12 rows in set, 2 warnings (0.00 sec)

mysql>
mysql> SELECT
    -> ABNAME,ABTELF,RAFILA,RALOCA,RADESC,RADATR,RADATE,RAUSER,RAFLG1,RAFLG2,RADTOV,
    -> RAPDTO,PVIMPO,RACOPR,RACOTA,PVCOTA,PVCOPR,ABCABO
    ->   FROM MYSRSVAB A,MYSABONA,PFPREEV
    ->  WHERE RATIPO='AP' AND RACICL='9999999' AND RAFUNC='MYSQL1 ' AND RAZONA='AA1'
    -> AND ABCABO=RAABON
    ->    AND RADATI IS NULL AND PVRCTO=RATIPO AND PVCICL=RACICL AND PVFUNC=RAFUNC AND
    -> PVZONA=RAZONA
    ->    AND PVCOTA=RACOTA
    ->  ORDER BY ABNAME,RAFILA,RALOCA;
+------------------+--------+--------+--------+--------+------------+------------+------------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
| ABNAME           | ABTELF | RAFILA | RALOCA | RADESC | RADATR     | RADATE     | RAUSER     | RAFLG1 | RAFLG2 | RADTOV | RAPDTO | PVIMPO | RACOPR | RACOTA | PVCOTA | PVCOPR | ABCABO |
+------------------+--------+--------+--------+--------+------------+------------+------------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
| ICSOFTWARE MYSQL |        |      3 |      1 |        | 2005-09-26 | 2005-09-28 | MYSQL      | S      | S      | 99     |      0 |   1200 | 1      | 02     | 02     | 2      | 119    |
| ICSOFTWARE MYSQL |        |      3 |      1 |        | 2005-09-26 | 2005-09-28 | MYSQL      | S      | S      | 99     |      0 |   1500 | 1      | 02     | 02     | 1      | 119    |
| ICSOFTWARE MYSQL |        |      3 |      1 |        | 2005-09-26 | 2005-09-28 | MYSQL      | S      | S      | 99     |      0 |   1000 | 1      | 02     | 02     | 3      | 119    |
| ICSOFTWARE MYSQL |        |      3 |      2 |        | 2005-09-26 | 2005-09-28 | MYSQL      | S      | S      | 99     |      0 |   1500 | 1      | 02     | 02     | 1      | 119    |
| ICSOFTWARE MYSQL |        |      3 |      2 |        | 2005-09-26 | 2005-09-28 | MYSQL      | S      | S      | 99     |      0 |   1000 | 1      | 02     | 02     | 3      | 119    |
| ICSOFTWARE MYSQL |        |      3 |      2 |        | 2005-09-26 | 2005-09-28 | MYSQL      | S      | S      | 99     |      0 |   1200 | 1      | 02     | 02     | 2      | 119    |
| ICSOFTWARE MYSQL |        |      3 |      3 |        | 2005-09-26 | 2005-09-28 | MYSQL      | S      | S      | 99     |      0 |   1500 | 1      | 02     | 02     | 1      | 119    |
| ICSOFTWARE MYSQL |        |      3 |      3 |        | 2005-09-26 | 2005-09-28 | MYSQL      | S      | S      | 99     |      0 |   1000 | 1      | 02     | 02     | 3      | 119    |
| ICSOFTWARE MYSQL |        |      3 |      3 |        | 2005-09-26 | 2005-09-28 | MYSQL      | S      | S      | 99     |      0 |   1200 | 1      | 02     | 02     | 2      | 119    |
| ICSOFTWARE MYSQL |        |      3 |      4 |        | 2005-09-26 | 2005-09-28 | MYSQL      | S      | S      | 99     |      0 |   1500 | 1      | 02     | 02     | 1      | 119    |
| ICSOFTWARE MYSQL |        |      3 |      4 |        | 2005-09-26 | 2005-09-28 | MYSQL      | S      | S      | 99     |      0 |   1000 | 1      | 02     | 02     | 3      | 119    |
| ICSOFTWARE MYSQL |        |      3 |      4 |        | 2005-09-26 | 2005-09-28 | MYSQL      | S      | S      | 99     |      0 |   1200 | 1      | 02     | 02     | 2      | 119    |
+------------------+--------+--------+--------+--------+------------+------------+------------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
12 rows in set, 2 warnings (0.00 sec)