Minimalistic SELECT

Question

Minimalistic SELECT

Thomas Franz

Hall of Fame

Points: 3897
More actions
August 26, 2019 at 12:00 am

#3670076

Comments posted to this topic are about the item Minimalistic SELECT
God is real, unless declared integer.

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

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

Stewart "Arturius" Campbell SSC Guru Points: 72634 More actions · Answer 1

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: 22680 More actions · Answer 2

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

This reply was modified 5 years, 3 months ago by Carlo Romagnano.

webrunner SSC-Dedicated Points: 31882 More actions · Answer 3

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: 3897 More actions · Answer 4

@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: 22680 More actions · Answer 5

Carlo Romagnano

SSC-Insane

Points: 22680

August 26, 2019 at 7:06 pm

#3674494

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

Oddvar Eikli SSC Journeyman Points: 91 More actions · Answer 6

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 SSChampion Points: 10009 More actions · Answer 7

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: 22680 More actions · Answer 8

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 SSChampion Points: 10009 More actions · Answer 9

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: 22680 More actions · Answer 10

The second dot is a "virtual AS".

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

George Vobr SSChampion Points: 10009 More actions · Answer 11

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 Default port Points: 1487 More actions · Answer 12

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 SSCrazy Points: 2177 More actions · Answer 13

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.