bug in subselect in mysql-4.1

am 07.10.2002 14:50:15 von Jocelyn Fournier

Hi,

I don't know if it's time to play with mysql-4.1, but just in case, I report
this bug.
How-to-repeat :

CREATE TABLE `inscrit` (
`pseudo` varchar(35) character set latin1 NOT NULL default '',
`email` varchar(60) character set latin1 NOT NULL default '',
PRIMARY KEY (`pseudo`),
UNIQUE KEY `email` (`email`)
) TYPE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC

INSERT INTO inscrit (pseudo,email) VALUES ('joce','test');
INSERT INTO inscrit (pseudo,email) VALUES ('joce1','test1');
INSERT INTO inscrit (pseudo,email) VALUES ('2joce1','2test1');

mysql> SELECT pseudo FROM inscrit WHERE pseudo=(SELECT pseudo FROM inscrit
WHERE pseudo LIKE '%joce%');
ERROR 1240: Subselect returns more than 1 record
mysql>
Number of processes running now: 0
021007 14:41:56 mysqld restarted

Here is the stack trace associated with the crash :

0x809e557 handle_segfault(int) + 487
0x828426a pthread_sighandler + 138
0x804c7cb Item::save_in_field(Field*) + 347
0x80d57ce store_key_item::copy() + 30
0x80d0454 create_ref_for_key(JOIN*, st_join_table*, keyuse_t*, unsigned
long) + 1156
0x80c9e37 make_join_statistics(JOIN*, st_table_list*, Item*,
st_dynamic_array*) + 2759
0x80c5065 JOIN::optimize() + 325
0x80c69da mysql_select(THD*, st_table_list*, List&, Item*, st_order*,
st_order*, Item*, st_order*, unsigned long, select_result*,
st_select_lex_unit*) + 170
0x80c49f3 handle_select(THD*, st_lex*, select_result*) + 227
0x80a8838 mysql_execute_command(THD*) + 840
0x80ad469 mysql_parse(THD*, char*, unsigned) + 153
0x80a75c4 dispatch_command(enum_server_command, THD*, char*, unsigned) + 916
0x80a721d do_command(THD*) + 109
0x80a6ad8 handle_one_connection(void*) + 888
0x828185e pthread_start_thread + 222
0x82b867a thread_start + 4

New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://www.mysql.com/doc/U/s/Using_stack_trace.html and follow
instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x8eb4bb8 = SELECT pseudo FROM inscrit WHERE pseudo=(SELECT
pseudo FROM inscrit WHERE pseudo LIKE '%joce%')
thd->thread_id=108

mysql> SELECT pseudo FROM inscrit WHERE pseudo=(SELECT pseudo FROM inscrit
WHERE pseudo LIKE 'joce');
Empty set (0.00 sec)

mysql> SELECT pseudo FROM inscrit WHERE pseudo=(SELECT pseudo FROM inscrit
WHERE pseudo='joce');
+--------+
| pseudo |
+--------+
| joce |
| joce1 |
+--------+
2 rows in set (0.00 sec)

Regards,
Jocelyn

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12651@lists.mysql.com
To unsubscribe, e-mail

Re: bug in subselect in mysql-4.1

am 07.10.2002 20:58:51 von Sanja Byelkin

Hi!

On Mon, Oct 07, 2002 at 02:50:15PM +0200, Jocelyn Fournier wrote:
> Hi,
>
> I don't know if it's time to play with mysql-4.1, but just in case, I report
> this bug.
> How-to-repeat :
>
> CREATE TABLE `inscrit` (
> `pseudo` varchar(35) character set latin1 NOT NULL default '',
> `email` varchar(60) character set latin1 NOT NULL default '',
> PRIMARY KEY (`pseudo`),
> UNIQUE KEY `email` (`email`)
> ) TYPE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC
>
> INSERT INTO inscrit (pseudo,email) VALUES ('joce','test');
> INSERT INTO inscrit (pseudo,email) VALUES ('joce1','test1');
> INSERT INTO inscrit (pseudo,email) VALUES ('2joce1','2test1');
>
> mysql> SELECT pseudo FROM inscrit WHERE pseudo=(SELECT pseudo FROM inscrit
> WHERE pseudo LIKE '%joce%');
> ERROR 1240: Subselect returns more than 1 record
> mysql>
> Number of processes running now: 0
> 021007 14:41:56 mysqld restarted

[skip]

Thank you for good bugreport.
Here is patch to solve problem (it will be soon in bk repository):

===== sql/item_subselect.cc 1.13 vs edited =====
*** /tmp/item_subselect.cc-1.13-29506 Sat Sep 28 18:34:56 2002
--- edited/sql/item_subselect.cc Mon Oct 7 21:50:26 2002
***************
*** 119,139 ****
--- 119,148 ----
double Item_singleval_subselect::val ()
{
if (engine->exec())
+ {
+ assign_null();
return 0;
+ }
return real_value;
}

longlong Item_singleval_subselect::val_int ()
{
if (engine->exec())
+ {
+ assign_null();
return 0;
+ }
return int_value;
}

String *Item_singleval_subselect::val_str (String *str)
{
if (engine->exec() || null_value)
+ {
+ assign_null();
return 0;
+ }
return &str_value;
}

***************
*** 157,177 ****
--- 166,195 ----
double Item_exists_subselect::val ()
{
if (engine->exec())
+ {
+ assign_null();
return 0;
+ }
return (double) value;
}

longlong Item_exists_subselect::val_int ()
{
if (engine->exec())
+ {
+ assign_null();
return 0;
+ }
return value;
}

String *Item_exists_subselect::val_str(String *str)
{
if (engine->exec())
+ {
+ assign_null();
return 0;
+ }
str->set(value);
return str;
}

--
For technical support contracts, visit https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Oleksandr Byelkin
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Lugansk, Ukraine
<___/ www.mysql.com

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12660@lists.mysql.com
To unsubscribe, e-mail

Re: bug in subselect in mysql-4.1

am 09.10.2002 15:34:21 von Jocelyn Fournier

Hi,

Perhaps another bug :

mysql> SELECT pseudo FROM inscrit WHERE pseudo=(SELECT pseudo FROM inscrit
WHERE pseudo='joce');
Empty set (0.00 sec)

returns no result where as

mysql> SELECT pseudo FROM inscrit WHERE pseudo='joce';
+--------+
| pseudo |
+--------+
| joce |
+--------+
1 row in set (0.00 sec)

But

mysql> SELECT pseudo FROM inscrit WHERE pseudo LIKE (SELECT pseudo FROM
inscrit WHERE pseudo='joce');
+--------+
| pseudo |
+--------+
| joce |
+--------+
1 row in set (0.00 sec)

works fine.

Regards,
Jocelyn

----- Original Message -----
From: "Sanja Byelkin"
To: "Jocelyn Fournier"
Cc:
Sent: Monday, October 07, 2002 8:58 PM
Subject: Re: bug in subselect in mysql-4.1

> Hi!
>
> On Mon, Oct 07, 2002 at 02:50:15PM +0200, Jocelyn Fournier wrote:
> > Hi,
> >
> > I don't know if it's time to play with mysql-4.1, but just in case, I
report
> > this bug.
> > How-to-repeat :
> >
> > CREATE TABLE `inscrit` (
> > `pseudo` varchar(35) character set latin1 NOT NULL default '',
> > `email` varchar(60) character set latin1 NOT NULL default '',
> > PRIMARY KEY (`pseudo`),
> > UNIQUE KEY `email` (`email`)
> > ) TYPE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC
> >
> > INSERT INTO inscrit (pseudo,email) VALUES ('joce','test');
> > INSERT INTO inscrit (pseudo,email) VALUES ('joce1','test1');
> > INSERT INTO inscrit (pseudo,email) VALUES ('2joce1','2test1');
> >
> > mysql> SELECT pseudo FROM inscrit WHERE pseudo=(SELECT pseudo FROM
inscrit
> > WHERE pseudo LIKE '%joce%');
> > ERROR 1240: Subselect returns more than 1 record
> > mysql>
> > Number of processes running now: 0
> > 021007 14:41:56 mysqld restarted
>
> [skip]
>
> Thank you for good bugreport.
> Here is patch to solve problem (it will be soon in bk repository):
>
> ===== sql/item_subselect.cc 1.13 vs edited =====
> *** /tmp/item_subselect.cc-1.13-29506 Sat Sep 28 18:34:56 2002
> --- edited/sql/item_subselect.cc Mon Oct 7 21:50:26 2002
> ***************
> *** 119,139 ****
> --- 119,148 ----
> double Item_singleval_subselect::val ()
> {
> if (engine->exec())
> + {
> + assign_null();
> return 0;
> + }
> return real_value;
> }
>
> longlong Item_singleval_subselect::val_int ()
> {
> if (engine->exec())
> + {
> + assign_null();
> return 0;
> + }
> return int_value;
> }
>
> String *Item_singleval_subselect::val_str (String *str)
> {
> if (engine->exec() || null_value)
> + {
> + assign_null();
> return 0;
> + }
> return &str_value;
> }
>
> ***************
> *** 157,177 ****
> --- 166,195 ----
> double Item_exists_subselect::val ()
> {
> if (engine->exec())
> + {
> + assign_null();
> return 0;
> + }
> return (double) value;
> }
>
> longlong Item_exists_subselect::val_int ()
> {
> if (engine->exec())
> + {
> + assign_null();
> return 0;
> + }
> return value;
> }
>
> String *Item_exists_subselect::val_str(String *str)
> {
> if (engine->exec())
> + {
> + assign_null();
> return 0;
> + }
> str->set(value);
> return str;
> }
>
>
> --
> For technical support contracts, visit https://order.mysql.com/
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Mr. Oleksandr Byelkin
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
> /_/ /_/\_, /___/\___\_\___/ Lugansk, Ukraine
> <___/ www.mysql.com
>
> ------------------------------------------------------------ ---------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail bugs-thread12660@lists.mysql.com
> To unsubscribe, e-mail
>
>

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12679@lists.mysql.com
To unsubscribe, e-mail

Re: bug in subselect in mysql-4.1

am 09.10.2002 20:26:58 von Sanja Byelkin

Hi!

On Wed, Oct 09, 2002 at 03:34:21PM +0200, Jocelyn Fournier wrote:

[skip]

> mysql> SELECT pseudo FROM inscrit WHERE pseudo=(SELECT pseudo FROM inscrit
> WHERE pseudo='joce');
> Empty set (0.00 sec)

Thank you for bug report. I take care of it.
I'll inform you as soon as I fix it.

[skip]

--
For technical support contracts, visit https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Oleksandr Byelkin
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Lugansk, Ukraine
<___/ www.mysql.com

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12683@lists.mysql.com
To unsubscribe, e-mail

Re: bug in subselect in mysql-4.1

am 10.10.2002 08:44:50 von Marc Lazzaro

Hi,

I do not know very well MySQL syntax but it seems to me that this query
cannot work as "pseudo" will never be equal to the list returned by the
subselect. The correct syntax from my view should be:

SELECT pseudo FROM inscrit WHERE pseudo IN SELECT pseudo FROM inscrit
WHERE pseudo='joce');

Am I wrong?

Rgds

Marc

sanja@mysql.com wrote:
>
> Hi!
>
> On Wed, Oct 09, 2002 at 03:34:21PM +0200, Jocelyn Fournier wrote:
>
> [skip]
>
> > mysql> SELECT pseudo FROM inscrit WHERE pseudo=(SELECT pseudo FROM inscrit
> > WHERE pseudo='joce');
> > Empty set (0.00 sec)
>
> Thank you for bug report. I take care of it.
> I'll inform you as soon as I fix it.
>
> [skip]
>
> --
> For technical support contracts, visit https://order.mysql.com/
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Mr. Oleksandr Byelkin
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
> /_/ /_/\_, /___/\___\_\___/ Lugansk, Ukraine
> <___/ www.mysql.com
>
> ------------------------------------------------------------ ---------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail bugs-thread12683@lists.mysql.com
> To unsubscribe, e-mail

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12688@lists.mysql.com
To unsubscribe, e-mail

Re: bug in subselect in mysql-4.1

am 10.10.2002 08:53:09 von Jocelyn Fournier

Hi,

In fact in this case SELECT pseudo FROM inscrit WHERE pseudo='joce' returns
only 1 result.
But even the IN syntax returns the wrong result.
BTW, the syntax with IN seems to work only if we put 2 times braces.

e.g. :
mysql> SELECT pseudo FROM inscrit WHERE pseudo IN SELECT pseudo FROM inscrit
WHERE pseudo='joce';
ERROR 1064: You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use near
'SELECT pseudo FROM inscrit WHERE pseudo='joce'' at line 1
mysql> SELECT pseudo FROM inscrit WHERE pseudo IN (SELECT pseudo FROM
inscrit WHERE pseudo='joce');
ERROR 1064: You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use near
'SELECT pseudo FROM inscrit WHERE pseudo='joce')' at line 1
mysql> SELECT pseudo FROM inscrit WHERE pseudo IN ((SELECT pseudo FROM
inscrit WHERE pseudo='joce'));
Empty set (0.09 sec)

And even IN seems to not accept more than 1 result from the subselect :

mysql> SELECT pseudo FROM inscrit WHERE pseudo IN ((SELECT pseudo FROM
inscrit WHERE pseudo LIKE 'joce%'));
ERROR 1240: Subselect returns more than 1 record

Regards,
Jocelyn

----- Original Message -----
From: "Marc Lazzaro"
To:
Cc: ;
Sent: Thursday, October 10, 2002 8:44 AM
Subject: Re: bug in subselect in mysql-4.1

> Hi,
>
> I do not know very well MySQL syntax but it seems to me that this query
> cannot work as "pseudo" will never be equal to the list returned by the
> subselect. The correct syntax from my view should be:
>
> SELECT pseudo FROM inscrit WHERE pseudo IN SELECT pseudo FROM inscrit
> WHERE pseudo='joce');
>
> Am I wrong?
>
> Rgds
>
> Marc
>
> sanja@mysql.com wrote:
> >
> > Hi!
> >
> > On Wed, Oct 09, 2002 at 03:34:21PM +0200, Jocelyn Fournier wrote:
> >
> > [skip]
> >
> > > mysql> SELECT pseudo FROM inscrit WHERE pseudo=(SELECT pseudo FROM
inscrit
> > > WHERE pseudo='joce');
> > > Empty set (0.00 sec)
> >
> > Thank you for bug report. I take care of it.
> > I'll inform you as soon as I fix it.
> >
> > [skip]
> >
> > --
> > For technical support contracts, visit https://order.mysql.com/
> > __ ___ ___ ____ __
> > / |/ /_ __/ __/ __ \/ / Mr. Oleksandr Byelkin
> > / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
> > /_/ /_/\_, /___/\___\_\___/ Lugansk, Ukraine
> > <___/ www.mysql.com
> >
> > ------------------------------------------------------------ ---------
> > Before posting, please check:
> > http://www.mysql.com/manual.php (the manual)
> > http://lists.mysql.com/ (the list archive)
> >
> > To request this thread, e-mail bugs-thread12683@lists.mysql.com
> > To unsubscribe, e-mail
>

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12689@lists.mysql.com
To unsubscribe, e-mail

Re: bug in subselect in mysql-4.1

am 10.10.2002 13:13:53 von Sanja Belkin

Hi!

On Thu, Oct 10, 2002 at 08:53:09AM +0200, Jocelyn Fournier wrote:
> Hi,
>
> In fact in this case SELECT pseudo FROM inscrit WHERE pseudo='joce' returns
> only 1 result.
> But even the IN syntax returns the wrong result.
> BTW, the syntax with IN seems to work only if we put 2 times braces.
>

2 times braces makes single-value subselect from it (i.e. IN (expression)).
IN (SELECT ...) is not yet finished, but it can be rewriten via EXISTS syntax
(it is what I am trying to do internally).

[skip]

--
For technical support contracts, visit https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Belkin
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Lugansk, Ukraine
<___/ www.mysql.com

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12691@lists.mysql.com
To unsubscribe, e-mail

Re: bug in subselect in mysql-4.1

am 11.10.2002 16:04:39 von Sanja Byelkin

Hi!

On Wed, Oct 09, 2002 at 03:34:21PM +0200, Jocelyn Fournier wrote:
> Hi,
>
> Perhaps another bug :
>
> mysql> SELECT pseudo FROM inscrit WHERE pseudo=(SELECT pseudo FROM inscrit
> WHERE pseudo='joce');
> Empty set (0.00 sec)

Thank you for your bug report. Bug is fixed now. Following is patch for it
fixing:

diff -Nrc a/sql/item_subselect.cc b/sql/item_subselect.cc
*** a/sql/item_subselect.cc Fri Oct 11 17:02:41 2002
--- b/sql/item_subselect.cc Fri Oct 11 17:02:41 2002
***************
*** 150,155 ****
--- 150,157 ----
assign_null();
return 0;
}
+ // Assign temporary buffer with stored value
+ str_value.set(string_value, 0, string_value.length());
return &str_value;
}

diff -Nrc a/sql/item_subselect.h b/sql/item_subselect.h
*** a/sql/item_subselect.h Fri Oct 11 17:02:41 2002
--- b/sql/item_subselect.h Fri Oct 11 17:02:41 2002
***************
*** 80,89 ****
class Item_singleval_subselect :public Item_subselect
{
protected:
! longlong int_value; /* here stored integer value of this item */
! double real_value; /* here stored real value of this item */
enum Item_result res_type; /* type of results */
!
public:
Item_singleval_subselect(THD *thd, st_select_lex *select_lex);
Item_singleval_subselect(Item_singleval_subselect *item):
--- 80,95 ----
class Item_singleval_subselect :public Item_subselect
{
protected:
! longlong int_value; /* Here stored integer value of this item */
! double real_value; /* Here stored real value of this item */
! /*
! Here stored string value of this item.
! (str_value used only as temporary buffer, because it can be changed
! by Item::save_field)
! */
! String string_value;
enum Item_result res_type; /* type of results */
!
public:
Item_singleval_subselect(THD *thd, st_select_lex *select_lex);
Item_singleval_subselect(Item_singleval_subselect *item):
***************
*** 91,96 ****
--- 97,103 ----
{
int_value= item->int_value;
real_value= item->real_value;
+ string_value.set(item->string_value, 0, item->string_value.length());
max_length= item->max_length;
decimals= item->decimals;
res_type= item->res_type;
diff -Nrc a/sql/sql_class.cc b/sql/sql_class.cc
*** a/sql/sql_class.cc Fri Oct 11 17:02:41 2002
--- b/sql/sql_class.cc Fri Oct 11 17:02:41 2002
***************
*** 884,892 ****
it->decimals= val_item->decimals;
it->binary= val_item->binary;
it->int_value= val_item->val_int();
! String *s= val_item->val_str(&it->str_value);
! if (s != &it->str_value)
! it->str_value.set(*s, 0, s->length());
it->res_type= val_item->result_type();
}
it->assigned(1);
--- 884,897 ----
it->decimals= val_item->decimals;
it->binary= val_item->binary;
it->int_value= val_item->val_int();
! String *s= val_item->val_str(&it->string_value);
! if (s != &it->string_value)
! {
! it->string_value.set(*s, 0, s->length());
! }
! // TODO: remove when correct charset handling appeared for Item
! it->str_value.set(*s, 0, s->length()); // store charset
!
it->res_type= val_item->result_type();
}
it->assigned(1);

--
For technical support contracts, visit https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Oleksandr Byelkin
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Lugansk, Ukraine
<___/ www.mysql.com

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12697@lists.mysql.com
To unsubscribe, e-mail

Re: bug in subselect in mysql-4.1

am 11.10.2002 23:15:28 von Jocelyn Fournier

Hi,

Another (small) bug :

mysql> SELECT pseudo FROM inscrit WHERE pseudo=(SELECT pseudo,email FROM
inscrit WHERE pseudo='joce');
ERROR 1239: Subselect returns more than 1 field

This is ok.

mysql> SELECT pseudo FROM inscrit WHERE pseudo=(SELECT * FROM inscrit WHERE
pseudo='joce');
+--------+
| pseudo |
+--------+
| joce |
+--------+
1 row in set (0.00 sec)

This should return ERROR 1239 too ?

Regards,
Jocelyn

----- Original Message -----
From: "Sanja Byelkin"
To: "Jocelyn Fournier"
Cc:
Sent: Friday, October 11, 2002 4:04 PM
Subject: Re: bug in subselect in mysql-4.1

> Hi!
>
> On Wed, Oct 09, 2002 at 03:34:21PM +0200, Jocelyn Fournier wrote:
> > Hi,
> >
> > Perhaps another bug :
> >
> > mysql> SELECT pseudo FROM inscrit WHERE pseudo=(SELECT pseudo FROM
inscrit
> > WHERE pseudo='joce');
> > Empty set (0.00 sec)
>
> Thank you for your bug report. Bug is fixed now. Following is patch for it
> fixing:
>
> diff -Nrc a/sql/item_subselect.cc b/sql/item_subselect.cc
> *** a/sql/item_subselect.cc Fri Oct 11 17:02:41 2002
> --- b/sql/item_subselect.cc Fri Oct 11 17:02:41 2002
> ***************
> *** 150,155 ****
> --- 150,157 ----
> assign_null();
> return 0;
> }
> + // Assign temporary buffer with stored value
> + str_value.set(string_value, 0, string_value.length());
> return &str_value;
> }
>
> diff -Nrc a/sql/item_subselect.h b/sql/item_subselect.h
> *** a/sql/item_subselect.h Fri Oct 11 17:02:41 2002
> --- b/sql/item_subselect.h Fri Oct 11 17:02:41 2002
> ***************
> *** 80,89 ****
> class Item_singleval_subselect :public Item_subselect
> {
> protected:
> ! longlong int_value; /* here stored integer value of this item */
> ! double real_value; /* here stored real value of this item */
> enum Item_result res_type; /* type of results */
> !
> public:
> Item_singleval_subselect(THD *thd, st_select_lex *select_lex);
> Item_singleval_subselect(Item_singleval_subselect *item):
> --- 80,95 ----
> class Item_singleval_subselect :public Item_subselect
> {
> protected:
> ! longlong int_value; /* Here stored integer value of this item */
> ! double real_value; /* Here stored real value of this item */
> ! /*
> ! Here stored string value of this item.
> ! (str_value used only as temporary buffer, because it can be changed
> ! by Item::save_field)
> ! */
> ! String string_value;
> enum Item_result res_type; /* type of results */
> !
> public:
> Item_singleval_subselect(THD *thd, st_select_lex *select_lex);
> Item_singleval_subselect(Item_singleval_subselect *item):
> ***************
> *** 91,96 ****
> --- 97,103 ----
> {
> int_value= item->int_value;
> real_value= item->real_value;
> + string_value.set(item->string_value, 0,
item->string_value.length());
> max_length= item->max_length;
> decimals= item->decimals;
> res_type= item->res_type;
> diff -Nrc a/sql/sql_class.cc b/sql/sql_class.cc
> *** a/sql/sql_class.cc Fri Oct 11 17:02:41 2002
> --- b/sql/sql_class.cc Fri Oct 11 17:02:41 2002
> ***************
> *** 884,892 ****
> it->decimals= val_item->decimals;
> it->binary= val_item->binary;
> it->int_value= val_item->val_int();
> ! String *s= val_item->val_str(&it->str_value);
> ! if (s != &it->str_value)
> ! it->str_value.set(*s, 0, s->length());
> it->res_type= val_item->result_type();
> }
> it->assigned(1);
> --- 884,897 ----
> it->decimals= val_item->decimals;
> it->binary= val_item->binary;
> it->int_value= val_item->val_int();
> ! String *s= val_item->val_str(&it->string_value);
> ! if (s != &it->string_value)
> ! {
> ! it->string_value.set(*s, 0, s->length());
> ! }
> ! // TODO: remove when correct charset handling appeared for Item
> ! it->str_value.set(*s, 0, s->length()); // store charset
> !
> it->res_type= val_item->result_type();
> }
> it->assigned(1);
>
>
> --
> For technical support contracts, visit https://order.mysql.com/
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Mr. Oleksandr Byelkin
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
> /_/ /_/\_, /___/\___\_\___/ Lugansk, Ukraine
> <___/ www.mysql.com
>
> ------------------------------------------------------------ ---------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail bugs-thread12697@lists.mysql.com
> To unsubscribe, e-mail
>
>

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12704@lists.mysql.com
To unsubscribe, e-mail

Re: bug in subselect in mysql-4.1

am 12.10.2002 00:15:07 von Sanja Byelkin

Hi!

On Fri, Oct 11, 2002 at 11:15:28PM +0200, Jocelyn Fournier wrote:
> Hi,
>
> Another (small) bug :
>
> mysql> SELECT pseudo FROM inscrit WHERE pseudo=(SELECT pseudo,email FROM
> inscrit WHERE pseudo='joce');
> ERROR 1239: Subselect returns more than 1 field
>
> This is ok.
>
> mysql> SELECT pseudo FROM inscrit WHERE pseudo=(SELECT * FROM inscrit WHERE
> pseudo='joce');
> +--------+
> | pseudo |
> +--------+
> | joce |
> +--------+
> 1 row in set (0.00 sec)
>
> This should return ERROR 1239 too ?

Yes, you are right! Thank you for your bug report again :)
It is fix for it:

diff -Nrc a/sql/item_subselect.cc b/sql/item_subselect.cc
*** a/sql/item_subselect.cc Sat Oct 12 01:12:51 2002
--- b/sql/item_subselect.cc Sat Oct 12 01:12:51 2002
***************
*** 81,95 ****

bool Item_subselect::fix_fields(THD *thd, TABLE_LIST *tables, Item **ref)
{
- // Is it one field subselect?
- if (engine->cols() > max_columns)
- {
- my_message(ER_SUBSELECT_NO_1_COL, ER(ER_SUBSELECT_NO_1_COL), MYF(0));
- return 1;
- }
int res= engine->prepare();
if (!res)
fix_length_and_dec();
return res;
}

--- 81,97 ----

bool Item_subselect::fix_fields(THD *thd, TABLE_LIST *tables, Item **ref)
{
int res= engine->prepare();
if (!res)
+ {
+ // Is it one field subselect?
+ if (engine->cols() > max_columns)
+ {
+ my_message(ER_SUBSELECT_NO_1_COL, ER(ER_SUBSELECT_NO_1_COL), MYF(0));
+ return 1;
+ }
fix_length_and_dec();
+ }
return res;
}

[skip]

--
For technical support contracts, visit https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Oleksandr Byelkin
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Lugansk, Ukraine
<___/ www.mysql.com

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12705@lists.mysql.com
To unsubscribe, e-mail

Re: bug in subselect in mysql-4.1

am 12.10.2002 00:42:00 von Jocelyn Fournier

Hi,

Another bug, while playing with explain this time ;)

mysql> EXPLAIN SELECT pseudo,(SELECT email FROM inscrit WHERE pseudo=(SELECT
pseudo FROM inscrit WHERE pseudo='joce')) FROM inscrit WHERE pseudo=(SELECT
pseudo FROM inscrit WHERE pseudo='joce');
ERROR 2013: Lost connection to MySQL server during query

Stack trace :

0x809f3d7 handle_segfault(int) + 487
0x828552a pthread_sighandler + 138
0x80caf08 make_join_statistics(JOIN*, st_table_list*, Item*,
st_dynamic_array*) + 1672
0x80c6498 JOIN::optimize() + 328
0x8081adf subselect_single_select_engine::exec() + 223
0x80812bd Item_singleval_subselect::val_str(String*) + 29
0x804c850 Item::save_in_field(Field*) + 320
0x80d6aa8 store_key_const_item::copy() + 56
0x80d2348 join_read_const(st_join_table*) + 344
0x80d2005 join_read_const_table(st_join_table*, st_position*) + 69
0x80cb36d make_join_statistics(JOIN*, st_table_list*, Item*,
st_dynamic_array*) + 2797
0x80c6498 JOIN::optimize() + 328
0x80c7e78 mysql_select(THD*, st_table_list*, List&, Item*, st_order*,
st_order*, Item*, st_order*, unsigned long, select_result*,
st_select_lex_unit*, st_select_lex*) + 184
0x80ca877 mysql_explain_select(THD*, st_select_lex*, char const*,
select_result*) + 135
0x80a980c mysql_execute_command(THD*) + 876
0x80ae76f mysql_parse(THD*, char*, unsigned) + 159
0x80a8554 dispatch_command(enum_server_command, THD*, char*, unsigned) + 916
0x80a81ad do_command(THD*) + 109
0x80a7a78 handle_one_connection(void*) + 904
0x8282b1e pthread_start_thread + 222
0x82b993a thread_start + 4

BTW, I'm just wondering if the following is really a non valid sql query :

mysql> SELECT pseudo,(SELECT pseudo,email FROM inscrit WHERE pseudo='joce')
FROM inscrit WHERE pseudo='joce';
ERROR 1239: Subselect returns more than 1 field

Regards,
Jocelyn

----- Original Message -----
From: "Sanja Byelkin"
To: "Jocelyn Fournier"
Cc:
Sent: Saturday, October 12, 2002 12:15 AM
Subject: Re: bug in subselect in mysql-4.1

> Hi!
>
> On Fri, Oct 11, 2002 at 11:15:28PM +0200, Jocelyn Fournier wrote:
> > Hi,
> >
> > Another (small) bug :
> >
> > mysql> SELECT pseudo FROM inscrit WHERE pseudo=(SELECT pseudo,email FROM
> > inscrit WHERE pseudo='joce');
> > ERROR 1239: Subselect returns more than 1 field
> >
> > This is ok.
> >
> > mysql> SELECT pseudo FROM inscrit WHERE pseudo=(SELECT * FROM inscrit
WHERE
> > pseudo='joce');
> > +--------+
> > | pseudo |
> > +--------+
> > | joce |
> > +--------+
> > 1 row in set (0.00 sec)
> >
> > This should return ERROR 1239 too ?
>
> Yes, you are right! Thank you for your bug report again :)
> It is fix for it:
>
> diff -Nrc a/sql/item_subselect.cc b/sql/item_subselect.cc
> *** a/sql/item_subselect.cc Sat Oct 12 01:12:51 2002
> --- b/sql/item_subselect.cc Sat Oct 12 01:12:51 2002
> ***************
> *** 81,95 ****
>
> bool Item_subselect::fix_fields(THD *thd, TABLE_LIST *tables, Item
**ref)
> {
> - // Is it one field subselect?
> - if (engine->cols() > max_columns)
> - {
> - my_message(ER_SUBSELECT_NO_1_COL, ER(ER_SUBSELECT_NO_1_COL),
MYF(0));
> - return 1;
> - }
> int res= engine->prepare();
> if (!res)
> fix_length_and_dec();
> return res;
> }
>
> --- 81,97 ----
>
> bool Item_subselect::fix_fields(THD *thd, TABLE_LIST *tables, Item
**ref)
> {
> int res= engine->prepare();
> if (!res)
> + {
> + // Is it one field subselect?
> + if (engine->cols() > max_columns)
> + {
> + my_message(ER_SUBSELECT_NO_1_COL, ER(ER_SUBSELECT_NO_1_COL),
MYF(0));
> + return 1;
> + }
> fix_length_and_dec();
> + }
> return res;
> }
>
>
>
> [skip]
>
> --
> For technical support contracts, visit https://order.mysql.com/
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Mr. Oleksandr Byelkin
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
> /_/ /_/\_, /___/\___\_\___/ Lugansk, Ukraine
> <___/ www.mysql.com
>

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12706@lists.mysql.com
To unsubscribe, e-mail

Re: bug in subselect in mysql-4.1

am 12.10.2002 00:49:56 von Sanja Byelkin

Re: bug in subselect in mysql-4.1

am 12.10.2002 02:33:10 von Jocelyn Fournier

Hi,

I think I've hit one other bug ;)

How-to-repeat :

CREATE TABLE `searchconthardwarefr3` (
`topic` mediumint(8) unsigned NOT NULL default '0',
`date` date NOT NULL default '0000-00-00',
`pseudo` varchar(35) character set latin1 NOT NULL default '',
PRIMARY KEY (`pseudo`,`date`,`topic`),
KEY `topic` (`topic`)
) TYPE=MyISAM ROW_FORMAT=DYNAMIC

INSERT INTO searchconthardwarefr3 (topic,date,pseudo) VALUES
('43506','2002-10-02','joce'),('40143','2002-08-03','joce');

mysql> SELECT DISTINCT date FROM searchconthardwarefr3 WHERE
date='2002-08-03';
+------------+
| date |
+------------+
| 2002-08-03 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM searchconthardwarefr3 WHERE pseudo='joce' AND date >=
(SELECT DISTINCT date FROM searchconthardwarefr3 WHERE date='2002-08-03');
+-------+------------+--------+
| topic | date | pseudo |
+-------+------------+--------+
| 43506 | 2002-10-02 | joce |
+-------+------------+--------+
1 row in set (0.01 sec)

Bug, it should return two result.

However :

mysql> SELECT * FROM searchconthardwarefr3 WHERE pseudo='joce' AND date >=
(SELECT date FROM searchconthardwarefr3 WHERE date='2002-08-03');
+-------+------------+--------+
| topic | date | pseudo |
+-------+------------+--------+
| 40143 | 2002-08-03 | joce |
| 43506 | 2002-10-02 | joce |
+-------+------------+--------+
2 rows in set (0.00 sec)

is correct.

mysql> SELECT * FROM searchconthardwarefr3 WHERE pseudo='joce' AND date >=
'2002-08-03';
+-------+------------+--------+
| topic | date | pseudo |
+-------+------------+--------+
| 40143 | 2002-08-03 | joce |
| 43506 | 2002-10-02 | joce |
+-------+------------+--------+
2 rows in set (0.00 sec)

Regards,
Jocelyn
----- Original Message -----
From: "Sanja Byelkin"
To: "Jocelyn Fournier"
Cc:
Sent: Saturday, October 12, 2002 12:49 AM
Subject: Re: bug in subselect in mysql-4.1

> Hi!
>
> On Sat, Oct 12, 2002 at 12:42:00AM +0200, Jocelyn Fournier wrote:
> > Hi,
> >
> > Another bug, while playing with explain this time ;)
> >
> > mysql> EXPLAIN SELECT pseudo,(SELECT email FROM inscrit WHERE
pseudo=(SELECT
> > pseudo FROM inscrit WHERE pseudo='joce')) FROM inscrit WHERE
pseudo=(SELECT
> > pseudo FROM inscrit WHERE pseudo='joce');
> > ERROR 2013: Lost connection to MySQL server during query
>
> [skip]
>
> Thank you for bug report! Next morning I'll take care of it (01:47 here :)
>
> > BTW, I'm just wondering if the following is really a non valid sql query
:
> >
> > mysql> SELECT pseudo,(SELECT pseudo,email FROM inscrit WHERE
pseudo='joce')
> > FROM inscrit WHERE pseudo='joce';
> > ERROR 1239: Subselect returns more than 1 field
>
> Now it is implementation limit, i.e. multi-fields subselects is not
> implementing yet.
>
> --
> For technical support contracts, visit https://order.mysql.com/
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Mr. Oleksandr Byelkin
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
> /_/ /_/\_, /___/\___\_\___/ Lugansk, Ukraine
> <___/ www.mysql.com
>

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12709@lists.mysql.com
To unsubscribe, e-mail

Re: bug in subselect in mysql-4.1

am 13.10.2002 13:35:11 von Sanja Byelkin

Hi!

On Sat, Oct 12, 2002 at 12:42:00AM +0200, Jocelyn Fournier wrote:
> Hi,
>
> Another bug, while playing with explain this time ;)
>
> mysql> EXPLAIN SELECT pseudo,(SELECT email FROM inscrit WHERE pseudo=(SELECT
> pseudo FROM inscrit WHERE pseudo='joce')) FROM inscrit WHERE pseudo=(SELECT
> pseudo FROM inscrit WHERE pseudo='joce');
> ERROR 2013: Lost connection to MySQL server during query

[skip]

Thank you for yours good bugreports. This bug is fixed now:

diff -Nrc a/sql/item_subselect.cc b/sql/item_subselect.cc
*** a/sql/item_subselect.cc Sun Oct 13 14:33:58 2002
--- b/sql/item_subselect.cc Sun Oct 13 14:33:58 2002
***************
*** 212,218 ****
select_subselect *result,
Item_subselect *item):
subselect_engine(thd, item, result),
! executed(0), optimized(0)
{
select_lex= select;
SELECT_LEX_UNIT *unit= select_lex->master_unit();
--- 212,218 ----
select_subselect *result,
Item_subselect *item):
subselect_engine(thd, item, result),
! prepared(0), optimized(0), executed(0)
{
select_lex= select;
SELECT_LEX_UNIT *unit= select_lex->master_unit();
***************
*** 251,256 ****
--- 251,259 ----

int subselect_single_select_engine::prepare()
{
+ if (prepared)
+ return 0;
+ prepared= 1;
SELECT_LEX *save_select= thd->lex.select;
thd->lex.select= select_lex;
if(join->prepare((TABLE_LIST*) select_lex->table_list.first,
diff -Nrc a/sql/item_subselect.h b/sql/item_subselect.h
*** a/sql/item_subselect.h Sun Oct 13 14:33:58 2002
--- b/sql/item_subselect.h Sun Oct 13 14:33:58 2002
***************
*** 179,186 ****

class subselect_single_select_engine: public subselect_engine
{
! my_bool executed; /* simple subselect is executed */
my_bool optimized; /* simple subselect is optimized */
st_select_lex *select_lex; /* corresponding select_lex */
JOIN * join; /* corresponding JOIN structure */
public:
--- 179,187 ----

class subselect_single_select_engine: public subselect_engine
{
! my_bool prepared; /* simple subselect is prepared */
my_bool optimized; /* simple subselect is optimized */
+ my_bool executed; /* simple subselect is executed */
st_select_lex *select_lex; /* corresponding select_lex */
JOIN * join; /* corresponding JOIN structure */
public:
diff -Nrc a/sql/sql_lex.cc b/sql/sql_lex.cc
*** a/sql/sql_lex.cc Sun Oct 13 14:33:58 2002
--- b/sql/sql_lex.cc Sun Oct 13 14:33:58 2002
***************
*** 944,950 ****
global_parameters= this;
select_limit_cnt= HA_POS_ERROR;
offset_limit_cnt= 0;
! optimized= 0;
item= 0;
}

--- 944,950 ----
global_parameters= this;
select_limit_cnt= HA_POS_ERROR;
offset_limit_cnt= 0;
! prepared= optimized= 0;
item= 0;
}

diff -Nrc a/sql/sql_lex.h b/sql/sql_lex.h
*** a/sql/sql_lex.h Sun Oct 13 14:33:58 2002
--- b/sql/sql_lex.h Sun Oct 13 14:33:58 2002
***************
*** 227,232 ****
--- 227,233 ----
select_result *result;
int res;
bool describe, found_rows_for_union,
+ prepared, //prepare phase already performed for UNION (unit)
optimized; // optimize phase already performed for UNION (unit)
public:
/*
diff -Nrc a/sql/sql_union.cc b/sql/sql_union.cc
*** a/sql/sql_union.cc Sun Oct 13 14:33:58 2002
--- b/sql/sql_union.cc Sun Oct 13 14:33:58 2002
***************
*** 104,114 ****
typedef JOIN * JOIN_P;
int st_select_lex_unit::prepare(THD *thd, select_result *result)
{
describe=(first_select()->options & SELECT_DESCRIBE) ? 1 : 0;
res= 0;
found_rows_for_union= false;
TMP_TABLE_PARAM tmp_table_param;
- DBUG_ENTER("st_select_lex_unit::prepare");
this->thd= thd;
this->result= result;
SELECT_LEX *lex_select_save= thd->lex.select;
--- 104,119 ----
typedef JOIN * JOIN_P;
int st_select_lex_unit::prepare(THD *thd, select_result *result)
{
+ DBUG_ENTER("st_select_lex_unit::prepare");
+
+ if (prepared)
+ DBUG_RETURN(0);
+ prepared= 1;
+
describe=(first_select()->options & SELECT_DESCRIBE) ? 1 : 0;
res= 0;
found_rows_for_union= false;
TMP_TABLE_PARAM tmp_table_param;
this->thd= thd;
this->result= result;
SELECT_LEX *lex_select_save= thd->lex.select;

--
For technical support contracts, visit https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Oleksandr Byelkin
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Lugansk, Ukraine
<___/ www.mysql.com

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12711@lists.mysql.com
To unsubscribe, e-mail

Re: bug in subselect in mysql-4.1

am 14.10.2002 01:03:12 von Jocelyn Fournier

Hi,

Another bug, with UNION this time :)

How-to-repeat :

mysql> SELECT (SELECT 1) UNION SELECT (SELECT 2);
ERROR 1222: The used SELECT statements have a different number of columns

Regards,
Joce

----- Original Message -----
From: "Sanja Byelkin"
To: "Jocelyn Fournier"
Cc:
Sent: Sunday, October 13, 2002 1:35 PM
Subject: Re: bug in subselect in mysql-4.1

> Hi!
>
> On Sat, Oct 12, 2002 at 12:42:00AM +0200, Jocelyn Fournier wrote:
> > Hi,
> >
> > Another bug, while playing with explain this time ;)
> >
> > mysql> EXPLAIN SELECT pseudo,(SELECT email FROM inscrit WHERE
pseudo=(SELECT
> > pseudo FROM inscrit WHERE pseudo='joce')) FROM inscrit WHERE
pseudo=(SELECT
> > pseudo FROM inscrit WHERE pseudo='joce');
> > ERROR 2013: Lost connection to MySQL server during query
>
> [skip]
>
> Thank you for yours good bugreports. This bug is fixed now:
>
> diff -Nrc a/sql/item_subselect.cc b/sql/item_subselect.cc
> *** a/sql/item_subselect.cc Sun Oct 13 14:33:58 2002
> --- b/sql/item_subselect.cc Sun Oct 13 14:33:58 2002
> ***************
> *** 212,218 ****
> select_subselect *result,
> Item_subselect *item):
> subselect_engine(thd, item, result),
> ! executed(0), optimized(0)
> {
> select_lex= select;
> SELECT_LEX_UNIT *unit= select_lex->master_unit();
> --- 212,218 ----
> select_subselect *result,
> Item_subselect *item):
> subselect_engine(thd, item, result),
> ! prepared(0), optimized(0), executed(0)
> {
> select_lex= select;
> SELECT_LEX_UNIT *unit= select_lex->master_unit();
> ***************
> *** 251,256 ****
> --- 251,259 ----
>
> int subselect_single_select_engine::prepare()
> {
> + if (prepared)
> + return 0;
> + prepared= 1;
> SELECT_LEX *save_select= thd->lex.select;
> thd->lex.select= select_lex;
> if(join->prepare((TABLE_LIST*) select_lex->table_list.first,
> diff -Nrc a/sql/item_subselect.h b/sql/item_subselect.h
> *** a/sql/item_subselect.h Sun Oct 13 14:33:58 2002
> --- b/sql/item_subselect.h Sun Oct 13 14:33:58 2002
> ***************
> *** 179,186 ****
>
> class subselect_single_select_engine: public subselect_engine
> {
> ! my_bool executed; /* simple subselect is executed */
> my_bool optimized; /* simple subselect is optimized */
> st_select_lex *select_lex; /* corresponding select_lex */
> JOIN * join; /* corresponding JOIN structure */
> public:
> --- 179,187 ----
>
> class subselect_single_select_engine: public subselect_engine
> {
> ! my_bool prepared; /* simple subselect is prepared */
> my_bool optimized; /* simple subselect is optimized */
> + my_bool executed; /* simple subselect is executed */
> st_select_lex *select_lex; /* corresponding select_lex */
> JOIN * join; /* corresponding JOIN structure */
> public:
> diff -Nrc a/sql/sql_lex.cc b/sql/sql_lex.cc
> *** a/sql/sql_lex.cc Sun Oct 13 14:33:58 2002
> --- b/sql/sql_lex.cc Sun Oct 13 14:33:58 2002
> ***************
> *** 944,950 ****
> global_parameters= this;
> select_limit_cnt= HA_POS_ERROR;
> offset_limit_cnt= 0;
> ! optimized= 0;
> item= 0;
> }
>
> --- 944,950 ----
> global_parameters= this;
> select_limit_cnt= HA_POS_ERROR;
> offset_limit_cnt= 0;
> ! prepared= optimized= 0;
> item= 0;
> }
>
> diff -Nrc a/sql/sql_lex.h b/sql/sql_lex.h
> *** a/sql/sql_lex.h Sun Oct 13 14:33:58 2002
> --- b/sql/sql_lex.h Sun Oct 13 14:33:58 2002
> ***************
> *** 227,232 ****
> --- 227,233 ----
> select_result *result;
> int res;
> bool describe, found_rows_for_union,
> + prepared, //prepare phase already performed for UNION (unit)
> optimized; // optimize phase already performed for UNION (unit)
> public:
> /*
> diff -Nrc a/sql/sql_union.cc b/sql/sql_union.cc
> *** a/sql/sql_union.cc Sun Oct 13 14:33:58 2002
> --- b/sql/sql_union.cc Sun Oct 13 14:33:58 2002
> ***************
> *** 104,114 ****
> typedef JOIN * JOIN_P;
> int st_select_lex_unit::prepare(THD *thd, select_result *result)
> {
> describe=(first_select()->options & SELECT_DESCRIBE) ? 1 : 0;
> res= 0;
> found_rows_for_union= false;
> TMP_TABLE_PARAM tmp_table_param;
> - DBUG_ENTER("st_select_lex_unit::prepare");
> this->thd= thd;
> this->result= result;
> SELECT_LEX *lex_select_save= thd->lex.select;
> --- 104,119 ----
> typedef JOIN * JOIN_P;
> int st_select_lex_unit::prepare(THD *thd, select_result *result)
> {
> + DBUG_ENTER("st_select_lex_unit::prepare");
> +
> + if (prepared)
> + DBUG_RETURN(0);
> + prepared= 1;
> +
> describe=(first_select()->options & SELECT_DESCRIBE) ? 1 : 0;
> res= 0;
> found_rows_for_union= false;
> TMP_TABLE_PARAM tmp_table_param;
> this->thd= thd;
> this->result= result;
> SELECT_LEX *lex_select_save= thd->lex.select;
>
>
> --
> For technical support contracts, visit https://order.mysql.com/
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Mr. Oleksandr Byelkin
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
> /_/ /_/\_, /___/\___\_\___/ Lugansk, Ukraine
> <___/ www.mysql.com
>
> ------------------------------------------------------------ ---------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail bugs-thread12711@lists.mysql.com
> To unsubscribe, e-mail
>
>

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12713@lists.mysql.com
To unsubscribe, e-mail

Re: bug in subselect in mysql-4.1

am 14.10.2002 13:38:54 von Sanja Byelkin

On Mon, Oct 14, 2002 at 01:03:12AM +0200, joce wrote:
> Hi,
>
> Another bug, with UNION this time :)
>
> How-to-repeat :
>
> mysql> SELECT (SELECT 1) UNION SELECT (SELECT 2);
> ERROR 1222: The used SELECT statements have a different number of columns

Thank you again for good bug report (I also do not forget about DISTINCT
bug). This bug was in create temporary field of subselect routine, here is
its fix:

diff -Nrc a/sql/sql_select.cc b/sql/sql_select.cc
*** a/sql/sql_select.cc Mon Oct 14 14:37:11 2002
--- b/sql/sql_select.cc Mon Oct 14 14:37:11 2002
***************
*** 3605,3610 ****
--- 3605,3611 ----
case Item::COND_ITEM:
case Item::FIELD_AVG_ITEM:
case Item::FIELD_STD_ITEM:
+ case Item::SUBSELECT_ITEM:
/* The following can only happen with 'CREATE TABLE ... SELECT' */
case Item::INT_ITEM:
case Item::REAL_ITEM:

--
For technical support contracts, visit https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Oleksandr Byelkin
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Lugansk, Ukraine
<___/ www.mysql.com

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12718@lists.mysql.com
To unsubscribe, e-mail

Re: bug in subselect in mysql-4.1

am 23.10.2002 22:39:47 von Sanja Byelkin

Hi!

On Sat, Oct 12, 2002 at 02:33:10AM +0200, Jocelyn Fournier wrote:
> Hi,
>
> I think I've hit one other bug ;)
>
> How-to-repeat :
>
> CREATE TABLE `searchconthardwarefr3` (
> `topic` mediumint(8) unsigned NOT NULL default '0',
> `date` date NOT NULL default '0000-00-00',
> `pseudo` varchar(35) character set latin1 NOT NULL default '',
> PRIMARY KEY (`pseudo`,`date`,`topic`),
> KEY `topic` (`topic`)
> ) TYPE=MyISAM ROW_FORMAT=DYNAMIC
>
> INSERT INTO searchconthardwarefr3 (topic,date,pseudo) VALUES
> ('43506','2002-10-02','joce'),('40143','2002-08-03','joce');
>
> mysql> SELECT DISTINCT date FROM searchconthardwarefr3 WHERE
> date='2002-08-03';
> +------------+
> | date |
> +------------+
> | 2002-08-03 |
> +------------+
> 1 row in set (0.00 sec)
>
> mysql> SELECT * FROM searchconthardwarefr3 WHERE pseudo='joce' AND date >=
> (SELECT DISTINCT date FROM searchconthardwarefr3 WHERE date='2002-08-03');
> +-------+------------+--------+
> | topic | date | pseudo |
> +-------+------------+--------+
> | 43506 | 2002-10-02 | joce |
> +-------+------------+--------+
> 1 row in set (0.01 sec)
>
> Bug, it should return two result.

Thank you for good bug report. This bug is fixed:

diff -Nrc a/sql/item.h b/sql/item.h
*** a/sql/item.h Wed Oct 23 23:37:57 2002
--- b/sql/item.h Wed Oct 23 23:37:57 2002
***************
*** 71,76 ****
--- 71,77 ----
virtual double val_result() { return val(); }
virtual longlong val_int_result() { return val_int(); }
virtual String *str_result(String* tmp) { return val_str(tmp); }
+ virtual bool is_null_result() { return is_null(); }
virtual table_map used_tables() const { return (table_map) 0L; }
virtual bool basic_const_item() const { return 0; }
virtual Item *new_item() { return 0; } /* Only for const items */
***************
*** 124,129 ****
--- 125,131 ----
double val_result();
longlong val_int_result();
String *str_result(String* tmp);
+ bool is_null_result() { return result_field->is_null(); }
bool send(THD *thd, String *str_arg)
{
return result_field->send(thd,str_arg);
diff -Nrc a/sql/sql_class.cc b/sql/sql_class.cc
*** a/sql/sql_class.cc Wed Oct 23 23:37:57 2002
--- b/sql/sql_class.cc Wed Oct 23 23:37:57 2002
***************
*** 873,880 ****
Following val() call have to be first, because function AVG() & STD()
calculate value on it & determinate "is it NULL?".
*/
! it->real_value= val_item->val();
! if ((it->null_value= val_item->is_null()))
{
it->assign_null();
}
--- 873,880 ----
Following val() call have to be first, because function AVG() & STD()
calculate value on it & determinate "is it NULL?".
*/
! it->real_value= val_item->val_result();
! if ((it->null_value= val_item->is_null_result()))
{
it->assign_null();
}
***************
*** 883,890 ****
it->max_length= val_item->max_length;
it->decimals= val_item->decimals;
it->binary= val_item->binary;
! it->int_value= val_item->val_int();
! String *s= val_item->val_str(&it->string_value);
if (s != &it->string_value)
{
it->string_value.set(*s, 0, s->length());
--- 883,890 ----
it->max_length= val_item->max_length;
it->decimals= val_item->decimals;
it->binary= val_item->binary;
! it->int_value= val_item->val_int_result();
! String *s= val_item->str_result(&it->string_value);
if (s != &it->string_value)
{
it->string_value.set(*s, 0, s->length());

--
For technical support contracts, visit https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Oleksandr Byelkin
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Lugansk, Ukraine
<___/ www.mysql.com

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12842@lists.mysql.com
To unsubscribe, e-mail