MySQL Bugs: #9116: Subqueries as tables creating errors

Bug #9116	Subqueries as tables creating errors
Submitted:	11 Mar 2005 3:19	Modified:	20 Mar 2005 10:23
Reporter:	Kamran Lotfi	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.10	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
Did the syntax change for subqueries between version 4.1.7 and 4.1.10?

The following query works just fine in version 4.1.7, but does not have any value for the two variables in version 4.1.10.  The both work just fine  

set @date='2005-12-04';
set @merchant_id=2;

insert into rpt_customer_segmentation (week_of_date, merchant_id,category_name,spending_threshold, total_customers ,
gross_revenue , spending_per_visit, visits_per_week,visited_last_week,percent_visited_last_week)
select @date as week_of_date ,@merchant_id as merchant_id ,c.category_name,c.spending_threshold, c.total_customers,
 ifnull(sum(transaction_amount),0) as gross_revenue
,ifnull(ifnull(sum(transaction_amount),0)/count(distinct transaction_id),0) as spending_per_visit
,ifnull(count(distinct t.account_Id)/count(am.account_id),0) as visits_per_week
,count(distinct t.account_id) as visited_last_week,count(distinct t.account_id)/c.total_customers*100 as percent_visited_last_week
from customer_categories c 
left join  account_merchant am on (am.merchant_customer_category_id=c.merchant_customer_category_id)
left join (select * from transaction where transaction_date between @date and date_add(@date, interval 7 DAY))  as t on (t.account_id=am.account_id)
group by c.category_name;

How to repeat:
I have tried writing a simple query to duplicate it, but can't.  I don't need the insert statement to make it work though.  If I just set the variables and execute the select if will return a value of 0 and a date of 0:00..

BTW, these are INNODB tables that is why I submitted the bug here.

Suggested fix:
Not sure what is going on here.

Hi!

I am changing this to a 'MySQL Server' bug, since the bug apparently is in the MySQL interpreter.

What do you mean by that the two variables 'do not get any value' in 4.1.10?

Please post a 4.1.7 and 4.1.10 mysql interactive SQL client session that demonstrates the problem.

Regards,

Heikki

The data inserted for week_of_date and merchant_id are null in the table after the insert.  When I use another approach instead of having a subquery in the select statement, the problem goes away.  Also if I just execute the select statement without insert, I still have the same problem so the issue is in the select statement.

BTW, what do you mean by an interactive client session?

Hi!

By an interactive session, we mean something similar to the following:

shell> mysql
mysql> all your queries;
mysql> and steps you took to replicate;
mysql> this issue;

Also, the create table statement of your table will also be very helpful to us.

Thanks.

creates the tables and shows the null fields

Attachment: error.sql (application/octet-stream, text), 2.10 KiB.

I just added a script that creates the tables, inserts values and finally has the select statement I was talking about with null values.  When this script is run on version 4.1.10, the values for week_of_date and merchant_id in the results from the query are null, even though they are specified in the variables above. 

If you remove the subquery in the select statement and create a temporary table with it instead.  Then join the temporary table in the select statement, the results are as expected.  The first two columns return the values set above.

BTW, I ran this script using the MYSQL Control Center Query Window.  It has the same error with the Query Browser though. 

Finally for you reference, here is the script in text form just in case you can't read the attached Windows files

Please let me know if you need anything else.  Hope this helps.  

Thanks,

Kamran Lotfi

drop table if exists account;

create table account_merchant(
account_id varchar(100) not null,
merchant_id int unsigned not null,
merchant_customer_category_id int unsigned not null,
point_balance int unsigned ,
dollar_balance float(10,2) ,
email_opt_in bit default 1,
direct_mail_opt_in bit default 1,
mailing_list_opt_in bit default 1,
first_visit_date datetime not null ,
last_visit_date datetime,
customer_category_spending float(10,2),
customer_category_visits int unsigned,
index (merchant_id),
index (merchant_customer_category_Id),
primary key (account_id,merchant_id)
) type=INNODB;

drop table if exists transaction;

create table transaction (
transaction_id bigint unsigned auto_increment not null primary key,
transaction_quantity int not null,
transaction_date datetime,
merchant_id int unsigned not null,
mobile_device_id varchar(100) not null,
account_id varchar(100) not null,
transaction_type_id int unsigned not null,
transaction_amount  float(10,2) not null 
) type=INNODB;

delete from account_merchant;
insert into account_merchant (account_id,merchant_id,merchant_customer_category_id)
values('0001',1,1),
('0002',1,2),
('0003',1,2);

delete from transaction;
insert into transaction(transaction_id,transaction_quantity,transaction_date,merchant_id,mobile_device_id,account_id,transaction_type_id,transaction_amount)
values(1,1,'2004-12-01',1,1,'0001',1,23.23)
,(2,1,'2004-12-01',1,1,'0002',1,23.23)
,(3,1,'2004-12-01',1,1,'0003',1,23.23)
,(4,1,'2004-12-01',1,1,'0001',1,23.23)
,(5,1,'2004-12-01',1,1,'0002',1,23.23)
,(6,1,'2004-12-01',1,1,'0003',1,23.23)
,(7,1,'2004-12-01',1,1,'0003',1,23.23);

set @rpt_date='2004-12-01';
set @merchant_id=1;

# insert regular values
select @rpt_date as week_of_date ,@merchant_id as merchant_id,t.account_id,
count(distinct transaction_id) as spending_per_visit
from account_merchant am 
left join (select * from transaction where transaction_date between @rpt_date and date_add(@rpt_date, interval 7 DAY) and merchant_id=@merchant_id ) as t on (t.account_id=am.account_id)
group by t.account_id;

Thank you for the bug report, this issue is already fixed on BK source:

    -> group by t.account_id;
+--------------+-------------+------------+--------------------+
| week_of_date | merchant_id | account_id | spending_per_visit |
+--------------+-------------+------------+--------------------+
| NULL         | NULL        | 0001       |                  2 |
| NULL         | NULL        | 0002       |                  2 |
| NULL         | NULL        | 0003       |                  3 |
+--------------+-------------+------------+--------------------+
3 rows in set (0.01 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.10-nt |
+-----------+
1 row in set (0.00 sec)

    -> group by t.account_id;
+--------------+-------------+------------+--------------------+
| week_of_date | merchant_id | account_id | spending_per_visit |
+--------------+-------------+------------+--------------------+
| 2004-12-01   | 1           | 0001       |                  2 |
| 2004-12-01   | 1           | 0002       |                  2 |
| 2004-12-01   | 1           | 0003       |                  3 |
+--------------+-------------+------------+--------------------+
3 rows in set (0.03 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 4.1.11-debug |
+--------------+