Examples with StoredProcedure org.teiid.query.sql.lang.StoredProcedure used on opensource projects

Example 16 with StoredProcedure

use of org.teiid.query.sql.lang.StoredProcedure in project teiid by teiid.

the class TestProcedureRelational method testIssue119.

/*
     * The following are tests that were removed from the validator.  We are no longer trying to validate a priori whether 
     * procedure input criteria is valid.  This can be addressed later more generally when we do up front validation of
     * access patterns and access patterns have a wider range of semantics.
     * 
    @Test public void testProcInVirtualGroupDefect14609_1() throws Exception{
        helpValidate("select ve3 from vm1.vgvp1 where ve1=1.1 and ve2='a'", new String[] {"ve1 = 1.1"}, RealMetadataFactory.example1Cached()); //$NON-NLS-1$ //$NON-NLS-2$
    }
   
    @Test public void testProcInVirtualGroupDefect14609_2() throws Exception{
        helpValidate("select ve3 from vm1.vgvp1 where convert(ve1, integer)=1 and ve2='a'", new String[] {"convert(ve1, integer) = 1" }, RealMetadataFactory.example1Cached()); //$NON-NLS-1$ //$NON-NLS-2$
    }
    
    @Test public void testProcInVirtualGroupDefect14609_3() throws Exception{
        helpValidate("select ve3 from vm1.vgvp1 where 1.1=ve1 and ve2='a'", new String[] {"1.1 = ve1" }, RealMetadataFactory.example1Cached()); //$NON-NLS-1$ //$NON-NLS-2$
    }
    
    @Test public void testProcInVirtualGroupDefect14609_4() throws Exception{
        helpValidate("select ve3 from vm1.vgvp1 where 1=convert(ve1, integer) and ve2='a'", new String[] {"1 = convert(ve1, integer)" }, RealMetadataFactory.example1Cached()); //$NON-NLS-1$ //$NON-NLS-2$
    }    

    @Test public void testDefect15861() throws Exception{
        helpValidate("select ve3 from vm1.vgvp1 where (ve1=1 or ve1=2) and ve2='a'", new String[] {"(ve1 = 1) OR (ve1 = 2)", "ve1 = 2"}, RealMetadataFactory.example1Cached()); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
    }

    @Test public void testProcInVirtualGroup1_Defect20164() {
        helpFailProcedure("select ve3 from vm1.vgvp2 where (ve1=1 and ve2='a') or ve3='c'", RealMetadataFactory.example1Cached()); //$NON-NLS-1$
    }

    @Test public void testProcInVirtualGroup2_Defect20164() {
        helpFailProcedure("select ve3 from vm1.vgvp2 where ve1=1 or ve2='a'", RealMetadataFactory.example1Cached()); //$NON-NLS-1$
    }

    @Test public void testProcInVirtualGroup3_Defect20164() {
        helpFailProcedure("select ve3 from vm1.vgvp2, pm1.g1 where ve1=pm1.g1.e2 and ve2='a'", RealMetadataFactory.example1Cached()); //$NON-NLS-1$
    }

    @Test public void testProcInVirtualGroup4_Defect20164() {
        helpValidate("select ve3 from vm1.vgvp2 where (ve1=1 and ve2='a') and (ve3='a' OR ve3='c')", new String[0], RealMetadataFactory.example1Cached()); //$NON-NLS-1$
    }

    @Test public void testProcInVirtualGroup5_Defect20164() {
        helpFailProcedure("select ve3 from vm1.vgvp2 where ve1=1 and NOT(ve2='a')", RealMetadataFactory.example1Cached()); //$NON-NLS-1$
    }

    @Test public void testProcInVirtualGroup6_Defect20164() {
        helpValidate("select ve3 from vm1.vgvp2 where ve1=1 and ve2 is null", new String[0], RealMetadataFactory.example1Cached()); //$NON-NLS-1$
    }

    @Test public void testProcInVirtualGroup7_Defect20164() {
        helpFailProcedure("select ve3 from vm1.vgvp2 where ve1=1 and ve2 is not null", RealMetadataFactory.example1Cached()); //$NON-NLS-1$
    }*/
/**
 * Ensures that dependent procedures are processed 1 at a time so that projected input values
 * are set correctly.
 */
@Test
public void testIssue119() throws Exception {
    MetadataStore metadataStore = new MetadataStore();
    // $NON-NLS-1$
    Schema v1 = RealMetadataFactory.createVirtualModel("v1", metadataStore);
    // $NON-NLS-1$
    Schema pm1 = RealMetadataFactory.createPhysicalModel("pm1", metadataStore);
    // $NON-NLS-1$
    ProcedureParameter in = RealMetadataFactory.createParameter("in1", SPParameter.IN, DataTypeManager.DefaultDataTypes.INTEGER);
    // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$
    ColumnSet<Procedure> rs1 = RealMetadataFactory.createResultSet("v1.vp1.rs1", new String[] { "e1", "e2", "e3", "e4", "e5" }, new String[] { DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.INTEGER });
    // $NON-NLS-1$
    QueryNode n1 = new QueryNode("CREATE VIRTUAL PROCEDURE BEGIN SELECT vp1.in1 e1, x.in1 e2, x.e1 e3, y.in1 e4, y.e1 e5 FROM pm1.sp119 x, pm1.sp119 y where x.in1 = vp1.in1 and y.in1 = x.e1; END");
    // $NON-NLS-1$
    Procedure vt1 = RealMetadataFactory.createVirtualProcedure("vp1", v1, Arrays.asList(in), n1);
    vt1.setResultSet(rs1);
    // $NON-NLS-1$
    ProcedureParameter in1 = RealMetadataFactory.createParameter("in1", SPParameter.IN, DataTypeManager.DefaultDataTypes.INTEGER);
    // $NON-NLS-1$ //$NON-NLS-2$
    ColumnSet<Procedure> rs3 = RealMetadataFactory.createResultSet("pm1.sp119.rs1", new String[] { "e1" }, new String[] { DataTypeManager.DefaultDataTypes.INTEGER });
    // $NON-NLS-1$
    Procedure sp1 = RealMetadataFactory.createStoredProcedure("sp119", pm1, Arrays.asList(in1));
    sp1.setResultSet(rs3);
    // $NON-NLS-1$
    String sql = "select * from (exec v1.vp1(1)) foo order by e4, e5";
    List<?>[] expected = new List[] { Arrays.asList(1, 1, 3, 3, 5), Arrays.asList(1, 1, 3, 3, 8), Arrays.asList(1, 1, 6, 6, 8), Arrays.asList(1, 1, 6, 6, 11) };
    QueryMetadataInterface metadata = RealMetadataFactory.createTransformationMetadata(metadataStore, "foo");
    // Construct data manager with data
    // Plan query
    ProcessorPlan plan = TestProcedureProcessor.getProcedurePlan(sql, metadata);
    // Run query
    HardcodedDataManager dataManager = new HardcodedDataManager() {

        @Override
        public TupleSource registerRequest(CommandContext context, Command command, String modelName, RegisterRequestParameter parameterObject) throws TeiidComponentException {
            if (command instanceof StoredProcedure) {
                StoredProcedure proc = (StoredProcedure) command;
                List<SPParameter> params = proc.getInputParameters();
                assertEquals(1, params.size());
                int value = (Integer) ((Constant) params.get(0).getExpression()).getValue();
                return new FakeTupleSource(command.getProjectedSymbols(), new List[] { Arrays.asList(value + 2), Arrays.asList(value + 5) });
            }
            return super.registerRequest(context, command, modelName, parameterObject);
        }
    };
    TestProcedureProcessor.helpTestProcess(plan, expected, dataManager, metadata);
}

Example 17 with StoredProcedure

use of org.teiid.query.sql.lang.StoredProcedure in project teiid by teiid.

the class TestCallableStatementParsing method testCallNoParams.

@Test
public void testCallNoParams() throws QueryParserException {
    // $NON-NLS-1$
    StoredProcedure sp = (StoredProcedure) QueryParser.getQueryParser().parseCommand("{call procedure_name}");
    assertFalse(sp.returnsScalarValue());
    // $NON-NLS-1$
    assertEquals("procedure_name", sp.getProcedureName());
    assertEquals(0, sp.getParameters().size());
}

Example 18 with StoredProcedure

use of org.teiid.query.sql.lang.StoredProcedure in project teiid by teiid.

the class RulePlanProcedures method execute.

/**
 * @see org.teiid.query.optimizer.relational.OptimizerRule#execute(org.teiid.query.optimizer.relational.plantree.PlanNode, org.teiid.query.metadata.QueryMetadataInterface, org.teiid.query.optimizer.capabilities.CapabilitiesFinder, org.teiid.query.optimizer.relational.RuleStack, org.teiid.query.analysis.AnalysisRecord, org.teiid.query.util.CommandContext)
 */
public PlanNode execute(PlanNode plan, final QueryMetadataInterface metadata, CapabilitiesFinder capabilitiesFinder, RuleStack rules, AnalysisRecord analysisRecord, CommandContext context) throws QueryPlannerException, QueryMetadataException, TeiidComponentException {
    for (PlanNode node : NodeEditor.findAllNodes(plan, NodeConstants.Types.SOURCE, NodeConstants.Types.ACCESS)) {
        if (!FrameUtil.isProcedure(node.getFirstChild())) {
            continue;
        }
        StoredProcedure proc = (StoredProcedure) node.getProperty(NodeConstants.Info.NESTED_COMMAND);
        if (!proc.isProcedureRelational()) {
            continue;
        }
        HashSet<ElementSymbol> inputSymbols = new HashSet<ElementSymbol>();
        List<Reference> inputReferences = new LinkedList<Reference>();
        PlanNode critNode = node.getParent();
        List<Criteria> conjuncts = new LinkedList<Criteria>();
        HashSet<ElementSymbol> coveredParams = new HashSet<ElementSymbol>();
        for (Iterator<SPParameter> params = proc.getInputParameters().iterator(); params.hasNext(); ) {
            SPParameter param = params.next();
            ElementSymbol symbol = param.getParameterSymbol();
            Expression input = param.getExpression();
            inputReferences.add((Reference) input);
            inputSymbols.add(symbol);
        }
        findInputNodes(inputSymbols, critNode, conjuncts, coveredParams);
        List<Expression> defaults = new LinkedList<Expression>();
        for (Reference ref : inputReferences) {
            ElementSymbol symbol = ref.getExpression();
            Expression defaultValue = null;
            /*try {
                    defaultValue = ResolverUtil.getDefault(symbol, metadata);
                } catch (QueryResolverException qre) {
                    //Just ignore
                }*/
            defaults.add(defaultValue);
            if (defaultValue == null && !coveredParams.contains(symbol)) {
                throw new QueryPlannerException(QueryPlugin.Event.TEIID30270, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID30270, symbol));
            }
        }
        /*if (conjuncts.isEmpty()) {
                for (int j = 0; j < inputReferences.size(); j++) {
                    Reference ref = (Reference)inputReferences.get(j);
                    ref.setValue(defaults.get(j));
                }
                continue;
            }*/
        PlanNode accessNode = NodeEditor.findNodePreOrder(node, NodeConstants.Types.ACCESS);
        Criteria crit = Criteria.combineCriteria(conjuncts);
        if (crit != null) {
            accessNode.setProperty(NodeConstants.Info.PROCEDURE_CRITERIA, crit);
            accessNode.setProperty(NodeConstants.Info.PROCEDURE_INPUTS, inputReferences);
            accessNode.setProperty(NodeConstants.Info.PROCEDURE_DEFAULTS, defaults);
            accessNode.setProperty(NodeConstants.Info.IS_DEPENDENT_SET, Boolean.TRUE);
        }
    }
    return plan;
}

Example 19 with StoredProcedure

use of org.teiid.query.sql.lang.StoredProcedure in project teiid by teiid.

the class RuleAssignOutputElements method assignOutputElements.

/**
 * <p>Assign the output elements at a particular node and recurse the tree.  The
 * outputElements needed from above the node have been collected in
 * outputElements.</p>
 *
 * <p>SOURCE nodes:  If we find a SOURCE node, this must define the top
 * of a virtual group.  Physical groups can be identified by ACCESS nodes
 * at this point in the planning stage.  So, we filter the virtual elements
 * in the virtual source based on the required output elements.</p>
 *
 * <p>SET_OP nodes:  If we hit a SET_OP node, this must be a union.  Unions
 * require a lot of special care.  Unions have many branches and the projected
 * elements in each branch are "equivalent" in terms of nodes above the union.
 * This means that any filtering must occur in an identical way in all branches
 * of a union.</p>
 *
 * @param root Node to assign
 * @param outputElements Output elements needed for this node
 * @param metadata Metadata implementation
 */
private void assignOutputElements(PlanNode root, List<Expression> outputElements, QueryMetadataInterface metadata, CapabilitiesFinder capFinder, RuleStack rules, AnalysisRecord analysisRecord, CommandContext context) throws QueryPlannerException, QueryMetadataException, TeiidComponentException {
    int nodeType = root.getType();
    // Update this node's output columns based on parent's columns
    List<Expression> oldOutput = (List<Expression>) root.setProperty(NodeConstants.Info.OUTPUT_COLS, outputElements);
    if (root.getChildCount() == 0) {
        // update temp access
        if (root.getType() == NodeConstants.Types.SOURCE && root.getGroups().size() == 1) {
            GroupSymbol gs = root.getGroups().iterator().next();
            if (gs.getMetadataID() instanceof TempMetadataID) {
                for (Expression ex : outputElements) {
                    if (ex instanceof ElementSymbol) {
                        Object id = ((ElementSymbol) ex).getMetadataID();
                        if (id instanceof TempMetadataID) {
                            ((TempMetadataID) id).setAccessed(true);
                        }
                    }
                }
            }
        }
        return;
    }
    switch(nodeType) {
        case NodeConstants.Types.ACCESS:
            Command command = FrameUtil.getNonQueryCommand(root);
            if (command instanceof StoredProcedure) {
                // if the access node represents a stored procedure, then we can't actually change the output symbols
                root.setProperty(NodeConstants.Info.OUTPUT_COLS, command.getProjectedSymbols());
            } else {
                ProcessorPlan plan = FrameUtil.getNestedPlan(root);
                if (plan != null && (command == null || !RelationalNodeUtil.isUpdate(command))) {
                    // nested with clauses are handled as sub plans, which have a fixed set of output symbols
                    root.setProperty(NodeConstants.Info.OUTPUT_COLS, ResolverUtil.resolveElementsInGroup(root.getGroups().iterator().next(), metadata));
                }
                if (checkSymbols) {
                    Object modelId = RuleRaiseAccess.getModelIDFromAccess(root, metadata);
                    for (Expression symbol : outputElements) {
                        if (!RuleRaiseAccess.canPushSymbol(symbol, true, modelId, metadata, capFinder, analysisRecord)) {
                            throw new QueryPlannerException(QueryPlugin.Event.TEIID30258, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID30258, symbol, modelId));
                        }
                    }
                }
                if (NodeEditor.findParent(root, NodeConstants.Types.PROJECT, NodeConstants.Types.SOURCE) != null) {
                    // there's a chance that partial projection was used.  we are not a defacto project node
                    // take credit for creating anything that is not an element symbol
                    LinkedHashSet<Expression> filteredElements = new LinkedHashSet<Expression>();
                    for (Expression element : outputElements) {
                        if (element instanceof ElementSymbol) {
                            filteredElements.add(element);
                        } else {
                            filteredElements.addAll(ElementCollectorVisitor.getElements(element, false));
                        }
                    }
                    outputElements = new ArrayList<Expression>(filteredElements);
                }
            }
            assignOutputElements(root.getLastChild(), outputElements, metadata, capFinder, rules, analysisRecord, context);
            break;
        case NodeConstants.Types.DUP_REMOVE:
            // targeted optimization based upon swapping the dup remove for a limit 1
            // TODO: may need to also check for grouping over constants
            boolean allConstants = true;
            for (Expression ex : outputElements) {
                if (!(EvaluatableVisitor.willBecomeConstant(SymbolMap.getExpression(ex)))) {
                    allConstants = false;
                    break;
                }
            }
            if (allConstants && addLimit(rules, root, metadata, capFinder)) {
                // TODO we could more gracefully handle the !addLimit case
                PlanNode parent = root.getParent();
                if (parent != null) {
                    NodeEditor.removeChildNode(root.getParent(), root);
                    execute(parent, metadata, capFinder, rules, analysisRecord, context);
                    return;
                }
            }
        case NodeConstants.Types.SORT:
            // correct expression positions and update the unrelated flag
            OrderBy order = (OrderBy) root.getProperty(NodeConstants.Info.SORT_ORDER);
            if (order != null && (oldOutput == null || !oldOutput.equals(outputElements))) {
                outputElements = new ArrayList<Expression>(outputElements);
                boolean hasUnrelated = false;
                for (OrderByItem item : order.getOrderByItems()) {
                    int index = outputElements.indexOf(item.getSymbol());
                    if (index != -1) {
                        item.setExpressionPosition(index);
                    } else {
                        hasUnrelated = true;
                        outputElements.add(item.getSymbol());
                    }
                }
                if (!hasUnrelated) {
                    root.setProperty(NodeConstants.Info.UNRELATED_SORT, false);
                } else {
                    root.setProperty(NodeConstants.Info.UNRELATED_SORT, true);
                }
            }
            assignOutputElements(root.getLastChild(), outputElements, metadata, capFinder, rules, analysisRecord, context);
            break;
        case NodeConstants.Types.TUPLE_LIMIT:
            assignOutputElements(root.getLastChild(), outputElements, metadata, capFinder, rules, analysisRecord, context);
            break;
        case NodeConstants.Types.SOURCE:
            {
                outputElements = (List<Expression>) determineSourceOutput(root, outputElements, metadata, capFinder);
                if (!finalRun && root.getProperty(Info.PARTITION_INFO) != null && NodeEditor.findParent(root, NodeConstants.Types.JOIN) != null) {
                    GroupSymbol group = root.getGroups().iterator().next();
                    Object modelId = RuleDecomposeJoin.getEffectiveModelId(metadata, group);
                    String name = metadata.getExtensionProperty(modelId, RuleDecomposeJoin.IMPLICIT_PARTITION_COLUMN_NAME, true);
                    if (name != null) {
                        // keep projecting the implicit partitioning column through the source so that it can
                        // be used in the decomposition logic
                        ElementSymbol es = new ElementSymbol(name, group);
                        if (!outputElements.contains(es)) {
                            es.setMetadataID(metadata.getElementID(es.getName()));
                            outputElements.add(es);
                        }
                    }
                }
                root.setProperty(NodeConstants.Info.OUTPUT_COLS, outputElements);
                List<Expression> childElements = filterVirtualElements(root, outputElements, metadata);
                assignOutputElements(root.getFirstChild(), childElements, metadata, capFinder, rules, analysisRecord, context);
                break;
            }
        case NodeConstants.Types.SET_OP:
            {
                for (PlanNode childNode : root.getChildren()) {
                    PlanNode projectNode = NodeEditor.findNodePreOrder(childNode, NodeConstants.Types.PROJECT);
                    List<Expression> projectCols = (List<Expression>) projectNode.getProperty(NodeConstants.Info.PROJECT_COLS);
                    assignOutputElements(childNode, projectCols, metadata, capFinder, rules, analysisRecord, context);
                }
                break;
            }
        default:
            {
                PlanNode sortNode = null;
                if (root.getType() == NodeConstants.Types.PROJECT) {
                    GroupSymbol intoGroup = (GroupSymbol) root.getProperty(NodeConstants.Info.INTO_GROUP);
                    if (intoGroup != null) {
                        // if this is a project into, treat the nodes under the source as a new plan root
                        PlanNode intoRoot = NodeEditor.findNodePreOrder(root, NodeConstants.Types.SOURCE);
                        execute(intoRoot.getFirstChild(), metadata, capFinder, rules, analysisRecord, context);
                        return;
                    }
                    List<Expression> projectCols = outputElements;
                    sortNode = NodeEditor.findParent(root, NodeConstants.Types.SORT, NodeConstants.Types.SOURCE);
                    if (finalRun && sortNode != null && sortNode.hasBooleanProperty(NodeConstants.Info.UNRELATED_SORT)) {
                        root.getGroups().clear();
                        root.addGroups(GroupsUsedByElementsVisitor.getGroups(projectCols));
                        root.addGroups(GroupsUsedByElementsVisitor.getGroups(root.getCorrelatedReferenceElements()));
                    }
                    root.setProperty(NodeConstants.Info.PROJECT_COLS, projectCols);
                    if (root.hasBooleanProperty(Info.HAS_WINDOW_FUNCTIONS)) {
                        Set<WindowFunction> windowFunctions = getWindowFunctions(projectCols);
                        if (windowFunctions.isEmpty()) {
                            root.setProperty(Info.HAS_WINDOW_FUNCTIONS, false);
                        }
                    }
                }
                List<Expression> requiredInput = collectRequiredInputSymbols(root, metadata, capFinder);
                // targeted optimization for unnecessary aggregation
                if (root.getType() == NodeConstants.Types.GROUP && root.hasBooleanProperty(Info.IS_OPTIONAL) && NodeEditor.findParent(root, NodeConstants.Types.ACCESS) == null) {
                    PlanNode parent = removeGroupBy(root, metadata);
                    if (!root.hasCollectionProperty(Info.GROUP_COLS)) {
                        // just lob off everything under the projection
                        PlanNode project = NodeEditor.findNodePreOrder(parent, NodeConstants.Types.PROJECT);
                        project.removeAllChildren();
                    } else if (!addLimit(rules, parent, metadata, capFinder)) {
                        // $NON-NLS-1$
                        throw new AssertionError("expected limit node to be added");
                    }
                    execute(parent, metadata, capFinder, rules, analysisRecord, context);
                    return;
                }
                // Call children recursively
                if (root.getChildCount() == 1) {
                    assignOutputElements(root.getLastChild(), requiredInput, metadata, capFinder, rules, analysisRecord, context);
                    if (!finalRun && root.getType() == NodeConstants.Types.PROJECT && sortNode != null && sortNode.hasBooleanProperty(NodeConstants.Info.UNRELATED_SORT)) {
                        // if this is the initial rule run, remove unrelated order to preserve the original projection
                        OrderBy elements = (OrderBy) sortNode.getProperty(NodeConstants.Info.SORT_ORDER);
                        outputElements = new ArrayList<Expression>(outputElements);
                        for (OrderByItem item : elements.getOrderByItems()) {
                            if (item.getExpressionPosition() == -1) {
                                outputElements.remove(item.getSymbol());
                            }
                        }
                        root.setProperty(NodeConstants.Info.PROJECT_COLS, outputElements);
                    }
                } else {
                    // determine which elements go to each side of the join
                    for (PlanNode childNode : root.getChildren()) {
                        Set<GroupSymbol> filterGroups = FrameUtil.findJoinSourceNode(childNode).getGroups();
                        List<Expression> filteredElements = filterElements(requiredInput, filterGroups);
                        // Call child recursively
                        assignOutputElements(childNode, filteredElements, metadata, capFinder, rules, analysisRecord, context);
                    }
                }
            }
    }
}

Example 20 with StoredProcedure

use of org.teiid.query.sql.lang.StoredProcedure in project teiid by teiid.

the class FrameUtil method isProcedure.

static boolean isProcedure(PlanNode projectNode) {
    if (projectNode != null && projectNode.getType() == NodeConstants.Types.PROJECT && projectNode.getChildCount() > 0) {
        PlanNode accessNode = projectNode.getFirstChild();
        Command command = getNonQueryCommand(accessNode);
        return command instanceof StoredProcedure;
    }
    return false;
}