| Bug #60459 | Using default values of table fields when we do a left join table. | ||
|---|---|---|---|
| Submitted: | 14 Mar 2011 12:07 | Modified: | 14 Mar 2011 16:50 |
| Reporter: | Jerzy Kruszyna-Kotulski | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Options | Severity: | S5 (Performance) |
| Version: | OS: | Linux (Ubuntu 10.04) | |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | default values, left join, using default values of fields | ||
[14 Mar 2011 12:37]
Valeriy Kravchuk
Sorry, but I do not follow you. I get the following results from your query:
mysql> select * from tests_definitions
-> inner join tests_status
-> on tests_definitions.test_id = tests_status.t
est_id
-> left join tests
-> on tests_status.test_number = tests.test_numb
er
-> where test_status = 1
-> ;
+---------+----------+--------------+-------------------------+-------------+---
------+-------------+----------+---------+--------------+-------------+
| test_id | point_id | name | data | test_number | te
st_id | test_status | point_id | answare | point_status | test_number |
+---------+----------+--------------+-------------------------+-------------+---
------+-------------+----------+---------+--------------+-------------+
| 1 | 1 | test point 1 | test point 1 dfgsdfgdfg | 1 |
1 | 1 | NULL | NULL | NULL | NULL |
| 1 | 2 | test point 2 | asdc sadf sadf sd | 1 |
1 | 1 | NULL | NULL | NULL | NULL |
| 1 | 3 | test point 3 | adsASD SADF SADF SD | 1 |
1 | 1 | NULL | NULL | NULL | NULL |
| 1 | 4 | test point 4 | SDAFG L.FNSDLFG DFG | 1 |
1 | 1 | NULL | NULL | NULL | NULL |
+---------+----------+--------------+-------------------------+-------------+---
------+-------------+----------+---------+--------------+-------------+
4 rows in set (0.06 sec)
What other result you expect or would like to get?
[14 Mar 2011 14:27]
Valeriy Kravchuk
I do not think that we need any new syntax for this. Consider the following simplified example: macbook-pro:5.5 openxs$ bin/mysql -uroot test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 Server version: 5.5.11-debug Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create table t1(c1 int default 0, c2 int default 1); Query OK, 0 rows affected (0.07 sec) mysql> create table t2(c1 int default 0, c2 int default 1); Query OK, 0 rows affected (0.13 sec) mysql> insert into t1 values(1,1); Query OK, 1 row affected (0.00 sec) mysql> select * from t1 left join t2 on t1.c1 = t2.c1; +------+------+------+------+ | c1 | c2 | c1 | c2 | +------+------+------+------+ | 1 | 1 | NULL | NULL | +------+------+------+------+ 1 row in set (0.00 sec) If you want to use default value of, say, column c2 from table t2 when it is NULL (as a result of left join), you can use a combination of IFNULL() and DEFAULT() functions: mysql> select t1.*, t2.c1, ifnull(t2.c2, default(t2.c2)) as t2c2 from t1 left join t2 on t1.c1 = t2.c1; +------+------+------+------+ | c1 | c2 | c1 | t2c2 | +------+------+------+------+ | 1 | 1 | NULL | 1 | +------+------+------+------+ 1 row in set (0.03 sec)
[14 Mar 2011 15:17]
Valeriy Kravchuk
Looks like it's time to ask: what exact version of MySQL server, x.y.z, do you use?
[14 Mar 2011 16:13]
Valeriy Kravchuk
Well, this is what I get:
mysql> SELECT tests_definitions.*,
-> tests_status.*,
-> ifnull(tests.point_status, 0 ) as point_status
-> FROM tests_definitions
-> INNER JOIN tests_status ON tests_definitions.test_id = tests_status.test_id
-> LEFT JOIN tests ON tests_status.test_number = tests.test_number
-> WHERE test_status =1
-> ;
+---------+----------+--------------+-------------------------+-------------+---------+-------------+--------------+
| test_id | point_id | name | data | test_number | test_id | test_status | point_status |
+---------+----------+--------------+-------------------------+-------------+---------+-------------+--------------+
| 1 | 1 | test point 1 | test point 1 dfgsdfgdfg | 1 | 1 | 1 | 0 |
| 1 | 2 | test point 2 | asdc sadf sadf sd | 1 | 1 | 1 | 0 |
| 1 | 3 | test point 3 | adsASD SADF SADF SD | 1 | 1 | 1 | 0 |
| 1 | 4 | test point 4 | SDAFG L.FNSDLFG DFG | 1 | 1 | 1 | 0 |
+---------+----------+--------------+-------------------------+-------------+---------+-------------+--------------+
4 rows in set (0.08 sec)
NULL is replaced by 0 as expected, because there is no row in tests that correspond to other table. Now, I add one:
mysql> insert into tests values(1,'aaa',1,1);
Query OK, 1 row affected (0.07 sec)
Note that it will correspond to all 4 tuples from other tables, thus the result:
mysql> SELECT tests_definitions.*, tests_status.*, ifnull(tests.point_status, 0 ) as point_status FROM tests_definitions INNER JOIN tests_status ON tests_definitions.test_id = tests_status.test_id LEFT JOIN tests ON tests_status.test_number = tests.test_number WHERE test_status =1;
+---------+----------+--------------+-------------------------+-------------+---------+-------------+--------------+
| test_id | point_id | name | data | test_number | test_id | test_status | point_status |
+---------+----------+--------------+-------------------------+-------------+---------+-------------+--------------+
| 1 | 1 | test point 1 | test point 1 dfgsdfgdfg | 1 | 1 | 1 | 1 |
| 1 | 2 | test point 2 | asdc sadf sadf sd | 1 | 1 | 1 | 1 |
| 1 | 3 | test point 3 | adsASD SADF SADF SD | 1 | 1 | 1 | 1 |
| 1 | 4 | test point 4 | SDAFG L.FNSDLFG DFG | 1 | 1 | 1 | 1 |
+---------+----------+--------------+-------------------------+-------------+---------+-------------+--------------+
4 rows in set (0.00 sec)
value 1 in every row. I do not see any bug here, sorry.
[14 Mar 2011 16:43]
Jerzy Kruszyna-Kotulski
You have right, I make a change in query to this one:
SELECT tests_definitions.*,
tests_status.*,
ifnull(tests.point_status, 0 ) as point_status
FROM tests_definitions
INNER JOIN tests_status
ON tests_definitions.test_id = tests_status.test_id
LEFT JOIN tests
ON tests_definitions.point_id = tests.point_id
WHERE test_status = 1
And now it's work like I need.
Brilliant wokr Valeriy, thanks a lot for your patience and time
Jerzy
ps. It's not a bug.
Description: I am on just finish my project, which is Test Manager. I have this table: Table 1: tests_definitions - which contain each points of test (test_id, point_id, name and description). Table 2: tests_status - contain test_number, test_id, test_status tests_status have 2 values: 0 - non activ test 1 - activ test Table 3: tests - which contain answares of tests and status fields:(point_id, answare, point_status, test_number). point_status have 3 values: 0 - non active (this value is set as default) 1 - done 2 - active I make a join of this tables: select * from tests_definitions inner join tests_status on tests_definitions.test_id = tests_status.test_id left join tests on tests_status.test_number = tests.test_number where test_status = 1 In answare of this query we have an issue. Description of issue: When we have 10 points of test and we start new test. In table tests_definitions we have 10 records with test points. In table tests_status we have one record with test_number and test_id. In table tests we have nothing (no records). Results of join query give as 10 records and each of records have null as a value in field point_status. But it will be very good if it have default value 0. Why this is so important? Becouse using this query I can get easy next test point, when tester give me first answere then I insert them in to tests table as a first record of 10, with point_status = 1. After first answare I ask database again and I will be grat when answare have still 10 records but first of them have point_status = 1 and rest null. But here I have issue. Rest 9 test_points have been overwritten values by first record, which is 1. With this results I don't know which test point are not done. I know I can insert the whole test in to a tests table and then issue dissapear, but when I don't want to make so many copies of the test in one moment, becouse some testers can left the test in half way, then I need something like my solution. It is also good and usefull when I ask db at the first time and the 'tests' table contain defaults value not all null. How to repeat: CREATE TABLE IF NOT EXISTS `tests` ( `point_id` int(5) NOT NULL default '0' COMMENT 'Test Number', `answare` char(255) NOT NULL COMMENT 'answare', `point_status` tinyint(1) NOT NULL default '0' COMMENT 'point status', `test_number` int(5) NOT NULL COMMENT 'Test Number', PRIMARY KEY (`point_id`,`test_number`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `tests_definitions` ( `test_id` int(5) NOT NULL COMMENT 'Test ID', `point_id` int(5) NOT NULL COMMENT 'Test point ID', `name` char(150) NOT NULL COMMENT 'name of test point', `data` char(255) NOT NULL COMMENT 'Description of test point' ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `tests_definitions` (`test_id`, `point_id`, `name`, `data`) VALUES (1, 1, 'test point 1', 'test point 1 dfgsdfgdfg'), (1, 2, 'test point 2', 'asdc sadf sadf sd'), (1, 3, 'test point 3', 'adsASD SADF SADF SD'), (1, 4, 'test point 4', 'SDAFG L.FNSDLFG DFG'); CREATE TABLE IF NOT EXISTS `tests_status` ( `test_number` int(5) unsigned NOT NULL auto_increment COMMENT 'Test Number', `test_id` int(5) NOT NULL COMMENT 'Test ID', `test_status` tinyint(1) NOT NULL COMMENT 'Test status', PRIMARY KEY (`test_number`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ; INSERT INTO `tests_status` (`test_number`, `test_id`, `test_status`) VALUES (1, 1, 1); select * from tests_definitions inner join tests_status on tests_definitions.test_id = tests_status.test_id left join tests on tests_status.test_number = tests.test_number where test_status = 1 Suggested fix: Just start using default values to left join querys :).