Minimalistic SELECT

  • Thomas Franz

    Hall of Fame

    Points: 3631

    Comments posted to this topic are about the item Minimalistic SELECT

    God is real, unless declared integer.

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71607

    Nice question to start the week on, thanks Thomas

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • Carlo Romagnano

    SSC-Insane

    Points: 21873

    The explanation is that constant numbers are evaluated as one token! It doesn't matter if there is not a space at the end of the constant

    These queries are equivalent:

    SELECT 1.test
    SELECT 1. test
    SELECT 0+1test
    SELECT 1 AS test
    ORDER BY 1DESC -- same thing

     

  • webrunner

    SSC-Dedicated

    Points: 30092

    Thanks, Carlo. That's crazy!

    -- webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • Thomas Franz

    Hall of Fame

    Points: 3631

    @Carlo: so the dot in SELECT 1.test is not a whitespace or a dot as between filename and extension, but a simple decimal separator without a following decimal number as in SELECT 1.0test

    God is real, unless declared integer.

  • Carlo Romagnano

    SSC-Insane

    Points: 21873

    That's true! It's the decimal separator.

  • Oddvar Eikli

    SSC Journeyman

    Points: 89

    However, the data type isn't strictly speaking an INT it is a DECIMAL(1,0):

    The query

    sp_describe_first_result_set @tsql = N'SELECT 1.test;' 

    shows it is a system_type_id 108 (numeric) and the system_type_name is "numeric(1,0)".

  • George Vobr

    SSCrazy Eights

    Points: 9082

    Thanks Thomas for a funny but interesting question, and thanks

    Oddvar for the reminder sp_describe_first_result_set.

    This clearly shows that if the expression before the dot is data type numeric,

    then the expression after the dot is taken as a column name.

    For example, try these expressions:

    SELECT '1'*1.test
    UNION
    SELECT AVG(1+1)*1.test;
    GO
    /*
    Results
    test
    ---------------------------------------
    1
    2

    (2 row(s) affected)
    */
    -- Next, verify the expression data type before the dot:
    sp_describe_first_result_set @tsql = N'SELECT AVG(1+1)*1.test;';
    GO
    sp_describe_first_result_set @tsql = N'SELECT ''1''*1.test;';
    GO
  • Carlo Romagnano

    SSC-Insane

    Points: 21873

    This clearly shows that if the expression before the dot is data type numeric,

    then the expression after the dot is taken as a column name.

    The dot is part of constant number, it's the decimal point and not a separator.

  • George Vobr

    SSCrazy Eights

    Points: 9082

    Hi Carlo, yes you're right. The first dot in constant numeric number in the selected

    expression is the decimal point. The second dot is a "virtual AS".

    SELECT 1.77+0. test;
    GO

    sp_describe_first_result_set @tsql = N'SELECT 1.77+0. test';
    GO

    Results
    test
    ---------------------------------------
    1.77

    (1 row(s) affected)

    is_hidden column_ordinal name is_nullable system_type_id system_type_name
    --------- -------------- -------------------------------------------------------------------------------------------------------------------------------- ----------- -------------- ----------------
    0 1 test 1 108 numeric(4,2)

    (1 row(s) affected)
  • Carlo Romagnano

    SSC-Insane

    Points: 21873

    The second dot is a "virtual AS".

    Absolutely NO, but if you like to think so OK.

  • George Vobr

    SSCrazy Eights

    Points: 9082

    Ah..., my apologies, it is obvious that in the expression '1.77 + 0. test' is the second dot also an decimal point. I had to be blind. I have overlooked your explanation from the examples above. Thanks again for your post.

  • t.ovod-everett

    Ten Centuries

    Points: 1346

    Oddvar Eikli wrote:

    However, the data type isn't strictly speaking an INT it is a DECIMAL(1,0):

    The query

    sp_describe_first_result_set @tsql = N'SELECT 1.test;' 

    shows it is a system_type_id 108 (numeric) and the system_type_name is "numeric(1,0)".

    I concur - numeric(1,0) may be similar to an int (i.e. it only stores integers), but it has a different range, different behaviors, etc.

  • andrei_solntsev

    SSCommitted

    Points: 1601

    Nice question!

    Try this: SELECT 9test, 1prod;

    which is equivalent to

    SELECT 9 test, 1 prod;

    SELECT 9 AS test, 1 AS prod;

    Looks like when name starts with number, SQL interprets it as a value and do not require any delimiter after that. Nearly any non-numeric character which is not an operator will stop processing numbers and the rest of the sting become a column name.

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply