| Bug #12971 | Inconsistent data in a view | ||
|---|---|---|---|
| Submitted: | 3 Sep 2005 20:10 | Modified: | 7 Sep 2005 17:46 |
| Reporter: | Jakub Velkoborsky | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 5.0.12-beta | OS: | Linux (Linux, Windows) |
| Assigned to: | Evgeny Potemkin | CPU Architecture: | Any |
[4 Sep 2005 10:32]
Valeriy Kravchuk
I repeated this behaviour using the test case provided, also on Windows. The table used was the following:
create table Tkupon (
id_kupon smallint(5) unsigned primary key auto_increment,
kod char(12) unique not null,
castka double(6,2) not null,
id_user_pouzil smallint(5) unsigned,
cas_pouziti datetime,
cas_vydani datetime);
Compare the results of:
INSERT INTO Tkupon (kod,castka,cas_vydani) VALUES ('ASDFGHJKZXCV', 200,
NOW()); SELECT * FROM Tkupon_aktivni;
(newly inserted row is NOT visible in the select results) with the results of:
INSERT INTO Tkupon (kod,castka,cas_vydani) VALUES ('ASDFGHJKZXCY', 200,
NOW());SELECT * FROM Tkupon;
(newly inserted row IS visible). This is definitely a bug, and no restrictions like that are desacribed at http://dev.mysql.com/doc/mysql/en/view-restrictions.html
[7 Sep 2005 17:46]
Evgeny Potemkin
View Tkupon_aktivni have condition cas_vydani < NOW(). NOW() measures time in seconds. INSERT just before SELECT takes less than second, thus in INSERT and in SELECT FROM tkupon_activni NOW() have the same value. Because of this just inserted record isn't selected.
Description: If I insert data in a table form which a view is derived and try to get data from that view in the same line of query, the data returned are inconsitent. However next query returns correct result. Notice, that it doesn't happen, when those two queries are separated intoto lines. How to repeat: mysql> DELETE FROM Tkupon; Query OK, 1 row affected (0.00 sec) mysql> DESCRIBE Tkupon; +----------------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+----------------------+------+-----+---------+----------------+ | id_kupon | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | kod | char(12) | NO | UNI | | | | castka | double(6,2) | NO | | | | | id_user_pouzil | smallint(5) unsigned | YES | | NULL | | | cas_pouziti | datetime | YES | | NULL | | | cas_vydani | datetime | YES | | NULL | | +----------------+----------------------+------+-----+---------+----------------+ 6 rows in set (0.00 sec) mysql> CREATE OR REPLACE VIEW Tkupon_aktivni AS SELECT Tkupon.* FROM Tkupon WHERE (cas_vydani IS NOT NULL) AND (cas_vydani < NOW()) AND (id_user_pouzil IS NULL) AND (cas_pouziti IS NULL); Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO Tkupon (kod,castka,cas_vydani) VALUES ('ASDFGHJKZXCV', 200, NOW()); SELECT * FROM Tkupon_aktivni; Query OK, 1 row affected (0.00 sec) Empty set (0.00 sec) mysql> SELECT * FROM Tkupon_aktivni; +----------+--------------+--------+----------------+-------------+---------------------+ | id_kupon | kod | castka | id_user_pouzil | cas_pouziti | cas_vydani | +----------+--------------+--------+----------------+-------------+---------------------+ | 15 | ASDFGHJKZXCV | 200.00 | NULL | NULL | 2005-09-03 22:05:24 | +----------+--------------+--------+----------------+-------------+---------------------+ 1 row in set (0.00 sec) mysql> INSERT INTO Tkupon (kod,castka,cas_vydani) VALUES ('ASQW47589203', 200, NOW()); Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM Tkupon_aktivni; +----------+--------------+--------+----------------+-------------+---------------------+ | id_kupon | kod | castka | id_user_pouzil | cas_pouziti | cas_vydani | +----------+--------------+--------+----------------+-------------+---------------------+ | 15 | ASDFGHJKZXCV | 200.00 | NULL | NULL | 2005-09-03 22:05:24 | | 16 | ASQW47589203 | 200.00 | NULL | NULL | 2005-09-03 22:06:59 | +----------+--------------+--------+----------------+-------------+---------------------+ 2 rows in set (0.00 sec) mysql> SELECT VERSION(); +-----------------+ | VERSION() | +-----------------+ | 5.0.12-beta-log | +-----------------+ 1 row in set (0.00 sec)