none
U-SQL Query strange behavior

    Question

  • I have simple query which behaves very odd to me. Here is the query.

    CREATE DATABASE IF NOT EXISTS SampleDB; DROP TABLE IF EXISTS [SampleDB].[dbo].[Client1]; CREATE TABLE [SampleDB].[dbo].[Client1] ( [Id] int, [PartnerId] int, [CurrencyId] string, [TEST] string, INDEX clx_Id CLUSTERED(Id ASC) PARTITIONED BY (CurrencyId) DISTRIBUTE BY ROUND ROBIN ); ALTER TABLE [SampleDB].[dbo].[Client1] ADD IF NOT EXISTS PARTITION("USD"), PARTITION("RUB"), PARTITION("CNY"), PARTITION("No_Currency");

    After table creation I insert data into it in following way

    DECLARE @badPartition string = "No_Currency";
    INSERT INTO [SampleDB].[dbo].[Client1]
    (
        [Id],
        [PartnerId],    
        [CurrencyId],
        [TEST]
    
    )
    ON INTEGRITY VIOLATION MOVE TO PARTITION
                           (
                               @badPartition
                           )
    VALUES
    (
        1,
        2,
        "USD",
        "TEST1"
    ),
    (
        2,
        2,
        "USD",
        "TEST2"
    ),
    (
        3,
        2,
        "USD",
        "TEST3"
    ),
    (
        4,
        2,
        "USD1",
        "WRONG-TEST"
    );

    when i preview the table i get this

    1   2   TEST1   USD
    4   2   WRONG-TEST  No_Currency
    3   2   TEST3   USD
    2   2   TEST2   USD
    

    which is very strange since, third column should be CurrencyId and in addition to that i have modified values in last column which seems to be CurrencyId but containing Partition values?


    • Edited by zone7 Wednesday, December 6, 2017 4:16 PM
    Wednesday, December 6, 2017 4:15 PM

All replies

  • Thanks for reaching out.  I notice that this question is posted in two places.  Lets use StackOverflow as the place for a response.

    https://stackoverflow.com/questions/47678513/u-sql-query-strange-behavior

    Thank you,

    Guy

    Wednesday, December 6, 2017 7:25 PM