Repeatable in maria-5.3, maria-5.2.

The following query:

SELECT * FROM t1 JOIN t2 USING (f3);

returns rows even though there are no rows in the table for which t1.f3 = t2.f3 . All other forms of the query, that is SELECT without a * or ON instead of USING return correct results.

Also, it seems that the table needs to be populated with INSERT statements that also insert into the virtual column, such as those that are produced by mysqldump.

Test case:

CREATE TABLE t1 (
f3 int
);
INSERT INTO t1 VALUES (NULL),(78),(185),(0),(154);

CREATE TABLE t2 (
f1 int,
f3 int AS (f1) VIRTUAL
);

INSERT INTO t2 VALUES (187,187),(9,9),(187,187),(9,9);

SELECT * FROM t1 JOIN t2 USING (f3);

explain:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL 4
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using join buffer

bzr version-info
revision-id: <email address hidden>
date: 2011-07-04 04:32:53 +0300
build-date: 2011-07-05 19:49:40 +0300
revno: 2998
branch-nick: maria-5.2