6 Replies Latest reply on Jun 13, 2014 7:34 AM by Steven Hawkins

    Join tables losing their metedata.

    Sanjay Chaturvedi Novice

      Want to retrieve foreign key value from table in vdb, this is working fine and left and right tables are coming as :

       

      sql=select * from tab1 a inner join tab2 b on s.id=b.is and a.id='1234';

       

      jleft = (NamedTable) j.getLeftItem(); //this is tab1

      jright = (NamedTable) j.getRightItem(); //this is tab2

       

      jright.getMetadataObject().getForeignKeys() //gives foreign key info This works fine when teiid calls method createResultSetExecution.

      But when try to run test case by parsing same sql using command

       

      command = QueryParser.getQueryParser().parseCommand(sql);

      QueryResolver.resolveCommand(command, metadata);

      command = QueryRewriter.rewrite(command, metadata, null);

      return new LanguageBridgeFactory(metadata).translate(command);

      select =(select) command;

      when this command passed to

       

      .createResultSetExecution(select,executionContext, metadata,odcProxy)

      this select has Named table as "tab1 as a" while previously it was "tab1". Also its is not giving me metadata of left /right table. it is null.

       

      jright.getMetadataObject()=null

       

      Any help? Urgent.

      Why it is different?

      how to preserve metadata of left /right table.

       

       

       

       

        • 1. Re: Join tables losing their metedata.
          Steven Hawkins Master

          > this select has Named table as "tab1 as a" while previously it was "tab1"

          > Why it is different?


          Are you comparing the push down command from the optimizer based upon user sql to something that is crafted roughly by the code above based upon the same user sql?  If so, then yes you should expect differences.  The optimizer can strip or generate alias among other things even for a full push down query.

           

          > how to preserve metadata of left /right table.


          Are tab1/tab2 initially views in Teiid?  If so then they won't have push down metadata.  Otherwise can you post your test code more fully?

          • 2. Re: Join tables losing their metedata.
            Sanjay Chaturvedi Novice

            Thanks Stewan, tab1 and tab2 are views in vdb but metadat can be avail when teiid calls createresultsetexecution, but when doing unit testing, we manually call this method and to pass first argument as command we did above coding to parse query.
            untitled1.PNG

            As you can see, 'command' here has metedata available including info abt foreign keys and all but next line is transforming this org.teiid.query.sql.lang.command to org.teiid.language.command using:

            org.teiid.language.command cmd=new LanguageBuyildfactory(metadata).translate(command);

            here cmd has metadata as null.

            Any guess or idea abt how can I call createresultssetexecution with metadata in command, so that i can extract join, left/right table and their metadata info from this.

            • 3. Re: Join tables losing their metedata.
              Steven Hawkins Master

              >  Any guess or idea abt how can I call createresultssetexecution with metadata in command, so that i can extract join, left/right table and their metadata info from this.

               

              You are dealing with two different scenarios.  In the case of the user query that the optimizer runs against, it will produce a source query that references the physical tables.  However if you simply start with the user query, which references views, and ask for the translated command then it will not reference the physical tables and the associated metadata.

               

              To make the scenarios more similar your input to the unit test case should be the push down sql (which can be seen in the query plan) - not the user sql.

              • 4. Re: Join tables losing their metedata.
                Ramesh Reddy Master

                What is intention behind the unit test? Typically what I do is, when it requires all the query planning levels, I use "FakeServer" and try to mock the level I want to test. If you are testing the Custom translator, the metadata is typically available. So take look at tests based on FakeServer in Teiid source?

                 

                Ramesh..

                • 5. Re: Join tables losing their metedata.
                  Sanjay Chaturvedi Novice

                  Hi Stewan, Before translating the command m starting an execution factory and also pointing to the VDB, so as I showed in screenshot in my last reply: It is showing meta data because it is actually referring source views of VDB. So its not just translating the command only, it has source as well.

                  • 6. Re: Join tables losing their metedata.
                    Steven Hawkins Master

                    I will second what Ramesh is saying.  If the intention is to just get a unit test running in a similar manner to the server, then using EmbeddedServer/FakeServer is the way to go.  Then you can start with your user level query and everything will flow from there.

                     

                    I cannot see from your screenshot what the metadata represents.  Again if it is a Teiid view, then it will have no push down metadata.  Can you post the query plan for query that you running against the server?  You can get the source sql from there.