Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Query Folding produces syntactically incorrect SQL when appending three or more queries

I have an issue where I'm trying to append the results of two queries into a third, using the append query option.

 

The tables all are just a list of User ID and Date, and I'm also adding a column to have a static number for the instance ID so that I can track which site was visited in the aggregate statistics.

 

However, the Query Folding gives an SQL statement that is syntatcially incorrect, and SQL reports an error, though the Query Builder reports no errors.

 

The resultant SQL by viewing the native query is:

 

(

select `_`.`id` as `id`,
`_`.`user_id` as `user_id`,
`_`.`date` as `date`,
344 as `instance_id`
from
(
select `_`.`id`,
`_`.`user_id`,
`_`.`date`
from `ebdb`.`ssv1_344_stat_pageview` `_`
where `_`.`date` >= '2019-01-01 00:00:00' and `_`.`date` < '2019-07-06 00:00:00'
) `_`
union all select `_`.`id` as `id`,
`_`.`user_id` as `user_id`,
`_`.`date` as `date`,
345 as `instance_id`
from
(
select `_`.`id`,
`_`.`user_id`,
`_`.`date`
from `ebdb`.`ssv1_345_stat_pageview` `_`
where `_`.`date` >= '2019-01-01 00:00:00' and `_`.`date` < '2019-07-06 00:00:00'
) `_`
)
union all select `_`.`id` as `id`,
`_`.`user_id` as `user_id`,
`_`.`date` as `date`,
329 as `instance_id`
from
(
select `_`.`id`,
`_`.`user_id`,
`_`.`date`
from `ebdb`.`ssv1_329_stat_pageview` `_`
where `_`.`date` >= '2019-01-01 00:00:00' and `_`.`date` < '2019-07-06 00:00:00'
) `_`

 

This throws an error. If I then modify the three queries within query builder to add an extra column by duplicating the date column, the query works, and produces this SQL:

select `_`.`id` as `id`,
`_`.`user_id` as `user_id`,
`_`.`date` as `date`,
`_`.`instance_id` as `instance_id`,
`_`.`date - Copy` as `Hour`
from
(
(
select `_`.`id` as `id`,
`_`.`user_id` as `user_id`,
`_`.`date` as `date`,
344 as `instance_id`,
`_`.`date` as `date - Copy`
from
(
select `_`.`id`,
`_`.`user_id`,
`_`.`date`
from `ebdb`.`ssv1_344_stat_pageview` `_`
where `_`.`date` >= '2019-01-01 00:00:00' and `_`.`date` < '2019-07-06 00:00:00'
) `_`
union all select `_`.`id` as `id`,
`_`.`user_id` as `user_id`,
`_`.`date` as `date`,
345 as `instance_id`,
`_`.`date` as `date - Copy`
from
(
select `_`.`id`,
`_`.`user_id`,
`_`.`date`
from `ebdb`.`ssv1_345_stat_pageview` `_`
where `_`.`date` >= '2019-01-01 00:00:00' and `_`.`date` < '2019-07-06 00:00:00'
) `_`
)
union all select `_`.`id` as `id`,
`_`.`user_id` as `user_id`,
`_`.`date` as `date`,
329 as `instance_id`,
`_`.`date` as `date - Copy`
from
(
select `_`.`id`,
`_`.`user_id`,
`_`.`date`
from `ebdb`.`ssv1_329_stat_pageview` `_`
where `_`.`date` >= '2019-01-01 00:00:00' and `_`.`date` < '2019-07-06 00:00:00'
) `_`
) `_`

 

By comparing the two, it seems when just adding the extra column of a number for instance_id the query folding is omitting a 

select `_`.`id` as `id`,
`_`.`user_id` as `user_id`,
`_`.`date` as `date`,
`_`.`instance_id` as `instance_id`
from
(

 

from the beginning of the query, and

) `_`

 

from the end.

Status: New
Comments
v-qiuyu-msft
Community Support

Hi @Anonymous, 

 

Please share the DDL for us to create sample tables in SQL database, and detail steps in Power BI desktop for us to reproduce the issue. 

 

Best Regards,
Qiuyun Yu 

Anonymous
Not applicable

Any three database tables will work as long as you have several columns, including an id, user_id and date columns. id and user_id are integers, date is MySQL datetime format.

 

To recreate in PowerBI desktop, create a query for two of the tables, setting them both up to filter columns for just the id, user_id and date. Create an additional column called instance_id and set this to be a custom formula, and give it the formula =123 (a different number for each of the three). Filter the date to be this year to date.

 

Do the same for the second query.

 

For the third query, do the same again, then append the first two queries. This will then produce the invalid folded SQL statements.