Uploaded image for project: 'Cayenne'
  1. Cayenne
  2. CAY-1484

Flattened attribute queries are incorrectly generated

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 3.0.1
    • 3.0.2, 3.1M1
    • Core Library
    • None
    • Gentoo linux
      MySQL 5.1
      java version "1.6.0_20"
      Java(TM) SE Runtime Environment (build 1.6.0_20-b02)
      Java HotSpot(TM) 64-Bit Server VM (build 16.3-b01, mixed mode)

    Description

      Attached eclipse project demonstrating issue, including cayenne model
      When creating database structure like:
      table1
      t1key
      t1value
      t2keyref
      table2
      t2key
      t2value
      t3keyref
      table3
      t3key
      t3value
      and cayenne model with flattened attribute table3.t3value in table2
      it produces incorrect join query to table2 and 3:
      SELECT t1.t3value, t1.t2key, t0.t2value, t0.t3keyref, t0.t2key FROM testschema.table2 t0 JOIN testschema.table3 t1 ON (t0.t3key = t1.t3keyref) WHERE t0.t2key = ?

      Note t1.t2key, which obviously shouldn't be there and produces error.

      Attachments

        1. CayenneBugDemo.zip
          17 kB
          Misha Doronin
        2. CAY-1484.-Fixed-using-of-incorrect-alias.patch
          9 kB
          Ksenia Khailenko
        3. bugdemo_maven_derby.zip
          22 kB
          Misha Doronin
        4. bugdemo_maven_derby_nobug.zip
          22 kB
          Misha Doronin

        Activity

          closing this, as it is already fixed on 3.0 and 3.1 branches

          andrus Andrus Adamchik added a comment - closing this, as it is already fixed on 3.0 and 3.1 branches

          The problem was in incorrect using of aliases during the translation of the select query.
          The name of flattened attribute, that is longer than name of corresponding db attribute, forced the order of declaredProperties in PersistentDescriptor as

          {"flattened attribute","some obj relationship",...}

          . And when the visitor worked, the processing of "flattened attribute" gave alias "t1", that was used also for the source attribute of "some obj relationship", because there was not the resetting of join stack after the processing of "flattened attribute". The patch with the unit test and the fix is attached

          ksenia Ksenia Khailenko added a comment - The problem was in incorrect using of aliases during the translation of the select query. The name of flattened attribute, that is longer than name of corresponding db attribute, forced the order of declaredProperties in PersistentDescriptor as {"flattened attribute","some obj relationship",...} . And when the visitor worked, the processing of "flattened attribute" gave alias "t1", that was used also for the source attribute of "some obj relationship", because there was not the resetting of join stack after the processing of "flattened attribute". The patch with the unit test and the fix is attached

          Ok, I've solved the problem in bugdemo project by changing the flattened attribute Album.ArtistName name from "ArtistName" to "artistName" and regenerating the classes(btw, the regeneration of classes also solved the NPE problem with the commented line in Main - you've changed the Artist.albumsRel to Artist.albumRel in datamap, but forgotten to regenerate Artist java class).

          But such a solution didn't explain the error in CayenneBugDemo - the flattened attribute l3value starts with lowercase letter. I've played with naming of attributes and discovered, that for the correct query generation name of the flattened attribute must match the name of the referring db attribute: "artistName" in case of bugdemo and "l3val" in case of CayenneBugDemo.

          So, this is the bug and SelectTranslator should be checked in place where it generates aliases and columns for the flattened attributes(It seems like the relationships are added basing on the incorrect attribute name )

          ksenia Ksenia Khailenko added a comment - Ok, I've solved the problem in bugdemo project by changing the flattened attribute Album.ArtistName name from "ArtistName" to "artistName" and regenerating the classes(btw, the regeneration of classes also solved the NPE problem with the commented line in Main - you've changed the Artist.albumsRel to Artist.albumRel in datamap, but forgotten to regenerate Artist java class). But such a solution didn't explain the error in CayenneBugDemo - the flattened attribute l3value starts with lowercase letter. I've played with naming of attributes and discovered, that for the correct query generation name of the flattened attribute must match the name of the referring db attribute: "artistName" in case of bugdemo and "l3val" in case of CayenneBugDemo. So, this is the bug and SelectTranslator should be checked in place where it generates aliases and columns for the flattened attributes(It seems like the relationships are added basing on the incorrect attribute name )

          Thanks for the recent attachments, I'll see what it can be

          ksenia Ksenia Khailenko added a comment - Thanks for the recent attachments, I'll see what it can be
          misdoro Misha Doronin added a comment - - edited

          As you requested, here it goes.
          Two configurations, one worknig, one not.

          And in "bug" configuration, there is commented out line in Main.java, which if enabled, causes null pointer exception.

          The meaningful difference seems to be in fact that in broken configuration two different relationships have the same name.

          misdoro Misha Doronin added a comment - - edited As you requested, here it goes. Two configurations, one worknig, one not. And in "bug" configuration, there is commented out line in Main.java, which if enabled, causes null pointer exception. The meaningful difference seems to be in fact that in broken configuration two different relationships have the same name.

          Really, in described situation(using datamap of the project attached), the sql string is generated incorrectly:
          new SelectQuery(L2table.class) produces:
          SELECT t1.l3val, t1.l2pk, t0.l2value, t0.l3fk, t0.l2pk FROM l2table t0 JOIN l3table t1 ON (t0.l3fk = t1.l3pk)

          and t1.l2pk is used instead of t1.l2fk
          I've debugged this query evaluation and discovered that this column is generated in such a way because in org.apache.cayenne.access.trans.SelectTranslator.appendQueryColumns[313].PropertyVisitor.visitRelationship[366] there is considered the relationship between table1 and table2, but not between table2 and table3 as expected. So, it's strange why it uses incorrect alias and skips the join between table2 and table1 in from section. need to investigate deeper. seems like incorrectly defined relationship is used.

          Misha, could you be so kind to name the entities more intuitive understandable then they are now?) it's very hard to understand what it it incorrect in the relationship if we are using just numbered names. And as for the attached project - could you make this project like it is described in http://cayenne.apache.org/doc30/tutorial-starting-project.html? there's used inMemory db and maven, so we would be not dependent on libraries and schema.

          Regards, Ksenia

          ksenia Ksenia Khailenko added a comment - Really, in described situation(using datamap of the project attached), the sql string is generated incorrectly: new SelectQuery(L2table.class) produces: SELECT t1.l3val, t1.l2pk, t0.l2value, t0.l3fk, t0.l2pk FROM l2table t0 JOIN l3table t1 ON (t0.l3fk = t1.l3pk) and t1.l2pk is used instead of t1.l2fk I've debugged this query evaluation and discovered that this column is generated in such a way because in org.apache.cayenne.access.trans.SelectTranslator.appendQueryColumns [313] .PropertyVisitor.visitRelationship [366] there is considered the relationship between table1 and table2, but not between table2 and table3 as expected. So, it's strange why it uses incorrect alias and skips the join between table2 and table1 in from section. need to investigate deeper. seems like incorrectly defined relationship is used. Misha, could you be so kind to name the entities more intuitive understandable then they are now?) it's very hard to understand what it it incorrect in the relationship if we are using just numbered names. And as for the attached project - could you make this project like it is described in http://cayenne.apache.org/doc30/tutorial-starting-project.html? there's used inMemory db and maven, so we would be not dependent on libraries and schema. Regards, Ksenia
          misdoro Misha Doronin added a comment -

          Eclipse project to demonstrate bug
          Database dump and cayenne project are included.

          misdoro Misha Doronin added a comment - Eclipse project to demonstrate bug Database dump and cayenne project are included.

          People

            Unassigned Unassigned
            misdoro Misha Doronin
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Time Tracking

                Estimated:
                Original Estimate - 1h
                1h
                Remaining:
                Remaining Estimate - 1h
                1h
                Logged:
                Time Spent - Not Specified
                Not Specified