'select t1.* from t1'; select statements with wildcards on columns are not returning all records

Bug #309865 reported by Aarti Pai on 2008-12-20
2
Affects Status Importance Assigned to Milestone
Drizzle
Low
Stewart Smith

Bug Description

Refer to alias.test, line 176.
Select statement with wildcard on column is not returning all records.
The same results were generated when when the create table was changed to MyISAM.
Variations of the select statements are not returning all records. See example of select statement listing below.

create table t1 (a int, b int, c int);
create table t2 (d int);
create table t3 (a1 int, b1 int, c1 int);
insert into t1 values(1,2,3);
insert into t1 values(11,22,33);
insert into t2 values(99);

 select t1.* from t1;
 a b c
 1 2 3

drizzletest: Result content mismatch

 select t2.* from t2;
 d
 99

drizzletest: Result content mismatch

Variations of the select statement with wildcards in the column also fail to produce the correct results.
select a, t1.* from t1;
select t1.*, a from t1;
select a, t1.*, b from t1;
select (select d from t2 where d > a), t1.* from t1;
select t1.*, (select a from t2 where d > a) from t1;
select a as 'x', t1.* from t1;
select t1.*, a as 'x' from t1;
select a as 'x', t1.*, b as 'x' from t1;
select (select d from t2 where d > a) as 'x', t1.* from t1;
select t1.*, (select a from t2 where d > a) as 'x' from t1;

All of these produce 'drizzletest: Result content mismatch' and do not produce all the results.

Related branches

Jay Pipes (jaypipes) wrote :

This is not an error, but expected SQL behaviour.

Changed in drizzle:
importance: Undecided → Low
status: New → Invalid
Jay Pipes (jaypipes) wrote :
Download full text (3.6 KiB)

Here is verified behaviour, which is correct:

drizzle> use test
Database changed
drizzle> create table t1 (a int, b int, c int);
Query OK, 0 rows affected (0.00 sec)

drizzle> create table t2 (d int);
Query OK, 0 rows affected (0.01 sec)

drizzle> create table t3 (a1 int, b1 int, c1 int);
Query OK, 0 rows affected (0.01 sec)

drizzle> insert into t1 values(1,2,3);
Query OK, 1 row affected (0.00 sec)

drizzle> insert into t1 values(11,22,33);
Query OK, 1 row affected (0.00 sec)

drizzle> insert into t2 values(99);
Query OK, 1 row affected (0.00 sec)

drizzle>
drizzle> select t1.* from t1;
+------+------+------+
| a | b | c |
+------+------+------+
| 1 | 2 | 3 |
| 11 | 22 | 33 |
+------+------+------+
2 rows in set (0.00 sec)

drizzle> select a, t1.* from t1;
+------+------+------+------+
| a | a | b | c |
+------+------+------+------+
| 1 | 1 | 2 | 3 |
| 11 | 11 | 22 | 33 |
+------+------+------+------+
2 rows in set (0.00 sec)

drizzle> select t1.*, a from t1;
+------+------+------+------+
| a | b | c | a |
+------+------+------+------+
| 1 | 2 | 3 | 1 |
| 11 | 22 | 33 | 11 |
+------+------+------+------+
2 rows in set (0.00 sec)

drizzle> select a, t1.*, b from t1;
+------+------+------+------+------+
| a | a | b | c | b |
+------+------+------+------+------+
| 1 | 1 | 2 | 3 | 2 |
| 11 | 11 | 22 | 33 | 22 |
+------+------+------+------+------+
2 rows in set (0.00 sec)

drizzle> select (select d from t2 where d > a), t1.* from t1;
+--------------------------------+------+------+------+
| (select d from t2 where d > a) | a | b | c |
+--------------------------------+------+------+------+
| 99 | 1 | 2 | 3 |
| 99 | 11 | 22 | 33 |
+--------------------------------+------+------+------+
2 rows in set (0.00 sec)

drizzle> select t1.*, (select a from t2 where d > a) from t1;
+------+------+------+--------------------------------+
| a | b | c | (select a from t2 where d > a) |
+------+------+------+--------------------------------+
| 1 | 2 | 3 | 1 |
| 11 | 22 | 33 | 11 |
+------+------+------+--------------------------------+
2 rows in set (0.00 sec)

drizzle> select a as 'x', t1.* from t1;
+------+------+------+------+
| x | a | b | c |
+------+------+------+------+
| 1 | 1 | 2 | 3 |
| 11 | 11 | 22 | 33 |
+------+------+------+------+
2 rows in set (0.00 sec)

drizzle> select t1.*, a as 'x' from t1;
+------+------+------+------+
| a | b | c | x |
+------+------+------+------+
| 1 | 2 | 3 | 1 |
| 11 | 22 | 33 | 11 |
+------+------+------+------+
2 rows in set (0.00 sec)

drizzle> select a as 'x', t1.*, b as 'x' from t1;
+------+------+------+------+------+
| x | a | b | c | x |
+------+------+------+------+------+
| 1 | 1 | 2 | 3 | 2 |
| 11 | 11 | 22 | 33 | 22 |
+------+------+------+------+------+
2 rows in set (0.01 sec)

drizzle> select (sel...

Read more...

Stewart Smith (stewart) wrote :

Test needed to be re-enabled.

Changed in drizzle:
assignee: nobody → Stewart Smith (stewart-flamingspork)
milestone: none → bell
status: Invalid → Fix Committed
Stewart Smith (stewart) on 2010-01-26
Changed in drizzle:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers