Bug #40275 My SQL Query Problem

• Submitted: 23 Oct 2008 5:13
• Modified: 23 Nov 2008 22:25
• Reporter: Maneesh Thareja
• Status: No Feedback
• Category: MySQL Server: General
• Severity: S3 (Non-critical)
• Version: 5.0.51a, 5.0.67
• OS: Linux
• Assigned to: Assigned Account
• CPU Architecture: Any

[23 Oct 2008 5:13] Maneesh Thareja

Description:
Following query is not returning any result.I am using My SQL 5.0.51a version.

Where as on another server where we have MySQL 4.1.13 is running properly.

Can anybody suggest.

SELECT DISTINCT 
CASE WHEN p.trading_name <> ''
AND p.user_id = u.id
THEN p.trading_name
ELSE u.trading_name1
END AS tradingName, u.id AS userid, u.user_type, bd.brand_id, p.retailer_flag, p.distributor_flag, p.save_flag, u.username, u.business_name, u.post_address1, u.post_address2, u.post_city, u.other_post_state, u.gen_email, u.gen_phone, u.user_type, u.approved, u.active_status, s.state_name, c.country_name, o.payment_status
FROM (
(
site_users u
INNER JOIN site_state AS s ON u.post_state = s.id
)
INNER JOIN site_country c ON u.post_country = c.id
)
LEFT JOIN (
(
(
(
(
(
site_products AS p
INNER JOIN site_boat_details AS bd ON p.id = bd.product_id
INNER JOIN site_brand AS b ON b.id = bd.brand_id
)
INNER JOIN site_model AS m ON m.id = bd.model_id
)
)
)
INNER JOIN site_product_invoice AS i ON p.id = i.product_id
)
INNER JOIN site_product_order AS o ON o.id = i.order_id
) ON u.id = p.user_id
WHERE 1 
AND p.product_type = 'boat'
AND p.save_flag = '1'
AND p.status = '1'
AND u.approved = '1'
AND u.active_status = '1'
AND b.status = '1'
AND m.status = '1'
AND CASE WHEN p.distributor_flag = '1'
THEN u.id
IN (

SELECT retail_id
FROM site_distributor_retailer
WHERE dist_id = p.user_id
)
WHEN p.retailer_flag = '1'
THEN (
u.id = p.user_id
AND o.payment_status = '1'
AND p.product_expiry_date >= NOW( ) 
)
END GROUP BY userid, tradingName

How to repeat:
Evrery time it is not returning any result.

[23 Oct 2008 5:45] Sveta Smirnova

Thank you for the report.

But version 5.0.51a is a bit old. Please try with current version 5.0.67 and if problem still exists provide output of SHOW CREATE TABLE for all underlying tables.

[23 Oct 2008 9:09] Maneesh Thareja

user table

Attachment: users.htm (text/html), 8.66 KiB.

[23 Oct 2008 9:13] Maneesh Thareja

Product

Attachment: product.htm (text/html), 5.62 KiB.

[23 Oct 2008 9:15] Maneesh Thareja

boat_details

Attachment: boatdetails.htm (text/html), 4.26 KiB.

[23 Oct 2008 9:15] Maneesh Thareja

Distributor_retailer

Attachment: distributor_retailer.htm (text/html), 3.19 KiB.

[23 Oct 2008 9:18] Maneesh Thareja

Invoice

Attachment: invoice.htm (text/html), 3.12 KiB.

[23 Oct 2008 9:19] Maneesh Thareja

Order Table

Attachment: order.htm (text/html), 4.32 KiB.

[23 Oct 2008 9:25] Maneesh Thareja

State

Attachment: state.htm (text/html), 2.84 KiB.

[23 Oct 2008 9:38] Maneesh Thareja

Country

Attachment: country.htm (text/html), 2.81 KiB.

[23 Oct 2008 9:40] Maneesh Thareja

Hi,

I have uploaded all the Show Create Table screen shots.Please check it and let me know your feedback.

I have tested on the My Sql 5.0.67 and still nothing is happening.

Thanks,

Maneesh

[23 Oct 2008 22:25] Sveta Smirnova

Thank you for the feedback.

You forget to upload SHOW CREATE TABLE site_brand.

If you could upload partial dump problem is repeatable with it would be appreciated too.

[24 Nov 2008 0:00] Bugs System

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".