quine (self-producing) SQL query
This is a very interesting wiki article about programs that print their own source code without any access to physical source file (in the filesystem). Examples in the articles include C and Scheme quine programs (yeah, it appears they are called like that). I remember someone asking me long ago (about 5 years) whether I coul开发者_如何学God write an SQL query that "returns itself". I had given it little thought back then (to be perfectly honest I hadn't given it any thought at all). But After reading this article I recalled that thing. Now I want to state that the person who asked that problem is not 100% credible in that it is pretty much possible he had no idea what he was talking about. But maybe it's possible? So, does anyone know if there exist quine SQL queries, whatever "return itself" may mean in that context. Thanks.
SQL Server version, from here:
SELECT Replace(Replace(
'SELECT REPLACE(REPLACE("$",CHAR(34),CHAR(39)),CHAR(36),"$") AS Quine',
Char(34), Char(39)), Char(36),
'SELECT REPLACE(REPLACE("$",CHAR(34),CHAR(39)),CHAR(36),"$") AS Quine')
AS Quine
Oracle version:
SELECT REPLACE(REPLACE('SELECT REPLACE(REPLACE("$",CHR(34),CHR(39)),CHR(36),"$") AS Quine FROM dual',CHR(34),CHR(39)),CHR(36),'SELECT REPLACE(REPLACE("$",CHR(34),CHR(39)),CHR(36),"$") AS Quine FROM dual') AS Quine FROM dual
A couple solutions from the book Oracle SQL Revealed.
123 bytes
select
replace('@''[@'||chr(93)||''')from dual;','@',q'[select
replace('@''[@'||chr(93)||''')from dual;','@',q]')from dual;
100 bytes
select
substr(rpad(1,125,'||chr(39)),26)from dual;select
substr(rpad(1,125,'||chr(39)),26)from dual;
Sorry for digging this out. My shortest version for MS SQL Server is this 131 characters long quine:
DECLARE @ CHAR(65)='DECLARE @ CHAR(65)=#PRINT REPLACE(@,CHAR(35),CHAR(39)+@+CHAR(39))'PRINT REPLACE(@,CHAR(35),CHAR(39)+@+CHAR(39))
Using Snowflake and CURRENT_STATEMENT():
SELECT CURRENT_STATEMENT();
Output:
SELECT CURRENT_STATEMENT();
A PostgreSQL quine (from here):
SELECT left(A.v, 81) || chr(39) || A.v || chr(39) || right(A.v, 12) FROM (SELECT 'SELECT left(A.v, 81) || chr(39) || A.v || chr(39) || right(A.v, 12) FROM (SELECT AS v) AS A;' AS v) AS A;
Here is an alternative SQL Server solution that I think is a bit more explanatory. This shows how you can include arbitrary information such as comments. It makes apparent the difference between "code" and "data" in a quine. That is, you can see how there is an "escaped" representation (source code representation) of the content, and where the "unescaped" representation of the content comes from.
The "content" comes from the encoded formatting of the print
statements. New line characters are included with CHAR(10)
. The VARBINARY is created by coping the "content" and escaping (with newlines added):
SELECT CONVERT(VARBINARY(MAX), 'print ''-- prefix'';'+CHAR(10)+'print ''DECLARE @vs VARBINARY(MAX) = '' + master.dbo.fn_varbintohexstr(@vs) + '';'';'+CHAR(10)+'print CAST(@vs AS VARCHAR(MAX))')
(Note: the output of the above is upper case, fn_varbintohexstr
is lowercase, so one of those will have to change)
This is then used to build the quine:
-- prefix
DECLARE @vs VARBINARY(MAX) = 0x7072696e7420272d2d20707265666978273b0a7072696e7420274445434c415245204076732056415242494e415259284d415829203d2027202b206d61737465722e64626f2e666e5f76617262696e746f6865787374722840767329202b20273b273b0a7072696e742043415354284076732041532056415243484152284d41582929;
print '-- prefix';
print 'DECLARE @vs VARBINARY(MAX) = ' + master.dbo.fn_varbintohexstr(@vs) + ';';
print CAST(@vs AS VARCHAR(MAX))
精彩评论