August 26, 2019 at 12:00 am
Comments posted to this topic are about the item Minimalistic SELECT
God is real, unless declared integer.
August 26, 2019 at 4:50 am
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”
August 26, 2019 at 7:28 am
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
August 26, 2019 at 2:45 pm
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
August 26, 2019 at 3:07 pm
@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.
August 26, 2019 at 7:06 pm
That's true! It's the decimal separator.
August 26, 2019 at 9:53 pm
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)".
August 27, 2019 at 12:29 am
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
August 27, 2019 at 6:56 am
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.
August 27, 2019 at 9:30 am
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)
August 27, 2019 at 9:35 am
The second dot is a "virtual AS".
Absolutely NO, but if you like to think so OK.
August 27, 2019 at 12:19 pm
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.
August 27, 2019 at 8:47 pm
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.
November 25, 2019 at 6:33 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy