Issue moved from http://drupal.org/node/1223758#comment-7029238

Steps for reproducing it:

  1. Create a content type and add 2 or more product reference fields
  2. Create a new content view
  3. Add a Relationship: Content: Referenced product
  4. Add a field Content: Product variations

// The Error Message you will get right after finishing configuring the Content: Product variations field.
"SQLSTATE[42000]: Syntax error or access violation: 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 'AS commerce_product_field_data_field_product_, node.created AS node_created, '' ' at line 1"

// View SQL Query
SELECT commerce_product_field_data_field_product. AS commerce_product_field_data_field_product_, node.created AS node_created, '' AS field_data_field_product__entity_type
FROM 
node node
LEFT JOIN field_data_field_product field_data_field_product ON node.nid = field_data_field_product.entity_id AND (field_data_field_product.entity_type = 'node' AND field_data_field_product.deleted = '0')
LEFT JOIN commerce_product commerce_product_field_data_field_product ON field_data_field_product.field_product_product_id = commerce_product_field_data_field_product.product_id
WHERE (( (node.status = '1') AND (node.type IN  ('MY_CONTENT_TYPE')) ))
ORDER BY node_created DESC
LIMIT 10 OFFSET 0

/** 
  * I think I can see where the problem is but do not know how to fix it.
  *
  * By doing a manual MYSQL query and changing the following:
  */
//From
SELECT commerce_product_field_data_field_product. AS commerce_product_field_data_field_product_, ...

// To
SELECT commerce_product_field_data_field_product.product_id  AS commerce_product_field_data_field_product_product_id, ...

// The Query results are returned without any errors.

Just a gut feeling that this issue #1898998: Error when creating product display view perhaps has the same troubling root.

Comments

jsacksick’s picture

Status: Active » Closed (duplicate)

This seems to be a duplicate of #1898998: Error when creating product display view, closing this issue

FranciscoLuz’s picture

Status: Closed (duplicate) » Active

@jsacksick

How did you figure this is a duplicate? You said here http://drupal.org/node/1898998#comment-7038642 you could not reproduce that issue.

I agree with you that both issues might be of the same nature and therefore either one should be set to duplicate but unless you can effectively assert the cause of both claims you can not reasonably say that they are duplicates.

FranciscoLuz’s picture

Issue summary: View changes

Added a new step for reproducing the issue.

bojanz’s picture

Category: bug » support
Status: Active » Fixed

There is a bug in the Views UI that sometimes causes fields to "forget" which relationship they belong to.
This causes the error you are seeing.

Edit your fields one by one, make sure they have the correct relationship selected, then resave their settings.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

Anonymous’s picture

Issue summary: View changes

..

boban_dj’s picture

I just want to confirm bojanz suggestion, when you add one field at the time it works fine.

knalstaaf’s picture

In addition to #3 (and #5):

In order to avoid this error I had to create a necessary relation, and then add my fields one by one instead of searching and checking them from the list and then click "Add". So I had to click "Add", pick one desired field and hit "Add and configure" - this over and over again.

Apparently Views freaked out when all the fields were added all at once.

It's not limited to Commerce Kickstart I believe. It may be a general issue.