Hi,

I've got a problem with a complicated INSERT INTO ... SELECT statement.
It works correctly in mysql 5.0.45, but I've had complaints it doesn't
work in 4.1, and according to the documentation it shouldn't work in any
version.

The issue, is that the columns `stages`.`count`, referenced in the HAVING
clause of the SELECT statement, is not in the SELECT list. However, I
can't add it there, because there is no column to insert it into. Any
options I'm overlooking?

SQL Creates:

CREATE TABLE `roster_items` (
`member_id` int(11) unsigned NOT NULL default '0',
`item_name` varchar(96) NOT NULL default '',
`item_parent` varchar(64) NOT NULL default '',
`item_slot` varchar(32) NOT NULL default '',
`item_color` varchar(16) NOT NULL default '',
`item_id` varchar(64) default NULL,
`item_texture` varchar(64) NOT NULL default '',
`item_quantity` int(11) default NULL,
`item_tooltip` mediumtext NOT NULL,
`level` int(11) default NULL,
`item_level` int(11) default NULL,
`locale` varchar(4) default NULL,
PRIMARY KEY (`member_id`,`item_parent`,`item_slot`),
KEY `parent` (`item_parent`),
KEY `slot` (`item_slot`),
KEY `name` (`item_name`) `member_id` int(11) NOT
NULL DEFAULT 0,
`key_name` varchar(16) NOT NULL DEFAULT '',
`stage` int(11) NOT NULL DEFAULT 0,
PRIMARY KEY (`member_id`, `key_name`, `stage`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `roster_addons_keys_stages` (
`faction` char(1) NOT NULL DEFAULT '',
`key_name` varchar(16) NOT NULL DEFAULT '',
`stage` int(11) NOT NULL DEFAULT 0,
`type` char(2) NOT NULL DEFAULT '',
`value` varchar(128) NOT NULL DEFAULT '',
`count` int(11) NOT NULL DEFAULT 0,
`flow` char(2) NOT NULL DEFAULT '',
`active` int(1) NOT NULL DEFAULT 0,
PRIMARY KEY (`faction`, `key_name`, `stage`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `roster_addons_keys_keycache` (
`member_id` int(11) NOT NULL DEFAULT 0,
`key_name` varchar(16) NOT NULL DEFAULT '',
`stage` int(11) NOT NULL DEFAULT 0,
PRIMARY KEY (`member_id`, `key_name`, `stage`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

SQL Query:

INSERT INTO `roster_addons_keys_keycache`
(`member_id`, `key_name`, `stage`)
SELECT '47', `stages`.`key_name`, `stages`.`stage`
FROM `roster_addons_keys_stages` AS stages, `roster_items` AS data
WHERE `stages`.`faction` = 'H'
AND `data`.`member_id` = '47'
AND
(`stages`.`type` = 'In'
AND `data`.`item_name` = `stages`.`value`
OR `stages`.`type` = 'Ii'
AND `data`.`item_id` LIKE CONCAT(`stages`.`value`, ':%')
)
GROUP BY `stages`.`key_name`, `stages`.`stage`, `stages`.`count`
HAVING SUM(`data`.`item_quantity`) >= `stages`.`count`;


`keycache` is an internal cache table that is updated on a data update,
to save work and complexity on display. '47' is a literal filled in by
PHP code, depending on what bit of data got updated.

--
PleegWat
Remove caps to reply

On 6 Feb, 20:35, PleegWat
<pleegwat.REM...@CAPS.leegwater-68.nl.INVALID> wrote:
> Hi,
>
> I've got a problem with a complicated INSERT INTO ... SELECT statement.
> It works correctly in mysql 5.0.45, but I've had complaints it doesn't
> work in 4.1, and according to the documentation it shouldn't work in any
> version.
> Any
> options I'm overlooking?

One option that you're overlooking is to tell us more than "it doesn't
work". What is the precise error that is being observed?

You also say: "according to the documentation it shouldn't work in any
version.". How about pointing us to the precise piece of documentation
that says that?

On Thu, 07 Feb 2008 04:14:51 -0800, Captain Paralytic wrote:

> On 6 Feb, 20:35, PleegWat
> <pleegwat.REM...@CAPS.leegwater-68.nl.INVALID> wrote:
>> Hi,
>>
>> I've got a problem with a complicated INSERT INTO ... SELECT statement.
>> It works correctly in mysql 5.0.45, but I've had complaints it doesn't
>> work in 4.1, and according to the documentation it shouldn't work in
>> any version.
>> Any
>> options I'm overlooking?
>
> One option that you're overlooking is to tell us more than "it doesn't
> work". What is the precise error that is being observed?
>
> You also say: "according to the documentation it shouldn't work in any
> version.". How about pointing us to the precise piece of documentation
> that says that?

Ah, right. Sorry.

Error message is:

| 1054: Unknown column 'stages.count' in 'having clause'

The MySQL 4.1 documentation (http://dev.mysql.com/doc/refman/4.1/en/
select.html) states:

| A HAVING clause can refer to any column or alias named in a
| select_expr in the SELECT list or in outer subqueries, and to
| aggregate functions. (Standard SQL requires that HAVING must reference
| only columns in the GROUP BY clause or columns used in aggregate
| functions.)

MySQL 5.0 documentation (http://dev.mysql.com/doc/refman/5.0/en/
select.html) states:

| A HAVING clause can refer to any column or alias named in a
| select_expr in the SELECT list or in outer subqueries, and to
| aggregate functions. However, the SQL standard requires that HAVING
| must reference only columns in the GROUP BY clause or columns used in
| aggregate functions. To accommodate both standard SQL and the MySQL
| -specific behavior of being able to refer columns in the SELECT list,
| MySQL 5.0.2 and up allows HAVING to refer to columns in the SELECT
| list, columns in the GROUP BY clause, columns in outer subqueries, and
| to aggregate functions.

Which, now that I reread it, is consistent with the functionality I'm
seeing, since the problematic column is in the group by clause.


--
PleegWat\nRemove caps to reply

On Thu, 07 Feb 2008 20:28:57 +0000, PleegWat wrote:

> On Thu, 07 Feb 2008 04:14:51 -0800, Captain Paralytic wrote:
>
>> On 6 Feb, 20:35, PleegWat
>> <pleegwat.REM...@CAPS.leegwater-68.nl.INVALID> wrote:
>>> Hi,
>>>
>>> I've got a problem with a complicated INSERT INTO ... SELECT
>>> statement. It works correctly in mysql 5.0.45, but I've had complaints
>>> it doesn't work in 4.1, and according to the documentation it
>>> shouldn't work in any version.
>>> Any
>>> options I'm overlooking?
>>
>> One option that you're overlooking is to tell us more than "it doesn't
>> work". What is the precise error that is being observed?
>>
>> You also say: "according to the documentation it shouldn't work in any
>> version.". How about pointing us to the precise piece of documentation
>> that says that?
>
> Ah, right. Sorry.
>
> Error message is:
>
> | 1054: Unknown column 'stages.count' in 'having clause'
>
> The MySQL 4.1 documentation (http://dev.mysql.com/doc/refman/4.1/en/
> select.html) states:
>
> | A HAVING clause can refer to any column or alias named in a |
> select_expr in the SELECT list or in outer subqueries, and to |
> aggregate functions. (Standard SQL requires that HAVING must reference |
> only columns in the GROUP BY clause or columns used in aggregate |
> functions.)
>
> MySQL 5.0 documentation (http://dev.mysql.com/doc/refman/5.0/en/
> select.html) states:
>
> | A HAVING clause can refer to any column or alias named in a |
> select_expr in the SELECT list or in outer subqueries, and to |
> aggregate functions. However, the SQL standard requires that HAVING |
> must reference only columns in the GROUP BY clause or columns used in |
> aggregate functions. To accommodate both standard SQL and the MySQL |
> -specific behavior of being able to refer columns in the SELECT list, |
> MySQL 5.0.2 and up allows HAVING to refer to columns in the SELECT |
> list, columns in the GROUP BY clause, columns in outer subqueries, and |
> to aggregate functions.
>
> Which, now that I reread it, is consistent with the functionality I'm
> seeing, since the problematic column is in the group by clause.

OK, I've found something that works:

I've replaced `roster_addons_keys_stages` in the FROM clause by the
subquery:

SELECT `faction`,`key_name`,`stage`,`type`,`value`,`count `
FROM `roster_addons_keys_stages`

--
PleegWat
Remove caps to reply