4.1 Subselect, Union error

4.1 Subselect, Union error

am 18.07.2003 16:57:30 von Asher Hastings

Hi,

I have regularly encountered the following error with MySQL 4.1 Alpha
running on a Linux i386 platform.

--- Start of error command ---

T@36874: >do_command
T@36874: | >vio_is_blocking
T@36874: | | exit: 0
T@36874: | T@36874: | >vio_read
T@36874: | | enter: sd=10, buf=0x89ef230, size=4
T@36874: | | exit: 4
T@36874: | T@36874: | >vio_read
T@36874: | | enter: sd=10, buf=0x89ef230, size=1165
T@36874: | | exit: 1165
T@36874: | T@36874: | info: Command on TCP/IP (10) = 3 (Query)
T@36874: T@36874: >dispatch_command
T@36874: | query: INSERT INTO TEMP SELECT p.Project_ID FROM ms_project AS p,
ms_account_user_map AS map, (SELECT U1.User_ID AS id FROM ms_role AS R1,
00DF4895dF577l4ojK.ms_role AS R2 LEFT JOIN ms_user AS U1 ON
R2.id=U1.User_RoleId WHERE R1.lft > R2.lft AND R1.lft < R2.rgt AND
R2.id=(SELECT U2.User_RoleId FROM ms_user AS U2 WHERE U2.User_ID='12345') )
AS role WHERE p.Project_TypeId=map.Account_ID AND map.User_ID=role.id AND
p.Project_Recycle=0 GROUP BY p.Project_ID UNION ALL SELECT p.Project_ID AS
ID FROM ms_project AS p, ms_project_team_map AS t, (SELECT U1.User_ID AS id
FROM ms_role AS R1, ms_role AS R2 LEFT JOIN ms_user AS U1 ON
R2.id=U1.User_RoleId WHERE R1.lft > R2.lft AND R1.lft < R2.rgt AND
R2.id=(SELECT U2.User_RoleId FROM ms_user AS U2 WHERE U2.User_ID='12345') )
AS role WHERE p.Project_ID=t.Team_Project_ID AND t.Team_User_ID=role.id AND
p.Project_Recycle=0 GROUP BY p.Project_ID
T@36874: | >mysql_parse
T@36874: | | >mysql_init_query
T@36874: | | T@36874: | | >Query_cache::send_result_to_client
T@36874: | | T@36874: | | >add_table_to_list
T@36874: | | T@36874: | | >set_lock_for_tables
T@36874: | | | enter: lock_type: 7 for_update: 1
T@36874: | | T@36874: | | >add_table_to_list
T@36874: | | T@36874: | | >add_table_to_list
T@36874: | | T@36874: | | >add_table_to_list
T@36874: | | T@36874: | | >add_table_to_list
T@36874: | | T@36874: | | >add_table_to_list
T@36874: | | T@36874: | | >add_table_to_list
T@36874: | | T@36874: | | >Item_singlerow_subselect::Item_singlerow_subselect
T@36874: | | | >Item_subselect::init
T@36874: | | | | subs: select_lex 0x89e5fc8l
T@36874: | | | T@36874: | | T@36874: | | >add_table_to_list
T@36874: | | T@36874: | | >my_malloc
T@36874: | | | my: Size: 8164 MyFlags: 16
T@36874: | | | exit: ptr: 89eab50
T@36874: | | T@36874: | | >add_to_list
T@36874: | | T@36874: | | >add_table_to_list
T@36874: | | T@36874: | | >add_table_to_list
T@36874: | | T@36874: | | >add_table_to_list
T@36874: | | T@36874: | | >add_table_to_list
T@36874: | | T@36874: | | >add_table_to_list
T@36874: | | T@36874: | | >add_table_to_list
T@36874: | | T@36874: | | >Item_singlerow_subselect::Item_singlerow_subselect
T@36874: | | | >Item_subselect::init
T@36874: | | | | subs: select_lex 0x89eba98l
T@36874: | | | T@36874: | | T@36874: | | >add_table_to_list
T@36874: | | T@36874: | | >my_malloc
T@36874: | | | my: Size: 8164 MyFlags: 16
T@36874: | | | exit: ptr: 89f7250
T@36874: | | T@36874: | | >add_to_list
T@36874: | | T@36874: | | >send_error
T@36874: | | | enter: sql_errno: 1149 err: NULL <<----------- Error
seems to get thrown here
T@36874: | | | >query_cache_abort
T@36874: | | | T@36874: | | | >net_write_command
T@36874: | | | | enter: length: 131
T@36874: | | | T@36874: | | | >net_flush
T@36874: | | | | >vio_is_blocking
T@36874: | | | | | exit: 0
T@36874: | | | | T@36874: | | | | >net_real_write
T@36874: | | | | | >vio_write
T@36874: | | | | | | enter: sd=10, buf=0x89ef230, size=136
T@36874: | | | | | | exit: 136
T@36874: | | | | | T@36874: | | | | T@36874: | | | T@36874: | | T@36874: | | info: Command aborted. Fatal_error: 0
T@36874: | | >query_cache_abort
T@36874: | | T@36874: | | >my_free
T@36874: | | | my: ptr: 0
T@36874: | | T@36874: | | >my_free
T@36874: | | | my: ptr: 0
T@36874: | | T@36874: | T@36874: | info: query ready
T@36874: | >free_root
T@36874: | | enter: root: 89e2774 flags: 1
T@36874: | | >my_free
T@36874: | | | my: ptr: 89eab50
T@36874: | | T@36874: | | >my_free
T@36874: | | | my: ptr: 89f7250
T@36874: | | T@36874: | T@36874:
--- end of error command ---

I am running the following SQL query(s):

Query 1:

DROP TEMPORARY TABLE IF EXISTS TEMP

Query 2:

CREATE TEMPORARY TABLE TEMP( tmpId varchar(100) NOT NULL, PRIMARY KEY( tmpId
) ) TYPE=MYISAM

Query 3:

Create a union from two subqueries...

INSERT INTO TEMP SELECT p.Project_ID FROM ms_project AS p,
ms_account_user_map AS map, (SELECT U1.User_ID AS id FROM
00DF4895dF577l4ojK.ms_role AS R1, ms_role AS R2 LEFT JOIN ms_user AS U1 ON
R2.id=U1.User_RoleId WHERE R1.lft > R2.lft AND R1.lft < R2.rgt AND
R2.id=(SELECT U2.User_RoleId FROM ms_user AS U2 WHERE U2.User_ID='12345') )
AS role WHERE p.Project_TypeId=map.Account_ID AND map.User_ID=role.id AND
p.Project_Recycle=0 GROUP BY p.Project_ID UNION ALL SELECT p.Project_ID AS
ID FROM ms_project AS p, ms_project_team_map AS t, (SELECT U1.User_ID AS id
FROM ms_role AS R1, ms_role AS R2 LEFT JOIN ms_user AS U1 ON
R2.id=U1.User_RoleId WHERE R1.lft > R2.lft AND R1.lft < R2.rgt AND
R2.id=(SELECT U2.User_RoleId FROM ms_user AS U2 WHERE U2.User_ID='12345') )
AS role WHERE p.Project_ID=t.Team_Project_ID AND t.Team_User_ID=role.id AND
p.Project_Recycle=0 GROUP BY p.Project_ID

Query 4:

SELECT project.Project_ID, account.Account_Name FROM TEMP AS t LEFT JOIN
ms_project AS project on t.tmpId=project.Project_ID LEFT JOIN ms_user AS pm
ON pm.User_ID=project.Project_ManagerId LEFT JOIN ms_user AS po ON
po.User_ID=project.Project_OwnerId LEFT JOIN ms_account AS account ON
account.Account_ID=project.Project_TypeId WHERE project.Project_Status=1
ORDER BY account.Account_Name ASC LIMIT 0,21

Query 5:

DROP TEMPORARY TABLE IF EXISTS TEMP


Chrs,
Asher

____________________________________________________________ _____
Surf the net and talk on the phone with Xtra Jetstream @
http://www.xtra.co.nz/products/0,,5803,00.html !


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: 4.1 Subselect, Union error

am 19.07.2003 14:14:25 von Sinisa Milivojevic

Asher Hastings writes:
> Hi,
>
> I have regularly encountered the following error with MySQL 4.1 Alpha
> running on a Linux i386 platform.
>

[skip]
>
>
> Chrs,
> Asher
>
> ____________________________________________________________ _____
> Surf the net and talk on the phone with Xtra Jetstream @
> http://www.xtra.co.nz/products/0,,5803,00.html !
>

HI!

In order to be able to repeat a problem we need the other tables
involved.

Upload them to : ftp://support.mysql.com:/pub/mysql/secret

and let us know a file name.

--

Regards,

--
For technical support contracts, go to https://order.mysql.com/?ref=msmi
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB
/_/ /_/\_, /___/\___\_\___/ Fulltime Developer and Support Coordinator
<___/ www.mysql.com Larnaca, Cyprus


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: 4.1 Subselect, Union error

am 28.07.2003 14:31:02 von Sinisa Milivojevic

Asher Hastings writes:
> Hi,
>
> I have regularly encountered the following error with MySQL 4.1 Alpha
> running on a Linux i386 platform.
>

Hi!

I was not able to repeat a problem you report.

With latest 4.1.1, I get with the following query:

>
> INSERT INTO TEMP SELECT p.Project_ID FROM ms_project AS p,
> ms_account_user_map AS map, (SELECT U1.User_ID AS id FROM
> 00DF4895dF577l4ojK.ms_role AS R1, ms_role AS R2 LEFT JOIN ms_user AS U1 ON
> R2.id=U1.User_RoleId WHERE R1.lft > R2.lft AND R1.lft < R2.rgt AND
> R2.id=(SELECT U2.User_RoleId FROM ms_user AS U2 WHERE U2.User_ID='12345') )
> AS role WHERE p.Project_TypeId=map.Account_ID AND map.User_ID=role.id AND
> p.Project_Recycle=0 GROUP BY p.Project_ID UNION ALL SELECT p.Project_ID AS
> ID FROM ms_project AS p, ms_project_team_map AS t, (SELECT U1.User_ID AS id
> FROM ms_role AS R1, ms_role AS R2 LEFT JOIN ms_user AS U1 ON
> R2.id=U1.User_RoleId WHERE R1.lft > R2.lft AND R1.lft < R2.rgt AND
> R2.id=(SELECT U2.User_RoleId FROM ms_user AS U2 WHERE U2.User_ID='12345') )
> AS role WHERE p.Project_ID=t.Team_Project_ID AND t.Team_User_ID=role.id AND
> p.Project_Recycle=0 GROUP BY p.Project_ID
>

ERROR 1265 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation '='

And with the following one:

> Query 4:
>
> SELECT project.Project_ID, account.Account_Name FROM TEMP AS t LEFT JOIN
> ms_project AS project on t.tmpId=project.Project_ID LEFT JOIN ms_user AS pm
> ON pm.User_ID=project.Project_ManagerId LEFT JOIN ms_user AS po ON
> po.User_ID=project.Project_OwnerId LEFT JOIN ms_account AS account ON
> account.Account_ID=project.Project_TypeId WHERE project.Project_Status=1
> ORDER BY account.Account_Name ASC LIMIT 0,21
>

ERROR 1146 (42S02): Table 'bug.ms_account' doesn't exist


Can you send us the additional info ??

--

Regards,

--
For technical support contracts, go to https://order.mysql.com/?ref=msmi
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB
/_/ /_/\_, /___/\___\_\___/ Fulltime Developer and Support Coordinator
<___/ www.mysql.com Larnaca, Cyprus


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org