[teiid-issues] [JBoss JIRA] (TEIID-5161) Push down rand() function

[dalex dalex (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-5161?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13506730#comment-13506730 ] 

dalex dalex commented on TEIID-5161:
------------------------------------

Hi [~shawkins], I've just applied all these changes for my teiid 9.3.4 and got one strange problem related to MSSQL, all values were the same. How to reproduce the problem:
1. create in MSSQL table_a table:
{code:sql}
CREATE TABLE public.test_a
(
  a integer,
  b integer
);
INSERT INTO public.test_a(a, b) VALUES (1, 1);
INSERT INTO public.test_a(a, b) VALUES (1, 2);
INSERT INTO public.test_a(a, b) VALUES (2, 1);
INSERT INTO public.test_a(a, b) VALUES (2, 2);
INSERT INTO public.test_a(a, b) VALUES (3, 2);
INSERT INTO public.test_a(a, b) VALUES (3, 10);
{code}

2. add MSSQL as a source to Teiid:
{code:xml}
                 <datasource jndi-name="java:/mssql-test-tables" pool-name="mssql-test-tables" enabled="true" use-java-context="true">
                    <connection-url>jdbc:sqlserver://localhost:1433;databaseName=test_tables</connection-url>
                    <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
                    <driver>com.microsoft.sqlserver</driver>
                    <new-connection-sql>SET ANSI_WARNINGS OFF</new-connection-sql>
                    <pool>
                        <min-pool-size>2</min-pool-size>
                        <max-pool-size>70</max-pool-size>
                    </pool>
                    <security>
                        <user-name>****</user-name>
                        <password>****</password>
                    </security>
                    <validation>
                        <check-valid-connection-sql>select 1</check-valid-connection-sql>
                    </validation>
                    <timeout>
                        <blocking-timeout-millis>120000</blocking-timeout-millis>
                        <idle-timeout-minutes>5</idle-timeout-minutes>
                    </timeout>
                </datasource>
{code}

3. add ms model:
{code:xml}
    <model name="ms">
        <property name="importer.useFullSchemaName" value="false"/>
        <property name="importer.tableTypes" value="TABLE,VIEW"/>
        <property name="importer.importKeys" value="false"/>
        <property name="importer.schemaPattern" value="dbo"/>
        <source name="test_tables_ms" translator-name="sqlserver" connection-jndi-name="java:/mssql-test-tables"/>
    </model>
{code}

4. run the following query:
{code:sql}
select rand() from ms.test_a;;
{code}
as a result I can see six rand function values which are absolutely the same, something like this:
{code:noformat}
0.62444
0.62444
0.62444
0.62444
0.62444
0.62444
{code}

but running the same query through PostgreSQL, for example:
{code:sql}
select rand() from test_pg.test_a;;
{code}
I'm getting absolutely different six values:
{code:noformat}
0.90137
0.57251
0.68237
0.28293
0.16568
0.73541
{code}

I don't know what we can do here, maybe it's better just not to push down rand function for MSSQL or maybe there is a problem on teiid core...

> Push down rand() function
> -------------------------
>
>                 Key: TEIID-5161
>                 URL: https://issues.jboss.org/browse/TEIID-5161
>             Project: Teiid
>          Issue Type: Quality Risk
>          Components: JDBC Connector, Query Engine
>    Affects Versions: 9.3.4
>         Environment: teiid-9.3.4 on WildFly Full 10.0.0.Final (WildFly Core 2.0.10.Final)
>            Reporter: dalex dalex
>            Assignee: Steven Hawkins
>             Fix For: 10.1
>
>
> As per discussion within TEIID-5153 we should push down rand() function where ever it seems to be missing. But first of all the function should be pushed down for at least PostgreSQL, MySQL, MSSQL and Oracle databases.



--
This message was sent by Atlassian JIRA
(v7.5.0#75005)