randgen team mailing list archive

[Shivaprasad <iamshiv@xxxxxxxxx>]

Hi,
    I am in the process generating complex queries from TPCH Database. Coma across QGen and installed it on my PC.

Now I am having few issues in grammar part; which I like to put front, If someone had same issue, hope I will get some answers!

Created two tables with different column name of same data type.

mysql> explain t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| x | int(11) | YES | | NULL | |
| y | int(11) | YES | | NULL | |
| z | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.11 sec)

mysql> explain t2;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a | int(11) | YES | | NULL | |
| b | int(11) | YES | | NULL | |
| c | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.08 sec)

mysql>

The 2 used grammars are below,
select:
SELECT X._field
FROM _table AS X
INNER JOIN _table AS Y
ON X._field = Y._field ;

OR

select:
SELECT _table[invariant]._field
FROM _table[invariant] AS X
INNER JOIN _table AS Y
ON _table[invariant]._field = Y._field ;

The queries generated are also below. The problem with the queries is it is not mapping columns to its table. So queries are logically not correct. sql engine don't understand it!
Even after using invariant of no use. 

SELECT X.`y` FROM `t2` AS X INNER JOIN `t2` AS Y ON X.`b` = Y.`b`;
SELECT X.`y` FROM `t1` AS X INNER JOIN `t2` AS Y ON X.`a` = Y.`b`;
SELECT X.`y` FROM `t2` AS X INNER JOIN `t2` AS Y ON X.`b` = Y.`b`;
SELECT X.`y` FROM `t1` AS X INNER JOIN `t2` AS Y ON X.`a` = Y.`b`;
SELECT X.`y` FROM `t2` AS X INNER JOIN `t1` AS Y ON X.`x` = Y.`y`;
SELECT X.`z` FROM `t1` AS X INNER JOIN `t2` AS Y ON X.`b` = Y.`c`;
SELECT X.`x` FROM `t2` AS X INNER JOIN `t1` AS Y ON X.`z` = Y.`z`;
SELECT X.`z` FROM `t1` AS X INNER JOIN `t1` AS Y ON X.`x` = Y.`z`;
SELECT X.`y` FROM `t1` AS X INNER JOIN `t1` AS Y ON X.`z` = Y.`z`;
SELECT X.`x` FROM `t1` AS X INNER JOIN `t1` AS Y ON X.`y` = Y.`x`;
SELECT X.`y` FROM `t2` AS X INNER JOIN `t2` AS Y ON X.`a` = Y.`c`;
SELECT X.`y` FROM `t2` AS X INNER JOIN `t1` AS Y ON X.`x` = Y.`x`;
SELECT X.`x` FROM `t2` AS X INNER JOIN `t2` AS Y ON X.`c` = Y.`c`;
SELECT X.`x` FROM `t1` AS X INNER JOIN `t2` AS Y ON X.`b` = Y.`b`;
SELECT X.`x` FROM `t2` AS X INNER JOIN `t1` AS Y ON X.`y` = Y.`x`;
SELECT X.`y` FROM `t1` AS X INNER JOIN `t1` AS Y ON X.`z` = Y.`y`;
SELECT X.`x` FROM `t1` AS X INNER JOIN `t2` AS Y ON X.`a` = Y.`c`;
SELECT X.`y` FROM `t1` AS X INNER JOIN `t1` AS Y ON X.`y` = Y.`x`;
SELECT X.`y` FROM `t1` AS X INNER JOIN `t1` AS Y ON X.`z` = Y.`x`;
SELECT X.`z` FROM `t1` AS X INNER JOIN `t2` AS Y ON X.`a` = Y.`b`;
SELECT X.`y` FROM `t2` AS X INNER JOIN `t2` AS Y ON X.`c` = Y.`a`;
SELECT X.`z` FROM `t1` AS X INNER JOIN `t1` AS Y ON X.`x` = Y.`x`;
SELECT X.`x` FROM `t2` AS X INNER JOIN `t1` AS Y ON X.`z` = Y.`y`;
SELECT X.`z` FROM `t2` AS X INNER JOIN `t2` AS Y ON X.`b` = Y.`c`;
SELECT X.`x` FROM `t2` AS X INNER JOIN `t2` AS Y ON X.`b` = Y.`a`;
SELECT X.`z` FROM `t1` AS X INNER JOIN `t1` AS Y ON X.`y` = Y.`y`;
SELECT X.`z` FROM `t2` AS X INNER JOIN `t2` AS Y ON X.`c` = Y.`c`;
SELECT X.`x` FROM `t1` AS X INNER JOIN `t1` AS Y ON X.`z` = Y.`y`;
SELECT X.`z` FROM `t1` AS X INNER JOIN `t1` AS Y ON X.`y` = Y.`x`;
SELECT X.`x` FROM `t1` AS X INNER JOIN `t1` AS Y ON X.`z` = Y.`z`;
SELECT X.`z` FROM `t1` AS X INNER JOIN `t2` AS Y ON X.`c` = Y.`c`;
SELECT X.`z` FROM `t2` AS X INNER JOIN `t1` AS Y ON X.`z` = Y.`x`;
SELECT X.`y` FROM `t1` AS X INNER JOIN `t2` AS Y ON X.`a` = Y.`c`;
SELECT X.`x` FROM `t2` AS X INNER JOIN `t1` AS Y ON X.`x` = Y.`x`;
SELECT X.`z` FROM `t1` AS X INNER JOIN `t1` AS Y ON X.`x` = Y.`y`;
SELECT X.`y` FROM `t2` AS X INNER JOIN `t2` AS Y ON X.`c` = Y.`b`;
SELECT X.`y` FROM `t2` AS X INNER JOIN `t1` AS Y ON X.`z` = Y.`z`;
SELECT X.`y` FROM `t2` AS X INNER JOIN `t1` AS Y ON X.`z` = Y.`z`;

So not sure now how to go further in this? Any idea?

Thanks and Regards,
Shiv