| Bug #43658 | The code below returns an error, while other databases allow it | ||
|---|---|---|---|
| Submitted: | 15 Mar 2009 16:07 | Modified: | 15 Mar 2009 18:42 |
| Reporter: | Philip Orleans | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S1 (Critical) |
| Version: | 5.1 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | SQL enhancement | ||
[15 Mar 2009 16:10]
Philip Orleans
I have a large application to port but I am stuck because of this issue.
[15 Mar 2009 16:40]
Valeriy Kravchuk
Sorry, but this is not a bug. Current behavior is intended and clearly described in the manual, http://dev.mysql.com/doc/refman/5.1/en/select-into-statement.html. Some other commercial databases, like Oracle, also do not allow for SELECT ... INTO to select more than one row. So you have to workaround this in your application using explicit cursor and loop. Read http://dev.mysql.com/doc/refman/5.1/en/cursors.html for the details and some examples.
[15 Mar 2009 16:48]
Philip Orleans
I disagree with the response. We don't compete with Oracle, we compete with SQL Server and Sybase. MS SQL offers a richer functionality than Oracle and Mysql. I think we should change the concept. My application needs to do this 200-300 times per second. Any other technique would be too slow. Please consider it for an enhancement. Why would we let Microsoft take the lead in all aspects of database technology? We can do better. Oracle is wrong in this case.
[15 Mar 2009 18:35]
Valeriy Kravchuk
Can you, please, give me URL to Microsoft's documentation that desribes this feature?
[15 Mar 2009 18:42]
Philip Orleans
Thanks for your answer. The feature is not documented, because MS thinks that this is the "natural" way the things should work, and I built my entire company around that feature. Believe it or not.
Please run this code in any MS-SQL, you will see that, at the end, the variable @plist holds the contents of both rows concatenated. If you don't have a copy of free MS-SQL Express, please email me and I will send you a user name and password to my development box, where you may simply open a new query, paste the code below and run it. You will immediately see my point.
set nocount on
declare @iplist varchar(256)
select @iplist=' '
--drop table #provrates_x
create table #provrates_x(code varchar(16) not null)
INSERT #provrates_x(code) VALUES('132121%')
INSERT #provrates_x(code) VALUES('1954%')
select @iplist=isnull(@iplist,' ')+code from #provrates_x
select @iplist
[15 Mar 2009 20:07]
Valeriy Kravchuk
I had asked for URL to get some description for possible new feature request. I was also not able to find description of this feature in MSDN, at list during a couple of minutes... I also wonder if some dirty tricks with GROUP_CONCAT (see http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html#function_group-concat) can help you: mysql> select @myname:='richard'; +--------------------+ | @myname:='richard' | +--------------------+ | richard | +--------------------+ 1 row in set (0.00 sec) mysql> select group_concat(code,@myname) into @myname from (select * from worker limit 2) a; Query OK, 1 row affected (0.00 sec) mysql> select @myname; +-----------------------+ | @myname | +-----------------------+ | unorichard,dosrichard | +-----------------------+ 1 row in set (0.00 sec)
Description: select @myname:='richard'; select @myname; CREATE TABLE worker (code char(20), workerid int(11) NOT NULL auto_increment, name char(15) default NULL, PRIMARY KEY (workerid)); INSERT INTO worker VALUES ('uno',null,'tom'); INSERT INTO worker VALUES ('dos',null,'dick'); INSERT INTO worker VALUES ('tres',null,'harry'); select CONCAT_WS(code,@myname) into @myname from worker limit 2; #drop table worker; select @myname; How to repeat: The setting of LIMIT to anything other than 1 returns an error, but that is wrong, MS SQL server allows it this is what happens: the variable @myname concatenates all the rows for the field "code". The SQL engine traverses the table row by row concatenating the variable to itself and the field's value, in a recursive manner. This the correct way of doing this. My application's code cannot be ported to Mysql because of this. There are business problems that are very difficult to solve unless this works. The "select [field] into [variable]' construct should never fail. It should select the last row that matches the query's "where" clause. This is how other commercial databases work, like Sybase and MS SQL Suggested fix: Please read above