Issue: not support  multiple union query ! 
Author: b0123456789
State: closed
Created: 2017-03-15 02:54:42

<pre>
a = AccountBilibili.select(AccountBilibili.am_id.alias('id'), AccountBilibili.am_name.alias('name'),   SQL("(select 'bilibili')").alias('type'))

b = AccountMeipai.  select(AccountMeipai.am_id.alias('id'), AccountBilibili.am_name.alias('name'),     SQL("(select 'meipai')").alias('type'))

c = AccountMiaopai. select(AccountMiaopai.am_id.alias('id'), AccountBilibili.am_name.alias('name'),    SQL("(select 'miaopai')").alias('type'))

d= (a|b|c)

print(d.sql())

</pre>

It output this below.
<pre>
("((SELECT `t1`.`am_id` AS id, `t1`.`am_name` AS name, (select 'bilibili') AS type FROM `wyx_account_bilibili` AS t1) UNION (SELECT `t2`.`am_id` AS id, `t3`.`am_name` AS name, (select 'meipai') AS type FROM `wyx_account_meipai` AS t2)) UNION (SELECT `t3`.`am_id` AS id, `t4`.`am_name` AS name, (select 'miaopai') AS type FROM `wyx_account_miaopai` AS t3)", [])
</pre>

It output this when I  formated .
<pre>
(
	(
		SELECT
			`t1`.`am_id` AS id,
			`t1`.`am_name` AS NAME,
			(SELECT 'bilibili') AS type
		FROM
			`wyx_account_bilibili` AS t1
	)
	UNION
		(
			SELECT
				`t2`.`am_id` AS id,
				`t3`.`am_name` AS NAME,
				(SELECT 'meipai') AS type
			FROM
				`wyx_account_meipai` AS t2
		)
)
UNION
	(
		SELECT
			`t3`.`am_id` AS id,
			`t4`.`am_name` AS NAME,
			(SELECT 'miaopai') AS type
		FROM
			`wyx_account_miaopai` AS t3
	) 
</pre>


Mysql not support this sql, and there are some obvious  errer here.

Can you please provide a small example so I can reproduce the issue? Can you be more clear where the obvious errors / issues are?

OK, so I guess in the second query you are wanting `AccountBilibili` to be aliased to `t1` instead of `t3`? In the third query (`c`) the same issue?  Would that even work, though?

The issue here is that the parentheses are not valid in MySQL.
This should have been fixed with #1173 in version 2.8.7.

So the solution might just be to upgrade to the latest version of peewee.


The correct sql should be like this  below.
<pre>
(
	SELECT
		`t1`.`am_id` AS id,
		`t1`.`am_name` AS name,
		(SELECT 'bilibili') AS type
	FROM
		`wyx_account_bilibili` AS t1
)
UNION 
	(
		SELECT
			`t2`.`am_id` AS id,
			`t2`.`am_name` AS name,
			(SELECT 'meipai') AS type
		FROM
			`wyx_account_meipai` AS t2
	)
UNION 
	(
		SELECT
			`t3`.`am_id` AS id,
			`t3`.`am_name` AS name,
			(SELECT 'miaopai') AS type
		FROM
			`wyx_account_miaopai` AS t3
	)
</pre>

The first issue is that the <pre>the first parentheses  outside  is not support by mysql. </pre>
the second issue is  that  <pre> the seconde  union  `t3` table is not found as well as the third union table ` t4`</pre>

It still get errors after upgrading  to 2.9.1, tell me what's the date the next vesion release. i'll try.

 I change "**d= (a|b|c)**" to  "**d = a.union_all(b).union_all(c)**"
It output this.
<pre>
("(SELECT `t1`.`am_id` AS id, `t1`.`am_name` AS `name`, (select 'bilibili') AS type FROM `wyx_account_bilibili` AS t1) UNION ALL (SELECT `t2`.`am_id` AS id, `t3`.`am_name` AS `name`, (select 'meipai') AS type FROM `wyx_account_meipai` AS t2) UNION ALL (SELECT `t3`.`am_id` AS id, `t4`.`am_name` AS `name`, (se
lect 'miaopai') AS type FROM `wyx_account_miaopai` AS t3)", [])
</pre>

It output that below when it formated.
<pre>
(
	SELECT
		`t1`.`am_id` AS id,
		`t1`.`am_name` AS `name`,
		(SELECT 'bilibili') AS type
	FROM
		`wyx_account_bilibili` AS t1
)
UNION ALL
	(
		SELECT
			`t2`.`am_id` AS id,
			`t3`.`am_name` AS `name`,
			(SELECT 'meipai') AS type
		FROM
			`wyx_account_meipai` AS t2
	)
UNION ALL
	(
		SELECT
			`t3`.`am_id` AS id,
			`t4`.`am_name` AS `name`,
			(SELECT 'miaopai') AS type
		FROM
			`wyx_account_miaopai` AS t3
	)
</pre>

It has not first issue above,but it still  got the seconde issue "**[Err] 1054 - Unknown column 't3.am_name' in 'field list'**" the table is not correct.



For test.
<pre>
CREATE TABLE `wyx_account_bilibili` (
  `am_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `am_name` varchar(150) NOT NULL DEFAULT '',
  PRIMARY KEY (`am_id`)
) ENGINE=InnoDB AUTO_INCREMENT=345 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

CREATE TABLE `wyx_account_meipai` (
  `am_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `am_name` varchar(150) NOT NULL DEFAULT '',
  PRIMARY KEY (`am_id`)
) ENGINE=InnoDB AUTO_INCREMENT=345 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;


CREATE TABLE `wyx_account_miaopai` (
  `am_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `am_name` varchar(150) NOT NULL DEFAULT '',
  PRIMARY KEY (`am_id`)
) ENGINE=InnoDB AUTO_INCREMENT=345 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

</pre>

<pre>
# encoding: utf-8


from peewee import *
from playhouse.read_slave import ReadSlaveModel
from playhouse.shortcuts import RetryOperationalError


class MyRetryDB(RetryOperationalError, MySQLDatabase):
    pass


master = MyRetryDB(
    host='127.0.0.1',
    user='root',
    passwd='root',
    database='weiyingxiao',
    charset='utf8',
    port=3306
)


class BaseModel(ReadSlaveModel):
    class Meta:
        database = master


class AccountBilibili(BaseModel):
    class Meta:
        db_table = 'wyx_account_bilibili'

    am_id = PrimaryKeyField()
    am_name = CharField()  # nickname


class AccountMeipai(BaseModel):
    class Meta:
        db_table = 'wyx_account_meipai'

    am_id = PrimaryKeyField()  #
    am_name = CharField()  # nickname


class AccountMiaopai(BaseModel):
    class Meta:
        db_table = 'wyx_account_miaopai'

    am_id = PrimaryKeyField()  # 
    am_name = CharField()  # nickname


def get_video_account():
    a = AccountBilibili.select(AccountBilibili.am_id.alias('id'), AccountBilibili.am_name.alias('name'),
                               SQL("(select 'bilibili')").alias('type'))

    b = AccountMeipai.select(AccountMeipai.am_id.alias('id'), AccountBilibili.am_name.alias('name'),
                             SQL("(select 'meipai')").alias('type'))

    c = AccountMiaopai.select(AccountMiaopai.am_id.alias('id'), AccountBilibili.am_name.alias('name'),
                              SQL("(select 'miaopai')").alias('type'))

    d = (a | b | c)
    # d = a.union_all(b).union_all(c)

    print(d.sql())


if __name__ == '__main__':
    get_video_account()

</pre>



You are mixing models in your queries.

It should be:
```python
def get_video_account():
    a = AccountBilibili.select(AccountBilibili.am_id.alias('id'), AccountBilibili.am_name.alias('name'),
                               SQL("(select 'bilibili')").alias('type'))

    b = AccountMeipai.select(AccountMeipai.am_id.alias('id'), AccountMeipai.am_name.alias('name'),
                             SQL("(select 'meipai')").alias('type'))

    c = AccountMiaopai.select(AccountMiaopai.am_id.alias('id'), AccountMiaopai.am_name.alias('name'),
                              SQL("(select 'miaopai')").alias('type'))

    d = (a | b | c)
```

THX

Copy/paste fail? I wondered why the models were all mixed up like that :/

Yes, It make mistake form copying and pasting.